Spring进阶之SpringDao的增删查改

一)springDAO
    (1)springDAO与springAOP的关系
         DAO会依赖于AOP模块

    (2)springDAO能够解决什么问题
         主要争对JDBC的简化操作
         解决持久层的增、删、改、查,批操作
    (3)springDAO开发环境
        (A)导入jar包
             >>spring.jar/commons-logging.jar

             >>aspectjweaver.jar
/aspectjrt.jar/
cglib-nodep-2.1_3.jar
    >>c3p0-0.9.1.2.jar
/mysql-connector-java-5.1.7-bin.jar
        (B)XML声明
    (4)springDAO中配置C3P0数据源
    (5)常用JdbcTemplate类的API【users表(id/username/password/gender/tel/email)】 
(A)jdbcTemplate.update():增、删、改
(B)jdbcTemplate.batchUpdate():批
(C)jdbcTemplate.queryForObject():查询单个对象
(D)jdbcTemplate.query():查询多个对象
(E)jdbcTemplate.query():分页查询多个对象

        (F)jdbcTemplate.queryForInt():查询出一个整型值

第一种实现增加:

首先配置文件applicationContext.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:aop="http://www.springframework.org/schema/aop"
		xsi:schemaLocation="
			http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.5.xsd
			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">
			<!-- 配置c3p0数据库连接池 -->
		<bean id="comdatasource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
			<property name="driverClass" value="com.mysql.jdbc.Driver"></property>
			<property name="jdbcUrl" value="jdbc:mysql://127.0.0.1:3306/example_db"></property>
			<property name="user" value="root"></property>
			<property name="password" value="04010"></property>
			<property name="initialPoolSize" value="30"></property>
			<property name="acquireIncrement" value="5"></property>
		</bean>
		<!-- 配置JdbcTemplate -->
		<bean id="jt" class="org.springframework.jdbc.core.JdbcTemplate">
			<property name="dataSource" ref="comdatasource"></property>
		</bean>
		<bean id="userDao" class="com.spring.aop_operation.UserDao">
			<property name="jt" ref="jt"></property>
		</bean>
</beans>

接着是实体类User:

package com.spring.aop_operation;

public class User {

	private Integer id;
	private String name;
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	
	
}

UserDao操作类:

package com.spring.aop_operation;

import org.springframework.jdbc.core.JdbcTemplate;

public class UserDao {

	private JdbcTemplate jt;
	public UserDao(){}
	
	public void setJt(JdbcTemplate jt) {
		this.jt = jt;
	}

	public void addUser(User user){
		System.out.println("添加用户");
		String sql="insert into users(id,name) values(?,?)";
		Object[] params={user.getId(),user.getName()};
		jt.update(sql, params);
	}
	
	public void deleteAllUser(Integer [] ids){
		
	}
}

测试类:

package com.spring.aop_operation;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class Test {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		ApplicationContext ac=new ClassPathXmlApplicationContext(new String[]{"com/spring/aop_operation/applicationContext.xml"});
		UserDao dao= (UserDao) ac.getBean("userDao");
		User user=new User();
		user.setId(2014);
		user.setName("小红");
		dao.addUser(user);
		
	}

}

运行结果:

2014-7-10 15:32:29 org.springframework.context.support.AbstractApplicationContext prepareRefresh
信息: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@8fce95: display name [org.springframework.context.support.ClassPathXmlApplicationContext@8fce95]; startup date [Thu Jul 10 15:32:29 CST 2014]; root of context hierarchy
2014-7-10 15:32:29 org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
信息: Loading XML bean definitions from class path resource [com/spring/aop_operation/applicationContext.xml]
2014-7-10 15:32:30 org.springframework.context.support.AbstractApplicationContext obtainFreshBeanFactory
信息: Bean factory for application context [org.springframework.context.support.ClassPathXmlApplicationContext@8fce95]: org.springframework.beans.factory.support.DefaultListableBeanFactory@49d67c
2014-7-10 15:32:30 org.springframework.beans.factory.support.DefaultListableBeanFactory preInstantiateSingletons
信息: Pre-instantiating singletons in org.springframework.beans.factory.support.DefaultListableBeanFactory@49d67c: defining beans [comdatasource,jt,userDao]; root of factory hierarchy
2014-7-10 15:32:30 com.mchange.v2.log.MLog <clinit>
信息: MLog clients using java 1.4+ standard logging.
2014-7-10 15:32:30 com.mchange.v2.c3p0.C3P0Registry banner
信息: Initializing c3p0-0.9.1.2 [built 21-May-2007 15:04:56; debug? true; trace: 10]
添加用户
2014-7-10 15:32:30 com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource getPoolManager
信息: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 5, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> 2ryp0e938niy28t3qppf|1712b3a, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 2ryp0e938niy28t3qppf|1712b3a, idleConnectionTestPeriod -> 0, initialPoolSize -> 30, jdbcUrl -> jdbc:mysql://127.0.0.1:3306/example_db, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 15, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, numThreadsAwaitingCheckoutDefaultUser -> 0, preferredTestQuery -> null, properties -> {user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false ]

第二种实现批量删除:

在UserDao中加入批量删除的代码:

package com.spring.aop_operation;

import org.springframework.jdbc.core.JdbcTemplate;

public class UserDao {

	private JdbcTemplate jt;
	public UserDao(){}
	
	public void setJt(JdbcTemplate jt) {
		this.jt = jt;
	}

	public void addUser(User user){
		System.out.println("添加用户");
		String sql="insert into users(id,name) values(?,?)";
		Object[] params={user.getId(),user.getName()};
		jt.update(sql, params);
	}
	
	public void deleteAllUser(Integer [] ids){
		System.out.println("删除id为1和2的用户");
		String[] sqls=new String[ids.length];
		for(int i=0;i<sqls.length;i++){
			sqls[i]="delete from users where id="+ids[i];
		}
		jt.batchUpdate(sqls);
	}
}

测试类中写测试代码:

package com.spring.aop_operation;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class Test {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		ApplicationContext ac=new ClassPathXmlApplicationContext(new String[]{"com/spring/aop_operation/applicationContext.xml"});
		UserDao dao= (UserDao) ac.getBean("userDao");
		//User user=new User();
		//user.setId(2014);
		//user.setName("小红");
		//dao.addUser(user);
		dao.deleteAllUser(new Integer[]{1,2});
		
	}

}

第三种实现查询单个对象:

在UserDao增加查询单个对象的方法:

/**
	 * 查询单个对象
	 * @param id
	 * @return
	 */
	public User findUserById(Integer id){
		String sql="select * from users where id=?";
		Object[] params={id};
		User user= (User) jt.queryForObject(sql, params,new RowMapper() {
			//rs:指的是当前的记录,rowNum值得当前的行号,从0开始
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
			  Integer id= rs.getInt("id");
			  String name= rs.getString("name");
			  User user=new User();
			  user.setId(id);
			  user.setName(name);
				return user;
			}
		});
		return user;
	}

在测试类中写测试方法:

package com.spring.aop_operation;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class Test {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		ApplicationContext ac=new ClassPathXmlApplicationContext(new String[]{"com/spring/aop_operation/applicationContext.xml"});
		UserDao dao= (UserDao) ac.getBean("userDao");
		//User user=new User();
		//user.setId(2014);
		//user.setName("小红");
		//dao.addUser(user);
		//dao.deleteAllUser(new Integer[]{1,2});
		User user= dao.findUserById(2);
		System.out.println(user.getName());
		
	}

}

查询结果:

信息: Pre-instantiating singletons in org.springframework.beans.factory.support.DefaultListableBeanFactory@64ab4d: defining beans [comdatasource,jt,userDao]; root of factory hierarchy
2014-7-10 16:17:29 com.mchange.v2.log.MLog <clinit>
信息: MLog clients using java 1.4+ standard logging.
2014-7-10 16:17:29 com.mchange.v2.c3p0.C3P0Registry banner
信息: Initializing c3p0-0.9.1.2 [built 21-May-2007 15:04:56; debug? true; trace: 10]
2014-7-10 16:17:29 com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource getPoolManager
信息: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 5, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> 2ryp0e938p4stiez6vt2|15e0873, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 2ryp0e938p4stiez6vt2|15e0873, idleConnectionTestPeriod -> 0, initialPoolSize -> 30, jdbcUrl -> jdbc:mysql://127.0.0.1:3306/example_db, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 15, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, numThreadsAwaitingCheckoutDefaultUser -> 0, preferredTestQuery -> null, properties -> {user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false ]
小明

第四种查询多个对象:

在UserDao写查询多个对象的方法:

/**
	 * 查询多个对象
	 */
	public List<User> findUserAll(){
		final List<User> list=new ArrayList<User>();
		String sql="select * from users";
		jt.query(sql, new RowMapper() {
			//调用多次
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				// TODO Auto-generated method stub
				 Integer id= rs.getInt("id");
				 String name= rs.getString("name");
				 User user=new User();
				 user.setId(id);
				 user.setName(name);
				 list.add(user);
				return list;
			}
		});
		return list;
	}


在测试类中写测试方法:

package com.spring.aop_operation;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class Test {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		ApplicationContext ac=new ClassPathXmlApplicationContext(new String[]{"com/spring/aop_operation/applicationContext.xml"});
		UserDao dao= (UserDao) ac.getBean("userDao");
		//User user=new User();
		//user.setId(2014);
		//user.setName("小红");
		//dao.addUser(user);
		//dao.deleteAllUser(new Integer[]{1,2});
		//User user= dao.findUserById(2);
		//System.out.println(user.getName());
		for(User user:dao.findUserAll()){
			System.out.println(user.getName());
		}
		
	}

}

运行结果如下:

2014-7-10 16:21:56 com.mchange.v2.log.MLog <clinit>
信息: MLog clients using java 1.4+ standard logging.
2014-7-10 16:21:56 com.mchange.v2.c3p0.C3P0Registry banner
信息: Initializing c3p0-0.9.1.2 [built 21-May-2007 15:04:56; debug? true; trace: 10]
2014-7-10 16:21:56 com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource getPoolManager
信息: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 5, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> 2ryp0e938paiwt1hybhnc|cd2e33, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 2ryp0e938paiwt1hybhnc|cd2e33, idleConnectionTestPeriod -> 0, initialPoolSize -> 30, jdbcUrl -> jdbc:mysql://127.0.0.1:3306/example_db, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 15, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, numThreadsAwaitingCheckoutDefaultUser -> 0, preferredTestQuery -> null, properties -> {user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false ]
null
小明
小红

第五种分页查询:

/**
	 * 分页查询对象
	 */
	public List<User> findUserByLimit(){
		final List<User> list=new ArrayList<User>();
		String sql="select * from users limit ?,?";
		Object[] params={0,2};
		jt.query(sql,params, new RowMapper() {
			//调用多次
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				// TODO Auto-generated method stub
				 Integer id= rs.getInt("id");
				 String name= rs.getString("name");
				 User user=new User();
				 user.setId(id);
				 user.setName(name);
				 list.add(user);
				return list;
			}
		});
		return list;
	}

测试方法:

package com.spring.aop_operation;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class Test {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		ApplicationContext ac=new ClassPathXmlApplicationContext(new String[]{"com/spring/aop_operation/applicationContext.xml"});
		UserDao dao= (UserDao) ac.getBean("userDao");
		//User user=new User();
		//user.setId(2014);
		//user.setName("小红");
		//dao.addUser(user);
		//dao.deleteAllUser(new Integer[]{1,2});
		//User user= dao.findUserById(2);
		//System.out.println(user.getName());
		for(User user:dao.findUserByLimit()){
			System.out.println(user.getName());
		}
		
	}

}

第六种查询总记录数:

/**
	 * 查询总记录数
	 */
	public int findcount(){
		final List<User> list=new ArrayList<User>();
		String sql="select count(*) from users";
		int count=jt.queryForInt(sql);
		return count;
	}

测试方法:

package com.spring.aop_operation;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class Test {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		ApplicationContext ac=new ClassPathXmlApplicationContext(new String[]{"com/spring/aop_operation/applicationContext.xml"});
		UserDao dao= (UserDao) ac.getBean("userDao");
		//User user=new User();
		//user.setId(2014);
		//user.setName("小红");
		//dao.addUser(user);
		//dao.deleteAllUser(new Integer[]{1,2});
		//User user= dao.findUserById(2);
		//System.out.println(user.getName());
		//for(User user:dao.findUserByLimit()){
			//System.out.println(user.getName());
		//}
		System.out.println("总记录数:"+dao.findcount());
		
	}

}

运行结果:

2014-7-10 16:26:51 org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
信息: Loading XML bean definitions from class path resource [com/spring/aop_operation/applicationContext.xml]
2014-7-10 16:26:51 org.springframework.context.support.AbstractApplicationContext obtainFreshBeanFactory
信息: Bean factory for application context [org.springframework.context.support.ClassPathXmlApplicationContext@998b08]: org.springframework.beans.factory.support.DefaultListableBeanFactory@5e13ad
2014-7-10 16:26:51 org.springframework.beans.factory.support.DefaultListableBeanFactory preInstantiateSingletons
信息: Pre-instantiating singletons in org.springframework.beans.factory.support.DefaultListableBeanFactory@5e13ad: defining beans [comdatasource,jt,userDao]; root of factory hierarchy
2014-7-10 16:26:52 com.mchange.v2.log.MLog <clinit>
信息: MLog clients using java 1.4+ standard logging.
2014-7-10 16:26:52 com.mchange.v2.c3p0.C3P0Registry banner
信息: Initializing c3p0-0.9.1.2 [built 21-May-2007 15:04:56; debug? true; trace: 10]
2014-7-10 16:26:52 com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource getPoolManager
信息: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 5, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> 2ryp0e938pgutolyjwr3|7cbde6, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 2ryp0e938pgutolyjwr3|7cbde6, idleConnectionTestPeriod -> 0, initialPoolSize -> 30, jdbcUrl -> jdbc:mysql://127.0.0.1:3306/example_db, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 15, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, numThreadsAwaitingCheckoutDefaultUser -> 0, preferredTestQuery -> null, properties -> {user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false ]
总记录数:3


  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值