JDBC error caused by the execution order of WHERE and SELECT clauses

The following SQL statement is executed without any issue in MySQL Workbench environment.

	CREATE OR REPLACE VIEW Users_Pilot1
	AS SELECT * FROM users
	WHERE u_id <= 25 and u_issubmitted = 1;

view raw no_good_sql.sql hosted with ❤ by GitHub

However, if you try to execute the same statement in Java

	createViewStatement = "CREATE OR REPLACE VIEW Users_Pilot1 "
	+ "AS SELECT * FROM users "
	+ "WHERE u_id <= 25 and u_issubmitted = 1";
	executeCreateViewStatement(createViewStatement);
	private void executeCreateViewStatement(String createViewStatement) {
	if (createViewStatement == null
	|| createViewStatement.equalsIgnoreCase("")) {
	return;
	}
	String url = Constants.DB_URL;
	Connection con = null;
	PreparedStatement ps = null;
	try {
	Class.forName(Constants.JDBC_DRIVER);
	con = DriverManager.getConnection(url, Constants.DB_USER,
	Constants.DB_PASSWORD);
	ps = con.prepareStatement(createViewStatement);
	ps.execute();
	} catch (ClassNotFoundException | SQLException e) {
	e.printStackTrace();
	} finally {
	try {
	if (ps != null) {
	ps.close();
	}
	if (con != null) {
	con.close();
	}
	} catch (Exception e) {
	e.printStackTrace();
	}
	}
	}

view raw no_good_sql_jdbc.java hosted with ❤ by GitHub

You will encounter this error

Unknown column 'u_issubmitted' in 'where clause'

The reason is that the WHERE clause is executed before the execution of the SELECT clause, thus the attribute 'u_issubmitted' of the users table is unseen from the WHERE clause at the time of its execution. The error does not appear in the MySQL Workbench, probably because this SQL IDE has some background considerations that keep track of the attributes of every table.

The correct and safe way to do that in Java is as following with the explicit pointers to the columns (attributes) from the users table.

	createViewStatement = "CREATE OR REPLACE VIEW Users_Pilot1 "
	+ "AS SELECT * FROM users "
	+ "where users.u_id <= 25 and users.u_issubmitted = 1";
	executeCreateViewStatement(createViewStatement);

view raw good_sql_jdbc.java hosted with ❤ by GitHub