[セットトップ]Springフレーム-JDBCのサポート

public class JDBCTest {

	private ApplicationContext applicationContext = null;
	private JdbcTemplate jdbcTemplate = null;
	{
		applicationContext = new ClassPathXmlApplicationContext("applicationContext-jdbc.xml");
		jdbcTemplate = (JdbcTemplate) applicationContext.getBean("jdbcTemplate");
	}

	/*
	 *        ，      
	 *    JdbcTemplate.queryForObject(String sql, Class<T> requiredType)  
	 */
	@Test
	public void testQueryForObject2(){
		String sql= "SELECT count(id) FROM employees";
		Long count = jdbcTemplate.queryForObject(sql, Long.class);
		System.out.println(count);
	}
	/*
	 *         
	 *   JdbcTemplate.query(String sql, RowMapper<Employee> rowMapper, Object... args)   
	 *        queryForList()  
	 */
	@Test
	public void testQueryForList(){
		String sql="SELECT id, name, email FROM employees WHERE id>?";
		RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class);
		List<Employee> list = jdbcTemplate.query(sql,rowMapper,4);
		System.out.println(list);
	}
	
	/*
	 *           ，       
	 *       JdbcTemplate.queryForObject(String sql, Class<Employee> requiredType, Object... args)  
	 *     JdbcTemplate.queryForObject(String sql, RowMapper<Employee> rowMapper, Object... args)  
	 *   1.RowMapper            ，       BeanPropertyRowMapper
	 * 2.  sql                  
	 * 3.       ，JdbcTemplate   JDBC      ，   ORM  
	 */
	@Test
	public void testQueryForObject(){
		String sql="SELECT id, name, email FROM employees WHERE id=?";
//		Employee employee = jdbcTemplate.queryForObject(sql, Employee.class,5);
		RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class);
		Employee employee = jdbcTemplate.queryForObject(sql, rowMapper,3);
		System.out.println(employee);
				
	}
	
	//    ,  JdbcTemplate    batchUpdate(String sql, List<Object[]> batchArgs)  
	@Test
	public void testBatchUpdate(){
		String sql = "INSERT INTO employees(name, email, dept_id) VALUES(?,?,?)";
		List<Object[]> batchArgs= new ArrayList<>();
		batchArgs.add(new Object[]{"jack2","[email protected]",5});
		batchArgs.add(new Object[]{"jack3","[email protected]",5});
		batchArgs.add(new Object[]{"jack4","[email protected]",5});
		jdbcTemplate.batchUpdate(sql, batchArgs);
	}
	//INSERT , DELETE, UPDATE   JdbcTemplate    update()  
	@Test
	public void testUpdate(){
		String sql="INSERT employees VALUES(4,'yuchen','[email protected]',3)";
		jdbcTemplate.update(sql);
		
		sql = "UPDATE employees SET name=? WHERE id=?";
		jdbcTemplate.update(sql, "jack",1);
		
		sql="DELETE FROM employees WHERE id=?";
		jdbcTemplate.update(sql, 1);
	}
}

//     ，    context:component-scan  
@Repository
public class EmployeeDAO {

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	public Employee  getEmployeeById(int id){
		String sql = "SELECT id, name, email FROM employees WHERE id=?";
		RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<>(Employee.class);
		if(jdbcTemplate==null){
			System.out.println("null");
		}
		Employee employee = jdbcTemplate.queryForObject(sql, rowMapper,id);
		return employee;
	}
	
	@Test
	public void testGetEmployeeById(){
		ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext-jdbc.xml");
		//   EmployeeDAO      IOC      ，  new     ，         jdbcTemplate
		//  new     ，jdbcTemplate    
		EmployeeDAO employeeDAO = (EmployeeDAO) applicationContext.getBean("employeeDAO");
		Employee employee = employeeDAO.getEmployeeById(3);
		System.out.println(employee);
	}
}

//     JdbcDaoSupport，     JdbcTemplate        
//  JdbcDaoSupport  ，    dataSource  
@Repository
public class DepartmentDAO extends JdbcDaoSupport{

	//    JdbcDaoSupport setDataSource()   finally，    ，     
	//     ，      
	@Autowired//      
	public void setDataSource2(DataSource dataSource){
		setDataSource(dataSource);
	}
	
	public Department get(Integer id){
		String sql= "SELECT id,dept_name name FROM department WHERE id = ?";
		RowMapper<Department> rowMapper = new BeanPropertyRowMapper<>(Department.class);
		//  JdbcDaoSupprot     JdbcTemplate          
		Department department = getJdbcTemplate().queryForObject(sql, rowMapper,id);
		return department;
	}
	
	@Test
	public void testGet(){
		ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext-jdbc.xml");
		DepartmentDAO departmentDAO = (DepartmentDAO) ctx.getBean("departmentDAO");
		Department department = departmentDAO.get(1);
		System.out.println(department);
	}
	
}

<context:component-scan base-package="com.cqupt.spring.jdbc"></context:component-scan>

	/*
	 *   NamedParameterJdbcTemplate，
	 *   ：        ，       ，    
	 *   ：    
	 */
	@Test
	public void testNamedParameterJdbcTemplate(){
//		String sql="INSERT INTO employees VALUES(?,?,?)";
		String sql="INSERT INTO employees(name,email,dept_id) VALUES(:name,:email,:deptId)";
		NamedParameterJdbcTemplate njt = (NamedParameterJdbcTemplate) applicationContext.getBean("namedParameterJdbcTemplate");
		
		//  Map      
		Map<String, Object> paramMap = new HashMap<>();
		paramMap.put("name", "zhangM");
		paramMap.put("email", "[email protected]");
		paramMap.put("deptId", 4);
		njt.update(sql, paramMap);
		
		//        
		//    ，    ，  SqlParameterSource  
		//update(String sql, SqlParameterSource paramSource) 
		Employee employee = new Employee();
		employee.setName("wangX");
		employee.setEmail("[email protected]");
		employee.setDeptId(5);
		SqlParameterSource paramSource = new BeanPropertySqlParameterSource(employee);
		njt.update(sql, paramSource);
	}

		Connection conn = null;
		
		try{//    ：           
			conn = JDBCTools.getConnection();
			//      ，       false
			conn.setAutoCommit(false);
			
			String sql= "UPDATE users SET balance = balance+500 WHERE username = 'yuchen' ";
			update(conn, sql);
			int a =10/0;
			
			sql= "UPDATE users SET balance = balance-500 WHERE username = 'jack'";
			update(conn,sql);
			
			//  ，       
			conn.commit();
		} catch (Exception e){
			e.printStackTrace();
			try {
				//    ，       
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			
		} finally{
			JDBCTools.release(null,null, conn);
		}

public interface BookShopDao {
	
	//          
	public double findBookPriceByIsbn(String isbn);
	
	//      ，        -1
	public void updateBookStock(String isbn);
	
	//       ， username  balance-price
	public void updateUserAccount(String isbn, double price);
}

@Repository("bookShopDao")
public class BookShopDaoImpl implements BookShopDao {

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@Override
	public double findBookPriceByIsbn(String isbn) {
		String sql = "SELECT price FROM book WHERE isbn=?";
		double price = jdbcTemplate.queryForObject(sql, double.class,isbn);
		return price;
	}

	@Override
	public void updateBookStock(String isbn) {
		//          ，   ，    （         ，  mysq       ）
		String sqlCheck = "SELECT stock FROM book_stock WHERE isbn = ?";
		int stock = jdbcTemplate.queryForObject(sqlCheck, int.class,isbn);
		if(stock == 0){
			throw new BookStockException("    ！");
		}
		
		String sql = "UPDATE book_stock SET stock = stock-1 WHERE isbn=?";
		jdbcTemplate.update(sql, isbn);
	}

	@Override
	public void updateUserAccount(String username, double price) {
		//           ，   ，    
		String sqlCheck = "SELECT balance FROM account WHERE username = ?";
		double balance = jdbcTemplate.queryForObject(sqlCheck, double.class,username);
		if(balance -price < 0){
			throw new BookStockException("    ！");
		}
		String sql = "UPDATE account SET balance = balance-? WHERE username=?";
		jdbcTemplate.update(sql,price,username);
	}

}

public interface BookShopService {

	//      ，      ：    、         
	public void purchase(String username,String isbn);
}

@Repository("bookShopService")
public class BookShopServiceImpl implements BookShopService{

	@Autowired
	private BookShopDao bookShopDao;
	
	
	@Override
	public void purchase(String username, String isbn) {
		//      
		double price = bookShopDao.findBookPriceByIsbn(isbn);
		//    
		bookShopDao.updateBookStock(isbn);
		//      
		bookShopDao.updateUserAccount(username, price);
	}

}

	<!--         ，        -->
	<bean id="transactionManager" 
		class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
		<constructor-arg ref="dataSource"></constructor-arg>
	</bean>

	<!--        -->
	<tx:annotation-driven transaction-manager="transactionManager"/>

	//      
	@Transactional
	@Override
	public void purchase(String username, String isbn) {
		//      
		double price = bookShopDao.findBookPriceByIsbn(isbn);
		//    
		bookShopDao.updateBookStock(isbn);
		//      
		bookShopDao.updateUserAccount(username, price);
	}

public interface Cashier {

	//       
	public void checkout(String username, List<String> isbns);
}

@Repository("cashier")
public class CashierImpl implements Cashier {

	@Autowired
	private BookShopService bookShopService;
	

	@Transactional
	@Override
	public void checkout(String username, List<String> isbns) {
		for(String isbn : isbns){
			//purchase()         
			bookShopService.purchase(username,isbn);
		}
	}

}

	//      
	//  propagation         ，      REQUIRED，          
	//           REQUIRES_NEW，                ，          ，
	//           ，   
	@Transactional(propagation=Propagation.REQUIRES_NEW)
	@Override
	public void purchase(String username, String isbn) {
		//      
		double price = bookShopDao.findBookPriceByIsbn(isbn);
		//    
		bookShopDao.updateBookStock(isbn);
		//      
		bookShopDao.updateUserAccount(username, price);
	}

	@Transactional(propagation=Propagation.REQUIRES_NEW,//    
			isolation=Isolation.READ_COMMITTED,//    
			noRollbackFor={AccountBalanceException.class},//        
			readOnly=false,//    
			timeout=3//    
			)
	@Override
	public void purchase(String username, String isbn) {
		//      
		double price = bookShopDao.findBookPriceByIsbn(isbn);
		//    
		bookShopDao.updateBookStock(isbn);
		//      
		bookShopDao.updateUserAccount(username, price);
	}

<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.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.2.xsd
		http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.2.xsd
		http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.2.xsd">
	
	
	<!--          -->
	<context:property-placeholder location="classpath:db.properties"/>
	<!--   c3p0    -->
	<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
		<property name="user" value="${jdbc.user}"></property>
		<property name="password" value="${jdbc.password}"></property>
		<property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property>
		<property name="driverClass" value="${jdbc.driverClass}"></property>
		
		
		<property name="initialPoolSize" value="${jdbc.initialPoolSize}"></property>
		<property name="maxPoolSize" value="${jdbc.maxPoolSize}"></property>
	</bean>
	
	<!--   spring JdbcTemplate -->
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource"></property>
	</bean>
	
	<!--        bean -->
	<bean id="bookShopDao" class="com.cqupt.spring.tx.xml.BookShopDaoImpl">
		<property name="jdbcTemplate" ref="jdbcTemplate"></property>
	</bean>
	<bean id="bookShopService" class="com.cqupt.spring.tx.xml.service.impl.BookShopServiceImpl">
		<property name="bookShopDao" ref="bookShopDao"></property>
	</bean>
	<bean id="cashier" class="com.cqupt.spring.tx.xml.service.impl.CashierImpl">
		<property name="bookShopService" ref="bookShopService"></property>
	</bean>

	
	<!--1.         ，        -->
	<bean id="transactionManager" 
		class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
		<constructor-arg ref="dataSource"></constructor-arg>
	</bean>
	<!-- 2.       -->
	<tx:advice id="txAdvice" transaction-manager="transactionManager">
		<tx:attributes>
			<!--    tx:method   ，       ，            ，    *          -->
			<tx:method name="purchase" propagation="REQUIRES_NEW"/>
			<tx:method name="get*" read-only="true"/>
			<tx:method name="find*" read-only="true"/>
			<!--                       ，       name="*"，
			                ，       ，          -->
			<tx:method name="*"/>
		</tx:attributes>
	</tx:advice>
	
	<!-- 3.       ，                  -->
	<aop:config>
		<aop:pointcut expression="execution(* com.cqupt.spring.tx.xml.service.impl.*.*(..))" 
				id="txPintcut"/>
		<aop:advisor advice-ref="txAdvice" pointcut-ref="txPintcut"/>
	</aop:config>
	
</beans>