ij中java连接数据库_[JDBC学习]学习使用jdk1.7中内置数据库Derby(三)

继续上文：

学习使用jdk1.7中内置数据库Derby(二)

三) 运行网络模式的Derby数据库

这种模式下，需要使用两个控制台窗口，一个用于启动Derby数据库服务端，另一个做为访问Derby数据库的客户端。

可以通过DERBY_HOMEin目录下的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>

四、 在Java应用程序中访问网络模式Derby数据库

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 = "

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)上面网络服务端程序运行过程,打开一DOS窗口(当前工作目录d:db)

D:db>jar.bat(运行这个批处理,内容如下)

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(运行这个批处理内容如下)

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(运行网络数据库服务器)

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();

}

}

}[/code]

由于网络模式下，Derby数据库做为一个独立运行的数据库，可以被多个应用程序所访问，

所以应用程序在运行结束时不应该关闭Derby数据库。

(5)运行网络客户端过程(打开另一DOS窗口,当前工作目录d:db)

D:db>jar.bat(运行这个批处理内容如下)

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(运行这个批处理内容如下)

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使用了未经检查或不安全的操作。

注: 有关详细信息, 请使用 -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!

(全文完)