JDBC Template - 基础篇

<?xmlversion="1.0"encoding="UTF-8"?><beansxmlns="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/beanshttp://www.springframework.org/schema/beans/spring-beans.xsdhttp://www.springframework.org/schema/contexthttp://www.springframework.org/schema/context/spring-context.xsdhttp://www.springframework.org/schema/aophttp://www.springframework.org/schema/aop/spring-aop.xsdhttp://www.springframework.org/schema/txhttp://www.springframework.org/schema/tx/spring-tx.xsd"><beanid="dataSource"class="org.springframework.jdbc.datasource.DriverManagerDataSource"><propertyname="driverClassName"value="com.mysql.jdbc.Driver"/><propertyname="url"value="jdbc:mysql://localhost:3306/selection_course?useUnicode=true&amp;characterEncoding=utf-8"/><propertyname="username"value="root"/><propertyname="password"value="root"/></bean><beanid="jdbcTemplate"class="org.springframework.jdbc.core.JdbcTemplate"><propertyname="dataSource"ref="dataSource"/></bean><context:component-scanbase-package="com.imooc.sc"/></beans>

packagecom.imooc.sc.dao;
importcom.imooc.sc.entity.Course;
importcom.imooc.sc.entity.Student;
importjava.util.List;
publicinterfaceCourseDao {
voidinsert(Coursecourse);
voidupdate(Coursecourse);
voiddelete(intid);
Courseselect(intid);
List<Course>selectAll();
}

packagecom.imooc.sc.dao.impl;
importcom.imooc.sc.dao.CourseDao;
importcom.imooc.sc.entity.Course;
importcom.imooc.sc.entity.Student;
importorg.springframework.beans.factory.annotation.Autowired;
importorg.springframework.jdbc.core.JdbcTemplate;
importorg.springframework.jdbc.core.RowMapper;
importorg.springframework.stereotype.Repository;
importjava.sql.ResultSet;
importjava.sql.SQLException;
importjava.util.List;
@RepositorypublicclassCourseDaoImplimplementsCourseDao {
@AutowiredprivateJdbcTemplatejdbcTemplate;
publicvoidinsert(Coursecourse) {
Stringsql="insert into course(name,score) values(?,?)";
jdbcTemplate.update(sql,course.getName(),course.getScore());
    }
publicvoidupdate(Coursecourse) {
Stringsql="update course set name=?,score=? where id=?";
jdbcTemplate.update(sql,course.getName(),course.getScore(),course.getId());
    }
publicvoiddelete(intid) {
Stringsql="delete from course where id=?";
jdbcTemplate.update(sql,id);
    }
publicCourseselect(intid) {
Stringsql="select * from course where id=?";
returnjdbcTemplate.queryForObject(sql,newCourseRowMapper(),id);
    }
publicList<Course>selectAll() {
Stringsql="select * from course";
returnjdbcTemplate.query(sql,newCourseRowMapper());
    }
privateclassCourseRowMapperimplementsRowMapper<Course> {
publicCoursemapRow(ResultSetresultSet, inti) throwsSQLException {
Coursecourse=newCourse();
course.setId(resultSet.getInt("id"));
course.setName(resultSet.getString("name"));
course.setScore(resultSet.getInt("score"));
returncourse;
        }
    }
}

packagecom.imooc.sc.dao;
importcom.imooc.sc.entity.Selection;
importjava.util.List;
importjava.util.Map;
publicinterfaceSelectionDao {
voidinsert(List<Selection>seles);
voiddelete(intsid,intcid);
List<Map<String,Object>>selectByStudent(intsid);
List<Map<String,Object>>selectByCourse(intcid);
}

packagecom.imooc.sc.dao.impl;
importcom.imooc.sc.dao.SelectionDao;
importcom.imooc.sc.entity.Selection;
importorg.springframework.beans.factory.annotation.Autowired;
importorg.springframework.jdbc.core.JdbcTemplate;
importorg.springframework.stereotype.Repository;
importjava.util.ArrayList;
importjava.util.List;
importjava.util.Map;
@RepositorypublicclassSelectionDaoImplimplementsSelectionDao {
@AutowiredprivateJdbcTemplatejdbcTemplate;
publicvoidinsert(List<Selection>seles) {
Stringsql="insert into selection values(?,?,?,?)";
List<Object[]>list=newArrayList<Object[]>();
for(Selectionsel:seles){
Object[] args=newObject[4];
args[0] =sel.getSid();
args[1]=sel.getCid();
args[2] =sel.getSelTime();
args[3] =sel.getScore();
list.add(args);
        }
jdbcTemplate.batchUpdate(sql,list);
    }
publicvoiddelete(intsid,intcid) {
Stringsql="delete from selection where student=? and course=?";
jdbcTemplate.update(sql,sid,cid);
    }
publicList<Map<String, Object>>selectByStudent(intsid) {
Stringsql="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=?";
returnjdbcTemplate.queryForList(sql,sid);
    }
publicList<Map<String, Object>>selectByCourse(intcid) {
Stringsql="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=?";
returnjdbcTemplate.queryForList(sql,cid);
    }
}

packagecom.imooc.sc.dao;
importcom.imooc.sc.entity.Student;
importjava.util.List;
publicinterfaceStudentDao {
voidinsert(Studentstu);
voidupdate(Studentstu);
voiddelete(intid);
Studentselect(intid);
List<Student>selectAll();
}

packagecom.imooc.sc.dao.impl;
importcom.imooc.sc.dao.StudentDao;
importcom.imooc.sc.entity.Student;
importorg.springframework.beans.factory.annotation.Autowired;
importorg.springframework.jdbc.core.JdbcTemplate;
importorg.springframework.jdbc.core.RowMapper;
importorg.springframework.stereotype.Repository;
importjava.sql.ResultSet;
importjava.sql.SQLException;
importjava.util.List;
@RepositorypublicclassStudentDaoImplimplementsStudentDao {
@AutowiredprivateJdbcTemplatejdbcTemplate;
publicvoidinsert(Studentstu) {
Stringsql="insert into student(name,sex,born) values(?,?,?)";
jdbcTemplate.update(sql,stu.getName(),stu.getSex(),stu.getBorn());
    }
publicvoidupdate(Studentstu) {
Stringsql="update student set name=?,sex=?,born=? where id=?";
jdbcTemplate.update(sql,stu.getName(),stu.getSex(),stu.getBorn(),stu.getId());
    }
publicvoiddelete(intid) {
Stringsql="delete from student where id=?";
jdbcTemplate.update(sql,id);
    }
publicStudentselect(intid) {
Stringsql="select * from student where id=?";
returnjdbcTemplate.queryForObject(sql,newStudentRowMapper(),id);
    }
publicList<Student>selectAll() {
Stringsql="select * from student";
returnjdbcTemplate.query(sql,newStudentRowMapper());
    }
privateclassStudentRowMapperimplementsRowMapper<Student> {
publicStudentmapRow(ResultSetresultSet, inti) throwsSQLException {
Studentstu=newStudent();
stu.setId(resultSet.getInt("id"));
stu.setName(resultSet.getString("name"));
stu.setSex(resultSet.getString("sex"));
stu.setBorn(resultSet.getDate("born"));
returnstu;
        }
    }
}