一)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():分页查询多个对象
接着是实体类User:
UserDao操作类:
测试类:
运行结果:
第二种实现批量删除:
测试类中写测试代码:
第三种实现查询单个对象:
在测试类中写测试方法:
查询结果:
第四种查询多个对象:
运行结果如下:
第五种分页查询:
测试方法:
第六种查询总记录数:
测试方法:
运行结果:
(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