图片.png
- XML配置
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">
<!--直接使用spring包里面的数据源-->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.cj.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql:///selection_course?characterEncoding=utf-8"></property>
<property name="username" value="root"></property>
<property name="password" value="root"></property>
</bean>
<!--配置template-->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
</beans>
update和batchUpdate
图片.png
@org.junit.Test
public void testUpdate(){
String sql = "insert into student(name,sex) values(?,?)";
jdbcTemplate.update(sql,new Object[]{"aaa","男"});
}
@org.junit.Test
public void testUpdate2(){
String sql = "update student set sex =? where id = ?";
jdbcTemplate.update(sql,"中",1);
}
@org.junit.Test
public void testBatchUpdate2(){
String sql = "insert into selection(student,course) values(?,?)";
List<Object[]> list = new ArrayList<>();
list.add(new Object[]{1,1001});
list.add(new Object[]{1,1003});
jdbcTemplate.batchUpdate(sql,list);
}
queryForObject和queryForList
@org.junit.Test
public void testQuerySimple(){
String sql = "select count(*) from student";
int i = jdbcTemplate.queryForObject(sql, Integer.class);
System.out.println(i);
}
@org.junit.Test
public void testQuerySimple2(){
String sql = "select name from student where sex = ?";
List<String> list = jdbcTemplate.queryForList(sql, String.class, "中");
System.out.println(list);
}
@org.junit.Test
public void testQueryMap1(){
String sql = "select * from student where id = ?";
Map<String, Object> map = jdbcTemplate.queryForMap(sql,1);
System.out.println(map);
}
@org.junit.Test
public void testQueryMap2(){
String sql = "select * from selection ";
List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
System.out.println(list);
}
@org.junit.Test
//查询复杂对象（封装为实体类）
public void testQueryEntity1(){
String sql = "select * from student where id = ?";
Student student = jdbcTemplate.queryForObject(sql, new RowMapper<Student>() {
@Override
public Student mapRow(ResultSet resultSet, int i) throws SQLException {
Student student = new Student();
student.setId(resultSet.getInt("id"));
student.setName(resultSet.getString("name"));
student.setSex(resultSet.getString("sex"));
student.setBorn(resultSet.getDate("born"));
return student;
}
}, 1);
System.out.println(student);
}
@org.junit.Test
public void testQueryEntity2(){
String sql = "select * from student";
List<Student> students = jdbcTemplate.query(sql, new StudentRowMapper());
System.out.println(students);
}
private class StudentRowMapper implements RowMapper<Student>{
public Student mapRow(ResultSet resultSet, int i) throws SQLException {
Student student = new Student();
student.setId(resultSet.getInt("id"));
student.setName(resultSet.getString("name"));
student.setSex(resultSet.getString("sex"));
student.setBorn(resultSet.getDate("born"));
return student;
}
}
持久层示例
- CourseDao
package com.alan.dao;
import com.alan.entity.Course;
import java.util.List;
public interface CourseDao {
void insert(Course course);
void update(Course course);
void delete(int id);
Course select(int id);
List<Course> selectAll();
}
- CourseDaoImpl
package com.alan.dao.impl;
import com.alan.dao.CourseDao;
import com.alan.entity.Course;
import com.alan.entity.Student;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
/**
* 交给Spring来管理，不用每个controller都需要创建
*/
//@Repository 用于对DAO实现类进行标注
@Repository
public class CourseDaoImpl implements CourseDao {
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public void insert(Course course) {
String sql = "insert into course(name,score) values(?,?)";
jdbcTemplate.update(sql,course.getName(),course.getScore());
}
@Override
public void update(Course course) {
String sql = "update course set name = ?, score = ? where id = ?";
jdbcTemplate.update(sql,course.getName(),course.getScore(),course.getId());
}
@Override
public void delete(int id) {
String sql = "delete from course where id = ?";
jdbcTemplate.update(sql,id);
}
@Override
public Course select(int id) {
String sql = "select * from course where id = ?";
Course course = jdbcTemplate.queryForObject(sql,new CourseRowMapper(),id);
return course;
}
@Override
public List<Course> selectAll() {
String sql = "select * from course";
List<Course> courseList = jdbcTemplate.query(sql,new CourseRowMapper());
return courseList;
}
private class CourseRowMapper implements RowMapper<Course> {
public Course mapRow(ResultSet resultSet, int i) throws SQLException {
Course course = new Course();
course.setId(resultSet.getInt("id"));
course.setName(resultSet.getString("name"));
course.setScore(resultSet.getInt("score"));
return course;
}
}
}
- SelectionDao
package com.alan.dao;
import com.alan.entity.Selection;
import java.util.List;
import java.util.Map;
public interface SelectionDao {
void insert(List<Selection> selections);
void delete(int sid,int cid);
List<Map<String,Object>> selectByStudent(int sid);
List<Map<String,Object>> selectByCourse(int cid);
}
- SelectionDaoImpl
package com.alan.dao.impl;
import com.alan.dao.SelectionDao;
import com.alan.entity.Selection;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
@Repository
public class SelectionDaoImpl implements SelectionDao {
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public void insert(List<Selection> selections) {
String sql = "insert into selection values(?,?,?,?)";
List<Object[]> list = new ArrayList<Object[]>();
for(Selection selection:selections){
Object[] args = new Object[4];
args[0]=selection.getSid();
args[1]=selection.getCid();
args[2]= selection.getSelTime();
args[3] = selection.getScore();
list.add(args);
}
jdbcTemplate.batchUpdate(sql,list);
}
@Override
public void delete(int sid,int cid) {
String sql = "delete from selection where student = ? and course = ?";
jdbcTemplate.update(sql,sid,cid);
}
@Override
public List<Map<String, Object>> selectByStudent(int sid) {
String sql = "select se.*,stu.name sname,cou.name cname from selection se " +
"left join student stu on se.student=stu.id " +
"left join course cou on se.course=cou.id" +
"where student=?";
return jdbcTemplate.queryForList(sql,sid); }
@Override
public List<Map<String, Object>> selectByCourse(int cid) {
String sql = "select se.*,stu.name sname,cou.name cname from selection se " +
"left join student stu on se.student=stu.id " +
"left join course cou on se.course=cou.id" +
"where course=?";
return jdbcTemplate.queryForList(sql,cid); }
}
- StudentDao
package com.alan.dao;
import com.alan.entity.Student;
import java.util.List;
public interface StudentDao {
void insert(Student student);
void update(Student student);
void delete(int id);
Student select(int id);
List<Student> selectAll();
}
- StudentDaoImpl
package com.alan.dao.impl;
import com.alan.dao.StudentDao;
import com.alan.entity.Student;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
/**
* 交给Spring来管理，不用每个controller都需要创建
*/
//@Repository 用于对DAO实现类进行标注
@Repository
public class StudentDaoImpl implements StudentDao {
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public void insert(Student student) {
String sql = "insert into student(name,sex,born) values(?,?,?)";
//jdbcTemplate会将util.date转换为sql.date
jdbcTemplate.update(sql,student.getName(),student.getSex(),student.getBorn());
}
@Override
public void update(Student student) {
String sql = "update student set name =? , sex = ?, born = ? where id = ?";
jdbcTemplate.update(sql,student.getName(),student.getSex(),student.getBorn(),student.getId());
}
@Override
public void delete(int id) {
String sql = "delete from student where id = ?";
jdbcTemplate.update(sql,id);
}
@Override
public Student select(int id) {
String sql = "select * from student where id = ?";
Student student = jdbcTemplate.queryForObject(sql,new StudentRowMapper(),id);
return student;
}
@Override
public List<Student> selectAll() {
String sql = "select * from student";
List<Student> studentList = jdbcTemplate.query(sql, new StudentRowMapper());
return studentList;
}
private class StudentRowMapper implements RowMapper<Student> {
public Student mapRow(ResultSet resultSet, int i) throws SQLException {
Student student = new Student();
student.setId(resultSet.getInt("id"));
student.setName(resultSet.getString("name"));
student.setSex(resultSet.getString("sex"));
student.setBorn(resultSet.getDate("born"));
return student;
}
}
}
总结
1、JDBC Template是Spring框架对JDBC操作的封装，简单、灵活但不够强大。
2、实际应用中还需要和其他ORM框架混合应用。
3、Spring之所以没过度强化Template，是给其他ORM框架留出空间，更好的结合其他的ORM框架。
网友评论