英文:
How to use preparedStatement to create a table with java and mysql (j-connector)?
问题
I want use PreparedStatement to create a mysql database table and I want take table name by a String. The String can have some aphostrophe, so I tried to use this code:
Connection cn = DriverManager.getConnection("jdbc:mysql://localhost:3306/chatclient?user=root&password=");
String sql = "CREATE TABLE chatclient.? ( username VARCHAR(255) NOT NULL , msg TEXT NOT NULL ) ENGINE = InnoDB;";
PreparedStatement ps = cn.prepareStatement(sql);
ps.setString(1, "nametable");
ps.executeUpdate();
That are the errors:
Exception in thread "main" java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'tablename' ( username VARCHAR(255) NOT NULL , msg TEXT NOT NULL ) ENGINE = ' at line 1
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:370)
at socket.prova.main(prova.java:27)
But not work, I tried without "?" and without setString:
Connection cn = DriverManager.getConnection("jdbc:mysql://localhost:3306/chatclient?user=root&password=");
String sql = "CREATE TABLE chatclient.nametable ( username VARCHAR(255) NOT NULL , msg TEXT NOT NULL ) ENGINE = InnoDB;";
PreparedStatement ps = cn.prepareStatement(sql);
ps.executeUpdate();
This code work, I think the mistake is that when I use it? with ps.setString() set the text delimited by single quotes (') such as:
String sql = "CREATE TABLE chatclient.? etc.";
PreparedStatement ps = cn.prepareStatement(sql);
ps.setString("tablename");
ps.executeUpdate();
The ps.setString() method change the String to "CREATE TABLE chatclient.'tablename' etc."
Does anyone know how to make ps.setString automatically avoid delimitation (with single quotes ')? Or does anyone know how to solve it differently?
英文:
I want use PreparedStatement to create a mysql database table and I want take table name by a String. The String can have some aphostrophe, so I tried to use this code:
Connection cn=DriverManager.getConnection("jdbc:mysql://localhost:3306/chatclient?user=root&password=");
String sql="CREATE TABLE chatclient.? ( username VARCHAR(255) NOT NULL , msg TEXT NOT NULL ) ENGINE = InnoDB;";
PreparedStatement ps=cn.prepareStatement(sql);
ps.setString(1,"nametable");
ps.executeUpdate();
That are the errors:
Exception in thread "main" java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''tablename' ( username VARCHAR(255) NOT NULL , msg TEXT NOT NULL ) ENGINE = ' at line 1
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:370)
at socket.prova.main(prova.java:27)
But not work, I tried without "?" and without setString:
Connection cn=DriverManager.getConnection("jdbc:mysql://localhost:3306/chatclient?user=root&password=");
String sql="CREATE TABLE chatclient.nametable ( username VARCHAR(255) NOT NULL , msg TEXT NOT NULL ) ENGINE = InnoDB;";
PreparedStatement ps=cn.prepareStatement(sql);
ps.executeUpdate();
This code work, I think the mistake is that when I use it? with ps.setString () set the text delimited by ' ' such as:
String sql="CREATE TABLE chatclient.? etc."
PreparedStatement ps=cn.prepareStatement(sql);
ps.setString("tablename");
ps.executeUpdate();
The ps.setString() method change the String to "CREATE TABLE chatclient.'tablename' etc."
Does anyone know how to make ps.setString automatically avoid delimitation (with ')?
Or does anyone know how to solve it differently?
专注分享java语言的经验与见解,让所有开发者获益!
评论