Converting Spring Boot Postgres to H2

Question

I would like to start running H2 as an embedded database in my API layer which is very light weight. I am getting an error I can't seem to find much on, what are the major changes other than my SQL syntax for using H2 instead of postgres:

16 9:27:41 PM org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is java.lang.RuntimeException: Invalid value "1000" for parameter "resultSetHoldability" [90008-193]] with root cause
org.h2.jdbc.JdbcSQLException: Invalid value "1000" for parameter "resultSetHoldability" [90008-193]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
    at org.h2.message.DbException.get(DbException.java:179)
    at org.h2.message.DbException.getInvalidValueException(DbException.java:228)
    at org.h2.jdbc.JdbcConnection.checkHoldability(JdbcConnection.java:1445)
    at org.h2.jdbc.JdbcConnection.createStatement(JdbcConnection.java:261)
    at com.project.x.api.utils.PsqlUtils.getStatement(PsqlUtils.java:146)
    at com.project.x.api.utils.PsqlUtils.sqltoList(PsqlUtils.java:51)
    at com.project.x.api.utils.PsqlUtils.sqltoJson(PsqlUtils.java:79)
    at com.project.x.api.dao.UserDao.getUserCnt(UserDao.java:192)

Here is my class PsqlUtils:

@Component
public class PsqlUtils {

    final static Logger log = LoggerFactory.getLogger(PsqlUtils.class);

    //Config
    private final String db_url;
    private final String db_username;
    private final String db_password;
    @Autowired
    public PsqlUtils(@Value("${db.url}") String db_url, @Value("${db.username}") String db_username, @Value("${db.password}") String db_password) {
        this.db_url = db_url;
        this.db_username = db_username;
        this.db_password = db_password;
    }



    public List<Map<String, Object>> sqltoList(final String sql) {
        log.debug("SqltoJson sql : {}", sql);
        String jsonData;

        Connection connection;
        Statement statement;
        ResultSet resultSet;
        final List<Map<String, Object>> objList = new ArrayList<>();

        try  {
            connection = getConnection();
            connection.setAutoCommit(false);
            statement = getStatement(connection);
            resultSet = executeQuery(statement, sql);

            final int columnCount = resultSet.getMetaData().getColumnCount();

            while (resultSet.next()) {
                final Map<String, Object> rowData = new HashMap<>();
                for (int column = 1; column <= columnCount; ++column) {
                    rowData.put(resultSet.getMetaData().getColumnName(column), resultSet.getObject(column));
                }
                objList.add(rowData);
            }

            resultSet.close();
            statement.close();
            connection.close();


        }catch (SQLException | IOException e){
            throw new RuntimeException(e.getMessage(), e);
        }
        return objList;
    }

    public String sqltoJson(final String sql) throws SQLException {
        log.debug("SqltoJson sql : {}", sql);
        String jsonData;

        List<Map<String, Object>> objList = sqltoList(sql);

        if (!objList.isEmpty()) {
            final ObjectMapper mapper = new ObjectMapper();
            try {
                jsonData = mapper.writeValueAsString(objList);
            } catch (JsonProcessingException e) {
                throw new RuntimeException(e.getMessage(), e);
            }
        } else {
            jsonData = "[]";
        }



        return jsonData;
    }

    public String sqltoJsonObject(final String sql) throws SQLException {
        log.debug("SqltoJson sql : {}", sql);

        String jsonData;

        Connection connection;
        Statement statement;
        ResultSet resultSet;

        try  {
            connection = getConnection();
            connection.setAutoCommit(false);
            statement = getStatement(connection);
            resultSet = executeQuery(statement, sql);

            final int columnCount = resultSet.getMetaData().getColumnCount();
            final List<Map<String, Object>> objList = new ArrayList<>();

            resultSet.next();
            final Map<String, Object> rowData = new HashMap<>();
            for (int column = 1; column <= columnCount; ++column) {
                rowData.put(resultSet.getMetaData().getColumnName(column), resultSet.getObject(column));
            }
            objList.add(rowData);

            if (!objList.isEmpty()) {
                final ObjectMapper mapper = new ObjectMapper();
                jsonData = mapper.writeValueAsString(rowData);
            } else {
                jsonData = "[]";
            }


        } catch (SQLException | IOException e){
            throw new RuntimeException(e.getMessage(), e);
        }
        return jsonData;
    }

    public Connection getConnection() throws IOException, SQLException {

        //final Properties props = readProperties(propertiesPath);
        final Connection connection = DriverManager.getConnection(db_url, db_username, db_password);
        connection.setAutoCommit(false);
        return connection;
    }


    public Statement getStatement(Connection connection) throws SQLException {
        final Statement statement = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.FETCH_FORWARD);
        statement.setFetchSize(0);
        return statement;
    }

    public ResultSet executeQuery(Statement statement, String query) throws SQLException {
        log.debug("sql : {}", query);
        return statement.executeQuery(query);
    }

    public Statement getStatementExecute(Connection connection) throws SQLException {
        return connection.createStatement();
    }
}

Sample User DAO incase the problem may be there:

public String getUserCnt() throws NarratorException {
    final String sql = "select count(\"user_id\") cnt from \"user\"";
    final String jsonArray;

    try {
        jsonArray = psqlUtils.sqltoJson(sql);
    } catch (SQLException e){
        throw new RuntimeException(e.getMessage(), e);
    }
    if (jsonArray.equals("[]")) {
        throw new NarratorException(Response.Status.NOT_FOUND, "User Not Found!!!");
    }
        return jsonArray;
}

On The H2 Console the SQL works fine:

select count("user_id") cnt from "user";

Answer 1

Try to use PostgreSQL compatibility mode:

To use the PostgreSQL mode, use the database URL jdbc:h2:~/test;MODE=PostgreSQL or the SQL statement SET MODE PostgreSQL.