|
繼續(xù)上文： 學(xué)習(xí)使用jdk1.7中內(nèi)置數(shù)據(jù)庫(kù)Derby(二) 三） 運(yùn)行網(wǎng)絡(luò)模式的Derby數(shù)據(jù)庫(kù) 這種模式下，需要使用兩個(gè)控制臺(tái)窗口，一個(gè)用于啟動(dòng)Derby數(shù)據(jù)庫(kù)服務(wù)端，另一個(gè)做為訪問(wèn)Derby數(shù)據(jù)庫(kù)的客戶端,。 可以通過(guò)DERBY_HOMEin目錄下的startNetworkServer.bat來(lái)啟動(dòng)Derby數(shù)據(jù)庫(kù)服務(wù)端，只需要在命令行中輸入： startNetworkServer.bat 數(shù)據(jù)庫(kù)就啟動(dòng)了,，啟動(dòng)成功會(huì)在控制臺(tái)輸出如下信息： D:db>startNetworkServer Fri Nov 16 10:38:55 CST 2012 : 已使用基本服務(wù)器安全策略安裝了安全管理程序,。 Fri Nov 16 10:38:58 CST 2012 : Apache Derby 網(wǎng)絡(luò)服務(wù)器 － 10.8.1.2 - (1095077) 已啟動(dòng)并準(zhǔn)備接受端口 1527 上的連接 在另一個(gè)控制臺(tái)使用ij命令訪問(wèn)Derby數(shù)據(jù)庫(kù)服務(wù)端,，在輸入ij.bat啟動(dòng)ij工具后,，通過(guò)如下命令建立與服務(wù)端的連接， 并創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)： connect "jdbc:derby://127.0.0.1 :1527/seconddb;create=true"; 參數(shù)中的數(shù)據(jù)庫(kù)命部分和內(nèi)嵌模式不同,，這里使用了“//127.0.0.1:1527/”,，訪問(wèn)網(wǎng)絡(luò)模式的URL需要 指定服務(wù)器的IP地址和端口，其它的就和內(nèi)嵌模式一樣了,。 例: D:db>ij ij 版本 10.8 ij> connect "jdbc:derby://127.0.0.1:1527/seconddb;create=true"; ij> create table secondtable(id int primary key,name varchar(20)); 已插入／更新／刪除 0 行 ij> insert into secondtable values(1,"hot"); 已插入／更新／刪除 1 行 ij> select * from secondtable; ID |NAME -------------------------------- 1 |hot 已選擇 1 行 ij> exit; D:db> 四,、 在Java應(yīng)用程序中訪問(wèn)網(wǎng)絡(luò)模式Derby數(shù)據(jù)庫(kù) 1:簡(jiǎn)單的服務(wù)端程序，在程序中啟動(dòng)數(shù)據(jù)庫(kù)服務(wù) import java.io.BufferedReader; import java.io.InputStreamReader; import java.io.PrintWriter; import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; import org.apache.derby.drda.NetworkServerControl; public class TestDerbyServer { public static void main(String[] args) { try { NetworkServerControl dbserver = new NetworkServerControl();//啟動(dòng)服務(wù)器 PrintWriter pw = new PrintWriter(System.out);//獲取服務(wù)器輸出 dbserver.start(pw); Connection conn = DriverManager.getConnection("jdbc:derby:TESTDB;create=true");//本地連接數(shù)據(jù)庫(kù) Statement st = conn.createStatement(); st.execute("create table USER_INFO (ID INT NOT NULL,NAME VARCHAR(10) NOT NULL)");//建表 st.executeUpdate("insert into USER_INFO(ID,NAME) values (1,"hermit")");//插入數(shù)據(jù) st.executeUpdate("insert into USER_INFO(ID,NAME) values (2,"test")");//插入數(shù)據(jù) /* *等待用戶輸入，讓程序繼續(xù)運(yùn)行,，不然程序會(huì)運(yùn)行結(jié)束,，客戶端就連不上了 */ BufferedReader in = new BufferedReader(new InputStreamReader(System.in)); System.out.println("Press [Enter] to stop Server"); in.readLine(); } catch (Exception ex) { ex.printStackTrace(); } } } (2)jdk1.7自帶的網(wǎng)絡(luò)服務(wù)端程序(在程序中啟動(dòng)數(shù)據(jù)庫(kù)服務(wù)) import java.sql.*; import javax.sql.DataSource; import org.apache.derby.drda.NetworkServerControl; import java.util.Properties; import java.io.BufferedReader; import java.io.InputStreamReader; public class SimpleNetworkServerSample{ private static String DBNAME="cwbDB";//測(cè)試用數(shù)據(jù)庫(kù)名 public static void main (String[] args) throws Exception{ Connection embeddedConn = null; try{ startNetworkServer();//啟動(dòng)網(wǎng)絡(luò)服務(wù) }catch (Exception e){ System.out.println("Failed to start NetworkServer: " + e); System.exit(1); } try{ embeddedConn = getEmbeddedConnection(DBNAME,"create=true;");//創(chuàng)建數(shù)據(jù)庫(kù)并連接 System.out.println("Got an embedded connection."); System.out.println("Testing embedded connection by executing a sample query "); test(embeddedConn);//本地測(cè)試 String howToConnect = ijUsage(); System.out.println(howToConnect); waitForExit();//等待客戶連接 }catch (SQLException sqle){ System.out.println("Failure making connection: " + sqle); sqle.printStackTrace(); } finally{ if(embeddedConn != null) embeddedConn.close();//關(guān)閉連接 try{ // 關(guān)閉數(shù)據(jù)庫(kù)服務(wù) DriverManager.getConnection("jdbc:derby:;shutdown=true"); }catch(SQLException se){ //ignore se } } } //啟動(dòng)網(wǎng)絡(luò)服務(wù) public static void startNetworkServer() throws Exception{ startWithProperty(); waitForStart(); } private static void startWithProperty() throws Exception{ System.out.println("Starting Network Server"); System.setProperty("derby.drda.startNetworkServer","true"); Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance(); } private static void waitForStart() throws Exception{//等待服務(wù)器啟動(dòng) org.apache.derby.drda.NetworkServerControl server = null; // Use NetworkServerControl.ping() to wait for server = new NetworkServerControl(); System.out.println("Testing if Network Server is up and running!"); for (int i = 0; i < 10 ; i ++){ try { Thread.currentThread().sleep(5000); server.ping(); }catch (Exception e){ System.out.println("Try #" + i + " " +e.toString()); if (i == 9 ){ System.out.println("放棄對(duì)服務(wù)器的連接!"); throw e; } } } System.out.println("Derby 網(wǎng)絡(luò)服務(wù)器已運(yùn)行"); } //獲取數(shù)據(jù)庫(kù)連接 public static Connection getEmbeddedConnection(String database,String attributes) throws Exception{ String dbUrl = "jdbc:derby:"+database +";"+attributes; Connection conn = DriverManager.getConnection(dbUrl); return conn; } public static void test(Connection conn)throws Exception{ Statement stmt = null; ResultSet rs = null; try{ stmt = conn.createStatement(); rs = stmt.executeQuery("select count(*) from sys.systables"); while(rs.next()) System.out.println("number of rows in sys.systables = "+ rs.getInt(1)); }catch(SQLException sqle){ System.out.println("SQLException when querying on the database connection; "+ sqle); throw sqle; } finally{ if(rs != null) rs.close(); if(stmt != null) stmt.close(); } } private static void waitForExit() throws Exception{ System.out.println("Clients can continue to connect: "); BufferedReader in = new BufferedReader(new InputStreamReader(System.in)); System.out.println("Press [Enter] to stop Server"); in.readLine(); } private static String ijUsage(){//命令行啟動(dòng)ij連接數(shù)據(jù)庫(kù)的方法 String ijUsage = " While my app is busy with embedded work, "; ijUsage += "ij might connect like this: "; ijUsage += " $ java -Dij.user=me -Dij.password=pw -Dij.protocol= jdbc:derby://localhost:1527/ org.apache.derby.tools.ij "; ijUsage += " ij> connect "" + DBNAME + ""; "; return ijUsage; } } [/code] (3)上面網(wǎng)絡(luò)服務(wù)端程序運(yùn)行過(guò)程,打開(kāi)一DOS窗口(當(dāng)前工作目錄d:db) D:db>jar.bat(運(yùn)行這個(gè)批處理,內(nèi)容如下) D:db>set DERBY_HOME=c:jdk1.7db D:db>set path=c:jdk1.7dbin;c:jdk1.7in;C:WINDOWSsystem32;C:WINDOWS;C:WINDOWSSystem32Wbem D:db>setNetworkServerCP.bat(運(yùn)行這個(gè)批處理內(nèi)容如下) D:db>SET DERBY_INSTALL=c:jdk1.7db D:db>set CLASSPATH=c:jdk1.7dblibderbynet.jar;c:jdk1.7dblibderbytools.jar;.;c:jdk1.7lib D:db>javac SimpleNetworkServerSample.java(編譯) D:db>java SimpleNetworkServerSample(運(yùn)行網(wǎng)絡(luò)數(shù)據(jù)庫(kù)服務(wù)器) Starting Network Server Testing if Network Server is up and running! Derby 網(wǎng)絡(luò)服務(wù)器已運(yùn)行 Got an embedded connection. Testing embedded connection by executing a sample query number of rows in sys.systables = 22 While my app is busy with embedded work, ij might connect like this: $ java -Dij.user=me -Dij.password=pw -Dij.protocol=jdbc:derby://localhost:1527/ org.apache.derby.tools.ij ij> connect "cwbDB"; Clients can continue to connect(等待客戶端連接): Press [Enter] to stop Server (4)jdk1.7自帶的網(wǎng)絡(luò)客戶端程序 網(wǎng)絡(luò)模式和內(nèi)嵌模式的不同出在于： A． 數(shù)據(jù)庫(kù)連接URL的不同； B． 應(yīng)用程序退出時(shí)無(wú)需關(guān)閉Derby數(shù)據(jù)庫(kù),； C． 數(shù)據(jù)庫(kù)驅(qū)動(dòng)的不同,； String driver = “org.apache.derby.jdbc.ClientDriver”; String url =“jdbc:derby: //localhost:1527/cwbdb;create=true”; Connection conn; try { Class.forName(driver); conn = DriverManager.getConnection(url); }catch(Exception e) { …… } import java.lang.reflect.InvocationTargetException; import java.lang.reflect.Method; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; import javax.sql.DataSource; public class SimpleNetworkClientSample{ private static String DBNAME="cwbdb";//要連接的數(shù)據(jù)庫(kù)名 private static int NETWORKSERVER_PORT=1527;//服務(wù)器端口 /** * Derby Network Client Driver class names */ public static final String DERBY_CLIENT_DRIVER = "org.apache.derby.jdbc.ClientDriver"; private static final String DERBY_CLIENT_DS = "org.apache.derby.jdbc.ClientDataSource"; private static final String DERBY_CLIENT_URL= "jdbc:derby://localhost:"+ NETWORKSERVER_PORT+"/"+DBNAME+";create=true"; String url = DERBY_CLIENT_URL; String jdbcDriver = DERBY_CLIENT_DRIVER; String jdbcDataSource = DERBY_CLIENT_DS; public static void main (String[] args) throws Exception{ new SimpleNetworkClientSample().startSample(args); } public void startSample (String[] args) throws Exception{ DataSource clientDataSource = null; Connection clientConn1 = null; Connection clientConn2 = null; try{ System.out.println("Starting Sample client program "); // load the appropriate JDBC Driver loadDriver(); // 使用DriverManager取得數(shù)據(jù)庫(kù)連接 clientConn1 = getClientDriverManagerConnection(); System.out.println("通過(guò)DriverManager獲取連接."); // 創(chuàng)建數(shù)據(jù)源 javax.sql.DataSource myDataSource = getClientDataSource(DBNAME, null, null); // 使用數(shù)據(jù)源取得一個(gè)連接 clientConn2 = getClientDataSourceConn(myDataSource); System.out.println("通過(guò)數(shù)據(jù)源獲取一個(gè)連接"); // 測(cè)試連接 System.out.println("通過(guò)DriverManager獲取連接，執(zhí)行簡(jiǎn)單查詢測(cè)試連接"); test(clientConn1); System.out.println("通過(guò)數(shù)據(jù)源獲取一個(gè)連接,，執(zhí)行簡(jiǎn)單查詢測(cè)試連接"); test(clientConn2); System.out.println("Goodbye!"); }catch (SQLException sqle){ System.out.println("Failure making connection: " + sqle); sqle.printStackTrace(); } finally{ if(clientConn1 != null) clientConn1.close(); if(clientConn2 != null) clientConn2.close(); } } public Connection getClientDataSourceConn(javax.sql.DataSource ds) throws Exception{ Connection conn = ds.getConnection("usr2", "pass2"); System.out.print("connection from datasource; getDriverName = "); System.out.println(conn.getMetaData().getDriverName()); return conn; } public javax.sql.DataSource getClientDataSource(String database, String user, String password) throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException, NoSuchMethodException, InvocationTargetException{ Class nsDataSource = Class.forName(jdbcDataSource); DataSource ds = (DataSource) nsDataSource.newInstance(); Class[] methodParams = new Class[] {String.class}; Method dbname = nsDataSource.getMethod("setDatabaseName", methodParams); Object[] args = new Object[] {database}; dbname.invoke(ds, args); if (user != null) { Method setuser = nsDataSource.getMethod("setUser", methodParams); args = new Object[] {user}; setuser.invoke(ds, args); } if (password != null) { Method setpw = nsDataSource.getMethod("setPassword", methodParams); args = new Object[] {password}; setpw.invoke(ds, args); } Method servername = nsDataSource.getMethod("setServerName", methodParams); args = new Object[] {"localhost"}; servername.invoke(ds, args); methodParams = new Class[] {int.class}; Method portnumber = nsDataSource.getMethod("setPortNumber", methodParams); args = new Object[] {new Integer(1527)}; portnumber.invoke(ds, args); return ds; } public void loadDriver() throws Exception{ // Load the Driver Class.forName(jdbcDriver).newInstance(); } public Connection getClientDriverManagerConnection() throws Exception { Properties properties = new java.util.Properties(); properties.setProperty("user","derbyuser"); properties.setProperty("password","pass"); Connection conn = DriverManager.getConnection(url,properties); return conn; } //測(cè)試連接 public void test(Connection conn) throws Exception{ Statement stmt = null; ResultSet rs = null; try { stmt = conn.createStatement(); rs = stmt.executeQuery("select count(*) from sys.systables"); while(rs.next()) System.out.println("number of rows in sys.systables = "+ rs.getInt(1)); } catch(SQLException sqle) { System.out.println("SQLException when querying on the database connection; "+ sqle); throw sqle; } finally { if(rs != null) rs.close(); if(stmt != null) stmt.close(); } } }[/code] 由于網(wǎng)絡(luò)模式下,，Derby數(shù)據(jù)庫(kù)做為一個(gè)獨(dú)立運(yùn)行的數(shù)據(jù)庫(kù)，可以被多個(gè)應(yīng)用程序所訪問(wèn),， 所以應(yīng)用程序在運(yùn)行結(jié)束時(shí)不應(yīng)該關(guān)閉Derby數(shù)據(jù)庫(kù),。 (5)運(yùn)行網(wǎng)絡(luò)客戶端過(guò)程(打開(kāi)另一DOS窗口,當(dāng)前工作目錄d:db) D:db>jar.bat(運(yùn)行這個(gè)批處理內(nèi)容如下) D:db>set DERBY_HOME=c:jdk1.7db D:db>set path=c:jdk1.7dbin;c:jdk1.7in;C:WINDOWSsystem32;C:WINDOWS;C:WINDOWSSystem32Wbem D:db>setNetworkClientCP.bat(運(yùn)行這個(gè)批處理內(nèi)容如下) D:db>SET DERBY_HOME=c:jdk1.7db D:db>set CLASSPATH=c:jdk1.7dblibderbyclient.jar;c:jdk1.7dblibderbytools.jar;.;c:jdk1.7lib D:db>javac SimpleNetworkClientSample.java(編譯) 注: SimpleNetworkClientSample.java使用了未經(jīng)檢查或不安全的操作。 注: 有關(guān)詳細(xì)信息, 請(qǐng)使用 -Xlint:unchecked 重新編譯,。 D:db>java SimpleNetworkClientSample(運(yùn)行網(wǎng)絡(luò)客戶端) Starting Sample client program 通過(guò)DriverManager獲取連接. connection from datasource; getDriverName = Apache Derby Network Client JDBC Driver 通過(guò)數(shù)據(jù)源獲取一個(gè)連接 通過(guò)DriverManager獲取連接,，執(zhí)行簡(jiǎn)單查詢測(cè)試連接 number of rows in sys.systables = 22 通過(guò)數(shù)據(jù)源獲取一個(gè)連接，執(zhí)行簡(jiǎn)單查詢測(cè)試連接 number of rows in sys.systables = 22 Goodbye! (全文完) 源碼下載：http://file./2014/10/11/020409578.zip |
|
|
