java大批量处理数据代码

连接数据库程序：

public class CreateODBC {
	public static Connection getconn(){
		Connection conn = null;
		String driver = "com.mysql.jdbc.Driver";

		String url = "jdbc:mysql://localhost:3306/db?&characterEncoding=UTF-8";
		String user = "root";
		String password = "root";
		try {
			Class.forName(driver);
			conn=DriverManager.getConnection(url, user, password);
		} catch(Exception e) {
			e.printStackTrace();
		} 
		return conn;
	}
}

处理程序：

public class DoSql {
	/**
	 * 每次取存的数量
	 */
	static int rows = 5000;

	/**
	 * items总条数
	 */
	static int count = 0;
	/**
	 * 得到一个数据库连接
	 */
	static Connection con = null;
	/**
	 * 得items总条数的statement对象
	 */
	static Statement smSelectCount = null;
	/**
	 * 保存items总条数的结果
	 */
	static ResultSet rsSelectCount = null;
	/**
	 * 从items 查name、nums的statement对象
	 */

	static Statement smSelect = null;
	/**
	 * 保存从items 查name、nums的结果
	 */
	static ResultSet rsSelect = null;

	/**
	 * 将name(nums),name(nums)保存到orders表中的statement对象
	 */
	static Statement smUpdate = null;

	/**
	 * 查询orders表中是否已存在tostr（如果存在追加上去）
	 */
	static Statement smSelectName = null;
	/**
	 * 保存 查询orders表中是否已存在tostr结果
	 */
	static ResultSet rsSelectName = null;

	static String tostr = null;

	/**
	 * @param args
	 * @describe 将tiems 表中的name字段、nums字段
	 *           转换成name(nums),name(nums)...的形式，存到sdb_orders表的tostr字段
	 */

	public static void main(String[] args) {
		try {
			count = getCount();

			System.out.println("总条数为：" + count);
			int count_2 = count % rows;
			int times;
			if (count_2 == 0) {
				times = count / rows;
			} else {
				times = count / rows + 1;
			}
			int i = 0;
			for (i = 0; i < times; i++) {
				con=CreateODBC.getconn();
				smSelect = con.createStatement();
				smUpdate = con.createStatement();
				String sql = getSelectSql(i * rows);
				System.out.println(i + "、" + sql);
				rsSelect = smSelect.executeQuery(sql);
				int co = 0;
				while (rsSelect.next()) {
					tostr = rsSelect.getString("name") + "("
							+ rsSelect.getString("nums") + ")";
					//System.out.println(tostr);
					String getTostr = "SELECT order_id,tostr from sdb_orders where order_id ="
							+ rsSelect.getString("order_id") + ";";
					smSelectName = con.createStatement();
					rsSelectName = smSelectName.executeQuery(getTostr);
					while (rsSelectName.next()) {
						// 如果tostr不为空，就追加上去
						if (!((rsSelectName.getString("tostr")=="") || (rsSelectName
								.getString("tostr")==null))) {
							tostr +=(rsSelectName.getString("tostr")=="")?(""):("," + rsSelectName.getString("tostr"));
				
						}
					}
					String insertSql = "UPDATE sdb_orders SET tostr ='"
							+ escap(tostr) + "'WHERE order_id= '"
							+ rsSelect.getInt("order_id") + "';";
					System.out.println("插入的第" + (i * rows + co) + "条:"
							+ insertSql);
					smUpdate.executeUpdate(insertSql);
					co++;
				}
				rsSelectCount.close();
				rsSelect.close();

				smSelectCount.close();
				smSelect.close();
				smUpdate.close();
				con.close();
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			try {
				rsSelectCount.close();
				rsSelect.close();

				smSelectCount.close();
				smSelect.close();
				smUpdate.close();
				con.close();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		} finally {
			
		}

	}

	/**
	 * 得到 items 的总条数
	 * 
	 * @return 总条数
	 * @throws SQLException
	 */
	private static int getCount() throws SQLException {
		con = CreateODBC.getconn();
		smSelectCount = con.createStatement();
		String getcount = "select count(*) from items;";
		rsSelectCount = smSelectCount.executeQuery(getcount);
		while (rsSelectCount.next()) {
			count = rsSelectCount.getInt(1);
		}
		rsSelectCount.close();
		smSelectCount.close();
		return count;
	}

	/**
	 * 选出从m 开始的rows条数据
	 * 
	 * @param m
	 *            开始行
	 * @return 该sql语句
	 */
	public static String getSelectSql(int m) {
		String str = "";
		str = "SELECT name,nums,order_id FROM items LIMIT " + m + ","
				+ rows;
		return str;
	}

	/**
	 * 将字符串str中的'转义
	 * 
	 * @param str
	 *            要转义的字符串
	 * @return 转义后的字符串
	 */
	public static String escap(String str) {
		str = str.replace("'", "\\'");
		return str;
	}
}

本人菜鸟，请斧正

评论

1 楼 yuechengkun 2012-09-11  

这也算是大批量处理? 不靠边......