如何检查我的数据库中是否已经存在accountNumber(非主键)?

Question

String accountQuery = "insert into Account (accountNumber,currentBalance,type,personId) values (?,?,?,?);";
PreparedStatement accountPs = null;
try {
// These are my prepare Statements for my queries
accountPs = conn.prepareStatement(accountQuery);
//  accountPs.setInt(1, personId.getPersonId());
accountPs.setInt(1, accountHolder.getAccountNumber());
accountPs.setDouble(2, accountHolder.getCurrentBalance());
accountPs.setString(3, accountHolder.getType());
accountPs.setInt(4, personId.getPersonId());

accountPs.executeUpdate();
accountPs.close();
conn.close();
}

如何检查我的数据库中是否已经存在accountNumber(非主键)?每当我不止一次地运行我的程序时，它就会用重复的数据填充我的表，因为accountNumber不是主键，而且我的accountId是auto_increment。注意:我不能更改表的任何内容。

create table Account(
accountId int primary key not null auto_increment,
accountNumber int not null,
currentBalance double not null,
type varchar(1) not null,
personId int not null,
foreign key(personId) references Person(personId)
);

Answer 1

如果我理解你的问题，我能想到的最简单的事情是为你的account_number表添加一个唯一的约束。如,

ALTER TABLE Account ADD CONSTRAINT account_number_uniq UNIQUE (accountNumber)

Answer 2

如果你想避免一个异常，你需要检查这个数字是否已经在这里，如果没有添加它。可能像这样:

String accountQuery = "insert into Account (accountNumber,currentBalance,type,personId) values (?,?,?,?);";
PreparedStatement accountPs = null;
String checkQuery = "select count(*) noAccounts from Account where accountNumber = ?";
PreparedStatement accountCheck = null;
ResultSet checker = null;
try {
accountCheck = conn.prepareStatement(checkQuery);
accountCheck.setInt(1,accountHolder.getAccountNumber());
checker = accountCheck.executeQuery();
checker.next();  
if ( checker.getInt(1) == 0 ) {
// These are my prepare Statements for my queries
accountPs = conn.prepareStatement(accountQuery);
accountPs.setInt(1, accountHolder.getAccountNumber());
accountPs.setDouble(2, accountHolder.getCurrentBalance());
accountPs.setString(3, accountHolder.getType());
accountPs.setInt(4, personId.getPersonId());
accountPs.executeUpdate();
}
checker.close();
accountCheck.close();
accountPs.close();
conn.close();
}