小弟现在没分,请大家帮下忙吧 我需要对某一列进行更,请问我怎么更新,数据量太 大,有20万条记录。下面是代码,请大家看下,
package DES; import java.io.UnsupportedEncodingException;
import java.security.InvalidKeyException;
import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List; import javax.crypto.BadPaddingException;
import javax.crypto.IllegalBlockSizeException;
import javax.crypto.NoSuchPaddingException; import com.sun.corba.se.spi.orbutil.fsm.Guard.Result; public class DesDao {
private static final String sql = "select * from Actor";
private static final String sql_update = "update Actor set Dest = ? where A_id = ?";
private PreparedStatement ps;
private PreparedStatement ps_update;
private ResultSet rs;
private ResultSet rs_update; Connection conn = null;
Connection conn_update = null;
List al = null; public void updateDES() {
Connection conn = DBConnection.getConn();
Connection conn_update = DBConnection.getConn();
al = new ArrayList();
try {
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()){
DesBean desbean = new DesBean();
desbean.setA_id(rs.getInt(1));
try {
desbean.setDesencrypt(DesUtil.encrypt(rs.getString(2)));//将加密好的东西放进去
} catch (Exception e) { e.printStackTrace();
}
al.add(desbean);//将这个对象加入到bean里面去
}
for(int i=0;i <al.size();i++){ DesBean des1 = new DesBean();
des1 = (DesBean) al.get(i);
int id = des1.getA_id();
String dest = des1.getDesencrypt(); ps_update = conn_update.prepareStatement(sql_update);
ps_update.setString(1, dest);
ps_update.setInt(2, id);
ps_update.executeUpdate();//
} } catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
conn.close();
conn_update.close();
System.out.print("OK");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} } } }
package DES; import java.io.UnsupportedEncodingException;
import java.security.InvalidKeyException;
import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List; import javax.crypto.BadPaddingException;
import javax.crypto.IllegalBlockSizeException;
import javax.crypto.NoSuchPaddingException; import com.sun.corba.se.spi.orbutil.fsm.Guard.Result; public class DesDao {
private static final String sql = "select * from Actor";
private static final String sql_update = "update Actor set Dest = ? where A_id = ?";
private PreparedStatement ps;
private PreparedStatement ps_update;
private ResultSet rs;
private ResultSet rs_update; Connection conn = null;
Connection conn_update = null;
List al = null; public void updateDES() {
Connection conn = DBConnection.getConn();
Connection conn_update = DBConnection.getConn();
al = new ArrayList();
try {
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()){
DesBean desbean = new DesBean();
desbean.setA_id(rs.getInt(1));
try {
desbean.setDesencrypt(DesUtil.encrypt(rs.getString(2)));//将加密好的东西放进去
} catch (Exception e) { e.printStackTrace();
}
al.add(desbean);//将这个对象加入到bean里面去
}
for(int i=0;i <al.size();i++){ DesBean des1 = new DesBean();
des1 = (DesBean) al.get(i);
int id = des1.getA_id();
String dest = des1.getDesencrypt(); ps_update = conn_update.prepareStatement(sql_update);
ps_update.setString(1, dest);
ps_update.setInt(2, id);
ps_update.executeUpdate();//
} } catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
conn.close();
conn_update.close();
System.out.print("OK");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} } } }
DesBean des1 = new DesBean();
des1 = (DesBean) al.get(i);
int id = des1.getA_id();
String dest = des1.getDesencrypt();
首先这里不要这样写。DesBean des1 = null;
for(int i=0;i <al.size();i++){
des1 = (DesBean) al.get(i);
第二:使用批处理。
ps_update = conn.prepareStatement(sql_update);
DesBean des1 = null;
for(int i=0;i<al.size();i++){
des1 = (DesBean) al.get(i);
int id = des1.getA_id();
String dest = des1.getDesencrypt();
ps_update.setString(1, String.valueOf(dest));
ps_update.setInt(2,Integer.valueOf(id));
ps_update.addBatch();
}
int[]result = ps_update.executeBatch();
conn.commit();
批处理解决的是减少连接次数和sql执行次数的问题,但是大数据量的处理要1000 1000的来处理,否则会内存溢出
2. 如果数据库本身没有提供加密功能,建议你不要直接用JDBC更新。而是先将更新语句写到文件中,然后使用数据库自身的批处理功能进行更新。
for(int i=0;i<list.size();i++){
content=((String)list.get(i)).split(",");
pstmt.setString(1, content[0]);
pstmt.setString(2, content[1]);
pstmt.setDate(11, new java.sql.Date(date.getTime()));
pstmt.addBatch();
if(i!=0&&i%COMMIT_COUNT_TIME==0)conn.commit();
}
pstmt.executeBatch();
conn.commit();
content=((String)list.get(i)).split(",");
这行你可以无视,是我的用到的,你的用不到
这一行。
满1000条就提交一次,不要等到最后20万条一起提交,在你commit之前数据是保存到内存中的,20万足可以撑爆你的内存
还有阿,你不要原封不动的照搬我的代码啊,COMMIT_COUNT_TIME这个是个静态变量,你要在类下面加上这个:
public static int COMMIT_COUNT_TIME = 1000;(1000可以随便写,根据你的服务器性能而定)
Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
这个是报的错~
这个再改下,这个也会内存溢出,你不就是为了拿到一个ID方便修改吗?那你为什么select * 把所有的属性都查出来啊,对不对?
改称select a.id from Actor 再时时
第一列是id,第二列是name 第三级就是要在写入数据的列,列名是Dest
private static final String sql = "select a.id,a.password from Actor a";
private static final String sql_update = "update Actor set Dest = ? where A_id = ?";
private PreparedStatement ps;
private PreparedStatement ps_update;
private ResultSet rs;
private ResultSet rs_update;
private final static int COMMIT_COUNT_TIME = 1000;Connection conn = null;
Connection conn_update = null;
List al = null; public void updateDES() {
Connection conn = DBConnection.getConn();
Connection conn_update = DBConnection.getConn();
al = new ArrayList();
try {
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()){
DesBean desbean = new DesBean();
desbean.setA_id(rs.getInt(1));
try {
desbean.setDesencrypt(DesUtil.encrypt(rs.getString(2)));//将加密好的东西放进去
} catch (Exception e) { e.printStackTrace();
}
al.add(desbean);//将这个对象加入到bean里面去
}
for(int i=0;i <al.size();i++){
DesBean des1 = (DesBean) al.get(i);
int id = des1.getA_id();
String dest = des1.getDesencrypt(); ps_update = conn_update.prepareStatement(sql_update);
ps_update.setString(1, dest);
ps_update.setInt(2, id);
ps_update.addBatch();
if(i!=0&&i%COMMIT_COUNT_TIME==0)conn.commit();
}
ps_update.executeBatch();
conn.commit();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
conn.close();
conn_update.close();
System.out.print("OK");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} } } }
password改称name就可以了。
我优化了下,修改的地方改成红色了。
ps_update.executeBatch(); 这一行是使用批处理,放在for循环的外面,你原来的写法是在for里面,每条记录都执行一遍,也就是说执行了20万次。所以很慢。
package DES;import java.io.UnsupportedEncodingException;
import java.security.InvalidKeyException;
import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;import javax.crypto.BadPaddingException;
import javax.crypto.IllegalBlockSizeException;
import javax.crypto.NoSuchPaddingException;import com.sun.corba.se.spi.orbutil.fsm.Guard.Result;public class DesDao {
private static final String sql = "select A_id,name from Actor";
private static final String sql_update = "update Actor set Dest = ? where A_id = ?";
private PreparedStatement ps;
private PreparedStatement ps_update;
private ResultSet rs;
private ResultSet rs_update;
private final static int COMMIT_COUNT_TIME = 1000; Connection conn = null;
Connection conn_update = null;
List al = null; public void updateDES() {
conn = DBConnection.getConn();
conn_update = DBConnection.getConn();
int nuberm = 0;
al = new ArrayList();
try {
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();//得到其结果集
while(rs.next()){
DesBean desb = new DesBean();
desb.setA_id(rs.getInt(1));
try {
desb.setDesencrypt(DesUtil.encrypt(rs.getString(2)));
} catch (Exception e) {
e.printStackTrace();
}
al.add(desb);
}
ps_update = conn_update.prepareStatement(sql_update);
for(int i=1;i<al.size();i++){
DesBean des1 = (DesBean) al.get(i);
String dexstring = des1.getDesencrypt();
int id = des1.getA_id();
ps_update.setString(1,dexstring);//得到加密好的值
ps_update.setInt(2,id);
ps_update.addBatch();
if(i!=0&&i%COMMIT_COUNT_TIME==0)conn.commit();
}
ps_update.executeBatch();
conn.commit();
System.out.println("OK");
} catch (SQLException e) {
try {
conn_update.rollback();
throw e;
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
finally{
try {
conn.close();
conn_update.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
在rs = ps.executeQuery();//得到其结果集 这一行后面打印个System.out.println("******");
看看是查询报错还是后面的批处理报错
警告: Failed to load the sqljdbc_auth.dll
***************************
Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
at java.net.PlainSocketImpl.socketClose0(Native Method)
at java.net.PlainSocketImpl.socketPreClose(PlainSocketImpl.java:651)
at java.net.PlainSocketImpl.close(PlainSocketImpl.java:485)
at java.net.SocksSocketImpl.close(SocksSocketImpl.java:1030)
at java.net.Socket.close(Socket.java:1332)
at java.net.SocketInputStream.close(SocketInputStream.java:231)
at com.microsoft.sqlserver.jdbc.TDSChannel.close(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.close(Unknown Source)
at DES.DesDao.updateDES(DesDao.java:92)
at DES.DES.main(DES.java:7)