Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Named parameters in JDBC [duplicate]

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 
like image 516
Fakrudeen Avatar asked Feb 22 '10 09:02

Fakrudeen


People also ask

How to write named parameterized query in java?

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);

What is named parameter JDBC template?

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.

What are named parameters in SQL?

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.


2 Answers

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); 
like image 116
Malax Avatar answered Oct 03 '22 02:10

Malax


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.

like image 20
InvulgoSoft Avatar answered Oct 03 '22 00:10

InvulgoSoft