Question posted in
The official documentation can be found here.

How can I use Redisson Write-through caching strategy with an auto-generated PK

I am planning to use Redis with Redisson as a caching layer between my Java app and a PostgreSQL DB.
I have a table called Nodes looking like this:

CREATE TABLE nodes
(
node_id bigint GENERATED BY DEFAULT AS IDENTITY(START WITH 1 INCREMENT BY 1),
node_name varchar(100) NOT NULL,
PRIMARY KEY (node_id)
)

I want to use Redisson RMap persistence to cache this structure.
My goal is to have an rmap looking like this:

Rmap<Integer, Node>

where the key is the PK, and the value is the node itself.

I want to use read-through and write-trhough strategies for caching this Rmap, by using the MapLoader and the MapWriter.

Then, I want to have a Java method which should create and persist a node. 

public void createNode(String nodeName) {
  Node node = new Node();
  node.setName(nodeName);

  // how can I put elements in the rmap since, 
  // the PK will be available after the INSERT statement will run?
  rmap.put(?, node);
}

And here comes the problem. Since the PK is auto-generated from Postgres, how can I use the RMapWriter to insert a node, since, for putting elements in the RMap I need the key, which I don’t have until the insert statement will run?

Tags:

2

Answers


  1. 0

    You can get generated Keys from postgres using prepared Statement. 

       ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS) ;
   ps.execute();
   ResultSet rs = ps.getGeneratedKeys();
    Login or Signup to reply.
  2. 0

    I’m aware this is an older post but answering because I came across the same issue.
    For me, the solution was to use the MapLoader rather than the MapWriter, using a CallableStatement (rather than a PreparedStatement) backed by a stored procedure in the SELECT_or_INSERTthenSELECT mould.

        @Override
    public Integer load(Node node) {
        // second parameter to the procedure is an OUTPUT param
        try(CallableStatement callableStatement = conn.prepareCall("{CALL INSERT_or_SELECT_THEN_INSERT (?, ?)}"))
        {
            callableStatement.setString(1, node.getName());
            callableStatement.registerOutParameter(2, java.sql.Types.BIGINT);
            callableStatement.executeUpdate();
    
            System.out.println("Debug nodeName: " + node.getName() + ", id: " + callableStatement.getLong(2));
            return callableStatement.getLong(2);
        } catch (Exception e) {
            throw new IllegalStateException(e);
        }
    }

    Now you can just call map.get(node) and the load() override will be called if the node is not in the map.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search