JAVA connects to SQLServer basics and reads and writes 0.2 million data records (0.4 million operations) within five minutes. The qlserver

JAVA connects to SQLServer basics and reads and writes 0.2 million data records (0.4 million operations) within five minutes. The qlserver

First, the most basic connection method:

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);

Problems:

[Microsoft] [odbc SQL Server Driver] after another hstmt has checked many articles, it seems that resources, such as rs and statement, have not been released due to a large amount of access in a short time, conn, etc.

This is foundConnection Pool,Database Connection Pool: found the link http://bbs.csdn.net/topics/340245414 this post on the fourth floor method, as follows:

Create two files in the util package, one is performancefactory. java, and databaseresource. properties configuration file, there are several jar packages, commons-dbcp-1.2.1.jar, commons-pool-1.3.jar, msbase. jar, mssqlserver. jar, msutil. jar, these 5 jar packages are downloaded from the Internet.

The content of performancefactory. java is as follows:

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;}}

The content in databaseresource. properties is as follows: (comment after removing)

DriverClassName = com. microsoft. jdbc. sqlserver. SQLServerDriver
Url = jdbc: microsoft: sqlserver: // 127.0.0.1: 1433; DatabaseName = friend // friend indicates the Database Name

Username = sa // database connection username
Password = null // database connection password
DefaultAutoCommit = true // whether to automatically submit the transaction
MaxActive = 10 // maximum number of connections in the connection pool


Use it in the program after preparation
Connection conn = performancefactory. getInstance (). getConnection ();
You can wait until the database is connected.

So with my code:

As follows:

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();    }   }}

A new problem emerged this time,

[Microsoft] [SQLServer 2000 Driver for JDBC] Error establishing socket.

I guess it is still an old problem and the resources are not released

Unbearably, stupid method: after each processing of 100 records, disconnect and reconnect to the database. Sleep 100 ms before proceeding

if(i%100==0){serverLink.closeLink();serverLink.link();try {Thread.sleep(100);} catch (InterruptedException e) {// TODO Auto-generated catch blocke.printStackTrace();}}