Spring全家桶(九)Spring JdbcTemplate

一、Spring JDBC

Spring JDBC是在JDBC API的基础上定义一个抽象层，用以简化JDBC操作。Spring JdbcTemplate是Spring JDBC框架的核心，为不同类型的JDBC操作提供模板方法，每个模板方法都能控制整个过程，并允许覆盖过程中的特定任务。基于此，可以在保留灵活性的情况下，将数据库存取的工作量降到最低。

二、Bean配置

在Spring配置文件中，配置一个Spring JdbcTemplate Bean：

   <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>

三、准备测试数据

登录mysql，创建一张用于测试的数据表：

create database testdb;
use testdb

创建一张数据表：

CREATE TABLE `user` (
  `uid` int(1) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(200) NOT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

四、JdbcTemplate提供的常用方法

import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by bee on 17/5/17.
 */

public class JDBCTest {
    private ApplicationContext ctx = null;
    private JdbcTemplate jdbcTemplate;

    {
        ctx = new ClassPathXmlApplicationContext("beans-jdbc.xml");
        jdbcTemplate = (JdbcTemplate) ctx.getBean("jdbcTemplate");
    }

    /**
     *
     * @throws SQLException
     */
    @Test
    public void TestDataSource() throws SQLException {
        DataSource dataSource = ctx.getBean(DataSource.class);
        System.out.println(dataSource.getConnection());
    }

    /**
     *更新属性
     */
    @Test
    public void updateTest() {
        String sql = "UPDATE user SET username = ? WHERE uid = ?";
        jdbcTemplate.update(sql, "神乐", 2);
    }

    /**
     *插入数据
     */
    @Test
    public void insertTest() {
        String sql = "INSERT user(username,password) values('Tonny','123456')";
        jdbcTemplate.execute(sql);
    }

    /**
     *删除记录
     */
    @Test
    public void deleteTest() {
        String sql = "DELETE FROM user where uid = ?";
        jdbcTemplate.update(sql, 3);
    }

    /**
     *批量插入
     */
    @Test
    public void batchUpdateTest() {
        String sql = "INSERT INTO user(username,password) values(?,?)";
        List<Object[]> batchArgs = new ArrayList<Object[]>();
        batchArgs.add(new Object[]{"Tina", "876543"});
        batchArgs.add(new Object[]{"Judy", "587641"});
        batchArgs.add(new Object[]{"Sam", "987632"});
        jdbcTemplate.batchUpdate(sql, batchArgs);
    }

    /**
     *查询
     */

    @Test
    public void queryForObject() {
        String sql = "SELECT uid AS id,username AS userName,password AS passWord " +
                "FROM user WHERE uid = ?";
        RowMapper<User> rowMapper = new BeanPropertyRowMapper<User>(User.class);
        User user = (User) jdbcTemplate.queryForObject(sql, rowMapper, 2);
        System.out.println(user);
    }


    /**
     *批量查询
     */
    @Test
    public void queryForList() {
        String sql = "SELECT uid AS id,username AS userName,password AS passWord FROM user WHERE uid > ?";
        RowMapper<User> rowMapper = new BeanPropertyRowMapper<User>(User.class);
        List<User> users = jdbcTemplate.query(sql, rowMapper, 3);
        System.out.println(users);
    }


    /**
     *统计
     */
    @Test
    public void countTest() {
        String sql = "SELECT COUNT(*) FROM user";
        long count = jdbcTemplate.queryForObject(sql, Long.class);
        System.out.println(count);
    }

}