目次へ

解答例 - 実習課題3 - 2.SQL文の実行

(実習課題3)

実習課題2のプログラムを更に改良しなさい。

  • テーブル内のデータ更新をバッチ処理で行うようにする事。
  • 行の追加機能を追加する事。

解答例

▼データベースアクセス用クラスのソース

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(製品)情報管理クラスのソース

JDBC 1章 実習課題2を参照

▼テーブル表示ウィンドウ作成クラスのソース

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();
            }
        });
    }
}

▼データベースの指定

JDBC 1章 実習課題1を参照


↑このページの先頭へ

こちらもチェック!

PR
  • XMLDB.jp