JdbcTemplate
1、 概述
为了使JDBC更加易于使用，Spring在JDBC API上定义了一个抽象层，以此建立一个JDBC存取框架。
作为Spring JDBC框架的核心，JDBC模板的设计目的是为不同类型的JDBC操作提供模板方法，通过这种方式，可以在尽可能保留灵活性的情况下，将数据库存取的工作量降到最低。
可以将Spring的JdbcTemplate看作是一个小型的轻量级持久化层框架，和我们之前使用过的DBUtils风格非常接近。
2、环境准备：导入JAR包
1) IOC容器所需要的JAR包
commons-logging-1.1.1.jar
spring-beans-4.0.0.RELEASE.jar
spring-context-4.0.0.RELEASE.jar
spring-core-4.0.0.RELEASE.jar
spring-expression-4.0.0.RELEASE.jar
2) JdbcTemplate所需要的JAR包
spring-jdbc-4.0.0.RELEASE.jar
spring-orm-4.0.0.RELEASE.jar
spring-tx-4.0.0.RELEASE.jar
3) 数据库驱动和数据源
druid-1.1.9.jar
mysql-connector-java-5.1.7-bin.jar
3、创建连接数据库基本信息属性文件
jdbc.driver=com.mysql.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/ssm jdbc.username=root jdbc.password=123456
4、在Spring配置文件中配置相关的bean
<?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-4.0.xsd"> <!-- 引入属性文件 --> <!-- <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"> <property name="location" value="db.properties"></property> </bean> --> <!-- 引入属性文件 --> <context:property-placeholder location="db.properties"/> <!-- 创建数据源 --> <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"> <property name="driverClassName" value="${jdbc.driver}"></property> <property name="url" value="${jdbc.url}"></property> <property name="username" value="${jdbc.username}"></property> <property name="password" value="${jdbc.password}"></property> </bean> <!-- 通过数据源配置JdbcTemplate --> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource"></property> </bean> </beans>
5、持久化操作
1) 增删改
JdbcTemplate.update(String, Object...)
2) 批量增删改
JdbcTemplate.batchUpdate(String, List<Object[]>)
Object[]封装了SQL语句每一次执行时所需要的参数
List集合封装了SQL语句多次执行时的所有参数
3) 查询单行
JdbcTemplate.queryForObject(String, RowMapper<Department>, Object...)
4) 查询多行
JdbcTemplate.query(String, RowMapper<Department>, Object...)
RowMapper对象依然可以使用BeanPropertyRowMapper
5) 查询单一值
JdbcTemplate.queryForObject(String, Class, Object...)
package com.atguigu.jdbctempalte; import static org.junit.Assert.*; import java.util.ArrayList; import java.util.List; 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; public class TestJdbcTemplate { ApplicationContext ac = new ClassPathXmlApplicationContext("jdbc.xml"); JdbcTemplate jdbcTemplate = ac.getBean("jdbcTemplate", JdbcTemplate.class); @Test public void testUpdate() { //jdbcTemplate.update("insert into emp values(null,‘张三‘,23,‘男‘)"); /*String sql = "insert into emp values(null, ?, ?, ?)"; jdbcTemplate.update(sql, "李四", 24, "女");//单个增删改*/ String eids = "3,4,5"; String sql = "delete from emp where eid in ("+eids+")"; jdbcTemplate.update(sql); String * = "a"; String sqls = "select * from emp where ename like ‘%?%‘";//不能使用 String sqlss = "select * from emp where ename like concat(‘%‘, ? ,‘%‘)";//可以使用 //不能使用通配符？的情况 //① where id in (?) ->使用字符串拼接 //② 模糊查询 like ‘?‘ ->采用concat()函数拼接 } @Test public void testBatchUpdate() { String sql = "insert into emp values(null, ?, ?, ?)"; List<Object[]> list = new ArrayList<>(); list.add(new Object[] {"a1", 1, "男"}); list.add(new Object[] {"a2", 2, "男"}); list.add(new Object[] {"a3", 3, "男"}); jdbcTemplate.batchUpdate(sql, list);//批量增删改 } @Test public void testQueryForObject() { //jdbcTemplate.queryForObject(sql, requiredType)用来获取单个的值 //jdbcTemplate.queryForObject(sql, rowMapper)用来获取单条数据 /*String sql = "select eid,ename,age,sex from emp where eid = ?"; RowMapper<Emp> rowMapper = new BeanPropertyRowMapper<>(Emp.class);//将列名（字段名或字段名的别名）与属性名进行映射 Emp emp = jdbcTemplate.queryForObject(sql, new Object[] {7}, rowMapper); System.out.println(emp);*/ String sql = "select count(*) from emp"; Integer count = jdbcTemplate.queryForObject(sql, Integer.class); System.out.println(count); } @Test public void testQuery() { String sql = "select eid,ename,age,sex from emp"; RowMapper<Emp> rowMapper = new BeanPropertyRowMapper<>(Emp.class); List<Emp> list = jdbcTemplate.query(sql, rowMapper); for (Emp emp : list) { System.out.println(emp); } } }
package com.atguigu.jdbctempalte; public class Emp { private Integer eid; private String ename; private Integer age; private String sex; public Integer getEid() { return eid; } public void setEid(Integer eid) { this.eid = eid; } public String getEname() { return ename; } public void setEname(String ename) { this.ename = ename; } public Integer getAge() { return age; } public void setAge(Integer age) { this.age = age; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } @Override public String toString() { return "Emp [eid=" + eid + ", ename=" + ename + ", age=" + age + ", sex=" + sex + "]"; } }