Postgresql with java: query produced no result after insert

Question

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?

Use [prepared statements](http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html). — user2864740, Jul 06 '15 at 05:44

elrado · Accepted Answer · 2015-07-06T05:51:57.887

2

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!

edited Jul 06 '15 at 05:51

answered Jul 06 '15 at 05:46

elrado

4,960
1
17
15

score 0 · Answer 2 · answered Jul 06 '15 at 05:44

0

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().

answered Jul 06 '15 at 05:44

Patrick

29,357
6
62
90

score 0 · Answer 3 · answered Jul 06 '15 at 05:47

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

Postgresql with java: query produced no result after insert

3 Answers3

Linked