16、使用Spring提供的JdbcTemplate操作数据库

1、mysql.properties

jdbc.url= jdbc\:mysql\://localhost\:3306/testdatabase
jdbc.driver=com.mysql.jdbc.Driver
jdbc.user=root
jdbc.pwd=1234
c3p0.initialPoolSize=3
c3p0.maxPoolSize=10
c3p0.maxStatements=100
c3p0.acquireIncrement=2

2、log4j.properties

### 设置###  
###debug,stdout,D,E都是自己起appender的名称  
###在后面的appender进行了具体的配置  
log4j.rootLogger = debug,stdout,D,E  
  
### 输出信息到控制抬 ###  
log4j.appender.stdout = org.apache.log4j.ConsoleAppender  
log4j.appender.stdout.Target = System.out  
log4j.appender.stdout.layout = org.apache.log4j.PatternLayout  
log4j.appender.stdout.layout.ConversionPattern = [%-5p] %d{yyyy-MM-dd HH\:mm\:ss,SSS} method\:%l%n%m%n  
  
### 输出DEBUG 级别以上的日志到=E://logs/error.log ###  
log4j.appender.D = org.apache.log4j.DailyRollingFileAppender  
log4j.appender.D.File = E\://logs/log.log  
log4j.appender.D.Append = true  
log4j.appender.D.Threshold = DEBUG   
log4j.appender.D.layout = org.apache.log4j.PatternLayout  
log4j.appender.D.layout.ConversionPattern = %-d{yyyy-MM-dd HH\:mm\:ss}  [ %t\:%r ] - [ %p ]  %m%n  
  
### 输出ERROR 级别以上的日志到=E://logs/error.log ###  
log4j.appender.E = org.apache.log4j.DailyRollingFileAppender  
log4j.appender.E.File =E\://logs/error.log   
log4j.appender.E.Append = true  
log4j.appender.E.Threshold = ERROR   
log4j.appender.E.layout = org.apache.log4j.PatternLayout  
log4j.appender.E.layout.ConversionPattern =%-d{yyyy-MM-dd HH\:mm\:ss}  [ %t\:%r ] - [ %p ]  %m%n 

3、bean.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:p="http://www.springframework.org/schema/p"
	xmlns:context="http://www.springframework.org/schema/context"
	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.xsd
        http://www.springframework.org/schema/aop
        http://www.springframework.org/schema/aop/spring-aop.xsd">

	<!-- 开启注解扫描 -->
	<context:component-scan base-package="com.dao" />

	<!-- 加载配置文件 -->
	<!--配置数据源属性文件 -->
	<bean id="propertyConfigurer"
		class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
		<property name="locations">
			<list>
				<value>classpath:mysql.properties</value>
				<!-- <value>/WEB-INF/configs/mysql.properties</value> -->
			</list>
		</property>
	</bean>

	<!-- dao 实例 -->
	<bean id="userDao" class="com.dao.UserDao" />


</beans> 

4、bean-jdbc-tx.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:p="http://www.springframework.org/schema/p"
	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.xsd
     	 http://www.springframework.org/schema/context
         http://www.springframework.org/schema/context/spring-context.xsd
         http://www.springframework.org/schema/aop
         http://www.springframework.org/schema/aop/spring-aop.xsd
         http://www.springframework.org/schema/tx
     	 http://www.springframework.org/schema/tx/spring-tx.xsd">

	<!-- JDBC的事务配置 -->

	<!-- 1. 数据源对象: C3P0连接池 -->
	<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.pwd}"></property>
		<property name="initialPoolSize" value="${c3p0.initialPoolSize}"></property>
		<property name="maxPoolSize" value="${c3p0.maxPoolSize}"></property>
		<property name="maxStatements" value="${c3p0.maxStatements}"></property>
		<property name="acquireIncrement" value="${c3p0.acquireIncrement}"></property>
	</bean>

	<!-- 2. 创建JdbcTemplate对象 -->
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource" />
	</bean>


	<!-- #############5. Spring声明式事务管理配置############### -->
	<!-- 5.1 配置事务管理器类 -->
	<bean id="txManager"
		class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
		<property name="dataSource" ref="dataSource"></property>
	</bean>

	<!-- 5.2 配置事务增强(如果管理事务?) -->
	<tx:advice id="txAdvice" transaction-manager="txManager">
		<tx:attributes>
			<tx:method name="find*" read-only="true" />
			<tx:method name="save*" propagation="REQUIRED" />
			<tx:method name="delete*" propagation="REQUIRED" />
			<tx:method name="update*" propagation="REQUIRED" />
		</tx:attributes>
	</tx:advice>

	<!-- 5.3 Aop配置： 拦截哪些方法(切入点表表达式) + 应用上面的事务增强配置 -->
	<aop:config>
		<aop:pointcut expression="execution(* com.dao.*.*(..))"
			id="pt" />
		<aop:advisor advice-ref="txAdvice" pointcut-ref="pt" />
	</aop:config>
</beans>      

5、UserDao.java

public class UserDao {

	@Resource
	private JdbcTemplate jdbcTemplate;

	public void save(User user) {
		String sql = " insert into user(name) values(?) ";
		jdbcTemplate.update(sql, user.getName());
	}

	public List<User> findAll() {
		String sql = " select * from user ";
		return jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(
				User.class));
	}

	public User findById(int id) {
		String sql = " select * from user where id = ?";
		return jdbcTemplate.queryForObject(sql,
				new BeanPropertyRowMapper<User>(User.class), id);
	}

	public void update(User user) {
		String sql = "update user set name = ? where id = ?";
		System.out.println(user.getName());
		jdbcTemplate.update(sql, user.getName(), user.getId());
	}

	public void delete(User user) {
		jdbcTemplate.update("delete from user where id = ?",
				new Object[] { user.getId() },
				new int[] { java.sql.Types.INTEGER });
	}
}

6、User.java

public class User {
	private int id;
	private String name;

	public User() {
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

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

	@Override
	public String toString() {
		return "User [id=" + id + ", name=" + name + "]";
	}

}

7、UserTest.java

public class UserTest {
	public static void main(String[] args) {
		ApplicationContext context = new ClassPathXmlApplicationContext(
				"classpath:bean*.xml");
		UserDao userDao = context.getBean(UserDao.class);
		System.out.println(userDao);
		// 测试单个对象的获取
		User user = userDao.findById(1);
		System.out.println(user);
		// 测试List的获取
		List<User> list = userDao.findAll();
		System.out.println(list);
		// 测试更新
		User updateUser = new User();
		updateUser.setId(1);
		// libk
		updateUser.setName("111");
		userDao.update(updateUser);
		user = userDao.findById(1);
		System.out.println(user);

		// 删除
		// userDao.delete(user);
		// 保存
		// user.setId(1);
		// user.setName("111111");
		// userDao.save(user);
	}
}

8、 源码下载