Java connection to SQL Server Foundation, and 5 minutes to read + Write 200,000 data (400,000 operations), problems encountered

First, the most basic way to connect:

Private String URL = "Jdbc:odbc:driver={sql Server}; Server=dell-pc;database=medicaldata ";p rivate string user =" sa ";p rivate string password =" Netlab ";p rivate 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 encountered:

[Microsoft][odbc SQL Server Driver] Connection busy leads to another hstmtAfter checking a lot of articles, the feeling should be due to a lot of short time access, resulting in not releasing resources, such as Rs,statement,conn

This is a check. connection pool, database connection pool: found the link http://bbs.csdn.net/topics/340245414 this post's four-storey approach, as follows:

Create a new two files in the Util package, one is Datasourcefactory.java, The other is the Databaseresource.properties configuration file, with several jar packages, Commons-dbcp-1.2.1.jar,commons-pool-1.3.jar,msbase.jar,mssqlserver.jar,msuti L.jar, these 5 jar packs go online and download them.

The contents of Datasourcefactory.java are as follows

Package Util;import org.apache.commons.dbcp.*;import java.sql.*;import java.util.*;p ublic class DataSourceFactory { private static String Properties_file = "/util/databaseresource.properties";p rivate static Datasourcefactory instance = Null;private basicdatasource ds1;private datasourcefactory () {Properties prop = new Properties (); try {prop.load ( This.getclass (). getResourceAsStream (Properties_file));d S1 = (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.printst Acktrace (); return null;} Return conn;}}

The contents of the databaseresource.properties are as follows: (Removal//post-comment)

Driverclassname=com.microsoft.jdbc.sqlserver.sqlserverdriver
Url=jdbc:microsoft:sqlserver://127.0.0.1:1433;databasename=friend//friend for database name

USERNAME=SA//Database connection user name
Password=null//Database connection password
Defaultautocommit=true//Whether the transaction is automatically committed
maxactive=10//Connection pool Max connections


After it's done in the program
Connection conn = Datasourcefactory.getinstance (). getconnection ();
You can wait until the database is connected.

So I have 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 ";p rivate string user =" sa ";p rivate string password =" Netlab ";p rivate 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.pri Ntstacktrace ();}}        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 (); }   }}

this time there is a new problem, run a little later,

[Microsoft] [SQL Server Driver for JDBC] Error establishing socket.

I guess it's an old problem, resources are not released.

Unbearable, stupid method: after each processing 100, disconnect, reconnect the database. Sleep 100ms to proceed again

if (i%100==0) {serverlink.closelink (); Serverlink.link (); try {thread.sleep ()} catch (Interruptedexception e) {// TODO auto-generated catch Blocke.printstacktrace ();}}

Java connection to SQL Server Foundation, and 5 minutes to read + Write 200,000 data (400,000 operations), problems encountered