传统JDBC
JDBCTemplate封装JDBC
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); }
public interface CourseDao { void insert(Course course); void update(Course course); void delete(int id); Course select(int id); List<Course> selectAll(); }
public interface StudentDao { void insert(Student student); void update(Student student); void delete(int id); Student select(int id); List<Student> selectAll(); }
package com.imooc.sc.dao.impl; import com.imooc.sc.dao.CourseDao; import com.imooc.sc.entity.Course; import com.imooc.sc.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; @Repository public class CourseDaoImpl implements CourseDao { @Autowired private JdbcTemplate jdbcTemplate; public void insert(Course course) { String sql="insert into course(name,score) values(?,?)"; jdbcTemplate.update(sql, course.getName(), course.getScore()); } public void update(Course course) { String sql="update course set name=?,score=? where id=?"; jdbcTemplate.update(sql, course.getName(), course.getScore(), course.getId()); } public void delete(int id) { String sql="delete from course where id=?"; jdbcTemplate.update(sql,id); } public Course select(int id) { String sql="select * from course where id=?"; return jdbcTemplate.queryForObject(sql,new CourseRowMapper(),id); } public List<Course> selectAll() { String sql="select * from student"; return jdbcTemplate.query(sql,new CourseRowMapper()); } 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; } } }
package com.imooc.sc.dao.impl; import com.imooc.sc.dao.SelectionDao; import com.imooc.sc.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; public void insert(List<Selection> selections) { String sql ="insert into selection values(?,?,?,?)"; List<Object[]> list=new ArrayList<Object[]>(); for(Selection sel:selections){ Object[] args=new Object[4]; args[0]=sel.getSid(); args[1]=sel.getCid(); args[2]=sel.getSelTime(); args[3]=sel.getScore(); list.add(args); } jdbcTemplate.batchUpdate(sql,list); } public void delete(int sid,int cid) { String sql="delete from selection where student=? and course=?"; jdbcTemplate.update(sql,sid,cid); } 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); } 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); } }
package com.imooc.sc.dao.impl; import com.imooc.sc.dao.StudentDao; import com.imooc.sc.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; @Repository public class StudentDaoImpl implements StudentDao { @Autowired private JdbcTemplate jdbcTemplate; public void insert(Student student) { String sql="insert into student(name,sex,born) values(?,?,?)"; jdbcTemplate.update(sql, student.getName(), student.getBorn()); } 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()); } public void delete(int id) { String sql="delete from student where id=?"; jdbcTemplate.update(sql,id); } public Student select(int id) { String sql="select * from student where id=?"; return jdbcTemplate.queryForObject(sql,new StudentRowMapper(),id); } public List<Student> selectAll() { String sql="select * from student"; return jdbcTemplate.query(sql,new StudentRowMapper()); } private class StudentRowMapper implements RowMapper<Student> { public Student mapRow(ResultSet resultSet, int i) throws SQLException { Student stu=new Student(); stu.setId(resultSet.getInt("id")); stu.setName(resultSet.getString("name")); stu.setSex(resultSet.getString("sex")); stu.setBorn(resultSet.getDate("born")); return stu; } } }
package com.imooc.sc.entity; public class Course { private int id; private String name; private int score; 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 getScore() { return score; } public void setScore(int score) { this.score = score; } }
package com.imooc.sc.entity; import java.util.Date; public class Selection { private int sid; private int cid; private Date selTime; private int score; public int getSid() { return sid; } public void setSid(int sid) { this.sid = sid; } public int getCid() { return cid; } public void setCid(int cid) { this.cid = cid; } public Date getSelTime() { return selTime; } public void setSelTime(Date selTime) { this.selTime = selTime; } public int getScore() { return score; } public void setScore(int score) { this.score = score; } }
package com.imooc.sc.entity; import java.util.Date; public class Student { private int id; private String name; private String sex; private Date born; 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 String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public Date getBorn() { return born; } public void setBorn(Date born) { this.born = born; } @Override public String toString() { return "Student{" + "id=" + id + ", name='" + name + '\'' + ", sex='" + sex + '\'' + ", born=" + born + '}'; } }
<?xml version="1.0" encoding="UTF-8" ?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd"> <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost:3306/selection_course?useUnicode=true&characterEncoding=utf-8"/> <property name="username" value="root"/> <property name="password" value=""/> </bean> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource"/> </bean> <context:component-scan base-package="com.imooc.sc"/> </beans>
import com.imooc.sc.entity.Student; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import java.util.Map; public class Test { private JdbcTemplate jdbcTemplate; { ApplicationContext context=new ClassPathXmlApplicationContext("spring.xml"); jdbcTemplate=(JdbcTemplate)context.getBean("jdbcTemplate"); } @org.junit.Test public void testExecute(){ jdbcTemplate.execute("create table user1(id int,name varchar(20))"); } @org.junit.Test public void testUpdate(){ String sql="insert into student(name,sex) values(?,?)"; jdbcTemplate.update(sql,new Object[]{"张飞","男"}); } @org.junit.Test public void testUpdate2(){ String sql="UPDATE student set sex =? where id=?"; jdbcTemplate.update(sql,"女" ,1); } @org.junit.Test public void testBatchUpdate(){ String[] sqls={ "insert into student(name,sex) values('关羽','女')", "insert into student(name,sex) values('刘备','男')", "update student set sex='男' where id=1" }; jdbcTemplate.batchUpdate(sqls); } @org.junit.Test public void testBatchUpdate2(){ String sql="insert into selection(student,course) values(?,?)"; List<Object[]> list=new ArrayList<Object[]>(); list.add(new Object[]{4,1001}); list.add(new Object[]{4,1003}); jdbcTemplate.batchUpdate(sql,list); } @org.junit.Test public void testQuerySimple(){ String sql="select count(*) from student"; int count=jdbcTemplate.queryForObject(sql,Integer.class); System.out.println(count); } @org.junit.Test public void testQuerySimple2(){ String sql="select name from student where sex=?"; List<String> names=jdbcTemplate.queryForList(sql,String.class,"男"); System.out.println(names); } @org.junit.Test public void testQueryMap1(){ String sql="select * from student where id=?"; Map<String,Object> stu=jdbcTemplate.queryForMap(sql,4); System.out.println(stu); } @org.junit.Test public void testQueryMap2(){ String sql="select * from student"; List<Map<String,Object>> stus=jdbcTemplate.queryForList(sql); System.out.println(stus); } @org.junit.Test public void testQueryEntity1(){ String sql="select * from student where id=?"; Student stu=jdbcTemplate.queryForObject(sql, new StudentRowMapper(), 4); System.out.println(stu); } @org.junit.Test public void testQueryEntity2(){ String sql="select * from student"; List<Student> stus=jdbcTemplate.query(sql, new StudentRowMapper()); System.out.println(stus); } private class StudentRowMapper implements RowMapper<Student>{ public Student mapRow(ResultSet resultSet, int i) throws SQLException { Student stu=new Student(); stu.setId(resultSet.getInt("id")); stu.setName(resultSet.getString("name")); stu.setSex(resultSet.getString("sex")); stu.setBorn(resultSet.getDate("born")); return stu; } } }