jdbctemplate-白红宇

jdbctemplate
阅读量：4927 次
发布时间：2019-06-11
本文共 27456 字，大约阅读时间需要 91 分钟。
package com.tz.jdbctemplate;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Types;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import org.apache.struts2.json.JSONException;import org.apache.struts2.json.JSONUtil;import org.junit.Test;import org.junit.runner.RunWith;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.context.ApplicationContext;import org.springframework.context.support.ClassPathXmlApplicationContext;import org.springframework.dao.DataAccessException;import org.springframework.jdbc.core.CallableStatementCallback;import org.springframework.jdbc.core.CallableStatementCreator;import org.springframework.jdbc.core.CallableStatementCreatorFactory;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.jdbc.core.PreparedStatementCreator;import org.springframework.jdbc.core.PreparedStatementSetter;import org.springframework.jdbc.core.RowCallbackHandler;import org.springframework.jdbc.core.RowMapper;import org.springframework.jdbc.core.SqlOutParameter;import org.springframework.jdbc.core.SqlParameter;import org.springframework.jdbc.datasource.DriverManagerDataSource;import org.springframework.jdbc.support.GeneratedKeyHolder;import org.springframework.jdbc.support.KeyHolder;import org.springframework.test.context.ContextConfiguration;import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;import com.tz.bean.User;@RunWith(SpringJUnit4ClassRunner.class)@ContextConfiguration(locations = "classpath:config/applicationContext.xml")public class TemplateTest {        @Autowired    private JdbcTemplate jdbcTemplate;        //明白的原理，会懂得配置，然后会懂得使用和开发，然后在深入    @Test    public void handle() throws SQLException{        DriverManagerDataSource dataSource = new DriverManagerDataSource();        dataSource.setDriverClassName("com.mysql.jdbc.Driver");        dataSource.setUrl("jdbc:mysql://localhost:3306/test");        dataSource.setUsername("root");        dataSource.setPassword("xiaoer");        Connection connection = dataSource.getConnection();        PreparedStatement statement = connection.prepareStatement("select * from student");        ResultSet rs = statement.executeQuery();        while(rs.next()){            System.out.println(rs.getInt("id")+"==="+rs.getString("name"));        }        rs.close();        statement.close();        connection.close();    }        @Test    public void handle2(){        DriverManagerDataSource dataSource = new DriverManagerDataSource();        dataSource.setDriverClassName("com.mysql.jdbc.Driver");        dataSource.setUrl("jdbc:mysql://localhost:3306/test");        dataSource.setUsername("root");        dataSource.setPassword("xiaoer");        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);        jdbcTemplate.query("select * from student", new RowCallbackHandler() {            @Override            public void processRow(ResultSet rs) throws SQLException {                System.out.println(rs.getInt("id")+"==="+rs.getString("name"));            }        });        //hibernate            //List
     
       students = getSession().createSQLQuery("select * from student").list();        //List
      
        students = getSession().createQuery("from student").list();    }            @Test    public void handle3(){        jdbcTemplate.query("select * from student", new RowCallbackHandler() {            @Override            public void processRow(ResultSet rs) throws SQLException {                System.out.println(rs.getInt("id")+"==="+rs.getString("name"));            }        });    }        @Test    public void handle4(){        ApplicationContext context = new ClassPathXmlApplicationContext("classpath:config/applicationContext.xml");        JdbcTemplate jdbcTemplate = (JdbcTemplate) context.getBean("jdbcTemplate");        jdbcTemplate.query("select * from student", new RowCallbackHandler() {            @Override            public void processRow(ResultSet rs) throws SQLException {                System.out.println(rs.getInt("id")+"==="+rs.getString("name"));            }        });    }            /*增加，修改，删除*/    @Test    public void handle5(){
   //几乎不用        //新增，修改，删除---update()        final String sql = "INSERT INTO student(name,age,male) VALUES(?,?,?)";//常量效率远远要高于普通变量        jdbcTemplate.update(new PreparedStatementCreator() {            @Override            public PreparedStatement createPreparedStatement(Connection con)                    throws SQLException {                PreparedStatement statement = con.prepareStatement(sql);                statement.setString(1, "zhangsan");                statement.setInt(2, 100);                statement.setInt(3, 1);                return statement;            }        });    }        @Test    public void handle6(){
   //不建议使用        //新增，修改，删除---update()        final String sql = "INSERT INTO student(name,age,male) VALUES('keke',56,1)";//常量效率远远要高于普通变量        jdbcTemplate.update(sql);    }        @Test    public void handle8(){
   //可以使用        final String sql = "INSERT INTO student(name,age,male) VALUES(?,?,?)";//常量效率远远要高于普通变量        //PreparedStatementSetter 就是专门给所有sql语句(insert update select delete)带有?赋值的匿名内部类。        jdbcTemplate.update(sql,new PreparedStatementSetter() {            @Override            public void setValues(PreparedStatement ps) throws SQLException {                ps.setString(1, "kekexxx");                ps.setInt(2, 587);                ps.setInt(3, 0);            }        });    }    @Test    public void handle7(){
   //经常使用        //新增，修改，删除---update()        final String sql = "INSERT INTO student(name,age,male) VALUES(?,?,?)";//常量效率远远要高于普通变量        jdbcTemplate.update(sql,"xxxxx",686,0);    }        @Test    public void handle9(){
   //经常使用        final String sql = "INSERT INTO student(name,age,male) VALUES(?,?,?)";//常量效率远远要高于普通变量        Object[] params = {"mark", 16,1};        jdbcTemplate.update(sql,params,new int[]{Types.VARCHAR,Types.INTEGER,Types.INTEGER});        //jdbcTemplate.update(sql, new Object[]{"mark", 16,1},new int[]{Types.VARCHAR,Types.INTEGER,Types.INTEGER});    }        //在新增的时候有时候需要返回主键    @Test    public void handle10(){        final String sql = "INSERT INTO student(name,age,male) VALUES(?,?,?)";//常量效率远远要高于普通变量        KeyHolder keyHolder = new GeneratedKeyHolder();        jdbcTemplate.update(new PreparedStatementCreator() {            @Override            public PreparedStatement createPreparedStatement(Connection con)                    throws SQLException {                PreparedStatement statement = con.prepareStatement(sql);                statement.setString(1, "zhangsan");                statement.setInt(2, 100);                statement.setInt(3, 1);                return statement;            }        }, keyHolder);        //返回你新增的主键        int key = keyHolder.getKey().intValue();        System.out.println("我添加的数据返回的主键是:======"+key);    }            @Test    public void handle11(){
   //经常使用        //新增，修改，删除---update()        final String sql = "UPDATE student SET `name`=?,age=? WHERE name=?";//常量效率远远要高于普通变量        jdbcTemplate.update(sql,"xiaoer",10,"小丑");    }        @Test    public void handle12(){
   //经常使用        //新增，修改，删除---update()        final String sql = "delete from student where id = ?";//常量效率远远要高于普通变量        jdbcTemplate.update(sql,7);    }                /*     * *************************************查询*******************************      * RowCallbackHandler:无返回值，在大数据量处理的一定使用RowCallbackHandler     * RowMapper：这个返回的是一个集合.     * 给所有的用户(100)用户发一条短信,RowCallbackHandler     * RowMapper---100万集合中--在发送     *      *  jdbcTemplate.query(psc, rch);     *    jdbcTemplate.query(sql, rch);     *    jdbcTemplate.query(sql, pss, rch);     *         *  下面这三个是常用的     *    jdbcTemplate.query(sql, rch, args);     *    jdbcTemplate.query(sql, args, rch);     *    jdbcTemplate.query(sql, args, argTypes, rch);     *      */    @Test    public void handle13(){
   //不常用        //oracle sqlservler mysql:查询表中所有的数据的时候，数据库服务器并不会把所有的数据都都放内存中，而在通过里面数据服务器里面的默认因子去装载到内存中         //oracle ==10---io()        final String sql = "select * from student where male = ?";        final List
       
         users = new ArrayList
        
         ();        jdbcTemplate.query(new PreparedStatementCreator() {            @Override            public PreparedStatement createPreparedStatement(Connection con)                    throws SQLException {                PreparedStatement statement = con.prepareStatement(sql);                statement.setInt(1, 0);                return statement;            }        }, new RowCallbackHandler() {            @Override            public void processRow(ResultSet rs) throws SQLException {                User user = new User();                user.setUserId(rs.getInt("id"));                user.setUsername(rs.getString("name"));                user.setAge(rs.getInt("age"));                users.add(user);            }        });                for (User user : users) {            System.out.println(user.getUserId()+"==="+user.getUsername()+"==="+user.getAge());        }    }            @Test    public void handle14(){
   //可能会使用，不建议        final String sql = "select * from student where male = 0";        final List
         
           users = new ArrayList
          
           (); jdbcTemplate.query(sql,new RowCallbackHandler() { @Override public void processRow(ResultSet rs) throws SQLException { User user = new User(); user.setUserId(rs.getInt("id")); user.setUsername(rs.getString("name")); user.setAge(rs.getInt("age")); users.add(user); } }); for (User user : users) { System.out.println(user.getUserId()+"==="+user.getUsername()+"==="+user.getAge()); } } @Test public void handle16(){ //可以使用,不推荐 final String sql = "select * from student where male = ?"; final List
           
             users = new ArrayList
            
             (); jdbcTemplate.query(sql,new PreparedStatementSetter() { @Override public void setValues(PreparedStatement ps) throws SQLException { ps.setInt(1, 0); } },new RowCallbackHandler() { @Override public void processRow(ResultSet rs) throws SQLException { User user = new User(); user.setUserId(rs.getInt("id")); user.setUsername(rs.getString("name")); user.setAge(rs.getInt("age")); users.add(user); } }); for (User user : users) { System.out.println(user.getUserId()+"==="+user.getUsername()+"==="+user.getAge()); } } @Test public void handle17(){ //经常使用,推荐 final String sql = "select * from student where male = ?"; final List
             
               users = new ArrayList
              
               (); jdbcTemplate.query(sql,new Object[]{0},new RowCallbackHandler() { @Override public void processRow(ResultSet rs) throws SQLException { User user = new User(); user.setUserId(rs.getInt("id")); user.setUsername(rs.getString("name")); user.setAge(rs.getInt("age")); users.add(user); } }); for (User user : users) { System.out.println(user.getUserId()+"==="+user.getUsername()+"==="+user.getAge()); } } @Test public void handle18(){ //经常使用,推荐 final String sql = "select * from student where male = ? "; final List
               
                 users = new ArrayList
                
                 (); jdbcTemplate.query(sql,new RowCallbackHandler() { @Override public void processRow(ResultSet rs) throws SQLException { User user = new User(); user.setUserId(rs.getInt("id")); user.setUsername(rs.getString("name")); user.setAge(rs.getInt("age")); users.add(user); } },0); for (User user : users) { System.out.println(user.getUserId()+"==="+user.getUsername()+"==="+user.getAge()); } } @Test public void handle19(){ //经常使用,推荐 final String sql = "select * from student where male = ? and age > ?"; final List
                 
                   users = new ArrayList
                  
                   (); jdbcTemplate.query(sql,new Object[]{0,20},new RowCallbackHandler() { @Override public void processRow(ResultSet rs) throws SQLException { User user = new User(); user.setUserId(rs.getInt("id")); user.setUsername(rs.getString("name")); user.setAge(rs.getInt("age")); users.add(user); } }); for (User user : users) { System.out.println(user.getUserId()+"==="+user.getUsername()+"==="+user.getAge()); } } @Test public void handle21(){ //经常使用,不推荐 final String sql = "select * from student where male = ? and age > ?"; final List
                   
                     users = new ArrayList
                    
                     (); jdbcTemplate.query(sql,new Object[]{0,20},new int[]{Types.INTEGER,Types.INTEGER},new RowCallbackHandler() { @Override public void processRow(ResultSet rs) throws SQLException { User user = new User(); user.setUserId(rs.getInt("id")); user.setUsername(rs.getString("name")); user.setAge(rs.getInt("age")); users.add(user); } }); for (User user : users) { System.out.println(user.getUserId()+"==="+user.getUsername()+"==="+user.getAge()); } } @Test public void handle20(){ //经常使用,推荐 final String sql = "select * from student where male = ? and age > ? "; final List
                     
                       users = new ArrayList
                      
                       (); jdbcTemplate.query(sql,new RowCallbackHandler() { @Override public void processRow(ResultSet rs) throws SQLException { User user = new User(); user.setUserId(rs.getInt("id")); user.setUsername(rs.getString("name")); user.setAge(rs.getInt("age")); users.add(user); } },0,20); for (User user : users) { System.out.println(user.getUserId()+"==="+user.getUsername()+"==="+user.getAge()); } } /** * * RowMapper---100万集合中--在发送 * * 
                       
                         List
                        
                          jdbcTemplate.query(psc, rowMapper); * 
                         
                           List
                          
                           jdbcTemplate.query(sql, rowMapper); * 
                           
                             List
                            
                             jdbcTemplate.query(sql, pss, rowMapper); * * 下面这三个是常用的 * 
                             
                               List
                              
                               jdbcTemplate.query(sql, rowMapper, args); * 
                               
                                 List
                                
                                 jdbcTemplate.query(sql, args, rowMapper); * 
                                 
                                   List
                                  
                                   jdbcTemplate.query(sql, args, argTypes, rowMapper); */ @Test public void handle22(){ //经常使用,推荐 final String sql = "select * from student where male = ? and age > ? "; List
                                   
                                     users = jdbcTemplate.query(sql,new RowMapper
                                    
                                     () { @Override public User mapRow(ResultSet rs, int index) throws SQLException { User user = new User(); user.setUserId(rs.getInt("id")); user.setUsername(rs.getString("name")); user.setAge(rs.getInt("age")); return user; } },0,20); for (User user : users) { System.out.println(user.getUserId()+"==="+user.getUsername()+"==="+user.getAge()); } } @Test public void handle23(){ //经常使用,推荐 final String sql = "select * from student where male = ? and age > ? "; List
                                     
                                       users = jdbcTemplate.query(sql,new Object[]{0,20},new RowMapper
                                      
                                       () { @Override public User mapRow(ResultSet rs, int index) throws SQLException { User user = new User(); user.setUserId(rs.getInt("id")); user.setUsername(rs.getString("name")); user.setAge(rs.getInt("age")); return user; } }); for (User user : users) { System.out.println(user.getUserId()+"==="+user.getUsername()+"==="+user.getAge()); } } //当个实体对象返回 @Test public void handle24(){ //经常使用,推荐 final String sql = "select * from student where id = ? "; User user = jdbcTemplate.query(sql,new Object[]{8},new RowMapper
                                       
                                        () { @Override public User mapRow(ResultSet rs, int index) throws SQLException { User user = new User(); user.setUserId(rs.getInt("id")); user.setUsername(rs.getString("name")); user.setAge(rs.getInt("age")); return user; } }).get(0); System.out.println(user.getUserId()+"==="+user.getUsername()+"==="+user.getAge()); } @Test public void handle25(){ //经常使用,推荐 final String sql = "select * from student where id = ? "; final User user = new User(); jdbcTemplate.query(sql,new Object[]{8},new RowCallbackHandler() { @Override public void processRow(ResultSet rs) throws SQLException { user.setUserId(rs.getInt("id")); user.setUsername(rs.getString("name")); user.setAge(rs.getInt("age")); } }); System.out.println(user.getUserId()+"==="+user.getUsername()+"==="+user.getAge()); } @Test public void hanle26(){ final String sql = "select * from student where id = ? "; User user = jdbcTemplate.queryForObject(sql, new RowMapper
                                        
                                         () { @Override public User mapRow(ResultSet rs, int rowNum) throws SQLException { User user = new User(); user.setUserId(rs.getInt("id")); user.setUsername(rs.getString("name")); user.setAge(rs.getInt("age")); return user; } },8); System.out.println(sql); System.out.println(user.getUserId()+"==="+user.getUsername()+"==="+user.getAge()); } @Test public void hanle27(){ final String sql = "select * from student where id = ? "; Map
                                         
                                           map = jdbcTemplate.queryForMap(sql,8); System.out.println(map); } //求总数(把常用的聚合函数sum,max,avg,自己去实现一次,日期，截取，替换这些常用sql要去总结和归纳) @Test public void handle28(){ final String sql = "SELECT count(1) from student"; jdbcTemplate.query(sql, new RowCallbackHandler() { @Override public void processRow(ResultSet rs) throws SQLException { System.out.println(rs.getInt(1)); } }); } @Test public void test2(){ sayHello("Nio","BUKENG","robinName"); } public void sayHello(String ...names){ for (String string : names) { System.out.println(string); } } /** * int queryForInt(sql) * int queryForInt(sql,Object...args) * int queryForInt(sql,Object[] params,int[] types) * */ @Test public void handle29(){ //第一种方式 //final String sql = "SELECT count(1) from student where age > 30 && male = 1"; //int count = jdbcTemplate.queryForInt(sql); final String sql = "SELECT count(1) from student where age > ? && male = ?"; int count =jdbcTemplate.queryForInt(sql,30,1); System.out.println("count=============="+count); int count2 = jdbcTemplate.queryForInt(sql, new Object[]{30,1},new int[]{Types.INTEGER,Types.INTEGER}); System.out.println("count2=============="+count2); } @Test public void handle30(){ final String sql = "SELECT count(1) from student where age > ? && male = ?"; Integer count = jdbcTemplate.queryForObject(sql, new Object[]{30,1},new RowMapper
                                          
                                           (){ @Override public Integer mapRow(ResultSet rs, int rowNum) throws SQLException { return rs.getInt(1); } }); System.out.println("================"+count); } //求年龄大于30岁的总数和综合，和平均数 @Test public void handle31(){ final String sql = "SELECT sum(age) as s,count(1) as c ,avg(age) from student where age > ? && male = ?"; Map
                                           
                                             map = jdbcTemplate.queryForObject(sql, new Object[]{30,1},new RowMapper
                                            
                                             
                                              >(){ @Override public Map
                                              
                                                mapRow(ResultSet rs, int rowNum) throws SQLException { Map
                                               
                                                 map = new HashMap
                                                
                                                 (); map.put("sum", rs.getInt(1)); map.put("count", rs.getInt(2)); map.put("avg", rs.getInt(3)); return map; } }); System.out.println("================"+map); } @Test public void handle32(){ final String sql = "SELECT sum(age) as s,count(1) as c ,avg(age) from student where age > ? && male = ?"; Integer[] integers = jdbcTemplate.queryForObject(sql, new Object[]{30,1},new RowMapper
                                                 
                                                  (){ @Override public Integer[] mapRow(ResultSet rs, int rowNum) throws SQLException { Integer[] integers = new Integer[3]; integers[0] = rs.getInt(1); integers[1] = rs.getInt(2); integers[2] = rs.getInt(3); return integers; } }); for (Integer integer : integers) { System.out.println("================"+integer); } } @Test public void handle33(){ final String sql = "SELECT sum(age) as s,count(1) as c ,avg(age) from student where age > ? && male = ?"; Count count= jdbcTemplate.queryForObject(sql, new Object[]{30,1},new RowMapper
                                                  
                                                   (){ @Override public Count mapRow(ResultSet rs, int rowNum) throws SQLException { Count count = new Count(); count.setSum(rs.getInt(1)); count.setCount(rs.getInt(2)); count.setAvg(rs.getInt(3)); return count; } }); System.out.println("================"+count.getSum()); System.out.println("================"+count.getCount()); System.out.println("================"+count.getAvg()); } @Test public void handle34() throws JSONException{ List
                                                   
                                                    
                                                     > maps = jdbcTemplate.queryForList("select * from student"); System.out.println(maps); //struts2 -json-lib.jar --xwork.jar String jsonString = JSONUtil.serialize(maps, null, null, false, true); System.out.println(jsonString); //[{id=1, name=单晨, age=17, male=1, createtime=2014-10-19 20:39:04.0, updatetime=null}, {id=2, name=keke, age=30, male=1, createtime=2014-10-19 20:39:25.0, updatetime=null}, {id=3, name=赵龙, age=22, male=1, createtime=2014-10-19 20:39:25.0, updatetime=null}, {id=4, name=白羊, age=21, male=0, createtime=2014-10-19 20:39:46.0, updatetime=null}, {id=6, name=xiaoer, age=10, male=0, createtime=2014-10-19 20:40:28.0, updatetime=null}, {id=8, name=keke, age=21, male=1, createtime=2015-02-26 23:40:33.0, updatetime=null}, {id=9, name=fei, age=1, male=1, createtime=2015-02-26 23:42:03.0, updatetime=null}, {id=10, name=zhangsan, age=100, male=1, createtime=2015-02-26 23:45:17.0, updatetime=null}, {id=11, name=keke, age=56, male=1, createtime=2015-02-26 23:46:37.0, updatetime=null}, {id=12, name=xxxxx, age=686, male=0, createtime=2015-02-26 23:47:59.0, updatetime=null}, {id=13, name=kekexxx, age=587, male=0, createtime=2015-02-26 23:53:27.0, updatetime=null}, {id=14, name=mark, age=16, male=1, createtime=2015-02-26 23:55:58.0, updatetime=null}, {id=15, name=mark, age=16, male=1, createtime=2015-02-26 23:57:47.0, updatetime=null}, {id=16, name=zhangsan, age=100, male=1, createtime=2015-02-27 00:01:26.0, updatetime=null}] //[{"id":1,"name":"单晨","age":17,"male":1,"createtime":"2014-10-19T20:39:04"},{"id":2,"name":"keke","age":30,"male":1,"createtime":"2014-10-19T20:39:25"},{"id":3,"name":"赵龙","age":22,"male":1,"createtime":"2014-10-19T20:39:25"},{"id":4,"name":"白羊","age":21,"male":0,"createtime":"2014-10-19T20:39:46"},{"id":6,"name":"xiaoer","age":10,"male":0,"createtime":"2014-10-19T20:40:28"},{"id":8,"name":"keke","age":21,"male":1,"createtime":"2015-02-26T23:40:33"},{"id":9,"name":"fei","age":1,"male":1,"createtime":"2015-02-26T23:42:03"},{"id":10,"name":"zhangsan","age":100,"male":1,"createtime":"2015-02-26T23:45:17"},{"id":11,"name":"keke","age":56,"male":1,"createtime":"2015-02-26T23:46:37"},{"id":12,"name":"xxxxx","age":686,"male":0,"createtime":"2015-02-26T23:47:59"},{"id":13,"name":"kekexxx","age":587,"male":0,"createtime":"2015-02-26T23:53:27"},{"id":14,"name":"mark","age":16,"male":1,"createtime":"2015-02-26T23:55:58"},{"id":15,"name":"mark","age":16,"male":1,"createtime":"2015-02-26T23:57:47"},{"id":16,"name":"zhangsan","age":100,"male":1,"createtime":"2015-02-27T00:01:26"}] } @Test //错误 public void handle35() throws JSONException{ //如果要想支持的话，那么久和hibernate一样了。 List
                                                     
                                                       students = jdbcTemplate.queryForList("select * from student", Student.class); String jsonString = JSONUtil.serialize(students, null, null, false, true); System.out.println(jsonString); } @Test public void handle36(){ //execute:动态创建数据库，表，索引，alert 存储过程，函数，触发器 jdbcTemplate.execute("create database keke"); } //批量处理（修改，删除，新增）为了解决大数量的导入和导出的时候，一些大批量量数据的的发送，100万发一条的短信 @Test public void hanler37(){ jdbcTemplate.query("select * from student",new RowCallbackHandler(){ @Override public void processRow(ResultSet rs) throws SQLException { //给学生发送短信 } }); } @Test public void hanler38(){ //我要把所有的学生的名称加上一个前缀tz final List
                                                      
                                                        students = new ArrayList
                                                       
                                                        (); jdbcTemplate.query("select * from student",new RowCallbackHandler(){ @Override public void processRow(ResultSet rs) throws SQLException { //给学生发送短信 Student student = new Student(); student.setId(rs.getInt("id")); student.setName(rs.getString("name")); students.add(student); } }); //如果牵涉到update 删除的时候一定要非常的谨慎条件十分满足。 //所有的数据删除一定要逻辑删除，而且数据库一定要每天必须一定要备份. //服务器的IO瓶颈，// for (Student student : students) {// jdbcTemplate.update("update student set name=? where id = ?","tz"+student.getName(),student.getId());// } //第一种写法// jdbcTemplate.batchUpdate("update student set name=? where id = ?", new BatchPreparedStatementSetter() {// @Override// public void setValues(PreparedStatement ps, int index) throws SQLException {// Student student = students.get(index);// ps.setString(1, student.getName());// ps.setInt(2, student.getId());// }// // @Override// public int getBatchSize() {// return students.size();// }// }); //第二种写法 List
                                                        
                                                          objects = new ArrayList
                                                         
                                                          (); for (Student student : students) { Object[] object = {student.getName()+"我爱你们",student.getId()}; objects.add(object); } jdbcTemplate.batchUpdate("update student set name=? where id = ?", objects); } //调用存储过程(单条记录和多条记录) @Test public void handle39(){ List
                                                          
                                                            sqlParameters = new ArrayList
                                                           
                                                            (); Map
                                                            
                                                              map = jdbcTemplate.call(new CallableStatementCreator() { @Override public CallableStatement createCallableStatement(Connection con) throws SQLException { CallableStatement statement = con.prepareCall("{call keke()}");// ResultSet rs = statement.executeQuery();// while(rs.next()){// // } return statement; } }, sqlParameters); System.out.println(map.get("#result-set-1")); } @Test public void handle40(){ List
                                                             
                                                               sqlParameters = new ArrayList
                                                              
                                                               (); final List
                                                               
                                                                 students = new ArrayList
                                                                
                                                                 (); jdbcTemplate.call(new CallableStatementCreator() { @Override public CallableStatement createCallableStatement(Connection con) throws SQLException { CallableStatement statement = con.prepareCall("{call keke()}"); ResultSet rs = statement.executeQuery(); Student student = null; while(rs.next()){ student= new Student(); student.setId(rs.getInt("id")); student.setName(rs.getString("name")); students.add(student); } return statement; } }, sqlParameters); for (Student student : students) { System.out.println(student.getId()+"==="+student.getName()); } } @Test public void hanle41(){ List
                                                                 
                                                                   sqlParameters = new ArrayList
                                                                  
                                                                   (); sqlParameters.add(new SqlParameter("cage", Types.INTEGER)); sqlParameters.add(new SqlOutParameter("number", Types.INTEGER)); sqlParameters.add(new SqlOutParameter("name", Types.VARCHAR)); Map
                                                                   
                                                                     map = jdbcTemplate.call(new CallableStatementCreator() { @Override public CallableStatement createCallableStatement(Connection con) throws SQLException { CallableStatement statement = con.prepareCall("{call USER_RELATIONS(?,?,?)}"); statement.setInt(1, 17); return statement; } }, sqlParameters); System.out.println(map.get("number")); System.out.println(map.get("name")); } @Test public void hanle42(){ CallableStatementCreatorFactory factory = new CallableStatementCreatorFactory("{call USER_RELATIONS(?,?,?)}"); factory.addParameter(new SqlParameter("cage", Types.INTEGER)); factory.addParameter(new SqlOutParameter("number", Types.INTEGER)); factory.addParameter(new SqlOutParameter("name", Types.VARCHAR)); Map
                                                                    
                                                                      params = new HashMap
                                                                     
                                                                      (); params.put("cage", 17); CallableStatementCreator creator = factory.newCallableStatementCreator(params); //====================================【出异常了哦!!!!】CallableStatementCallback; SQL [{call USER_RELATIONS(?,?,?)}]; Parameter number 1 is not an OUT parameter; nested exception is java.sql.SQLException: Parameter number 1 is not an OUT parameter jdbcTemplate.execute(creator,new CallableStatementCallback
                                                                      
                                                                       
                                                                        >() { @Override public Map
                                                                        
                                                                          doInCallableStatement( CallableStatement cs) throws SQLException, DataAccessException { cs.execute(); Map
                                                                         
                                                                           map = new HashMap
                                                                          
                                                                           (); System.out.println(cs.getInt(2)+"========="+cs.getString(3)); return map; } }); } //分页 @Test public void hanle43(){ List
                                                                           
                                                                             users = findUsers("578789",10, 10); for (User user : users) { System.out.println(user.getUserId()+"==="+user.getUsername()); } } public List
                                                                            
                                                                              findUsers(String password,int pageNo,int pageSize){ String sql = "select * from user where 1=1 "; String countSql ="select count(1) from user where 1=1 "; if(password!=null){ sql+=" and password = '"+password+"'"; countSql+=" and password = '"+password+"' "; } System.out.println(countSql); List
                                                                             
                                                                               users = jdbcTemplate.query(sql+" limit ?,?", new RowMapper
                                                                              
                                                                               (){ @Override public User mapRow(ResultSet rs, int index) throws SQLException { User user = new User(); user.setUserId(rs.getInt("id")); user.setUsername(rs.getString("username")); return user; } },pageNo,pageSize); Integer totalCount = jdbcTemplate.queryForObject(countSql,Integer.class); System.out.println("总页数是:"+totalCount); return users; } }