<script language=JavaScript>
</script>
目次)
Java入門 | Java和暦クラス作成 | JSP入門 |
JDBC(Java DataBase Connectivity)入門
by T.Shirokaze 2002/04/03
目次)
JDBCとは
JDBCはJava DataBase Connectivityの略であり,JavaからOracle,DB2,SQL Server,MDBなどのデータベースにアクセスする機能を提供する。最近ではDB以外のファイルやスプレッドシートへのアクセスをも提供している。主にSQLを用いるが,SQLを介さずにDBアクセスを行うクラスも用意されている。
環境設定
LinuxでOracleを用いた場合を例にする。 CLASSPATHにclasses12.zipを加えていることを仮定している。 classes12.zipはjavax.sql.*を含んでおり,これだけでJ2EE対応のJDBC(Java DataBase Connectivity)を利用できる。
sakuraはデフォルトでclasses12.zipがCLASSPATHに入るように設定してあるので,新しい設定は不要。
Tableの確認
OracleにTableが無ければ何もできないので,通常はSQLのcreate文でTableを作成する。 今回はすでにあるTableを検索するサンプルなので,作成は行わずに確認だけ行う。 $sqlplus Enter user-name: scott Enter password: tiger(画面に出てこないが入力はされている) SQL> desc emp; Name Null? Type ----------------------------------------- -------- ---------------------------- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) emp(大文字,小文字は区別しない)は,EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO を1行とするテーブルであると解る。EMPNOがNOT NULL制約がかかっている。 empの全行,全カラムを表示するには,以下を実行する。 SQL>select * from emp; (怒淘のメッセージ) SQL>exit; でsqlplusを終了する。
Javaソースファイルの作成
まず自分のホームディレクトリにviやemacsなどのエディタでソースファイルを作る。以下はOracleに付属しているサンプルのDB接続情報を変Xしたものである。 Employee.javaimport java.sql.*; class Employee { public static void main (String args []) throws SQLException { // Load the Oracle JDBC driver DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); // Connect to the database // You must put a database name after the @ sign in the connection URL. // You can use either the fully specified SQL*net syntax or a short cut // syntax as <host>:<port>:<sid>. The example uses the short cut syntax. // localhostはホスト名であり,他のマシンに接続する時はそのマシン名にする。 // 1521 は通信のポート番号であり,通常はこの1521でよい。 // o8ibeginはOracleのインスタンス(DB)名である。 // scottはOracleのサンプルユーザ名であり,練習はこれで良い。 // tigerはscottのパスワードである。 Connection conn = DriverManager.getConnection ("jdbc:oracle:thin:@localhost:1521:o8ibegin", "scott", "tiger"); // Create a Statement Statement stmt = conn.createStatement (); // Select the ENAME column from the EMP table // scottスキーマのEMPというテーブルからENAME列を全検索するSQLの実行 ResultSet rset = stmt.executeQuery ("select ENAME from EMP"); // Iterate through the result and print the employee names // リザルトセットを最初から最後まで繰り返す // SQLでENAME以外をSELECTした場合,rset.getString(),rset.getInt(), // rset.getDate()で値を取り出す。 while (rset.next ()) System.out.println (rset.getString (1)); rset.close(); conn.close(); } }コンパイル & 実行
$javac Employee.java $java Employee SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER $
テーブルの全項目表示JSP
実行例<%@ page contentType="text/html; charset=SHIFT_JIS" %> <%@ page import ="java.sql.*" %> <%! ResultSet rset; Connection conn; %> <% try { String tableName = request.getParameter("tableName"); if(tableName == null || tableName.equals("")) { tableName = "cat"; } // Load the Oracle JDBC driver DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); // Connect to the database // You must put a database name after the @ sign in the connection URL. // You can use either the fully specified SQL*net syntax or a short cut // syntax as ::. The example uses the short cut syntax. // localhostはホスト名であり,他のマシンに接続する時はそのマシン名にする。 // 1521 は通信のポート番号であり,通常はこの1521でよい。 // o8ibeginはOracleのインスタンス(DB)名である。 // scottはOracleのサンプルユーザ名であり,練習はこれで良い。 // tigerはscottのパスワードである。 conn = DriverManager.getConnection ("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger"); // Create a Statement Statement stmt = conn.createStatement (); // Select the ENAME column from the EMP table // scottスキーマのEMPというテーブルからENAME列を全検索するSQLの実行 rset = stmt.executeQuery ("select * from "+tableName); ResultSetMetaData rmeta = rset.getMetaData(); int columnCount = rmeta.getColumnCount(); String[] columnNames = new String[columnCount]; for(int i = 0; i < columnCount ; i++){ columnNames[i] = rmeta.getColumnName(i+1); } // Iterate through the result and print the employee names // リザルトセットを最初から最後まで繰り返す // SQLでENAME以外をSELECTした場合,rset.getString(),rset.getInt(), // rset.getDate()で値を取り出す。 %> <HTML> <HEAD> <TITLE>WEB SQL</TITLE> </HEAD> <BODY> <P><B>WEB SQL(SCOTT SCHEMA)</B></P> <form method=get> TableName <input type=text name=tableName> <input type=submit value="Submit"> </form> <%= tableName %> <TABLE border="1"> <TR> <% for(int i=0; i<columnCount; i++) { %> <TH><%= columnNames[i] %></TH> <% } %> </TR> <% while (rset.next ()) { %> <TR> <% for(int i=0; i<columnCount; i++) { %> <TD><%= rset.getString (i+1) %></TD> <% } %> </TR> <% } %> </TABLE> </BODY> </HTML> <% } catch(Exception ex) { ex.printStackTrace(); } finally { rset.close(); conn.close(); } %>
JDBC解説
-
JDBCドライバのロード (ドライバクラス名指定)
-
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
DBコネクションの取得(URL=prefix,ホスト名,ポート番号,DB名,ユーザ名,パスワード)
-
Connection conn = DriverManager.getConnection ("jdbc:oracle:thin:@localhost:1521:o8ibegin", "scott", "tiger");
Statement(SQL文)の作成と実行
-
Statement stmt = conn.createStatement (); ResultSet rset = stmt.executeQuery ("select * from "+tableName);
ResultSetからのデータの取り出し
-
String str1 = rset.getString(1); // 1カラム目がCHAR, VARCHAR, VARCHAR2の場合 int int2 = rset.getInt(2); // 2カラム目がNUMBERの場合 Date date3 = rset.getDate(3); // 3カラム目がDATEの場合 // getXXX()の引数はカラム番号(int)でなく,カラム名(String)でも取り出せる
ResultSetからのResultSetMetaData(テーブルカラムの名前,型,サイズ情報)の取得
-
ResultSetMetaData rmeta = rset.getMetaData(); int columnCount = rmeta.getColumnCount(); String[] columnNames = new String[columnCount]; for(int i = 0; i < columnCount ; i++){ columnNames[i] = rmeta.getColumnName(i+1); }
ResultSet,Connectionのクローズ
-
rset.close(); conn.close();
テーブルへの1行挿入
-
Statement stmt = con.createStatement(); stmt.executeUpdate( "INSERT INTO COFFEES VALUES ('Colombian', 101, 7.99, 0, 0)");
PreparedStatementの利用
-
通常のSQLの実行は,DB関数の前に構文解析してから処理される。 PreparedStatemetは,構文とデータを別々に指定するので,高速に動作する。 一つのPreparedStatementを再利用すると全体のパフォーマンスは更に向上する。 sql文中の値に相当するところに,"?"を埋め込んでおいて,別メソッド(setString, setInt等で値を指定する。 PreparedStatement updateSales; String updateString = "update COFFEES set SALES = ? where COF_NAME like ?"; updateSales = con.prepareStatement(updateString); int [] salesForWeek = {175, 150, 60, 155, 90}; String [] coffees = {"Colombian", "French_Roast", "Espresso", "Colombian_Decaf", "French_Roast_Decaf"}; int len = coffees.length; for(int i = 0; i < len; i++) { updateSales.setInt(1, salesForWeek[i]); updateSales.setString(2, coffees[i]); updateSales.executeUpdate(); }
DataSourceとコネクションプーリング
-
DataSource(接続情報)を用いると,コネクションをプールから取り出せるので, SQLの実行の都度コネクションを取得する場合に比べて,素早く取得できる。 J2EE環境では以下のようにJNDI(Java Name and Directory Interface)から検索して取得する。 Context ctx = new InitialContext(); DataSource ds = (DataSource)ctx.lookup("jdbc/InventoryDB"); Connection con = ds.getConnection();
トランザクション
-
selectで検索する場合はデフォルトのAutoCommitで良いが,insert, update, deleteで 複数のSQLが全て成功しないと意味のある処理とならない場合は,AutoCommitをはずしてトランザクションを実行する。 下の例で,conはConnection,DBExceptionは自分で定義した例外とする。 try { con.setAutoCommit(false); update処理1 update処理2 ... con.commit(); } catch (Exception ex) { try { con.rollback(); throw new DBException("Transaction failed: " + ex.getMessage()); } catch (SQLException sqx) { throw new DBException("Rollback failed: " + sqx.getMessage()); } }
SQLの組み立てはStringBufferを用いる
-
配列データなどからSQLを組み立てることは頻繁に行われるが,これにStringの "+"を用いると非常にコストがかかる。この場合は以下のようにStringBuffer.append() を用いると10倍以上高速化できる。 StringBuffer sqlSt = new StringBuffer("inset into coffers values ('); for(int i=0; i<len-1; i++) { sqlStr.append(data[i]).append("',"); } sqlStr.append(data[len-1]).append("')");
ビデオ管理システム
複数のページを切り替えるシステムになると,表示(View)はJSP, 入力パラメータの解釈とロジックの呼び出し(Controller)はサーブレット,データの保持とロジックはJavaBeans(通常のJavaクラス)で作成する。 DBはOracleである。
JSP: index.jsp<%@ page import="java.util.*, java.text.*, java.io.*" %> <%@ page import="com.freedomsoft.video.*" %> <%@ page contentType="text/html; charset=shift_jis"%> <jsp:useBean id="videoBean" scope="session" class="com.freedomsoft.video.VideoBe an" /> <html> <HEAD> <liNK REL="stylesheet" TYPE="text/css" HREF="/video/freedom.css"> <TITLE>ビデオ管理システム</TITLE> </HEAD> <% ArrayList list = videoBean.getRentalList(); ArrayList updateList = videoBean.getUpdateList(); SimpleDateFormat dateformatter = new SimpleDateFormat("yyyy/MM/dd(EE) hh:mm"); String dateString = dateformatter.format(new java.util.Date()); %> <body> <H1>ビデオ管理システム</H1> <H4><%= dateString %></H4> <form method="post" action="/video/control"> <table align=center> <tr> <TH>番号</TH> <TH>タイトル</TH> <TH>借りた人</TH> <TH>返却予定日</TH> <TH>更新</TH> </TR> <% int iSize = list.size(); for(int i=0; i<iSize; i++) { String[] record = (String[])list.get(i); %> <tr> <TD><%= record[0] %></TD> <TD class=blue><%= record[1] %></TD> <TD><input name=user value="<%= record[2] %>"></TD> <TD><input name=date value="<%= record[3] %>"></TD> <TD><input type=checkbox name=check value="<%= i %>"></TD> </TR> <% } %> </TABLE> <BR clear=all> <CENTER> <INPUT type=submit name="submit" value="更新実行"> </CENTER> <HR> <H3>新ソフト</H3> <table align=center> <tr> <TH>番号</TH> <TH>タイトル</TH> <TH>追加</TH> </TR> <% for(int i=0; i<5; i++) { %> <tr> <TD><input name=newid></TD> <TD><input name=newtitle></TD> <TD><input type=checkbox name=newcheck value="<%= i %>"></TD> </TR> <% } %> </TABLE> </form> </body> </html>JavaBean: VideoBean.java
package com.freedomsoft.video; import java.sql.*; import javax.sql.*; import java.util.*; import com.freedomsoft.db.DataSourceManager; /** VideoBean.java 2002.01.25 by T.Shirokaze * ビデオ貸し出し情報をもつ Bean */ public class VideoBean { private ArrayList updateList = new ArrayList(); private Connection con = null; /** * コンストラクタ */ public VideoBean() { } /** 変更リストを取得 */ public ArrayList getUpdateList() { return updateList; } /** リストを挿入 */ public void insertList(ArrayList ar) { int aSize = ar.size(); System.out.println("insertList"+aSize); try { makeConnection(); con.setAutoCommit(false); for(int i=0; i<aSize; i++) { String[] record = (String[])ar.get(i); insert(record); } con.commit(); } catch(Exception e) { try { con.rollback(); } catch(Exception re) {} e.printStackTrace(); } finally { closeConnection(); } } /** リストを変更 */ public void setUpdateList(ArrayList ar) { this.updateList = ar; int aSize = ar.size(); System.out.println("setUpdateList"+aSize); try { makeConnection(); con.setAutoCommit(false); for(int i=0; i<aSize; i++) { String[] record = (String[])ar.get(i); update(record); } con.commit(); } catch(Exception e) { try { con.rollback(); } catch(Exception re) {} e.printStackTrace(); } finally { closeConnection(); } } /** コネクションを作成 */ private void makeConnection() { System.out.println("makeConnection"); try { con = DataSourceManager.getConnection(DataSourceManager.DEFAULT_URL); } catch(Exception e) { e.printStackTrace(); } } /** コネクションをクローズ */ private void closeConnection() { try { con.close(); } catch (Exception e) { e.printStackTrace(); } } /** ビデオ情報を挿入 */ public int insert(String[] record) throws SQLException { int count = -1; PreparedStatement prepStmt = null; String insertStatement = "insert into video_list values ( ? , ? , ? , ?)"; prepStmt = con.prepareStatement(insertStatement); prepStmt.setString(1, record[0]); prepStmt.setString(2, record[1]); prepStmt.setString(3, ""); prepStmt.setString(4, ""); count = prepStmt.executeUpdate(); prepStmt.close(); return count; } /** * レンタル情報の更新を行います。 * @param newStrings 更新する値の配列 * @return -1 < 更新した行数。-1 == 更新失敗 */ public int update(String[] sa) throws SQLException { int rowCnt = -1; String sb = "update video_list set karite = ? , hizuke = ? where videoid = ?"; System.out.println("update "+ sa[0] + " " + sa[1] + " " + sa[2]); PreparedStatement prepStmt = null; prepStmt = con.prepareStatement(sb); prepStmt.setString(1, sa[1]); prepStmt.setString(2, sa[2]); prepStmt.setString(3, sa[0]); prepStmt.executeUpdate(); return rowCnt; } /** ビデオ情報を削除 */ public void deleteVideo(String videoid) { PreparedStatement prepStmt = null; try { makeConnection(); String deleteStatement = "delete from video_list " + "where title = ? "; prepStmt = con.prepareStatement(deleteStatement); prepStmt.setString(1, videoid); prepStmt.executeUpdate(); prepStmt.close(); } catch(Exception e) { e.printStackTrace(); } finally { try { if(prepStmt != null) { prepStmt.close(); } } catch(Exception e) { } closeConnection(); } } /** ヴィデオ情報をロード */ public ArrayList getRentalList() { System.out.println("LoadList"); Statement stmt = null; ResultSet rs = null; ArrayList ar = new ArrayList(); try { makeConnection(); String selectStatement = "select * from video_list"; stmt = con.createStatement(); rs = stmt.executeQuery(selectStatement); while(rs.next()) { String[] record = new String[4]; record[0] = rs.getString(1); record[1] = rs.getString(2); record[2] = rs.getString(3); record[3] = rs.getString(4); for(int i=0; i<4; i++) { if(record[i] == null) record[i]=""; System.out.println(record[i]); } ar.add(record); } } catch(Exception e) { e.printStackTrace(); } finally { try { if(stmt != null) stmt.close(); } catch(Exception e) { } closeConnection(); } return ar; } static void main(String[] args) { VideoBean vb = new VideoBean(); String[] sa = {"C4","ゲーム","",""}; ArrayList ar = new ArrayList(); ar.add(sa); vb.insertList(ar); } }JavaBean: DataSourceManager.java
package com.freedomsoft.db; import java.util.*; import java.sql.*; import javax.naming.*; import javax.sql.DataSource; import java.sql.Connection; import oracle.jdbc.pool.OracleDataSource; import oracle.jdbc.pool.OracleConnectionCacheImpl; /** * 説明:コントロールサーブレット
* Oracleデータソースを提供します。 */ public class DataSourceManager{ /** デフォルトデータソース */ static public final String DEFAULT_URL = "/jdbc/DefaultDataSource"; /** * ローカルモード用のデータソースを保管します。 */ private static HashMap localModeConnectionData = new HashMap(); /** * ローカルモード用DataSourceの接続先PORT番号 */ private static final int DATABASE_PORT = 1521; static { synchronized(localModeConnectionData){ localModeConnectionData.put(DEFAULT_URL,new ConnectionData("thin","localhost","o8ibegin",DATABASE_PORT,"scott","tiger")); } } /** * ローカルモード用DataSourceインスタンスを生成します。 * 注意:ConnectionDataの内容の妥当性チェックはしていません。 * もし、内容に誤りがあった場合、getConnection()時にSQLExceptionが発生します。 * * @param localModeConnectionData DataSourceを生成するために必要な情報 * @return OracleDataSource * @throws SQLException OracleDataSource生成に失敗したとき */ public static OracleDataSource createOracleDataSource(ConnectionData localModeConnectionData) throws SQLException{ OracleDataSource oracleDataSource = new OracleDataSource(); // 本来ならOracleConnectionCacheImpl でコネクションプーリングが行えるはず。 // Oracle8.1.7では動いていたが、Oracle8.1.6では動かなかった。 // OracleConnectionCacheImpl oracleDataSource = new OracleConnectionCacheImpl(); oracleDataSource.setDriverType(localModeConnectionData.driverType); oracleDataSource.setServerName(localModeConnectionData.hostname); oracleDataSource.setDatabaseName(localModeConnectionData.sid); oracleDataSource.setPortNumber(localModeConnectionData.port); oracleDataSource.setUser(localModeConnectionData.user); oracleDataSource.setPassword(localModeConnectionData.password); return oracleDataSource; } /** * データソースを取得します。 * * @param jndiUrl JNDIのPathを指定します。 * @return OracleDataSource OracleDataSourceです。Connectionを取得するときは引数なしのgetConnection()を使用してください。 * @throws SQLException ローカルモードでDataSourceの生成に失敗した場合に発生します。 */ public static DataSource getDataSource(String jndiUrl) throws SQLException{ /* データソース */ OracleDataSource dataSource = null; try{ dataSource = createOracleDataSource((ConnectionData)localModeConnectionData.get(jndiUrl)); }catch(SQLException se){ System.out.println("DataSourceManager:SQLException:Can't get the DataSource with localmode"); throw se; } return dataSource; } /** * コネクションを取得します。 * * @param jndiUrl JNDIのPathを指定します。 * @return Connection * @throws SQLException */ public static Connection getConnection(String jndiUrl) throws SQLException { /* OracleDataSourceも使えない場合は以下でConnectionを返す。 DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); return DriverManager.getConnection ("jdbc:oracle:thin:@localhost:1521:o8ibegin", "scott", "tiger"); */ return getDataSource(jndiUrl).getConnection(); } }
package com.freedomsoft.video; import java.io.*; import java.util.*; import java.text.*; import javax.servlet.*; import javax.servlet.http.*; /** * RentalServelet.java by T.Shirokaze 2002/01/25 * 説明:ビデオ管理サーブレット */ public class RentalServlet extends HttpServlet { ServletContext ctx = null; public void init(ServletConfig config) { System.out.println("RentalServlet will go on"); synchronized(this) { if(ctx == null) { ctx = config.getServletContext(); } } } public void doPost (HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { System.out.println("/n in doPost"); HttpSession session = request.getSession(); response.setContentType("text/html; charset=shift_jis"); VideoBean videoBean = (VideoBean)session.getAttribute("videoBean"); ArrayList list = videoBean.getRentalList(); String[] users = null; String[] dates = null; String[] checks = null; String[] newChecks = null; String[] newIDs = null; String[] newTitles = null; int nChecks=0; int nNewIDs=0; int nNewTitles=0; int nNewChecks=0; Enumeration e = request.getParameterNames(); while (e.hasMoreElements()) { String sParamName = (String)e.nextElement(); System.out.println("sParamName="+sParamName); String[] sVals = request.getParameterValues(sParamName); for(int i=0; i<sVals.length; i++) { System.out.print(sVals[i]+" "); } System.out.println(); if("user".equals(sParamName)) { users = new String[sVals.length]; for(int i=0; i < sVals.length; i++) { users[i] = new String(sVals[i].getBytes("8859_1"), "JISAutoDetect"); } System.out.println("users: "+users.length); } else if("date".equals(sParamName)) { dates = sVals; System.out.println("dates: "+dates.length); } else if("check".equals(sParamName)) { checks = sVals; System.out.println("checks: "+checks.length); nChecks = sVals.length; } else if("newid".equals(sParamName)) { newIDs = sVals; nNewIDs = sVals.length; } else if("newtitle".equals(sParamName)) { newTitles = new String[sVals.length]; for(int i=0; i < sVals.length; i++) { newTitles[i] = new String(sVals[i].getBytes("8859_1"), "JISAutoDetect"); } nNewTitles = sVals.length; System.out.println("newTitles: "+nNewTitles); } else if("newcheck".equals(sParamName)) { newChecks = sVals; nNewChecks = sVals.length; System.out.println("newChecks: "+nNewChecks); } else if(sParamName.equals("submit")) { } } System.out.println("newChecks: "+nNewChecks); ArrayList newAr = new ArrayList(); for(int i=0; i< nNewChecks; i++) { int j = Integer.parseInt(newChecks[i]); String[] record = new String[2]; record[0] = newIDs[j]; record[1] = newTitles[j]; newAr.add(record); System.out.println(j+" "+record[0]+" "+record[1]); } videoBean.insertList(newAr); ArrayList ar = new ArrayList(); for(int i=0; i< nChecks; i++) { int j = Integer.parseInt(checks[i]); String[] lRecord = (String[])list.get(j); System.out.print("check:"+j); String[] record = new String[3]; record[0] = lRecord[0]; record[1] = users[j]; record[2] = dates[j]; ar.add(record); } videoBean.setUpdateList(ar); // session.setAttribute("videoBean", videoBean); System.out.println("forward to list.jsp"); RequestDispatcher rd = ctx.getRequestDispatcher("/index.jsp"); rd.forward(request, response); } }
参考URL)
姉妹編)Java入門 | Java和暦クラス作成 | JSP入門 |