【Spring】spring对jdbc的优化

使用步骤:

先引入jdbc相关jar包

然后在db.properties中配置连接数据库的信息

jdbc.user=root
jdbc.password=root
jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.jdbcUrl=jdbc:mysql:///day09
jdbc.initPoolSize=5
jdbc.maxPoolSize=10
jdbc.maxStatements=100
jdbc.acquireIncrement=2

接下来创建Dept对象

package cn.qblank.h_jdbc;

public class Dept {
	private int deptId;
	private String deptName;
	public int getDeptId() {
		return deptId;
	}
	public void setDeptId(int deptId) {
		this.deptId = deptId;
	}
	public String getDeptName() {
		return deptName;
	}
	public void setDeptName(String deptName) {
		this.deptName = deptName;
	}
	@Override
	public String toString() {
		return "Dept [deptId=" + deptId + ", deptName=" + deptName + "]";
	}
}

编写对应的UserDao类

package cn.qblank.h_jdbc;

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

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

public class UserDao {
	//加入IOC容器中
	private JdbcTemplate jdbcTemplate;
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
	/**
	 * 添加数据
	 */
	public void save(){
		String sql = "insert into dept(deptName) value('赵六')";
		jdbcTemplate.update(sql);
	}
	/**
	 * 通过id查找数据
	 * @param id
	 * @return
	 */
	public Dept findById(int id){
		String sql = "select * from dept where deptId = ?";
		List<Dept> list = jdbcTemplate.query(sql, new MyResult(),id);
		return (list!=null && list.size() >0)?list.get(0):null;
	}
	/**
	 * 查询所有
	 * @return
	 */
	public List<Dept> findAll(){
		String sql = "select * from dept";
		List<Dept> list = jdbcTemplate.query(sql, new MyResult());
		return list;
	}
	
	//结果集内部类
	class MyResult implements RowMapper<Dept>{
		@Override
		public Dept mapRow(ResultSet rs, int index) throws SQLException {
			Dept dept = new Dept();
			dept.setDeptId(rs.getInt("deptId"));
			dept.setDeptName(rs.getString("deptName"));
			return dept;
		}
	}
}

然后配置bean.xml,将对象（userDao和jdbcTemplate）加入IOC容器以及初始化c3p0连接池

<?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:p="http://www.springframework.org/schema/p"
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:aop="http://www.springframework.org/schema/aop"
    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.xsd
        http://www.springframework.org/schema/aop
        http://www.springframework.org/schema/aop/spring-aop.xsd">
        <!-- 导入资源文件 -->
        <context:property-placeholder location="classpath:db.properties"/>
        <!-- 配置c3p0连接池 -->
        <bean name="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        	<property name="driverClass" value="${jdbc.driverClass}"></property>
        	<property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property>
        	<property name="user" value="${jdbc.user}"></property>
        	<property name="password" value="${jdbc.password}"></property>
        	<property name="initialPoolSize" value="${jdbc.initPoolSize}"></property>
        	<property name="maxPoolSize" value="${jdbc.maxPoolSize}"></property>
        	<property name="maxStatements" value="${jdbc.maxStatements}"></property>
        	<property name="acquireIncrement" value="${jdbc.acquireIncrement}"></property>
        </bean>
		<!-- 创建jdbcTemplate对象 -->
		<bean name="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
			<!-- 加入c3p0配置 -->
			<property name="dataSource" ref="dataSource"></property>
		</bean>        
        <!-- jdbcTemplate实例 -->
        <bean id="userDao" class="cn.qblank.h_jdbc.UserDao">
        	<property name="jdbcTemplate" ref="jdbcTemplate"></property>
        </bean>
</beans>

接下来我们来测试各个方法

package cn.qblank.h_jdbc;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class Test {
	public static void main(String[] args) {
		ApplicationContext ac = new ClassPathXmlApplicationContext("cn/qblank/h_jdbc/bean.xml");
		UserDao userDao = (UserDao) ac.getBean("userDao");
		userDao.save();
		System.out.println(userDao.findById(3));
		System.out.println(userDao.findAll());
		ac = null;
	}
}

插入的数据在数据库中也能看到了