PreparedStatementでの複数検索
- struts1.3, postgresql8.4, JDK1.6を使用して、1つから6つの条件でSQLを検索する方法について説明します。
- 条件にnullが含まれる場合、検索は行われません。PreparedStatementを使用してSQLを実行し、結果を処理する方法も紹介します。
- if文の数を減らすための良い方法を探しています。具体的な方法をお教えください。
- ベストアンサー
PreparedStatementでの複数検索
PreparedStatementでの複数検索 環境：struts1.3,postgresql8.4,JDK1.6 テキストボックスなどから1つから6つの条件でsqlを検索したいのですが この場合、1つでもnullがある時は検索されません。 Connection con = null; con = ProductDAO.getConnection(); //SQLの実行 String selectStatement = "SELECT * FROM test,test_list WHERE test.id = test_list.id " + "AND prefecture=? " + "AND low_price>=? " + "AND low_age>=? " + "AND sameday_select =? " + "AND terms_select =?" + "AND category=?"; PreparedStatement prepStmt = con.prepareStatement(selectStatement); prepStmt.setString(1, aForm.getPrefecture()); prepStmt.setInt(2, aForm.getLow_price()); prepStmt.setInt(3, aForm.getLow_age()); prepStmt.setString(4, aForm.getSameday_select()); prepStmt.setString(5, aForm.getTerms_select()); prepStmt.setString(6, aForm.getCategory()); ResultSet rs = prepStmt.executeQuery(); (3) SQLの実行結果の処理 while (rs.next()) { 処理 } この場合、if文などでaForm.getPrefecture()がnullの場合などで1つ1つ条件を付けないと駄目でしょうか？その場合、if文が膨大な数になってしまうため何か良い方法はありますか。 あるならば、具体的に教えて下さい。
- toetou
- お礼率52% (19/36)
- PostgreSQL
- 回答数3
- ありがとう数29
- みんなの回答 （3）
- 専門家の回答
質問者が選んだベストアンサー
回答番号: No.2 の続きです。 aForm.getPrefecture() などの戻り値が null ではなく空文字 ("") の場合には、coalesce 関数の 1 つ目に渡している条件式の結果が NULL になりません。空文字の場合には NULL になるように nullif 関数をかませるとうまくいくと思います。 String selectStatement = "SELECT * FROM test,test_list WHERE test.id = test_list.id " + "AND coalesce(prefecture=nullif(?, ''), true) " + "AND coalesce(low_price>=nullif(?, ''), true) " + "AND coalesce(low_age>=nullif(?, ''), true) " + "AND coalesce(sameday_select =nullif(?, ''), true) " + "AND coalesce(terms_select =nullif(?, ''), true)" + "AND coalesce(category=nullif(?, ''), true)"; 値が指定されていない場合に aForm.getLow_price() などの int 型の戻り値がどうなるかが分からないのですが、0 を返すなら nullif(?, 0) などと書き換えてあげる必要があるかもしれません。
その他の回答 (2)
- yamada59
- ベストアンサー率74% (29/39)
NULLに対する演算はNULLになるので、以下のSQLを書き換えてみてはいかがでしょうか。 String selectStatement = "SELECT * FROM test,test_list WHERE test.id = test_list.id " + "AND coalesce(prefecture=?, true) " + "AND coalesce(low_price>=?, true) " + "AND coalesce(low_age>=?, true) " + "AND coalesce(sameday_select =?, true) " + "AND coalesce(terms_select =?, true)" + "AND coalesce(category=?, true)"; coalesce関数は引数の中から最初のNULLでない値を返すので、NULL以外が渡された場合には条件式を評価してtrueかfalseとなり、NULLが渡された場合には2つ目の引数が返されtrueとなって条件式が無視されます。
補足
回答ありがとうございます。 "AND coalesce(prefecture=?, true) " + "AND coalesce(low_price>=?, true) " + "AND coalesce(low_age>=?, true) " + "AND coalesce(sameday_select =?, true) " + "AND coalesce(terms_select =?, true)" + "AND coalesce(category=?, true)"; 上記の方法で試してみましたが、 すべての値を選択する場合は、検索できましたが、 やはりどれか一つでも値が選択されていない場合は、検索されませんでした。 logを見ると選択されていない物は、NULLではなく、="" の状態です。
- root139
- ベストアンサー率60% (488/809)
if文でnull判定する以外に簡単な方法は思いつきませんね。 StringBuilder や PreparedStatement#setObject を使えば多少簡潔に出来るとは思いますが。 例 ---------------------------------------------------- StringBuilder sqlBuilder = new StringBuilder("SELECT * FROM test,test_list WHERE test.id = test_list.id "); List<Object> sqlParams = new ArrayList<Object>(); if (aForm.getPrefecture() != null) { sqlBuilder.append("AND prefecture = ? "); sqlParams.add(aForm.getPrefecture()); } if (aForm.getLow_price() != null) { sqlBuilder.append("AND low_price >= ? "); sqlParams.add(aForm.getLow_price()); } if (aForm.getLow_price() != null) { sqlBuilder.append("AND low_age >= ? "); sqlParams.add(aForm.getLow_age()); } if (aForm.getSameday_select() != null) { sqlBuilder.append("AND sameday_select = ? "); sqlParams.add(aForm.getSameday_select()); } if (aForm.getSameday_select() != null) { sqlBuilder.append("AND terms_select = ? "); sqlParams.add(aForm.getTerms_select()); } if (aForm.getCategory() != null) { sqlBuilder.append("AND category = ? "); sqlParams.add(aForm.getCategory()); } PreparedStatement prepStmt = conn.prepareStatement(sqlBuilder.toString()); int paramIndex = 0; for (Object param : sqlParams) { prepStmt.setObject(++paramIndex, param); } ResultSet rs = prepStmt.executeQuery(); ------------------------------------------------------- (aForm の getLow_price, getLow_age はInteger型を返すとする)
補足
回答ありがとうございます。 例えば、PrefectureとSameday_selectなど複数で検索したい場合は、 さらにif文を記載するという方法ですよね。
関連するQ&A
- oracle10g java jdbc 大量insertの高速化
oracle10g java jdbc 大量insertの高速化 以下のプログラムで1万件処理したところ30秒ぐらいかかりました。 まだ高速化の余地があると思うのですが、何かいい方法はありませんでしょうか？ String sql = "INSERT INTO TEST3 VALUES(?,?)"; PreparedStatement ps = con.prepareStatement(sql); while(rs5.next()){ int id = rs5.getInt(1); String line = rs5.getString(2); ps.setInt(1,id); ps.setString(2,line); ps.addBatch(); } ps.executeBatch();
- ベストアンサー
- Java
- WebSphere/DB2/ResultSet close/Exception
WebSphereを使って、Listenerで取得したデータソースを利用していますが、 String sql1 = "SELECT A1 FROM TEST1"; String sql2 = "SELECT B2 FROM TEST2 WHERE B1 = ?"; Connection conn = datasource.getConnection(); PreparedStatement ps1 = conn.preparedStatement(sql1); ResultSet rs1 = ps1.executeQuery(); while(rs1.next()){ PreparedStatement ps2 = conn.preparedStatement(sql2); ps2.setString(1,rs1.getString("A1")); ResultSet rs2 = ps2.executeQuery(); ps2.close(); } このロジックで、ps2をクローズすると、ps1で取得したResultSet rs1がクローズされるというExceptionが発生してしまいます。 (ループして戻ったときにrs1.next()でexceptionが起こる) なにか対処方法があるかご存知の方いらっしゃいましたら、よろしくお願いいたします。
- ベストアンサー
- Java
- SQL文(検索)で困っています
下記のSQL文は顧客IDで顧客名を取得していますが、 PreparedStatement stmt = con.prepareStatemen("SELECT * FROM CUSTOMER_MASTER WHERE CUS_ID =?"); stmt.setString(1,"cus_id"); 以下省略・・・ 全顧客リストを取得するにはどのように指定すればよいでしょうか？ アスタリスクみたいな指定も考えたのですが・・・。 よろしくお願いします。
- ベストアンサー
- Java
- データベースへ接続してからのデータ追加方法
掲示板を作成しているのですが、DAOクラスの使いかたがいまいちわかりません。 public ArrayList<textbeen> findAll(){ //スレッド一覧を保持するリスト ArrayList<textbeen> list = new ArrayList<textbeen>(); Connection con = null; try{ //データベースに接続 con = createConnection(); //データベースにSQL文を送信 String sql = "select * from TEXT"; Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(sql); while(rs.next()){ String name = rs.getString("KB_NAME"); String title = rs.getString("KB_TITLE"); String mail = rs.getString("KB_MAIL"); String text = rs.getString("KB_TEXT"); String pass = rs.getString("KB_TITLE_PASS"); textbeen thread = new textbeen(name, title, mail, text, pass); //TO(Threadオブジェクト)を、保持するリストに追加 list.add(thread); } //スレッド一覧を返す。 return list; }catch(Exception e){ e.printStackTrace(); return null; }finally{ //データベースからの切断 closeConnection(con); } } public void create(textbeen emp){ Connection con = null; try{ //データベースに接続 con = createConnection(); //データベースにSQL文を送信 String sql = "insert into TEXT values(?, ?, ?, ? ,?)"; PreparedStatement stmt = con.prepareStatement(sql); stmt.setString(1, emp.getName()); stmt.setString(2, emp.getTitle()); stmt.setString(3, emp.getMail()); stmt.setString(4, emp.getText()); stmt.setString(5, emp.getPassword()); stmt.executeUpdate(); }catch(Exception e){ e.printStackTrace(); }finally{ //データベースからの切断 closeConnection(con); } } 以上のようにTextDAOクラスを作り、MVCモデルに従ってサーブレットからJSPにて追加用のページを表示したいのですが、どうすればDAOクラスのメソッドを使えるのかがわかりません。DAOクラスは入門書に書いてあったのを名前だけ変えたりして使っております。 すでにtextbeen.javaファイルにてsetName()やgetName()などは定義しております。 宜しく御願い致します。
- ベストアンサー
- Java
- ＪＤＢＣのＤＢ検索
import java.io.BufferedReader; import java.io.IOException; import java.io.InputStreamReader; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import com.mysql.jdbc.PreparedStatement; import com.mysql.jdbc.Statement; public class AddressDB { private static enum Procnumlist { NAME("名前"), FNAME("フルネーム"), END("終了"); private String name; private Procnumlist(String name) { } } //上の列挙型を基にした処理判定用マップを作成 private static final Map<Integer, Procnumlist> procmap; static { procmap = new HashMap<Integer, Procnumlist>(); procmap.put(1,Procnumlist.NAME); procmap.put(2,Procnumlist.FNAME); procmap.put(6,Procnumlist.END); } //結果 //ResultSetは受け取らない private static void ans(List<Addr> ans) { try { System.out.println("検索結果"); for (int i = 0; i < ans.size(); i++) { Addr addr = ans.get(i); System.out.println(addr.getName() + "：" ); } } catch (SQLException e) { e.printStackTrace(); } finally { //閉じる。 close(); } } //名前検索（フルネーム） private static List<Addr> srchFName(String keyword,Connection con) throws IOException { String sql = "SELECT * FROM data2 where name=?"; PreparedStatement ps = null; try{ ps = (PreparedStatement) con.prepareStatement(sql); ps.setString(1,keyword); ResultSet rs = ps.executeQuery(); //List<Addr> result = new ArrayList(Addr); while(rs.next()){ rs.getString("name"); } }catch (SQLException ex) { } return null; } //名前検索 private static List<Addr> srchName(String keyword,Connection con) throws IOException { String sql = "SELECT * FROM data2 where name like ?"; PreparedStatement ps = null; try{ ps = (PreparedStatement) con.prepareStatement(sql); ps.setString(1,"%"+keyword+"%"); ResultSet rs = ps.executeQuery(); while(rs.next()){ Addr addr = rs.getString("name"); } }catch (SQLException ex) { } return result; } private static boolean checkProcNum(int value) { return procmap.get(value) != null; } public static void main(String[] args)throws IOException{ while(true) { createConeection(); //処理条件入力 System.out.println("検索\n１．名前（ファーストネーム）で検索\n２．フルネームで検索\n６．終了"); //検索値入力 BufferedReader br = new BufferedReader(new InputStreamReader(System.in)); int procnum = Integer.valueOf(br.readLine()); //入力チェック if (!checkProcNum(procnum)) { System.out.println("正しい値を入れてください。"); continue; } Procnumlist proc = procmap.get(procnum); if (proc == Procnumlist.END) { break; } if(proc == Procnumlist.NAME){ System.out.println("名前を入力してください。"); BufferedReader name = new BufferedReader(new InputStreamReader(System.in)); String keyword = name.readLine(); List<Addr> result = srchName(keyword,null); ans(result); } if(proc == Procnumlist.FNAME){ System.out.println("フルネームを入力してください。"); BufferedReader name = new BufferedReader(new InputStreamReader(System.in)); String keyword = name.readLine(); List<Addr> result = srchFName(keyword,null); ans(result); } } } //コネクション private static Connection createConeection() { String url = "jdbc:mysql://localhost/address"; String user = "test"; String password = "pass"; try { return DriverManager.getConnection(url, user, password); } catch (SQLException e) { e.printStackTrace(); return null; } } } 現在ＪＤＢＣでＤＢ接続し入力された値をＤＢから検索し出力するものを作成しておりますが詰んでしまいました。 入力した値をsrchで検索し結果をansで出力しようとしています。どのようにしたらよいのでしょうか
- 締切済み
- Java
- DAOクラスの使い方
ある書籍を参考に勉強しております。以下のDAOクラスのデータ追加に関して使い方がわかりません。 サーブレット、もしくはJSPにどのように書けば、DBにデータを追加できるようになるのでしょうか？ 宜しく御願い致します。 package chap11_database.sec03; import java.sql.*; import java.util.ArrayList; public class EmployeeDAO { //データベースとの接続に必要な情報を保持するフィールド変数 private final static String DRIVER_URL ="jdbc:mysql://192.168.2.200:3306/satoh" + "?useUnicode=true&characterEncoding=utf8"; private final static String DRIVER_NAME = "com.mysql.jdbc.Driver"; private final static String USER_NAME = "root"; private final static String PASSWORD = "1qaz!QAZ"; //接続メソッド protected Connection createConnection(){ try{ Class.forName(DRIVER_NAME); Connection con = DriverManager.getConnection(DRIVER_URL, USER_NAME, PASSWORD); return con; } catch(Exception e){ e.printStackTrace(); } return null; //例外が発生した場合はnullを返す。 } //切断メソッド protected void closeConnection(Connection con){ try{ con.close(); } catch(Exception ex){ ex.printStackTrace(); } } public ArrayList<Employee> findAll(){ //社員一覧を保持するリスト ArrayList<Employee> list = new ArrayList<Employee>(); Connection con = null; try{ //データベースに接続 con = createConnection(); //データベースにSQL文を送信 String sql = "select * from employee"; Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(sql); while(rs.next()){ int id = rs.getInt("id"); String name = rs.getString("name"); String address = rs.getString("address"); int tel = rs.getInt("tel"); Date birthday = rs.getDate("birthday"); Employee employee = new Employee(id, name, address, tel, birthday); //TO(Employeeオブジェクト)を、社員一覧を保持するリス手に追加 list.add(employee); } //社員一覧を返す。 return list; }catch(Exception e){ e.printStackTrace(); return null; }finally{ //データベースからの切断 closeConnection(con); } } //社員登録メソッド public void create(Employee emp){ Connection con = null; try{ //データベースに接続 con = createConnection(); //データベースにSQL文を送信 String sql = "insert into employee values(?, ?, ?, ? ,?)"; PreparedStatement stmt = con.prepareStatement(sql); stmt.setInt(1, emp.getId()); stmt.setString(2, emp.getName()); stmt.setString(3, emp.getAddress()); stmt.setInt(4, emp.getTel()); stmt.setDate(5, emp.getBirthday()); stmt.executeUpdate(); }catch(Exception e){ e.printStackTrace(); }finally{ //データベースからの切断 closeConnection(con); } } }
- ベストアンサー
- Java
- requestについて困っています。
下記のbooleanの結果を他のサーブレットで取得して、結果ごとに"mapping.findForward("")"の処理をしたいのですが、取得の仕方が分かりません。お願い致します。 ＜MemberDAO.java＞ ・・・ public boolean isEntriedMail(String member_mail) throws SQLException{ Connection con = ds.getConnection(); PreparedStatement stmt = con.prepareStatement(ISENTRIEDSQL); ResultSet rs = null; try{ stmt.setString(1,member_mail); rs = stmt.executeQuery(); if(rs.next()){ int count = rs.getInt(1); if(count == 0){ return false; } } }catch(SQLException ex){ return true; }finally{ if(rs != null){ rs.close(); } stmt.close(); con.close(); } return true; }
- 締切済み
- Java
- ＪＤＢＣについて
import java.io.BufferedReader; import java.io.IOException; import java.io.InputStreamReader; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import com.mysql.jdbc.PreparedStatement; import com.mysql.jdbc.Statement; public class DBtest { public static void main(String[] args){ try { Connection con = null; PreparedStatement ps = null; String url = "jdbc:mysql://localhost/address"; String user = "test"; String password = "pass"; con = DriverManager.getConnection(url, user, password); Statement st = (Statement) con.createStatement(); Statement st2 = (Statement) con.createStatement(); String sql = "SELECT * FROM data2"; ResultSet rs = st2.executeQuery(sql); try { System.out.println("ok"); // ここでクエリを実行 while(rs.next()){ System.out.println(rs.getString("name")+rs.getString("age")); } //氏名入力 System.out.println("氏名を入力してください。"); BufferedReader na = new BufferedReader(new InputStreamReader(System.in)); String Na = na.readLine(); String ins = "INSERT INTO data2(name,age)VALUES('?',20)"; ps = (PreparedStatement) con.prepareStatement(ins); ps.setString(1,Na); ps.executeUpdate(); //INSERT実行 int rs2 = st2.executeUpdate(ins); System.out.println(rs2); } catch (IOException e) { // TODO 自動生成された catch ブロック e.printStackTrace(); } finally { // 直前の try ブロックに入ったら、ここは必ず実行される con.close(); } } catch (SQLException ex) { System.out.println("SQLException: " + ex.getMessage()); System.out.println("SQLState: " + ex.getSQLState()); System.out.println("VendorError: " + ex.getErrorCode()); // 接続できない時 } } } 現在ｊａｖａでｍｙｓｑｌに接続してＤＢ内の検索や追加等を行うものを作成しています。 現状は入力されたものをＤＢに追加することまでできましたが 検索で 入力された値がＤＢの中に一致する、または部分一致したものを表示させるやり方がわかりません
- ベストアンサー
- Java
- PreparedStatementを使用したＤＢへの書き込み
検索させていただいたのですが、同じ事例が見当たらなかったので、質問させてください。 タイトルのようにPreparedStatementを使用してＤＢへ書き込みたいのです。 import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.Statement; import java.sql.ResultSet; import java.sql.DriverManager; import java.util.Vector; import java.util.Hashtable; import java.util.Date; import java.util.Iterator; ～略～ Date date = new Date(); insert.setString(1,loginid); insert.setInt(2,cart.getGoodsid()); insert.setInt(3,cart.getOrdernum()); insert.setDate(4,date); ※ insert.executeUpdate(); ～略～ というような感じで行いたいのですが、 ※部分でコンパイルエラーになってしまいます。 （この場合はシンボルを解決できませんとなります） 上記以外にもいろいろ試してみましたが、 どうしてもわかりません。 回答よろしくお願いします。
- 締切済み
- Java
- javaの掲示板について
今現在パスワードを比較して一致していれば削除としたのですがjava.sql.SQLException: Can not issue data manipulation statements with executeQuery().というエラーがtomcatにでます。 サーブレットはこう書いています //削除 if(deleteMessage != null){ String deleteid = request.getParameter("id"); String deletepassword = request.getParameter("deletepassword"); String rspassword = dao.getPassword(deleteid); if (deletepassword.equals(rspassword)){ dao.deleteMessage(delete); success = "削除しました。"; request.setAttribute("success", success); } else{ errorpass = "パスワードが違います。"; request.setAttribute("errorpass", errorpass); } } 比較用のパスワードをデータベースから取り出すDAOはこうしています。 public int deleteMessage(String strid) throws Exception { Vector messageList = new Vector(); int i = 0; Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; try{ conn = this.getConnection(); String sql = "delete from MESSAGE_TABLE where ID = ? "; pstmt = conn.prepareStatement(sql); pstmt.setString(1, strid); rs = pstmt.executeUpdate(); } finally { this.close(rs); this.close(pstmt); this.close(conn); } return i; } public String getPassword(String id) throws Exception { Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; String rspassword = null; try { String deleteid = null; conn = this.getConnection(); String sql = "SELECT * FROM MESSAGE_TABLE where ID = ? "; pstmt = conn.prepareStatement(sql); pstmt.setString(1, id); rs = pstmt.executeQuery(); if(rs.next()){ rspassword = rs.getString("PASSWORD"); } } finally { this.close(rs); this.close(pstmt); this.close(conn); } return rspassword; } データベースからパスワードがとれていないから起きているのだとは思うのですが、どのようにしたらとれるでしょうか？ よろしくお願いします。
- ベストアンサー
- Java
お礼
ありがとうございます。 無事if文を使わずに複数の検索ができました。 int 型もnullif(?, 0)にした場合、無事できました。