1. Connection
Connection是JDBC中代表数据库连接的接口。Connection对象通常友DriverManager和DataSource的getConnection()方法产生。其中DataSource主要有3种类型:标准DataSource、提供连接池的ConnectionPoolDataSource和实现分布式事务的XADataSource。
1.1 DriverManager
动态加载驱动,获得连接,示例如下:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class ComputerAccess ... {
public static void main(String[] args) ...{
Connection con = null;
try ...{
//加载MySQL数据库驱动
Class.forName("org.gjt.mm.mysql.Driver");
//设置访问属性
Properties prop = new Properties();
prop.setProperty("user", "root");
prop.setProperty("password", "passwd");
//打开数据库连接并连接到指定的URL
con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/hibernate", prop);
} catch (ClassNotFoundException e) ...{
e.printStackTrace();
} catch (SQLException e) ...{
e.printStackTrace();
}
try ...{
//编辑SQL数据库语句
Statement sm = con.createStatement();
String query = "select cpu from computer;";
//执行SQL查询语句
ResultSet rs = sm.executeQuery(query);
//从返回结果集中取出结果
while (rs.next()) ...{
String cpu = rs.getString("cpu");
System.out.println(cpu);
}
//关闭数据库连接
con.close();
} catch (SQLException e1) ...{
e1.printStackTrace();
}
}
}
1.2 标准DataSource
由数据库驱动提供。下面例子中使用了MySQL数据库驱动软件提供的标准DataSource实现。
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
public class BasicDataSourceTest ... {
public static void main(String[] args) throws SQLException ...{
MysqlDataSource ds = new MysqlDataSource();
ds.setURL("jdbc:mysql://localhost:3306/hibernate");
ds.setUser("root");
ds.setPassword("passwd");
Connection con = ds.getConnection();
//编辑SQL数据库语句
Statement sm = con.createStatement();
String query = "select cpu from computer;";
//执行SQL查询语句
ResultSet rs = sm.executeQuery(query);
//从返回结果集中取出结果
while (rs.next()) ...{
String cpu = rs.getString("cpu");
System.out.println(cpu);
}
//关闭数据库连接
con.close();
}
}
1.3 ConnectionPoolDataSource
由数据库驱动或者应用服务器提供。实际的数据库访问过程需要频繁的进行数据库的连接,为了提高效率,JDBC加入了连接池的机制:每次进行连接时,都会首先请求连接池中的连接,如果连接池中没有可用连接,则创建新的物理连接。连接池需要实现对可用连接的缓冲管理和响应处理。实例如下:
1.3.1 C3P0连接池
C3P0是一个开放源代码的JDBC连接池,它在lib目录中与Hibernate一起发布,包括了实现jdbc3和jdbc2扩展规范说明的Connection和Statement池的DataSources对象。实例如下:
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class C3P0Test ... {
public static void main(String[] args) throws Exception ...{
//创建C3P0提供的连接池数据源
ComboPooledDataSource cpds = new ComboPooledDataSource();
//设置数据库驱动
cpds.setDriverClass("org.gjt.mm.mysql.Driver");
//设置JDBC的URL
cpds.setJdbcUrl("jdbc:mysql://localhost:3306/hibernate");
//设置用户名
cpds.setUser("root");
//设置密码
cpds.setPassword("passwd");
//从连接池数据源获取数据库连接
Connection con = cpds.getConnection();
//编辑SQL数据库语句
Statement sm = con.createStatement();
String query = "select cpu from computer;";
//执行SQL查询语句
ResultSet rs = sm.executeQuery(query);
//从返回结果集中取出结果
while (rs.next()) ...{
String cpu = rs.getString("cpu");
System.out.println(cpu);
}
//关闭数据库连接
con.close();
}
}
1.3.2 PooledDataSource与JNDI
利用连接池实现的DataSource是一个系统中需要反复调用的资源,通常被注册到JNDI,以便于被其他应用程序使用。示例如下,注意这个例子使用的JNDI服务来自Apache的name包naming-common.jar,运行时要将其导入项目。
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Hashtable;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class C3P0JNDITest ... {
public static void main(String[] args) throws Exception ...{
Hashtable table = new Hashtable();
//设置JNDI的工厂类
table.put(Context.INITIAL_CONTEXT_FACTORY,
"org.apache.naming.java.javaURLContextFactory");
//初始化JNDI上下文
InitialContext ctx = new InitialContext(table);
//创建C3P0提供的连接池数据源
ComboPooledDataSource cpds = new ComboPooledDataSource();
//设置数据库驱动
cpds.setDriverClass("org.gjt.mm.mysql.Driver");
//设置JDBC的URL
cpds.setJdbcUrl("jdbc:mysql://localhost:3306/hibernate");
//设置用户名
cpds.setUser("root");
//设置密码
cpds.setPassword("passwd");
//设置连接池的初始大小
cpds.setInitialPoolSize(10);
//把数据源绑定到JNDI,名称是"hibernateDS"
ctx.bind("hibernateDS", cpds);
//从JNDI查找数据源
DataSource ds = (DataSource) ctx.lookup("hibernateDS");
//从连接池数据源获取数据库连接
Connection con = ds.getConnection();
//编辑SQL数据库语句
Statement sm = con.createStatement();
String query = "select cpu from computer;";
//执行SQL查询语句
ResultSet rs = sm.executeQuery(query);
//从返回结果集中取出结果
while (rs.next()) ...{
String cpu = rs.getString("cpu");
System.out.println(cpu);
}
//关闭数据库连接
con.close();
}
}
1.4 XADataSource
由应用服务器提供。
1.4.1 Tomcat中的数据源
在server.xml中Context配置resource即可。示例程序如下:
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
public class DataSourceServlet extends HttpServlet ... {
private DataSource ds;
/**//* (non-Javadoc)
* @see javax.servlet.http.HttpServlet#doGet(javax.servlet.http.HttpServletRequest, javax.servlet.http.HttpServletResponse)
*/
protected void doGet(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException ...{
PrintWriter out = response.getWriter();
try ...{
Connection con = ds.getConnection();
//编辑SQL数据库语句
Statement sm = con.createStatement();
String query = "select cpu from computer;";
//执行SQL查询语句
ResultSet rs = sm.executeQuery(query);
//从返回结果集中取出结果
while (rs.next()) ...{
String cpu = rs.getString("cpu");
out.println(cpu);
}
//关闭数据库连接
con.close();
} catch (SQLException e) ...{
e.printStackTrace();
}
}
/**//* (non-Javadoc)
* @see javax.servlet.http.HttpServlet#doPost(javax.servlet.http.HttpServletRequest, javax.servlet.http.HttpServletResponse)
*/
protected void doPost(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException ...{
super.doGet(request, response);
}
/**//* (non-Javadoc)
* @see javax.servlet.Servlet#init(javax.servlet.ServletConfig)
*/
public void init(ServletConfig config) throws ServletException ...{
super.init(config);
try ...{
InitialContext ctx = new InitialContext();
ds = (DataSource) ctx.lookup("java:comp/env/jdbc/hibernate");
} catch (NamingException e) ...{
e.printStackTrace();
}
}
}
2. Statement
JDBC通过Statement完成执行SQL的过程,JDBC中包含3种Statement:Statement、PreparedStatement、CallableStatement,它们之间是依次继承的关系,其中PreparedStatement是实现Hibernate的基础。
2.1 Statement
用于执行静态的SQL语句。
方法名 | 对应SQL操作 | 返回结果 |
executeUpdate | insert、update、delete | 受影响的行数 |
executeQuery | - | 单一ResultSet |
execute | - | 多个ResultSet |
2.1.1 executeUpdate() demo
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class ExcuteUpdateTest ... {
public static void main(String[] args) ...{
Connection con = null;
try ...{
//加载MySQL数据库驱动
Class.forName("org.gjt.mm.mysql.Driver");
//设置访问属性
Properties prop = new Properties();
prop.setProperty("user", "root");
prop.setProperty("password", "passwd");
//打开数据库连接并连接到指定的URL
con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/hibernate", prop);
} catch (ClassNotFoundException e) ...{
e.printStackTrace();
} catch (SQLException e) ...{
e.printStackTrace();
}
try ...{
//编辑SQL数据库语句
Statement sm = con.createStatement();
String query = "insert into computer(id,cpu,display) values(NULL,'Intel 奔腾 P4 2.4B GHz','LG 563LS');";
//执行SQL查询语句
int result = sm.executeUpdate(query);
System.out.println(result);
//关闭数据库连接
con.close();
} catch (SQLException e1) ...{
e1.printStackTrace();
}
}
}
2.1.2 execute() demo
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class ExcuteTest ... {
public static void main(String[] args) ...{
Connection con = null;
try ...{
//加载MySQL数据库驱动
Class.forName("org.gjt.mm.mysql.Driver");
//设置访问属性
Properties prop = new Properties();
prop.setProperty("user", "root");
prop.setProperty("password", "passwd");
//打开数据库连接并连接到指定的URL
con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/hibernate", prop);
//DatabaseMetaData dmd = con.getMetaData();
//System.out.println(dmd.getSchemaTerm());
//System.out.println(dmd.getCatalogTerm());
} catch (ClassNotFoundException e) ...{
e.printStackTrace();
} catch (SQLException e) ...{
e.printStackTrace();
}
try ...{
//编辑SQL数据库语句
Statement sm = con.createStatement();
//String query = "insert into computer(id,cpu,display) values(NULL,'Intel 奔腾 P4 2.4B GHz','LG 563LS');";
String query = "select * from computer;";
//执行SQL查询语句
boolean result = sm.execute(query);
if (!result) ...{
System.out.println(sm.getUpdateCount());
} else ...{
ResultSet rs = sm.getResultSet();
while (rs.next()) ...{
String id = rs.getString("id");
System.out.println(id);
}
}
//关闭数据库连接
con.close();
} catch (SQLException e1) ...{
e1.printStackTrace();
}
}
}
2.1.3 executeQuery() demo
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class ExcuteQueryTest ... {
public static void main(String[] args) ...{
Connection con = null;
try ...{
//加载MySQL数据库驱动
Class.forName("org.gjt.mm.mysql.Driver");
//设置访问属性
Properties prop = new Properties();
prop.setProperty("user", "root");
prop.setProperty("password", "passwd");
//打开数据库连接并连接到指定的URL
con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/hibernate", prop);
} catch (ClassNotFoundException e) ...{
e.printStackTrace();
} catch (SQLException e) ...{
e.printStackTrace();
}
try ...{
//编辑SQL数据库语句
Statement sm = con.createStatement();
String query = "select * from computer;";
//执行SQL查询语句
ResultSet rs = sm.executeQuery(query);
while (rs.next()) ...{
String id = rs.getString("id");
System.out.println(id);
}
//关闭数据库连接
con.close();
} catch (SQLException e1) ...{
e1.printStackTrace();
}
}
}
2.2 PreparedStatement
PreparedStatement对SQL执行预编译过程,执行速度较快;IN参数以占位符“?”的方式添加。示例如下:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
public class PreparedStatementQuery ... {
public static void main(String[] args) ...{
Connection con = null;
try ...{
//加载MySQL数据库驱动
Class.forName("org.gjt.mm.mysql.Driver");
//设置访问属性
Properties prop = new Properties();
prop.setProperty("user", "root");
prop.setProperty("password", "passwd");
//打开数据库连接并连接到指定的URL
con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/hibernate", prop);
} catch (ClassNotFoundException e) ...{
e.printStackTrace();
} catch (SQLException e) ...{
e.printStackTrace();
}
try ...{
String query = "select * from computer where id = ?;";
//编辑SQL数据库语句
PreparedStatement ps = con.prepareStatement(query);
ps.setLong(1, 2);
//执行SQL查询语句
ResultSet rs = ps.executeQuery();
while (rs.next()) ...{
String cpu = rs.getString("cpu");
System.out.println(cpu);
}
//关闭数据库连接
con.close();
} catch (SQLException e1) ...{
e1.printStackTrace();
}
}
}
2.3 CallableStatement
提供了调用数据库存储过程的方法。调用存储过程需要使用转义语法来完成,转义语法可以包括结果参数,也可以不包括结果参数。
2.3.1 创建存储过程
begin
select id
from hibernate.computer;
end
2.3.2 demo
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class CallableStatementQuery ... {
public static void main(String[] args) ...{
Connection con = null;
try ...{
//加载MySQL数据库驱动
Class.forName("org.gjt.mm.mysql.Driver");
//设置访问属性
Properties prop = new Properties();
prop.setProperty("user", "root");
prop.setProperty("password", "passwd");
//打开数据库连接并连接到指定的URL
con = DriverManager.getConnection(
"jdbc:mysql://localhost/hibernate", prop);
} catch (ClassNotFoundException e) ...{
e.printStackTrace();
} catch (SQLException e) ...{
e.printStackTrace();
}
try ...{
String produce = "CREATE PROCEDURE SELECT_COMPUTER() BEGIN SELECT id FROM hibernate.Computer; END";
Statement stm = con.createStatement();
stm.executeUpdate(produce);
//String query = "select * from computer where id = ?;";
//编辑SQL数据库语句
CallableStatement cs = con.prepareCall("{call SELECT_COMPUTER()}");
//cs.setLong(1,1);
//执行SQL查询语句
ResultSet rs = cs.executeQuery();
while (rs.next()) ...{
String id = rs.getString("id");
System.out.println(id);
}
//关闭数据库连接
con.close();
} catch (SQLException e1) ...{
e1.printStackTrace();
}
}
}
3. 事务
使用Savepoint可以记录当前的状态并标记为一个Savepoint,以后的操作可以退回到这个SavePoint。示例如下:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Statement;
import java.util.Properties;
public class SavepointTest ... {
public static void main(String[] args) ...{
Connection con = null;
try ...{
//加载MySQL数据库驱动
Class.forName("org.gjt.mm.mysql.Driver");
//设置访问属性
Properties prop = new Properties();
prop.setProperty("user", "root");
prop.setProperty("password", "passwd");
//打开数据库连接并连接到指定的URL
con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/hibernate", prop);
con.setAutoCommit(false);
} catch (ClassNotFoundException e) ...{
e.printStackTrace();
} catch (SQLException e) ...{
e.printStackTrace();
}
String query01 = "insert into computer(id,cpu,display) values(NULL,'Intel 奔腾 P4 2.3B GHz','LG 563LS');";
String query02 = "insert into computer(id,cpu,display) values(NULL,'Intel 奔腾 P4 2.4B GHz','LG 563LS');";
try ...{
Statement stm = con.createStatement();
stm.executeUpdate(query01);
Savepoint point01 = con.setSavepoint("a");
stm.executeUpdate(query02);
//第二次的插入操作被取消了
con.rollback(point01);
con.commit();
con.close();
} catch (SQLException e2) ...{
e2.printStackTrace();
}
}
}
3. ResultSet
ResultSet的可更新和可滚动属性是相互独立的。
4. Blob和Clob
在JDBC中,Blob和Clob用来存储大数据对象。Blob用来存储二进值大对象,Clob用于存储字符型大对象。
4.1 创建表
`name` varchar ( 30 ),
`value` longblob,
`id` bigint default '' not null ,
primary key (`id`)
);
create unique index ` PRIMARY ` on `hibernate`.`bigimage`(`id`);
4.2 demo
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;
public class FileToBlob ... {
public static void main(String[] args) ...{
Connection con = null;
try ...{
//加载MySQL数据库驱动
Class.forName("org.gjt.mm.mysql.Driver");
//设置访问属性
Properties prop = new Properties();
prop.setProperty("user", "root");
prop.setProperty("password", "passwd");
//打开数据库连接并连接到指定的URL
con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/hibernate", prop);
} catch (ClassNotFoundException e) ...{
e.printStackTrace();
} catch (SQLException e) ...{
e.printStackTrace();
}
try ...{
String query = "insert into bigImage(id,name,value) values(?,?,?);";
//编辑SQL数据库语句
PreparedStatement ps = con.prepareStatement(query);
ps.setLong(1, 4);
ps.setString(2, "photo001");
File imageFile = new File("test.bmp");
FileInputStream is = new FileInputStream(imageFile);
ps.setBinaryStream(3, is, (int) imageFile.length());
//执行SQL查询语句
ps.executeUpdate();
//关闭数据库连接
con.close();
} catch (SQLException e1) ...{
e1.printStackTrace();
} catch (FileNotFoundException e2) ...{
e2.printStackTrace();
}
}
}
参考《精通Hibernate》刘洋 著