如何使用"set @variable = 0&"在JDBC中 [英] How to use "set @variable = 0" in JDBC

问题描述

我正在将MySQL与JDBC一起使用.

I am using MySQL with JDBC.

有没有一种方法可以使用用户定义的变量类似于设置@rank = 0;"在JDBC中?

Is there a way I could use user-defined variables like "set @rank = 0;" in JDBC?

特别是我想用JDBC实现排名.

Specifically I want to implement ranking with JDBC.

set @rank = 0;
set @userCount = (select COUNT(*) from usertwo);

update usertwo A set userRank = 100*(@rank:=@rank+1/@userCount) order by (select AVG(B.votePoint) from votelist B where A.userNum = B.targetUserNum);

推荐答案

我是MySQL DBA，但我对JDBC一无所知(与Java有关"除外，这是充分的理由)让我发现有关它的阅读会很痛苦)...但是，看来executeUpdate()是您要寻找的.

I'm a MySQL DBA but I don't know anything at all about JDBC (other than "it's something to do with Java," which is sufficient reason for me to find reading about it to be painful)... however, it looks like executeUpdate() is what you are looking for.

int executeUpdate(String sql)
              throws SQLException
Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE
statement or an SQL statement that returns nothing, such as an SQL DDL statement.

最后一部分(不返回任何内容的SQL语句")听起来像SET @rank = 0的合适描述；就结果集而言，它什么也不返回.

That last part ("an SQL statement that returns nothing") sounds like a fitting description of SET @rank = 0; it returns nothing as far as a result set.

Parameters:
sql - an SQL Data Manipulation Language (DML) statement, such as INSERT, 
      UPDATE or DELETE; or an SQL statement that returns nothing, 
      such as a DDL statement.
Returns:
      either (1) the row count for SQL Data Manipulation Language (DML) 
      statements or (2) 0 for SQL statements that return nothing
Throws:
      SQLException - if a database access error occurs, this method is called on
      a closed Statement or the given SQL statement produces a ResultSet object

http://docs.oracle.com/javase/6/docs/api/java/sql/Statement.html#executeUpdate%28java.lang.String%29

我假设这与您在UPDATE usertwo...查询中使用的东西相同...因此，针对同一个数据库连接依次执行的三个executeUpdate()调用应该可以实现您的预​​期.

I assume that's the same thing you'd use for the UPDATE usertwo... query... so three executeUpdate() calls, executed sequentially against the same database connection, should accomplish what you intend.

或者，实际上，您只需要对数据库进行2次调用，因为前两个可以合并为一个查询:

Or, actually, you only need 2 calls to the database, because the first two can be combined in a single query:

SET @rank = 0, @userCount = (select COUNT(*) from usertwo);

用户定义的变量保留在MySQL会话中，该会话绑定到数据库的单个连接.

User-defined variables persist in the MySQL session, which is bound to a single connection to the database.

这篇关于如何使用"set @variable = 0&"在JDBC中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！