Derby 数据库先容(2)--利用
本文主要先容 Derby 的根本利用(简介可参考:Derby 数据库先容(1)--简介)，文中所利用到的软件版本：Java 1.8.0_341、Derby 10.14.2.0。1、嵌入模式
直接利用 JDBC 连接数据库，可创建和启动数据库。
1.1、内存数据库
@Test
public void embeddedMemory() throws SQLException {
String user = "admin";
Connection con = JdbcUtil.getConnection("org.apache.derby.jdbc.EmbeddedDriver", "jdbc:derby:memory:myDb;create=true", user, "");
business(con, user);
con.close();
}
private void business(Connection con, String user) throws SQLException {
String tableName = "a_student";
Statement st = con.createStatement();
String sql = "select 1 from sys.sysTABLES a,sys.sysschemas b where a.schemaid=b.schemaid and upper(b.schemaname)=? and upper(a.tablename)=?";
PreparedStatement pst = con.prepareStatement(sql);
pst.setString(1, user.toUpperCase());
pst.setString(2, tableName.toUpperCase());
ResultSet rs = pst.executeQuery();
if (!rs.next()) {//表不存在则创建并初始化数据，这里根据业务需要进行操作
st.executeUpdate("create table " + tableName + "(id int, name varchar(32))");
st.executeUpdate("insert into " + tableName + "(id,name) values (1,'李白')");
st.executeUpdate("insert into " + tableName + "(id,name) values (2,'杜甫')");
}
rs = st.executeQuery("select * from " + tableName);
while (rs.next()) {
log.info("id={},name={}", rs.getInt("id"), rs.getString("name"));
}
}1.2、文件数据库
@Test
public void embeddedFile() throws SQLException {
String user = "admin";
Connection con = JdbcUtil.getConnection("org.apache.derby.jdbc.EmbeddedDriver", "jdbc:derby:d:/temp/myDb;create=true", user, "");
business(con, user);
con.close();
}1.3、资源数据库
@Test
public void embeddedJar() throws SQLException {
String user = "admin";
Connection con = JdbcUtil.getConnection("org.apache.derby.jdbc.EmbeddedDriver", "jdbc:derby:classpath:myDb", user, "");
business(con, user);
con.close();
}jar 包不在 classpath 之中：
@Test
public void embeddedJar2() throws SQLException {
String user = "admin";
Connection con = JdbcUtil.getConnection("org.apache.derby.jdbc.EmbeddedDriver", "jdbc:derby:jar:(d:/temp/my.jar)myDb", user, "");
business(con, user);
con.close();
}2、服务器模式
在服务端启动 Derby 数据库服务：
cd ${DERBY_HOME}/bin
NetworkServerControl.bat start -p 1527 -noSecurityManager客户端通过 JDBC 访问 Derby 服务。
2.1、内存数据库
@Test
public void networkMemory() throws SQLException {
String user = "admin";
Connection con = JdbcUtil.getConnection("org.apache.derby.jdbc.ClientDriver", "jdbc:derby://localhost:1527/memory:myDb;create=true", user, "123456");
business(con, user);
con.close();
}2.2、文件数据库
@Test
public void networkFile() throws SQLException {
String user = "admin";
Connection con = JdbcUtil.getConnection("org.apache.derby.jdbc.ClientDriver", "jdbc:derby://localhost:1527/d:/temp/myDb;create=true", user, "123456");
business(con, user);
con.close();
}2.3、资源数据库
@Test
public void networkJar() throws SQLException {
String user = "admin";
Connection con = JdbcUtil.getConnection("org.apache.derby.jdbc.ClientDriver", "jdbc:derby://localhost:1527/classpath:myDb", user, "123");
business(con, user);
con.close();
}jar 包不在 classpath 之中：
@Test
public void networkJar2() throws SQLException {
String user = "admin";
Connection con = JdbcUtil.getConnection("org.apache.derby.jdbc.ClientDriver", "jdbc:derby://localhost:1527/jar:(d:/temp/my.jar)myDb", user, "123");
business(con, user);
con.close();
}3、混合模式
应用通过代码的方式启动数据库服务，应用内访问数据库可以利用进程模式，其他应用通过服务器模式访问。
@Test
public void networkServerControl() throws Exception {
NetworkServerControl server = new NetworkServerControl(InetAddress.getByName("0.0.0.0"), 1527);
server.start (null);
CountDownLatch countDownLatch = new CountDownLatch(1);
new Thread(() -> {
try {
//模拟其他应用访问
networkMemory();
} catch (Exception e) {
e.printStackTrace();
}
countDownLatch.countDown();
}).start();
countDownLatch.await();
server.shutdown();
}
完整代码：
https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gifhttps://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gifpackage com.abc.demo.db;
import lombok.extern.slf4j.Slf4j;
import org.apache.derby.drda.NetworkServerControl;
import org.junit.Test;
import java.net.InetAddress;
import java.sql.*;
import java.util.concurrent.CountDownLatch;
@Slf4j
public class DerbyCae {
@Test
public void embeddedMemory() throws SQLException {
String user = "admin";
Connection con = JdbcUtil.getConnection("org.apache.derby.jdbc.EmbeddedDriver", "jdbc:derby:memory:myDb;create=true", user, "");
business(con, user);
con.close();
}
@Test
public void embeddedFile() throws SQLException {
String user = "admin";
Connection con = JdbcUtil.getConnection("org.apache.derby.jdbc.EmbeddedDriver", "jdbc:derby:d:/temp/myDb;create=true", user, "");
business(con, user);
con.close();
}
@Test
public void embeddedJar() throws SQLException {
String user = "admin";
Connection con = JdbcUtil.getConnection("org.apache.derby.jdbc.EmbeddedDriver", "jdbc:derby:classpath:myDb", user, "");
business(con, user);
con.close();
}
@Test
public void embeddedJar2() throws SQLException {
String user = "admin";
Connection con = JdbcUtil.getConnection("org.apache.derby.jdbc.EmbeddedDriver", "jdbc:derby:jar:(d:/temp/my.jar)myDb", user, "");
business(con, user);
con.close();
}
@Test
public void networkMemory() throws SQLException {
String user = "admin";
Connection con = JdbcUtil.getConnection("org.apache.derby.jdbc.ClientDriver", "jdbc:derby://localhost:1527/memory:myDb;create=true", user, "123456");
business(con, user);
con.close();
}
@Test
public void networkFile() throws SQLException {
String user = "admin";
Connection con = JdbcUtil.getConnection("org.apache.derby.jdbc.ClientDriver", "jdbc:derby://localhost:1527/d:/temp/myDb;create=true", user, "123456");
business(con, user);
con.close();
}
@Test
public void networkJar() throws SQLException {
String user = "admin";
Connection con = JdbcUtil.getConnection("org.apache.derby.jdbc.ClientDriver", "jdbc:derby://localhost:1527/classpath:myDb", user, "123");
business(con, user);
con.close();
}
@Test
public void networkJar2() throws SQLException {
String user = "admin";
Connection con = JdbcUtil.getConnection("org.apache.derby.jdbc.ClientDriver", "jdbc:derby://localhost:1527/jar:(d:/temp/my.jar)myDb", user, "123");
business(con, user);
con.close();
}
@Test
public void networkServerControl() throws Exception {
NetworkServerControl server = new NetworkServerControl(InetAddress.getByName("0.0.0.0"), 1527);
server.start (null);
CountDownLatch countDownLatch = new CountDownLatch(1);
new Thread(() -> {
try {
//模拟其他应用访问
networkMemory();
} catch (Exception e) {
e.printStackTrace();
}
countDownLatch.countDown();
}).start();
countDownLatch.await();
server.shutdown();
}
private void business(Connection con, String user) throws SQLException {
String tableName = "a_student";
Statement st = con.createStatement();
String sql = "select 1 from sys.sysTABLES a,sys.sysschemas b where a.schemaid=b.schemaid and upper(b.schemaname)=? and upper(a.tablename)=?";
PreparedStatement pst = con.prepareStatement(sql);
pst.setString(1, user.toUpperCase());
pst.setString(2, tableName.toUpperCase());
ResultSet rs = pst.executeQuery();
if (!rs.next()) {//表不存在则创建并初始化数据，这里根据业务需要进行操作
st.executeUpdate("create table " + tableName + "(id int, name varchar(32))");
st.executeUpdate("insert into " + tableName + "(id,name) values (1,'李白')");
st.executeUpdate("insert into " + tableName + "(id,name) values (2,'杜甫')");
}
rs = st.executeQuery("select * from " + tableName);
while (rs.next()) {
log.info("id={},name={}", rs.getInt("id"), rs.getString("name"));
}
}
}DerbyCae.javahttps://images.cnblogs.com/OutliningIndicators/ContractedBlock.gifhttps://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gifpackage com.abc.demo.db;
import lombok.extern.slf4j.Slf4j;
import java.sql.*;
@Slf4j
public class JdbcUtil {
private JdbcUtil() {}
public static Connection getConnection(String driver, String url, String username, String password) {
Connection con = null;
try {
Class.forName(driver);
con = DriverManager.getConnection(url, username, password);
} catch (ClassNotFoundException | SQLException e) {
log.warn("url={},username={},password={}", url, username, password);
e.printStackTrace();
}
return con;
}
}JdbcUtil.java
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]