The JSR specifies the use of the? Sign.
Character ? this is not a wildcard parameter, it is only a placeholder, all the characters of the placeholders, the prepareStatement() function will be replaced by substitution variables (or bind variables ) using the syntax for the wildcard variables of the target DBMS.
If you specify the wildcard parameters in the SQL expression immediately in the form in which the target DBMS is prepared and compiled ( parse ), then the prepareStatement() function prepareStatement() not make any replacement and will not notice that it has been “deprived of work”.
Is this a driver implementation feature?
Yes, or better to say, this is a feature of the target DBMS that this driver supports. Regarding substitution variables, different DBMSs have different syntax for these variables:
Oracle:: :var ( colon )
MySQL, DB2 and Firebird ? ( question marks )
Sybase, MS SQL Server: @var ( at sign )
PostgreSQL: $var ( dollar sign )
To confirm the above, consider the following example.
Perform the same query several times with different use of placeholders and wildcard parameters:
create or replace and compile java source named TestPrepared as import java.sql.*; public class TestPrepared { public static void selectValue (int id, String item) { String[] stmts = { /*#1*/"/* ? */ select id, item from items where id = ? and item = ?", /*#2*/"/* ? */ select id, item from items where id = :1 and item = :2", /*#3*/"/* ? */ select id, item from items where id = :2 and item = :3", /*#4*/"/* ? */ select id, item from items where id = :id and item = :item"}; try { Connection con = DriverManager.getConnection("jdbc:oracle:thin:@dbsrv:1521/pdb1","sh","sh"); for (int i=0; i<stmts.length; i++) { PreparedStatement ps = con.prepareStatement(stmts[i]); ps.setInt (1, id); ps.setString (2, item); ResultSet resultSet = ps.executeQuery(); while (resultSet.next()) { System.out.println("id=" + resultSet.getObject(1) + ",item=" + resultSet.getObject(2)); } } } catch (Exception e) { e.printStackTrace(); } } } / create or replace procedure selectValue (id number, item varchar2) as language java name 'TestPrepared.selectValue (int, java.lang.String)'; / exec selectValue (2, 'item2') id=2,item=item2 id=2,item=item2 id=2,item=item2 id=2,item=item2
Which SQL expressions were sent to the database server:
select sql_id, parse_calls parses, substr (sql_text,1,78) text from v$sql where parsing_schema_name = user and sql_text like '/*%' ; SQL_ID PARSES TEXT ------------- ------ ------------------------------------------------------------------- dvc53b7ugwu0q 1 /* :1 */ select id, item from items where id = :1 and item = :2 8zbzysz5m53wf 2 /* :1 */ select id, item from items where id = :2 and item = :3 872ztnzx59hm8 1 /* :1 */ select id, item from items where id = :id and item = :item
In the original SQL expression # 1, the prepareStatement() function replaced the placeholders ? into substitution variables :1, :2, :3 . In # 3, the replacement did not happen (not counting in the comments). Both SQL expressions, # 1 and # 3, completely matched and are considered by the DB as one, prepared and compiled ( parses=2 ) SQL expression.
That is, there is no reaction to the name at all, only on the ":" order.
Yes, in the JDBC standard, only order is important ( bind by index ). In the code of the question to which this quotation relates, the order is broken, which led to an error. In the example examined above, the replacement was made taking into account only the order (ie, by the index), which names / numbers stood after the symbol : did not play any role.
If the substitution by name is interesting ( bind by name ), then as mentioned in of. documentation ch. Binding by name
It doesn’t support it.
Oracle does not support substitution by name when using the method, as in the question setString() . That is, it is not supported in the standard PreparedStatement JDBC interface.
But you can use, for example, an extension to this interface OraclePreparedStatement :
create or replace and compile java source named TestPreparedOra as import java.sql.*; import oracle.jdbc.*; public class TestPreparedOra { public static void selectValue (int id, String item) { try { Connection con = DriverManager.getConnection("jdbc:default:connection"); OraclePreparedStatement ps = (OraclePreparedStatement) con.prepareStatement ( "select id, item from items where item = :item and id = :id"); ps.setStringAtName("item", item); ps.setIntAtName( "id", id); ResultSet resultSet = ps.executeQuery(); while (resultSet.next()) { System.out.println("id=" + resultSet.getObject(1) + ",item=" + resultSet.getObject(2)); } } catch (Exception e) { e.printStackTrace(); } } } / create or replace procedure selectValueOraEx (id number, item varchar2) as language java name 'TestPreparedOra.selectValue (int, java.lang.String)'; / exec selectValueOraEx (2, 'item2') PL/SQL procedure successfully completed. id=2,item=item2
