Greetings. To my best knowledge, H2 database does not support Named Parameters. Furthermore, Oracle does not support Parameters in DDL Statements (not even in CTAS). I have started writing a small library https://github.com/manticore-projects/MJdbcUtils in order to handle Named Parameter Statements nicely. Summary and examples are shown below.
It is in its very early stages and I will appreciate feedback and tests and are more than willing to incorporate interesting use cases. I do use H2 as basis for the development, although I want to be RDBMS agnostic as possible. Warm regards Andreas -- MJdbcUtils Library supporting Named Parameters (e.g. :Customer_Id ) in Queries or DML/DDL statements. Use case When the RDBMS does not support Named Parameters directly, it will: 1) find any Named Parameter, 2) replace it with an ordinary Positional Parameter ?, 3) maintain a map between the Position and the Named Parameter 4) provide methods for applying the Parameter Values and retrieving Parameter Type information. It supports PreparedStatements with parameters and also rewriting/injecting SQL Statements for execution without parameters. Rewriting/injecting is useful for Oracle Databases, which do not allow parameters for DDL Statements (not even for the query block of CTAS). Furthermore, MJdbcUtils makes it easy to build an UI Parameter Dialog based on the used Parameters and the Type Information. Examples Based on a Table Definition CREATE TABLE test ( a DECIMAL(3) PRIMARY KEY , b VARCHAR(128) NOT NULL , c DATE NOT NULL , d TIMESTAMP NOT NULL , e DECIMAL(23,5) NOT NULL ) 1) We can fill the table with a simple update // DML statement with Named Parameters String dmlStr = "INSERT INTO test VALUES ( :a, :b, :c, :d, :e )"; // Helper function will fill our parameter map with values Map<String, Object> parameters = toMap("a", 1, "b", "Test String", "c", new Date(), "d", new Date(), "e", "0.12345"); // Create a Prepared Statement, which holds our paramater mapping MPreparedStatement st = new MPreparedStatement(conn, dmlStr); // Execute our statement with the provided parameter values Assertions.assertFalse( st.execute(parameters) ); 2) We can fill table using Batch Updates int maxRecords = 100; int batchSize = 4; String dmlStr = "INSERT INTO test VALUES ( :a, :b, :c, :d, :e )"; Map<String, Object> parameters = toMap("a", 1, "b", "Test String", "c", new Date(), "d", new Date(), "e", "0.12345"); MPreparedStatement st = new MPreparedStatement(conn, dmlStr, batchSize); for (int i=0; i < maxRecords; i++) { parameters.put("a", i); parameters.put("b", "Test String " + i); // submit a new set of parameter values and execute automatically after 4 records int[] results = st.addAndExecuteBatch(parameters); } // submit any outstanding records st.executeBatch(); 3) We can query our table String qryStr = "SELECT Count(*) FROM test WHERE a = :a or b = :b"; Map<String, Object> parameters = toMap("a", 1, "b", "Test String", "c", new Date(), "d", new Date(), "e", "0.12345"); MPreparedStatement st = new MPreparedStatement(conn, qryStr); ResultSet rs = st.executeQuery(parameters); 4) We can rewrite our statement and inject the parameter values directly (useful for Oracle DDLs) Date dateParameterValue = new Date(); HashMap<String, Object> parameters = new HashMap<>(); parameters.put("param1", "Test String"); parameters.put("param2", 2); parameters.put("param3", dateParameterValue); String sqlStr = "select :param1, :param2, :param3;"; String rewrittenSqlStr = MJdbcTools.rewriteStatementWithNamedParameters(sqlStr, parameters); Assertions.assertEquals("SELECT 'Test String', 2, " + getSQLDateTimeStr(dateParameterValue), rewrittenSqlStr); sqlStr = "UPDATE tableName SET a = :param1, b = :param2, c = :param3;"; rewrittenSqlStr = MJdbcTools.rewriteStatementWithNamedParameters(sqlStr, parameters); Assertions.assertEquals("UPDATE tableName SET a = 'Test String', b = 2, c = " + getSQLDateTimeStr(dateParameterValue), rewrittenSqlStr); 5) We can retrieve the information about the used parameters for building a UI Dialog String qryStr = "SELECT * FROM test WHERE d = :d and c = :c and b = :b and a = :a and e = :e"; MPreparedStatement st = new MPreparedStatement(conn, qryStr); List<MNamedParameter> parameters = st.getNamedParametersByAppearance(); Output of the List: INFO: Found Named Parameters: D java.sql.Timestamp C java.sql.Date B java.lang.String A java.math.BigDecimal E java.math.BigDecimal -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/863843e79b79d2290503fafa94b49ef58125f25f.camel%40manticore-projects.com.