Question 22120 in Mysql Question posted in
The official MySQL documentation can be found here.

How can I check if accountNumber (non primary key) already exists in my database? – Mysql

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();
}

How can I check if accountNumber (non primary key) already exists in my database? Whenever I run my program more than once, it’ll populate my tables with repeated data because accountNumber isn’t a primary key and because my accountId is an auto_increment. Note I cannot change any of the contents of the table.

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)
);
Tags:

2

Answers


  1. 0

    If I understand your question, the simplest thing I can think of is to add a unique constraint to your table for account_number. Like,

    ALTER TABLE Account ADD CONSTRAINT account_number_uniq UNIQUE (accountNumber)
    Login or Signup to reply.
  2. 0

    If you want to avoid an exception, you need to check if this number is already here and if not add it.
    Probably somehow like this:

    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();
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search