一、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*/@Testpublic void TestDataSource() throws SQLException {DataSource dataSource = ctx.getBean(DataSource.class);System.out.println(dataSource.getConnection());}/***更新属性*/@Testpublic void updateTest() {String sql = "UPDATE user SET username = ? WHERE uid = ?";jdbcTemplate.update(sql, "神乐", 2);}/***插入数据*/@Testpublic void insertTest() {String sql = "INSERT user(username,password) values('Tonny','123456')";jdbcTemplate.execute(sql);}/***删除记录*/@Testpublic void deleteTest() {String sql = "DELETE FROM user where uid = ?";jdbcTemplate.update(sql, 3);}/***批量插入*/@Testpublic 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);}/***查询*/@Testpublic 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);}/***批量查询*/@Testpublic 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);}/***统计*/@Testpublic void countTest() {String sql = "SELECT COUNT(*) FROM user";long count = jdbcTemplate.queryForObject(sql, Long.class);System.out.println(count);}}