1

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?

ioan tinca
  • 31
  • 2

2 Answers2

1

You can get generated Keys from postgres using prepared Statement. 

   ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS) ;
   ps.execute();
   ResultSet rs = ps.getGeneratedKeys();
tabreaz
  • 649
  • 4
  • 17
  • 1
    Yes, I know. But how can I do this using the Redisson MapWriter? The MapWriter.write method requests a Key,Value pair and then runs the INSERT statement. So, how can I put an element in the RMap without knowing the key, since the key will be generated just after the INSERT statement runs? – ioan tinca May 13 '20 at 08:31
  • I’m not aware of how MapWriter works, however you can consider Postgres sequence rather than autoincrement id. – tabreaz May 13 '20 at 13:15
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.

Nik Colman
  • 1
  • 1