spring下配置Sql Server 2008 R2數據庫連接

需要導入包：
 <dependency>
    <groupId>org.springframework</groupId>
  <artifactId>spring-jdbc</artifactId>
  <version>3.2.4.RELEASE</version>
  </dependency>
  
  <dependency>
    <groupId>net.sourceforge.jtds</groupId>
  <artifactId>jtds</artifactId>
  <version>1.3.1</version>
  </dependency>
</div>

 

配置方法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);
    }
}