前言

     在没有任何框架的帮助下我们操作数据库都是用jdbc，耗时耗力，那么有了Spring，我们则不用重复造轮子了，先来试试Spring JDBC增删改查，其中关键就是构造JdbcTemplate类。

     其中jdbcTemplate已经实现了queryForList()，但是经过测试后发现之能返回简单数据类型String、Integer之类。 如果需要返回List<T>则使用query()并且让model实现RowMappper接口。

 

开发环境

     idea2016、jdk1.8、maven3.3、

     spring-jdbc 4.3.6

 

Spring Jdbc初体验之增删改查

   1、创建一个maven项目，导入所依赖的jar，spring jdbc主要依赖spring-jdbc。

1 
     
       2     
      
        3       
       
        org.springframework
        4       
       
        spring-jdbc
        5       
       
        4.3.6.RELEASE
        6     
       7     
      
        8       
       
        junit
        9       
       
        junit
       10       
       
        4.10
       11     
      12     
      
       13       
       
        com.microsoft.sqlserver
       14       
       
        sqljdbc4
       15       
       
        4.0
       16     
      17   
     

 

   2、创建DriverManagerDataSource、创建jdbcTemplate。主要调用query()、queryForObject()、queryForList()、queryForMap、

package com.autohome.dao;import com.autohome.model.User;import org.junit.BeforeClass;import org.junit.Test;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.jdbc.core.PreparedStatementSetter;import org.springframework.jdbc.core.RowCallbackHandler;import org.springframework.jdbc.datasource.DriverManagerDataSource;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Types;import java.util.ArrayList;import java.util.Iterator;import java.util.List;import java.util.Map;public class UserDaoTest {    private static JdbcTemplate jdbcTemplate;    @BeforeClass    public static void setUpClass(){        DriverManagerDataSource dataSource=new DriverManagerDataSource();        dataSource.setUrl("jdbc:sqlserver://127.0.0.1:1433;databaseName=test");        dataSource.setDriverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver");        dataSource.setUsername("sa");        dataSource.setPassword("");        jdbcTemplate=new JdbcTemplate(dataSource);    }    @Test    public void selectAll(){        jdbcTemplate.query("select * from t_student", new RowCallbackHandler() {            public void processRow(ResultSet resultSet) throws SQLException {                System.out.println("====id:"+resultSet.getInt("id")+"===name:"+resultSet.getString("name"));            }        });    }    @Test    public void insert(){        //SQL        //int result = jdbcTemplate.update("insert into t_student (name,age) VALUES (?,?)",new Object[]{"Lin",30});        //避免sql参数注入        int result = jdbcTemplate.update("insert into t_student (name,age) values(?,?)",                new PreparedStatementSetter() {            public void setValues(PreparedStatement ps) throws SQLException {                ps.setString(1,"zhangsan");                ps.setInt(2,35);            }        });        if(result>0){            System.out.println("insert success...");        }    }    @Test    public void update(){        int result = jdbcTemplate.update("update t_student set Name=? ,Age=? where Id=?", new PreparedStatementSetter() {            public void setValues(PreparedStatement ps) throws SQLException {                ps.setString(1,"Linshuhao");                ps.setInt(2,40);                ps.setInt(3,3);            }        });        if(result>0){            System.out.println("update success...");        }    }    @Test    public void delete(){        int result = jdbcTemplate.update("delete from t_student where id=?",new Object[]{1},new int[]{Types.INTEGER});        if(result>0){            System.out.println("delete success...");        }    }    @Test    public void listAll(){        List
     
       allUser = jdbcTemplate.query("SELECT * FROM t_student",new User());        for(User user:allUser){            System.out.println("===id:"+user.getId()+",name:"+user.getName()+",age:"+user.getAge());        }    }    @Test    public void selectUser(){        String sql="SELECT * FROM t_student where id=?";        User user =  (User) jdbcTemplate.queryForObject(sql,new Object[]{2},new User());        System.out.println("===id:"+user.getId()+",name:"+user.getName()+",age:"+user.getAge());    }}
     

　　

User.java 主要是实现RowMapper接口，要不然jdbc不知道你的实体类和sql字段怎么映射，仅此而已

package com.autohome.model;import org.springframework.jdbc.core.RowMapper;import java.sql.ResultSet;import java.sql.SQLException;public class User implements RowMapper {    private int id;    private String name;    private int age;    public int getId() {        return id;    }    public void setId(int id) {        this.id = id;    }    public String getName() {        return name;    }    public void setName(String name) {        this.name = name;    }    public int getAge() {        return age;    }    public void setAge(int age) {        this.age = age;    }    public Object mapRow(ResultSet rs, int i) throws SQLException {        User user=new User();        user.setId(rs.getInt("id"));        user.setName(rs.getString("name"));        user.setAge(rs.getInt("age"));        return user;    }}

　　

 

参考资料