Spring框架学习（13）：JdbcTemplate和JdbcDaoSupport

最新推荐文章于 2024-10-09 08:28:28 发布

zhanglq23

最新推荐文章于 2024-10-09 08:28:28 发布

阅读量680

点赞数

分类专栏： Spring框架学习文章标签： Spring mysql c3p0 数据库

本文链接：https://blog.csdn.net/qq_24627733/article/details/70160025

版权

Spring框架学习专栏收录该内容

13 篇文章 0 订阅

订阅专栏

这篇文章主要讲一下使用JdbcTemplate连接数据库

我在本地使用的数据库是MySQL5.7

为了使用JdbcTemplate和连接上数据库，需要添加c3p0的依赖包和mysql-connectorJ的依赖包,我是使用maven添加的依赖：

		<!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
		<dependency>
		    <groupId>org.springframework</groupId>
		    <artifactId>spring-jdbc</artifactId>
		    <version>4.3.7.RELEASE</version>
		</dependency>
		
		<!-- https://mvnrepository.com/artifact/org.springframework/spring-core -->
		<dependency>
		    <groupId>org.springframework</groupId>
		    <artifactId>spring-core</artifactId>
		    <version>4.3.7.RELEASE</version>
		</dependency>
		
		<!-- https://mvnrepository.com/artifact/com.mchange/c3p0 -->
		<dependency>
		    <groupId>com.mchange</groupId>
		    <artifactId>c3p0</artifactId>
		    <version>0.9.5.2</version>
		</dependency>
		
		<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
		<dependency>
		    <groupId>mysql</groupId>
		    <artifactId>mysql-connector-java</artifactId>
		    <version>6.0.6</version>
		</dependency>

在MySQL中使用的数据表为：

下面写两个类：Department和employee

package jdbc;

public class Department {
	private Integer id;
	private String name;
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	@Override
	public String toString() {
		return "Department [id=" + id + ", name=" + name + "]";
	}
	
	
}

package jdbc;

public class Employee {
	
	private Integer id;
	private String lastName;
	private String email;
	
	private Integer deptId;

	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 getDeptId() {
		return deptId;
	}

	public void setDeptId(Integer deptId) {
		this.deptId = deptId;
	}

	@Override
	public String toString() {
		return "Employee [id=" + id + ", lastName=" + lastName + ", email="
				+ email + ", deptId=" + deptId + "]";
	}	
	
}

然后写两个数据访问层的Dao类

package jdbc;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;

/**
 * 
 * 不推荐使用JdbcDaoSupport，而推荐直接使用JdbcTempate作为Dao类的成员变量
 * @author 86538
 *
 */
@Repository
public class DepartmentDao extends JdbcDaoSupport {
	
	@Autowired
	public void setDataSource2(DataSource dataSource) {
		setDataSource(dataSource);
	}
	
	public Department get(Integer id) {
		String sql = "SELECT id, dept_name name FROM department WHERE id = ?";
		RowMapper<Department> rowMapper = new BeanPropertyRowMapper<>(Department.class);
		return getJdbcTemplate().queryForObject(sql, rowMapper, id);
	}
}

package jdbc;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

@Repository
public class EmployeeDao {
	
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	public Employee get(Integer id) {
		String sql = "SELECT id, last_name lastName, email FROM employees WHERE id = ?";
		RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class);;
		Employee employees = jdbcTemplate.queryForObject(sql, rowMapper, id);
		
		return employees;
	}
	
}

在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"
	xmlns:tx="http://www.springframework.org/schema/tx"
	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-3.2.xsd
		http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.2.xsd">

	<context:component-scan base-package="jdbc"></context:component-scan>
	
	<!-- 导入资源文件 -->
	<context:property-placeholder location="classpath:db.properties"/>

	<!-- 配置c3p0数据源 -->
	<bean id="dataSource"
		class="com.mchange.v2.c3p0.ComboPooledDataSource">
		<property name="user" value="${jdbc.user}"></property>
		<property name="password" value="${jdbc.password}"></property>
		<property name="jdbcUrl" value="${jdbc.jdbcurl}"></property>
		<property name="driverClass" value="${jdbc.driverclass}"></property>
		
		<property name="initialPoolSize" value="${jdbc.initPoolSize}"></property>
		<property name="maxPoolSize" value="${jdbc.maxPoolSize}"></property>
	</bean>
	
	<!-- 配置Spring的JdbcTemplate -->
	<bean id="jdbcTemplate"
		class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource"></property>
	</bean>
	
	<!-- 配置 NameParameterJdbcTemplate,该对象可以使用具名参数，其没有无参数的构造器，所以必须为其构造器指定参数-->
	<bean id="namedParameterJdbcTemplate"
		class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
		<constructor-arg ref="dataSource"></constructor-arg>
	</bean>

</beans>

property配置文件：

jdbc.user=root
jdbc.password=1234
jdbc.driverclass=com.mysql.cj.jdbc.Driver
jdbc.jdbcurl=jdbc:mysql:///spring-4?serverTimezone=UTC&useSSL=true

jdbc.initPoolSize=5
jdbc.maxPoolSize=10

然后再弄个junit的test文件测试一下效果：

package jdbc;

import static org.junit.Assert.*;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

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;

public class JDBCTest {
	
	private ApplicationContext ctx = null;
	private JdbcTemplate jdbcTemplate;
	private EmployeeDao employeeDao;
	private DepartmentDao departmentDao;
	private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
	
	{
		ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
		jdbcTemplate = (JdbcTemplate)ctx.getBean("jdbcTemplate");
		employeeDao = ctx.getBean(EmployeeDao.class);
		departmentDao = ctx.getBean(DepartmentDao.class);
		namedParameterJdbcTemplate = ctx.getBean(NamedParameterJdbcTemplate.class);
	}
	
	/**
	 * 使用具名参数时，可以使用NamedParameterJdbcTemplate.update(String sql, SqlParameterSource paramSource) 
	 * 方法进行更新操作
	 * 1.SQL语句中的参数名和类的属性名一致
	 * 2.使用SqlParameterSource的BeanPropertySqlParameterSource实现类作为参数
	 */
	@Test
	public void testNamedParameterJdbcTemplate2() {
		String sql = "INSERT INTO employees(last_name, email, dept_id) VALUES(:lastName,:email,:deptId)";
		
		Employee employee = new Employee();

		employee.setLastName("XYZ");
		employee.setEmail("xyz@sina.com");
		employee.setDeptId(3);
		
		SqlParameterSource paramSource = new BeanPropertySqlParameterSource(employee);
		namedParameterJdbcTemplate.update(sql, paramSource);
	}
	
	/**
	 * 可以为参数起名字，与？相比
	 * 1.好处：若有多个参数，则不用再去对应位置，直接对应参数名字，如last_name
	 * 2.缺点：较为麻烦 
	 */
	@Test
	public void testNamedParameterJdbcTemplate() {
		String sql = "INSERT INTO employees(last_name, email, dept_id) VALUES(:ln,:email,:deptid)";
		
		Map<String, Object> paramMap = new HashMap<>();
		paramMap.put("ln", "FF");
		paramMap.put("email", "FF@atguigu.com");
		paramMap.put("deptid", 2);
		
		namedParameterJdbcTemplate.update(sql, paramMap);
	}
	
	@Test
	public void testDepartmentDao() {
		System.out.println(departmentDao.get(1));
	}
	
	@Test
	public void testEmployeeDao() {
		System.out.println(employeeDao.get(1));
	}
	
	/**
	 * 获取单个列的值，或做统计查询
	 */
	@Test
	public void testQueryForObject2() {
		String sql = "SELECT count(id) FROM employees";
		long count = jdbcTemplate.queryForObject(sql, Long.class);
		
		System.out.println(count);
		
	}
	
	
	/**
	 * 查到实体类的集合
	 * 注意不是调用queryForList方法
	 */
	@Test
	public void testQueryForList() {
		String sql = "SELECT id, last_name lastName, email FROM employees WHERE id > ?";
		RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class);;
		List<Employee> employees = jdbcTemplate.query(sql, rowMapper, 5);
		
		System.out.println(employees);
	}
	
	
	
	/**
	 * 从数据库中获取一条记录，实际得到对应的对象
	 * 注意不是调用jdbcTemplate.queryForObject(sql, requiredType, args)方法
	 * 而是使用queryForObject(sql, rowMapper,1);
	 * 1.其中RowMapper指定如何去映射结果集的行，常用的实现类为BeanPropertyRowMapper
	 * 2.使用SQL中列的别名完成列名和类的属性名的映射，例如last_name lastName
	 * 3.不支持级联属性，JdbcTemplate到底是一个JDBC的小工具，而不是ORM框架
	 */
	@Test
	public void testQueryForObject() {
		String sql = "SELECT id,last_name lastName, email, dept_id as \'department.id\' FROM employees WHERE id = ?";
		RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class);
		Employee employee = jdbcTemplate.queryForObject(sql, rowMapper,1);
		
		System.out.println(employee);
	}
	
	
	/**
	 * 执行批量更新：批量的IUD
	 * 最后一个参数是Object[]的list类型：因为修改一条记录需要一个Object的数组，那么多条就需要一个List
	 */
	public void testBatchUpdate() {
		String sql = "INSERT INTO employees(id,last_name, email, dept_id) VALUES(?,?,?,?)";
		
		List<Object[]> batchArgs = new ArrayList<>();
		
		batchArgs.add(new Object[]{6,"AA", "aa@atguigu.com", 1});
		batchArgs.add(new Object[]{7,"BB", "bb@atguigu.com", 1});
		batchArgs.add(new Object[]{8,"CC", "cc@atguigu.com", 1});
		batchArgs.add(new Object[]{9,"DD", "dd@atguigu.com", 1});
		batchArgs.add(new Object[]{10,"EE","ee@atguigu.com", 1});
		
		jdbcTemplate.batchUpdate(sql, batchArgs);
		
	}
	
	/*
	 * INSERT,UPDATE,DELETE
	 */
	//@Test
	public void testUpdata() {
		String sql = "UPDATE employees SET last_name = ? WHERE id = ?";
		jdbcTemplate.update(sql, "Jack", 5);
	}
	
	//@Test
	public void testDataSource() throws SQLException {
		DataSource dataSource = ctx.getBean(DataSource.class);
		System.out.println(dataSource.getConnection());
	}

运行之后可以看到程序是可以正常运行的。如果出问题，一般是因为依赖的jar包没有成功加进来或者是加的版本不对，可以在maven里面尝试多几个版本，还有就是MySQL的版本问题，在SQL5中driverClass是com.mysql.jdbc.Driver，jdbcurl是jdbc:mysql:///databaseName，在SQL6中driverClass是com.mysql.cj.jdbc.Driver,jdbcurl需要加上服务器时间域serverTimezone和是否使用加密useSSL等配置信息，出问题的话自己看报错信息一般都能解决。