整合步骤:
1.配置数据源
<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>
2.配置事务
配置事务有两种方式,(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>
3.数据库sql
create table userinfo(
id int(5) not null auto_increment,
name varchar(50) not null,
primary key(id)
);
4.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 + ")";
}
}
5.创建业务层接口
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对应