![Webアプリ開発エンジニアのための技術情報サイト[テックスコア]](/common/img/description.gif){kind=small}
解答例 - 実習課題３ - ２．SQL文の実行
（実習課題３）
実習課題２のプログラムを更に改良しなさい。
- テーブル内のデータ更新をバッチ処理で行うようにする事。
- 行の追加機能を追加する事。
解答例
▼データベースアクセス用クラスのソース
package com.techscore.jdbc.chapter2.exercise3;
/**
* CoonectDataBase.java
* TECHSCORE JDBC2章 実習課題3
*
* Copyright (c) 2004 Four-Dimensional Data, Inc.
*/
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.DriverManager;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
public class ProductDAO{
private Connection getConnection() throws SQLException,ClassNotFoundException{
Connection conn = null;
Class.forName("org.postgresql.Driver");
conn = DriverManager.getConnection("jdbc:postgresql://dbserver:5432/Training"
,"postgres" //ユーザ名
,""); //パスワード
return conn;
}
public List loadSummary() throws SQLException,ClassNotFoundException{
List list = new LinkedList();
Connection conn = null;
try{
conn = getConnection();
final String sql = "select p_num, p_name, type, price" +
" from product order by p_num";
Statement statement = conn.createStatement();
ResultSet result = statement.executeQuery(sql);
while (result.next()){
Product data = new Product(
result.getInt(1),
result.getString(2),
result.getString(3),
result.getInt(4));
list.add(data);
}
result.close();
statement.close();
}finally{
if (conn != null){
conn.close();
}
}
return list;
}
public void update(List list) throws SQLException,ClassNotFoundException{
Connection conn = null;
try{
conn = getConnection();
PreparedStatement statement = null;
Iterator ite=list.iterator();
for(int i=0; ite.hasNext(); i++){
Product product = (Product)ite.next();
final String sql = "update product set" +
" p_name = ? " +
",type = ? " +
",price = ? " +
" where p_num = ?";
statement = conn.prepareStatement(sql);
statement.setString(1,product.getPName());
statement.setString(2,product.getType());
statement.setInt(3,product.getPrice());
statement.setInt(4,product.getPNum());
statement.addBatch();
}
if (statement != null){
int result[]=statement.executeBatch();
statement.close();
}
}finally{
if (conn != null){
conn.close();
}
}
}
public void delete(List list) throws SQLException,ClassNotFoundException{
Connection conn = null;
try{
conn = getConnection();
PreparedStatement statement = null;
Iterator ite=list.iterator();
for(int i=0; ite.hasNext(); i++){
final String sql = "delete from product " +
"where p_num = ?";
statement = conn.prepareStatement(sql);
statement.setInt(1,Integer.parseInt(ite.next().toString()));
statement.addBatch();
}
if (statement != null){
int result[]=statement.executeBatch();
statement.close();
}
}finally{
if (conn != null){
conn.close();
}
}
}
public void insert(Product product) throws SQLException,ClassNotFoundException{
Connection conn = null;
try{
conn = getConnection();
PreparedStatement statement = null;
final String sql = "insert into product " +
"values(?,?,?,?)";
statement = conn.prepareStatement(sql);
statement.setInt(1,product.getPNum());
statement.setString(2,product.getPName());
statement.setString(3,product.getType());
statement.setInt(4,product.getPrice());
statement.addBatch();
if (statement != null){
int result[]=statement.executeBatch();
statement.close();
}
}finally{
if (conn != null){
conn.close();
}
}
}
}
▼Product(製品)情報管理クラスのソース
▼テーブル表示ウィンドウ作成クラスのソース
package com.techscore.jdbc.chapter2.exercise3;
/**
* DisplayProductTable.java
* TECHSCORE JDBC2章 実習課題3
*
* Copyright (c) 2004 Four-Dimensional Data, Inc.
*/
import java.util.Iterator;
import java.util.List;
import java.util.LinkedList;
import java.sql.SQLException;
import javax.swing.AbstractButton;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import java.awt.Dimension;
import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
public class DisplayProductTable extends JPanel implements ActionListener {
private JTable table = null;
private List list = null;
private Object[][] originalData;
private Object[][] data;
private static JFrame frame;
public DisplayProductTable() {
super(new GridLayout(2,1));
ProductDAO productDAO = new ProductDAO();
try{
list = (List)productDAO.loadSummary();
//テーブルデータ内容
data = new Object[list.size()][4];
originalData = new Object[list.size()][4];
Iterator ite=list.iterator();
for(int i=0; ite.hasNext(); i++){
Product product = (Product)ite.next();
data[i][0] = new Integer(product.getPNum()).toString();
data[i][1] = product.getPName();
data[i][2] = product.getType();
data[i][3] = new Integer(product.getPrice()).toString();
originalData[i][0] = new Integer(product.getPNum()).toString();
originalData[i][1] = product.getPName();
originalData[i][2] = product.getType();
originalData[i][3] = new Integer(product.getPrice()).toString();
}
createParts(data); //表示する部品を生成する
}catch(SQLException e){
e.printStackTrace();
System.exit(1);
}catch(ClassNotFoundException e){
e.printStackTrace();
System.exit(1);
}
}
private void createParts(Object[][] data){
//テーブルのタイトル
String[] columnNames = {"製品ナンバー",
"製品名",
"製品のタイプ",
"製品の価格"};
table = new JTable(data, columnNames);
table.setPreferredScrollableViewportSize(new Dimension(400, 100));
JScrollPane scrollPane = new JScrollPane(table);
add(scrollPane);
JButton bu = new JButton("更新");
bu.setVerticalTextPosition(AbstractButton.BOTTOM);
bu.setHorizontalTextPosition(AbstractButton.CENTER);
bu.setActionCommand("update");
bu.setSize(20,20);
bu.addActionListener(this);
JButton bud = new JButton("削除");
bud.setVerticalTextPosition(AbstractButton.BOTTOM);
bud.setHorizontalTextPosition(AbstractButton.CENTER);
bud.setActionCommand("delete");
bud.setSize(20,20);
bud.addActionListener(this);
JButton bui = new JButton("挿入");
bui.setVerticalTextPosition(AbstractButton.BOTTOM);
bui.setHorizontalTextPosition(AbstractButton.CENTER);
bui.setActionCommand("insert");
bui.setSize(20,20);
bui.addActionListener(this);
JPanel panel = new JPanel();
panel.setSize(10,30);
panel.add(bu);
panel.add(bud);
panel.add(bui);
add(panel);
}
public void actionPerformed(ActionEvent e) {
ProductDAO productDAO = new ProductDAO();
if ("update".equals(e.getActionCommand())) {
System.out.println("Push Update Button");
List list = (List)checkUpdateData();
try{
productDAO.update(list);
}catch(SQLException ex){
ex.printStackTrace();
System.exit(1);
}catch(ClassNotFoundException ex){
ex.printStackTrace();
System.exit(1);
}
}
if ("delete".equals(e.getActionCommand())) {
System.out.println("Push Delete Button");
List dlist = checkDeleteData();
try{
productDAO.delete(dlist);
}catch(SQLException ey){
ey.printStackTrace();
System.exit(1);
}catch(ClassNotFoundException ey){
ey.printStackTrace();
System.exit(1);
}
}
if ("insert".equals(e.getActionCommand())) {
System.out.println("Push Insert Button");
Product product = createInsertData();
try{
productDAO.insert(product);
}catch(SQLException ez){
ez.printStackTrace();
System.exit(1);
}catch(ClassNotFoundException ez){
ez.printStackTrace();
System.exit(1);
}
}
frame.setVisible(false);
createParts(data);
createAndShowGUI(); //再表示
}
private Product createInsertData(){
Product product = new Product();
int primaryKey = Integer.parseInt(originalData[originalData.length-1][0].toString()) + 1;
product.setPNum(primaryKey);
product.setPName("DefaultName");
product.setType("DefaultType");
product.setPrice(0);
return product;
}
private List checkDeleteData(){
List list = new LinkedList();
int[] rows = table.getSelectedRows();
for(int i = 0; i < rows.length; i++){
System.out.println("SelectedRows:" + rows[i]);
list.add(originalData[rows[i]][0]);
}
return list;
}
private List checkUpdateData(){
int numRows = table.getRowCount();
int numCols = table.getColumnCount();
List list = new LinkedList();
javax.swing.table.TableModel model = table.getModel();
System.out.println("check of data: ");
for (int i=0; i < numRows; i++) {
System.out.print(" row " + i + ":");
for (int j=1; j < numCols; j++) {
System.out.print(" " + model.getValueAt(i, j));
if (!(model.getValueAt(i,j).equals(originalData[i][j]))){
Product product = new Product();
product.setPNum(Integer.parseInt((String)model.getValueAt(i,0)));
product.setPName((String)model.getValueAt(i,1));
product.setType((String)model.getValueAt(i,2));
product.setPrice(Integer.parseInt((String)model.getValueAt(i,3)));
//更新前データを保存
originalData[i][1] = (String)model.getValueAt(i,1);
originalData[i][2] = (String)model.getValueAt(i,2);
originalData[i][3] = (String)model.getValueAt(i,3);
list.add(product);
System.out.println("[UP]");
break;
}
}
System.out.println();
}
System.out.println("--------------------------");
return list;
}
private static void createAndShowGUI() {
// JFrame frame = new JFrame("製品情報テーブル表示");
frame = new JFrame("製品情報テーブル表示");
DisplayProductTable newContentPane = new DisplayProductTable();
newContentPane = new DisplayProductTable();
frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
frame.setContentPane(newContentPane);
frame.pack();
frame.setVisible(true);
}
public static void main(String[] args) {
javax.swing.SwingUtilities.invokeLater(new Runnable() {
public void run() {
createAndShowGUI();
}
});
}
}
▼データベースの指定
