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