自己收藏用:spring jdbc

这是一个spring连接sql server数据库的例子。

POM文件:

 

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
 <modelVersion>4.0.0</modelVersion>

 <groupId>MyProject</groupId>
 <artifactId>myTestProject</artifactId>
 <version>0.0.1-SNAPSHOT</version>
 <packaging>jar</packaging>

 <name>myTestProject</name>
 <url>http://maven.apache.org</url>
 <repositories>
  <repository>
   <id>com.springsource.repository.bundles.release</id>
   <name>EBR Spring Release Repository</name>
   <url>http://repository.springsource.com/maven/bundles/release</url>
  </repository>
  <repository>
   <id>com.springsource.repository.bundles.external</id>
   <name>EBR External Release Repository</name>
   <url>http://repository.springsource.com/maven/bundles/external</url>
  </repository>
 </repositories>

 <properties>
  <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  <org.springframework.version>3.1.2.RELEASE</org.springframework.version>
  <project.org.slf4j.version>1.6.6</project.org.slf4j.version>
 </properties>

 <dependencies>
  <dependency>
   <groupId>junit</groupId>
   <artifactId>junit</artifactId>
   <version>3.8.1</version>
   <scope>test</scope>
  </dependency>
  <dependency>
   <groupId>com.jolbox</groupId>
   <artifactId>bonecp</artifactId>
   <version>0.7.1.RELEASE</version>
   <exclusions>
    <!--<exclusion>
     <groupId>com.google.guava</groupId>
     <artifactId>guava</artifactId>
    </exclusion>-->
    <exclusion>
     <groupId>org.slf4j</groupId>
     <artifactId>slf4j-api</artifactId>
    </exclusion>
   </exclusions>
  </dependency>
  <!-- Logging -->
  <dependency>
   <groupId>org.slf4j</groupId>
   <artifactId>slf4j-api</artifactId>
   <version>${project.org.slf4j.version}</version>
  </dependency>
  <dependency>
   <groupId>org.slf4j</groupId>
   <artifactId>jcl-over-slf4j</artifactId>
   <version>${project.org.slf4j.version}</version>
   <scope>runtime</scope>
  </dependency>

  <dependency>
   <groupId>ch.qos.logback</groupId>
   <artifactId>logback-classic</artifactId>
   <version>1.0.7</version>
  </dependency>

  <!-- Core utilities used by other modules. Define this if you use Spring
   Utility APIs (org.springframework.core.*/org.springframework.util.*) -->
  <dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring-core</artifactId>
   <version>${org.springframework.version}</version>
  </dependency>

  <!-- Expression Language (depends on spring-core) Define this if you use
   Spring Expression APIs (org.springframework.expression.*) -->
  <dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring-expression</artifactId>
   <version>${org.springframework.version}</version>
  </dependency>

  <!-- Bean Factory and JavaBeans utilities (depends on spring-core) Define
   this if you use Spring Bean APIs (org.springframework.beans.*) -->
  <dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring-beans</artifactId>
   <version>${org.springframework.version}</version>
  </dependency>

  <!-- Aspect Oriented Programming (AOP) Framework (depends on spring-core,
   spring-beans) Define this if you use Spring AOP APIs (org.springframework.aop.*) -->
  <dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring-aop</artifactId>
   <version>${org.springframework.version}</version>
  </dependency>

  <!-- Application Context (depends on spring-core, spring-expression, spring-aop,
   spring-beans) This is the central artifact for Spring's Dependency Injection
   Container and is generally always defined -->
  <dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring-context</artifactId>
   <version>${org.springframework.version}</version>
  </dependency>

  <!-- Various Application Context utilities, including EhCache, JavaMail,
   Quartz, and Freemarker integration Define this if you need any of these integrations -->
  <dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring-context-support</artifactId>
   <version>${org.springframework.version}</version>
  </dependency>

  <!-- Transaction Management Abstraction (depends on spring-core, spring-beans,
   spring-aop, spring-context) Define this if you use Spring Transactions or
   DAO Exception Hierarchy (org.springframework.transaction.*/org.springframework.dao.*) -->
  <dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring-tx</artifactId>
   <version>${org.springframework.version}</version>
  </dependency>

  <!-- JDBC Data Access Library (depends on spring-core, spring-beans, spring-context,
   spring-tx) Define this if you use Spring's JdbcTemplate API (org.springframework.jdbc.*) -->
  <dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring-jdbc</artifactId>
   <version>${org.springframework.version}</version>
  </dependency>

  <!-- Object-to-Relation-Mapping (ORM) integration with Hibernate, JPA,
   and iBatis. (depends on spring-core, spring-beans, spring-context, spring-tx)
   Define this if you need ORM (org.springframework.orm.*) -->
  <dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring-orm</artifactId>
   <version>${org.springframework.version}</version>
  </dependency>

  <!-- Object-to-XML Mapping (OXM) abstraction and integration with JAXB,
   JiBX, Castor, XStream, and XML Beans. (depends on spring-core, spring-beans,
   spring-context) Define this if you need OXM (org.springframework.oxm.*) -->
  <dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring-oxm</artifactId>
   <version>${org.springframework.version}</version>
  </dependency>

  <!-- Web application development utilities applicable to both Servlet and
   Portlet Environments (depends on spring-core, spring-beans, spring-context)
   Define this if you use Spring MVC, or wish to use Struts, JSF, or another
   web framework with Spring (org.springframework.web.*) -->
  <dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring-web</artifactId>
   <version>${org.springframework.version}</version>
  </dependency>

  <!-- Spring MVC for Servlet Environments (depends on spring-core, spring-beans,
   spring-context, spring-web) Define this if you use Spring MVC with a Servlet
   Container such as Apache Tomcat (org.springframework.web.servlet.*) -->
  <dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring-webmvc</artifactId>
   <version>${org.springframework.version}</version>
  </dependency>

  <!-- Spring MVC for Portlet Environments (depends on spring-core, spring-beans,
   spring-context, spring-web) Define this if you use Spring MVC with a Portlet
   Container (org.springframework.web.portlet.*) -->
  <dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring-webmvc-portlet</artifactId>
   <version>${org.springframework.version}</version>
  </dependency>

  <!-- Support for testing Spring applications with tools such as JUnit and
   TestNG This artifact is generally always defined with a 'test' scope for
   the integration testing framework and unit testing stubs -->
  <dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring-test</artifactId>
   <version>${org.springframework.version}</version>
   <scope>test</scope>
  </dependency>
  <dependency>
   <groupId>net.sourceforge.jtds</groupId>
   <artifactId>jtds</artifactId>
   <version>1.2.7</version>
  </dependency>
 </dependencies>
</project>

//App.java

package com.nf;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;

/**
 * Hello world!
 *
 */
public class App
{
    public static void main( String[] args )
    {
     ApplicationContext appContext = new ClassPathXmlApplicationContext("classpath:myspring.xml");
        TestClass tst = appContext.getBean(TestClass.class);
        tst.displayDB();
     System.out.println( "Hello World!" );
    }
}

 

//TestClass.java

package com.nf;

import java.sql.*;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;

public class TestClass {
 private static Logger logger = LoggerFactory.getLogger(TestClass.class);
 private DataSource dataSource;
 public TestClass(DataSource inDataSource) {
  dataSource = inDataSource;
 }
 public void displayDB() {
  JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
  String query = "select * from FormElement";
  int iColumnNumber = getColumnNumber(query);
  SqlRowSet sqlRowSet = jdbcTemplate.queryForRowSet(query);
  if (sqlRowSet.first() == true ) {
   while (sqlRowSet.isAfterLast() == false) {
    for(int i = 1; i <= iColumnNumber ; i++) {
     Object obj = sqlRowSet.getObject(i);
     logger.info(String.format("%s ", obj.toString()));
    }
    logger.info("----------");
    sqlRowSet.next();
   }
  }
 }
 private int getColumnNumber(String query) {
  int iColumnsNumber = 0;
  try {
   Connection conn = dataSource.getConnection();
   Statement st = conn.createStatement();
   ResultSet rs = st.executeQuery(query);
   ResultSetMetaData rsmd = rs.getMetaData();

   iColumnsNumber = rsmd.getColumnCount();
   conn.close();
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
  return iColumnsNumber;
 }
}

//myspring.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
 xmlns:jdbc="http://www.springframework.org/schema/jdbc" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans.xsd
       http://www.springframework.org/schema/jdbc
       http://www.springframework.org/schema/jdbc/spring-jdbc.xsd">

 <bean
  class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
  <property name="locations" value="classpath:myspring.properties" />
 </bean>
 <bean class="com.nf.TestClass">
  <constructor-arg name="inDataSource" ref="dataSource" />
 </bean>
 <bean id="dataSource" class="com.jolbox.bonecp.BoneCPDataSource"
  destroy-method="close">
  <property name="driverClass" value="${db.driverClass}" />
  <property name="jdbcUrl" value="${db.jdbcUrl}" />
  <property name="username" value="${db.dbusername}" />
  <property name="password" value="${db.dbpassword}" />
  <property name="idleConnectionTestPeriodInMinutes" value="10" />
  <property name="idleMaxAgeInMinutes" value="10" />
  <property name="maxConnectionsPerPartition" value="1" />
  <property name="minConnectionsPerPartition" value="1" />
  <property name="partitionCount" value="1" />
  <property name="acquireIncrement" value="5" />
  <property name="statementsCacheSize" value="100" />
  <property name="releaseHelperThreads" value="3" />
 </bean>

 <bean id="transactionManager"
  class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
  <property name="dataSource" ref="dataSource" />
 </bean>
 <beans profile="production">

  <!--<jdbc:initialize-database data-source="dataSource" enabled="#{systemProperties['INITIALIZE_DATABASE']
   != null}"> <jdbc:script location="org/springframework/batch/core/schema-mysql.sql"
   /> </jdbc:initialize-database> -->
 </beans>

</beans>

 

//myspring.properties

db.driverClass=net.sourceforge.jtds.jdbc.Driver
db.jdbcUrl=jdbc:jtds:sqlserver://127.0.0.1:1452/ApplicationExpertShare;
db.dbusername=sa
db.dbpassword=......