Spring—连接数据库

1. 所需jar包

所需jar包如下图：

下载地址在：https://download.csdn.net/download/qq_35556254/11237862

2. 配置属性文档

db.properties

jdbc.user=root
jdbc.password=
jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.jdbcUrl=jdbc:mysql://localhost:3306/spring4

jdbc.initPoolSize=5
jdbc.maxPoolSize=10

3. 配置xml文档

applicationContext.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: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>
</beans>

3. java测试类

JDBCTest.java

package jdbc;

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

import javax.sql.DataSource;

import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;

public class JDBCTest {
	
	private ApplicationContext ctx = null;
	private JdbcTemplate jdbcTemplate;
	
	{
		ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
		jdbcTemplate = (JdbcTemplate) ctx.getBean("jdbcTemplate");
	}
	
	/*
	 * 执行批量更新：批量的insert， delete， update
	 * 最后一个参数是Objec[] 的list类型，因为修改一条记录需要一个Object的数组，那么多条就需要多个Object的数组
	 */
	@Test
	public void testBatchUpdate() {
		// TODO Auto-generated method stub
		String sql = "INSERT INTO employee(id, last_name, email, dept_id) VALUES(?, ?, ?, ?)";
		
		List<Object[]> batchArgs = new ArrayList<>();
		batchArgs.add(new Object[]{7, "AA", "aa@163.com", 1});
		batchArgs.add(new Object[]{8, "BB", "bb@163.com", 2});
		batchArgs.add(new Object[]{9, "CC", "cc@163.com", 3});
		batchArgs.add(new Object[]{10, "DD", "dd@163.com", 3});
		batchArgs.add(new Object[]{11, "EE", "ee@163.com", 2});
		jdbcTemplate.batchUpdate(sql, batchArgs);
		

	}
	/**
	 * 执行insert，update，delete
	 */
	@Test
	public void testUpdata() {
		// TODO Auto-generated method stub
		String sql = "UPDATE employee SET last_name = ? WHERE id = ?";
		jdbcTemplate.update(sql, "Tonny",5);
		System.out.println("success");
	}
	@Test
	public void testDataSource() throws SQLException {
		DataSource dataSource = (DataSource) ctx.getBean(DataSource.class);
		System.out.println(dataSource.getConnection());
	}
}

4. 数据库表结构

数据库employee表结构如下，自己建一个就好：