JAVA使用JDBC批量插入SQL

数据库连接使用proxool连接JDBC

代码 JAVA类

package com.czr.proxool;import org.logicalcobwebs.proxool.ProxoolException;import org.logicalcobwebs.proxool.configuration.JAXPConfigurator;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;/** * 使用proxool连接orcale * 当使用JDBC操作数据时出现起出打开游标最大数的原因：一般出现在执行大量插入语句 时，没有关闭Statement.解决方法：每次插入一条数据时或找执行一次批量提交时，记得 * @author chenzhirong */public class ProxoolUtile {public static String dburl = "jdbc:oracle:thin:@192.168.104.192:1521:tim";public static String user = "tim";public static String password = "tim_8968888";private static ProxoolUtile proxoolUtile;private static Connection conn;private static Statement stmt;private static int count;public static ProxoolUtile getProxoolUtile() {// TODO Auto-generated method stubif (proxoolUtile == null) {proxoolUtile = new ProxoolUtile();}return proxoolUtile;}/** * JDBC方式测试 *  * @throws Exception */public static void test1() throws Exception {String testsql = "select * from village t where lastid = 346";// 1：注册驱动类Class.forName("oracle.jdbc.driver.OracleDriver");// 2：创建数据库连接Connection conn = DriverManager.getConnection(dburl, user, password);// 3：创建执行SQL的对象Statement stmt = conn.createStatement();// 4：执行SQL，并获取返回结果ResultSet rs = stmt.executeQuery(testsql);// 5：处理返回结果，此处打印查询结果while (rs.next()) {System.out.print(rs.getLong("id") + "\t");System.out.print(rs.getString("name") + "\t");System.out.println();}// 6：关闭数据库连接conn.close();}/** * proxool方式测试 创建数据库连接 *  * @throws Exception */public static Connection getConn() {// Java应用中先要加载配置文件，否则谁知道你配置给谁用的try {if (conn == null) {JAXPConfigurator.configure("proxool.xml", false);// 1：注册驱动类，这次这个驱动已经不是Oracle的驱动了，是Proxool专用的驱动Class.forName("org.logicalcobwebs.proxool.ProxoolDriver");// 2：创建数据库连接，这个参数是一个字符串，是数据源的别名，在配置文件中配置的timalias，参数格式为：proxool.数据源的别名}if (conn == null || conn.isClosed() == true) {conn = DriverManager.getConnection("proxool.DBczr");}} catch (ProxoolException e) {System.out.println("配置文件不正確");e.printStackTrace();} catch (ClassNotFoundException e) {// TODO Auto-generated catch blockSystem.out.println("加載驱动");e.printStackTrace();} catch (SQLException e) {// TODO Auto-generated catch blockSystem.out.println("数据库连接断开");e.printStackTrace();}return conn;}/** * 使用Statement批量提交SQL *  * @param sql SQL语句 * @return */public int[] insertStmtBatchSql(String sql) {int[] i = null;if (count == 0) {try {if(conn == null || conn.isClosed() == true){ conn = ProxoolUtile.getConn();}stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);} catch (SQLException e1) {e1.printStackTrace();}}try {// 增加批量语句if(stmt == null){stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);}stmt.addBatch(sql);count++;// 500条SQL时，执行批量提交if (count > 500) {i = stmt.executeBatch();conn.commit();stmt.close();conn.close();count = 0;}} catch (SQLException e) {e.printStackTrace();}return i;}/** *  * Statement * 不完整个，自己加SQL语句参数 * @param sql * @return */public void insertBatchSql(String sql) {int count = 0;try {if (conn == null) {// Java应用中先要加载配置文件，否则谁知道你配置给谁用的JAXPConfigurator.configure("proxool.xml", false);// 1：注册驱动类，这次这个驱动已经不是Oracle的驱动了，是Proxool专用的驱动Class.forName("org.logicalcobwebs.proxool.ProxoolDriver");// 2：创建数据库连接，这个参数是一个字符串，是数据源的别名，在配置文件中配置的timalias，参数格式为：proxool.数据源的别名conn = DriverManager.getConnection("proxool.DBczr");// 设置手动提交conn.setAutoCommit(false);}PreparedStatement ps = conn.prepareStatement(sql);count++;// 记录if (count % 500 == 0) {// 当增加了500个批处理的时候再提交ps.executeBatch();// 执行批处理conn.commit();// 提交stmt.close();conn.close();// 关闭数据库}} catch (ProxoolException e) {System.out.println("配置文件不正確");e.printStackTrace();} catch (ClassNotFoundException e) {// TODO Auto-generated catch blockSystem.out.println("加載驱动");e.printStackTrace();} catch (SQLException e) {// TODO Auto-generated catch blockSystem.out.println("数据库连接断开");e.printStackTrace();}}/** * 查询序列 * Statement记得关闭，不然使用批量插入会有问题 * @param sql * @return */public Long querySeq(String sql) {Long seq = Long.valueOf(1);//Connection conn = null;Statement stmt = null;try {if(conn == null || conn.isClosed() == true ){conn = ProxoolUtile.getConn();}stmt = conn.createStatement();ResultSet rs = stmt.executeQuery(sql);while (rs.next()) {seq = rs.getLong("nextval");}} catch (SQLException e) {e.printStackTrace();} catch (Exception e) {e.printStackTrace();}finally{try {stmt.close();//conn.close();} catch (SQLException e) {e.printStackTrace();}}return seq;}/** * 关闭连接 */public static void closeConn() {try {if (conn != null) {conn.close();}} catch (SQLException e) {e.printStackTrace();}}public static void main(String[] args) throws Exception {// getStatement();}}

proxool  XML配置文件

<?xml version="1.0" encoding="UTF-8"?> <something-else-entirely>         <proxool>                 <alias>DBczr</alias>                 <!--数据源的别名 10.142.54.121 -->                <driver-url>jdbc:oracle:thin:@127.0.0.1:1521:orcl</driver-url>                 <!--url连接串-->                 <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>                 <!--驱动类-->                 <driver-properties>                         <property name="user" value="czr"/>                         <!--用户名-->                         <property name="password" value="czr"/>                        <!--密码-->                 </driver-properties>                 <!--最大连接数(默认5个),超过了这个连接数,再有请求时,就排在队列中等候,最大的等待请求数由maximum-new-connections决定 -->                 <maximum-connection-count>100</maximum-connection-count>                 <!--最小连接数(默认2个)-->                 <minimum-connection-count>10</minimum-connection-count>                 <!--proxool自动侦察各个连接状态的时间间隔(毫秒),侦察到空闲的连接就马上回收,超时的销毁 默认30秒-->                 <house-keeping-sleep-time>90000</house-keeping-sleep-time>                 <!--没有空闲连接可以分配而在队列中等候的最大请求数,超过这个请求数的用户连接就不会被接受-->                 <maximum-new-connections>10</maximum-new-connections>                 <!--最少保持的空闲连接数(默认2个)-->                 <prototype-count>5</prototype-count>                 <!--在使用之前测试-->                 <test-before-use>true</test-before-use>                 <!--用于保持连接的测试语句 -->                 <house-keeping-test-sql>select sysdate from dual</house-keeping-test-sql>         </proxool> </something-else-entirely>

耍要的JAR包

dom4j-1.6.1.jar
log4j-1.2.13.jar
ojdbc14.jar
proxool-0.9.1.jar
proxool-cglib.jar