Hibernate底层技术简介 JDBC编程

技术2022-05-11 122

1. Connection

Connection是JDBC中代表数据库连接的接口。Connection对象通常友DriverManager和DataSource的getConnection()方法产生。其中DataSource主要有3种类型：标准DataSource、提供连接池的ConnectionPoolDataSource和实现分布式事务的XADataSource。

1.1 DriverManager

动态加载驱动，获得连接，示例如下：

package com.weportal.jdbc; 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实现。

package com.weportal.jdbc; 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对象。实例如下：

package com.weportal.jdbc.c3p; 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，运行时要将其导入项目。

package com.weportal.jdbc.c3p;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即可。示例程序如下：

package com.weportal.jdbc; 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语句。

Statement的主要方法方法名对应SQL操作返回结果executeUpdateinsert、update、delete受影响的行数executeQuery-单一ResultSetexecute-多个ResultSet

2.1.1 executeUpdate() demo

package com.weportal.jdbc.statement; 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

package com.weportal.jdbc.statement; 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

package com.weportal.jdbc.statement; 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参数以占位符“?”的方式添加。示例如下：

package com.weportal.jdbc.preparedstatement; 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 创建存储过程

create procedure select_computer() begin select id from hibernate.computer; end

2.3.2 demo

package com.weportal.jdbc.callablestatement; 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。示例如下：

package com.weportal.jdbc.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 创建表

create table `hibernate`.`bigimage`( `name` varchar ( 30 ), `value` longblob, `id` bigint default '' not null , primary key (`id`) ); create unique index ` PRIMARY ` on `hibernate`.`bigimage`(`id`);

4.2 demo

package com.weportal.jdbc.blob; 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》刘洋著

专利

最新回复(0)