java编程出现mysql访问主键的错误,提示Duplicate entry '987656' for key 'PRIMARY'

「已注销」 2011-11-07 10:37:09
数据库代码:
import java.sql.*;
//连接数据
public class Dao {
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/mysqlTest";
String username = "root";
String password = "365642";
private Connection conn = null;
private Statement stmt = null;
private ResultSet rs = null;

public Dao(){
try{
Class.forName(driver);
conn = DriverManager.getConnection(url,username,password);
conn.setAutoCommit(false); //设置自动提交为false;
System.out.println("连接成功");
}catch(ClassNotFoundException e){
e.printStackTrace();
}catch(SQLException e){
e.printStackTrace();
}
}
//查询
public ResultSet executeQuery(String sql) throws SQLException{
try{
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery(sql);
}catch(SQLException e){
System.out.println("异常");
conn.rollback();
e.printStackTrace();
throw e;
}
return rs;
}
//更新
public int executeUpdate(String sql) throws SQLException{
int count = 0;
try{
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
count = stmt.executeUpdate(sql);
}catch(SQLException e){
System.out.println("异常");
conn.rollback();
e.printStackTrace();
throw e;
}
return count;
}
//向数据库提交数据
public void commit() throws SQLException{
try{
conn.commit();
}catch(SQLException e){
System.out.println("异常");
conn.rollback();
e.printStackTrace();
throw e;
}finally{
releaseConn();
}
}


//关闭与数据库的连接
public void releaseConn(){
try{
stmt.close();
if(conn!=null){
conn.close();
}
}catch(SQLException e){
System.out.println("关闭数据库异常");
e.printStackTrace();
}
}
}
调用数据库的类:
import java.sql.*;
public class CommonAccountCheck {
private ResultSet rs = null;
//Dao dao = new Dao();
public boolean checkAccountID(String userCardID){
boolean userflag = false;
Dao dao = new Dao();
String sqlSelectID = "";

try{
sqlSelectID = "Select count(*) from userCardInfo where userCardID = '"+userCardID+"'";
rs = dao.executeQuery(sqlSelectID);
rs.next();
int rowcount = rs.getInt(1);
System.out.println("检索出的数据记录数为:"+rowcount);
if(rowcount>0){
userflag = true;
}else{
userflag = false;
}
}catch(SQLException e){
e.printStackTrace();
}
return userflag;
}
//验证密码是否合理
public boolean checkAccountPwd(String userCardID,String userCardPwd){
boolean userflag = false;
Dao dao = new Dao();
String sqlSelect = "";
try{
sqlSelect ="select count(*) from userCardInfo where userCardID = '"+userCardID+"'And userCardPwd = '"+userCardPwd+"'";
rs = dao.executeQuery(sqlSelect);
rs.next();
int count = rs.getInt(1);
System.out.println("检索出的数据记录为:"+count);
if(count>0){
userflag = true;
}else{
userflag = false;
}
}catch(SQLException e){
e.printStackTrace();
}
return userflag;
}
//查询余额
@SuppressWarnings("finally")
public double queryBalance(String userCardID){
Dao dao = new Dao();
double money = 0.0;
String sqlSelect = "";
try{
sqlSelect = "select money from userCardInfo where userCardID = '"+userCardID +"'";
rs = dao.executeQuery(sqlSelect);
rs.next();
money = rs.getInt("money");
}catch(SQLException e){
e.printStackTrace();
}
finally{
return money;
}
}

public synchronized boolean reduceMoney(String userCardID,int getMoney){
boolean balanceflag = false;
Dao dao = new Dao();
String sqlselect = "select money from userCardInfo where userCardID = '"+userCardID+"'";
try{
rs = dao.executeQuery(sqlselect);
rs.next();
double money = rs.getDouble("money");
if(money > getMoney){
String updsql = "update userCardInfo set money = money-'"+getMoney+"' where userCardID = '"+userCardID+"'";
String inssql = "INSERT into userAccountInfo values ('"+userCardID+"','1',now(),0,"+getMoney+")";
dao.executeUpdate(updsql);
dao.executeUpdate(inssql);
dao.commit();
balanceflag = true;
}else{
balanceflag = false;
}
}catch(SQLException e){
e.printStackTrace();
}
return balanceflag;
}
@SuppressWarnings("static-access")
public void sleep(int second){
try{
Thread th = new Thread();
th.sleep(second);
}catch(Exception e){
e.printStackTrace();
}
}
public synchronized boolean transferInfo(String fromCardID,String toCardID,int transferMoney){
boolean transflag = false;
Dao dao = new Dao();//调用连接数据库的类
try{
String sqltoCard = "SELECT count(*) from userCardInfo where userCardID = '"+toCardID+"'";
rs = dao.executeQuery(sqltoCard);
rs.next();
int count = rs.getInt(1);
if(count>0){
String sqlMoney = "SELECT money from userCardInfo where userCardID = '"+fromCardID+"'";
rs = dao.executeQuery(sqlMoney);
rs.next();
double money = rs.getDouble("money");
if(money>transferMoney){
String udfosql = "update userCardInfo set money = money - '"+transferMoney+"' where userCardID = '"+fromCardID+"'";
String udtosql = "update userCardInfo set money = money + '"+transferMoney+"' where userCardID = '"+toCardID+"'";
String infromsql = "INSERT into userAccountInfo values('"+fromCardID+"','2',now(),"+transferMoney+",0)";
dao.executeUpdate(udfosql);
dao.executeUpdate(udtosql);
dao.executeUpdate(infromsql);
dao.commit();
transflag = true;
}else{
transflag = false;
}
}else{
transflag = false;
}
}catch(SQLException e){
e.printStackTrace();
}
System.out.println("判断是否成功");
return transflag;
}
public int changePwd(String userCardID,String userPwd,String oldPwd,String newPwd1,String newPwd2){
int pwdInt = 0;
Dao dao = new Dao();
try{
if(!userPwd.equals(oldPwd)){
pwdInt = -1;
}else if(newPwd1.length()==0||newPwd1.equals("")){
pwdInt = -2;
}else if(!newPwd1.equals(newPwd2)){
pwdInt = -3;
}else{
String updPwd = "update userCardInfo set userCardPwd ='" +newPwd1+"' where userCardID = '"+userCardID+"'";
dao.executeUpdate(updPwd);
dao.commit();
pwdInt = 1;
}
}catch(SQLException e){
e.printStackTrace();
}
return pwdInt;
}
public String searchList(String userCardID,String ymd){
Dao dao = new Dao();
String searchStr = "";
String selList;
try{
selList = "select u2.userCardID,u1.money,u2.operateType,sum(u2.remit),sum(u2.getMoney),DATE_FORMAT(u2.dates,'%Y/%m/%d')"
+"from userAccountInfo u2,userCardInfo u1 where u1.userCardID = u2.userCardID AND DATE_FORMAT(u2.dates,'%Y/%m/%d') ="
+"'"+ymd+"' AND u2.userCardID = '"+userCardID+"' GROUP BY u2.userCardID,u2.operateType";
System.out.println("查询明细的SQL语句为:"+selList);
rs = dao.executeQuery(selList);
rs.last();
int rowcount = rs.getRow();
System.out.println("检索记录数为:"+rowcount);
if(rowcount>0){
rs.first();
userCardID = rs.getString(1);
String money = rs.getString(2);
String operateType = rs.getString(3);
String remit = rs.getString(4);
String getMoney = rs.getString(5);
String date = rs.getString(6);
searchStr = userCardID+","+money+","+operateType+","+remit+","+getMoney+","+date+",";
while(rs.next()){
userCardID = rs.getString(1);
money = rs.getString(2);
operateType = rs.getString(3);
remit = rs.getString(4);
getMoney = rs.getString(5);
date = rs.getString(6);
searchStr = userCardID+","+money+","+operateType+","+remit+","+getMoney+","+date+",";
}
}else{
searchStr = "";
}
}catch(SQLException e){
e.printStackTrace();
}
System.out.println("searchStr:"+searchStr);
return searchStr;
}

}
运行提示错误:
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '987656' for key 'PRIMARY'
请高手们指点下,如何在一个方法中调用数据库,执行两次数据库更新操作,谢谢
...全文
354 回复 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
回复
切换为时间正序
请发表友善的回复…
发表回复
资源下载链接为: https://pan.quark.cn/s/67c535f75d4c 在使用MySQL数据库时,经常会遇到“mysql #1062 –Duplicate entry 1 for key PRIMARY”这个错误。它表明在向表中插入新数据时,主键出现了重复。主键是数据库表中用于唯一标识每条记录的关键字段,其值在表中必须是唯一的。在本例中,错误提示主键列(通常是ID列)的值1已经存在于表中,再次插入相同值的记录就会导致主键冲突。 这种情况常出现在WordPress、Discuz!等平台的数据迁移过程中。如果在备份和恢复数据时操作不当,比如备份数据中包含了与现有数据相同的主键值,那么在重新导入数据时就会触发这个错误。为了解决这个问题,可以采取以下几种方法: 首先,可以清空当前的数据表,然后再重新导入数据。因为主键要求唯一,清空数据表可以消除主键冲突的隐患,这是最直接的解决办法。 其次,要检查数据表的结构。有时候数据表的字段属性可能会丢失,例如自动递增(AUTO_INCREMENT)属性。如果这个属性丢失,插入数据时主键就不会自动递增,从而引发冲突。可以通过ALTER TABLE语句来修复字段属性,恢复自动递增功能。 另外,如果怀疑数据表损坏,可以尝试使用MySQL的REPAIR TABLE命令来修复。不过,在本案例中,这个方法可能并不奏效。 对于MyISAM存储引擎的表,还可以使用myisamchk工具进行修复。在命令行中运行myisamchk -r 数据表名.myi,其中数据表名是MyISAM表的文件名。这通常能够解决因数据表损坏而导致的主键冲突问题。 总之,处理“mysql #1062 –Duplicate entry 1 for key PRIMARY错误的关键在于理解主键的唯一性要求,并找到合适的方法来解决数据冲突,比如清理数据、修复表结构或使用专

51,402

社区成员

发帖
与我相关
我的任务
社区描述
Java相关技术讨论
javaspring bootspring cloud 技术论坛(原bbs)
社区管理员
  • Java相关社区
  • 小虚竹
  • 谙忆
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧