Hi,
I am using YugabyteDB YSQL. I have a use case where I need one conditional upsert to a table which has more than 100 fields, which is restricted in Yugabyte. I don’t want to use in that way too. I checked with jsonb and UDT and found UDT is handy.
I have created one Type where I have used all the fields that are required, passed it as IN parameter in a function. The function is working well. But from Java I am not able to map it properly. Below is what I have written:
Dependency :
com.yugabyte jdbc-yugabytedb 42.3.0My Java Code :
public void generateBatch(List myData) {
if (org.springframework.util.CollectionUtils.isEmpty(myData)) {
return;
}
log.info(“Called”);
try (Connection connection = dataLakeDriverManager.connect()) {
boolean autoCommit = connection.getAutoCommit();
connection.setAutoCommit(false);
handlePreparedStatement(connection, myData);
connection.setAutoCommit(autoCommit);
} catch (SQLException e) {
log.error("", e);
}
}
public Connection connect() throws SQLException {
Connection conn = null;
String dbConnectionString = connectionString();
conn = DriverManager.getConnection(dbConnectionString);
return conn;
}
private void handlePreparedStatement(Connection connection, List myData)
throws SQLException {
final int size = myData.size();
try (PreparedStatement prepareStatement = connection.prepareStatement("SELECT my_upsert(?)")) {
int rowNum = 1;
for (TxnDetails curTxnDetails : myData) {
prepareStatement.addBatch();
prepareStatement.setObject(1,
connection.createStruct("my_type", curTxnDetails.populate(prepareStatement, 0)));
if (rowNum % batchSize == 0 || rowNum == size) {
prepareStatement.executeBatch();
}
}
prepareStatement.executeBatch();
connection.commit();
} catch (SQLException e) {
log.error("", e);
connection.rollback();
}
}
The below error I am getting in runtime from the app log :
com.yugabyte.jdbc.PgConnection.createStruct(String, Object) is not yet implemented.
at com.yugabyte.Driver.notImplemented(Driver.java:848)
at com.yugabyte.jdbc.PgConnection.createStruct(PgConnection.java:1395)
at
ysqlsh => my_upsert(data my_type) :
CREATE OR REPLACE FUNCTION db.my_upsert(data my_type)
RETURNS void
LANGUAGE plpgsql
AS $function$
BEGIN
IF (condition 1) THEN
BEGIN
insert into db.table (data.col1,data.col2,data.col3,…,data.col109);
RETURN;
EXCEPTION
WHEN unique_violation THEN
–Let it go forward
END;
UPDATE db.table SET col1=data.col1, col2=data.co2, col3=data.col3, … , col109=data.col109 WHERE col20 = data.col20 and col5 = data.col5 and col104 = data.col104;
IF found THEN
RETURN;
END IF;
ELSE
LOOP
-- first try to update the key
UPDATE db.table SET col29=data.col29, col30=data.col30, col31=data.col30 WHERE col25 = $20 and rrn = $5 and txn_ts = $104;
IF found THEN
RETURN;
END IF;
-- not there, so try to insert the key
-- if someone else inserts the same key concurrently,
-- we could get a unique-key failure
BEGIN
Please let me know:
whether using UDT is a correct approach in this case.
How to bind the parameter from java.