转载请注明出处:http://coffeehot.iteye.com/admin/blogs/2184881
由于maven无法直接使用sqljdbc4.jar来连接SqlServer,因此使用jtds和c3p0来替代。
具体做法:
1. 加入pom依赖
<dependency>
<groupId>net.sourceforge.jtds</groupId>
<artifactId>jtds</artifactId>
<version>1.3.1</version>
</dependency>
<dependency>
<groupId>c3p0</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.1.2</version>
</dependency>
2. 在src/main/resources下加入c3p0-config.xml
<c3p0-config>
<default-config>
<property name="driverClass">net.sourceforge.jtds.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:jtds:sqlserver://localhost:1433/mydb
</property>
<property name="user">sa</property>
<property name="password">Admin123</property>
<property name="acquireIncrement">50</property>
<property name="initialPoolSize">100</property>
<property name="minPoolSize">50</property>
<property name="maxPoolSize">1000</property>
</default-config>
<named-config name="mydb">
<property name="driverClass">net.sourceforge.jtds.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:jtds:sqlserver://127.0.0.1:1433/mydb
</property>
<property name="user">sa</property>
<property name="password">Admin123</property>
<property name="checkoutTimeout">30000</property>
<property name="idleConnectionTestPeriod">30</property>
<property name="initialPoolSize">10</property>
<property name="maxIdleTime">30</property>
<property name="maxPoolSize">100</property>
<property name="minPoolSize">10</property>
<property name="maxStatements">200</property>
</named-config>
</c3p0-config>
3.测试
public class Test
{
private static HashMap connMap = new HashMap();
public static void main(String[] args) throws Exception
{
testConn();
}
public static void testConn() throws Exception
{
// Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
// Connection ct = DriverManager.getConnection("jdbc:sqlserver://localhost:1433; DatabaseName=mydb","sa","Admin123");
Connection ct = getConnection("mydb");
// Connection ct = getDefaultConnection();
PreparedStatement st = ct.prepareStatement("select id, name from dbo.test_conn");
st.execute();
ResultSet rs = st.getResultSet();
while(rs.next())
{
System.out.println("result: " + rs.getString(1));
}
}
public static Connection getConnection(String name){
Connection conn = null;
ComboPooledDataSource ds =null;
if(name==null||name.equals(""))
return null;
if(connMap.keySet().contains(name)){
ds = (ComboPooledDataSource)connMap.get(name);
}else{
ds = new ComboPooledDataSource(name);
}
try {
if(ds==null){
return null;
}
conn = ds.getConnection();
connMap.put(name, ds);
} catch (SQLException e) {
}
return conn;
}
public static Connection getDefaultConnection(){
ComboPooledDataSource ds = new ComboPooledDataSource();
try{
return ds.getConnection();
}catch(SQLException e){
}
return null;
}
}
{
private static HashMap connMap = new HashMap();
public static void main(String[] args) throws Exception
{
testConn();
}
public static void testConn() throws Exception
{
// Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
// Connection ct = DriverManager.getConnection("jdbc:sqlserver://localhost:1433; DatabaseName=mydb","sa","Admin123");
Connection ct = getConnection("mydb");
// Connection ct = getDefaultConnection();
PreparedStatement st = ct.prepareStatement("select id, name from dbo.test_conn");
st.execute();
ResultSet rs = st.getResultSet();
while(rs.next())
{
System.out.println("result: " + rs.getString(1));
}
}
public static Connection getConnection(String name){
Connection conn = null;
ComboPooledDataSource ds =null;
if(name==null||name.equals(""))
return null;
if(connMap.keySet().contains(name)){
ds = (ComboPooledDataSource)connMap.get(name);
}else{
ds = new ComboPooledDataSource(name);
}
try {
if(ds==null){
return null;
}
conn = ds.getConnection();
connMap.put(name, ds);
} catch (SQLException e) {
}
return conn;
}
public static Connection getDefaultConnection(){
ComboPooledDataSource ds = new ComboPooledDataSource();
try{
return ds.getConnection();
}catch(SQLException e){
}
return null;
}
}