public class CheckUserTrueName {
static String dataTableName = "temp_user_name_0124";
public static void main(String[] args) {
long begin = System.currentTimeMillis();
beginCheck();
long end = System.currentTimeMillis();
System.out.println("process finish,use " + ((end - begin) / 1000) + "s");
}
public static void beginCheck() {
Connection conn = null;
int pageSize = 10000;
int totalPage = 0;
int count = 0;
try {
PreparedStatement ps = null;
ResultSet rs = null;
conn = getDBConnection();
ps = conn.prepareStatement("select count(id) record_ from " + dataTableName );
rs = ps.executeQuery();
if (rs.next())
count = rs.getInt("record_");
System.out.println("总记录数:" + count);
if (count != 0 && (count % pageSize) != 0) {
totalPage = count / pageSize + 1;
} else {
totalPage = count / pageSize;
}
System.out.println("总页数:" + totalPage);
int startEntityId = 8798629;
HashMap<Integer,String> dataResults = new HashMap<Integer,String>();
for (int m = 1; m <= totalPage; m++) {
dataResults = getDataInfo(conn, startEntityId, pageSize);
if(dataResults.size() < 0){
startEntityId = startEntityId + pageSize;
continue;
}
// StringBuffer content = new StringBuffer();
Set<Integer> invalidNameIdSet = new HashSet<Integer>();
Set<Integer> userIdSet = dataResults.keySet();
for(int userId : userIdSet){
if(startEntityId < userId) startEntityId = userId;
String userName = dataResults.get(userId);
if(!checkUserName(userName)){
// String fileName = "D:/success.txt";
// String content = "id:"+userId + ",name:" + userName + ",合格!\n";
// appendContent(fileName, content);
}else{
// if(userName !=null && !"".equals(userName)){
// //String fileName = "D:/error.txt";
// content.append("id:").append(userId).append(",name:(").append(userName).append("),不合格!\n");
// }
invalidNameIdSet.add(userId);
}
}
System.out.println("下页开始id" + startEntityId);
// String fileName = "D:/error.txt";
// appendContent(fileName, content.toString());
System.out.println("不合法数据条数:" + invalidNameIdSet.size());
updateStatus(conn, invalidNameIdSet);
dataResults.clear();
invalidNameIdSet.clear();
userIdSet.clear();
System.out.println("总页数:" + totalPage +",第"+m+"页处理完毕");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
/**
* @param conn
* @param invalidNameIdSet
*/
private static void updateStatus(Connection conn, Set<Integer> invalidNameIdSet) {
try{
conn.setAutoCommit(false);
int point = 0;
String updateSql = "update " + dataTableName + " set status=99 WHERE id=? ";
PreparedStatement pstmt = conn.prepareStatement(updateSql);
for(int invalidNameId : invalidNameIdSet){
pstmt.setInt(1, invalidNameId);
point++;
pstmt.addBatch();
if (point != 0 && point % 1000 == 0) {// 批量更新实体状态
pstmt.executeBatch();
conn.commit();
System.out.println("更新条数为" + point);
}
}
pstmt.executeBatch();
conn.commit();
conn.setAutoCommit(true);
} catch (Exception ex){
ex.printStackTrace(System.out);
System.out.println("更新实体的状态异常......");
}
}
/**
* 获取数据来源
* @return
*/
public static HashMap<Integer,String> getDataInfo(Connection conn, int startEntityId, int pageSize){
HashMap<Integer,String> dataResults = new HashMap<Integer,String>();
PreparedStatement ps = null;
ResultSet rs = null;
try{
String sql = " SELECT id, user_truename FROM "+dataTableName+" WHERE id > " + startEntityId + " ORDER BY id ASC limit " + pageSize;
System.out.println("sql:" + sql);
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()){
int user_id = rs.getInt(1);
String userTrueName = rs.getString(2);
dataResults.put(user_id, userTrueName);
}
System.out.println("!!!添加完成" );
} catch (Exception ex){
ex.printStackTrace(System.out);
System.out.println("查询简历的entity_id异常......");
}
return dataResults;
}
/**
* 校验姓名是否合法
* @param userTrueName
* @return
*/
public static boolean checkUserName(String userTrueName){
if(userTrueName == null) return true;
//姓名
String name = userTrueName.replaceAll("\\s*", "");
if(name.length() < 2 || name.length() > 30) return true;
name = name.replaceAll("[\u4e00-\u9fa5]*","");
name = name.replaceAll("[a-zA-Z]*","");
if(name.length() > 0 ){
return true;
}
return false;
}
/**
* 获取数据库连接
* @return
*/
public static Connection getDBConnection(){
String user = "";
String password = "";
String url = "jdbc:mysql://localhost:3306/rencai?useUnicode=true&characterEncoding=utf8";
String driver = "com.mysql.jdbc.Driver";
Connection con = null;
try{
Class.forName(driver);
con = DriverManager.getConnection(url, user, password);
}catch(ClassNotFoundException e){
System.out.println("数据库驱动不存在!");
System.out.println(e.toString());
}catch(SQLException e2){
System.out.println("数据库存在异常!");
System.out.println(e2.toString());
}
System.out.println("getDBConnection success ......");
return con;
}
}
static String dataTableName = "temp_user_name_0124";
public static void main(String[] args) {
long begin = System.currentTimeMillis();
beginCheck();
long end = System.currentTimeMillis();
System.out.println("process finish,use " + ((end - begin) / 1000) + "s");
}
public static void beginCheck() {
Connection conn = null;
int pageSize = 10000;
int totalPage = 0;
int count = 0;
try {
PreparedStatement ps = null;
ResultSet rs = null;
conn = getDBConnection();
ps = conn.prepareStatement("select count(id) record_ from " + dataTableName );
rs = ps.executeQuery();
if (rs.next())
count = rs.getInt("record_");
System.out.println("总记录数:" + count);
if (count != 0 && (count % pageSize) != 0) {
totalPage = count / pageSize + 1;
} else {
totalPage = count / pageSize;
}
System.out.println("总页数:" + totalPage);
int startEntityId = 8798629;
HashMap<Integer,String> dataResults = new HashMap<Integer,String>();
for (int m = 1; m <= totalPage; m++) {
dataResults = getDataInfo(conn, startEntityId, pageSize);
if(dataResults.size() < 0){
startEntityId = startEntityId + pageSize;
continue;
}
// StringBuffer content = new StringBuffer();
Set<Integer> invalidNameIdSet = new HashSet<Integer>();
Set<Integer> userIdSet = dataResults.keySet();
for(int userId : userIdSet){
if(startEntityId < userId) startEntityId = userId;
String userName = dataResults.get(userId);
if(!checkUserName(userName)){
// String fileName = "D:/success.txt";
// String content = "id:"+userId + ",name:" + userName + ",合格!\n";
// appendContent(fileName, content);
}else{
// if(userName !=null && !"".equals(userName)){
// //String fileName = "D:/error.txt";
// content.append("id:").append(userId).append(",name:(").append(userName).append("),不合格!\n");
// }
invalidNameIdSet.add(userId);
}
}
System.out.println("下页开始id" + startEntityId);
// String fileName = "D:/error.txt";
// appendContent(fileName, content.toString());
System.out.println("不合法数据条数:" + invalidNameIdSet.size());
updateStatus(conn, invalidNameIdSet);
dataResults.clear();
invalidNameIdSet.clear();
userIdSet.clear();
System.out.println("总页数:" + totalPage +",第"+m+"页处理完毕");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
/**
* @param conn
* @param invalidNameIdSet
*/
private static void updateStatus(Connection conn, Set<Integer> invalidNameIdSet) {
try{
conn.setAutoCommit(false);
int point = 0;
String updateSql = "update " + dataTableName + " set status=99 WHERE id=? ";
PreparedStatement pstmt = conn.prepareStatement(updateSql);
for(int invalidNameId : invalidNameIdSet){
pstmt.setInt(1, invalidNameId);
point++;
pstmt.addBatch();
if (point != 0 && point % 1000 == 0) {// 批量更新实体状态
pstmt.executeBatch();
conn.commit();
System.out.println("更新条数为" + point);
}
}
pstmt.executeBatch();
conn.commit();
conn.setAutoCommit(true);
} catch (Exception ex){
ex.printStackTrace(System.out);
System.out.println("更新实体的状态异常......");
}
}
/**
* 获取数据来源
* @return
*/
public static HashMap<Integer,String> getDataInfo(Connection conn, int startEntityId, int pageSize){
HashMap<Integer,String> dataResults = new HashMap<Integer,String>();
PreparedStatement ps = null;
ResultSet rs = null;
try{
String sql = " SELECT id, user_truename FROM "+dataTableName+" WHERE id > " + startEntityId + " ORDER BY id ASC limit " + pageSize;
System.out.println("sql:" + sql);
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()){
int user_id = rs.getInt(1);
String userTrueName = rs.getString(2);
dataResults.put(user_id, userTrueName);
}
System.out.println("!!!添加完成" );
} catch (Exception ex){
ex.printStackTrace(System.out);
System.out.println("查询简历的entity_id异常......");
}
return dataResults;
}
/**
* 校验姓名是否合法
* @param userTrueName
* @return
*/
public static boolean checkUserName(String userTrueName){
if(userTrueName == null) return true;
//姓名
String name = userTrueName.replaceAll("\\s*", "");
if(name.length() < 2 || name.length() > 30) return true;
name = name.replaceAll("[\u4e00-\u9fa5]*","");
name = name.replaceAll("[a-zA-Z]*","");
if(name.length() > 0 ){
return true;
}
return false;
}
/**
* 获取数据库连接
* @return
*/
public static Connection getDBConnection(){
String user = "";
String password = "";
String url = "jdbc:mysql://localhost:3306/rencai?useUnicode=true&characterEncoding=utf8";
String driver = "com.mysql.jdbc.Driver";
Connection con = null;
try{
Class.forName(driver);
con = DriverManager.getConnection(url, user, password);
}catch(ClassNotFoundException e){
System.out.println("数据库驱动不存在!");
System.out.println(e.toString());
}catch(SQLException e2){
System.out.println("数据库存在异常!");
System.out.println(e2.toString());
}
System.out.println("getDBConnection success ......");
return con;
}
}
解决方案 »
- JAVA DAO工厂模式访问多个数据库或是不同的数据库!!
- 求java学习资料
- 再问取数问题!!
- JTable 与 Vector组合,查询未知数据
- Java中取"yyyy-mm-dd hh:mm:ss"型当前时间,如何取呢?
- 请问在log4j中的DailyRollingFileAppender如何设定输出目录啊?
- Jvm在执行程序时垃圾回收器的工作原理是什么???
- 求附书的小例子,或者作业也可以阿,helloworld已经完成好久了
- 可不可以帮我用java编一个图形!!!!!
- 怎么和JBuilder一样自己安装JDK和JRE,而不用SUN的安装程序!
- 如何指定程序运行自己的security 类
- 关于class文件的问题
还要关闭
ResultSet rs
PreparedStatement ps
static String dataTableName = "temp_user_name_0124";
public static void main(String[] args) {
long begin = System.currentTimeMillis();
beginCheck();
long end = System.currentTimeMillis();
System.out.println("process finish,use " + ((end - begin) / 1000) + "s");
}
public static void beginCheck() {
Connection conn = null;
int pageSize = 10000;
int totalPage = 0;
int count = 0;
try {
PreparedStatement ps = null;
ResultSet rs = null;
conn = getDBConnection();
ps = conn.prepareStatement("select count(id) record_ from " + dataTableName );
rs = ps.executeQuery();
if (rs.next())
count = rs.getInt("record_");
System.out.println("总记录数:" + count);
if (count != 0 && (count % pageSize) != 0) {
totalPage = count / pageSize + 1;
} else {
totalPage = count / pageSize;
}
System.out.println("总页数:" + totalPage);
int startEntityId = 0;
HashMap<Integer,String> dataResults = new HashMap<Integer,String>();
for (int m = 1; m <= totalPage; m++) {
dataResults = getDataInfo(conn, startEntityId, pageSize);
if(dataResults.size() < 0){
startEntityId = startEntityId + pageSize;
continue;
}
Set<Integer> invalidNameIdSet = new HashSet<Integer>();
Set<Integer> userIdSet = dataResults.keySet();
for(int userId : userIdSet){
if(startEntityId < userId) startEntityId = userId;
String userName = dataResults.get(userId);
if(!checkUserName(userName)){ }else{
invalidNameIdSet.add(userId);
}
}
System.out.println("下页开始id" + startEntityId);
System.out.println("不合法数据条数:" + invalidNameIdSet.size());
updateStatus(conn, invalidNameIdSet);
dataResults.clear();
invalidNameIdSet.clear();
userIdSet.clear();
System.out.println("总页数:" + totalPage +",第"+m+"页处理完毕");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
/**
* @param conn
* @param invalidNameIdSet
*/
private static void updateStatus(Connection conn, Set<Integer> invalidNameIdSet) {
try{
conn.setAutoCommit(false);
int point = 0;
String updateSql = "update " + dataTableName + " set status=99 WHERE id=? ";
PreparedStatement pstmt = conn.prepareStatement(updateSql);
for(int invalidNameId : invalidNameIdSet){
pstmt.setInt(1, invalidNameId);
point++;
pstmt.addBatch();
if (point != 0 && point % 1000 == 0) {// 批量更新实体状态
pstmt.executeBatch();
conn.commit();
System.out.println("更新条数为" + point);
}
}
pstmt.executeBatch();
conn.commit();
conn.setAutoCommit(true);
} catch (Exception ex){
ex.printStackTrace(System.out);
System.out.println("更新实体的状态异常......");
}
}
/**
* 获取数据来源
* @return
*/
public static HashMap<Integer,String> getDataInfo(Connection conn, int startEntityId, int pageSize){
HashMap<Integer,String> dataResults = new HashMap<Integer,String>();
PreparedStatement ps = null;
ResultSet rs = null;
try{
String sql = " SELECT id, user_truename FROM "+dataTableName+" WHERE id > " + startEntityId + " ORDER BY id ASC limit " + pageSize;
System.out.println("sql:" + sql);
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()){
int user_id = rs.getInt(1);
String userTrueName = rs.getString(2);
dataResults.put(user_id, userTrueName);
}
System.out.println("!!!添加完成" );
} catch (Exception ex){
ex.printStackTrace(System.out);
System.out.println("查询简历的entity_id异常......");
}
return dataResults;
}
/**
* 校验姓名是否合法
* @param userTrueName
* @return
*/
public static boolean checkUserName(String userTrueName){
if(userTrueName == null) return true;
//姓名
String name = userTrueName.replaceAll("\\s*", "");
if(name.length() < 2 || name.length() > 30) return true;
name = name.replaceAll("[\u4e00-\u9fa5]*","");
name = name.replaceAll("[a-zA-Z]*","");
if(name.length() > 0 ){
return true;
}
return false;
}
/**
* 获取数据库连接
* @return
*/
public static Connection getDBConnection(){
String user = "";
String password = "";
String url = "jdbc:mysql://localhost:3306/rencai?useUnicode=true&characterEncoding=utf8";
String driver = "com.mysql.jdbc.Driver";
Connection con = null;
try{
Class.forName(driver);
con = DriverManager.getConnection(url, user, password);
}catch(ClassNotFoundException e){
System.out.println("数据库驱动不存在!");
System.out.println(e.toString());
}catch(SQLException e2){
System.out.println("数据库存在异常!");
System.out.println(e2.toString());
}
System.out.println("getDBConnection success ......");
return con;
}
}
以后越来越慢慢到30s处理一万,最后抛出内存溢出。
求解!
statement和resultSet最好显示关闭一下。别等垃圾回收了。
这两个set的clear() 没有必要。clear方法是全遍历一次。
dataResults.clear();
invalidNameIdSet.clear();
userIdSet.clear();