I am using MySQL with JDBC.
Is there a way I could use user-defined variables like "set @rank = 0;" in 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);
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.
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
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.
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);
User-defined variables persist in the MySQL session, which is bound to a single connection to the database.
JDBC is how your java code will interface with a MySQL DB, when you are creating a query/insert/update in your java code, you can have whatever java variables you'd like and add them into your query string.
Of course, I may be misunderstanding your question. Could you please elaborate a little more?
