spring中jdbcTemplate以及事务的使用,本文采用注解方式配置
db.properties
jdbc.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
jdbc.url=jdbc:sqlserver://SKY-20140109AJI:1433;databaseName=fxcl
jdbc.user=fxcl
jdbc.password=fxcl
jdbc.minPoolSize=5
jdbc.maxPoolSize=10
jdbcconfig.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:aop="http://www.springframework.org/schema/aop"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:jdbc="http://www.springframework.org/schema/jdbc"
xmlns:p="http://www.springframework.org/schema/p"
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-3.2.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd
http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.2.xsd">
<context:component-scan base-package="com.liu.dao,com.liu.service"></context:component-scan>
<!-- 配置 config -->
<bean id="propertyPlaceholderConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="location" value="classpath:db.properties"></property>
</bean>
<!-- 配置数据源 -->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="${jdbc.driver}"></property>
<property name="jdbcUrl" value="${jdbc.url}"></property>
<property name="user" value="${jdbc.user}"></property>
<property name="password" value="${jdbc.password}"></property>
<property name="minPoolSize" value="${jdbc.minPoolSize}"></property>
<property name="maxPoolSize" value="${jdbc.maxPoolSize}"></property>
</bean>
<!-- 配置jdbcTemplate -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
<!-- 配置事务管理器 -->
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"></property>
</bean>
</beans>
IUserDAO.java
package com.liu.dao;
public interface IUserDAO {
public int selectUserMaxId();
public void updateUser(int id,String userName);
public int selectAmountMaxId();
public void updateAmount(int id,int userId,int amount,int year,int month);
}
UserDAO.java
package com.liu.dao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
@Repository
public class UserDAO implements IUserDAO {
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public int selectUserMaxId() {
String sql="SELECT MAX(ID) FROM [fxcl].[dbo].[TUSER]";
return jdbcTemplate.queryForObject(sql, int.class);
}
@Override
public void updateUser(int id, String userName) {
String sql="INSERT INTO [fxcl].[dbo].[TUSER] (ID,NAME) VALUES (?,?)";
jdbcTemplate.update(sql, id,userName);
}
@Override
public int selectAmountMaxId() {
String sql="SELECT MAX(ID) FROM [fxcl].[dbo].[TINCOME]";
return jdbcTemplate.queryForObject(sql, int.class);
}
@Override
public void updateAmount(int id, int userId, int amount, int year, int month) {
String sql="INSERT INTO [fxcl].[dbo].[TINCOME] (ID,USERID,AMOUNT,YEAR,MONTH) VALUES (?,?,?,?,?)";
jdbcTemplate.update(sql, id,userId,amount,year,month);
}
}
IUserService.java
package com.liu.service;
public interface IUserService {
public void updateInfo(String userName);
}
UserService.java
package com.liu.service;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import com.liu.dao.IUserDAO;
@Service
public class UserService implements IUserService {
@Autowired
private IUserDAO userDAO;
@Transactional
@Override
public void updateInfo(String userName) {
int userId;
int amountId;
userId = userDAO.selectUserMaxId()+1;
userDAO.updateUser(userId, userName);
amountId = userDAO.selectAmountMaxId()+1;
userDAO.updateAmount(amountId, userId, 100, 2017, 2);
}
}
TestJdbc.java
package com.liu.test;
import static org.junit.Assert.fail;
import java.sql.SQLException;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import com.liu.dao.IUserDAO;
import com.liu.service.IUserService;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class TestJdbc {
private ComboPooledDataSource dataSource;
private JdbcTemplate jdbcTemplate;
private IUserDAO userDAO;
private IUserService userService;
public TestJdbc(){
ApplicationContext ctx = new ClassPathXmlApplicationContext("jdbcconfig.xml");
dataSource = (ComboPooledDataSource)ctx.getBean("dataSource");
jdbcTemplate = (JdbcTemplate)ctx.getBean("jdbcTemplate");
userDAO =(IUserDAO)ctx.getBean("userDAO");
userService = (IUserService)ctx.getBean("userService");
}
@Test
public void testUpdateInfo(){
userService.updateInfo("TESTER");
}
@Test
public void testSelectUserMaxId(){
System.out.println(userDAO.selectUserMaxId());
}
@Test
public void testAddUser(){
String sql = "INSERT INTO [fxcl].[dbo].[TUSER] (ID,NAME) VALUES(?,?)";
jdbcTemplate.update(sql, "3","Yan");
}
@Test
public void testDataSource(){
try {
System.out.println(dataSource.getConnection());
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@Test
public void test() {
fail("Not yet implemented");
}
}