![Webアプリ開発エンジニアのための技術情報サイト[テックスコア]](/common/img/description.gif){kind=small}
解答例 - 実習課題１ - ４．便利なメソッド
(実習課題１)
以下のクラスを作成する
- PostgresqlのTrainingデータベースのaccept_orderテーブルへ、データの挿入、更新、削除を行うクラスを作成する 。
各操作（挿入、更新、削除)はそれぞれ別々のメソッドに実装する - TestSuite、setUp、tearDownを使用して、上のテストを作成する。
- Test実行前とTest実行後でデータベースの状態が変化しないように工夫すること。
解答例
▼ディレクトリ構成例├─com │ └─techscore │ └─junit │ └─chapter4 │ └─exercise1 AcceptingOrder.class │ AcceptingOrderDAO.class │ AcceptingOrderDAOTest.class ├─lib▼accept_order テーブルを検索するクラス
package com.techscore.junit.chapter4.exercise1;
/**
* AcceptingOrderDAO.java
* TECHSCORE JUnit4章 実習課題
*
* Copyright (c) 2004 Four-Dimensional Data, Inc.
*/
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.DriverManager;
public class AcceptingOrderDAO{
public AcceptingOrderDAO(){
}
private Connection getConnection() throws ClassNotFoundException,SQLException{
Connection conn = null;
Class.forName("org.postgresql.Driver");
conn = DriverManager.getConnection("jdbc:postgresql://sasuke:5432/Training","postgres","");
conn.setAutoCommit(false);
return conn;
}
public void insertOrder(AcceptingOrder order) throws ClassNotFoundException,SQLException{
Connection conn = null;
try{
conn = getConnection();
final String sql = "insert into accept_order " +
"values(" +
"?,?,?,?,?,?,?" +
")";
PreparedStatement statement = conn.prepareStatement(sql);
statement.setString(1,order.getONum());
statement.setString(2,order.getCNum());
statement.setString(3,order.getPNum());
statement.setInt(4,order.getDcRate());
statement.setInt(5,order.getOptionPrice());
statement.setString(6,order.getEmployee());
statement.setDate(7,(Date)order.getAcceptDate());
statement.executeUpdate();
conn.commit();
statement.close();
}catch(SQLException e){
throw e;
}finally{
if (conn != null){
conn.close();
}
}
}
public boolean deleteOrder(String orderNumber)
throws ClassNotFoundException,
SQLException{
Connection conn = null;
int deletedRow = 0;
boolean deletedResult = true;
try{
conn = getConnection();
final String sql = "delete from accept_order where o_num = ?";
PreparedStatement statement = conn.prepareStatement(sql);
statement.setString(1,orderNumber);
deletedRow = statement.executeUpdate();
if(deletedRow == 1){
deletedResult = true;
conn.commit();
}else{
if(deletedRow == 0){
deletedResult = false;
conn.rollback();
}else{
throw new SQLException();
}
}
statement.close();
}catch(SQLException e){
throw e;
}finally{
if (conn != null){
conn.close();
}
}
return deletedResult;
}
public boolean updateOrder(AcceptingOrder order)
throws ClassNotFoundException,
SQLException{
Connection conn = null;
int updatedRow = 0;
boolean updatedResult =true;
try{
conn = getConnection();
final String sql = "update accept_order " +
"set " +
"c_num = ?, p_num = ?," +
"dc_rate = ?, option_price = ?, " +
"employee = ?, accept_date = ? " +
"where o_num = ?";
PreparedStatement statement = conn.prepareStatement(sql);
statement.setString(1,order.getCNum());
statement.setString(2,order.getPNum());
statement.setInt(3,order.getDcRate());
statement.setInt(4,order.getOptionPrice());
statement.setString(5,order.getEmployee());
statement.setDate(6,(Date)order.getAcceptDate());
statement.setString(7,order.getONum());
updatedRow = statement.executeUpdate();
if (updatedRow == 1){
updatedResult = true;
conn.commit();
}else{
if(updatedRow == 0){
updatedResult = false;
conn.rollback();
}else{
throw new SQLException();
}
}
statement.close();
}catch(SQLException e){
throw e;
}finally{
if (conn != null){
conn.close();
}
}
return updatedResult;
}
}
▼AcceptingOrderDAO クラスにてテーブルへの挿入/更新/削除のテストを行う為のメソッドを持つクラス
package com.techscore.junit.chapter4.exercise1;
/**
* AcceptingOrderDAOTest.java
* TECHSCORE JUnit4章 実習課題
*
* Copyright (c) 2004 Four-Dimensional Data, Inc.
*/
import junit.framework.Test;
import junit.framework.TestCase;
import junit.framework.TestSuite;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.DriverManager;
import java.sql.ResultSet;
public class AcceptingOrderDAOTest extends TestCase{
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 UPDATED_ROW_NG_MESSAGE = "データの更新を失敗しました。";
private final static String DELETED_ROW_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 o_num = ?";
private Connection conn = null;
private AcceptingOrder order = null;
public AcceptingOrderDAOTest(String name){
super(name);
}
protected void setUp(){
String orderNumber ="9999";
String customerNumber = "1111";
String productNumber = "222";
int dcRate = 33;
int optionPrice = 44;
String employee = "555";
Date acceptDate = Date.valueOf("2004-09-16");
order = new AcceptingOrder(orderNumber,customerNumber,
productNumber,dcRate,
optionPrice, employee,
acceptDate);
try{
Class.forName("org.postgresql.Driver");
conn = DriverManager.getConnection("jdbc:postgresql://sasuke:5432/Training","postgres","");
AcceptingOrderDAO acceptingOrderDAO = new AcceptingOrderDAO();
}catch(ClassNotFoundException e){
e.printStackTrace();
fail(TEST_NG_MESSAGE);
}catch(SQLException e){
e.printStackTrace();
fail(TEST_NG_MESSAGE);
}
}
protected void tearDown(){
try{
if(conn != null){
conn.close();
}
}catch(SQLException e){
e.printStackTrace();
}
}
public static Test suite(){
TestSuite suite=new TestSuite();
suite.addTest(new AcceptingOrderDAOTest("testInsertOrder"));
suite.addTest(new AcceptingOrderDAOTest("testUpdateOrder"));
suite.addTest(new AcceptingOrderDAOTest("testDeleteOrder"));
return suite;
}
public void testInsertOrder(){
try{
AcceptingOrderDAO acceptingOrderDAO = new AcceptingOrderDAO();
acceptingOrderDAO.insertOrder(order);
PreparedStatement statement = conn.prepareStatement(sql);
statement.setString(1,order.getONum());
ResultSet result = statement.executeQuery();
checkSelectedData(result);
result.close();
statement.close();
}catch(ClassNotFoundException e){
e.printStackTrace();
fail(TEST_NG_MESSAGE);
}catch(SQLException e){
e.printStackTrace();
fail(TEST_NG_MESSAGE);
}
}
public void testDeleteOrder(){
try{
AcceptingOrderDAO acceptingOrderDAO = new AcceptingOrderDAO();
assertTrue(DELETED_ROW_NG_MESSAGE,acceptingOrderDAO.deleteOrder(order.getONum()));
PreparedStatement statement = conn.prepareStatement(sql);
statement.setString(1,order.getONum());
ResultSet result = statement.executeQuery();
assertFalse(DELETED_ROW_NG_MESSAGE,result.next());
result.close();
statement.close();
}catch(ClassNotFoundException e){
e.printStackTrace();
fail(TEST_NG_MESSAGE);
}catch(SQLException e){
e.printStackTrace();
fail(TEST_NG_MESSAGE);
}
}
public void testUpdateOrder() throws SQLException{
order.setCNum("0001");
order.setDcRate(99);
order.setAcceptDate(Date.valueOf("2004-09-17"));
try{
AcceptingOrderDAO acceptingOrderDAO = new AcceptingOrderDAO();
assertTrue(UPDATED_ROW_NG_MESSAGE,acceptingOrderDAO.updateOrder(order));
PreparedStatement statement = conn.prepareStatement(sql);
statement.setString(1,order.getONum());
ResultSet result = statement.executeQuery();
checkSelectedData(result);
result.close();
statement.close();
}catch(ClassNotFoundException e){
e.printStackTrace();
fail(TEST_NG_MESSAGE);
}catch(SQLException e){
e.printStackTrace();
fail(TEST_NG_MESSAGE);
}
}
private void checkSelectedData(ResultSet result){
try{
assertTrue(UPDATED_ROW_NG_MESSAGE,result.next());
assertEquals(ORDER_NUMBER_NG_MESSAGE,result.getString(1),order.getONum());
assertEquals(CUSTOMER_NUMBER_NG_MESSAGE,result.getString(2),order.getCNum());
assertEquals(PRODUCT_NUMBER_NG_MESSAGE,result.getString(3),order.getPNum());
assertEquals(RATE_NG_MESSAGE,new Integer(result.getInt(4)),new Integer(order.getDcRate()));
assertEquals(OPTION_PRICE_NG_MESSAGE,new Integer(result.getInt(5)),new Integer(order.getOptionPrice()));
assertEquals(ACCEPT_DATE_NG_MESSAGE,result.getString(6),order.getEmployee());
assertEquals(EMPLOYEE_NG_MESSAGE,result.getDate(7),order.getAcceptDate());
assertFalse(UPDATED_ROW_NG_MESSAGE,result.next());
}catch(SQLException e){
e.printStackTrace();
fail(TEST_NG_MESSAGE);
}
}
}
▼accept_order テーブルの内容は以下のURLにあります。
http://www.4dd.co.jp/techscore/developing/tech/sql/db.html▼受注データを管理するクラス
3章実習課題1と同じです。▼JUnit起動例
1.GUI版 # java junit.swingui.TestRunner com.techscore.junit.chapter4.exercise1.AcceptingOrderDAOTest 2.テキスト版 # java junit.textui.TestRunner com.techscore.junit.chapter4.exercise1.AcceptingOrderDAOTest いずれもカレントディレクトリは、comディレクトリと同じ並びです。▼環境設定
1章の3節に記述されている$CLASSPATHの設定にpostgresql用のJDBCド ライバであるjarファイルのパスを加えてください。▼データベースの指定
解答例のソースでのデータベースとの接続で指定している内容は、以下の内容です。 postgresJDBCドライバ:org.postgresql.Driver サブプロトコル :postgresql ホスト名 :sasuke ポート番号 :5432 データベース名 :Training ユーザ名 :postgres
