In general, for working with JDBC-mysql , I have only three data types:
Integer, String, Long

I'm not a fan of complicating things, but still, for convenience, I created the following PreparedStatement:

 private final int STR = 1, INT = 2, LONG = 3; private class Param { private int type, valueInt; private long valueLong; private String valueString; private Param(int type, String valueString) { this.type = type; this.valueString = valueString; } private Param(int type, int valueInt) { this.type = type; this.valueInt = valueInt; } private Param(int type, long valueLong) { this.type = type; this.valueLong = valueLong; } } private PreparedStatement prepareStatement(Connection connection, String sql, Param... params) throws SQLException { PreparedStatement prst = connection.prepareStatement(sql); for (int i = 0; i < params.length; i++) { switch (params[i].type) { case STR: prst.setString(i + 1, params[i].valueString); break; case INT: prst.setInt(i + 1, params[i].valueInt); break; case LONG: prst.setLong(i + 1, params[i].valueLong); break; } } return prst; }

Actually, this was done for the read try-with-resourse :

 try (PreparedStatement prst = prepareStatement(connection, "SELECT * FROM " + table + " WHERE id = ? AND age = ? AND time = ? LIMIT 1", new Param(STR, "value"), new Param(INT, 123), new Param(LONG, 123L)); ResultSet rs = prst.executeQuery()) { //... }

Ie here I have everything conveniently everything is beautiful ...

And there is still such an option known to all:

 private PreparedStatement prepareStatement(Connection connection, String sql, Object... params) throws SQLException { PreparedStatement prst = connection.prepareStatement(sql); for (int i = 0; i < params.length; i++) { prst.setObject(i + 1, params[i]); } return prst; }

And actually it is even more comfortable and beautiful:

 try (PreparedStatement prst = prepareStatement(connection, "SELECT * FROM " + table + " WHERE id = ? AND age = ? AND time = ? LIMIT 1", "value", 123, 123L); ResultSet rs = prst.executeQuery()) { //... }

But there is not something that is important to me - clarity .
And the definition of the data type is also an overhead.
Especially if customers are online 1000+ and requests are received every second.

Question: What do I risk and even risk in my implementation using the Param class? If there is a better option, please give an example.

UPD: Another option is Param :

 private final int STR = 1, INT = 2, LONG = 3; private class Param { private int type, valueInt; private long valueLong; private String valueString; private Param setString(String valueString) { this.type = STR; this.valueString = valueString; return this; } private Param setInt(int valueInt) { this.type = INT; this.valueInt = valueInt; return this; } private Param setLong(long valueLong) { this.type = LONG; this.valueLong = valueLong; return this; } } new Param(STR, value) vs new Param().setString(value)
  • The definition of clarity is not entirely clear. The type definition can be moved inside the Param constructor, because the type is already known, and the user is not required to specify the type. Type determination is not a costly operation, you can not worry. - Denis Lagutkin
  • But why if the same actions can be performed by PreparedStatement itself using setObject (1, object), but it will be longer than an explicit type indication, therefore I ask about the risks of Param - Sergey
  • I googled a lot of tests where instanceof vs switch vs if else compared and there is still a difference - Sergey
  • So you obviously do not specify the type anywhere. When you create an instance of the class Param(int, String) Param(int, int) Param(int, long) , there is always a choice of constructor depending on the second argument, and does not depend on the first one. In any case, using PreparedStatement you need to know the type of the argument. - Denis Lagutkin
  • one
    You can make static generation methods. Simply, the purpose of optimization is not clear? In this example, the most expensive operation is to work with the database, and the execution time of these queries is several times longer than the instanceof vs switch vs if else . If the code requirements are the minimum response time (for example, for trading), then you need to cache objects, data, make decision logic, etc. If this is the usual work of 1000+ queries, then the race in microseconds is not advisable when you access the database through the driver. - Denis Lagutkin

1 answer 1

I think that this option is simpler and more understandable:

 public static void setParam(PreparedStatement prstmt, int parameterIndex, String value) throws SQLException { prstmt.setString(parameterIndex, value); } public static void setParam(PreparedStatement prstmt, int parameterIndex, int value) throws SQLException { prstmt.setInt(parameterIndex, value); } public static void setParam(PreparedStatement prstmt, int parameterIndex, long value) throws SQLException { prstmt.setLong(parameterIndex, value); } ... PreparedStatement preparedStatement = connection.prepareStatement(sql); setParam(preparedStatement, 1, "StringValue"); setParam(preparedStatement, 2, 10); setParam(preparedStatement, 2, 15L);