org.springframework.jdbc.core.JdbcTemplate 常用操作

最新推荐文章于 2024-08-29 11:32:48 发布

老年张三丰

最新推荐文章于 2024-08-29 11:32:48 发布

阅读量9.9k

点赞数 1

分类专栏： mysql 文章标签： JdbcTempla

本文链接：https://blog.csdn.net/Mrzhang__/article/details/53217304

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

1、单条数据结果集的处理(RowCallbackHandler)

public Agent getAgent(final int id) {
        String sql = "select * from t_agent where id = ?";
        final Agent agent = new Agent();
        jdbcTemplate.query(sql, new Object[] { id }, new RowCallbackHandler() {

            public void processRow(ResultSet rs) throws SQLException {
                agent.setAge(rs.getInt("age"));
                agent.setName(rs.getString("name"));
                agent.setSex(rs.getString("sex"));
            }
        });
        return agent;
    }

2、多条数据结果集的处理(RowCallbackHandler)

public List<Agent> getAgent(final int fromId , final int toId) {
        String sql = "select * from t_agent where id between ? and ?";
        final List<Agent> agents = new ArrayList<Agent>();
        jdbcTemplate.query(sql, new Object[] { fromId,toId }, new RowCallbackHandler() {

            public void processRow(ResultSet rs) throws SQLException {
                Agent agent = new Agent();
                agent.setAge(rs.getInt("age"));
                agent.setName(rs.getString("name"));
                agent.setSex(rs.getString("sex"));
                agents.add(agent);
            }
        });
        return agents;
    }

3、使用RowMapper映射多行数据

public List<Agent> getAgent(final int fromId, final int toId){
        final String sql = "select userName,sex,age from t_agent where id between ? and ?";

        return jdbcTemplate.query(sql, new Object[]{fromId , toId}, new RowMapper<Agent>() {

            public Agent mapRow(ResultSet rs, int rowNum) throws SQLException {
                Agent agent = new Agent();
                agent.setAge(rs.getInt("age"));
                agent.setName(rs.getString("userName"));
                agent.setSex(rs.getString("sex"));
                return agent;
            }
        });
    }

4、批量插入操作(BatchPreparedStatementSetter)

public void batchUpdateInsert(final List<Agent> agents){
        final String sql = "insert into t_user (userName,sex,age) values(?,?,?)";
        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {

            public void setValues(PreparedStatement ps, int index) throws SQLException {
                Agent agent = agents.get(index);
                ps.setString(1, agent.getName());
                ps.setString(2, agent.getSex());
                ps.setInt(3, agent.getAge());

            }

            public int getBatchSize() {
                return agents.size();
            }
        });
    }

5、查询单值数据

public int queryForInt(){
    String sql = "select count(*) from t_agent";
    return jdbcTemplate.queryForInt(sql);
}

注、在编写SQL语句时，特意用大写方式编写SQL的关键字，
而用小写的形式编写表名，字段等非语义的元素是比较好的编码习惯，
Eclipse 转大写快捷键Ctrl + Shift + X，
转小写快捷键 Ctrl + Shift + Y。


6、调用存储函数

public int getViewPointNum(final int spaceId){
        String sql = "{call P_GET_VIEW_POINT_NUM(?,?)}";//调用存储过程的的SQL语句
        Integer num = (Integer) jdbcTemplate.execute(sql, new CallableStatementCallback() {

            public Integer doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
                cs.setInt(1, spaceId);//绑定参数
                cs.registerOutParameter(2, Types.INTEGER);//注册输出参数
                cs.execute();
                return cs.getInt(2);//获取输出参数的值
            }
        });
        return num;
    }

7、调用函数

//3.2.18 增加代理商坐席分组
    public int storedProc_addAgentGroup(final AgentUserInfo agent) {
        Integer result = (Integer) jdbcTemplate.execute(new CallableStatementCreator() {
            public CallableStatement createCallableStatement(Connection con) throws SQLException {
                String storedProc = "{?=call HD_SF_ADDGROUP(?,?,?,?,?)}";
                CallableStatement call = con.prepareCall(storedProc);
                call.registerOutParameter(1, Types.INTEGER); 注册输出参数的类型 
                call.setString(2, agent.getAgentname());// 设置输入参数的值   
                call.setString(3, agent.getUserphone());
                call.setInt(4, agent.getGroupid());
                call.setInt(5, agent.getState());
                call.setString(6, agent.getPhonenumber());
                return call;
            }
        }, new CallableStatementCallback() {
            public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
                cs.execute();
                System.out.println(cs.getInt(1));
                return cs.getInt(1);
            }
        });
        return result;
    }