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