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初体验之增加和删除改查

   ① 、创立3个maven项目,导入所信赖的jar,spring
jdbc首要借助spring-jdbc。

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

 

 
 贰 、成立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

 

相关文章