Named parameters in JDBC

Question

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

User · Answer

You can t use named parameters in JDBC itself  You could try using Spring framework  as it has some extensions that allow the use of named parameters in queries

User · Answer

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 lt String gt  fields   new ArrayList lt String gt         Example of calling the class   String sql  sql    SELECT id  Name  Age  TS FROM TestTable WHERE Age  lt   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

User · Answer

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

User · Answer

Vanilla JDBC only supports named parameters in a CallableStatement  e g  setString  name   name    and even then  I suspect the underlying stored procedure implementation has to support it   An example of how to use named parameters     uss Sybase ASE sysobjects table   adjust for your RDBMS stmt   conn prepareCall  create procedure p1   id int   null   name varchar 255    null  as begin              if  id is not null              select   from sysobjects where id    id              else if  name is not null              select   from sysobjects where name    name               end    stmt execute       call the proc using one of the 2 optional params stmt   conn prepareCall   call p1       stmt setInt   id   10   ResultSet rs   stmt executeQuery    while  rs next          System out println rs getString 1          use the other optional param stmt   conn prepareCall   call p1       stmt setString   name    sysprocedures    rs   stmt executeQuery    while  rs next          System out println rs getString 1

User · Answer

Plain vanilla JDBC does not support named parameters   If you are using DB2 then using DB2 classes directly    Using named parameter markers with PreparedStatement objects Using named parameter markers with CallableStatement objects

[java] Named parameters in JDBC

Examples related to java

Examples related to jdbc

Examples related to named-parameters