解答例 - 実習課題１ - ３．テストクラス２

(実習課題１）

以下のクラスを作成する

PostgresqlのTrainingデータベースのaccept_orderテーブルの検索を行うクラスを作成する
まず、Junitを用いてクラスのテストクラスを作成し、その後に検索クラスを作成する。
テストのソースなどをみれば、作成したクラスの仕様がわかるように工夫する

解答例

▼ディレクトリ構成例

├─com
│  └─techscore
│      └─junit
│          └─chapter3
│              └─exercise1  AcceptingOrder.class
│                             AcceptingOrderDAO.class
│                             AcceptingOrderDAOTest.class
├─lib

▼受注データを管理するクラス

package com.techscore.junit.chapter3.exercise1;

/**
 * AcceptingOrder.java
 * TECHSCORE JUnit3章 実習課題1
 *
 * Copyright (c) 2004 Four-Dimensional Data, Inc.
 */
import java.sql.Date;

public class AcceptingOrder {

    private String  oNum;
    private String  cNum;
    private String  pNum;
    private int     dcRate;
    private int     optionPrice;
    private String  employee;
    private Date    acceptDate;

    public AcceptingOrder(){
    }

	public AcceptingOrder(String oNum, String cNum, String pNum,
                          int dcRate, int optionPrice,
                          String employee, Date acceptDate ){
        this.oNum = oNum;
        this.cNum = cNum;
        this.pNum = pNum;
        this.dcRate = dcRate;
        this.optionPrice = optionPrice;
        this.employee     = employee;
        this.acceptDate  = acceptDate;
    }

	public Date getAcceptDate() {
		return acceptDate;
	}

	public String getCNum() {
		return cNum;
	}

	public int getDcRate() {
		return dcRate;
	}

	public String getEmployee() {
		return employee;
	}

	public String getONum() {
		return oNum;
	}

	public int getOptionPrice() {
		return optionPrice;
	}

	public String getPNum() {
		return pNum;
	}

	public void setAcceptDate(Date date) {
		this.acceptDate = date;
	}

	public void setCNum(String string) {
		this.cNum = string;
	}

	public void setDcRate(int i) {
		this.dcRate = i;
	}

	public void setEmployee(String string) {
        this.employee = string;
	}

	public void setONum(String string) {
        this.oNum = string;
	}

	public void setOptionPrice(int i) {
        this.optionPrice = i;
	}

	public void setPNum(String string) {
        this.pNum = string;
	}

}

▼accept_order テーブルを検索するクラス

package com.techscore.junit.chapter3.exercise1;

/**
 * AcceptingOrderDAO.java
 * TECHSCORE JUnit3章 実習課題1
 *
 * Copyright (c) 2004 Four-Dimensional Data, Inc.
 */
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.DriverManager;
import java.util.LinkedList;
import java.util.List;

public class AcceptingOrderDAO{
    public AcceptingOrderDAO(){
    }

    private Connection getConnection() throws SQLException,ClassNotFoundException{
        Connection conn = null;
        Class.forName("org.postgresql.Driver");
        conn = DriverManager.getConnection("jdbc:postgresql://sasuke:5432/Training",
		"postgres","");
        return conn;
    }

	public List loadSummary(String customerNumber) throws SQLException,
                                                          ClassNotFoundException{
        List list = new LinkedList();
        Connection conn = null;
        try{
            conn = getConnection();
            final String sql = "select o_num, c_num, p_num, " +
                                "dc_rate, option_price," +
                                "employee, accept_date " +
                                "from accept_order " +
                                "where c_num = ?";
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setString(1,customerNumber);
            ResultSet result = statement.executeQuery();
            while (result.next()){
                AcceptingOrder data = new AcceptingOrder(
                                        result.getString(1),
                                        result.getString(2),
                                        result.getString(3),
                                        result.getInt(4),
                                        result.getInt(5),
                                        result.getString(6),
                                        result.getDate(7));
                list.add(data);
            }
            result.close();
            statement.close();
        }finally{
            if (conn != null){
                conn.close();
            }
        }
        return list;
    }
}

▼accept_order テーブルを検索するクラスを単体デバッグするクラス

package com.techscore.junit.chapter3.exercise1;

/**
 * AcceptingOrderDAOTest.java
 * TECHSCORE JUnit3章 実習課題1
 *
 * Copyright (c) 2004 Four-Dimensional Data, Inc.
 */

import junit.framework.TestCase;
import java.util.List;
import java.util.Iterator;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class AcceptingOrderDAOTest extends TestCase{

    private final static String LIST_NG_MESSAGE = "データベースの検索リスト異常";
    private final static String ORDER_NUMBER_NG_MESSAGE = "受注番号の取得異常";
    private final static String CUSTOMER_NUMBER_NG_MESSAGE = "顧客ナンバーの取得異常";
    private final static String PRODUCT_NUMBER_NG_MESSAGE = "製品ナンバーの取得異常";
    private final static String RATE_NG_MESSAGE = "製品の値引率の取得異常";
    private final static String OPTION_PRICE_NG_MESSAGE = "オプションの価格の取得異常";
    private final static String ACCEPT_DATE_NG_MESSAGE = "受注した日の取得異常";
    private final static String EMPLOYEE_NG_MESSAGE = "従業員ナンバーの取得異常";
    private final static String TEST_NG_MESSAGE = "例外が発生しテストが異常終了しました。";
    private final static String sql = "select o_num, c_num, p_num, " +
                                      "dc_rate, option_price," +
                                      "employee, accept_date " +
                                      "from accept_order " +
                                      "where c_num = ?";

    public AcceptingOrderDAOTest(String name){
        super(name);
    }

    private Connection getConnection() throws ClassNotFoundException,SQLException{
         Class.forName("org.postgresql.Driver");
         Connection conn = DriverManager.getConnection("jdbc:postgresql://sasuke:5432/Training",
		 "postgres","");
         return conn;
    }

    public void testLoadSummary(){
        String customerNumber = "1009";
        AcceptingOrderDAO acceptingOrderDAO = new AcceptingOrderDAO();
        Connection conn = null;
        try{
            List list = acceptingOrderDAO.loadSummary(customerNumber);
            assertNotNull(LIST_NG_MESSAGE,list);
            conn = getConnection();
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setString(1,customerNumber);
            ResultSet result = statement.executeQuery();

            checkDetailData(list,result);

            result.close();
            statement.close();
        }catch(ClassNotFoundException e){
            e.printStackTrace();
            fail(TEST_NG_MESSAGE);
        }catch(SQLException e){
            e.printStackTrace();
            fail(TEST_NG_MESSAGE);
        }finally{
            try{
                if (conn != null){
                    conn.close();
                }
            }catch(SQLException e){
                e.printStackTrace();
            }
        }
    }

	public void checkDetailData(List list,ResultSet result) throws SQLException{
        Iterator iterator=list.iterator();
        while(result.next()){
            AcceptingOrder acceptingOrder = (AcceptingOrder)iterator.next();
            assertEquals(ORDER_NUMBER_NG_MESSAGE,result.getString(1),
                                                 acceptingOrder.getONum());
            assertEquals(CUSTOMER_NUMBER_NG_MESSAGE,result.getString(2),
                                                    acceptingOrder.getCNum());
            assertEquals(PRODUCT_NUMBER_NG_MESSAGE,result.getString(3),
                                                   acceptingOrder.getPNum());
            assertEquals(RATE_NG_MESSAGE,new Integer(result.getInt(4)),
                                         new Integer(acceptingOrder.getDcRate()));
            assertEquals(OPTION_PRICE_NG_MESSAGE,new Integer(result.getInt(5)),
                                                 new Integer(acceptingOrder.getOptionPrice()));
            assertEquals(ACCEPT_DATE_NG_MESSAGE,result.getString(6),
                                                acceptingOrder.getEmployee());
            assertEquals(EMPLOYEE_NG_MESSAGE,result.getDate(7).toString(),
                                             acceptingOrder.getAcceptDate().toString());
            AcceptingOrder order = new AcceptingOrder(result.getString(1),result.getString(2),
                                                      result.getString(3),result.getInt(4),
                                                      result.getInt(5),result.getString(6),
                                                      result.getDate(7));
        }
        assertFalse(LIST_NG_MESSAGE,iterator.hasNext());
    }
}

▼accept_order テーブルの内容は以下のURLにあります。

http://www.4dd.co.jp/techscore/developing/tech/sql/db.html

▼JUnit起動例

1.GUI版
# java junit.swingui.TestRunner com.techscore.junit.chapter3.exercise1.AcceptingOrderDAOTest
2.テキスト版
# java junit.textui.TestRunner com.techscore.junit.chapter3.exercise1.AcceptingOrderDAOTest
いずれもカレントディレクトリは、com ディレクトリと同じ並びです。

▼環境設定

1章の3節に記述されている$CLASSPATHの設定にpostgresql用のJDBCド
ライバであるjarファイルのパスを加えてください。

▼データベースの指定

解答例のソースでのデータベースとの接続で指定している内容は、以下の内容です。
postgresJDBCドライバ:org.postgresql.Driver
サブプロトコル		:postgresql
ホスト名			:sasuke
ポート番号			:5432
データベース名		:Training
ユーザ名			:postgres

↑このページの先頭へ