How to Use a Tablename Variable for a Java Prepared Statement Insert

How to use a tablename variable for a java prepared statement insert

You can't. You need to contruct the sql with string concatenation/placeholder with String.format. prepared statement is for the column values not for table name.

Using Prepared Statements to set Table Name

A table name can't be used as a parameter. It must be hard coded. So you can do something like:

private String query1 = "SELECT plantID, edrman, plant, vaxnode FROM [" + reportDate + "?]";

How to pass table name to a Prepared Statement in a SELECT COUNT query?

You can only bind values in a PreparedStatement, not syntactic elements or object names (in this case, the table name). You'll have to resort to string manipulation:

final String query = String.format("SELECT COUNT(*) FROM %s", tablename);
final PreparedStatement preparedStatement = connection.prepareStatement(query);
final ResultSet resultSet = preparedStatement.executeQuery();

Note that there are no placeholders in this query, so it's questionable whether there's really any advantage in using a PreparedStatement as opposed to a plain old Statement.

Safe way to use table name as parameter in JDBC query

I would try to solve the design problem, so you don't have to set the table name dynamically. If this is not possible, I would go for a design where you manage a list of available tables and users pick one from there, BY ID, so you can retrieve the real table name from the chosen id and replace the table name placeholder with it, avoiding any chance of sql injection in the table name replacement.

Get Table Name from prepared statement

We can get the table name from the following:

ResultSetMetaData resultSetMetaData = preparedStatement.getMetaData();        
System.out.println("Table name " + resultSetMetaData.getTableName(1)); 
//1 here is the first column of the table for which the operation is made.

Trying to figure out how can we get the operation performed.

We can manipulate the logic to see if it's select or insert or update and send notification with the operation and the retrieved table name from above step.

how to use value of variable in java as table name of mysql？

check the manual that corresponds to your MySQL server version for the right syntax to use near 00002 like stocktradtbl' at line 1

It looks like you are trying to create a table whose name contains only digits. AS explained in the documentation, MySQL by default does not allow digits at the beginning of the table name.

One quick solution would be to add a fixed alphabetic character at the beginning of the table name :

PreparedStatement creatTableStmt = 
    conn.prepareStatement("create table t" + myTblN +" like stocktradtbl");

Or you need to quote the identifier (but then be prepare to quote the table name in every subsequent query, which can be painful):

PreparedStatement creatTableStmt = 
    conn.prepareStatement("create table `" + myTblN +"` like stocktradtbl");

java sql statement inserting variable

You need a space before "values (?,?,?,?,?,?,?,?,?)")?

Have you tried with " values (?,?,?,?,?,?,?,?,?)")?

EDIT: Can you verify the value of sql is "insert into db.table1 values (?,?,?,?,?,?,?,?,?)". I don't think it is the case at all.

String table_name = "table1";
String sql = "insert into db."+table_name +"values (?,?,?,?,?,?,?,?,?)";
System.out.println(sql);
preparedStatement = connect.prepareStatement(sql);  

---

Related Topics