create database selection_course; use selection_course;
create table course ( id int not null auto_increment, name char(20), score int, primary key (id) );
create table selection ( student int not null, course int not null, selection_time datetime, score int, primary key (student, course) );
create table student ( id int not null auto_increment, name varchar(20), sex char(2), born date, primary key (id) );
alter table selection add constraint FK_Reference_1 foreign key (course) references course (id) on delete restrict on update restrict;
alter table selection add constraint FK_Reference_2 foreign key (student) references student (id) on delete restrict on update restrict;
insert into course(id,name,score) values(1001,'高等数学',5); insert into course(id,name,score) values(1002,'计算机组成原理',5); insert into course(id,name,score) values(1003,'数据结构',3); insert into course(id,name,score) values(1004,'网络协议',3);
public class TestMethod{ @Test public void TestExecute(){ ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml"); JdbcTemplate jdbcTemplate = (JdbcTemplate) applicationContext.getBean("jdbcTemplate"); jdbcTemplate.execute("create table testcourse(id int, name varchar(20))"); } }
public void TestBatchUpdate1(){ String [] sqls = { "insert into student(name,sex)values('小白','男')", "insert into student(name,sex)values('小花','女')", "update student set sex='女' where id =1002" }; jdbcTemplate.batchUpdate(sqls); }
public void TestBatchUpdate2(){ String sql = "insert into selection(student,course)values(?,?)"; List<Object[]> list = new ArrayList<Object[]>(); list.add(new Object[]{1002,1001}); list.add(new Object[]{1002,1003}); jdbcTemplate.batchUpdate(sql,list); }
数据查询操作
查询简单数据项（获取一个）
它有三种方式：
1 2 3
T queryForObject(String sql,Class<T> type) T queryForObject(String sql,Object[] args,Class<T> type) T queryForObject(String sql,Class<T> type,Object ... args)
举一个例子：
1 2 3 4 5
public void querySimpleOne(){ String sql = "select count(*) from student"; int count = jdbcTemplate.queryForObject(sql,Integer.class); System.out.println(count); }
public void querySimpleTwo(){ String sql = "select name from student where sex=?"; List<String> names = jdbcTemplate.queryForList(sql,String.class,"男"); System.out.println(names); }
public void queryComplexTwo(){ String sql = "select * from student "; List<Map<String,Object>> listNums = jdbcTemplate.queryForList(sql); System.out.println(listNums); }
查询复杂数据项（封装为实体对象）（获取一个）
它有三种方式（注意RowMapper是一个接口）：
1 2 3
T queryForObject(String sql,RowMapper<T> mapper) T queryForObject(String sql,Object[] args,RowMapper<T> mapper) T queryForObject(String sql,RowMapper<T> mapper,Object... args)
//实现了RowMapper<Student>接口的StudentRowMapper类 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; } }
public void queryComplexEntityOne(){ String sql = "select * from student where id=? "; Student student = jdbcTemplate.queryForObject(sql, new StudentRowMapper()); System.out.println(student); }
public void queryComplexEntityTwo(){ String sql = "select * from student"; List<Student> students = jdbcTemplate.query(sql, new StudentRowMapper()); System.out.println(students); }
public class Selection { private int studentId; private int courseId; private Date selectionTime; private int score;
public int getStudentId(){ return studentId; }
public void setStudentId(int studentId){ this.studentId =studentId; }
public int getCourseId(){ return courseId; }
public void setCourseId(int courseId){ this.courseId=courseId; }
public Date getSelectionTime(){ return selectionTime; }
public void setSelectionTime(Date selectionTime){ this.selectionTime=selectionTime; }
public int getScore(){ return score; }
public void setScore(int score){ this.score=score; } }
接着新建com.envy.dao包，里面用于存放各种接口。StudentDao.java：
1 2 3 4 5 6 7 8 9 10 11 12 13
package com.envy.dao;
import com.envy.entity.Student;
import java.util.List;
public interface StudentDao { public void insert(Student student); public void delete(int id); public void update(Student student); public Student find(int id); public List<Student> findAll(); }
public interface CourseDao { public void insert(Course course); public void delete(int id); public void update(Course course); public Course find(int id); public List<Course> findAll(); }
public interface SelectionDao { public void insert(List<Selection> selectionList); public void delete(int studentId,int courseId); public List<Map<String,Object>> selectByStudentId(int studentId); public List<Map<String,Object>> selectByCourseId(int courseId); }
@Repository public class SelectionDaoImpl implements SelectionDao { @Autowired private JdbcTemplate jdbcTemplate;
public void insert(List<Selection> selectionList) { String sql = "insert into selection(studentId,courseId,score,selectionTime)values(?,?,?,?)"; List<Object[]> list = new ArrayList<Object[]>(); for(Selection selection:selectionList){ Object[] args = new Object[4]; args[0] = selection.getStudentId(); args[1] = selection.getCourseId(); args[2] = selection.getScore(); args[3] = selection.getSelectionTime(); list.add(args); } jdbcTemplate.batchUpdate(sql,list); }
public void delete(int studentId,int courseId) { String sql = "delete * from selection where studentId=? and courseId =?"; jdbcTemplate.update(sql,studentId,courseId); }
public List<Map<String, Object>> selectByStudentId(int studentId) { String sql = "select se.*,stu.name sname,cou.name cname from selection se"+ "left join student stu on se.studentId = stu.id"+ "left join course cou on se.courseId = cou.id"+ "where studentId=?"; return jdbcTemplate.queryForList(sql,studentId); }
public List<Map<String, Object>> selectByCourseId(int courseId) { String sql = "select se.*,stu.name sname,cou.name cname from selection se"+ "left join student stu on se.studentId = stu.id"+ "left join course cou on se.courseId = cou.id"+ "where courseId=?"; return jdbcTemplate.queryForList(sql,courseId); } }
