public class Student {
private int id;
private String name;
private String sex;
private Date born;
public Date getBorn() {
return born;
}
public void setBorn(Date born) {
this.born = 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;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", sex='" + sex + '\'' +
", born=" + born +
'}';
}
}
public interface StudentDao {
void insert(Student stu);
void update(Student stu);
void delete(int id);
Student select(int id);
List<Student> selectAll();
}
@Repository
public class StudentDaoImpl implements StudentDao {
@Autowired
JdbcTemplate jdbcTemplate;
public void insert(Student stu) {
String sql = "insert into student(name,sex,born) values(?,?,?)";
jdbcTemplate.update(sql, stu.getName(), stu.getSex(), stu.getBorn());
}
public void update(Student stu) {
String sql = "update student set name=?.sex=?,born=? where id=?";
jdbcTemplate.update(sql, stu.getName(), stu.getSex(), stu.getBorn(), stu.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 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 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;
}
@Override
public String toString() {
return "Course{" +
"id=" + id +
", name='" + name + '\'' +
", score=" + score +
'}';
}
}
public interface CourseDao {
void insert(Course stu);
void update(Course stu);
void delete(int id);
Course select(int id);
List<Course> selectAll();
}
@Repository
public class CourseDaoImpl implements CourseDao {
@Autowired
JdbcTemplate jdbcTemplate;
public void insert(Course stu) {
String sql = "insert into course(name,score) values(?,?)";
jdbcTemplate.update(sql, stu.getName(),stu.getScore());
}
public void update(Course stu) {
String sql = "update course set name=?,score=? where id=?";
jdbcTemplate.update(sql, stu.getName(), stu.getScore(), stu.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 course";
return jdbcTemplate.query(sql,new CourseRowMapper());
}
private class CourseRowMapper implements RowMapper<Course> {
public Course mapRow(ResultSet resultSet, int i) throws SQLException {
Course student = new Course();
student.setId(resultSet.getInt("id"));
student.setName(resultSet.getString("name"));
student.setScore(resultSet.getInt("score"));
return student;
}
}
}
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;
}
}
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);
}
@Repository
public class SelectionDaoImpl implements SelectionDao {
@Autowired
JdbcTemplate jdbcTemplate;
public void insert(List<Selection> selections) {
String sql = "insert into selection(?,?,?,?)";
List<Object[]> list = new ArrayList<Object[]>();
for (Selection selection : selections) {
Object[] objects = new Object[4];
objects[0] = selection.getSid();
objects[1] = selection.getCid();
objects[2] = selection.getSelTime();
objects[3] = selection.getScore();
list.add(objects);
}
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);
}
}