spring下配置Sql Server 2008 R2数据库连接详解编程语言

配置方法1：org.apache.commons.dbcp.BasicDataSource    

 
<?xml version="1.0" encoding="UTF8"?> 
<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:tx="http://www.springframework.org/schema/tx" xmlns:aop="http://www.springframework.org/schema/aop" 
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd 
http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd 
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd 
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd" 
default-lazy-init="false"> 
     
   <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"> 
    	<property name="location" value="classpath:jdbc.properties" /> 
    </bean> 
     
   <bean id = "dataSource" 
         class = "org.apache.commons.dbcp.BasicDataSource"> 
       <property name = "driverClassName" value = "${driverClassName}" /> 
       <property name = "url" value = "${url}" /> 
       <property name = "username" value = "${username}" /> 
       <property name = "password" value = "${password}" /> 
       <property name = "initialSize" value = "5" /> 
       <property name = "maxActive" value = "10" /> 
       </bean> 
 
</beans> 

配置方法2：org.springframework.jdbc.datasource.DriverManagerDataS    

 
<?xml version="1.0" encoding="UTF8"?> 
<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:tx="http://www.springframework.org/schema/tx" xmlns:aop="http://www.springframework.org/schema/aop" 
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd 
http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd 
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd 
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd" 
default-lazy-init="false"> 
     
   <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"> 
    	<property name="location" value="classpath:jdbc.properties" /> 
    </bean> 
     
   <bean id = "dataSource" 
         class = "org.springframework.jdbc.datasource.DriverManagerDataSource"> 
       <property name = "driverClassName" value = "${driverClassName}" /> 
       <property name = "url" value = "${url}" /> 
       <property name = "username" value = "${username}" /> 
       <property name = "password" value = "${password}" /> 
       </bean> 
 
</beans> 

配置方法3：org.springframework.jdbc.datasource.SingleConnectionDa    

 
<?xml version="1.0" encoding="UTF8"?> 
<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:tx="http://www.springframework.org/schema/tx" xmlns:aop="http://www.springframework.org/schema/aop" 
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd 
http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd 
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd 
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd" 
default-lazy-init="false"> 
     
   <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"> 
    	<property name="location" value="classpath:jdbc.properties" /> 
    </bean> 
     
   <bean id = "dataSource" 
         class = "org.springframework.jdbc.datasource.SingleConnectionDataSource"> 
       <property name = "driverClassName" value = "${driverClassName}" /> 
       <property name = "url" value = "${url}" /> 
       <property name = "username" value = "${username}" /> 
       <property name = "password" value = "${password}" /> 
       </bean> 
 
</beans> 

4配置信息文件jdbc.properties    

 driverClassName=net.sourceforge.jtds.jdbc.Driver 
 url=jdbc:jtds:sqlserver://127.0.0.1:1433/mespdb       
 username=sa 
 password=sa 

5利用java.sql.*测试    

package dbcptest; 
 
import java.sql.Connection; 
import java.sql.SQLException; 
import java.sql.Statement; 
 
import javax.sql.DataSource; 
 
import org.junit.Test; 
import org.springframework.context.ApplicationContext; 
import org.springframework.context.support.ClassPathXmlApplicationContext; 
import org.springframework.test.context.ContextConfiguration; 
import org.springframework.test.context.junit4.AbstractJUnit4SpringContextTests; 
 
@ContextConfiguration(locations = { "classpath:/spring-jdbc-test-singleconnection.xml" }) 
public class DbcpDataSourceTest extends AbstractJUnit4SpringContextTests { 
 
    private final String SQL_INSERT = "insert into users values('zhangsan3','123456')"; 
 
    @Test 
    public void testVoid() { 
        System.out.println("test dbcp..."); 
        @SuppressWarnings("resource") 
        ApplicationContext ctx = new ClassPathXmlApplicationContext("spring-jdbc-test-singleconnection.xml"); 
        DataSource ds = (DataSource) ctx.getBean("dataSource"); 
        Connection con = null; 
        try { 
            con = ds.getConnection(); 
        } catch (SQLException e) { 
            e.printStackTrace(); 
        } 
        try { 
            Statement st = con.createStatement(); 
            st.execute(SQL_INSERT); 
        } catch (SQLException e) { 
            e.printStackTrace(); 
        } 
    } 
} 

6使用Spring的模板类：JdbcTemplate    

配置文件中增加配置： 
   <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> 
   <property name="dataSource"> 
     <ref local="dataSource" /> 
   </property> 
  </bean> 
测试代码： 
package dbcptest; 
 
import org.junit.Test; 
import org.springframework.context.ApplicationContext; 
import org.springframework.context.support.ClassPathXmlApplicationContext; 
import org.springframework.jdbc.core.JdbcTemplate; 
import org.springframework.test.context.ContextConfiguration; 
import org.springframework.test.context.junit4.AbstractJUnit4SpringContextTests; 
 
@ContextConfiguration(locations = { "classpath:/spring-jdbc-test-drivermanager.xml" }) 
public class JdbcTemplateTest extends AbstractJUnit4SpringContextTests { 
 
    private final String SQL_INSERT = "insert into users values('zhangsan3','223456')"; 
     
    @Test 
    public void testVoid() { 
        System.out.println("test jdbcTemplate..."); 
        @SuppressWarnings("resource") 
        ApplicationContext ctx = new ClassPathXmlApplicationContext("spring-jdbc-test-drivermanager.xml"); 
        JdbcTemplate jdbct = (JdbcTemplate) ctx.getBean("jdbcTemplate"); 
        jdbct.execute(SQL_INSERT); 
    } 
}