spring学习随笔7

本文介绍spring的jdbctemplate和事务管理,基于xml配置

 

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

jdbcconfigxml.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"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xsi:schemaLocation="http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.2.xsd
		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-3.2.xsd
		http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.2.xsd
		http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.3.xsd">

	<!-- 配置  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 -->
	<bean id="userDAO" class="com.liu.xml.dao.UserDAO">
		<property name="jdbcTemplate" ref="jdbcTemplate"></property>
	</bean>
	
	<bean id="userService" class="com.liu.xml.service.UserService">
		<property name="userDAO" ref="userDAO"></property>
	</bean>
	
	<!-- 配置事务管理器 -->
	<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
		<property name="dataSource" ref="dataSource"></property>
	</bean>
	
	<tx:advice id="txAdivce" transaction-manager="transactionManager">
		<tx:attributes>
			<tx:method name="*"/>
		</tx:attributes>
	</tx:advice>
	
	<aop:config>
		<aop:pointcut expression="execution(* com.liu.xml.service.IUserService.*(..))" id="pointCut1"/>
		<aop:advisor advice-ref="txAdivce" pointcut-ref="pointCut1"/>
	</aop:config>
</beans>

IUserDAO.java

package com.liu.xml.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.xml.dao;


import org.springframework.jdbc.core.JdbcTemplate;

public class UserDAO implements IUserDAO {
	
	private JdbcTemplate jdbcTemplate;
	
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.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.xml.service;

public interface IUserService {
	public void updateInfo(String userName);
}

UserService.java

package com.liu.xml.service;

import com.liu.xml.dao.IUserDAO;

/**
 * @author Administrator
 *
 */
public class UserService implements IUserService {

	private IUserDAO userDAO;
	
	public void setUserDAO(IUserDAO userDAO) {
		this.userDAO = userDAO;
	}
	

	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.xml.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.xml.dao.IUserDAO;
import com.liu.xml.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("jdbcconfigxml.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("TESTER22");
	}
	
	@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");
	}

}