Are there named parameters in JDBC instead of positional ones, like the @name
, @city
in the ADO.NET query below?
select * from customers where name=@name and city = @city
String query = "select * from people where (first_name = :name or last_name = :name) and address = :address"); NamedParameterStatement p = new NamedParameterStatement(con, query); p. setString("name", name); p. setString("address", address);
NamedParameterJdbcTemplate class is a template class with a basic set of JDBC operations, allowing the use of named parameters rather than traditional '?' placeholders. This class delegates to a wrapped JdbcTemplate once the substitution from named parameters to JDBC style '?' placeholders is done at execution time.
Certain DBMSs allow an application to specify the parameters to a stored procedure by name instead of by position in the procedure call. Such parameters are called named parameters. ODBC supports the use of named parameters.
JDBC does not support named parameters. Unless you are bound to using plain JDBC (which causes pain, let me tell you that) I would suggest to use Springs Excellent JDBCTemplate which can be used without the whole IoC Container.
NamedParameterJDBCTemplate supports named parameters, you can use them like that:
NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource); MapSqlParameterSource paramSource = new MapSqlParameterSource(); paramSource.addValue("name", name); paramSource.addValue("city", city); jdbcTemplate.queryForRowSet("SELECT * FROM customers WHERE name = :name AND city = :city", paramSource);
To avoid including a large framework, I think a simple homemade class can do the trick.
Example of class to handle named parameters:
public class NamedParamStatement { public NamedParamStatement(Connection conn, String sql) throws SQLException { int pos; while((pos = sql.indexOf(":")) != -1) { int end = sql.substring(pos).indexOf(" "); if (end == -1) end = sql.length(); else end += pos; fields.add(sql.substring(pos+1,end)); sql = sql.substring(0, pos) + "?" + sql.substring(end); } prepStmt = conn.prepareStatement(sql); } public PreparedStatement getPreparedStatement() { return prepStmt; } public ResultSet executeQuery() throws SQLException { return prepStmt.executeQuery(); } public void close() throws SQLException { prepStmt.close(); } public void setInt(String name, int value) throws SQLException { prepStmt.setInt(getIndex(name), value); } private int getIndex(String name) { return fields.indexOf(name)+1; } private PreparedStatement prepStmt; private List<String> fields = new ArrayList<String>(); }
Example of calling the class:
String sql; sql = "SELECT id, Name, Age, TS FROM TestTable WHERE Age < :age OR id = :id"; NamedParamStatement stmt = new NamedParamStatement(conn, sql); stmt.setInt("age", 35); stmt.setInt("id", 2); ResultSet rs = stmt.executeQuery();
Please note that the above simple example does not handle using named parameter twice. Nor does it handle using the : sign inside quotes.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With