spring与Jdbc的操作

************************

beans.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:context="http://www.springframework.org/schema/context"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:tx="http://www.springframework.org/schema/tx"
       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
           ">
           
           <!-- 利用spring提供的占位符的方式引用properties文件的数据 -->
           <context:property-placeholder location="classpath:jdbc.properties"/>
           <!-- dbcp数据源配置 -->
           <bean id="dataSource" class=" org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
           		<property name="driverClassName" value="${driverClassName}"></property>
           		<property name="url" 
           			value="${url}">
           		</property>
           		<property name="username" value="${username}"></property>
           		<property name="password" value="${password}"></property>
           		<!-- 连接池启动时初始值 -->
           		<property name="initialSize" value="${initialSize}"></property>
           		<!-- 连接池的最大值 -->
           		<property name="maxActive" value="${maxActive}"></property>
           		<!-- 最大空闲值。当经过一个高峰时间后，连接池可以慢慢将已经用不到的连接慢慢释放一部分，一直减少到
           		maxIdle为止 -->
           		<property name="maxIdle" value="${maxIdle}"></property>
           		<!-- 最小空闲值。当空闲的连接数少于阀值时，连接池就会预申请一些去连接，以免洪峰来时来不及申请 -->
           		<property name="minIdle" value="${minIdle}"></property>
           		
           </bean>
           
           <!-- 采用spring来管理数据源的事务 -->
           <bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
           		<property name="dataSource" ref="dataSource"></property>
           </bean>
           
           <!-- 使用@Transactional注解方式使用事务 -->
           <tx:annotation-driven transaction-manager="txManager"/>
           
           <bean id="personService" class="blog.service.impl.PersonServiceBean">
           		<property name="dataSource" ref="dataSource"></property>
           </bean>
</beans>

**********************

jdbc.properties

**********************

url=jdbc\:mysql\://localhost\:3306/springandjdbc?useUnicode\=true&characterEncoding\=utf-8
username=root
password=1234
initialSize=1
maxActive=500
maxIdle=2
minIdle=1
driverClassName=org.gjt.mm.mysql.Driver

需要的jar包

**********************

Person.java

**********************

package blog.service.bean;

public class Person {
	private Integer personId;
	private String name;

	public Integer getPersonId() {
		return personId;
	}

	public void setPersonId(Integer personId) {
		this.personId = personId;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public Person() {

	}

	public Person(String name) {
		this.name = name;
	}
}

******************

PersonService.java

******************

package blog.service;

import java.util.List;

import blog.service.bean.Person;

public interface PersonService {
	public void save(Person person);
	public void delete(Integer personId);
	public void update(Person person);
	public List<Person> getPersons();
	public Person getPerson(Integer personId);
}

*********************

PersonServiceBean.java

********************

package blog.service.impl;

import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.transaction.annotation.Transactional;

import blog.service.PersonService;
import blog.service.bean.Person;

@Transactional
public class PersonServiceBean implements PersonService {
	private JdbcTemplate jdbcTemplate;

	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}

	@Override
	public void delete(Integer personId) {
		jdbcTemplate.update("delete from person where id = ?", new Object[]{personId},new int[]{java.sql.Types.INTEGER});
	}

	@Override
	public Person getPerson(Integer personId) {
		Person person =(Person) jdbcTemplate.queryForObject("select * from person where id = ?", new Object[]{personId},new int[]{java.sql.Types.INTEGER}, new PersonRowMapper());
		return person;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Person> getPersons() {
		List<Person> persons = jdbcTemplate.query("select * from person", new PersonRowMapper());
		return persons;
	}

	@Override
	public void save(Person person) {
		jdbcTemplate.update("insert into person(name) values(?)", new Object[]{person.getName()}, 
				new int[]{java.sql.Types.VARCHAR});
	}

	@Override
	public void update(Person person) {
		jdbcTemplate.update("update  person set name = ? where id = ?", new Object[]{person.getName(),person.getPersonId()}, 
				new int[]{java.sql.Types.VARCHAR,java.sql.Types.INTEGER});
	}

}

*******************

PersonRowMap.java

*******************

package blog.service.impl;

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

import org.springframework.jdbc.core.RowMapper;

import blog.service.bean.Person;

public class PersonRowMapper implements RowMapper {

	@Override//if(rs.next())
	public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
		Person person = new Person(rs.getString("name"));
		person.setPersonId(rs.getInt("id"));
		return person;
	}

}

******************

junittest

******************

package junitTest;

import java.util.List;

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

import blog.service.PersonService;
import blog.service.bean.Person;

public class SpringAndJdbc {
	private static PersonService personService;

	@BeforeClass
	public static void setUpBeforeClass() throws Exception {
		try {
			ApplicationContext ctx = new ClassPathXmlApplicationContext("beans.xml");
			personService = (PersonService) ctx.getBean("personService");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	@Test
	public void getBeans() {
		List<Person> persons = personService.getPersons();
		for (Person person : persons) {
			System.out.println(person.getName());
		}
	}
	
	@Test
	public void save(){
		Person person = new Person("Alice");
		person.setPersonId(3);
		personService.save(person);
	}
	
	@Test
	public void delete(){
		personService.delete(1);
	}
	
	@Test
	public void getBean(){
		Person person = personService.getPerson(1);
		System.out.println(person.getName());
	}
	
	@Test
	public void update(){
		Person person = new Person("Judy");
		person.setPersonId(2);
		personService.update(person);
	}

}