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

Question

code:

String myTblN = fileN[0];

PreparedStatement creatTableStmt = 
    conn.prepareStatement("create table " + myTblN +" like stocktradtbl");
System.out.println(creatTableStmt);
creatTableStmt.execute();

error:

com.mysql.cj.jdbc.ClientPreparedStatement: create table 00002 like stocktradtbl java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 00002 like stocktradtbl' at line 1

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

00002 is not a valid identifier, since identifiers must start with a letter. — Andreas
– Andreas, Commented Feb 28, 2019 at 2:03

GMB · Accepted Answer · 2019-02-28 02:03:51Z

2

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");

answered Feb 28, 2019 at 2:03

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Scary Wombat · Accepted Answer · 2019-02-28 02:05:27Z

2

Your values need to be quoted with backticks or double-quotes

"create table `" + myTblN +"` like `stocktradtbl`"

Also as per @kartik

Also, you may want to add the reason that 00002 consists only of numbers, and mysql allows number-only identifiers only if they are quoted.

edited Feb 28, 2019 at 2:05

answered Feb 28, 2019 at 1:59

Scary Wombat

44.9k7 gold badges38 silver badges67 bronze badges

2 Comments

Kartik Over a year ago

I think it should be quoted with backticks, not apostrophes

Kartik Over a year ago

Also, you may want to add the reason that 00002 consists only of numbers, and mysql allows number-only identifiers only if they are quoted.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related