Click here to Skip to main content
15,878,945 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
The very same sql statement works fine in MySQL Workbench but fails in Java program. All I've done was setting a breakpoint before the execution of this statement in Java and paste the same statement ("select * from administrator where AdministratorID = 'Admin'") from Java to the Workbench, where showed ONE record. I surrounded the invoking with try-catch, it turned out successfully called but returned nothing (resultset.getRow() returns 0). Here is the Java code that executes the query:
Java
public ResultSet executeQuery(String tableName, String condition) {
		String sql = "select * from " + tableName + " where " + condition;
		ResultSet rs = null;
		Connection conn = null;
		try {
			conn = getConn();
			Statement statement = conn.createStatement();
			rs = statement.executeQuery(sql);
			statement.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rs;
	}

And the getConn() function:
Java
public Connection getConn() {
		Connection conn = null;
		try {
			Class.forName(name); // 指定连接类型
			String url = "jdbc:mysql://" + host + ":" + port + "/" + dbName;
			conn = DriverManager.getConnection(url, user, password); // 获取连接
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}

where name = "com.mysql.jdbc.Driver", host = "127.0.0.1", port = "3306". dbName, user and password were provided correctly.
Can anyone help tell me what went wrong?
Posted
Comments
Richard Deeming 28-Sep-15 9:28am    
Your code is vulnerable to SQL Injection[^].

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.
Wu Jiecheng 28-Sep-15 10:24am    
I tried PreparedStatement way, but got the same effect - no exceptions and no result row neither.

You close the connection before anything was read. Just for test, try reading data from the resultset right after the line rs = statement.executeQuery(sql); in your executeQuery method.

Just an advice on good programming style. Don't have your method getConn return null if creating a connection failed. Simply retrow the exception. This makes it so much more clear when and why it fails from where it is called.

And of course... beware of sql injection!

Good luck!
 
Share this answer
 
Comments
Wu Jiecheng 29-Sep-15 8:51am    
Googled "sql injection" and shocked! I 've never been aware of sql inject attack! Thanks for warning me! I tried getting the expression rs.getRow() right after the executeQuery(sql) statement and got the value 0. Is there anything wrong with my functiong getConn()?
E.F. Nijboer 29-Sep-15 12:46pm    
You need to use a while that will get the next record each time using rs.next(). Check out this link with the exact example code you are looking for. :-)

https://docs.oracle.com/javase/tutorial/jdbc/basics/processingsqlstatements.html
The resultset cursor is positioned before the first row. By using next() you move the cursor to next row. Post the final row (ie- if the cursor is positioned after last row) next() will return false
More details can be found here :
https://docs.oracle.com/javase/tutorial/jdbc/basics/retrieving.html[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900