Spring-JDBC

Spring-JDBC

sf2gis@163.com

2016年3月25日

 

1  目标：使用Spring管理JDBC，操作数据库。

连接数据库、操作数据库、事务等。

2 原理：Spring通过bean解耦jdbc配置，通过template简化操作。

参考：http://blog.youkuaiyun.com/cdsnmdl/article/details/4405161

3 流程：设置数据源，设置jdbc模板，创建dao，操作数据库

3.1 添加数据库驱动程序：下载mysql的jdbc驱动，添加到buildpath。

在Eclipse可以使用DataSourceExplore添加数据源，测试连接。

 

3.2 设置数据源:设置数据库的url，用户名、密码、驱动程序

参考：http://lavasoft.blog.51cto.com/62575/73467/

使用spring默认的数据源作为数据源bean的类。

      <bean id="dataSource"class="org.springframework.jdbc.datasource.DriverManagerDataSource">

           <property name="driverClassName"value="com.mysql.jdbc.Driver" />

           <property name="url"value="jdbc:mysql://localhost:3306/hib4" />

           <propertyname="username" value="root" />

           <propertyname="password" value="sf" />

      </bean>

3.3 设置jdbc模板

      <bean id="jdbcTemplate"     class="org.springframework.jdbc.core.JdbcTemplate">

           <propertyname="dataSource" ref="dataSource"></property>

      </bean>

3.4 创建dao

      <bean id="myDao"class="lee.MyDao">

           <property name="temp"ref="jdbcTemplate"></property>

      </bean>

//MyDao.java

package lee;

 

importjava.sql.ResultSet;

importjava.sql.SQLException;

importjava.util.List;

 

importorg.springframework.jdbc.core.JdbcTemplate;

importorg.springframework.jdbc.core.RowMapper;

 

public class MyDao{

      private JdbcTemplate temp;

      public JdbcTemplate getTemp() {

           return temp;

      }

      public void setTemp(JdbcTemplate temp) {

           this.temp = temp;

      }

      public void select(){

           String strSelect="select * from employee whereid=7";

           List<String>lstRtn=temp.query(strSelect, new RowMapper<String>(){

                 @Override

                 public String mapRow(ResultSetrs, int rowNo) throws SQLException {

                      // TODO Auto-generatedmethod stub

                      Stringrtn="rowNo="+rowNo+":id="+rs.getInt(1)+",name="+rs.getString(2)+",addr="+rs.getString(3)+",no="+rs.getString(4);

                      System.out.println(rtn);

                      return rtn;

                 }

           });

          

      }

}

3.5 操作数据库。

           String strSelect="select * from employee whereid=7";

           List<String>lstRtn=temp.query(strSelect, new RowMapper<String>(){

3.6 示例：使用spring默认数据库（DriverManagerDataSource）操作数据库

//beans.xml

<?xmlversion="1.0" encoding="UTF-8"?>

<beansxmlns="http://www.springframework.org/schema/beans"

      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

      xmlns:aop="http://www.springframework.org/schema/aop"

      xmlns:tx="http://www.springframework.org/schema/tx"

      xsi:schemaLocation="http://www.springframework.org/schema/aophttp://www.springframework.org/schema/aop/spring-aop-3.2.xsd

           http://www.springframework.org/schema/beanshttp://www.springframework.org/schema/beans/spring-beans-3.2.xsd

           http://www.springframework.org/schema/txhttp://www.springframework.org/schema/tx/spring-tx-3.2.xsd">

      <bean id="dataSource"class="org.springframework.jdbc.datasource.DriverManagerDataSource">

           <propertyname="driverClassName" value="com.mysql.jdbc.Driver" />

           <propertyname="url" value="jdbc:mysql://localhost:3306/hib4" />

           <propertyname="username" value="root" />

           <propertyname="password" value="sf" />

      </bean>

      <bean id="jdbcTemplate"     class="org.springframework.jdbc.core.JdbcTemplate">

           <propertyname="dataSource" ref="dataSource"></property>

      </bean>

      <bean id="myDao" class="lee.MyDao">

           <property name="temp"ref="jdbcTemplate"></property>

      </bean>

</beans>

//MyDao.java

package lee;

 

importjava.sql.ResultSet;

importjava.sql.SQLException;

importjava.util.List;

 

importorg.springframework.jdbc.core.JdbcTemplate;

importorg.springframework.jdbc.core.RowMapper;

 

public class MyDao{

      private JdbcTemplate temp;

      public JdbcTemplate getTemp() {

           return temp;

      }

      public void setTemp(JdbcTemplate temp) {

           this.temp = temp;

      }

      public void select(){

           String strSelect="select * from employee whereid=7";

           List<String>lstRtn=temp.query(strSelect, new RowMapper<String>(){

                 @Override

                 public String mapRow(ResultSetrs, int rowNo) throws SQLException {

                      // TODO Auto-generatedmethod stub

                      Stringrtn="rowNo="+rowNo+":id="+rs.getInt(1)+",name="+rs.getString(2)+",addr="+rs.getString(3)+",no="+rs.getString(4);

                      System.out.println(rtn);

                      return rtn;

                 }

           });

          

      }

}

//Main.java

package lee;

 

import org.springframework.context.ApplicationContext;

importorg.springframework.context.support.ClassPathXmlApplicationContext;

importorg.springframework.jdbc.core.JdbcTemplate;

 

public class Main{

 

      public static void main(String[] args) {

           // TODO Auto-generated method stub

           ApplicationContext ctx=newClassPathXmlApplicationContext("beans.xml");

           MyDaodao=ctx.getBean("myDao",MyDao.class);

           dao.select();

      }

}

//结果：

rowNo=0:id=7,name=updatexxx,addr=xxx,no=yyy

 

4 方法：JavaTemplate

4.1 目标：简化JDBC操作。

4.2 原理：模板化的设置JDBC参数。

4.3 方法

参考：http://my.oschina.net/u/437232/blog/279530

4.3.1通用的SQL语句执行：execute()。

4.3.2查询语句：query()，queryForXXX()。

4.3.3增加、删除、修改语句：update(),batchUpate()。

4.3.4结果集处理：使用RowMapper接口，将结果集进行转换后返回。

           List<String>lstRtn=temp.query(strSelect, new RowMapper<String>(){

                 @Override

                 public String mapRow(ResultSetrs, int rowNo) throws SQLException {

                      // TODO Auto-generatedmethod stub

                      Stringrtn="rowNo="+rowNo+":id="+rs.getInt(1)+",name="+rs.getString(2)+",addr="+rs.getString(3)+",no="+rs.getString(4);

                      System.out.println(rtn);

                      return rtn;

                 }

           });

5 方法：事务管理

5.1 目标：spring中使用事务管理数据库操作。

5.2 原理：使用Spring的TransactionTemplate创建事务，并在相关bean上控制事务。

参考：http://bbs.youkuaiyun.com/topics/350175424/

http://www.jb51.net/article/32246.htm

http://www.th7.cn/db/mysql/201505/103989.shtml

http://blog.chinaunix.net/uid-20577907-id-462940.html

http://blog.youkuaiyun.com/wangpeng047/article/details/22882537

注意：MySQL默认是MyISAM不支持事务（高性能），切换到innoDB才能够支持事务。

注意：MySQL默认是自动提交事务的，设置为手动提交才能支持回滚。

注意：Spring默认只对RuntimeException进行自动回滚。

查看当前表的详细信息：show create table 表名。

show create tableemployee;

设置表的引擎:alter table 表名type 引擎名。

alter tableemployee type innoDB;

设置事务的提交方式：set autocommit=0/1;//0=手动，1=自动。

set autocommit=0;

 

5.3 流程：设置jdbc模板，设置事务管理器和事务模板，为DAO设置JDBC模板和事务模板属性。使用事务模板管理事务。

5.3.1设置jdbc模板：指定数据源和事务模板类。

5.3.2设置事务管理器和事务模板：指定数据源，指定事务管理器。

      <bean id="transactionManager"class="org.springframework.jdbc.datasource.DataSourceTransactionManager">

           <propertyname="dataSource" ref="dataSource"></property>

      </bean>

 

      <bean id="transactionTemplate"    class="org.springframework.transaction.support.TransactionTemplate">

           <propertyname="transactionManager"ref="transactionManager"></property>

      </bean>

5.3.3为DAO设置JDBC模板和事务模板属性。

      <bean id="myDao"class="lee.MyDao">

           <property name="temp"ref="jdbcTemplate"></property>

           <propertyname="transaction"ref="transactionTemplate"></property>

      </bean>   

5.3.4使用事务模板管理事务：控制事务回滚。

                      try{

                            insert();

                            returnBoolean.TRUE;

                      }catch(Exception e){

                            System.out.println("wronginsert.");

                            status.setRollbackOnly();

                            returnBoolean.FALSE;

                      }

5.3.5示例：插入两条同样的数据，无法插入，回滚。

//beans.xml

<?xmlversion="1.0" encoding="UTF-8"?>

<beans xmlns="http://www.springframework.org/schema/beans"

      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

      xmlns:aop="http://www.springframework.org/schema/aop"

      xmlns:tx="http://www.springframework.org/schema/tx"

      xsi:schemaLocation="http://www.springframework.org/schema/aophttp://www.springframework.org/schema/aop/spring-aop-3.2.xsd

           http://www.springframework.org/schema/beanshttp://www.springframework.org/schema/beans/spring-beans-3.2.xsd

           http://www.springframework.org/schema/txhttp://www.springframework.org/schema/tx/spring-tx-3.2.xsd">

      <bean id="dataSource"

           class="com.mchange.v2.c3p0.ComboPooledDataSource">

           <propertyname="driverClass" value="com.mysql.jdbc.Driver" />

           <property name="jdbcUrl"value="jdbc:mysql://localhost:3306/hib4" />

           <property name="user"value="root" />

           <propertyname="password" value="sf" />

           <propertyname="minPoolSize" value="5" />

           <propertyname="maxPoolSize" value="10" />

      </bean>

      <bean id="jdbcTemplate"     class="org.springframework.jdbc.core.JdbcTemplate">

           <propertyname="dataSource" ref="dataSource"></property>

      </bean>

 

      <bean id="transactionManager"class="org.springframework.jdbc.datasource.DataSourceTransactionManager">

           <propertyname="dataSource" ref="dataSource"></property>

      </bean>

 

      <bean id="transactionTemplate"    class="org.springframework.transaction.support.TransactionTemplate">

           <propertyname="transactionManager"ref="transactionManager"></property>

      </bean>

 

      <bean id="myDao"class="lee.MyDao">

           <property name="temp"ref="jdbcTemplate"></property>

           <propertyname="transaction"ref="transactionTemplate"></property>

      </bean>   

</beans>

//MyDao.java

package lee;

 

importjava.sql.ResultSet;

importjava.sql.SQLException;

importjava.util.ArrayList;

importjava.util.List;

 

importorg.springframework.jdbc.core.JdbcTemplate;

importorg.springframework.jdbc.core.RowMapper;

importorg.springframework.transaction.TransactionStatus;

importorg.springframework.transaction.support.TransactionCallback;

import org.springframework.transaction.support.TransactionTemplate;

 

public class MyDao{

      private JdbcTemplate temp;

      private TransactionTemplate transaction;

      public JdbcTemplate getTemp() {

           return temp;

      }

      public void setTemp(JdbcTemplate temp) {

           this.temp = temp;

      }

      public void select(){

           String strSelect="select * fromemployee where id=7";

           List<String>lstRtn=temp.query(strSelect, new RowMapper<String>(){

                 @Override

                 public String mapRow(ResultSetrs, int rowNo) throws SQLException {

                      // TODO Auto-generatedmethod stub

                      Stringrtn="rowNo="+rowNo+":id="+rs.getInt(1)+",name="+rs.getString(2)+",addr="+rs.getString(3)+",no="+rs.getString(4);

                      System.out.println(rtn);

                      return rtn;

                 }

           });

      }

     

      public void insert(){

           String strSQL="insert intoemployee values(11,'tx','tx1','tx2')";

           //temp.update(strSQL);

           ArrayList<String> lstSQL=newArrayList<String>();

           lstSQL.add(strSQL);

           lstSQL.add("insert into employeevalues(11,'tx','tx1','tx2')");

           String[] arraySQL = new String[2];

           lstSQL.toArray(arraySQL);

           temp.batchUpdate(arraySQL);

      }

      public TransactionTemplategetTransaction() {

           return transaction;

      }

      public voidsetTransaction(TransactionTemplate transaction) {

           this.transaction = transaction;

      }

      public void tx(){

           transaction.execute(newTransactionCallback<Boolean>(){

 

                 @Override

                 public BooleandoInTransaction(TransactionStatus status) throws RuntimeException {

                      // TODO Auto-generatedmethod stub

                      try{

                            insert();

                            returnBoolean.TRUE;

                      }catch(Exceptione){

                            System.out.println("wronginsert.");

                            status.setRollbackOnly();

                            returnBoolean.FALSE;

                      }

                 }

                 });

           }

}

//Main.java

package lee;

 

importorg.springframework.context.ApplicationContext;

importorg.springframework.context.support.ClassPathXmlApplicationContext;

importorg.springframework.jdbc.core.JdbcTemplate;

 

public class Main{

 

      public static void main(String[] args) {

           // TODO Auto-generated method stub

           ApplicationContext ctx=new ClassPathXmlApplicationContext("beans.xml");

           MyDaodao=ctx.getBean("myDao",MyDao.class);

           dao.select();

 

           dao.tx();

      }

}

//结果：

rowNo=0:id=7,name=updatexxx,addr=xxx,no=yyy

Mar 25, 20166:29:37 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReaderloadBeanDefinitions

INFO: Loading XMLbean definitions from class path resource[org/springframework/jdbc/support/sql-error-codes.xml]

Mar 25, 20166:29:37 PM org.springframework.jdbc.support.SQLErrorCodesFactory <init>

INFO:SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle,PostgreSQL, Sybase]

wrong insert.

5.4 方法：使用AOP自动管理事务。

参考：http://www.cnblogs.com/rushoooooo/archive/2011/08/28/2155960.html

5.4.1目标：使用AOP自动管理事务，在切入点自动加入事务功能。

5.4.2原理：指定事务处理器为指定切入点添加事务功能。切入点将自动添加事务相关代码。

5.4.3流程：添加目标切入点，添加切入点的处理为事务。创建事务，指定事务处理器，并指定事务要处理的切入点上的指定方法。创建事务处理器，设置数据源。在dao中进行操作，将自动为指定的切入点执行事务。

5.4.3.1  创建事务处理器，设置数据源。

      <bean id="dataSource"

           class="com.mchange.v2.c3p0.ComboPooledDataSource">

           <propertyname="driverClass" value="com.mysql.jdbc.Driver" />

           <property name="jdbcUrl"value="jdbc:mysql://localhost:3306/hib4" />

           <property name="user"value="root" />

           <propertyname="password" value="sf" />

           <propertyname="minPoolSize" value="5" />

           <propertyname="maxPoolSize" value="10" />

      </bean>

      <bean id="transactionManager"class="org.springframework.jdbc.datasource.DataSourceTransactionManager">

           <propertyname="dataSource" ref="dataSource"></property>

      </bean>

5.4.3.2  创建事务，指定事务处理器，并指定事务要处理的切入点上的指定方法。

      <tx:advice id="txAdvice"transaction-manager="transactionManager">

           <tx:attributes>

                 <tx:method name="insert"propagation="REQUIRED" rollback-for="java.lang.Exception"/>

           </tx:attributes>

      </tx:advice>

      <aop:config>

           <aop:pointcutexpression="execution(* lee.MyDao.insert())"

                 id="myPointCut" />

           <aop:advisor advice-ref="txAdvice"pointcut-ref="myPointCut" />

      </aop:config>

</beans>

5.4.3.3  添加目标切入点，添加切入点的处理为事务。

      <aop:config>

           <aop:pointcutexpression="execution(* lee.MyDao.insert())"

                 id="myPointCut"/>

           <aop:advisor advice-ref="txAdvice"pointcut-ref="myPointCut" />

      </aop:config>

5.4.3.4  在dao中进行操作，将自动为指定的切入点执行事务。

                 dao.insert();

无法插入数据。因为插入第二条时会回滚。

5.4.3.5  示例：插入两条同样的数据，无法插入，回滚。

//beans.xml

<?xmlversion="1.0" encoding="UTF-8"?>

<beansxmlns="http://www.springframework.org/schema/beans"

      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

      xmlns:aop="http://www.springframework.org/schema/aop"

      xmlns:tx="http://www.springframework.org/schema/tx"

      xsi:schemaLocation="http://www.springframework.org/schema/aophttp://www.springframework.org/schema/aop/spring-aop-3.2.xsd

           http://www.springframework.org/schema/beanshttp://www.springframework.org/schema/beans/spring-beans-3.2.xsd

           http://www.springframework.org/schema/txhttp://www.springframework.org/schema/tx/spring-tx-3.2.xsd">

      <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">

           <propertyname="driverClass" value="com.mysql.jdbc.Driver" />

           <property name="jdbcUrl"value="jdbc:mysql://localhost:3306/hib4" />

           <property name="user"value="root" />

           <propertyname="password" value="sf" />

           <propertyname="minPoolSize" value="5" />

           <propertyname="maxPoolSize" value="10" />

      </bean>

      <bean id="jdbcTemplate"     class="org.springframework.jdbc.core.JdbcTemplate">

           <propertyname="dataSource" ref="dataSource"></property>

      </bean>

 

      <bean id="transactionManager"class="org.springframework.jdbc.datasource.DataSourceTransactionManager">

           <propertyname="dataSource" ref="dataSource"></property>

      </bean>

      <bean id="myDao"class="lee.MyDao">

           <property name="temp"ref="jdbcTemplate"></property>

      </bean>   

 

      <tx:advice id="txAdvice"transaction-manager="transactionManager">

           <tx:attributes>

                 <tx:method name="insert"propagation="REQUIRED" rollback-for="java.lang.Exception"/>

           </tx:attributes>

      </tx:advice>

      <aop:config>

           <aop:pointcutexpression="execution(* lee.MyDao.insert())"

                 id="myPointCut" />

           <aop:advisor advice-ref="txAdvice"pointcut-ref="myPointCut" />

      </aop:config>

</beans>

//MyDao.java

package lee;

 

importjava.sql.ResultSet;

importjava.sql.SQLException;

importjava.util.ArrayList;

importjava.util.List;

 

importorg.springframework.jdbc.core.JdbcTemplate;

importorg.springframework.jdbc.core.RowMapper;

 

public class MyDao{

      private JdbcTemplate temp;

      public JdbcTemplate getTemp() {

           return temp;

      }

      public void setTemp(JdbcTemplate temp) {

           this.temp = temp;

      }

      public void select(){

           String strSelect="select * fromemployee where id=7";

           List<String> lstRtn=temp.query(strSelect,new RowMapper<String>(){

                 @Override

                 public String mapRow(ResultSetrs, int rowNo) throws SQLException {

                      // TODO Auto-generatedmethod stub

                      Stringrtn="rowNo="+rowNo+":id="+rs.getInt(1)+",name="+rs.getString(2)+",addr="+rs.getString(3)+",no="+rs.getString(4);

                      System.out.println(rtn);

                      return rtn;

                 }

           });

      }

     

      public void insert(){

           String strSQL="insert into employeevalues(11,'tx','tx1','tx2')";

           ArrayList<String>lstSQL=new ArrayList<String>();

           lstSQL.add(strSQL);

           lstSQL.add("insertinto employee values(11,'tx','tx1','tx2')");

           String[]arraySQL = new String[2];

           lstSQL.toArray(arraySQL);

           temp.batchUpdate(arraySQL);

      }

}

//Main.java

package lee;

 

importorg.springframework.context.ApplicationContext;

import org.springframework.context.support.ClassPathXmlApplicationContext;

importorg.springframework.jdbc.core.JdbcTemplate;

 

public class Main{

 

      public static void main(String[] args) {

           // TODO Auto-generated method stub

           ApplicationContext ctx=new ClassPathXmlApplicationContext("beans.xml");

           MyDaodao=ctx.getBean("myDao",MyDao.class);

           dao.select();

           try{

                 dao.insert();

           }

           catch(Exception e){

                 System.out.println("insertError..."+e.getMessage());

           }     }

}

//结果：

rowNo=0:id=7,name=updatexxx,addr=xxx,no=yyy

Mar 25, 20166:15:54 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReaderloadBeanDefinitions

INFO: Loading XMLbean definitions from class path resource[org/springframework/jdbc/support/sql-error-codes.xml]

Mar 25, 2016 6:15:54PM org.springframework.jdbc.support.SQLErrorCodesFactory <init>

INFO:SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle,PostgreSQL, Sybase]

insert Error...StatementCallback; SQL [insert intoemployee values(11,'tx','tx1','tx2')]; Duplicate entry '11' for key 'PRIMARY';nested exception is java.sql.BatchUpdateException: Duplicate entry '11' for key'PRIMARY'

 

6 示例

6.1 DBCP数据源连接池操作JDBC：使用dbcp作为数据源bean的class

6.1.1添加数据库驱动程序：下载mysql的jdbc驱动，添加到buildpath。

6.1.2添加dbcp包：下载commons-dbcp.jar和commons-pool.jar,添加到buildpath。

//beans.xml

<?xmlversion="1.0" encoding="UTF-8"?>

<beansxmlns="http://www.springframework.org/schema/beans"

      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

      xmlns:aop="http://www.springframework.org/schema/aop"

      xmlns:tx="http://www.springframework.org/schema/tx"

      xsi:schemaLocation="http://www.springframework.org/schema/aophttp://www.springframework.org/schema/aop/spring-aop-3.2.xsd

           http://www.springframework.org/schema/beanshttp://www.springframework.org/schema/beans/spring-beans-3.2.xsd

           http://www.springframework.org/schema/txhttp://www.springframework.org/schema/tx/spring-tx-3.2.xsd">

      <beanid="dataSource"class="org.apache.commons.dbcp.BasicDataSource">

           <property name="driverClassName"value="com.mysql.jdbc.Driver" />

           <property name="url"value="jdbc:mysql://localhost:3306/hib4" />

           <property name="username"value="root" />

           <property name="password"value="sf" />

           <propertyname="initialSize" value="5" />

           <propertyname="maxActive" value="10" />

      </bean>

      <beanid="jdbcTemplate"     class="org.springframework.jdbc.core.JdbcTemplate">

           <property name="dataSource"ref="dataSource"></property>

      </bean>

      <beanid="myDao" class="lee.MyDao">

           <property name="temp" ref="jdbcTemplate"></property>

      </bean>

</beans>

 

//MyDao.java

package lee;

 

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.List;

 

importorg.springframework.jdbc.core.JdbcTemplate;

importorg.springframework.jdbc.core.RowMapper;

 

public class MyDao {

      privateJdbcTemplate temp;

      public JdbcTemplate getTemp() {

           return temp;

      }

      public void setTemp(JdbcTemplate temp) {

           this.temp = temp;

      }

      public void select(){

           StringstrSelect="select * from employee where id=7";

           List<String>lstRtn=temp.query(strSelect, new RowMapper<String>(){

                 @Override

                 public String mapRow(ResultSet rs, int rowNo) throwsSQLException {

                      // TODO Auto-generated method stub

                      Stringrtn="rowNo="+rowNo+":id="+rs.getInt(1)+",name="+rs.getString(2)+",addr="+rs.getString(3)+",no="+rs.getString(4);

                      System.out.println(rtn);

                      return rtn;

                 }

           });

          

      }

}

//Main.java

package lee;

 

importorg.springframework.context.ApplicationContext;

importorg.springframework.context.support.ClassPathXmlApplicationContext;

importorg.springframework.jdbc.core.JdbcTemplate;

 

public class Main {

 

      public static void main(String[] args) {

           // TODO Auto-generated method stub

           ApplicationContext ctx=new ClassPathXmlApplicationContext("beans.xml");

           MyDao dao=ctx.getBean("myDao",MyDao.class);

           dao.select();

      }

}

//结果：

rowNo=0:id=7,name=updatexxx,addr=xxx,no=yyy

6.2 C3P0数据源连接池操作JDBC：使用C3P0作为数据源bean的class

参考：http://hanqunfeng.iteye.com/blog/1671412

<beanid="dataSource"class="com.mchange.v2.c3p0.ComboPooledDataSource"destroy-method="close">

　　<propertyname="driverClass"><value>oracle.jdbc.driver.OracleDriver</value></property>

　　<propertyname="jdbcUrl"><value>jdbc:oracle:thin:@localhost:1521:Test</value></property>

　　<propertyname="user"><value>Kay</value></property>

　　<propertyname="password"><value>root</value></property>

　　<!--连接池中保留的最小连接数。-->

　　<propertyname="minPoolSize" value="10" />

　　<!--连接池中保留的最大连接数。Default: 15 -->

　　<propertyname="maxPoolSize" value="100" />

　　<!--最大空闲时间,1800秒内未使用则连接被丢弃。若为0则永不丢弃。Default: 0 -->

　　<propertyname="maxIdleTime" value="1800" />

　　<!--当连接池中的连接耗尽的时候c3p0一次同时获取的连接数。Default: 3 -->

　　<propertyname="acquireIncrement" value="3" />

　　<propertyname="maxStatements" value="1000" />

　　<propertyname="initialPoolSize" value="10" />

　　<!--每60秒检查所有连接池中的空闲连接。Default: 0 -->

　　<propertyname="idleConnectionTestPeriod" value="60" />

　　<!--定义在从数据库获取新连接失败后重复尝试的次数。Default: 30 -->

　　<propertyname="acquireRetryAttempts" value="30" />

　　<propertyname="breakAfterAcquireFailure" value="true" />

　　<propertyname="testConnectionOnCheckout" value="false" />

　　</bean>

6.2.1添加数据库驱动程序：下载mysql的jdbc驱动，添加到buildpath。

6.2.2添加C3P0包：下载c3p0.jar,添加到buildpath。

//beans.xml

<?xmlversion="1.0" encoding="UTF-8"?>

<beansxmlns="http://www.springframework.org/schema/beans"

      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

      xmlns:aop="http://www.springframework.org/schema/aop"

      xmlns:tx="http://www.springframework.org/schema/tx"

      xsi:schemaLocation="http://www.springframework.org/schema/aophttp://www.springframework.org/schema/aop/spring-aop-3.2.xsd

           http://www.springframework.org/schema/beanshttp://www.springframework.org/schema/beans/spring-beans-3.2.xsd

           http://www.springframework.org/schema/txhttp://www.springframework.org/schema/tx/spring-tx-3.2.xsd">

      <beanid="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">

           <property name="driverClass"value="com.mysql.jdbc.Driver" />

           <property name="jdbcUrl"value="jdbc:mysql://localhost:3306/hib4" />

           <property name="user" value="root"/>

           <property name="password"value="sf" />

           <property name="minPoolSize"value="5" />

           <property name="maxPoolSize"value="10" />

      </bean>

      <bean id="jdbcTemplate"     class="org.springframework.jdbc.core.JdbcTemplate">

           <property name="dataSource"ref="dataSource"></property>

      </bean>

      <bean id="myDao" class="lee.MyDao">

           <property name="temp"ref="jdbcTemplate"></property>

      </bean>

</beans>

//MyDao.java

package lee;

 

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.List;

 

importorg.springframework.jdbc.core.JdbcTemplate;

importorg.springframework.jdbc.core.RowMapper;

 

public class MyDao {

      privateJdbcTemplate temp;

      public JdbcTemplate getTemp() {

           return temp;

      }

      public void setTemp(JdbcTemplate temp) {

           this.temp = temp;

      }

      public void select(){

           StringstrSelect="select * from employee where id=7";

           List<String>lstRtn=temp.query(strSelect, new RowMapper<String>(){

                 @Override

                 public String mapRow(ResultSet rs, int rowNo) throwsSQLException {

                      // TODO Auto-generated method stub

                      Stringrtn="rowNo="+rowNo+":id="+rs.getInt(1)+",name="+rs.getString(2)+",addr="+rs.getString(3)+",no="+rs.getString(4);

                      System.out.println(rtn);

                      return rtn;

                 }

           });

          

      }

}

//Main.java

package lee;

 

importorg.springframework.context.ApplicationContext;

importorg.springframework.context.support.ClassPathXmlApplicationContext;

importorg.springframework.jdbc.core.JdbcTemplate;

 

public class Main {

 

      public static void main(String[] args) {

           // TODO Auto-generated method stub

           ApplicationContext ctx=newClassPathXmlApplicationContext("beans.xml");

           MyDao dao=ctx.getBean("myDao",MyDao.class);

           dao.select();

      }

}

//结果：

rowNo=0:id=7,name=updatexxx,addr=xxx,no=yyy