Named parameters in JDBC

问题:

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

回答1:

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

回答2:

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.

回答3:

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

回答4:

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.

回答5:

Plain vanilla JDBC does not support named parameters.

If you are using DB2 then using DB2 classes directly:

1. Using named parameter markers with PreparedStatement objects
2. Using named parameter markers with CallableStatement objects