JdbcTemplate

JdbcTemplate：

概述

​ 为了使JDBC更加易于使用，Spring在JDBC API上定义了一个抽象层，以此建立一个JDBC存取框架。 作为Spring JDBC框架的核心，JDBC模板的设计目的是为不同类型的JDBC操作提供模板方法，通过这种方式，可以在尽可能保留灵活性的情况下，将数据库存取的工作量降到最低。 可以将Spring的JdbcTemplate看作是一个小型的轻量级持久化层框架，和我们之前使用过的DBUtils风格非常接近。

jdbc.properties

1 2 3 4 5 6 7 8 9 10

jdbc.username=root //此处请带前缀否则会导致${username}为系统用户名 jdbc.password=123456 jdbc.url=jdbc:mysql://localhost:3306/bigdata jdbc.driver=com.mysql.jdbc.Driver initialPoolSize=30 minPoolSize=10 maxPoolSize=100 acquireIncrement=5 maxStatements=1000 maxStatementsPerConnection=10

spring配置文件

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"> <property name="driverClass" value="${jdbc.driver}"></property> <property name="jdbcUrl" value="${jdbc.url}"></property> <property name="user" value="${jdbc.username}"></property> <property name="password" value="${jdbc.password}"></property> <property name="initialPoolSize" value="${initialPoolSize}"/> <property name="minPoolSize" value="${minPoolSize}"/> <property name="maxPoolSize" value="${maxPoolSize}"/> <property name="acquireIncrement" value="${acquireIncrement}"/> <property name="maxStatements" value="${maxStatements}"/> <property name="maxStatementsPerConnection" value="${maxStatementsPerConnection}"/> </bean> <!--JdbcTemplate--> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource"></property> </bean>

持久化操作

增删改: JdbcTemplate.update(String, Object…)

批量增删改: JdbcTemplate.batchUpdate(String, List)

Object[]封装了SQL语句每一次执行时所需要的参数

List集合封装了SQL语句多次执行时的所有参数

查询单行 JdbcTemplate.queryForObject(String, RowMapper<Department>, Object…)

查询多行:JdbcTemplate.query(String, RowMapper<Department>, Object…) RowMapper对象依然可以使用BeanPropertyRowMapper

查询单一值:JdbcTemplate.queryForObject(String, Class, Object…)

前置准备

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49

public class Employee { private Integer id ; private String lastName; private String email ; private Integer gender; public Employee() { // TODO Auto-generated constructor stub } public Employee(Integer id, String lastName, String email, Integer gender) { super(); this.id = id; this.lastName = lastName; this.email = email; this.gender = gender; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getLastName() { return lastName; } public void setLastName(String lastName) { this.lastName = lastName; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public Integer getGender() { return gender; } public void setGender(Integer gender) { this.gender = gender; } @Override public String toString() { return "Employee [id=" + id + ", lastName=" + lastName + ", email=" + email + ", gender=" + gender + "]"; } }

方法实现

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131

import org.junit.Before; 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 org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource; import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; import org.springframework.jdbc.core.namedparam.SqlParameterSource; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; public class TestJdbc { private JdbcTemplate jdbcTemplate; private NamedParameterJdbcTemplate npjt; //前置操作初始化 @Before public void init() { ApplicationContext ctx = new ClassPathXmlApplicationContext("spring-jdbc.xml"); jdbcTemplate = ctx.getBean("jdbcTemplate", JdbcTemplate.class); npjt = ctx.getBean("namedParameterJdbcTemplate", NamedParameterJdbcTemplate.class); } /** * update(): 增删改操作 */ @Test public void testUpdate() { String sql = "insert into tbl_employee(last_name,email,gender) value(?,?,?)"; //jdbcTemplate.update(sql, "运慧","yh@atguigu.com",1); jdbcTemplate.update(sql, new Object[]{"QFX", "QFX@atguigu.com", 1}); } /** * batchUpdate(): 批量增删改 * 作业: 批量删 修改 */ @Test public void testBatchUpdate() { String sql = "insert into tbl_employee(last_name,email,gender) value(?,?,?)"; List<Object[]> batchArgs = new ArrayList<Object[]>(); batchArgs.add(new Object[]{"zsf", "zsf@sina.com", 1}); batchArgs.add(new Object[]{"zwj", "zwj@sina.com", 1}); batchArgs.add(new Object[]{"sqs", "sqs@sina.com", 1}); jdbcTemplate.batchUpdate(sql, batchArgs); } /** * queryForObject(): * 1. 查询单行数据 返回一个对象 * 2. 查询单值 返回单个值 */ @Test public void testQueryForObjectReturnObject() { String sql = "select id,last_name,email,gender from tbl_employee where id = ?"; //rowMapper: 行映射 将结果集的一条数据映射成具体的一个java对象. RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class); Employee employee = jdbcTemplate.queryForObject(sql, rowMapper, 1001); System.out.println(employee); } @Test public void testQueryForObjectReturnValue() { String sql = "select count(id) from tbl_employee"; Integer result = jdbcTemplate.queryForObject(sql, Integer.class); System.out.println(result); } /** * query(): 查询多条数据返回多个对象的集合. */ @Test public void testQuery() { String sql = "select id,last_name,email,gender from tbl_employee"; RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class); List<Employee> emps = jdbcTemplate.query(sql, rowMapper); System.out.println(emps); } /** * 测试具名参数模板类 */ @Test public void testNpjt() { String sql = "insert into tbl_employee(last_name,email,gender) values(:ln,:em,:ge)"; Map<String, Object> paramMap = new HashMap<>(); paramMap.put("ln", "Jerry"); paramMap.put("em", "jerry@sina.com"); paramMap.put("ge", 0); npjt.update(sql, paramMap); } @Test public void testNpjtObject() { //模拟Service层 直接传递给Dao层一个具体的 对象 Employee employee = new Employee(null, "张无忌", "zwj@sina.com", 1); //在dao的插入方法中: String sql = "insert into tbl_employee(last_name,email,gender) values(:lastName,:email,:gender)"; SqlParameterSource paramSource = new BeanPropertySqlParameterSource(employee); npjt.update(sql, paramSource); } }