JdbcTemplate

最新推荐文章于 2020-11-30 10:43:52 发布

duanqwei

最新推荐文章于 2020-11-30 10:43:52 发布

阅读量110

点赞数

本文链接：https://blog.youkuaiyun.com/weixin_45394128/article/details/100527295

版权

JdbcTemplate

简介

为了使JDBC更加易于使用，Spring在JDBC API上定义了一个抽象层，以此建立一个JDBC存取框架。

作为Spring JDBC框架的核心，JDBC模板的设计目的是为不同类型的JDBC操作提供模板方法，通过这种方式，可以在尽可能保留灵活性的情况下，将数据库存取的工作量降到最低。

①环境准备

所需要的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

（4）为了单元测试方便，导入spring提供的对测试框架支持的jar包

spring-test-4.0.0.RELEASE.jar

②数据库中创建测试表tbl_employee

CREATE TABLE `tbl_employee` (
  `id` int(11) NOT NULL,
  `last_name` varchar(50) DEFAULT NULL,
  `gender` char(1) DEFAULT NULL,
  `descr` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

③创建与表对应的实体类

package spring04_my.exer02;

public class Employee {
	private Integer id;
	private String lastName;
	private Integer gender;
	private String secr;
	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 Integer getGender() {
		return gender;
	}
	public void setGender(Integer gender) {
		this.gender = gender;
	}
	public String getSecr() {
		return secr;
	}
	public void setSecr(String secr) {
		this.secr = secr;
	}
	@Override
	public String toString() {
		return "Employee [id=" + id + ", lastName=" + lastName + ", gender=" + gender + ", secr=" + secr + "]";
	}
	
}

④创建数据连接的db.properties属性配置文件

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test01?characterEncoding=utf8
jdbc.username=root
jdbc.password=123456

⑤创建并配置spring_jdbc.xml配置文件

<?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">
	
	<!-- 组件扫描 -->
	<context:component-scan base-package="spring_my.exer02"></context:component-scan>
	
	<!-- 读取外部配置文件 -->
	<context:property-placeholder location="classpath:db.properties"/>
		
	<!-- 配置数据源，连接池 -->
	<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
		<property name="driverClassName" value="${jdbc.driver}"/>
		<property name="url" value="${jdbc.url}"/>
		<property name="username" value="${jdbc.username}"/>
		<property name="password" value="${jdbc.password}"/>
	</bean>
	
	<!-- 配置JdbcTemplate -->
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource"/>
	</bean>
</beans>

⑥创建单元测试类，方便后续测试

package spring04_my.exer02;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

@RunWith(SpringJUnit4ClassRunner.class)//指定Spring的运行环境
@ContextConfiguration("classpath:spring_jdbc.xml")//指定Spring的配置文件
public class TestJdbcTemplate {

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@Test
	public void test() {
		System.out.println(jdbcTemplate);
	}	
}

⑦持久化操作

（1）增加

@Test
public void testUpdate() {
		String sql = "insert into tbl_employee(last_name,gender,descr) values(?,?,?)";
		jdbcTemplate.update(sql, "张三丰",0,"武当派掌门");
}

(2)修改

@Test
public void testUpdate() {
		String sql = "UPDATE tbl_employee SET last_name=? WHERE id=?";
		jdbcTemplate.update(sql,"张无忌",1 );
}

(3)删除

@Test
public void testUpdate() {
		String sql = "DELETE FROM tbl_employee WHERE id =?";
		jdbcTemplate.update(sql,1);
}

(4)批量增加

@Test
	public void testUpdate() {
		String sql = "insert into tbl_employee(last_name,gender,descr) values(?,?,?)";
        /*
        JdbcTemplate.batchUpdate(String, List<Object[]>)
		Object[]封装了SQL语句每一次执行时所需要的参数
		List集合封装了SQL语句多次执行时的所有参数
        */
		List<Object[]> batchArgs = new ArrayList<Object[]>();
		batchArgs.add(new Object[] {"乔峰",1,"大哥"});
		batchArgs.add(new Object[] {"虚竹",1,"二哥"});
		batchArgs.add(new Object[] {"段誉",1,"三弟"});
		jdbcTemplate.batchUpdate(sql, batchArgs);
	}

（5）查询单行

/**
		 * 查询单行
		 * 		RowMapper<Employee>的泛型指定queryForObject方法的返回值类型
		 * 		Employee.class  将查询的记录转换为哪种类型
		 */
		@Test
		public void testQueryForObjectReturnBean() {
			String sql = "select id,last_name,gender,descr from tbl_employee where id = ?";
			RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<Employee>(Employee.class);
			// last_name ===> lastName  
			Employee employee = jdbcTemplate.queryForObject(sql, rowMapper, 3);
			System.out.println(employee);
		}

（6）查询单个值

/**
	 * 查询单个值
	 */
	@Test
	public void testQueryForObjectReturnSingleValue() {
		String sql = "select count(id) from tbl_employee";
		Integer num = jdbcTemplate.queryForObject(sql, Integer.class);
		System.out.println(num);
	}

（7）查询多行

/**
	 * 查询多行
	 */
	@Test
	public void testQuery() {
		String sql = "select id,last_name,gender,descr from tbl_employee";
		RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<Employee>(Employee.class);
		List<Employee> emps = jdbcTemplate.query(sql, rowMapper);
		System.out.println(emps);
	}