Without Hibernate, how to implement optimistic locking manually using native SQL?

Optimistic locking implemented manually with native SQL is done through record version control, usually using a version or timestamp field in the table.

Algorithm:

1. When reading a record, save the current version.
2. When updating, execute an SQL query with a condition that the version has not changed:

UPDATE table_name
SET data = ?, version = version + 1
WHERE id = ? AND version = ?

3. If the number of updated rows is 0 — it means someone else has modified the record, and you need to handle the conflict (for example, retry the operation or report an error).

Example in Java with JDBC:

String sql = "UPDATE table_name SET data = ?, version = version + 1 WHERE id = ? AND version = ?";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setString(1, newData);
ps.setLong(2, id);
ps.setInt(3, currentVersion);
int updated = ps.executeUpdate();
if (updated == 0) {
    throw new OptimisticLockException("Data was modified by another user");
}

This approach helps avoid database-level locks and detects conflicts during concurrent modifications.