Wednesday, September 28, 2022
HomeJavaSpring Knowledge JDBC - Use a sequence to generate main keys

Spring Knowledge JDBC – Use a sequence to generate main keys


By default, Spring Knowledge JDBC expects the database to offer a main key worth for each new report. The best technique to obtain that’s to make use of an autoincremented column. We used that within the Introduction to Spring Knowledge JDBC information. However what do you do in case your desk mannequin makes use of a database sequence as an alternative?

Spring Knowledge JDBC can, in fact, deal with that as properly. However it requires some additional code. As a substitute of counting on the default dealing with, you could get the worth from the database sequence and set the first key attribute earlier than the entity will get written to the database. The easiest way to try this is to implement a BeforeConvertCallback.

Implementing a BeforeConvertCallback to get a sequence worth

You would possibly already know the callback mechanism from different Spring Knowledge modules. The Entity Callback API was launched in Spring Knowledge Commons in model 2.2, and it’s the formally really useful technique to modify entity objects earlier than or after sure lifecycle occasions. When utilizing Spring Knowledge JDBC, you should utilize that mechanism to automate the retrieval of a sequence worth when persisting a brand new entity object.

Let’s use this method to robotically get a main key worth from a database sequence earlier than persisting the ChessGame combination.

public class ChessGame {

	@Id
	personal Lengthy id;
	
    personal String playerWhite;

    personal String playerBlack;

    personal Record<ChessMove> strikes = new ArrayList<>();

    ...
}

With none extra modifications, the next check case would persist the ChessGame combination and count on that the database supplies a main key worth. As talked about earlier, that is often achieved by modeling the first key column as an autoincremented column.

ChessGame sport = new ChessGame();
sport.setPlayerWhite("Thorben Janssen");
sport.setPlayerBlack("A robust participant");

ChessMove move1white = new ChessMove();
move1white.setMoveNumber(1);
move1white.setColor(MoveColor.WHITE);
move1white.setMove("e4");
sport.getMoves().add(move1white);

ChessMove move1Black = new ChessMove();
move1Black.setMoveNumber(1);
move1Black.setColor(MoveColor.BLACK);
move1Black.setMove("e5");
sport.getMoves().add(move1Black);

gameRepo.save(sport);

If you wish to use a special method to generate a main worth, you may set it utilizing a BeforeConvertCallback. Spring Knowledge JDBC will execute the callback earlier than it converts a ChessGame combination right into a database change.

As you may see within the following code snippet, the implementation of such a callback is easy. You implement the BeforeConvertCallback interface and supply the category of your combination root as a kind parameter.

@Part
public class GetSequenceValueCallback implements BeforeConvertCallback<ChessGame> {

    personal Logger log = LogManager.getLogger(GetSequenceValueCallback.class);

    personal closing JdbcTemplate jdbcTemplate;

    public GetSequenceValueCallback(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public ChessGame onBeforeConvert(ChessGame sport) {
        if (sport.getId() == null) {
            log.information("Get the subsequent worth from a database sequence and use it as the first key");

            Lengthy id = jdbcTemplate.question("SELECT nextval('chessgame_seq')",
                    rs -> {
                        if (rs.subsequent()) {
                            return rs.getLong(1);
                        } else {
                            throw new SQLException("Unable to retrieve worth from sequence chessgame_seq.");
                        }
                    });
            sport.setId(id);
        }

        return sport;
    }
}

When implementing the interface, you must outline a constructor that expects a JdbcTemplate. Spring will name it with a template that’s related to the present transaction. You possibly can then use that JdbcTemplate in your implementation of the onBeforeConvert technique.

Spring Knowledge JDBC triggers the BeforeConvertCallback for all insert and replace operations. When implementing the onBeforeConvert technique, you must, due to this fact, verify if the first key attribute is null. If that’s the case, we’re persisting a brand new combination and have to generate a novel main key worth. You are able to do this through the use of the JdbcTemplate to execute an SQL assertion that will get the subsequent worth from a database sequence and setting that worth as the first key.

That’s all you could do. In the event you rerun the identical check case, you may see the message written by the GetSequenceValueCallback and the SQL assertion to get the worth from the database sequence within the log output.

16:00:22.891  INFO 6728 - – [           main] c.t.j.mannequin.GetSequenceValueCallback     : Get the subsequent worth from a database sequence and use it as the first key
16:00:22.892 DEBUG 6728 - – [           main] o.s.jdbc.core.JdbcTemplate               : Executing SQL question [SELECT nextval('chessgame_seq')]
16:00:22.946 DEBUG 6728 - – [           main] o.s.jdbc.core.JdbcTemplate               : Executing ready SQL replace
16:00:22.947 DEBUG 6728 - – [           main] o.s.jdbc.core.JdbcTemplate               : Executing ready SQL assertion [INSERT INTO "chess_game" ("id", "player_black", "player_white") VALUES (?, ?, ?)]
16:00:22.969 DEBUG 6728 - – [           main] o.s.jdbc.core.JdbcTemplate               : Executing SQL replace and returning generated keys
16:00:22.970 DEBUG 6728 - – [           main] o.s.jdbc.core.JdbcTemplate               : Executing ready SQL assertion [INSERT INTO "chess_move" ("chess_game", "chess_game_key", "color", "move", "move_number") VALUES (?, ?, ?, ?, ?)]
16:00:22.979 DEBUG 6728 - – [           main] o.s.jdbc.core.JdbcTemplate               : Executing SQL replace and returning generated keys
16:00:22.980 DEBUG 6728 - – [           main] o.s.jdbc.core.JdbcTemplate               : Executing ready SQL assertion [INSERT INTO "chess_move" ("chess_game", "chess_game_key", "color", "move", "move_number") VALUES (?, ?, ?, ?, ?)]

Conclusion

By default, Spring Knowledge JDBC expects the database to offer a novel main key worth for each combination. Most DBAs use an autoincremented column for that.

As you noticed on this article, you may simply present your individual main key technology by implementing a BeforeConvertCallback. Spring Knowledge JDBC robotically calls it when persisting or updating an combination. Attributable to that, you could verify if you could generate the first key worth. If that’s the case, you should utilize a JdbcTemplate to execute a easy SQL assertion that will get the subsequent worth from a database sequence.

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments