重温Spring笔记6 - Spring JDBC模板

一、传统JDBC

1、传统JDBC步骤

• 加载驱动
• 获取connection连接
• 创建statement执行sql
• 返回resultSet查询结果
• 关闭资源

2、例子：添加用户和查询用户

• 引进msyql和数据源的jar包

  <dependency>

    <groupId>com.alibaba</groupId>

    <artifactId>druid</artifactId>

    <version>1.0.25</version>

  </dependency>

  <dependency>

   <groupId>mysql</groupId>

   <artifactId>mysql-connector-java</artifactId>

   <version>5.1.29</version>

  </dependency>

• 在数据库添加user用户表

CREATE TABLE `user` (

`id`  bigint(10) NOT NULL AUTO_INCREMENT COMMENT '自增id' ,

`userName`  varchar(16) NULL DEFAULT '' COMMENT '用户名' ,

PRIMARY KEY (`id`)

);

• 创建实体类User，并添加toString()方法

public class User {
    private Long id;
    private String userName;

    public Long getId() {
        return id;
    }
    public void setId(Long id) {
        this.id = id;
    }
    public String getUserName() {
        return userName;
    }
    public void setUserName(String userName) {
        this.userName = userName;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", userName='" + userName + '\'' +
                '}';
    }
}

• 创建userDao类，类中有saveUser（）方法和getUser（）方法

public class UserDao {
    //保存用户
    public void saveUser(String userName) throws Exception{
        //加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","123456");
        //创建statement执行sql
        Statement statement = connection.createStatement();
        //创建sql
        String sql = "insert into user(userName) values('"+userName+"')";
        //执行sql
        statement.execute(sql);
        //关闭资源
        statement.close();
        connection.close();
    }
    //获取用户
    public User getUser(long id)throws Exception{
        User user = new User();

        //加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","123456");
        //创建statement执行sql
        Statement statement = connection.createStatement();
        //创建sql
        String sql = "select * from user where id="+id;
        //执行sql
        ResultSet rt = statement.executeQuery(sql);

        while (rt.next()){
            user.setId(rt.getLong("id"));
            user.setUserName(rt.getString("userName"));
        }
        //关闭资源
        statement.close();
        connection.close();
        return user;
    }
}

• 编写测试类

public class Test {
    @org.junit.Test
    public void test() throws Exception{
        UserDao userDao = new UserDao();
        userDao.saveUser("chensr");
        User user = userDao.getUser(1l);
        System.out.println(user.toString());
    }
}

运行结果：数据库中多了一条username为“chensr”的数据，并且控制台打印

    User{id=1, userName='chensr'}

注：传统的JDBC每次与数据库交互的时候，都要获取数据库连接、关闭资源，使代码变得冗余。

 

二、Spring的JDBC模板

• spring的JDBC框架承担了资源管理和异常处理的工作，从而优化了JDBC代码，让我们只需编写从数据库读写数据必需的代码。对此Spring提供了JdbcDapSupport模板，通过getJdbcTemplate()对数据进行增删改查

1、例子：添加用户和查询用户

•  引入spring-mysql相关的jar包

  <dependency>

      <groupId>org.springframework</groupId>

      <artifactId>spring-jdbc</artifactId>

      <version>${spring.version}</version>

   </dependency>

• 创建userDao，实现JdbcDaoSupport

public class UserDao extends JdbcDaoSupport {

    //保存用户
    public void saveUser(String userName) throws Exception{

        //创建sql
        String sql = "insert into user(userName) values(?)";
        //执行sql
        this.getJdbcTemplate().update(sql,new Object[]{userName});

    }
    //获取用户
    public User getUser(long id)throws Exception{
        final User user = new User();
        //创建sql
        String sql = "select * from user where id=?";
        //执行sql
        this.getJdbcTemplate().query(sql, new Object[]{id}, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet rs) throws SQLException {
                user.setId(rs.getLong("id"));
                user.setUserName(rs.getString("username"));
            }
        });
        return user;
    }
}

• 配置数据源，并且为JdbcTemplate注入数据源

<beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd ">

    <!-- 配置数据源，这里用的是阿里巴巴的durid数据源-->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="url" value="jdbc:mysql://127.0.0.1:3306/test"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
    </bean>

    <!-- 为spring jdbcTemplate注入数据源 -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>

    <!-- 定义userDao，并注入jdbcTemplate目标 -->
    <bean id="userDao" class="com.chensr.test.UserDao">
        <property name="jdbcTemplate" ref="jdbcTemplate"/>
    </bean>
</beans>

•  创建测试类

public class Test {
    @org.junit.Test
    public void test() throws Exception{
        ClassPathXmlApplicationContext ac = new ClassPathXmlApplicationContext("spring-context.xml");
        UserDao userDao = (UserDao)ac.getBean("userDao");
        userDao.saveUser("隔壁老王");
        User user = userDao.getUser(2l);
        System.out.println(user.toString());
    }
}

运行结果：数据库中多了一条username为“隔壁老王”的数据，并且控制台打印

User{id=2, userName='隔壁老王'}