JAVA连接SQLServer基础,以及5分钟之内读+写20万条数据(40万条操作),遇到的问题
首先,最基本的连接方式:
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);
遇到问题:
查了很多文章以后,感觉应该是由于短时间大量访问,导致没有释放资源,如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();
就可以等到数据库连接了
于是有了我的代码:
如下:
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 exception e.printStackTrace(); } } public void closeLink() { if(conn!=null) try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } public ResultSet select(String sql) { ResultSet rs= null; try { PreparedStatement partnames=conn.prepareStatement(sql); rs=partnames.executeQuery(); } catch (Exception e) { // TODO: handle exception e.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 exception e.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 block e.printStackTrace(); } }
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。