Greetings. Apologies, I posted it into the wrong thread originall.
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/6765ca9511cd40edff971c9858b0e96081bbde9d.camel%40manticore-projects.com.
