The following SQL statement is executed without any issue in MySQL Workbench environment.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE OR REPLACE VIEW Users_Pilot1 | |
AS SELECT * FROM users | |
WHERE u_id <= 25 and u_issubmitted = 1; |
However, if you try to execute the same statement in Java
You will encounter this error
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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(); | |
} | |
} | |
} |
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.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
createViewStatement = "CREATE OR REPLACE VIEW Users_Pilot1 " | |
+ "AS SELECT * FROM users " | |
+ "where users.u_id <= 25 and users.u_issubmitted = 1"; | |
executeCreateViewStatement(createViewStatement); |
No comments:
Post a Comment