Spring JDBCTemplate连接SQL Server之初体验

前言

     在没有任何框架的帮助下我们操作数据库都是用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。

<dependencies>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>4.3.6.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.10</version>
    </dependency>
    <dependency>
      <groupId>com.microsoft.sqlserver</groupId>
      <artifactId>sqljdbc4</artifactId>
      <version>4.0</version>
    </dependency>
  </dependencies>

   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<User> 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;
    }
}

　　

参考资料

http://www.cnblogs.com/doudouxiaoye/p/5782003.html

http://1358440610-qq-com.iteye.com/blog/1826816