jdbctemplate

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<Student> students = getSession().createSQLQuery("select * from student").list();
        //List<Student> 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<User> users = new ArrayList<User>();
        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<User> users = new ArrayList<User>();
        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<User> users = new ArrayList<User>();
        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<User> users = new ArrayList<User>();
        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<User> users = new ArrayList<User>();
        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<User> users = new ArrayList<User>();
        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<User> users = new ArrayList<User>();
        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<User> users = new ArrayList<User>();
        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万集合中--在发送
     * 
     *  <T> List<T> jdbcTemplate.query(psc, rowMapper);
     *    <T> List<T>jdbcTemplate.query(sql, rowMapper);
     *    <T> List<T>jdbcTemplate.query(sql, pss, rowMapper);
     *    
     *  下面这三个是常用的
     *    <T> List<T>jdbcTemplate.query(sql, rowMapper, args);
     *    <T> List<T>jdbcTemplate.query(sql, args, rowMapper);
     *    <T> List<T>jdbcTemplate.query(sql, args, argTypes, rowMapper);
     */
    @Test
    public void handle22(){//经常使用,推荐
        final String sql = "select * from student where male = ? and age > ? ";
        List<User> users = jdbcTemplate.query(sql,new RowMapper<User>() {
            @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<User> users = jdbcTemplate.query(sql,new Object[]{0,20},new RowMapper<User>() {
            @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<User>() {
            @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<User>() {
            @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<String, Object> 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<Integer>(){
            @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<String, Object> map = jdbcTemplate.queryForObject(sql, new Object[]{30,1},new RowMapper<Map<String, Object>>(){
            @Override
            public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                Map<String, Object> map = new HashMap<String, Object>();
                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<Integer[]>(){
            @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<Count>(){
            @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<Map<String, Object>> 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<Student> 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<Student> students = new ArrayList<Student>();
        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<Object[]> objects = new ArrayList<Object[]>(); 
        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<SqlParameter> sqlParameters = new ArrayList<SqlParameter>();
        Map<String, Object> 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<SqlParameter> sqlParameters = new ArrayList<SqlParameter>();
        final List<Student> students = new ArrayList<Student>();
        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<SqlParameter> sqlParameters = new ArrayList<SqlParameter>();
        sqlParameters.add(new SqlParameter("cage", Types.INTEGER));
        sqlParameters.add(new SqlOutParameter("number", Types.INTEGER));
        sqlParameters.add(new SqlOutParameter("name", Types.VARCHAR));
        Map<String, Object> 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<String,Integer> params = new HashMap<String, Integer>();
        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<Map<String, Object>>() {
            @Override
            public Map<String, Object> doInCallableStatement(
                    CallableStatement cs) throws SQLException,
                    DataAccessException {
                cs.execute();
                Map<String,Object> map = new HashMap<String, Object>();
                System.out.println(cs.getInt(2)+"========="+cs.getString(3));
                return map;
            }
        });
        
        
    }
    
    //分页
    
    @Test
    public void hanle43(){
        List<User> users = findUsers("578789",10, 10);
        for (User user : users) {
            System.out.println(user.getUserId()+"==="+user.getUsername());
        }
    }
    
    public List<User> 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<User> users = jdbcTemplate.query(sql+" limit ?,?", new RowMapper<User>(){
            @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;
    }
    

    
}