Spring之JdbcTemplate使用和配置

编写jdbc配置的db.properties文件

jdbc.user=root
jdbc.password=root
jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.jdbcUrl=jdbc:mysql:///spring

jdbc.initPoolSize=5
jdbc.maxPoolSize=10

配置C3P0数据库和jdbcTemplate

<?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:aop="http://www.springframework.org/schema/aop"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:jdbc="http://www.springframework.org/schema/jdbc"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.0.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd
		http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-4.0.xsd">

<!-- 导入资源文件 -->
<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="driverClass" value="${jdbc.driverClass}"></property>
	<property name="jdbcUrl" value="${jdbc.jdbcUrl}"></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>
</beans>

通过jdbcTemplate查询数据

class Test {

	ApplicationContext ctx = null;
	DataSource dataSource = null;
	JdbcTemplate jdbcTemplate = null;
	
	{
		ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
		dataSource = (DataSource) ctx.getBean("dataSource");
		jdbcTemplate = (JdbcTemplate) ctx.getBean("jdbcTemplate");
	}
	
	/**
	 * 测试是否连接到数据库
	 */
	@org.junit.jupiter.api.Test
	void test() throws SQLException {
		System.out.println(dataSource.getConnection());
	}
	
	/**
	 * 更新某一条数据
	 */
	@org.junit.jupiter.api.Test
	public void TestUpdate() {
		String sql = "update user set name = ? where id = ?";
		jdbcTemplate.update(sql, "xu1", 1);
	}
	
	/**
	 * 执行批量更新
	 * 最后一个参数是Object[] 的List类型
	 */
	@org.junit.jupiter.api.Test
	public void testBatchUpdate(){
		String sql = "INSERT INTO user(name, age, email) VALUES(?,?,?)";
		
		List<Object[]> batchArgs = new ArrayList<>();
		batchArgs.add(new Object[] {"xu11", "11","12121212"});
		batchArgs.add(new Object[] {"xu12", "12","12121212"});
		
		jdbcTemplate.batchUpdate(sql, batchArgs);
	}
	
	
	/**
	 * 从数据库中获取一条记录，实际得到对应的一个对象
	 * 需要调用：queryForObject(String sql, RowMapper<User> rowMapper, Object... args) 
	 * 1、其中的RowMapper指定如何去映射结果集的行，常用的实现类为BeanPropertyRowMapper
	 * 2、使用SQL中列的别名完成列名和类属性名的映射，例如name name
	 * 3、不支持级联属性，jdbcTemplate 到底是一个jdbc的小工具， 不是ORM框架
	 */
	@org.junit.jupiter.api.Test
	public void testQueryForObject() {
		String sql = "select id, name, age, email FROM user where id = ?";
		RowMapper<User> rowMapper = new BeanPropertyRowMapper<>(User.class);
		User user = jdbcTemplate.queryForObject(sql, rowMapper,1);
		System.out.println(user);
	}
	
	/**
	 * 查到实体类的集合
	 */
	@org.junit.jupiter.api.Test
	public void testQueryList() {
		String sql = "select id, name, age, email from user where id < ?";
		RowMapper<User> rowMapper = new BeanPropertyRowMapper<>(User.class);
		List<User> users = jdbcTemplate.query(sql, rowMapper, 4);
		
		System.out.println(users);
	}
	
	/**
	 * 获取单个列的值，或是统计查询
	 */
	@org.junit.jupiter.api.Test
	public void testQueryForObject2() {
		String sql = "select count(id) from user";
		long count = jdbcTemplate.queryForObject(sql, Long.class);
		System.out.println(count);
	}
}

编写实体类

public class User {
	private Integer id;
	private Integer age;
	private String name;
	private String email;
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public Integer getAge() {
		return age;
	}
	public void setAge(Integer age) {
		this.age = age;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	@Override
	public String toString() {
		return "User [id=" + id + ", age=" + age + ", name=" + name + ", email=" + email + "]";
	}
}