Sprng4之JDBC--很原始的使用方法

\[www.dev1234.com]一头扎进Spring4视频教程\一头扎进Spring4源码\[www.java1234.com]《一头扎进Spring4》第九讲 源码\Spring404

此例子有对数据库的增删改查 CRUD  是指增加(Create)、读取查询(Retrieve)、更新(Update)和删除(Delete)

说它落后，因为到了

1、人人框架的xml里面根本上都看不到这些配置了：【但是比将数据库配置写在代码中要好】

2、在Dao 和Service的Impl实现中也不用在java代码里面写 SQL语句。sql语句写在xml里面

 课件内容：

　　

bean.xml 文件：

<?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:aop="http://www.springframework.org/schema/aop"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/aop
        http://www.springframework.org/schema/aop/spring-aop.xsd
http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context.xsd">

    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${jdbc.driverClassName}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
    </bean>
    <context:property-placeholder location="jdbc.properties"/>//加载本地的文件，文件如下下发
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>

    <bean id="studentDao" class="com.java1234.dao.impl.StudentDaoImpl">
        <property name="jdbcTemplate" ref="jdbcTemplate"></property>//注入属性，然后在StudentDaoImpl就可以用Jdbc了
    </bean> 

    <bean id="studentService" class="com.java1234.service.impl.StudentServiceImpl">
        <property name="studentDao" ref="studentDao"></property>//StudentServiceImpl类中要有studentDao属性和get、set方法，在这里注入属性，同上
    </bean> 

</beans>

jdbc.properties文件：

jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/db_spring
jdbc.username=root
jdbc.password=123456

渐进式优化：

1、 直接用jdbc来链接，很麻烦
  try {
            conn = dataSource.getConnection();
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, u.getName());
            ps.setString(2, u.getPassword());
            ps.executeUpdate();
            ps.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
2、JdbcTemplate
   public void insert(User u) {
        String sql = "insert into _user " + "values(null, ?, ?)";//普通的sql语句
        JdbcTemplate template = new JdbcTemplate(dataSource);
        template.update(sql, new Object[]{u.getName(), u.getPassword()});
    }

3、JdbcDaoSupport
这个更简单，不用new JdbcTemplate了。 this.getJdbcTemplate().update
     public void insert(User u) {
        String sql = "insert into _user " + "values(null, ?, ?)";//普通的sql语句
        this.getJdbcTemplate().update(sql, new Object[]{u.getName(), u.getPassword()});
    }

4、namedParameterJdbcTemplate 不用？好，用冒号加变量名
如：namedParameterJdbcTemplate =  new NamedParameterJdbcTemplate(jdbcTemplate);
    String selectSql = "select * from test where name=:name";
    String deleteSql = "delete from test where name=:name";
    Map<String, Object> paramMap = new HashMap<String, Object>();
    paramMap.put("name", "name5");
    namedParameterJdbcTemplate.update(insertSql, paramMap); //要传一个Map 进去。MapSqlparameterSource类型的也可以

=========例子结构==========

\src\jdbc.properties;//数据库配置
\src\beans.xml;//配置文件
\src\com\java1234\model\Student.java;//实体类
\src\com\java1234\dao\StudentDao.java;//dao接口
\src\com\java1234\dao\impl\StudentDaoImpl.java;//dao实现
\src\com\java1234\service\StudentService.java;//业务接口
\src\com\java1234\service\impl\StudentServiceImpl.java;//业务实现
\src\com\java1234\test\T.java;//调用层，可以当做UI展示层

package com.java1234.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;

import com.java1234.dao.StudentDao;
import com.java1234.model.Student;

public class StudentDaoImpl implements StudentDao{

    private JdbcTemplate jdbcTemplate;

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public int addStudent(Student student) {
        String sql="insert into t_student values(null,?,?)";
        Object []params=new Object[]{student.getName(),student.getAge()};
        return jdbcTemplate.update(sql,params);
    }

    @Override
    public int updateStudent(Student student) {
        String sql="update t_student set name=?,age=? where id=?";
        Object []params=new Object[]{student.getName(),student.getAge(),student.getId()};
        return jdbcTemplate.update(sql,params);
    }

    @Override
    public int deleteStudent(int id) {
        String sql="delete from t_student where id=?";
        Object []params=new Object[]{id};
        return jdbcTemplate.update(sql,params);
    }

    @Override
    public List<Student> findStudents() {
        String sql="select * from t_student";
        final List<Student> studentList=new ArrayList<Student>();
        jdbcTemplate.query(sql, new RowCallbackHandler(){

            @Override
            public void processRow(ResultSet rs) throws SQLException {
                Student student=new Student();
                student.setId(rs.getInt("id"));
                student.setName(rs.getString("name"));
                student.setAge(rs.getInt("age"));
                studentList.add(student);
            }

        });
        return studentList;
    }

}

StudentDaoImpl.java;

package com.java1234.test;

import java.util.List;

import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.java1234.model.Student;
import com.java1234.service.StudentService;

public class T {

    private ApplicationContext ac;

    @Before
    public void setUp() throws Exception {
        ac=new ClassPathXmlApplicationContext("beans.xml");
    }

    @Test
    public void addStudent() {
        StudentService studentService=(StudentService)ac.getBean("studentService");
        int addNums=studentService.addStudent(new Student("����", 1));
        if(addNums==1){
            System.out.println("��ӳɹ�");
        }
    }

    @Test
    public void updateStudent() {
        StudentService studentService=(StudentService)ac.getBean("studentService");
        int updateNums=studentService.updateStudent(new Student(8,"����2", 2));
        if(updateNums==1){
            System.out.println("���³ɹ�");
        }
    }

    @Test
    public void deleteStudent() {
        StudentService studentService=(StudentService)ac.getBean("studentService");
        int deleteNums=studentService.deleteStudent(8);
        if(deleteNums==1){
            System.out.println("ɾ���ɹ�");
        }
    }

    @Test
    public void findStudents() {
        StudentService studentService=(StudentService)ac.getBean("studentService");
        List<Student> studentList=studentService.findStudents();
        for(Student student:studentList){
            System.out.println(student);
        }
    }
}

T.java;//调用层