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);
        }
    }

}