JdbcTemplate（一）

 （一）

1、使用JdbcTemplate的execute()方法执行SQL语句

Java代码

1. jdbcTemplate.execute("CREATE TABLE USER (user_id integer, name varchar(100))");  

jdbcTemplate.execute("CREATE TABLE USER (user_id integer, name varchar(100))");

2、如果是UPDATE或INSERT,可以用update()方法。

Java代码

1. jdbcTemplate.update("INSERT INTO USER VALUES('"  
2.             + user.getId() + "', '"  
3.             + user.getName() + "', '"  
4.             + user.getSex() + "', '"  
5.             + user.getAge() + "')");  

jdbcTemplate.update("INSERT INTO USER VALUES('"            + user.getId() + "', '"            + user.getName() + "', '"            + user.getSex() + "', '"            + user.getAge() + "')");

3、带参数的更新

Java代码

1. jdbcTemplate.update("UPDATE USER SET name = ? WHERE user_id = ?", new Object[] {name, id});  

jdbcTemplate.update("UPDATE USER SET name = ? WHERE user_id = ?", new Object[] {name, id});

 

Java代码

1. jdbcTemplate.update("INSERT INTO USER VALUES(?, ?, ?, ?)", new Object[] {user.getId(), user.getName(), user.getSex(), user.getAge()});  

jdbcTemplate.update("INSERT INTO USER VALUES(?, ?, ?, ?)", new Object[] {user.getId(), user.getName(), user.getSex(), user.getAge()});

4、使用JdbcTemplate进行查询时，使用queryForXXX()等方法

Java代码

1. int count = jdbcTemplate.queryForInt("SELECT COUNT(*) FROM USER");  

int count = jdbcTemplate.queryForInt("SELECT COUNT(*) FROM USER");

 

Java代码

1. String name = (String) jdbcTemplate.queryForObject("SELECT name FROM USER WHERE user_id = ?", new Object[] {id}, java.lang.String.class);  

String name = (String) jdbcTemplate.queryForObject("SELECT name FROM USER WHERE user_id = ?", new Object[] {id}, java.lang.String.class);

 

Java代码

1. List rows = jdbcTemplate.queryForList("SELECT * FROM USER");  

List rows = jdbcTemplate.queryForList("SELECT * FROM USER");

 

Java代码

1. List rows = jdbcTemplate.queryForList("SELECT * FROM USER");   
2. Iterator it = rows.iterator();   
3. while(it.hasNext()) {   
4.      Map userMap = (Map) it.next();   
5.      System.out.print(userMap.get("user_id") + "/t");   
6.      System.out.print(userMap.get("name") + "/t");   
7.      System.out.print(userMap.get("sex") + "/t");   
8.      System.out.println(userMap.get("age") + "/t");   
9. }  

List rows = jdbcTemplate.queryForList("SELECT * FROM USER"); Iterator it = rows.iterator(); while(it.hasNext()) {     Map userMap = (Map) it.next();     System.out.print(userMap.get("user_id") + "/t");     System.out.print(userMap.get("name") + "/t");     System.out.print(userMap.get("sex") + "/t");     System.out.println(userMap.get("age") + "/t"); }

JdbcTemplate将我们使用的JDBC的流程封装起来，包括了异常的捕捉、SQL的执行、查询结果的转换等等。spring大量使用Template Method模式来封装固定流程的动作，XXXTemplate等类别都是基于这种方式的实现。
除了大量使用Template Method来封装一些底层的操作细节，spring也大量使用callback方式类回调相关类别的方法以提供JDBC相关类别的功能，使传统的JDBC的使用者也能清楚了解spring所提供的相关封装类别方法的使用。

JDBC的PreparedStatement

Java代码

1. final String id = user.getId();   
2. final String name = user.getName();   
3. final String sex = user.getSex() + "";   
4. final int age = user.getAge();   
5.   
6. jdbcTemplate.update("INSERT INTO USER VALUES(?, ?, ?, ?)",   
7.                      new PreparedStatementSetter() {   
8.                          public void setValues(PreparedStatement ps) throws SQLException {   
9.                               ps.setString(1, id);   
10.                               ps.setString(2, name);             
11.                               ps.setString(3, sex);   
12.                               ps.setInt(4, age);   
13.                           }   
14.                       });  

final String id = user.getId(); final String name = user.getName(); final String sex = user.getSex() + ""; final int age = user.getAge();  jdbcTemplate.update("INSERT INTO USER VALUES(?, ?, ?, ?)",                      new PreparedStatementSetter() {                          public void setValues(PreparedStatement ps) throws SQLException {                              ps.setString(1, id);                              ps.setString(2, name);                                        ps.setString(3, sex);                              ps.setInt(4, age);                          }                      });

 

Java代码

1. final User user = new User();   
2. jdbcTemplate.query("SELECT * FROM USER WHERE user_id = ?",   
3.                     new Object[] {id},   
4.                     new RowCallbackHandler() {   
5.                         public void processRow(ResultSet rs) throws SQLException {   
6.                              user.setId(rs.getString("user_id"));   
7.                              user.setName(rs.getString("name"));   
8.                              user.setSex(rs.getString("sex").charAt(0));   
9.                              user.setAge(rs.getInt("age"));   
10.                          }   
11.                      });  

final User user = new User(); jdbcTemplate.query("SELECT * FROM USER WHERE user_id = ?",                     new Object[] {id},                     new RowCallbackHandler() {                         public void processRow(ResultSet rs) throws SQLException {                             user.setId(rs.getString("user_id"));                             user.setName(rs.getString("name"));                             user.setSex(rs.getString("sex").charAt(0));                             user.setAge(rs.getInt("age"));                         }                     });

 

Java代码

1. class UserRowMapper implements RowMapper {   
2.     public Object mapRow(ResultSet rs, int index) throws SQLException {   
3.          User user = new User();   
4.   
5.          user.setId(rs.getString("user_id"));   
6.          user.setName(rs.getString("name"));   
7.          user.setSex(rs.getString("sex").charAt(0));   
8.          user.setAge(rs.getInt("age"));   
9.   
10.         return user;   
11.      }   
12. }   
13.   
14. public List findAllByRowMapperResultReader() {   
15.      String sql = "SELECT * FROM USER";   
16.     return jdbcTemplate.query(sql, new RowMapperResultReader(new UserRowMapper()));   
17. }  

class UserRowMapper implements RowMapper {     public Object mapRow(ResultSet rs, int index) throws SQLException {         User user = new User();          user.setId(rs.getString("user_id"));         user.setName(rs.getString("name"));         user.setSex(rs.getString("sex").charAt(0));         user.setAge(rs.getInt("age"));          return user;     } }  public List findAllByRowMapperResultReader() {     String sql = "SELECT * FROM USER";     return jdbcTemplate.query(sql, new RowMapperResultReader(new UserRowMapper())); }

在getUser(id)里面使用UserRowMapper

Java代码

1. public User getUser(final String id) throws DataAccessException {   
2.      String sql = "SELECT * FROM USER WHERE user_id=?";   
3.     final Object[] params = new Object[] { id };   
4.      List list = jdbcTemplate.query(sql, params, new RowMapperResultReader(new UserRowMapper()));   
5.   
6.     return (User) list.get(0);   
7. }  

public User getUser(final String id) throws DataAccessException {     String sql = "SELECT * FROM USER WHERE user_id=?";     final Object[] params = new Object[] { id };     List list = jdbcTemplate.query(sql, params, new RowMapperResultReader(new UserRowMapper()));      return (User) list.get(0); }

网上收集
org.springframework.jdbc.core.PreparedStatementCreator 返回预编译SQL 不能于Object[]一起用

Java代码

1. public PreparedStatement createPreparedStatement(Connection con) throws SQLException {   
2. return con.prepareStatement(sql);   
3. }  

public PreparedStatement createPreparedStatement(Connection con) throws SQLException {   return con.prepareStatement(sql);  }

1.增删改
org.springframework.jdbc.core.JdbcTemplate 类(必须指定数据源dataSource)

Java代码

1. template.update("insert into web_person values(?,?,?)",Object[]);  

template.update("insert into web_person values(?,?,?)",Object[]);

或

Java代码

1. template.update("insert into web_person values(?,?,?)",new PreparedStatementSetter(){ 匿名内部类 只能访问外部最终局部变量   
2.   
3. public void setValues(PreparedStatement ps) throws SQLException {   
4.    ps.setInt(index++,3);   
5. });  

template.update("insert into web_person values(?,?,?)",new PreparedStatementSetter(){ 匿名内部类 只能访问外部最终局部变量    public void setValues(PreparedStatement ps) throws SQLException {    ps.setInt(index++,3);  });

org.springframework.jdbc.core.PreparedStatementSetter 接口 处理预编译SQL

Java代码

1. public void setValues(PreparedStatement ps) throws SQLException {   
2. ps.setInt(index++,3);   
3. }  

public void setValues(PreparedStatement ps) throws SQLException {   ps.setInt(index++,3);  }

2.查询JdbcTemplate.query(String,[Object[]/PreparedStatementSetter],RowMapper/RowCallbackHandler)
org.springframework.jdbc.core.RowMapper 记录映射接口 处理结果集

Java代码

1. public Object mapRow(ResultSet rs, int arg1) throws SQLException {   int表当前行数   
2.    person.setId(rs.getInt("id"));   
3. }   
4. List template.query("select * from web_person where id=?",Object[],RowMapper);  

public Object mapRow(ResultSet rs, int arg1) throws SQLException {   int表当前行数    person.setId(rs.getInt("id"));  }  List template.query("select * from web_person where id=?",Object[],RowMapper);

org.springframework.jdbc.core.RowCallbackHandler 记录回调管理器接口 处理结果集

Java代码

1. template.query("select * from web_person where id=?",Object[],new RowCallbackHandler(){   
2. public void processRow(ResultSet rs) throws SQLException {   
3.    person.setId(rs.getInt("id"));   
4. });  

template.query("select * from web_person where id=?",Object[],new RowCallbackHandler(){   public void processRow(ResultSet rs) throws SQLException {    person.setId(rs.getInt("id"));  });

（二）

Spring：JdbcTemplate使用指南

关键字: spring

转载：http://blog.csdn.net/nomads
1. Spring的基本概念

       Spring框架核心的思想就是建立一个Java对象的大工厂，用户只要给工厂一个指令，工厂就能将用户需要的对象根据配置文件组装好返还给用户。用户需要做的许多工作则可以写成简单的配置文件。

       2. 丑陋的JDBC代码

Connection con= null;

PreparedStatement pStmt=null;

ResultSet rs = null;

try{          

            con = ods.getConnection();

            String sql = "select * from admin";

            pStmt=con.prepareStatement(sql);           

            rs=pStmt.executeQuery();

            while(rs.next())

            {            }

}

catch(Exception ex) {

try{

         con.rollback();

    }catch(SQLException sqlex){

          sqlex.printStackTrace(System.out);

     }

     ex.printStackTrace();

}finally{

   try{

            rs.close();

            pStmt.close();

            con.close();

   }catch(Exception e){e.printStackTrace();}

}






       以上是常见的JDBC代码，简单的select语句也需要冗长的出错处理，并且每个函数都不断地重复同样的代码。



       3. JdbcTemplate的作用

       JdbcTemplate正是为了减少上述繁琐的代码而设计出来的。它是对JDBC的一种封装，抽象我们常用的一些方法。Simple and Stupid就是它的目标。下面是完成了刚才JDBC代码同样功能的JdbcTemplate的代码：

String sql = "select * from admin";

jdbcTemplate.query(sql,new RowCallbackHandler() {

         public void processRow(ResultSet rs) throws SQLException {   

                }

            } );






       环境搭建：

1. 数据库的配置

       本文使用Oracle数据库，新建表admin：

create table admin (
       ID number(10) primary key,
       NAME varchar2(64),
       PASSWORD varchar2(64)
)






       2. Spring配置

       JdbcTemplate的使用需要有DataSource的支持，所以在配置文件中，我们首先要配置一个OracleDataSource，然后在将这个DataSource配置到JdbcTemplate里。接着将JdbcTemplate配置进DAO层，最后将DAO配置进Model层。简要的关系如下：



       模型层 : User

数据访问层：UserDAO

JdbcTemplate

OracleDataSource

<!--[if !vml]--><!--[endif]-->



<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN"

    "http://www.springframework.org/dtd/spring-beans.dtd">

<beans>

    <bean id="dataSource" class="oracle.jdbc.pool.OracleDataSource">

        <property name="URL">

            <value>jdbc:oracle:thin:root/123@localhost:1521/XE</value>

        </property>

    </bean>

   

    <bean id="jdbcTemplate"

class="org.springframework.jdbc.core.JdbcTemplate">

        <property name="dataSource"><ref bean="dataSource"/></property>

    </bean>



    <bean id="userDAO" class="DAO.Imp.UserDAOImp">

        <property name="jdbcTemplate">

<ref bean="jdbcTemplate" />

</property>

    </bean>

   

    <bean id="user" class="Model.User">

        <property name="dao"><ref bean="userDAO"/></property>

    </bean>

</beans>




       3. 环境配置， 如图：

       <!--[if !vml]--><!--[endif]-->



使用方法：

<!--[if !supportLists]-->1.       <!--[endif]-->查找

多行查询：

class UserRowMapper implements RowMapper {

        public Object mapRow(ResultSet rs,int index) throws SQLException

        {

            User u = new User();

            u.setId(rs.getString("ID"));

            u.setName(rs.getString("Name"));

            u.setPassword(rs.getString("Password"));

            return u;

        }

    }

public List select(String where)

    {

        List list;       

        String sql = "select * from admin "+where;       

        list = jdbcTemplate.query(sql,new RowMapperResultReader(new UserRowMapper()));

        return list;

    }








List最终返回的是满足条件的User队列。



单行查询：

public User selectById(String id){

    String sql = "select * from admin where id=?";

    final User u = new User();

    final Object[] params = new Object[] {id};

    jdbcTemplate.query(sql, params, new RowCallbackHandler(){

                        public void processRow(ResultSet rs) throws SQLException {

                                 u.setId(rs.getString("ID"));

                                 u.setName(rs.getString("NAME"));

                                 u.setPassword(rs.getString("PASSWORD"));

                        }                     

    });         

    return u;

}








<!--[if !supportLists]-->2.       <!--[endif]-->插入

public void insert(User u)

{

     String sql = "insert into admin (ID,NAME,PASSWORD) values (admin_id_seq.nextval,?,?)";

     Object[] params = new Object[] {

                                                u.getName(),

                                                u.getPassword() };

     jdbcTemplate.update(sql,params);

}




admin_id_seq.nextval为Oracle设置好的序列，问号“?”被params里的数据依次替代，最终执行sql。



<!--[if !supportLists]-->3.       <!--[endif]-->修改

非常简单：

public void update(String how)

{

        jdbcTemplate.update(how);

    }





 


源代码：

User.class:

package Model;



import java.util.List;

import DAO.UserDAO;

/**

*  Model层

*

*

* @author 李嘉陵

* @since 2006-4-30 12:10:30

* @version 0.10a

**/



public class User {

    private String name;

    private String id;

    private String password;

    private UserDAO dao;

   

    public User()

    {

      

    }

   

    public User(String name, String password)

    {

       this.name = name;

       this.password = password;

    }

   

    public void setDao(UserDAO dao)

    {

        this.dao = dao;

    }

    public String getId() {

        return id;

    }

   

    public void setId(String id) {

        this.id = id;

    }

   

    public String getName() {

        return name;

    }

   

    public void setName(String name) {

        this.name = name;

    }

   

    public String getPassword() {

        return password;

    }

   

    public void setPassword(String password) {

        this.password = password;

    }

   

    public void getInfo(String id)

    {

        List list = dao.select("where id="+id);

        User u = (User) list.get(0);

       

        this.id=id;

        this.name = u.getName();

        this.password = u.getPassword();

       

    }

   

    public void insert()

    {

        dao.insert(this);

    }

   

    public void update(String how)

    {

        dao.update(how);

    }

   

    public void update()

    {

        dao.update("update admin set name='"+name+"', password='"+password+"' where id="+id);

    }

   

    public List selectWithTemp(String where)

    {

        return dao.select(where);

    }

   

    public void selectWithTemp()

    {

        dao.selectWithTemp();

    }

   

    public User selectById(String id)

    {

       return dao.selectById(id);

    }

   

    public void insertUsers(List users)

    {

       dao.insertUsers(users);

    }

}






UserDAO.class :

package DAO;



import java.util.List;



import Model.User;



/**

* DAO层接口

*

*

* @author 李嘉陵

* @since 2006-4-30 8:40:56

* @version 0.10a

**/



public interface UserDAO {

    public void select();

    public void test();

    public void selectWithTemp();

    public List select(String where);

    public void update(String how);

    public void insert(User u);

    public User selectById(String id);

    public int[] insertUsers(final List users);

   

}




UserDAOImp.class:

package DAO.Imp;





import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.List;



import org.springframework.jdbc.core.BatchPreparedStatementSetter;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.core.RowCallbackHandler;

import org.springframework.jdbc.core.RowMapper;

import org.springframework.jdbc.core.RowMapperResultReader;



import DAO.UserDAO;

import Model.User;



/**

*  DAO层的实现

*

*

* @author 李嘉陵

* @since 2006-4-30 8:41:26

* @version 0.10a

**/



public class UserDAOImp implements UserDAO{

   

    private JdbcTemplate jdbcTemplate;

   

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate)

    {

        this.jdbcTemplate = jdbcTemplate;

    }

   

    class UserRowMapper implements RowMapper

    {

        public Object mapRow(ResultSet rs,int index) throws SQLException

        {

            User u = new User();

            u.setId(rs.getString("ID"));

            u.setName(rs.getString("Name"));

            u.setPassword(rs.getString("Password"));



            return u;

        }

    }

   

    public void selectWithTemp()

    {

        String sql = "select * from admin";

              

        jdbcTemplate.query(sql,new RowCallbackHandler() {

                public void processRow(ResultSet rs) throws SQLException {

                System.out.println("ID: "+rs.getString("ID")+"   Name: "+rs.getString("name"));

                }

            } );



    }

   

    public List select(String where)

    {

        List list;

        String sql = "select * from admin "+where;

        list = jdbcTemplate.query(sql,new RowMapperResultReader(new UserRowMapper()));

        return list;

    }

   

    public User selectById(String id)

    {

       String sql = "select * from admin where id=?";

       final User u = new User();

       final Object[] params = new Object[] {id};

      

       jdbcTemplate.query(sql,params, new RowCallbackHandler(){

                         public void processRow(ResultSet rs) throws SQLException {

                                   u.setId(rs.getString("ID"));

                                   u.setName(rs.getString("NAME"));

                                   u.setPassword(rs.getString("PASSWORD"));

                         }

       });

      

       return u;

    }

   

    public void update(String how)

    {

        String sql = how;

        jdbcTemplate.update(sql);

    }

   

    public void insert(User u)

    {

        String sql = "insert into admin (ID,NAME,PASSWORD) values (admin_id_seq.nextval,?,?)";

        Object[] params = new Object[] {

                                                   u.getName(),

                                                   u.getPassword()};

        jdbcTemplate.update(sql,params);

    }

  

}






UserAction.class:

//测试类

public class UserAction {

    public static void main(String[] args)

    {

        Resource resource=new ClassPathResource("beans.xml");

        BeanFactory factory = new XmlBeanFactory(resource);       

        User user = (User) factory.getBean("user");   

       

        user.selectWithTemp();

    }

}
package com.jdsy.crscal.oper.service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.RowMapper;

public class OperTicketService {

public List getFileName(){
  List list =  new ArrayList();
  String sql = "select filename, " +
    "operateman," +
    "operatetime, " +
    "count(0) total," +
    "sum(decode(sendflag, 1, 1, 0)) cls," +
    "sum(decode(sendflag, 1, 0, 1)) wcls " +
    "from oper_ticket where sendflag = 0 group by filename, operateman, operatetime";
  list = (List) getJdbcTemplate().query(sql, new RowMapper(){
   public Object mapRow(ResultSet rs, int index) throws SQLException {
    // TODO Auto-generated method stub
    Map map = new HashMap();
    map.put("fileName", rs.getString(1));
    map.put("operateMan", rs.getString(2));
    map.put("operateTime", rs.getString(3));
    map.put("total", rs.getString(4));
    map.put("", rs.getString(3));
    return map;
   }
    });
 
  return list;
 }

}

Spring配置文件：

<bean id="operTicketService" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
  <property name="transactionManager">
   <ref bean="transactionManager"/>
  </property>
  <property name="target">
   <bean class="com.jdsy.crscal.oper.service.OperTicketService">
    <property name="operTicketDAO">
     <ref bean="OperTicketDAO"/>
    </property>
    <property name="jdbcTemplate">
     <ref bean="jdbcTemplate"/>
    </property>
   </bean>
  </property>
  <property name="transactionAttributes">
   <props>
    <prop key="create*">PROPAGATION_REQUIRED</prop>
    <prop key="get*">PROPAGATION_REQUIRED</prop>
   </props>
  </property>
 </bean>

 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/kevin_w_l/archive/2007/08/13/1740559.aspx