spring07 JDBC
1.创建对应的数据库
2.在MyEclipse中创建项目 引入需要的jar包
3.创建数据访问层
public interface StudentDao { //新增学生 int addStudent(Student student); //删除学生 int delStudent(Student student); //修改学生 int updateStudent(Student student); //查询所有 List<Student> findStudents(); //查询所有的学生姓名 List<String> findNames(); }
public class StudentDaoImpl extends JdbcDaoSupport implements StudentDao { @Override//新增 public int addStudent(Student student) { String sql="insert into student(age,name) values(?,?)"; return getJdbcTemplate().update(sql,student.getAge(),student.getName()); } @Override//删除 public int delStudent(Student student) { String sql="delete from student where id=?"; return getJdbcTemplate().update(sql,student.getId()); } @Override//修改 public int updateStudent(Student student) { String sql="update student set age=?,name=? where id=?"; return getJdbcTemplate().update(sql,student.getAge(),student.getName(),student.getId()); } //查询所有的学生信息 @Override public List<Student> findStudents() { String sql="select * from student"; //需要配置 行 映射器 因为 表中有3列 spring期望值是一列 return getJdbcTemplate().query(sql, new StudentRowMapper()); } //查询所有的姓名 @Override public List<String> findNames() { String sql="select name from student"; return getJdbcTemplate().queryForList(sql, String.class); } }
public class StudentRowMapper implements RowMapper<Student> { //行映射器 //这里的ResultSet指的是单行数据 并不是 所有行的结果集 @Override public Student mapRow(ResultSet rs, int rowNum) throws SQLException { Student student=new Student(); student.setId(rs.getInt("id")); student.setAge(rs.getInt("age")); student.setName(rs.getString("name")); return student; } }
4.创建业务逻辑层
public interface StudentService { //新增学生 void addStudent(Student student); //删除学生 void delStudent(Student student); //修改学生 void updateStudent(Student student); //查询所有 List<Student> findStudents(); //查询所有的学生姓名 List<String> findNames(); }
public class StudentServiceImpl implements StudentService { private StudentDao dao; // 新增 @Override public void addStudent(Student student) { int num = dao.addStudent(student); if (num > 0) { System.out.println("新增成功"); } else { System.out.println("新增失败"); } } @Override // 删除 public void delStudent(Student student) { int num = dao.delStudent(student); if (num > 0) { System.out.println("删除成功"); } else { System.out.println("删除失败"); } } @Override // 修改 public void updateStudent(Student student) { int num = dao.updateStudent(student); if (num > 0) { System.out.println("修改成功"); } else { System.out.println("修改失败"); } } @Override // 查询所有的学生信息 public List<Student> findStudents() { return dao.findStudents(); } @Override // 查询所有的学生姓名 public List<String> findNames() { return dao.findNames(); } public StudentDao getDao() { return dao; } //DI 依赖注入 public void setDao(StudentDao dao) { this.dao = dao; } }
5.书写配置文件
<?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/tx http://www.springframework.org/schema/tx/spring-tx.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd"> <!-- 配置数据源 01.spring的默认数据源 --> <!-- <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql:///news"/> <property name="username" value="wym"/> <property name="password" value="wym"/> </bean> --> <!-- 配置数据源 02.dbcp数据源 --> <!-- <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"> <property name="driverClassName" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql:///news"/> <property name="username" value="wym"/> <property name="password" value="wym"/> </bean> --> <!-- 配置数据源 03.c3p0数据源 --> <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"> <property name="driverClass" value="${driverClass}" /> <property name="jdbcUrl" value="${jdbcUrl}" /> <property name="user" value="${user}" /> <property name="password" value="${password}" /> </bean> <!-- 01. 使用配置文件 加载 数据库需要的4要素 经常使用 --> <context:property-placeholder location="classpath:jdbc.properties" /> <!-- 02.使用配置文件 加载 数据库需要的4要素 <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"> <property name="location" value="classpath:jdbc.properties"></property> </bean> --> <!-- 配置模板 --> <bean id="template" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource"></property> </bean> <!--配置dao --> <bean id="dao" class="cn.bdqn.dao.impl.StudentDaoImpl"> <property name="jdbcTemplate" ref="template"></property> </bean> <!-- 配置service --> <bean id="service" class="cn.bdqn.service.impl.StudentServiceImpl"> <property name="dao" ref="dao"></property> </bean> </beans>
jdbc.properties文件
6.书写测试类
public class StudentTest { @Test // 新增 public void addTest() { ApplicationContext context = new ClassPathXmlApplicationContext( "applicationContext.xml"); StudentService service = (StudentService) context.getBean("service"); service.addStudent(new Student(5655, "小白5")); } @Test // 删除 public void delTest() { ApplicationContext context = new ClassPathXmlApplicationContext( "applicationContext.xml"); StudentService service = (StudentService) context.getBean("service"); Student student = new Student(); student.setId(2); service.delStudent(student); } @Test // 修改 public void updateTest() { ApplicationContext context = new ClassPathXmlApplicationContext( "applicationContext.xml"); StudentService service = (StudentService) context.getBean("service"); Student student = new Student(); student.setId(1); student.setName("xiao"); student.setAge(1000); service.updateStudent(student); } @Test // 查询学生姓名 public void findNames() { ApplicationContext context = new ClassPathXmlApplicationContext( "applicationContext.xml"); StudentService service = (StudentService) context.getBean("service"); List<String> names = service.findNames(); for (String string : names) { System.out.println(string); } } @Test // 查询学生 public void findStudents() { ApplicationContext context = new ClassPathXmlApplicationContext( "applicationContext.xml"); StudentService service = (StudentService) context.getBean("service"); List<Student> list = service.findStudents(); for (Student student : list) { System.out.println(student); } } }
