2

BookTable

isbn VARCHAR(25) NOT NULL PRIMARY KEY,
bookTitle VARCHAR(150) NOT NULL,
publisher VARCHAR (200) NOT NULL, 
copyRight  BLOB(45) NOT NULL,
isBookAvailable TINYINT(0) NOT NULL
UNIQUE(isbn)

AuthorTable

authorId INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
firtName VARCHAR(200) NOT NULL, 
lastName VARCHAR(200) NOT NULL

AuthorBooTable

authorIsbnId INT NOT NULL AUTO_INCREMENT,
isbn INT NOT NULL, 
authorId INT NOT NULL,
PRIMARY KEY ( authorIsbnId ), 
FOREIGN KEY (isbn) REFERENCES  BookTable (isbn) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (authorId) REFERENCES AuthorTable (authorId) ON DELETE CASCADE ON UPDATE CASCADE

So far, I know how to get the authorId by using Statement.RETURN_GENERATED_KEYS since the keys for the author table are generated automatically through "AUTO_INCREMENT". I need the both primary keys of the authorId from authorTable and isbn from the BookTable so that I can be able to create the relationship in the AuthorBookTable before saving the data to the database. My question, how can I get the isbn number from BookTable since it's a string and not "AUTO_INCREMENTED"? I have tried calling Statement.RETURN_GENERATED_KEYS but doesn't return the primary key for the Book isbn primary key. Your help will be greatly appreciated thanks!

Intelligent
  • 127
  • 1
  • 12
  • 1
    Which DBMS product and JDBC driver (version) are you using? –  Feb 12 '18 at 09:33
  • After inserting data into which table? With what SQL? – user207421 Feb 12 '18 at 09:42
  • Why do you need to get the isbn, you just inserted it, so you should already have it. And if not, why can't you just select it? – Mark Rotteveel Feb 12 '18 at 09:47
  • I am using mysql-connector-java-5.1.45-bin. The creation of the tables in the database is working great. the only problem I have is getting the isbn primary key from the BookTable. How do I determine the JBDC driver? I don't any clue. – Intelligent Feb 12 '18 at 09:48

1 Answers1

3

Try this one..

PreparedStatement ps = con.prepareStatement(insertsql, Statement.RETURN_GENERATED_KEYS)

Instead of Statement.RETURN_GENERATED_KEYS ,you can use column names:

PreparedStatement ps = con.prepareStatement(insertsql, new String[]{"isbn"}) 
            //put the name of the primary key column
    ps.executeUpdate();

    rs = ps.getGeneratedKeys();
    if (rs.next()) {
        generatedKey = rs.getString(1);
    }

Hope this fix your problem.

Kishor Velayutham
  • 818
  • 6
  • 18