JDBC_批处理

package cn.tedu.jdbc.day02;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

import org.apache.commons.dbcp.BasicDataSource;

/*
 * 连接池版本的 数据库 连接管理工具类
 * 适合于并发场合
 * 封装数据源连接池
 */
public class DBUtils {
	private static String driver;
	private static String url;
	private static String username;
	private static String password;
	private static int initSize;
	private static int maxActive;
	private static BasicDataSource ds;
	/*
	 * 创建连接池，读值赋值
	 */
	static {
		//创建连接池
		ds = new BasicDataSource();
		Properties cfg = new Properties();
		try {
			InputStream in = DBUtils.class
					.getClassLoader().getResourceAsStream("db.properties");
			//通过流打开文件
			cfg.load(in);
			//初始化参数
			driver = cfg.getProperty("jdbc.driver");
			url = cfg.getProperty("jdbc.url");
			username = cfg.getProperty("jdbc.username");
			password = cfg.getProperty("jdbc.password");
			//initSize是一个整数，但是key这个里面存的是字符串，那么
			//怎么把整数变成字符串呢？
			initSize = Integer.parseInt(cfg.getProperty("initSize"));
			maxActive = Integer.parseInt(cfg.getProperty("maxActive"));
			in.close();
			//初始化连接池
			ds.setDriverClassName(driver);
			ds.setUrl(url);
			ds.setUsername(username);
			ds.setPassword(password);
			ds.setInitialSize(initSize);
			ds.setMaxActive(maxActive);
		}catch(Exception e) {
			e.printStackTrace();
			throw new RuntimeException();
		}
	}
	public static Connection getConnection() {
		try {
			//getConnection()从连接池中获取重用的连接
			//如果连接池满了，则等待
			//如果有连接归还则获取重用的连接
			Connection conn = ds.getConnection();
			return conn;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
	}
	//归还连接
	public static void close(Connection conn) {
		if(conn!=null) {
			try{
				//将用过的连接归还到连接池
				conn.close();
			}catch(Exception e) {
				e.printStackTrace();
			}
		}
	}
	//回滚
	public static void rollback(Connection conn) {
		try {
			if(conn!=null) {
			conn.rollback();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}



需要用到如上的工具类。

package cn.tedu.jdbc.day03;

import java.sql.Connection;
import java.sql.Statement;
import java.util.Arrays;

import cn.tedu.jdbc.day02.DBUtils;

/*
 * 
 * 执行一批DDL,DDL语句不支持事务（指创建或者删除表）
 * 
 */
public class Demo04 {
	public static void main(String[] args) {
		String sql1="create table log_01_zqk "
				+ "(id number(8), "
				+ "msg varchar2(100))";
		String sql2="create table log_02_zqk "
				+ "(id number(8), "
				+ "msg varchar2(100))";
		String sql3="create table log_03_zqk "
				+ "(id number(8), "
				+ "msg varchar2(100))";
		//执行一批SQL
		Connection conn = null;
		try {
			conn = DBUtils.getConnection();
			Statement st = conn.createStatement();
			//sql1、sql2、sql3添加到Statement对象的缓存区中
			st.addBatch(sql1);
			st.addBatch(sql2);
			st.addBatch(sql3);
			//执行一批SQL
			int[] ary = st.executeBatch();
			System.out.println(Arrays.toString(ary));
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			DBUtils.close(conn);
		}
	}
}

package cn.tedu.jdbc.day03;


import java.sql.Connection;
import java.sql.PreparedStatement;


import cn.tedu.jdbc.day02.DBUtils;


/*
 * insert批量更新
 */
public class Demo05 {
	public static void main(String[] args) {
		String sql="insert into log_01_zqk (id,msg) values (?,?)";
		Connection conn = null;
		try {
			conn = DBUtils.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, 1);
			ps.setString(2, "2018-01-01");
			int n = ps.executeUpdate();
			System.out.println(n);
			ps.setInt(1, 2);
			ps.setString(2, "2018-01-02");
			n = ps.executeUpdate();
			System.out.println(n);
			ps.setInt(1, 3);
			ps.setString(2, "2018-01-03");
			n = ps.executeUpdate();
			System.out.println(n);
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			DBUtils.close(conn);
		}
	}
}

package cn.tedu.jdbc.day03;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Arrays;


import cn.tedu.jdbc.day02.DBUtils;
/*
 * 批量参数处理
 */
public class Demo06 {
	public static void main(String[] args) {
		String sql="insert into robin_user_zqk "
				+ "(id,name,pwd) values (?,?,?)";
		
		int[] ary;
		Connection conn = null;
		try {
			conn = DBUtils.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			for(int i=1;i<=100;i++) {
			//替换参数
				ps.setInt(1, i);
				ps.setString(2, "name"+i);
				ps.setString(3, "123");
			//将参数添加到缓冲区
				ps.addBatch();
				//分段处理缓存
				if(i%8==0){
					ary = ps.executeBatch();//及时处理
					ps.clearBatch();//清空缓冲区
					System.out.println(Arrays.toString(ary));
				}
			}
			//最后一次列表不足500条，处理
			ary = ps.executeBatch();
			System.out.println(Arrays.toString(ary));
		/*	//批量执行
			int[] ary = ps.executeBatch();
			System.out.println(Arrays.toString(ary));//返回100个-2
*/					
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			DBUtils.close(conn);
		}
	}
}

package cn.tedu.jdbc.day03;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;


import cn.tedu.jdbc.day02.DBUtils;
/*
 * sequence不会发生回滚
 * 
 */
public class Demo08 {
	public static void main(String[] args) {
		Connection conn = null;
		try {
			conn = DBUtils.getConnection();
			conn.setAutoCommit(false);
			//让“话题表“中自动生成序列号
			String sql="insert into r_keywords_zqk "
					+ "(id,word) values(key_seq_zqk.nextval,?)";
			String[] cols = {"id"};//自动生成序号的列名
			PreparedStatement ps = 
					conn.prepareStatement(sql,cols);
			ps.setString(1, "雾霾");
			int n = ps.executeUpdate();
			if(n!=1) {
				throw new Exception("话题添加失败");
			}
			//获取自动生成的id
			ResultSet rs = ps.getGeneratedKeys();
			int id=-1;
			while(rs.next()) {
				id = rs.getInt(1);//取出
			}
			rs.close();
			ps.close();
			//..sql
			//将获取出来的id插入到内容表中去
			sql="insert into r_post_zqk "
					+ "(id,content,k_id) values "
					+ "(p_seq_zqk.nextval,?,?)";
			ps = conn.prepareStatement(sql);
			ps.setString(1, "今天天气不错，晚上有雾霾!");
			ps.setInt(2, id);
			n = ps.executeUpdate();
			if(n!=1) {
				throw new Exception("天气太遭");
			}
			conn.commit();
			System.out.println("OKOK");
		} catch (Exception e) {
			e.printStackTrace();
			DBUtils.rollback(conn);
		}finally {
			DBUtils.close(conn);
		}
	}
}