jdbctemplate 增删查改sql

1增删改都使用update方法

<span style="font-family:KaiTi_GB2312;font-size:18px;">@Testpublic void handle7(){//经常使用//新增，修改，删除---update()final String sql = "INSERT INTO student(name,age,male) VALUES(?,?,?)";//常量效率远远要高于普通变量jdbcTemplate.update(sql,"xxxxx",686,0);}@Testpublic 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});}//在新增的时候有时候需要返回主键@Testpublic void handle10(){final String sql = "INSERT INTO student(name,age,male) VALUES(?,?,?)";//常量效率远远要高于普通变量KeyHolder keyHolder = new GeneratedKeyHolder();jdbcTemplate.update(new PreparedStatementCreator() {@Overridepublic 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);}@Testpublic void handle11(){//经常使用//新增，修改，删除---update()final String sql = "UPDATE student SET `name`=?,age=? WHERE name=?";//常量效率远远要高于普通变量jdbcTemplate.update(sql,"xiaoer",10,"小丑");}@Testpublic void handle12(){//经常使用//新增，修改，删除---update()final String sql = "delete from student where id = ?";//常量效率远远要高于普通变量jdbcTemplate.update(sql,7);}</span>

2查询

RowCallbackHandler:无返回值，在大数据量处理的一定使用RowCallbackHandler

RowMapper：这个返回的是一个集合

查询有六个方法可以使用

 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);

<span style="font-family:KaiTi_GB2312;font-size:18px;">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() {@Overridepublic 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());}}@Testpublic void handle18(){//经常使用,推荐final String sql = "select * from student where male = ? ";final List<User> users = new ArrayList<User>();jdbcTemplate.query(sql,new RowCallbackHandler() {@Overridepublic 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());}}@Testpublic 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() {@Overridepublic 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());}}@Testpublic 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() {@Overridepublic 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());}}@Testpublic void handle20(){//经常使用,推荐final String sql = "select * from student where male = ? and age > ? ";final List<User> users = new ArrayList<User>();jdbcTemplate.query(sql,new RowCallbackHandler() {@Overridepublic 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());}}</span>

以上是rowcallback的查询语句，下面使用rowmapper

<span style="font-family:KaiTi_GB2312;font-size:18px;">@Testpublic void handle22(){//经常使用,推荐final String sql = "select * from student where male = ? and age > ? ";List<User> users = jdbcTemplate.query(sql,new RowMapper<User>() {@Overridepublic 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());}}@Testpublic 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>() {@Overridepublic 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());}}//当个实体对象返回@Testpublic void handle24(){//经常使用,推荐final String sql = "select * from student where id = ? ";User user = jdbcTemplate.query(sql,new Object[]{8},new RowMapper<User>() {@Overridepublic 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());}@Testpublic void handle25(){//经常使用,推荐final String sql = "select * from student where id = ? ";final User user = new User();jdbcTemplate.query(sql,new Object[]{8},new RowCallbackHandler() {@Overridepublic 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());}@Testpublic void hanle26(){final String sql = "select * from student where id = ? ";User user = jdbcTemplate.queryForObject(sql, new RowMapper<User>() {@Overridepublic 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());}@Testpublic void hanle27(){final String sql = "select * from student where id = ? ";Map<String, Object> map = jdbcTemplate.queryForMap(sql,8);System.out.println(map);}</span>

下面是求和的sql

@Testpublic void handle30(){<span style="font-family:KaiTi_GB2312;">//求总数</span>final String sql = "SELECT count(1) from student where age > ? && male = ?";Integer count = jdbcTemplate.queryForObject(sql, new Object[]{30,1},new RowMapper<Integer>(){@Overridepublic Integer mapRow(ResultSet rs, int rowNum) throws SQLException {return rs.getInt(1);}});System.out.println("================"+count);}

这里不建议用queryRorInt或者Long，直接queryForObject.

如：

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>>(){@Overridepublic 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);}

这里返回是map，换成其他的集合也可以，这就是它的优势。

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>(){@Overridepublic 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());}

public void handle34() throws JSONException{//把查询出的集合转为json数据格式List<Map<String, Object>> maps = jdbcTemplate.queryForList("select * from student");System.out.println(maps);//struts2 -json-lib.jar --xwork.jarString jsonString = JSONUtil.serialize(maps, null, null, false, true);System.out.println(jsonString);}

public void handle36(){//execute:动态创建数据库，表，索引，alert 存储过程，函数，触发器jdbcTemplate.execute("create database daming");}

批量修改

public void hanler38(){//我要把所有的学生的名称加上一个前缀tzfinal List<Student> students = new ArrayList<Student>();jdbcTemplate.query("select * from student",new RowCallbackHandler(){@Overridepublic void processRow(ResultSet rs) throws SQLException {//给学生发送短信Student student = new Student();student.setId(rs.getInt("id"));student.setName(rs.getString("name"));students.add(student);}});//for (Student student : students) {//每执行一次就要往数据库执行一句sql//jdbcTemplate.update("update student set name=? where id = ?","tz"+student.getName(),student.getId());//}//第一种写法，执行多次后形成sql集，然后往数据库中执行sql//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()+"<span style="font-family:KaiTi_GB2312;">修改</span>",student.getId()};objects.add(object);}jdbcTemplate.batchUpdate("update student set name=? where id = ?", objects);}

存储过程(sql集，这个集合实现特定的功能，相当于java的方法)

1无参数

public void handle40(){List<SqlParameter> sqlParameters = new ArrayList<SqlParameter>();final List<Student> students = new ArrayList<Student>();jdbcTemplate.call(new CallableStatementCreator() {@Overridepublic CallableStatement createCallableStatement(Connection con)throws SQLException {CallableStatement statement = con.prepareCall("{call <span style="font-family:KaiTi_GB2312;">daming</span>()}");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());}}

call daming()在数据库中的写法：

create  procedure daming()begin     select * from student;end;call daming();

2有参数

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() {@Overridepublic 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"));}

在数据库它的存储过程

drop procedure if exists user_relations;create procedure user_relations(in cage int,out cnumber int,out cname varchar(20))begin    select count(1) into cnumber  from student where age>cage;    select name into cname from student where id= q;end;set @cnumber = 1;set @name ='zhangsan';call user_relations(17,@cnumber,@name);select @cnumber,@name;

//分页@Testpublic 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>(){@Overridepublic 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;}