Spring学习笔记之Spring与JDBC整合

整合步骤：

１.配置数据源

 	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
 	 destroy-method="close"
 	>
 	 	 <property name="driverClassName" value="org.gjt.mm.mysql.Driver" />
 	 	 <property name="url" value="jdbc:mysql://localhost:3306/learning?useUnicode=true&characterEncoding=UTF-8"></property>
 	 	 <property name="username" value="root"></property>
 	 	 <property name="password" value="root"></property>
 	 	 <!-- 初始连接数 -->
 	 	 <property name="initialSize" value="1"></property>
 	 	 <!-- 最大连接数 -->
 	 	 <property name="maxActive" value="500"></property>
 	 	 <!-- 连接数大于maxIdle后开始释放连接 -->
 	 	 <property name="maxIdle" value="2"></property>
 	 	 <!-- 连接数小于minIdle后开始申请连接 -->
 	 	 <property name="minIdle" value="1"></property>
 	</bean>

２.配置事务

配置事务有两种方式，(1)注解方式(2)xml方式，　这里先使用注解方式配置

命名空间:

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

注解方式配置

 <!-- 采用@Transaction注解方式使用事务 -->
 	<tx:annotation-driven transaction-manager="txManager"/>

 	<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
 		<property name="dataSource" ref="dataSource"></property>
 	</bean>

３.数据库sql

create table userinfo(
id int(5) not null auto_increment,
name varchar(50) not null,
primary key(id)
);

４.pojo类

package com.skymr.spring.jdbc.pojo;

public class UserInfo {
//	org.apache.commons.dbcp2.BasicDataSource t = new org.apache.commons.dbcp2.BasicDataSource();
//	org.springframework.jdbc.datasource.DataSourceTransactionManager
	
	private int id;
	
	private String name;

	public int getId() {
//		t.setm
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}
	
	
	public String toString(){
		return "(" + id +"," + name + ")";
	}
	
}

５.创建业务层接口

package com.skymr.spring.jdbc.service;

import java.util.List;

import com.skymr.spring.jdbc.pojo.UserInfo;

public interface UserInfoService {

	public void save(UserInfo userInfo);
	
	public void update(UserInfo userInfo);
	
	public UserInfo getUserInfo(Integer userId);
	
	public List<UserInfo> getAllUserInfos();
	
	public void delete(Integer userId);
}

package com.skymr.spring.jdbc.service.impl;

import java.util.List;

import org.apache.commons.dbcp.BasicDataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.transaction.annotation.Transactional;

import com.skymr.spring.jdbc.pojo.UserInfo;
import com.skymr.spring.jdbc.service.UserInfoService;
@Transactional//告诉Spring UserInfoServiceBean受Spring事务管理
public class UserInfoServiceBean implements UserInfoService{

	//JDBC模版
	private JdbcTemplate jdbcTemp;
	
	public void setDataSource(BasicDataSource dataSource){
		jdbcTemp = new JdbcTemplate(dataSource);
	}
	
	public void delete(Integer userId) {
		jdbcTemp.update("delete from userinfo where id=?", new Object[]{userId}, new int[]{java.sql.Types.INTEGER});
	}

	public List<UserInfo> getAllUserInfos() {
		return (List<UserInfo>)jdbcTemp.query("select id,name from userinfo", new UserInfoRowMapper());
	}

	public UserInfo getUserInfo(Integer userId) {
		return (UserInfo)jdbcTemp.queryForObject("select id,name from userinfo where id=?", new UserInfoRowMapper(),
				new Object[]{userId}, new int[]{java.sql.Types.INTEGER});
	}

	public void save(UserInfo userInfo) {
		jdbcTemp.update("insert into userinfo (name) values (?)", new Object[]{userInfo.getName()}, 
				new int[]{java.sql.Types.VARCHAR}
		);
	}

	public void update(UserInfo userInfo) {
		jdbcTemp.update("update usreinfo set name=? where id=?", 
				new Object[]{userInfo.getName(), userInfo.getId()},
				new int[]{java.sql.Types.VARCHAR, java.sql.Types.INTEGER} 
				);
	}

}

ResultSet转UserInfo工具类:

package com.skymr.spring.jdbc.service.impl;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

import com.skymr.spring.jdbc.pojo.UserInfo;

public class UserInfoRowMapper implements RowMapper{
	/**
	 * resultSet 转pojo
	 */
	public Object mapRow(ResultSet resultSet, int rowNum) throws SQLException {
		UserInfo userInfo = new UserInfo();
		userInfo.setId(resultSet.getInt("id"));
		userInfo.setName(resultSet.getString("name"));
		return userInfo;
	}

}

配置业务实例：

 	<bean id="userInfoService" class="com.skymr.spring.jdbc.service.impl.UserInfoServiceBean">
 		<property name="dataSource" ref="dataSource"></property>
 	</bean>

JUnit测试：

package com.skymr.spring.jdbc.test;

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

import com.skymr.spring.jdbc.pojo.UserInfo;
import com.skymr.spring.jdbc.service.UserInfoService;

public class JDBCTest {

	@Test
	public void testJdbc(){
		ApplicationContext context = new ClassPathXmlApplicationContext("jdbcsource.xml");
		UserInfoService userInfoService = (UserInfoService)context.getBean("userInfoService");
		UserInfo userInfo = new UserInfo();
		userInfo.setName("windskymr");
		userInfoService.save(userInfo);
		
		userInfo.setName("naruto");
		userInfoService.save(userInfo);
		
		userInfo.setName("fengsheng");
		userInfoService.save(userInfo);
		
		System.out.println(userInfoService.getAllUserInfos());
		((ClassPathXmlApplicationContext)context).close();
	}
//	java.lang.AbstractMethodError: com.mysql.jdbc.Connection.isValid(I)Z

}

在测试时可能出现异常:

java.lang.AbstractMethodError: com.mysql.jdbc.Connection.isValid(I)Z

那是因为commons-dbcp,commons-pool与 mysql-connector的版本不合适

commons-dbcp,commons-pool的1.x版本与mysql-connector5.0对应