Dynamically tune parameters for prepared Statement in JDBC

Question

I have a common class for all DAOs where we will read queries and execute them as shown below. I will send the parameters from the DAO to this class.

Connection connection = Queries.getConnection(); String query = Queries.getQuery(queryName);//Queries i will get from xml PreparedStatement preparedStatement = connection.prepareStatement(query); 

What is the best way to dynamically set parameters for a prepared Statement in JDBC. I suppose we do not have the concept of names in JDBC, as in spring JDBC. We are just JDBC in our project.

Answer 1

write something like this:

 public static int mapParams(PreparedStatement ps, Object... args) throws SQLException { int i = 1; for (Object arg : args) { if (arg instanceof Date) { ps.setTimestamp(i++, new Timestamp(((Date) arg).getTime())); } else if (arg instanceof Integer) { ps.setInt(i++, (Integer) arg); } else if (arg instanceof Long) { ps.setLong(i++, (Long) arg); } else if (arg instanceof Double) { ps.setDouble(i++, (Double) arg); } else if (arg instanceof Float) { ps.setFloat(i++, (Float) arg); } else { ps.setString(i++, (String) arg); } } } } 

and in queries just use '?' where you need to set the parameter.

I know this is an old school code, but just to give a minimalistic example ...

Answer 2

Take a look at this example page. Your request should contain? where you want to set the value.

 String query = "update COFFEES set SALES = ? where COF_NAME = ?"; 

And you can easily set values ​​like this

 preparedStatement.setInt(1, 100); preparedStatement.setString(2, "French_Roast"); 

Answer 3

A good way is to use a card.

 Map<String, Object> params = new HashMap<>(); params.put("id",0); params.put("name","test"); //more params here... String sql = "SELECT * FROM test"; boolean first = true; for (String paramName : params.keySet()) { Object paramValue = params.get(paramName); if (paramValue != null) { if (first){ sql += " where " + paramName + "=?"; first = false; } else { sql += " and " + paramName + "=?"; } } } Connection connection = DataSource.getInstance().getConnection(); ps = connection.prepareStatement(sql); int paramNumber = 1; for (String paramName : params.keySet()) { Object paramValue = params.get(paramName); if (paramValue != null) { if (param instanceof Date) { ps.setDate(paramNumber, (Date) param); } else if (param instanceof Integer) { ps.setString(paramNumber, (Integer) param); //more types here... } else { ps.setString(paramNumber, param.toString()); } paramNumber ++; } } 

Answer 4

Maybe you are interested in Named Parameters for PreparedStatement