一、说明
1.Spring JDBC 对原始的 JDBC 进行了封装,使其更加易用。
2.JdbcTemplate 作为 Spring JDBC 的核心,为不同类型的 JDBC 操作提供了模板方法。
3.JdbcTemplate 对于 Spring 作用与 DbUtils 对于 Jdbc 的意义相同。它们做的是同一件事情。
二、JdbcTemplate
1.在 Spring Config 文件中注册 JdbcTemplate 的实例,同时配置数据源,如:
<context:property-placeholder location="classpath:db.properties"/> <bean class="com.mchange.v2.c3p0.ComboPooledDataSource" id="dataSource">
<property name="driverClass" value="${jdbc.driverClass}"/>
<property name="jdbcUrl" value="${jdbc.jdbcUrl}"/>
<property name="user" value="${jdbc.user}"/>
<property name="password" value="${jdbc.password}"/>
</bean> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>
2.在目标类中,注入 JdbcTemplate 实例,进行相应的 Jdbc 操作。
/**
* @author solverpeng
* @create 2016-07-28-20:29
*/
@Repository
public class StudentDao {
@Autowired
private JdbcTemplate jdbcTemplate;
}
3.更新(增、删、改)和批量更新,这里只作一个类型的例子。
(1)增,返回受影响的行数
StudentDao:
public void insertStudent() {
String sql = "insert into student(student_id, student_name, student_class) " +
"values(?,?,?)";
int affectRows = this.jdbcTemplate.update(sql, 1, "tom", "0102");
System.out.println("affectRows:" + affectRows);
}
控制台输出:
affectRows:1
(2)增,返回增加的主键值
StudentDao:
public void insertStudent2() {
PreparedStatementCreator psc = new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
String sql = "insert into student(student_id, student_name, student_class) values(?, ?, ?)";
PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
ps.setInt(1, 2);
ps.setString(2, "jerry");
ps.setString(3, "0805");
return ps;
}
};
KeyHolder keyHolder = new GeneratedKeyHolder();
this.jdbcTemplate.update(psc, keyHolder);
Number key = keyHolder.getKey();
System.out.println(key);
}
数据库:
控制台输出:
2
在做这个测试的时候遇到一个问题:!Statement.GeneratedKeysNotRequested!
解决:http://*.com/questions/7162989/sqlexception-generated-keys-not-requested-mysql
(3)批量增
StudentDao:
public void batchUpdate() {
String sql = "insert into student(student_id, student_name, student_class) values(?, ?, ?)";
List<Object[]> list = new ArrayList<>();
list.add(new Object[]{3, "lily", "0806"});
list.add(new Object[]{4, "lucy", "0807"});
this.jdbcTemplate.batchUpdate(sql, list);
}
数据库:
4.查询单个对象:queryForObject
StudentDao:
public void getStudent(Integer id) {
String sql = "select student_id, student_name, student_class from student where student_id = ?";
RowMapper<Student> rowMapper = new BeanPropertyRowMapper<>(Student.class);
Student student = this.jdbcTemplate.queryForObject(sql, rowMapper, id);
System.out.println(student);
}
控制台输出:
Student{studentId=1, studentName='tom', studentClass='0804'}
5.返回查询的记录数
public void getCount() {
String sql = "select count(student_id) from student";
Long count = this.jdbcTemplate.queryForObject(sql, Long.class);
System.out.println(count);
}
控制台输出:
4
三、NamedParameterJdbcTemplate :在 sql 中嵌入命名参数。
1.同样在 Spring Config 文件中进行配置
2.使用 ParamMap 插入
StudentDao:
public void insert() {
String sql = "insert into student(student_id, student_name, student_class) values(:studentId, :studentName, :studentClass)";
Map<String, Object> paramMap = new HashMap<>();
paramMap.put("studentId", 5);
paramMap.put("studentName", "jakc");
paramMap.put("studentClass", "0808");
this.namedParameterJdbcTemplate.update(sql, paramMap);
}
数据库:
3.使用 SqlParameterSource 插入
public void insert2() {
String sql = "insert into student(student_id, student_name, student_class) values(:studentId, :studentName, :studentClass)";
Student student = new Student();
student.setStudentId(6);
student.setStudentName("mary");
student.setStudentClass("0809");
SqlParameterSource source = new BeanPropertySqlParameterSource(student);
this.namedParameterJdbcTemplate.update(sql, source);
}
数据库:
4.使用 SqlParameterSource 插入返回主键
public void insert3() {
String sql = "insert into student(student_id, student_name, student_class) values(:studentId, :studentName, :studentClass)";
Student student = new Student();
student.setStudentId(7);
student.setStudentName("kobe");
student.setStudentClass("0810");
SqlParameterSource source = new BeanPropertySqlParameterSource(student);
KeyHolder keyHolder = new GeneratedKeyHolder();
this.namedParameterJdbcTemplate.update(sql,source, keyHolder);
Number key = keyHolder.getKey();
System.out.println(key);
}
控制台输出:
7
数据库:
四、继承 JdbcDaoSupport
1.继承 JdbcDaoSupport,需要为每一个目标类配置数据源,JdbcTemplate会自动注入,不需要在SpringConfig文件中配置JdbcTemplate。
2.需要注意的是,不能在目标类中通过 @Autowired 的方式注入 dataSource。
3.演示一个例子:
/**
* @author solverpeng
* @create 2016-07-29-11:29
*/
@Repository
public class PersonDao extends JdbcDaoSupport{
public void insert() {
String sql = "insert into person(id, person_name) values(?, ?)";
this.getJdbcTemplate().update(sql, 1, "tom");
}
}
SpringConfig文件:
<bean class="com.nucsoft.spring.jdbc.PersonDao" id="dao">
<property name="dataSource" ref="dataSource"/>
</bean>