[java] 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

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.

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

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.

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