如何使用preparedStatement以及Java和MySQL(使用J连接器)来创建表格?

huangapple 未分类评论41阅读模式
英文:

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?

huangapple
  • 本文由 发表于 2020年7月24日 02:18:23
  • 转载请务必保留本文链接:https://java.coder-hub.com/63060682.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定