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'
请高手们指点下,如何在一个方法中调用数据库,执行两次数据库更新操作,谢谢