using java variable in sql statement

Question

I'm trying to build a web page to better learn Java and SQL. My question is, is there a way in Java to make a generic SQL select statement? For example:

SELECT var1 FROM var2 WHERE var3=var4

or something of the sort.

My idea is to fill the vars with user selected items from the web page. I know this can be done in PHP using the Post method, but I'm not using PHP. Also, I've read about the Prepared Statement in Java, but seems only to work when the used after the comparison operator; ex:

SELECT * FROM table Where attr = ? &

Also, I do know i can do the hard coded version of "SELECT " + var1 + "FROM " + var2 + "WHERE attr = " + var3 + " " but that doesn't seem very generic and prone to a lot of errors.

Incase: I'm trying to build this test page using HTML & JSP.

You are correct. I would highly recommend PreparedStatements for this. Your first method is subject to SQL injection and all sorts of other problems. I also would think the columns would be pretty static or at least I would expect there to be a finite number of real statements you would need. — tjg184, Jul 05 '12 at 03:28
you can take a look at similar question: http://stackoverflow.com/questions/3135973/variable-column-names-using-prepared-statements — ria, Jul 05 '12 at 04:21

score 3 · Accepted Answer · answered Jul 05 '12 at 04:49

What you are doing with the ? is parameterizing the query. The query can only be parameterized for values not names of tables or columns.

Every time you run a query. The database has to create a query plan. If you are running the same query again and again, you can reduce this overhead by creating a PreparedStatement. The first execution of PreparedStatement will generate the query plan. The subsequent executions will reuse the same plan.

Same query here means, it is identical in all respects except values used in where clause, expressions etc.

If you change the Column or Table name or modify the structure of the query, then it is a different query and will require a different query plan. A PreparedStement is not useful in this case and you should stick to the hardcoded version you talked about. Because of this reason you will get an error if you try to parameterize Table or Column names in PreparedStement.

Having said that. It is not advisable to take such a generic approach for queries. If your queries are that simple, you can benefit from ORM tools. You would not have to maintain even a line of SQL. For complex queries you have an option of using ORM specific query language or JPQL or Native SQL. Look for JPA + Hibernate

score 0 · Answer 2 · answered Jul 05 '12 at 03:35

Your specific usage is not permitted by JDBC. You need to hard code the table name when creating the prepared statement. If you really do want to do that I suggest you use String concatenation to create the SQL statements and then create a PreparedStatement with parameters to handle the where part. In case you are wondering why bother with PreparedStatements in the specific solution, it's to avoid SQL injection.

score 0 · Answer 3 · answered Jul 05 '12 at 05:09

0

You can use PreparedStatement to achive your objective.

For example -

String query = "SELECT * FROM table Where attr = ?";
PreparedStatement pt = con.prepareStatement(query);
pt.setString(1, attribete);
pt.executeUpdate();

answered Jul 05 '12 at 05:09

Pramod Kumar

7,914
5
28
37

score 0 · Answer 4 · answered Jul 05 '12 at 05:28

There is no such direct provision in any of SQL packaged classes or others to replace table, column names along with query parameter values, in a query string, using a single method.

You require to depend on both PreparedStatement and any of String methods replace(...) and replaceFirst(...) to achieve your requirement.

String sql = "Select $1, $2 from $3 where $4=? and $5=?";
sql = sql.replaceFirst( "$1", "col1_name" );
sql = sql.replaceFirst( "$2", "col2_name" );
sql = sql.replaceFirst( "$3", "table_name" );
sql = sql.replaceFirst( "$4", "col4_name" );
sql = sql.replaceFirst( "$5", "col5_name" );
// .. and so on

PreparedStatement pst = con.prepareStatement( sql );
// use relevant set methods to set the query parametrs.
pst.setXXX( 1, value_for_first_query_parameter ); // from a variable or literal
pst.setXXX( 2, value_for_second_query_parameter); // from a variable or literal
// ... and so on

score -3 · Answer 5 · answered Jul 05 '12 at 03:55

If you are using JDBC, can try this

PreparedStatement statement = connection.prepareStatement("SELECT ? FROM ? WHERE ?=? ");

then

statement.setString(1, "column_name");
statement.setString(2, "table_name");
statement.setString(3, "column_name");
statement.setBigDecimal(4, 123);

If you are using other ORM like Hibernate or JPA, I believe there are also ways to do.

using java variable in sql statement

5 Answers5