Derby 数据库先容(2)--利用

本文主要先容 Derby 的根本利用(简介可参考:Derby 数据库先容(1)--简介)，文中所利用到的软件版本：Java 1.8.0_341、Derby 10.14.2.0。
1、嵌入模式

直接利用 JDBC 连接数据库，可创建和启动数据库。
1.1、内存数据库

1. @Test
2. public void embeddedMemory() throws SQLException {
3.     String user = "admin";
4.     Connection con = JdbcUtil.getConnection("org.apache.derby.jdbc.EmbeddedDriver", "jdbc:derby:memory:myDb;create=true", user, "");
5.     business(con, user);
6.     con.close();
7. }
9. private void business(Connection con, String user) throws SQLException {
10.     String tableName = "a_student";
11.     Statement st = con.createStatement();
12.     String sql = "select 1 from sys.sysTABLES a,sys.sysschemas b where a.schemaid=b.schemaid and upper(b.schemaname)=? and upper(a.tablename)=?";
13.     PreparedStatement pst = con.prepareStatement(sql);
14.     pst.setString(1, user.toUpperCase());
15.     pst.setString(2, tableName.toUpperCase());
16.     ResultSet rs = pst.executeQuery();
17.     if (!rs.next()) {//表不存在则创建并初始化数据，这里根据业务需要进行操作
18.         st.executeUpdate("create table " + tableName + "(id int, name varchar(32))");
19.         st.executeUpdate("insert into " + tableName + "(id,name) values (1,'李白')");
20.         st.executeUpdate("insert into " + tableName + "(id,name) values (2,'杜甫')");
21.     }
23.     rs = st.executeQuery("select * from " + tableName);
24.     while (rs.next()) {
25.         log.info("id={},name={}", rs.getInt("id"), rs.getString("name"));
26.     }
27. }

复制代码

1.2、文件数据库

1. @Test
2. public void embeddedFile() throws SQLException {
3.     String user = "admin";
4.     Connection con = JdbcUtil.getConnection("org.apache.derby.jdbc.EmbeddedDriver", "jdbc:derby:d:/temp/myDb;create=true", user, "");
5.     business(con, user);
6.     con.close();
7. }

复制代码

1.3、资源数据库

1. @Test
2. public void embeddedJar() throws SQLException {
3.     String user = "admin";
4.     Connection con = JdbcUtil.getConnection("org.apache.derby.jdbc.EmbeddedDriver", "jdbc:derby:classpath:myDb", user, "");
5.     business(con, user);
6.     con.close();
7. }

复制代码

jar 包不在 classpath 之中：

1. @Test
2. public void embeddedJar2() throws SQLException {
3.     String user = "admin";
4.     Connection con = JdbcUtil.getConnection("org.apache.derby.jdbc.EmbeddedDriver", "jdbc:derby:jar:(d:/temp/my.jar)myDb", user, "");
5.     business(con, user);
6.     con.close();
7. }

复制代码

2、服务器模式

在服务端启动 Derby 数据库服务：

1. cd ${DERBY_HOME}/bin
2. NetworkServerControl.bat start -p 1527 -noSecurityManager

复制代码

客户端通过 JDBC 访问 Derby 服务。
2.1、内存数据库

1. @Test
2. public void networkMemory() throws SQLException {
3.     String user = "admin";
4.     Connection con = JdbcUtil.getConnection("org.apache.derby.jdbc.ClientDriver", "jdbc:derby://localhost:1527/memory:myDb;create=true", user, "123456");
5.     business(con, user);
6.     con.close();
7. }

复制代码

2.2、文件数据库

1. @Test
2. public void networkFile() throws SQLException {
3.     String user = "admin";
4.     Connection con = JdbcUtil.getConnection("org.apache.derby.jdbc.ClientDriver", "jdbc:derby://localhost:1527/d:/temp/myDb;create=true", user, "123456");
5.     business(con, user);
6.     con.close();
7. }

复制代码

2.3、资源数据库

1. @Test
2. public void networkJar() throws SQLException {
3.     String user = "admin";
4.     Connection con = JdbcUtil.getConnection("org.apache.derby.jdbc.ClientDriver", "jdbc:derby://localhost:1527/classpath:myDb", user, "123");
5.     business(con, user);
6.     con.close();
7. }

复制代码

jar 包不在 classpath 之中：

1. @Test
2. public void networkJar2() throws SQLException {
3.     String user = "admin";
4.     Connection con = JdbcUtil.getConnection("org.apache.derby.jdbc.ClientDriver", "jdbc:derby://localhost:1527/jar:(d:/temp/my.jar)myDb", user, "123");
5.     business(con, user);
6.     con.close();
7. }

复制代码

3、混合模式

应用通过代码的方式启动数据库服务，应用内访问数据库可以利用进程模式，其他应用通过服务器模式访问。

1. @Test
2. public void networkServerControl() throws Exception {
3.     NetworkServerControl server = new NetworkServerControl(InetAddress.getByName("0.0.0.0"), 1527);
4.     server.start (null);
6.     CountDownLatch countDownLatch = new CountDownLatch(1);
7.     new Thread(() -> {
8.         try {
9.             //模拟其他应用访问
10.             networkMemory();
11.         } catch (Exception e) {
12.             e.printStackTrace();
13.         }
14.         countDownLatch.countDown();
15.     }).start();
17.     countDownLatch.await();
18.     server.shutdown();
19. }

复制代码

 
 
完整代码：

1. package com.abc.demo.db;
3. import lombok.extern.slf4j.Slf4j;
4. import org.apache.derby.drda.NetworkServerControl;
5. import org.junit.Test;
7. import java.net.InetAddress;
8. import java.sql.*;
9. import java.util.concurrent.CountDownLatch;
11. @Slf4j
12. public class DerbyCae {
13.     @Test
14.     public void embeddedMemory() throws SQLException {
15.         String user = "admin";
16.         Connection con = JdbcUtil.getConnection("org.apache.derby.jdbc.EmbeddedDriver", "jdbc:derby:memory:myDb;create=true", user, "");
17.         business(con, user);
18.         con.close();
19.     }
21.     @Test
22.     public void embeddedFile() throws SQLException {
23.         String user = "admin";
24.         Connection con = JdbcUtil.getConnection("org.apache.derby.jdbc.EmbeddedDriver", "jdbc:derby:d:/temp/myDb;create=true", user, "");
25.         business(con, user);
26.         con.close();
27.     }
29.     @Test
30.     public void embeddedJar() throws SQLException {
31.         String user = "admin";
32.         Connection con = JdbcUtil.getConnection("org.apache.derby.jdbc.EmbeddedDriver", "jdbc:derby:classpath:myDb", user, "");
33.         business(con, user);
34.         con.close();
35.     }
37.     @Test
38.     public void embeddedJar2() throws SQLException {
39.         String user = "admin";
40.         Connection con = JdbcUtil.getConnection("org.apache.derby.jdbc.EmbeddedDriver", "jdbc:derby:jar:(d:/temp/my.jar)myDb", user, "");
41.         business(con, user);
42.         con.close();
43.     }
45.     @Test
46.     public void networkMemory() throws SQLException {
47.         String user = "admin";
48.         Connection con = JdbcUtil.getConnection("org.apache.derby.jdbc.ClientDriver", "jdbc:derby://localhost:1527/memory:myDb;create=true", user, "123456");
49.         business(con, user);
50.         con.close();
51.     }
53.     @Test
54.     public void networkFile() throws SQLException {
55.         String user = "admin";
56.         Connection con = JdbcUtil.getConnection("org.apache.derby.jdbc.ClientDriver", "jdbc:derby://localhost:1527/d:/temp/myDb;create=true", user, "123456");
57.         business(con, user);
58.         con.close();
59.     }
61.     @Test
62.     public void networkJar() throws SQLException {
63.         String user = "admin";
64.         Connection con = JdbcUtil.getConnection("org.apache.derby.jdbc.ClientDriver", "jdbc:derby://localhost:1527/classpath:myDb", user, "123");
65.         business(con, user);
66.         con.close();
67.     }
69.     @Test
70.     public void networkJar2() throws SQLException {
71.         String user = "admin";
72.         Connection con = JdbcUtil.getConnection("org.apache.derby.jdbc.ClientDriver", "jdbc:derby://localhost:1527/jar:(d:/temp/my.jar)myDb", user, "123");
73.         business(con, user);
74.         con.close();
75.     }
77.     @Test
78.     public void networkServerControl() throws Exception {
79.         NetworkServerControl server = new NetworkServerControl(InetAddress.getByName("0.0.0.0"), 1527);
80.         server.start (null);
82.         CountDownLatch countDownLatch = new CountDownLatch(1);
83.         new Thread(() -> {
84.             try {
85.                 //模拟其他应用访问
86.                 networkMemory();
87.             } catch (Exception e) {
88.                 e.printStackTrace();
89.             }
90.             countDownLatch.countDown();
91.         }).start();
93.         countDownLatch.await();
94.         server.shutdown();
95.     }
97.     private void business(Connection con, String user) throws SQLException {
98.         String tableName = "a_student";
99.         Statement st = con.createStatement();
100.         String sql = "select 1 from sys.sysTABLES a,sys.sysschemas b where a.schemaid=b.schemaid and upper(b.schemaname)=? and upper(a.tablename)=?";
101.         PreparedStatement pst = con.prepareStatement(sql);
102.         pst.setString(1, user.toUpperCase());
103.         pst.setString(2, tableName.toUpperCase());
104.         ResultSet rs = pst.executeQuery();
105.         if (!rs.next()) {//表不存在则创建并初始化数据，这里根据业务需要进行操作
106.             st.executeUpdate("create table " + tableName + "(id int, name varchar(32))");
107.             st.executeUpdate("insert into " + tableName + "(id,name) values (1,'李白')");
108.             st.executeUpdate("insert into " + tableName + "(id,name) values (2,'杜甫')");
109.         }
111.         rs = st.executeQuery("select * from " + tableName);
112.         while (rs.next()) {
113.             log.info("id={},name={}", rs.getInt("id"), rs.getString("name"));
114.         }
115.     }
116. }

复制代码

DerbyCae.java

1. package com.abc.demo.db;
3. import lombok.extern.slf4j.Slf4j;
5. import java.sql.*;
8. @Slf4j
9. public class JdbcUtil {
10.     private JdbcUtil() {}
12.     public static Connection getConnection(String driver, String url, String username, String password) {
13.         Connection con = null;
14.         try {
15.             Class.forName(driver);
16.             con = DriverManager.getConnection(url, username, password);
17.         } catch (ClassNotFoundException | SQLException e) {
18.             log.warn("url={},username={},password={}", url, username, password);
19.             e.printStackTrace();
20.         }
21.         return con;
22.     }
23. }

复制代码

JdbcUtil.java 

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。