继续上文:学习使用jdk1.7中内置数据库Derby(二)[url]http://128kj.iteye.com/blog/1727385[/url]
[color=blue]三) 运行网络模式的Derby数据库[/color]
这种模式下,需要使用两个控制台窗口,一个用于启动Derby数据库服务端,另一个做为访问Derby数据库的客户端。
可以通过DERBY_HOME\bin目录下的startNetworkServer.bat来启动Derby数据库服务端,只需要在命令行中输入:
startNetworkServer.bat
数据库就启动了,启动成功会在控制台输出如下信息:
D:\db>startNetworkServer
Fri Nov 16 10:38:55 CST 2012 : 已使用基本服务器安全策略安装了安全管理程序。
Fri Nov 16 10:38:58 CST 2012 : Apache Derby 网络服务器 - 10.8.1.2 - (1095077)
已启动并准备接受端口 1527 上的连接
在另一个控制台使用ij命令访问Derby数据库服务端,在输入ij.bat启动ij工具后,通过如下命令建立与服务端的连接,并创建一个数据库:
connect 'jdbc:derby://127.0.0.1 :1527/seconddb;create=true';
参数中的数据库命部分和内嵌模式不同,这里使用了“//127.0.0.1:1527/”,访问网络模式的URL需要指定服务器的IP地址和端口,其它的就和内嵌模式一样了。
例:
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>
[color=blue]
四、 在Java应用程序中访问网络模式Derby数据库[/color]
(3)上面网络服务端程序运行过程,打开一DOS窗口(当前工作目录d:\db)
D:\db>jar.bat(运行这个批处理,内容如下)
D:\db>set DERBY_HOME=c:\jdk1.7\db
D:\db>set path=c:\jdk1.7\db\bin;c:\jdk1.7\bin;C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem
D:\db>setNetworkServerCP.bat(运行这个批处理内容如下)
D:\db>SET DERBY_INSTALL=c:\jdk1.7\db
D:\db>set CLASSPATH=c:\jdk1.7\db\lib\derbynet.jar;c:\jdk1.7\db\lib\derbytools.jar;.;c:\jdk1.7\lib
D:\db>javac SimpleNetworkServerSample.java(编译)
D:\db>java SimpleNetworkServerSample(运行网络数据库服务器)
Starting Network Server
Testing if Network Server is up and running!
Derby 网络服务器已运行
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自带的网络客户端程序
网络模式和内嵌模式的不同出在于:
A. 数据库连接URL的不同;
B. 应用程序退出时无需关闭Derby数据库;
C. 数据库驱动的不同;
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) {
……
}
由于网络模式下,Derby数据库做为一个独立运行的数据库,可以被多个应用程序所访问,
所以应用程序在运行结束时不应该关闭Derby数据库。
(5)运行网络客户端过程(打开另一DOS窗口,当前工作目录d:\db)
D:\db>jar.bat(运行这个批处理内容如下)
D:\db>set DERBY_HOME=c:\jdk1.7\db
D:\db>set path=c:\jdk1.7\db\bin;c:\jdk1.7\bin;C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem
D:\db>setNetworkClientCP.bat(运行这个批处理内容如下)
D:\db>SET DERBY_HOME=c:\jdk1.7\db
D:\db>set CLASSPATH=c:\jdk1.7\db\lib\derbyclient.jar;c:\jdk1.7\db\lib\derbytools.jar;.;c:\jdk1.7\lib
D:\db>javac SimpleNetworkClientSample.java(编译)
注: SimpleNetworkClientSample.java使用了未经检查或不安全的操作。
注: 有关详细信息, 请使用 -Xlint:unchecked 重新编译。
D:\db>java SimpleNetworkClientSample(运行网络客户端)
Starting Sample client program
通过DriverManager获取连接.
connection from datasource; getDriverName = Apache Derby Network Client JDBC Driver
通过数据源获取一个连接
通过DriverManager获取连接,执行简单查询测试连接
number of rows in sys.systables = 22
通过数据源获取一个连接,执行简单查询测试连接
number of rows in sys.systables = 22
Goodbye!
(全文完)
[color=blue]三) 运行网络模式的Derby数据库[/color]
这种模式下,需要使用两个控制台窗口,一个用于启动Derby数据库服务端,另一个做为访问Derby数据库的客户端。
可以通过DERBY_HOME\bin目录下的startNetworkServer.bat来启动Derby数据库服务端,只需要在命令行中输入:
startNetworkServer.bat
数据库就启动了,启动成功会在控制台输出如下信息:
D:\db>startNetworkServer
Fri Nov 16 10:38:55 CST 2012 : 已使用基本服务器安全策略安装了安全管理程序。
Fri Nov 16 10:38:58 CST 2012 : Apache Derby 网络服务器 - 10.8.1.2 - (1095077)
已启动并准备接受端口 1527 上的连接
在另一个控制台使用ij命令访问Derby数据库服务端,在输入ij.bat启动ij工具后,通过如下命令建立与服务端的连接,并创建一个数据库:
connect 'jdbc:derby://127.0.0.1 :1527/seconddb;create=true';
参数中的数据库命部分和内嵌模式不同,这里使用了“//127.0.0.1:1527/”,访问网络模式的URL需要指定服务器的IP地址和端口,其它的就和内嵌模式一样了。
例:
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>
[color=blue]
四、 在Java应用程序中访问网络模式Derby数据库[/color]
1:简单的服务端程序,在程序中启动数据库服务
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();//启动服务器
PrintWriter pw = new PrintWriter(System.out);//获取服务器输出
dbserver.start(pw);
Connection conn = DriverManager.getConnection("jdbc:derby:TESTDB;create=true");//本地连接数据库
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')");//插入数据
st.executeUpdate("insert into USER_INFO(ID,NAME) values (2,'test')");//插入数据
/*
*等待用户输入,让程序继续运行,不然程序会运行结束,客户端就连不上了
*/
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自带的网络服务端程序(在程序中启动数据库服务)
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";//测试用数据库名
public static void main (String[] args) throws Exception{
Connection embeddedConn = null;
try{
startNetworkServer();//启动网络服务
}catch (Exception e){
System.out.println("Failed to start NetworkServer: " + e);
System.exit(1);
}
try{
embeddedConn = getEmbeddedConnection(DBNAME,"create=true;");//创建数据库并连接
System.out.println("Got an embedded connection.");
System.out.println("Testing embedded connection by executing a sample query ");
test(embeddedConn);//本地测试
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();//关闭连接
try{
// 关闭数据库服务
DriverManager.getConnection("jdbc:derby:;shutdown=true");
}catch(SQLException se){
//ignore se
}
}
}
//启动网络服务
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{//等待服务器启动
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("放弃对服务器的连接!");
throw e;
}
}
}
System.out.println("Derby 网络服务器已运行");
}
//获取数据库连接
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(){//命令行启动ij连接数据库的方法
String ijUsage = "\nWhile my app is busy with embedded work, ";
ijUsage += "ij might connect like this:\n\n";
ijUsage += "\t$ java -Dij.user=me -Dij.password=pw -Dij.protocol=
jdbc:derby://localhost:1527/ org.apache.derby.tools.ij\n";
ijUsage += "\tij> connect '" + DBNAME + "';\n\n";
return ijUsage;
}
}
(3)上面网络服务端程序运行过程,打开一DOS窗口(当前工作目录d:\db)
D:\db>jar.bat(运行这个批处理,内容如下)
D:\db>set DERBY_HOME=c:\jdk1.7\db
D:\db>set path=c:\jdk1.7\db\bin;c:\jdk1.7\bin;C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem
D:\db>setNetworkServerCP.bat(运行这个批处理内容如下)
D:\db>SET DERBY_INSTALL=c:\jdk1.7\db
D:\db>set CLASSPATH=c:\jdk1.7\db\lib\derbynet.jar;c:\jdk1.7\db\lib\derbytools.jar;.;c:\jdk1.7\lib
D:\db>javac SimpleNetworkServerSample.java(编译)
D:\db>java SimpleNetworkServerSample(运行网络数据库服务器)
Starting Network Server
Testing if Network Server is up and running!
Derby 网络服务器已运行
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自带的网络客户端程序
网络模式和内嵌模式的不同出在于:
A. 数据库连接URL的不同;
B. 应用程序退出时无需关闭Derby数据库;
C. 数据库驱动的不同;
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";//要连接的数据库名
private static int NETWORKSERVER_PORT=1527;//服务器端口
/**
* 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取得数据库连接
clientConn1 = getClientDriverManagerConnection();
System.out.println("通过DriverManager获取连接.");
// 创建数据源
javax.sql.DataSource myDataSource = getClientDataSource(DBNAME, null, null);
// 使用数据源取得一个连接
clientConn2 = getClientDataSourceConn(myDataSource);
System.out.println("通过数据源获取一个连接");
// 测试连接
System.out.println("通过DriverManager获取连接,执行简单查询测试连接");
test(clientConn1);
System.out.println("通过数据源获取一个连接,执行简单查询测试连接");
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;
}
//测试连接
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();
}
}
}
由于网络模式下,Derby数据库做为一个独立运行的数据库,可以被多个应用程序所访问,
所以应用程序在运行结束时不应该关闭Derby数据库。
(5)运行网络客户端过程(打开另一DOS窗口,当前工作目录d:\db)
D:\db>jar.bat(运行这个批处理内容如下)
D:\db>set DERBY_HOME=c:\jdk1.7\db
D:\db>set path=c:\jdk1.7\db\bin;c:\jdk1.7\bin;C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem
D:\db>setNetworkClientCP.bat(运行这个批处理内容如下)
D:\db>SET DERBY_HOME=c:\jdk1.7\db
D:\db>set CLASSPATH=c:\jdk1.7\db\lib\derbyclient.jar;c:\jdk1.7\db\lib\derbytools.jar;.;c:\jdk1.7\lib
D:\db>javac SimpleNetworkClientSample.java(编译)
注: SimpleNetworkClientSample.java使用了未经检查或不安全的操作。
注: 有关详细信息, 请使用 -Xlint:unchecked 重新编译。
D:\db>java SimpleNetworkClientSample(运行网络客户端)
Starting Sample client program
通过DriverManager获取连接.
connection from datasource; getDriverName = Apache Derby Network Client JDBC Driver
通过数据源获取一个连接
通过DriverManager获取连接,执行简单查询测试连接
number of rows in sys.systables = 22
通过数据源获取一个连接,执行简单查询测试连接
number of rows in sys.systables = 22
Goodbye!
(全文完)