Java JDBC 中的命名参数

Named parameters in JDBC

提问by Fakrudeen

Are there named parameters in JDBC instead of positional ones, like the @name, @cityin the ADO.NET query below?

是否有一个名为JDBC中，而不是那些位置参数，比如@name，@city在下面的ADO.NET查询？

select * from customers where name=@name and city = @city

采纳答案by Malax

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.

JDBC 不支持命名参数。除非您必须使用普通 JDBC（这会引起痛苦，让我告诉您），否则我建议您使用 Springs Excellence JDBCTemplate，它可以在没有整个 IoC 容器的情况下使用。

NamedParameterJDBCTemplatesupports named parameters, you can use them like that:

NamedParameterJDBCTemplate支持命名参数，你可以这样使用它们：

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

回答by Ivan Vrtari?

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.

您不能在 JDBC 本身中使用命名参数。您可以尝试使用 Spring 框架，因为它具有一些允许在查询中使用命名参数的扩展。

回答by skaffman

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.

Vanilla JDBC 仅支持 a CallableStatement(eg setString("name", name))中的命名参数，即使如此，我怀疑底层存储过程实现必须支持它。

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

回答by kovica

Plain vanilla JDBC does not support named parameters.

普通的 JDBC 不支持命名参数。

If you are using DB2 then using DB2 classes directly:

如果您使用的是 DB2，那么直接使用 DB2 类：

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

1. 对 PreparedStatement 对象使用命名参数标记
2. 将命名参数标记与 CallableStatement 对象一起使用

回答by InvulgoSoft

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.

请注意，上面的简单示例不会处理两次使用命名参数。它也不处理使用引号内的 : 符号。