Created
March 30, 2011 10:10
-
-
Save seyan/894164 to your computer and use it in GitHub Desktop.
PreparedStatementサンプル(SQL Server)。複数パラメータを用いた検索を考慮。(検索条件項目が複数存在していて,いずれの項目も必須で無い場合)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
import java.util.Date; | |
/** | |
* 本一冊を表すクラス | |
* | |
*/ | |
public class Book { | |
private Integer id; | |
private String title; | |
private String author; | |
private String publisher; | |
private Date date; | |
private Integer price; | |
public Book(){ | |
this.id = null; | |
this.title = null; | |
this.author = null; | |
this.publisher = null; | |
this.date = null; | |
this.price = null; | |
} | |
public Book(int id, String title, String author, String publisher, Date date, int price){ | |
this.id = id; | |
this.title = title; | |
this.author = author; | |
this.publisher = publisher; | |
this.date = date; | |
this.price = price; | |
} | |
/** | |
* アクセス用メソッド | |
* | |
*/ | |
public String getTitle() { | |
return title; | |
} | |
public void setTitle(String title) { | |
this.title = title; | |
} | |
public String getAuthor() { | |
return author; | |
} | |
public void setAuthor(String author) { | |
this.author = author; | |
} | |
public String getPublisher() { | |
return publisher; | |
} | |
public void setPublisher(String publisher) { | |
this.publisher = publisher; | |
} | |
public Date getDate() { | |
return date; | |
} | |
public void setDate(Date date) { | |
this.date = date; | |
} | |
public Integer getPrice() { | |
return price; | |
} | |
public void setPrice(Integer price) { | |
this.price = price; | |
} | |
public Integer getId() { | |
return id; | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
import java.sql.Connection; | |
import java.sql.PreparedStatement; | |
import java.sql.ResultSet; | |
import java.sql.SQLException; | |
import java.util.ArrayList; | |
import java.util.List; | |
import org.apache.commons.logging.Log; | |
import org.apache.commons.logging.LogFactory; | |
/** | |
* Bookテーブルへのアクセスに関する処理を行うDAOクラス | |
* | |
*/ | |
public class BookDAO { | |
/** 基本となるSQL文 | |
* dateに関しては、yyyy/mm/ddの形式(スタイル111)の文字列として取得する | |
* */ | |
private static final String sql = "SELECT id, title, author, publisher, CONVERT(VARCHAR(12), date, 111) AS date_varchar, price FROM books" | |
+ " WHERE NULLIF(title, '') = COALESCE( ?, NULLIF(title, '')) " | |
+ " AND NULLIF(author, '') = COALESCE( ?, NULLIF(author, '')) " | |
+ " AND NULLIF(publisher, '') = COALESCE( ?, NULLIF(publisher, '')) " | |
+ " AND NULLIF(price, 0) <= COALESCE( ?, NULLIF(price, 0))" | |
+ " AND NULLIF( CONVERT(DATETIME, CONVERT(VARCHAR(12), date, 111), 111) , '') >= COALESCE( ?, NULLIF( CONVERT(DATETIME, CONVERT(VARCHAR(12), date, 111), 111) , ''))" ; | |
/** ログを埋め込むクラス */ | |
private static final Log LOG = LogFactory.getLog(BookDAO.class.getName()); | |
private Connection con; | |
public BookDAO(Connection con){ | |
this.con = con; | |
} | |
/** | |
* Bookの検索を行う。 | |
* @param condition | |
* priceに関しては、パラメータとして渡されたprice以下の価格のものを検索する。 | |
* dateに関しては、パラメータとして渡されたdateより新しい日付を持つものを検索する。 | |
* その他の文字列などに関しては、完全一致のものを検索結果とする | |
* nullまたは空文字として渡されたパラメータに関する検索条件は無視される。 | |
* @return 検索結果 | |
*/ | |
public List<Book> searchBook(Book condition){ | |
if(condition == null){ | |
throw new IllegalArgumentException("引数bookがnullです。"); | |
} | |
List<Book> books = new ArrayList<Book>(); | |
ResultSet rset = null; | |
PreparedStatement pStmt = null; | |
try{ | |
pStmt = con.prepareStatement(sql); | |
// SQL Serverはnullと空文字を区別するので同等に扱うために空文字をnullに変換する | |
setEmptyToNull(condition); | |
pStmt.setString(1, condition.getTitle()); | |
pStmt.setString(2, condition.getAuthor()); | |
pStmt.setString(3, condition.getPublisher()); | |
pStmt.setInt(4, condition.getPrice()); | |
pStmt.setString(5, Util.date2String(condition.getDate())); | |
// クエリーを実行して結果セットを取得 | |
rset = pStmt.executeQuery(); | |
books = map(rset); | |
}catch(SQLException e){ | |
e.printStackTrace(); | |
}finally { | |
// DB接続オブジェクトをクローズ(★Connectionに関しては、呼び出し元でクローズする) | |
if (rset != null) { | |
try{ | |
rset.close(); | |
}catch(SQLException e){ | |
// getLocalizedmessage():地域対応された(翻訳など)メッセージを返す | |
LOG.error(e.getLocalizedMessage(), e); | |
} | |
} | |
if(pStmt != null){ | |
try{ | |
pStmt.close(); | |
}catch(SQLException e){ | |
LOG.error(e.getLocalizedMessage(), e); | |
} | |
} | |
} | |
return books; | |
} | |
/** | |
* 引数で与えられたResultSetをList化したものを返す | |
* @param rset | |
*/ | |
private List<Book> map(ResultSet rset){ | |
if(rset == null){ | |
throw new IllegalArgumentException("引数rsetがnullです。"); | |
} | |
ArrayList<Book> list = new ArrayList<Book>(); | |
try { | |
while (rset.next()) { | |
list.add(new Book(rset.getInt("id"), rset.getString("title"), rset.getString("author"), rset.getString("publisher"), Util.string2Date(rset.getString("date_varchar")), rset.getInt("price"))); | |
} | |
} catch (SQLException e) { | |
LOG.error(e.getLocalizedMessage(), e); | |
} | |
return list; | |
} | |
/** | |
* SQL Serverはnullと空文字を区別するので同等に扱うために空文字をnullに変換する | |
* @param condition | |
*/ | |
private void setEmptyToNull(Book condition){ | |
if(condition != null && "".equals( condition.getTitle() ) ){ | |
condition.setTitle(null); | |
} | |
if(condition != null && "".equals( condition.getAuthor() ) ){ | |
condition.setAuthor(null); | |
} | |
if(condition != null && "".equals( condition.getPublisher() ) ){ | |
condition.setPublisher(null); | |
} | |
if(condition != null && "".equals( condition.getDate() ) ){ | |
condition.setDate(null); | |
} | |
if(condition != null && "".equals( condition.getPrice() ) ){ | |
condition.setPrice(null); | |
} | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
import java.sql.Connection; | |
import java.sql.DriverManager; | |
import java.sql.SQLException; | |
import java.util.List; | |
import java.util.ResourceBundle; | |
import org.apache.commons.logging.Log; | |
import org.apache.commons.logging.LogFactory; | |
/** | |
* prepareStatementのサンプル | |
* 複数パラメータを用いた検索を考慮。(検索条件項目が複数存在していて,いずれの項目も必須で無い場合) | |
* | |
* SQL Server編 | |
* | |
*/ | |
public class Page131 { | |
private static final Log LOG = LogFactory.getLog(Page131.class.getName()); | |
private static final String TITLE = ""; | |
private static final String AUTHOR = "ShakeSpeare"; | |
private static final String PUBLISHER = null; | |
private static final String DATE = "1902/10/15"; | |
private static final String PRICE = "1300"; | |
/** プロパティファイルの名前(リソースバンドルの基底名。完全指定クラス名) */ | |
private static final String RESOURCE_BASENAME = "jp.co.test.wasbook.nakayama.sqlinjection.sqlinjection"; | |
private static final String DRIVER_NAME = "sqlserver.driverName"; | |
private static final String JDBC_URL = "sqlserver.jdbcUrl"; | |
private static final String USER_ID = "sqlserver.userId"; | |
private static final String PASSWORD = "sqlserver.password"; | |
public static void main(String[] args) { | |
Book condition = new Book(); | |
condition.setTitle(TITLE); | |
condition.setAuthor(AUTHOR); | |
condition.setPublisher(PUBLISHER); | |
if( DATE != null){ | |
condition.setDate(Util.string2Date(DATE)); | |
} | |
if( PRICE != null){ | |
condition.setPrice(Integer.parseInt(PRICE)); | |
} | |
Connection con = null; | |
try{ | |
// ResourceBundleでリソースを動的に取得し、DBにアクセス | |
ResourceBundle resource = ResourceBundle.getBundle(RESOURCE_BASENAME); | |
String driverName = resource.getString(DRIVER_NAME); | |
Class.forName(driverName); | |
String jdbcUrl = resource.getString(JDBC_URL); | |
String userId = resource.getString(USER_ID); | |
String password = resource.getString(PASSWORD); | |
con = DriverManager.getConnection(jdbcUrl, userId, password); | |
con.setAutoCommit(false); | |
// DAOを使って検索 | |
BookDAO dao = new BookDAO(con); | |
List<Book> list = dao.searchBook(condition); | |
//今回は結果を標準出力に表示 | |
printList(list); | |
}catch(ClassNotFoundException e){ | |
LOG.error(e.getLocalizedMessage(), e); | |
}catch(SQLException e){ | |
try{ | |
con.rollback(); | |
}catch(SQLException ex){ | |
LOG.error(ex.getLocalizedMessage(), ex); | |
} | |
}finally{ | |
try{ | |
if(con != null){ | |
con.close(); | |
} | |
}catch(SQLException e){ | |
LOG.error(e.getLocalizedMessage(), e); | |
} | |
} | |
} | |
/** | |
* 検索結果表示メソッド | |
* @param books | |
*/ | |
private static void printList(List<Book> books){ | |
for(Book b : books){ | |
System.out.println(b.getId()); | |
System.out.println(b.getTitle()); | |
System.out.println(b.getAuthor()); | |
System.out.println((b.getPublisher())); | |
System.out.println(b.getPrice()); | |
System.out.println(Util.date2String( b.getDate() )); | |
System.out.println(); | |
} | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# SQLinjection対策プログラム用 | |
sqlserver.driverName = com.microsoft.sqlserver.jdbc.SQLServerDriver | |
sqlserver.jdbcUrl = jdbc:sqlserver://localhost:1433;DatabaseName=wasbookDB | |
sqlserver.userId = dbusers | |
sqlserver.password = dbusers |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
import java.text.SimpleDateFormat; | |
import java.util.Date; | |
import org.apache.commons.logging.Log; | |
import org.apache.commons.logging.LogFactory; | |
public class Util { | |
private static final Log LOG = LogFactory.getLog(Util.class.getName()); | |
private static final String DATE_PATTERN = "yyyy/MM/dd"; | |
/** | |
* String文字列型をDate日付型へ変換 | |
* sql.dateはjavaのdateと考え方がずれていて問題の原因となるため、SQLServerから文字列として取得したのち、util.Dateに変換 | |
* @param str | |
* @return | |
*/ | |
public static Date string2Date(String str){ | |
SimpleDateFormat sdf = new SimpleDateFormat(DATE_PATTERN); | |
Date date = new Date(); | |
try { | |
date = sdf.parse(str); | |
} catch (java.text.ParseException e) { | |
LOG.error(e.getLocalizedMessage(), e); | |
} | |
return date; | |
} | |
/** | |
* 文字列を日付型へ変換 | |
* @param date | |
* @return | |
*/ | |
public static String date2String(Date date){ | |
if(date != null){ | |
SimpleDateFormat sdf = new SimpleDateFormat(DATE_PATTERN); | |
return sdf.format(date); | |
} | |
return null; | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
【レビュー後に変更した点】
propertiesファイルを使用するようにした。
loggingツールを使用するようにした。
Connetionオブジェクトのクローズ位置を変更した。(開いたクラスが閉じるべきであるため。)
java.sql.dateは問題児(javaのDateとSQLのDateでは、細かい部分が異なっており問題の原因となることがあるため)なので安易に使わない。SQLserverから文字列で日付を取得後、Javaでutil.Dateに変換して用いることとした。
BookSearchCondition(検索条件を保持するクラス)を削除し、検索条件はBookに保持させることにした。
下位クラスへのキャスト(ListをArrayListにキャストしたり)は行ってはいけないので、修正。
【メモ】
SQL Serverは列別名が使えない…。