Spring Jdbc

Spring Jdbc用法:

使用jdbcTemplate查询数据的时候可以使用queryForXXX等方法。下面我们就一一解析一下：
1、jdbcTemplate.queryForInt()和jdbcTemplate.queryForLong()
--使用queryForInt返回user表中的记录数量，queryForInt搭配这样的sql可以在分页的时候计算总记录数
jdbcTemplate.queryForInt("select count(*) from user");

这个queryForInt已经废弃



2、jdbcTemplate.queryForObject()
--本质上和queryForInt相同，只是可以返回不同的对象,例如返回一个String对象
String name = (String) jdbcTemplate.queryForObject(  --3个参数，1、sql 2、要传递的参数数组 3、返回来的对象class
"SELECT name FROM USER WHERE id = ?",  
new Object[] {id},  
java.lang.String.class);       

3、jdbcTemplate.queryForList(???)
--返回一个装有map的list,每一个map是一条记录,map里面的key是字段名
List rows = jdbcTemplate.queryForList("SELECT * FROM user");  --得到装有map的list
for(int i=0;i<rows.size();i++){                   --遍历
Map userMap=rows.get(i);
System.out.println(userMap.get("id"));  
System.out.println(userMap.get("name"));  
System.out.println(userMap.get("age"));
}


4、jdbcTemplate.queryForMap(SQL)
--这个查询只能是查询一条记录的查询,返回一个map,key的值是column的值
Map map = jdbcTemplate.queryForMap("select count(*) as keyval from user");
map.get("keyval")


5、jdbcTemplate.queryForRowSet(???)
--返回一个RowSet   然后调用.getString或者getInt等去取值




6、jdbc1.query(sql, new RowCallbackHandler()
--返回一个ResultSet对象， processRow有自动循环的机制，它会自动执行processRow中的语句直到
--rs的size执行完了为止。我们可以在这其中用list完成对象的转移，只不过list要用final来修饰
jdbc1.query(sql, new RowCallbackHandler() { //editing
            public void processRow(ResultSet rs) throws SQLException {
        VideoSearch vs = new VideoSearch();
        vs.setRECORDINGFILENAME(rs.getString("RECORDINGFILENAME"));
        vs.setCALLID(rs.getString("CALLID"));
        list.add(vs);

    }
    }

说明：
JDBCTemplate的使用方法:
在ApplicationContext.xml中定义一个jdbcTemplate的节点，使用POJO注入,获得注入后可以执行操作
不需要继承什么基类

<bean id="jdbcTemplate"
class="org.springframework.jdbc.core.JdbcTemplate">            
      <property name="dataSource" ref="dataSource"/>         
</bean>

SqlRowSet rs = jdbcTemplate.queryForRowSet(sql, params);
jdbcTemplate有很多的ORM化回调操作将返回结果转为对象列表，
但很多时候还是需要返回ResultSet，Spring有提供一个类似ResultSet的，实现JDBC3.0 RowSet接口的Spring SqlRowSet
注意

jdbcTemplate没有queryForString方法。

利用spring 的getJdbcTemplate().queryForMap如果返回空集，就会报
org.springframework.dao.EmptyResultDataAccessException: Incorrect result size: expected 1, actual 0 的异常，解决办法就是捕捉这个异常然后返回null即可。

Java代码

try{
return getJdbcTemplate().queryForMap("select s.fb,s.pb,s.tu,s.qt,s.fbc,s.pbc,s.tt,s.ft,s.lt from gbc$view_user s where s.ud = ? and ad= ?", new Object[]{ud,ad});
}catch (EmptyResultDataAccessException e) {
return null;
}

解决方案在queryForMap的地方 try catch 一下即可。

// hack
@Override
public Map<String, Object> queryForMap(String sql, Object... args) {
List<Map<String, Object>> result = queryForList(sql, args);
if (result != null && result.size() > 0)
return result.get(0);
else
return null; // or new HashMap<String, Object>();
}

public List<List<Object>> queryForArrays(String sql, Object... args) {
f inal List<List<Object>> arrays = new ArrayList<List<Object>>();
jdbcTemplate.query(sql, args, new RowCallbackHandler() {
// 不需要手动调用rs.next

public void processRow(ResultSet rs) throws SQLException {
List<Object> row = new ArrayList<Object>();
int count = rs.getMetaData().getColumnCount();
for (int col = 1; col <= count; col++) {
row.add(rs.getObject(col));
}
arrays.add(row);
}
});
return arrays;
}

示例：获取所有所有用户名
@SuppressWarnings("unchecked")
public List<String> getCustomerNames(){
return this.jdbcTemplate.queryForList("SELECT name FROM customer", String.class);
}

很好的文档:

http://wenku.baidu.com/link?url=kNso3A0jh_u0HSjTC5YohluDp3Xfow3_GDgstJlyrrIqSJfYRpxJcccZeEBkKlyH5PxBQZvAAkkXf_5hNFSFJZ2EotvBzKUv46XaporyaTC

The method queryForInt(String) is undefined for the type JdbcTemplate

String SQL1 = "select count(*) from issues";
int row1 = jdbcTemplateObject.queryForInt(SQL1);

queryForInt由于已经废弃，改成 queryForObject(String sql, Class<T> requiredType) with Integer.class as requiredType。

Upgrading Spring version and noticed that queryForInt() is deprecated, what should be replaced by?

Java

  private boolean isUserExists(String username) {

        String sql = "SELECT count(*) FROM USERS WHERE username = ?";
        boolean result = false;

        //The method queryForInt(String, Object...) from the type JdbcTemplate is deprecated
        int count = getJdbcTemplate().queryForInt(sql, new Object[] { username });
			
	if (count > 0) {
		result = true;
	}

	return result;
  }

Solution

Both queryForInt() and queryForLong() are deprecated since version 3.2.2 (correct me if mistake). To fix it, replace the code with queryForObject(String, Class).

Java

  private boolean isUserExists(String username) {

        String sql = "SELECT count(*) FROM USERS WHERE username = ?";
	boolean result = false;

	int count = getJdbcTemplate().queryForObject(
                        sql, new Object[] { username }, Integer.class);
			
	if (count > 0) {
		result = true;
	}

	return result;
  }

     
    /** 
     * 使用反射的基本行映射器 
     * @param name 
     * @return 
     */  
    public static User test1(String name){  
        JdbcTemplate jdbc = new JdbcTemplate(Temple.getDataSource());  
        String sql = "select id,name,money,birthday from user where id=?";  
        Object[] args = new Object[] {name};  
        Object use = jdbc.queryForObject(sql, args,new BeanPropertyRowMapper(User.class));  
        return (User)use;  
    }  
      
    /** 
     * 查询返回多个结果 
     * @param id 
     * @return 
     */  
    public static List<User> test2(int id){  
        JdbcTemplate jdbc = new JdbcTemplate(Temple.getDataSource());  
        String sql = "select id,name,money,birthday from user where id<?";  
        Object[] args = new Object[] {id};  
        @SuppressWarnings("unchecked")  
        List<User> use = jdbc.query(sql, args,new BeanPropertyRowMapper(User.class));  
        return use;  
    }

Review the Spring source code.

JdbcTemplate.java

Java

package org.springframework.jdbc.core;

public class JdbcTemplate extends JdbcAccessor implements JdbcOperations {

  //...
  @Deprecated
  public long queryForLong(String sql, Object... args) throws DataAccessException {
	Number number = queryForObject(sql, args, Long.class);
	return (number != null ? number.longValue() : 0);
  }

  @Deprecated
  public int queryForInt(String sql, Object... args) throws DataAccessException {
	Number number = queryForObject(sql, args, Integer.class);
	return (number != null ? number.intValue() : 0);
  }




执行DDL与更新   execute("create table test …")
执行update或者insert可以使用update方法

public class PersonDaoImpl implements PersonDao{
private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource) {
jdbcTemplate = new JdbcTemplate(dataSource);
}
public Person find (long id ){
List result = jdbcTemplate.queryForList("select * from Person");
jdbcTemplate.execute("create table test(test varchar2(200))");//DDL与更新
jdbcTemplate.update("insert into person values(?,?,?)",new Object[]{"","",""});//update或者insert
}
}

JdbcTemplate主要提供以下五类方法：

execute方法：可以用于执行任何SQL语句，一般用于执行DDL语句；
update方法及batchUpdate方法：update方法用于执行新增、修改、删除等语句；batchUpdate方法用于执行批处理相关语句；
query方法及queryForXXX方法：用于执行查询相关语句；
call方法：用于执行存储过程、函数相关语句。

参考:Spring JdbcTemplate方法详解

NamedParameterJdbcTemplate

在经典的 JDBC 用法中, SQL 参数是用占位符 ? 表示,并且受到位置的限制. 定位参数的问题在于, 一旦参数的顺序发生变化, 就必须改变参数绑定.

在 Spring JDBC 框架中, 绑定 SQL 参数的另一种选择是使用具名参数(named parameter).

那么什么是具名参数？

具名参数: SQL 按名称(以冒号开头)而不是按位置进行指定. 具名参数更易于维护, 也提升了可读性. 具名参数由框架类在运行时用占位符取代

具名参数只在 NamedParameterJdbcTemplate 中得到支持。

在 SQL 语句中使用具名参数时, 可以在一个 Map 中提供参数值, 参数名为键

也可以使用 SqlParameterSource 参数

批量更新时可以提供 Map 或 SqlParameterSource 的数组

/*
     * 可以为参数取名字:ln,:email,:deptid
     * 优点：如果有多个参数，不用去纠结于参数的位置顺序，直接对应参数名，便于维护
     * 缺点：较为麻烦
     */
    @Test
    public void testNamedParameterJdbcTemplate(){
        //之前不适用具名参数的用法：
        //String sql="insert employee(last_name,email,dept_id) values(?,?,?)";
        //我们给予参数赋值必须依赖于?的顺序
         
        // 使用具名参数的用法：
        String sql="insert employee(last_name,email,dept_id) values(:ln,:email,:deptid)";
        Map<String,Object> paramMap=new HashMap<String, Object>();
        paramMap.put("ln", "超级无敌银河最强临时工");
        paramMap.put("email", "super@qq.com");
        paramMap.put("deptid", 4);
        namedParameterJdbcTemplate.update(sql,paramMap);
    }

不过，这种方法还是有比较麻烦的地方，我们需要在map对象中逐一指定参数。

这时候，你可能不禁感慨，还是ORM框架好，类属属性能够自动与数据库表的字段映射。这里Spring JDBC在具名参数赋值时也考虑了类似的解决方法。

下面，我来介绍具名参数的第二种方法：

我们需要将具名参数定义为与类的属性名称一样的名字，然后，可以创建一个相应的类的对象，并调用相应属性的set方法赋值，之后，我们就调用update的另一个重载方法：

/*
     * 使用具名参数时，可以使用int org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(String sql, SqlParameterSource paramSource) throws DataAccessException
        方法进行更新操作：
        1. SQL语句中的具名参数与类的属性名一致
        2. 使用接口SqlParameterSource的BeanPropertySqlParameterSource实现类作为参数
         
 
     */
    @Test
    public void testNamedParameterJdbcTemplate2(){
        //之前不适用具名参数的用法：
        //String sql="insert employee(last_name,email,dept_id) values(?,?,?)";
        //我们给予参数赋值必须依赖于?的顺序
         
        // 使用具名参数的用法：
        String sql="insert employee(last_name,email,dept_id) values(:lastName,:email,:deptId)";
        EmployeeBean2 e=new EmployeeBean2();
        e.setLastName("haha");
        e.setEmail("haha@qq.com");
        e.setDeptId(4);
        SqlParameterSource sqlParameterSource=new BeanPropertySqlParameterSource(e);
        namedParameterJdbcTemplate.update(sql,sqlParameterSource);
    }

Spring Jdbc批量操作

http://blogs.aboutit.cn/index.php/archives/28.html

Spring 常用批量操作有很多种方法，但是大多数人知道一种或者其中的几种，先总结一些spring JDBC常用的批量操作的方法及其效率；

使用JDBCTemplate 进行基本的批量操作

这种方法是网上大多数采用的方法，但是在实际应用中我感觉不太方便，这个方法能不能做成一个通用的接口呢？一直没有仔细研究过

public class JdbcActorDao implements ActorDao {
  private JdbcTemplate jdbcTemplate;

  public void setDataSource(DataSource dataSource) {
    this.jdbcTemplate = new JdbcTemplate(dataSource);
  }

  public int[] batchUpdate(final List<Actor> actors) {
    int[] updateCounts = jdbcTemplate.batchUpdate("update t_actor set first_name = ?, " +
        "last_name = ? where id = ?",
      new BatchPreparedStatementSetter() {
        public void setValues(PreparedStatement ps, int i) throws SQLException {
            ps.setString(1, actors.get(i).getFirstName());
            ps.setString(2, actors.get(i).getLastName());
            ps.setLong(3, actors.get(i).getId().longValue());
          }

          public int getBatchSize() {
            return actors.size();
          }
        });
    return updateCounts;
  }

  // ... additional methods
}

二、使用List集合形式的参数的批量操作

如果使用List集合来进行批量操作，这种方法是比较合适的，spring jdbc core 包中提供了一个SqlParamterSource 对象，然后使用

SqlParameterSourceUtils.createBatch

这个方法，把javabean的list 转化成array，spring会循环的进行取值；

public class JdbcActorDao implements ActorDao {
  private NamedParameterTemplate namedParameterJdbcTemplate;

  public void setDataSource(DataSource dataSource) {
    this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
  }

  public int[] batchUpdate(final List<Actor> actors) {
    SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(actors.toArray());
    int[] updateCounts = namedParameterJdbcTemplate.batchUpdate(
        "update t_actor set first_name = :firstName, last_name = :lastName where id = :id",
        batch);
    return updateCounts;
  }

  // ... additional methods
}

当然，你还可以使用类似的方法来进行批量操作，如下代码：（代码来自官方网站示例）

public class JdbcActorDao implements ActorDao {

  private JdbcTemplate jdbcTemplate;

  public void setDataSource(DataSource dataSource) {
    this.jdbcTemplate = new JdbcTemplate(dataSource);
  }

  public int[] batchUpdate(final List<Actor> actors) {
    List<Object[]> batch = new ArrayList<Object[]>();
    for (Actor actor : actors) {
      Object[] values = new Object[] {
          actor.getFirstName(),
          actor.getLastName(),
          actor.getId()};
      batch.add(values);
    }
    int[] updateCounts = jdbcTemplate.batchUpdate(
        "update t_actor set first_name = ?, last_name = ? where id = ?",
        batch);
    return updateCounts;
  }

  // ... additional methods

}

---------------------

public void testBatchUpdate3() {
NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
String insertSql = "insert into test(name) values(:myName)";
UserModel model = new UserModel();
model.setMyName("name5");
SqlParameterSource[] params = SqlParameterSourceUtils.createBatch(new Object[] {model, model});
namedParameterJdbcTemplate.batchUpdate(insertSql, params);
Assert.assertEquals(2, jdbcTemplate.queryForInt("select count(*) from test"));
}

通过batchUpdate(String sql, SqlParameterSource[] batchArgs)方法进行命名参数批处理，batchArgs指定批处理数据集。SqlParameterSourceUtils.createBatch用于根据JavaBean对象或者Map创建相应的BeanPropertySqlParameterSource或MapSqlParameterSource。

 SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(actors.toArray());
        int[] updateCounts = simpleJdbcTemplate.batchUpdate(
                "update t_actor set first_name = :firstName, last_name = :lastName where id = :id",
                batch);

JDBC报错:Value '0000-00-00' can not be represented as java.sql.Date

当我从sql读出date类型时，有数据为0000-00-00,报上面的错误。这是因为 “0000-00-00”在mysql中是作为一个特殊值存在的，但是在Java中， java.sql.Date 会被视为不合法的值，被JVM认为格式不正确。

解决办法：

在jdbc的url加上 zeroDateTimeBehavior参数：参考：http://zhaohe162.blog.163.com/blog/static/3821679720110261248540/

datasource.url=jdbc:mysql://localhost:3306/pe?useUnicode=true&characterEncoding=gbk&zeroDateTimeBehavior=convertToNull

对于值为0000-00-00 00:00:00（默认值）的纪录，根据不同的配置，会返回不同的结果：

不配置：默认返回异常

zeroDateTimeBehavior=round 0001-01-01 00:00:00.0

zeroDateTimeBehavior=convertToNull null

Spring Jdbc

Solution

NamedParameterJdbcTemplate

使用JDBCTemplate 进行基本的批量操作