I want to insert a row to table using JDBC. But table has a big number of columns, so I don’t want to specify all of them into a statement. Is there another way of specifying column values?
INSERT INTO TABLE_NAME VALUES(?, ?, ?, ..., ?)
I hope there is another way to do it without a big number of placeholders.
EDITED:
I mean, can I just edit query like ... meaning that it expects a many values and dynamically set values with incremented index or something else. And yes as @f1sh mentioned the problem is in the number of placeholders, but not in case of laziness, more in case of convenience and cleanliness.
P.S.: for some funny enterprise requirements reasons I can not use JPA frameworks 🙂
Advertisement
Answer
EDIT
Another option is to write your own or use SqlBuilder
SqlBuilder changes that whole scenario by wrapping the SQL syntax within very lightweight and easy to use Java objects which follow the “builder” paradigm (similar to StringBuilder). This changes many common SQL syntactical, runtime errors into Java compile-time errors! Let’s dive right in to some quick examples to to see how it all works.
You can use Spring’s NamedParameterJdbcTemplate and use variable names
String SQL = INSERT INTO TABLE_NAME VALUES((:id),...);
MapSqlParameterSource parameters = new MapSqlParameterSource();
parameters.addValue("id", "idValue");
//...
namedParameterJdbcTemplate.update(SQL, parameters) > 0;
Template class with a basic set of JDBC operations, allowing the use of named parameters rather than traditional ‘?’ placeholders.
If you can (safely) use dynamic query using Statement:
Statement stmt = (Statement) con.createStatement(“SELECT username, password FROM users WHERE username='” + user + “‘ AND password='” + pass + “‘ limit 0,1”); ResultSet rs = stmt.executeQuery();
You can use it only on closed values so there will be no option for SQL injection