Spring JdbcTemplate操作小结
时间:2022-03-16 10:00
Spring
提供了JdbcTemplate 来封装数据库jdbc操作细节:
包括: 数据库连接[打开/关闭] ,异常转义 ,SQL执行 ,查询结果的转换
使用模板方式封装 jdbc数据库操作-固定流程的动作,提供丰富callback回调接口功能,方便用户自定义加工细节,更好模块化jdbc操作,简化传统的JDBC操作的复杂和繁琐过程。
1) 使用JdbcTemplate 更新(insert /update /delete)
Java代码
- int k = jdbcTemplate.update("UPDATE tblname SET prop1=?,prop2=?...", new Object[]{...});
Java代码
- jdbcTemplate.update("INSERT INTO tblname VALUES(?,?,..)", new Object[]{...},
- new int[]{Types.VARCHAR,Types.NUMERIC});
Java代码
- jdbcTemplate.update("INSERT INTO tblname VALUES(?,?,..)",
- new PreparedStatementSetter(){
- public void setValues(PreparedStatement ps) throws SQLException{
- ps.setLong(1, user.getId(1));
- ps.setString(2, user.getName(2));
- ps.setDate(3, new java.sql.Date(new Date().getTime());
- ps.setTimestamp(4, new Timestamp(new Date().getTime());
- }
- }
- );
2) 使用JdbcTemplate 查询 (select)
- final User user = new User();
- jdbcTemplate.query("SELECT id,name,.. FROM tblname WHERE id=1",
- new RowCallbackHandler(){
- public void processRow(ResultSet rs) throws SQLException{
- user.setId(rs.getLong(1));
- user.setName(rs.getString(2));
- }
- }
- );
Java代码
- List uGroup = jdbcTemplate.query("SELECT id,name,.. FROM tblname WHERE igroup=1",
- new RowMapper(){
- public Object mapRow(ResultSet rs,int no) throws SQLException{
- User user = new User();
- user.setId(rs.getLong(1));
- user.setName(rs.getString(2));
- return user ;
- }
- }
- };
3)使用JdbcTemplate 便捷方法
- List uNames = jdbcTemplate.queryForList("SELECT name FROM tblname WHERE id>?",
- new Integer []{5}, String.class);
Java代码
- List<Map> uMapList = (List<Map>) jdbcTemplate.queryForList( "SELECT id, name FROM tblname WHERE id>?",
- new Integer []{5});
- for(Map<String,Object> uMap :uMapList){
- Integer id = uMap.get("id");
- String name = uMap.get("name");
- };
Java代码
- String user = jdbcTemplate.queryForObject("SELECT name FROM tblname WHERE id=?",
- new Integer []{5}, String.class );
Java代码
- int uNum = jdbcTemplate.queryForInt("SELECT count(*) FROM tblname WHERE id>?",
- new Integer []{5});
4)使用jdbc 操作类
a)扩展 MappingSqlQuery类
Java代码- class JdbcQueryObject extends MappingSqlQuery { // extends SqlQuery
- public JdbcQueryObject (DataSource ds,String sql){
- this.setDataSource( ds );
- this.setSql( sql );
- this.declareParameter(new Sqlparameter("propName",
- Types.VARCHAR);// propName 提示作用
- this.compile();
- }
- public Object mapRow(ResultSet rs,int p) throws SQLException{
- // ...
- }
- }
- JdbcQueryObject queryObj = new JdbcQueryObject( ds,
- "SELECT .. FROM tblName WHERE param=?");
- List list = queryObj.execute(new Object[]{...});
b)使用 SqlFunction 类 查询单条结果
Java代码- SqlFunction queryFun = new SqlFunction( ds,
- "select count(*) from tblName where ..." ,new int[]{Types.CHAR,...} );
- queryFun.compile();
- queryFun.run(new Object[]{p1,p2,..});
c)使用 SqlUpdate 类 更新
Java代码- SqlUpdate updateFunc = new SqlUpdate(ds ,"INSERT tblName ...");
- updateFunc.declareParameter( new SqlParameter("prop",Types.CHAR) );
- updateFunc.compile();
- updateFunc.update(new String[]{s1,s1});
5)支持jdbc 事务
spring的事务管理有两种方式:编程式事务、声明式事务
这里谈一下 基于数据库单一资源的编程式事务:
spring用实现TransactionDefinition接口的类定义事务的属性:传播行为;隔离级别;超时值;只读标志
默认实现为:DefaultTransactionDefinition类
Java代码
- PlatformTransactionManager tm =
- new DataSourceTransactionManager(
- jdbcTemplate.getDataSource() );
- TransactionStatus status = null;
- try{
- //null 默认事务属性配置DefaultTransactionDefinition
- status = tm.getTransaction(null);
- for(final String wd: words){
- try {
- jdbcTemplate.update( insertWordSql,
- new PreparedStatementSetter(){
- public void setValues(PreparedStatement pstate)
- throws SQLException {
- pstate.setString(1, wd) ;
- pstate.setTimestamp(2,
- new Timestamp( new Date().getTime() ));
- }
- }
- );
- } catch (DataAccessException e) {
- e.printStackTrace();
- //tm.rollback(status);
- }
- } // end for
- } finally {
- tm.commit(status);
- }
- ------------------------------------------------------------------------------------------------------------------------------------------------
-
当hql等查询方式不能满足性能或灵活性的要求,必须使用SQL时,大家有三种选择:
第一、使用Hibernate 的sql 查询函数,将查询结果对象转为Entity对象。
第二、使用Hibernate Session的getConnection 获得JDBC Connection,然后进行纯JDBC API操作;
第三、选择把Spring的JDBCTemplate作为一种很不错的JDBC Utils来使用。
JDBCTemplate的使用很简单,只要在ApplicationContext文件里定义一个jdbcTemplate节点,POJO获得注入后可以直接执行操作,不需要继承什么基类,详见。
AplicationContext定义:
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>实际使用:
SqlRowSet rs = jdbcTemplate.queryForRowSet(sql, params);
Tips1: jdbcTemplate有很多的ORM化回调操作将返回结果转为对象列表,但很多时候还是需要返回ResultSet,Spring有提供一个类似ResultSet的 Spring SqlRowSet对象。
Tips2:.注意jdbcTemplate尽量只执行查询操作,莫要进行更新,否则很容易破坏Hibernate的二级缓存体系。
Chapter 11. 使用JDBC进行数据访问
11.1. 简介
Spring JDBC抽象框架所带来的价值将在以下几个方面得以体现:(注:使用了Spring JDBC抽象框架之后,应用开发人员只需要完成斜体字部分的编码工作。)
-
指定数据库连接参数
-
打开数据库连接
-
声明SQL语句
-
预编译并执行SQL语句
-
遍历查询结果(如果需要的话)
-
处理每一次遍历操作
-
处理抛出的任何异常
-
处理事务
-
关闭数据库连接
Spring将替我们完成所有单调乏味的JDBC底层细节处理工作。
11.1.1. Spring JDBC包结构
Spring JDBC抽象框架由四个包构成:core、 dataSource、object以及support。
org.springframework.jdbc.core包由JdbcTemplate类以及相关的回调接口(callback interface)和类组成。
org.springframework.jdbc.datasource包由一些用来简化DataSource访问的工具类,以及各种DataSource接口的简单实现(主要用于单元测试以及在J2EE容器之外使用JDBC)组成。工具类提供了一些静态方法,诸如通过JNDI获取数据连接以及在必要的情况下关闭这些连接。它支持绑定线程的连接,比如被用于DataSourceTransactionManager的连接。
接下来,org.springframework.jdbc.object包由封装了查询、更新以及存储过程的类组成,这些类的对象都是线程安全并且可重复使用的。它们类似于JDO,与JDO的不同之处在于查询结果与数据库是“断开连接”的。它们是在org.springframework.jdbc.core包的基础上对JDBC更高层次的抽象。
最后,org.springframework.jdbc.support包提供了一些SQLException的转换类以及相关的工具类。
在JDBC处理过程中抛出的异常将被转换成org.springframework.dao包中定义的异常。因此使用Spring JDBC进行开发将不需要处理JDBC或者特定的RDBMS才会抛出的异常。所有的异常都是unchecked exception,这样我们就可以对传递到调用者的异常进行有选择的捕获。
11.2. 利用JDBC核心类实现JDBC的基本操作和错误处理
11.2.1. JdbcTemplate类
JdbcTemplate是core包的核心类。它替我们完成了资源的创建以及释放工作,从而简化了我们对JDBC的使用。它还可以帮助我们避免一些常见的错误,比如忘记关闭数据库连接。JdbcTemplate将完成JDBC核心处理流程,比如SQL语句的创建、执行,而把SQL语句的生成以及查询结果的提取工作留给我们的应用代码。它可以完成SQL查询、更新以及调用存储过程,可以对ResultSet进行遍历并加以提取。它还可以捕获JDBC异常并将其转换成org.springframework.dao包中定义的,通用的,信息更丰富的异常。
使用JdbcTemplate进行编码只需要根据明确定义的一组契约来实现回调接口。PreparedStatementCreator回调接口通过给定的Connection创建一个PreparedStatement,包含SQL和任何相关的参数。CallableStatementCreateor实现同样的处理,只不过它创建的是CallableStatement。RowCallbackHandler接口则从数据集的每一行中提取值。
我们可以在一个service实现类中通过传递一个DataSource引用来完成JdbcTemplate的实例化,也可以在application context中配置一个JdbcTemplate bean,来供service使用。需要注意的是DataSource在application context总是配制成一个bean,第一种情况下,DataSource bean将传递给service,第二种情况下DataSource bean传递给JdbcTemplate bean。因为JdbcTemplate使用回调接口和SQLExceptionTranslator接口作为参数,所以一般情况下没有必要通过继承JdbcTemplate来定义其子类。
JdbcTemplate中使用的所有SQL将会以“DEBUG”级别记入日志(一般情况下日志的category是JdbcTemplate相应的全限定类名,不过如果需要对JdbcTemplate进行定制的话,可能是它的子类名)。
11.2.2. NamedParameterJdbcTemplate类
NamedParameterJdbcTemplate类增加了在SQL语句中使用命名参数的支持。在此之前,在传统的SQL语句中,参数都是用‘?‘占位符来表示的。 NamedParameterJdbcTemplate类内部封装了一个普通的JdbcTemplate,并作为其代理来完成大部分工作。下面的内容主要针对NamedParameterJdbcTemplate与JdbcTemplate的不同之处来加以说明,即如何在SQL语句中使用命名参数。
通过下面的例子我们可以更好地了解NamedParameterJdbcTemplate的使用模式(在后面我们还有更好的使用方式)。
// some JDBC-backed DAO class... public int countOfActorsByFirstName(String firstName) { String sql = "select count(0) from T_ACTOR where first_name = :first_name"; NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(this.getDataSource()); SqlParameterSource namedParameters = new MapSqlParameterSource("first_name", firstName); return template.queryForInt(sql, namedParameters); }
在上面例子中,sql变量使用了命名参数占位符“first_name”,与其对应的值存在namedParameters变量中(类型为MapSqlParameterSource)。
如果你喜欢的话,也可以使用基于Map风格的名值对将命名参数传递给NamedParameterJdbcTemplate(NamedParameterJdbcTemplate实现了NamedParameterJdbcOperations接口,剩下的工作将由调用该接口的相应方法来完成,这里我们就不再赘述):
// some JDBC-backed DAO class... public int countOfActorsByFirstName(String firstName) { String sql = "select count(0) from T_ACTOR where first_name = :first_name"; NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(this.getDataSource()); Map namedParameters = new HashMap(); namedParameters.put("first_name", firstName); return template.queryForInt(sql, namedParameters); }
另外一个值得一提的特性是与NamedParameterJdbcTemplate位于同一个包中的SqlParameterSource接口。在前面的代码片断中我们已经看到了该接口的实现(即MapSqlParameterSource类),SqlParameterSource可以用来作为NamedParameterJdbcTemplate命名参数的来源。MapSqlParameterSource类是一个非常简单的实现,它仅仅是一个java.util.Map适配器,当然其用法也就不言自明了(如果还有不明了的,可以在Spring的JIRA系统中要求提供更多的相关资料)。
SqlParameterSource接口的另一个实现--BeanPropertySqlParameterSource为我们提供了更有趣的功能。该类包装一个类似JavaBean的对象,所需要的命名参数值将由包装对象提供,下面我们使用一个例子来更清楚地说明它的用法。
// some JavaBean-like class... public class Actor { private Long id; private String firstName; private String lastName; public String getFirstName() { return this.firstName; } public String getLastName() { return this.lastName; } public Long getId() { return this.id; } // setters omitted... }
// some JDBC-backed DAO class... public int countOfActors(Actor exampleActor) { // notice how the named parameters match the properties of the above ‘Actor‘ class String sql = "select count(0) from T_ACTOR where first_name = :firstName and last_name = :lastName"; NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(this.getDataSource()); SqlParameterSource namedParameters = new BeanPropertySqlParameterSource(exampleActor); return template.queryForInt(sql, namedParameters); }
大家必须牢记一点:NamedParameterJdbcTemplate类内部包装了一个标准的JdbcTemplate类。如果你需要访问其内部的JdbcTemplate实例(比如访问JdbcTemplate的一些方法)那么你需要使用getJdbcOperations()方法返回的JdbcOperations接口。(JdbcTemplate实现了JdbcOperations接口)。
NamedParameterJdbcTemplate类是线程安全的,该类的最佳使用方式不是每次操作的时候实例化一个新的NamedParameterJdbcTemplate,而是针对每个DataSource只配置一个NamedParameterJdbcTemplate实例(比如在Spring IoC容器中使用Spring IoC来进行配置),然后在那些使用该类的DAO中共享该实例。
11.2.3. SimpleJdbcTemplate类
Note 在许多Spring开发人员中间存在有一种观点,那就是下面将要提到的各种RDBMS操作类 (类除外) 通常也可以直接使用JdbcTemplate相关的方法来替换。 相对于把一个查询操作封装成一个类而言,直接调用JdbcTemplate方法将更简单 而且更容易理解。
必须说明的一点就是,这仅仅只是一种观点而已, 如果你认为你可以从直接使用RDBMS操作类中获取一些额外的好处, 你不妨根据自己的需要和喜好进行不同的选择。
11.4.1. SqlQuery类
SqlQuery是一个可重用、线程安全的类,它封装了一个SQL查询。 其子类必须实现newResultReader()方法,该方法用来在遍历 ResultSet的时候能使用一个类来保存结果。 我们很少需要直接使用SqlQuery,因为其子类MappingSqlQuery作为一个更加易用的实现能够将结果集中的行映射为Java对象。 SqlQuery还有另外两个扩展分别是 MappingSqlQueryWithParameters和UpdatableSqlQuery。
11.4.2. MappingSqlQuery类
MappingSqlQuery是一个可重用的查询抽象类,其具体类必须实现 mapRow(ResultSet, int)抽象方法来将结果集中的每一行转换成Java对象。
在SqlQuery的各种实现中, MappingSqlQuery是最常用也是最容易使用的一个。
下面这个例子演示了一个定制查询,它将从客户表中取得的数据映射到一个 Customer类实例。
private class CustomerMappingQuery extends MappingSqlQuery { public CustomerMappingQuery(DataSource ds) { super(ds, "SELECT id, name FROM customer WHERE id = ?"); super.declareParameter(new SqlParameter("id", Types.INTEGER)); compile(); } public Object mapRow(ResultSet rs, int rowNumber) throws SQLException { Customer cust = new Customer(); cust.setId((Integer) rs.getObject("id")); cust.setName(rs.getString("name")); return cust; } }
在上面的例子中,我们为用户查询提供了一个构造函数并为构造函数传递了一个 DataSource参数。在构造函数里面我们把 DataSource和一个用来返回查询结果的SQL语句作为参数 调用父类的构造函数。SQL语句将被用于生成一个PreparedStatement对象, 因此它可以包含占位符来传递参数。而每一个SQL语句的参数必须通过调用 declareParameter方法来进行声明,该方法需要一个 SqlParameter(封装了一个字段名字和一个 java.sql.Types中定义的JDBC类型)对象作为参数。 所有参数定义完之后,我们调用compile()方法来对SQL语句进行预编译。
下面让我们看看该定制查询初始化并执行的代码:
public Customer getCustomer(Integer id) { CustomerMappingQuery custQry = new CustomerMappingQuery(dataSource); Object[] parms = new Object[1]; parms[0] = id; List customers = custQry.execute(parms); if (customers.size() > 0) { return (Customer) customers.get(0); } else { return null; } }
在上面的例子中,getCustomer方法通过传递惟一参数id来返回一个客户对象。 该方法内部在创建CustomerMappingQuery实例之后, 我们创建了一个对象数组用来包含要传递的查询参数。这里我们只有唯一的一个 Integer参数。执行CustomerMappingQuery的 execute方法之后,我们得到了一个List,该List中包含一个 Customer对象,如果有对象满足查询条件的话。
11.4.3. SqlUpdate类
SqlUpdate类封装了一个可重复使用的SQL更新操作。 跟所有RdbmsOperation类一样,SqlUpdate可以在SQL中定义参数。
该类提供了一系列update()方法,就像SqlQuery提供的一系列execute()方法一样。
SqlUpdate是一个具体的类。通过在SQL语句中定义参数,这个类可以支持 不同的更新方法,我们一般不需要通过继承来实现定制。
import java.sql.Types; import javax.sql.DataSource; import org.springframework.jdbc.core.SqlParameter; import org.springframework.jdbc.object.SqlUpdate; public class UpdateCreditRating extends SqlUpdate { public UpdateCreditRating(DataSource ds) { setDataSource(ds); setSql("update customer set credit_rating = ? where id = ?"); declareParameter(new SqlParameter(Types.NUMERIC)); declareParameter(new SqlParameter(Types.NUMERIC)); compile(); } /** * @param id for the Customer to be updated * @param rating the new value for credit rating * @return number of rows updated */ public int run(int id, int rating) { Object[] params = new Object[] { new Integer(rating), new Integer(id)}; return update(params); } }
11.4.4. StoredProcedure类
StoredProcedure类是一个抽象基类,它是对RDBMS存储过程的一种抽象。 该类提供了多种execute(..)方法,不过这些方法的访问类型都是protected的。
从父类继承的sql属性用来指定RDBMS存储过程的名字。 尽管该类提供了许多必须在JDBC3.0下使用的功能,但是我们更关注的是JDBC 3.0中引入的命名参数特性。
下面的程序演示了如何调用Oracle中的sysdate()函数。 这里我们创建了一个继承StoredProcedure的子类,虽然它没有输入参数, 但是我必须通过使用SqlOutParameter来声明一个日期类型的输出参数。 execute()方法将返回一个map,map中的每个entry是一个用参数名作key, 以输出参数为value的名值对。
import java.sql.Types; import java.util.HashMap; import java.util.Iterator; import java.util.Map; import javax.sql.DataSource; import org.springframework.jdbc.core.SqlOutParameter; import org.springframework.jdbc.datasource.*; import org.springframework.jdbc.object.StoredProcedure; public class TestStoredProcedure { public static void main(String[] args) { TestStoredProcedure t = new TestStoredProcedure(); t.test(); System.out.println("Done!"); } void test() { DriverManagerDataSource ds = new DriverManagerDataSource(); ds.setDriverClassName("oracle.jdbc.OracleDriver"); ds.setUrl("jdbc:oracle:thin:@localhost:1521:mydb"); ds.setUsername("scott"); ds.setPassword("tiger"); MyStoredProcedure sproc = new MyStoredProcedure(ds); Map results = sproc.execute(); printMap(results); } private class MyStoredProcedure extends StoredProcedure { private static final String SQL = "sysdate"; public MyStoredProcedure(DataSource ds) { setDataSource(ds); setFunction(true); setSql(SQL); declareParameter(new SqlOutParameter("date", Types.DATE)); compile(); } public Map execute() { // the ‘sysdate‘ sproc has no input parameters, so an empty Map is supplied... return execute(new HashMap()); } } private static void printMap(Map results) { for (Iterator it = results.entrySet().iterator(); it.hasNext(); ) { System.out.println(it.next()); } } }
下面是StoredProcedure的另一个例子,它使用了两个Oracle游标类型的输出参数。
import oracle.jdbc.driver.OracleTypes; import org.springframework.jdbc.core.SqlOutParameter; import org.springframework.jdbc.object.StoredProcedure; import javax.sql.DataSource; import java.util.HashMap; import java.util.Map; public class TitlesAndGenresStoredProcedure extends StoredProcedure { private static final String SPROC_NAME = "AllTitlesAndGenres"; public TitlesAndGenresStoredProcedure(DataSource dataSource) { super(dataSource, SPROC_NAME); declareParameter(new SqlOutParameter("titles", OracleTypes.CURSOR, new TitleMapper())); declareParameter(new SqlOutParameter("genres", OracleTypes.CURSOR, new GenreMapper())); compile(); } public Map execute() { // again, this sproc has no input parameters, so an empty Map is supplied... return super.execute(new HashMap()); } }
值得注意的是TitlesAndGenresStoredProcedure构造函数中 declareParameter(..)的SqlOutParameter参数, 该参数使用了RowMapper接口的实现。 这是一种非常方便而强大的重用方式。 下面我们来看一下RowMapper的两个具体实现。
首先是TitleMapper类,它简单的把ResultSet中的每一行映射为一个Title Domain Object。
import com.foo.sprocs.domain.Title; import org.springframework.jdbc.core.RowMapper; import java.sql.ResultSet; import java.sql.SQLException; public final class TitleMapper implements RowMapper { public Object mapRow(ResultSet rs, int rowNum) throws SQLException { Title title = new Title(); title.setId(rs.getLong("id")); title.setName(rs.getString("name")); return title; } }
另一个是GenreMapper类,也是非常简单的将ResultSet中的每一行映射为一个Genre Domain Object。
import org.springframework.jdbc.core.RowMapper; import java.sql.ResultSet; import java.sql.SQLException; import com.foo.domain.Genre; public final class GenreMapper implements RowMapper { public Object mapRow(ResultSet rs, int rowNum) throws SQLException { return new Genre(rs.getString("name")); } }
如果你需要给存储过程传输入参数(这些输入参数是在RDBMS存储过程中定义好了的), 则需要提供一个指定类型的execute(..)方法, 该方法将调用基类的protected execute(Map parameters)方法。 例如:
import oracle.jdbc.driver.OracleTypes; import org.springframework.jdbc.core.SqlOutParameter; import org.springframework.jdbc.object.StoredProcedure; import javax.sql.DataSource; import java.util.HashMap; import java.util.Map; public class TitlesAfterDateStoredProcedure extends StoredProcedure { private static final String SPROC_NAME = "TitlesAfterDate"; private static final String CUTOFF_DATE_PARAM = "cutoffDate"; public TitlesAfterDateStoredProcedure(DataSource dataSource) { super(dataSource, SPROC_NAME); declaraParameter(new SqlParameter(CUTOFF_DATE_PARAM, Types.DATE); declareParameter(new SqlOutParameter("titles", OracleTypes.CURSOR, new TitleMapper())); compile(); } public Map execute(Date cutoffDate) { Map inputs = new HashMap(); inputs.put(CUTOFF_DATE_PARAM, cutoffDate); return super.execute(inputs); } }
11.4.5. SqlFunction类
SqlFunction RDBMS操作类封装了一个SQL“函数”包装器(wrapper), 该包装器适用于查询并返回一个单行结果集。默认返回的是一个int值, 不过我们可以采用类似JdbcTemplate中的queryForXxx 做法自己实现来返回其它类型。SqlFunction优势在于我们不必创建 JdbcTemplate,这些它都在内部替我们做了。
该类的主要用途是调用SQL函数来返回一个单值的结果集,比如类似“select user()”、 “select sysdate from dual”的查询。如果需要调用更复杂的存储函数, 可以使用StoredProcedure或SqlCall。
SqlFunction是一个具体类,通常我们不需要它的子类。 其用法是创建该类的实例,然后声明SQL语句以及参数就可以调用相关的run方法去多次执行函数。 下面的例子用来返回指定表的记录行数:
public int countRows() { SqlFunction sf = new SqlFunction(dataSource, "select count(*) from mytable"); sf.compile(); return sf.run(); }
-
Spring JdbcTemplate操作小结,布布扣,bubuko.com