Using mysql variables into a java JDBC prepared statement

Question

let's say I have the next query:

SELECT UNIX_TIMESTAMP(Logins.FechaLogin) FROM GA.Logins WHERE Logins.IdEmpleado = ? AND UNIX_TIMESTAMP(Logins.FechaLogin) >= UNIX_TIMESTAMP(?) AND UNIX_TIMESTAMP(Logins.FechaLogin) <= UNIX_TIMESTAMP(?)

And I want something like:

Date = UNIX_TIMESTAMP(Logins.FechaLogin);

SELECT UNIX_TIMESTAMP(Logins.FechaLogin) FROM GA.Logins WHERE Logins.IdEmpleado = ? AND Date >= UNIX_TIMESTAMP(?) AND Date <= UNIX_TIMESTAMP(?)

stmt.setInt(1, EmployeeId);
stmt.setString(2, Date1);
stmt.setString(3, Date2);

All into a Prepared Statement (JDBC prepareStatement()), is there a way I could do something like this in order to avoid redundance into the query? or is this something useless? by the way I think it has to pass the UNIX_TIMESTAMP function in each SELECT iteration if I'm correct.

Thank's!

What is the datatype of the FechaLogin column? Isn't " d >= t AND d <= t " the same thing as " d = t "? — spencer7593
– spencer7593, Commented Jul 8, 2013 at 19:12
It's long, I didn't specified any datatype because it was only an example, (I never worked with mysql variables before) but since I'm formating each timestamp into an unix time then I use long as datatype. — Neo
– Neo, Commented Jul 8, 2013 at 19:19
I did a mistake giving the example, sorry for the confusion, see my updated answer, thank's for your replies.. — Neo
– Neo, Commented Jul 8, 2013 at 19:26
All I want to do is to assing a variable into the prepared statement and then use it in the query, my question is, is it possible? — Neo
– Neo, Commented Jul 8, 2013 at 19:28

spencer7593 · Accepted Answer · 2013-07-08 20:23:42Z

The short answer is no, you can't reference a MySQL variable in place of a column reference, or in place of an expression that contains column references. In a prepared statement, you can only "bind" variables to values; you can't "bind" table names or column names.

(This restriction has to do with the way a SQL statement is processed. When a SQL statement is "prepared", the optimizer needs to know all of the tables and columns that are being referenced. It has to verify that the references are valid, that the user has privileges on the referenced objects, it has to lookup datatypes of the columns, check for suitable indexes, and so on. The issue is that it can't do that if column names are missing. This explains why you won't find any examples of how to do what you are wanting to do.)

Obviously, you could make use of Java variables and string manipulation to produce a String containing the SQL text that will be passed to MySQL. But the final string that gets passed to MySQL can have placeholders, but those can only be placeholders for values.

That should answer your question.

There's several other notes we can make, regarding the use of the UNIX_TIMESTAMP function (whether that's even necessary or desirable), performance implications of predicates that have column references wrapped in functions, etc. But those don't really answer the question you were asking.

Collectives™ on Stack Overflow

Using mysql variables into a java JDBC prepared statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related