JAVA串連SQLServer基礎,以及5分鐘之內讀+寫20萬條資料(40萬條操作),遇到的問題,javasqlserver
首先,最基本的串連方式:
private String url = "jdbc:odbc:Driver={SQL Server};Server=DELL-PC;Database=MedicalData";private String user = "sa";private String password = "netlab";private Connection conn;
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();conn = DriverManager.getConnection(url, user, password);
PreparedStatement partnames=conn.prepareStatement(sql); rs=partnames.executeQuery();
Statement partnames=conn.createStatement(); partnames.executeUpdate(sql);
遇到問題:
[Microsoft][ODBC SQL Server Driver]串連佔線導致另一個 hstmt查了很多文章以後,感覺應該是由於短時間大量訪問,導致沒有釋放資源,如rs,statement,conn等
這是查到了串連池 ,資料庫連接池:查到了連結 http://bbs.csdn.net/topics/340245414 這篇文章的四樓方法,如下:
在util包中建立兩個檔案,一個是DataSourceFactory.java,另一個是databaseresource.properties設定檔,還有幾個jar包,commons-dbcp-1.2.1.jar,commons-pool-1.3.jar,msbase.jar,mssqlserver.jar,msutil.jar,這5個jar包自己去網上下載下來
DataSourceFactory.java的內容如下
package util;import org.apache.commons.dbcp.*;import java.sql.*;import java.util.*;public class DataSourceFactory {private static String PROPERTIES_FILE = "/util/databaseresource.properties";private static DataSourceFactory instance = null;private BasicDataSource ds1;private DataSourceFactory() {Properties prop = new Properties();try {prop.load(this.getClass().getResourceAsStream(PROPERTIES_FILE));ds1 = (BasicDataSource)BasicDataSourceFactory.createDataSource(prop);} catch (Exception e) {e.printStackTrace();} }public static DataSourceFactory getInstance() {if(instance == null) {instance = new DataSourceFactory();}return instance;}public Connection getConnection() {Connection conn = null;try {conn = ds1.getConnection();} catch (Exception e) {e.printStackTrace();return null;}return conn;}}
databaseresource.properties中的內容如下:(去除//之後的注釋)
driverClassName=com.microsoft.jdbc.sqlserver.SQLServerDriver
url=jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=friend //friend為資料庫名
username=sa //資料庫連接使用者名稱
password=null //資料庫連接密碼
defaultAutoCommit=true //是否自動認可事務
maxActive=10 //串連池最大串連數
弄好後在程式中用
Connection conn = DataSourceFactory.getInstance().getConnection();
就可以等到資料庫連接了
於是有了My Code:
如下:
package com.k.sqlsever;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import util.DataSourceFactory;public class SQLSeverLink {private String url = "jdbc:odbc:Driver={SQL Server};Server=DELL-PC;Database=MedicalData";private String user = "sa";private String password = "netlab";private Connection conn;public void link(){try {//Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();// conn = DriverManager.getConnection(url, user, password);conn = DataSourceFactory.getInstance().getConnection();} catch (Exception e) {// TODO: handle exceptione.printStackTrace();}}public void closeLink(){if(conn!=null)try {conn.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}public ResultSet select(String sql){ResultSet rs= null;try {PreparedStatement partnames=conn.prepareStatement(sql); rs=partnames.executeQuery(); } catch (Exception e) {// TODO: handle exceptione.printStackTrace();}return rs;}public void update(String sql){try {Statement partnames=conn.createStatement(); partnames.executeUpdate(sql); System.out.println("update success!");} catch (Exception e) {// TODO: handle exceptione.printStackTrace();}} /** * @param args */ public static void main(String[] args) { // TODO Auto-generated method stub try { Connection conn = DataSourceFactory.getInstance().getConnection(); PreparedStatement partnames=conn.prepareStatement("SELECT * FROM dbo.MS_CHK_ALL WHERE id=1"); ResultSet rs=partnames.executeQuery(); while(rs.next()) { String Data=rs.getString("chk_code"); System.out.println(Data); } } catch ( Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } }}
這次又出現了新的問題,運行一小會以後,
[Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket.
我估計還是老問題,資源未釋放
忍無可忍,笨方法: 每處理100條以後,中斷連線,重連資料庫。sleep 100ms 再繼續進行
if(i%100==0){serverLink.closeLink();serverLink.link();try {Thread.sleep(100);} catch (InterruptedException e) {// TODO Auto-generated catch blocke.printStackTrace();}}