Java MySQL PreparedStatement does not work as intended

[Yosuf Ibrahim]

Hello guys,

I was using PreparedStatement with a for loop to create a method that will make a table for dynamically without me having to type in the whole thing but the result as good not the intended, apparently the setString method places the string within apostrophes ruining the statement, is there any other way?

PreparedStatement preparedStatement = conn.prepareStatement(
					"CREATE TABLE ? (? ? not Null, ? ?,? ?,? ?,? ?,? ?,? ?,? ?,? ?,? ?, PRIMARY KEY (?))");

			preparedStatement.setString(1, TablesNames.PRODUCT.getName());
			int i = 2;
			for (SQL_Columns s: SQL_Columns.values()) {
				preparedStatement.setString(i, s.name);
				i++;
				preparedStatement.setString(i, s.type);
				i++;
			}
			preparedStatement.setString(i, SQL_Columns.PRO_CODE.name);
			System.out.println(preparedStatement);
			
			preparedStatement.executeUpdate();

Result:

CREATE TABLE 'product' ('pro_code' 'VARCHAR(6)' not Null, 'barcode' 'BIGINT','name' 'VARCHAR(255)','categ' 'INTEGER','brand' 'INTEGER','desc' 'TEXT','unit_type' 'SMALLINT','upr' 'INTEGER','usp' 'DECIMAL(10,2)','wsp' 'DECIMAL(10,2)', PRIMARY KEY ('pro_code'))

Intended result:

CREATE TABLE product (pro_code VARCHAR(6) not Null, barcode BIGINT, name VARCHAR(255), categ INTEGER, brand................

thank you in advance

[Tim Holloway]

Hmmm. Yes, I can see why it would do that.

You actually can (and sometimes must) quote column names in MySQL, but not with ordinary single quotes. You'd use "back-tick" quotes, instead.

I actually don't think that you can do this in a PreparedStatement. You'd need a standard Statement, instead and a LOT of caution to ensure that nobody can stuff SQL injection into your column name/type values.

Better yet, if you can, define a prototype table in your database and clone it as needed.

If you have an indeterminate set of columns, then perhaps what you really need is a NoSQL DBMS such as MongoDB or Node4J.

[Yosuf Ibrahim]

Fixed it this way

	public static boolean sql_createTable() {
		try {
			Connection conn = MySQLAccess.getConnect();
			
			String stmnt = "CREATE TABLE " + TablesNames.PRODUCT.getName() + "(";
			for (SQL_Columns s: SQL_Columns.values()) {
				stmnt = String.format("%s %s %s,", stmnt, s.name, s.type); 
			}
			stmnt =String.format("%s PRIMARY KEY (%s))", stmnt, SQL_Columns.PRO_CODE.name);
			
			PreparedStatement preparedStatement = conn.prepareStatement(stmnt);
			preparedStatement.executeUpdate();
			
			return true;
		} catch (Exception e) {
			ExceptionDialog.createDialog(e);
			return false;
		}
	}

i could not find a better way, if there is please let me know

[Paul Clapham]

Usually people don't create enough SQL tables to make it worthwhile writing code to generate the tables. For example in the time it has taken you to work on this code, you could have manually typed in the commands to create a hundred or so tables, or maybe more. That's why it isn't a priority for the writers of JDBC drivers to make them work for CREATE TABLE commands, I suppose.

So I agree with Tim that there may be better ways to achieve what you want to do. Where does the need to create a lot of tables quickly come from?

[Stephan van Hulst]

There really is no point to using PreparedStatement for executing static commands.

Just put the entire table creation command in a text file, and add the text file as an embedded resource to your application:

static void createFooTable(DataSource dataSource) throws SQLException {
  var sql = loadScript("createFooTable.sql");

  try (
    var connection = dataSource.getConnection();
    var statement = connection.createStatement();
  ) {
    statement.execute(sql);
  }
}

private static String loadScript(String resourceName) {
  try (
    var stream = DatabaseCommands.class.getResourceAsStream(resourceName);
    var reader = new BufferedReader(new InputStreamReader(stream, StandardCharsets.UTF_8));
  ) {
    return reader.lines().collect(joining("\n"));
  }
}

[Tim Holloway]

Stephan van Hulst wrote:There really is no point to using PreparedStatement for executing static commands.

Quite so. Although I did wonder if some JDBC drivers might be capable of detecting injected SQL on static text when preparing statements. I don't think there's a formal rule on that one way or the other.

Yusuf's solution is fairly tidy. But unless the Prepare processor can detect such malice, it wouldn't be better than a plain statement.

[Yosuf Ibrahim]

Hey guys,

Really thank you for all the support I am still learning and this is a personal project I am doing and hopefully would be a great learning experience. The idea is a I am making an Store Inventory management system for some of my family members, after I am done and I send them the application I will walk them through downloading MySQL and the java application I made and asking them to create the tables would be a hassle, that is why I have been trying to have the application create the Tables itself.

The reason I would rather not use the text file is because I am learning and I keep on editing the tables and its columns, so far I have deleted and remade the tables like 50 times in the past 48 hours alone, that's why I came up with having Enums that contain the table names and their columns as shown below and this allows me to automatically update the tables incase I made a spontaneous change as I usually do.

public enum TablesNames {
	
	PRODUCT ("product"),
	PURCHASES ("purchases"),
	CUSTOMERS ("customers"),
	SALES ("sales");

//Product column names
public enum TablesNames {
	
	PRODUCT ("product"),
	PURCHASES ("purchases"),
	CUSTOMERS ("customers"),
	SALES ("sales");

I am probably wrong in what I am doing, but it is what I do know how to do, so if there is a better method please let me know

Thank you again guys for your advice and help that I have received so far and that I am still going to receive, you guys have no idea how much your help means to me and others who are lost and guided to the light by you

[Tim Holloway]

Actually, this is a common issue and Spring Boot, for example, has a convention where you can create and pre-load database tables from resource file definitions that are made part of the deployable module.

And Spring JPA has the ability to create/mutate tables at application startup.

If you're not using either of those, you could simply create a resource file(s) that would go into the execution classpath and have a format something like this:

# Sample resource file to define database schema

db.create.users = "CREATE TABLE users (id numeric primary key, first_name text, last_name text)"
db.create.inventory_items = "CREATE TABLE inventory_items (id numeric primary key, sku text, description text, size numeric, size_units text)"

And so forth. I hope I remember how MySQL CREATE TABLE works!

Now you can load the resource and iterate through its keys, taking each value in turn and creating and executing the value as a SQL Statement (doesn't need to be PreparedStatement since there's no risk of inserting Bad Stuff).