简单mysql程序_【备忘】mysql简单操作程序

悲剧啊，发现原来写入数据库的数据有些问题，需要对这批数据进行处理

只有写几行代码连接到数据库批量修改了。

需要操作的字段数据：

public class GarbageData {

private long id;

private String comment;

public void setID(long id){

this.id = id;

}

public long getID(){

return this.id;

}

public void setComment(String comment){

this.comment = comment;

}

public String getComment(){

return this.comment;

}

}

修改代码：

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.List;

import org.apache.log4j.Logger;

public class UpdateFeature {

private Connection m_connection;

private Statement m_statement;

private ResultSet m_resultSet;

private String m_strUrl;

private String m_strDBUser;

private String m_strDBPass;

private static final Logger logger = Logger.getLogger(UpdateFeature.class);

public UpdateFeature(String strUrl,String strDBUser,String strDBPass){

this.m_strUrl = strUrl;

this.m_strDBUser = strDBUser;

this.m_strDBPass = strDBPass;

this.connDB();

}

public int connDB(){

try{

Class.forName("org.gjt.mm.mysql.Driver");

this.m_connection = DriverManager.getConnection(this.m_strUrl,this.m_strDBUser,this.m_strDBPass);

this.m_statement = this.m_connection.createStatement(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);

this.m_statement.setFetchSize(Integer.MIN_VALUE);

}catch(ClassNotFoundException e){

logger.error("Class not found exception.");

logger.error(e.getMessage(),e);

return -1;

}catch(SQLException ex){

logger.error("Connnect to " + this.m_strUrl + " failed!");

logger.error(ex.getMessage(),ex);

}

return 0;

}

public Statement getStatement(){

return this.m_statement;

}

public void updateFeature() throws SQLException{

String getSql = "SELECT * FROM `generalantispam`.`GarbageFeature` where insertTime > " + "\"2012-12-15 00:00:00\" and insertTime < " + "\"2012-12-18 00:00:00\"" + " order by garbageid desc";

logger.info(getSql);

ResultSet rs = this.m_statement.executeQuery(getSql);

List dataList = new ArrayList();

while (rs.next()){

GarbageData data = new GarbageData();

long garbageID = rs.getLong("garbageID");

String strComment = rs.getString("garbage");

String[] comments = strComment.split(";;;");

String strGarbage = comments[0];

data.setID(garbageID);

strGarbage = strGarbage.replaceAll("\"", "");

data.setComment(strGarbage);

dataList.add(data);

logger.info(strComment);

}

logger.info("List size: " + dataList.size());

this.m_connection.setAutoCommit(false);

for(int i = 0; i < dataList.size(); i++){

GarbageData data = dataList.get(i);

String strSql = "update `generalantispam`.`GarbageFeature` set garbage=\"" + data.getComment() + "\" where garbageID =" + data.getID() ;

logger.info(strSql);

this.m_statement.executeUpdate(strSql);

if (i % 50 == 0)

this.m_connection.commit();

}

this.m_connection.commit();

}

public void close(){

try {

if (this.m_resultSet != null)

m_resultSet.close();

if (this.m_statement != null)

m_statement.close();

if (this.m_connection != null)

m_connection.close();

} catch (Exception e) {

logger.equals(e.getMessage());

}

}

public static void main(String[] args) {

UpdateFeature upF = new UpdateFeature("jdbc:mysql://xxx.xxx.xxx.xxx:3306/generalantispam","garbage", "garbage");

try {

upF.updateFeature();

upF.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}