Postgresql with java: query produced no result aft

问题:

I write a little program to admin my video collection.

/*
insert new data set into the table
*/
int next = 0;
rs = st.executeQuery("Select max(category_id) from category;");
if (rs.next()) {
    next = rs.getInt(1) + 1;
    System.out.println(next);
}
String query = "INSERT INTO category VALUES (" + next + ", 'Mystics', now());";
rs = st.executeQuery(query);
//on this place is the exception thrown
// this will not execute anymore
rs = st.executeQuery("DELETE FROM category WHERE name = 'Mystics';"); 

The program can select on tables, make joins but insert make trouble. I try to insert some new data in my table (see Java-code). After the second test the output show me that the data was inserted. But after Insert was an exception thrown. 1 & 2 are the tests from yesterday and today. (3) was inserted but not selected yet.

1   Mystics 2015-07-05
2   Mystics 2015-07-06
3
org.postgresql.util.PSQLException: query produced no result.
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:287)
at postgre_java.Zetcode.main(Zetcode.java:55)

do you have some advises for me?

回答1:

Do not manipulate data with read statements! If you want to insert, update, delete data in db use

Statement stmt = conn.createStatement();
stmt.executeUpdate(SQL);

executeQuery returns resultset, but all that INSERT, UPDATE, DELETE can return is number of affected rows and that is what executeUpdate is returning.

And never, never, never*100 use string concatenation in SQL use Prepared statements!

回答2:

In Java, you use executeQuery for a SELECT statement or some other statement which returns something. If you want to execute an INSERT, UPDATE or DELETE without returning something, you should use executeUpdate().

回答3:

Statement#executeUpdate() is meant for that purpose

String query = "INSERT INTO category VALUES (" + next + ", 'Mystics', now());";
int noOfRows= st.executeQuery(query)

but it doesnt return a ResultSet , rather the no of rows affected that you could store into an Integer

Also your is highly vulnerable to Sql injection , try using the PreparedStatements to safeguard your code