How to add unit in date_sub function of mysql using java prepared statements

user_6207764 0
Viewed 815

I am trying to create a prepared query wherein the unit part of the date needs to be passed as a parameter. Below is the code 

PreparedStatement ps = conn.prepareStatement("select DATE_SUB(UTC_TIMESTAMP(),INTERVAL ? ?)");
ps.setInt(1,timeInterval);
ps.setString(2,unit);

Note: timeInterval is an int variable & unit is a String variable which can be "DAY","MINUTE" and so on. Using this final query build as

select DATE_SUB(1511846832475,INTERVAL 2 'DAY')

But when I run it the following exception is thrown.

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Day')' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.Util.getInstance(Util.java:381)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
prepared-statement mysql java
1 Answers

To my knowledge you cannot represent the interval unit using a placeholder. The reason for this is that, as you have seen, binding a unit as a string will escape it with single quotes. But it is possible to use a placeholder for the quantity, which you are already doing in your code snippet.

I can offer two workarounds. One is that you have separate prepared statements depending on whether the interval be DAY, MINUTE, and so on. But this might feel a bit clunky to you. Another workaround would be to convert all your interval values into a common unit, e.g. MINUTE. Then, just use the following single prepared statement for everything:

String query = "SELECT DATE_SUB(UTC_TIMESTAMP(), INTERVAL ? MINUTE)";
PreparedStatement ps = conn.prepareStatement(query);
ps.setInt(1, timeInterval);
user_18632290
Related