Spring JDBC的原理与实现

1、简介

1.1 为什么使用Spring框架中的JDBC？

1.2 使用Spring JDBC需要使用的模板类

2、实现

2.1 准备依赖

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>com.project</groupId>
  <artifactId>Spring04</artifactId>
  <version>1.0.0</version>

  <name>Spring04</name>
  <!-- FIXME change it to the project's website -->
  <url>http://www.example.com</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.7</maven.compiler.source>
    <maven.compiler.target>1.7</maven.compiler.target>
    <!--
            定义全局变量:变量名为project.spring.version
        -->
    <project.spring.version>5.0.0.RELEASE</project.spring.version>
  </properties>

  <dependencies>

    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.12</version>
    </dependency>


    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-core</artifactId>
      <version>${project.spring.version}</version>
    </dependency>

    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-beans</artifactId>
      <version>${project.spring.version}</version>
    </dependency>

    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-test</artifactId>
      <version>${project.spring.version}</version>
    </dependency>

    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-expression</artifactId>
      <version>${project.spring.version}</version>
    </dependency>


    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-context</artifactId>
      <version>${project.spring.version}</version>
    </dependency>

    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-aop</artifactId>
      <version>${project.spring.version}</version>
    </dependency>


    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>${project.spring.version}</version>
    </dependency>


    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-tx</artifactId>
      <version>${project.spring.version}</version>
    </dependency>

    <!--德鲁伊连接池-->
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
      <version>1.1.10</version>
    </dependency>
    <!--mysql连接驱动-->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.38</version>
    </dependency>


    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <version>1.16.20</version>
      <scope>provided</scope>
    </dependency>



    <dependency>
      <groupId>org.aspectj</groupId>
      <artifactId>aspectjweaver</artifactId>
      <version>1.9.2</version>
    </dependency>



  </dependencies>



  <build>

    <!--加载资源文件-->
    <resources>
      <resource>
        <directory>src/main/java</directory>
        <includes>
          <include>**/*.xml</include>
          <include>**/*.properties</include>
        </includes>
      </resource>

      <resource>
        <directory>src/main/resources</directory>
        <includes>
          <include>**/*.xml</include>
          <include>**/*.properties</include>
        </includes>
      </resource>
    </resources>


  </build>
</project>

2.2 创建数据表及实体类

@Setter@Getter@ToString
@AllArgsConstructor@NoArgsConstructor
public class User {
    private Integer id;
    private String name;
    private String passwd;

}

2.3 编写Dao层接口及实现类

实现类完成对数据库的增删改查方法：

public class UserDaoImpl implements IUserDao {

    /*通过JdbcTemplate类，获得jdbc的connection对象*/
    private JdbcTemplate jdbcTemplate;
    
    /*通过datasource的get方法，将xml里面的数据源注入*/
    public void setDataSource1(DataSource dataSource){
         this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    /*增删改都是使用update*/
    public void insert(User user) {
        this.jdbcTemplate.update("insert into users values (null,?,?)",
                user.getName(),user.getPasswd());
    }

    @Override
    public void update(User user) {
        jdbcTemplate.update("update users set name=? where id=?",
                user.getName(),user.getId());
    }

    @Override
    public void delete(Integer id) {
        jdbcTemplate.update("delete from users where id=?",
                id);
    }

    @Override
    public User selectone(Integer id) {
        List<User> list = this.jdbcTemplate.query("select * from users where id=?",
                new Object[]{id},
                new RowMapper<User>() {
                    @Override
                    public User mapRow(ResultSet rs, int i) throws SQLException {
                        User user = new User();
                        user.setId(rs.getInt("id"));
                        user.setName(rs.getString("name"));
                        user.setPasswd(rs.getString("passwd"));
                        return user;
                    }
                    //方法的返回值,就会直接封装到集合集合中

                });
        return list.size()>0? list.get(0):null;
    }

    @Override
    public List<User> select() {
        List<User> query = this.jdbcTemplate.query("select * from users",
                new RowMapper<User>() {

                    @Override
                    public User mapRow(ResultSet rs, int i) throws SQLException {
                        User user = new User();
                        user.setId(rs.getInt("id"));
                        user.setName(rs.getString("name"));
                        user.setPasswd(rs.getString("passwd"));
                        return user;
                    }
                });
        return query;
    }
}

2.4 配置Spring配置文件

<?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"
       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">
    
    <!--引入数据库数据源-->
    <context:property-placeholder location="classpath:db.properties" system-properties-mode="NEVER"/>
    
    <bean id="datasource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="${driver}"/>
        <property name="url" value="${url}"/>
        <property name="username" value="${username}"/>
        <property name="password" value="${password}"/>
    </bean>

    <bean id="service" class="com.project.SpringJDBC.service.impl.UserServiceImpl">
        <property name="dao" ref="dao"/>
    </bean>

    <bean id="dao" class="com.project.SpringJDBC.dao.impl.UserDaoImpl">
        <property name="dataSource1" ref="datasource"/>
    </bean>

</beans>

在resources目录下，新建db.properties文件

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/ssm?useUnicode=true&characterEncoding=UTF-8
username=root
password=admin

2.5 测试增删改查

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class TestRun {

    @Autowired
    private IUserService service;
    @Test
    public void save(){
        User user = new User(null,"张三三","10086");
        service.insert(user);
    }

    @Test
    public void update(){
        User user = new User(15,"张123","10086");
        service.update(user);
    }

    @Test
    public void delete(){

        service.delete(16);
    }

    @Test
    public void selectone(){

        User selectone = service.selectone(190);
        System.out.println(selectone);
    }

    @Test
    public void select(){
        List<User> select = service.select();
        System.out.println(select);
    }
}

3、小结

1.查询单个对象时,可以使用JDBCTemplate的queryForObject方法,方法的第一个参数为SQL语句,第二参数为SQL中需要的参数,第三个参数为实现RowMapper接口的对象,用于处理结果集,该方法要求返回单一的对象。

此方法在没有查询到结果集时会抛出异常。
2.查询集合对象,可以使用JDBCTemplate的query方法,参数与queryForObject相同,也可以部传入SQL需要的参数.

3.推荐使用Mybatis！