MySQL Get value inserted by trigger in Java

Question

Using MySQL, I have the following SQL Table definition:

CREATE TABLE books (
    author INT, 
    book INT, 
    name VARCHAR(128), 

    PRIMARY KEY(author, book)
);

What I want is that I have an Id for author that I set manually and an Id for book that is incremented for each author id. Therefore I created a trigger like so:

CREATE TRIGGER trBooks
    BEFORE INSERT ON books
    FOR EACH ROW SET NEW.book = (
    SELECT COALESCE(MAX(book), -1) + 1 FROM books
    WHERE author = NEW.author
    );

This works fine for me. But now I need to know the book id that was set for my inserted entry that I inserted in Java. Something like the Insert with Output as in MSSQL or a Statement.executeQuery("INSERT ..."). The solution has to be thread safe, so a separate INSERT and SELECT is no good solution, since there might have been another INSERT in the meantime.

Thanks for your help!

Don't use a trigger, but instead do the insert with a stored procedure, and let it find the new book number, so it can do the insert *and* return the number. E.g. see [MySql: Insert a row and get the content](http://stackoverflow.com/q/5432756/5221149). — Andreas, Dec 25 '16 at 16:14
@Andreas Wouldn't the trigger work with the procedure's insert or what's the reason for the `don't use the trigger`? But thanks for that, did not know about procedures.. These will be called with `Statement.executeQuery()` in Java? — WhiteStripes, Dec 25 '16 at 16:20
Called using [`CallableStatement`](https://docs.oracle.com/javase/8/docs/api/java/sql/CallableStatement.html). The reason for replacing the trigger with the stored procedure is that the stored procedure needs to do the `SELECT MAX(book) + 1`, so it knows the value and can return it to the caller using an output parameter. — Andreas, Dec 25 '16 at 16:27
Ah of cause.. Thank you very much! This solved my problem :) — WhiteStripes, Dec 25 '16 at 16:58

score 0 · Accepted Answer · answered Dec 25 '16 at 16:11

Your data model just doesn't make sense. You have two entities, "books" and "authors". These should each be represented as a table. Because a book can have multiple authors and an author can write multiple books, you want a junction table.

This looks like this:

CREATE TABLE Books (
    BookId INT auto_increment primary key,
    Title VARCHAR(255)
);

CREATE TABLE Authors (
    AuthorId INT auto_increment primary key,
    Name VARCHAR(255)
);

CREATE TABLE BookAuthors (
    BookAuthorId INT auto_increment primary key,
    AuthorId INT, 
    BookId INT, 
    CONSTRAINT fk_BookAuthor_BookId FOREIGN KEY (BookId) REFERENCES Books(BookId),
    CONSTRAINT fk_BookAuthor_AuthorId FOREIGN KEY (BookId) REFERENCES Authors(AuthorId),
    UNIQUE (AuthorId, BookId)
);

As for your question about inserts. You don't need a trigger to set auto-incremented ids. You can use LAST_INSERT_ID() to fetch the most recent inserted value.

I admit, I did not select the best example here. But I did not want to put my whole table here, so I used this example. — WhiteStripes, Dec 25 '16 at 16:14
Does LAST_INSERT_ID() also work on Non-Auto-Increment fields? Like shown in my Example? — WhiteStripes, Dec 25 '16 at 16:15
@WhiteStripes . . . No. You can use `LAST_INSERT_ID()` to get the auto-increment primary key value of the row inserted, and then look up other values in that row. — Gordon Linoff, Dec 26 '16 at 15:15

MySQL Get value inserted by trigger in Java

1 Answers1