How do you determine if an insert or update was successful using Java and MySQL?

Question

I am using Java to connect to a MySQL database. I am trying to insert or update data into the database.

Even though I am quite sure the insert was successful, it returns false.

According to the "execute" API, the return value is "true if the first result is a ResultSet object; false if it is an update count or there are no results".

How can I determine whether or not my insert or update was successful?

    public boolean insertSelections(String selection, String name){
        String sql ="INSERT INTO WORKREPORT VALUES (?,?,?,?,?)";
        boolean action = false;
        try {
            PreparedStatement stmt = conn.prepareStatement(sql);
            SimpleDateFormat dateFormat =  new java.text.SimpleDateFormat("yyyy：MM：dd hh:mm:ss");
            String formatDate = dateFormat.format(new java.util.Date(System.currentTimeMillis())); 
            java.util.Date mDate = dateFormat.parse(formatDate);
            java.sql.Timestamp timeStamp = new java.sql.Timestamp(System.currentTimeMillis());
//          Date time= new Date(mDate.getTime());

            stmt.setInt(1, Integer.parseInt(getNumberByName(name).trim()));
            stmt.setString(2, name);
//          stmt.setDate(3, time);
            stmt.setTimestamp(3, timeStamp);
            stmt.setString(4, selection);
            stmt.setString(5, "N/A");
            action = stmt.execute();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }   catch (ParseException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }   
        return action;
    }

Try with `stmt.executeUpdate();` for kind of `Update op(INSERT, UPDATE, DELETE)`. — Wundwin Born, Jun 24 '14 at 04:26

Elliott Frisch · Accepted Answer · 2014-06-24T04:36:31.990

Since you are using PreparedStatement you can call executeUpdate() -

 int count = stmt.executeUpdate();
 action = (count > 0); // <-- something like this.

From the Javadoc (Returns) link above, emphasis added,

either (1) the row count for SQL Data Manipulation Language (DML) statements or (2) 0 for SQL statements that return nothing.

If you want to insert a large number of entries, I would prefer addBatch() and executeBatch().

score 21 · Answer 2 · edited Jan 24 '19 at 10:50

First of all this you should know :

boolean execute() Executes the SQL statement in this PreparedStatement object, which may be any kind of SQL statement.

ResultSet executeQuery() Executes the SQL query in this PreparedStatement object and returns the ResultSet object generated by the query.

int executeUpdate() Executes the SQL statement in this PreparedStatement object, which must be an SQL INSERT, UPDATE or DELETE statement; or an SQL statement that returns nothing, such as a DDL statement.

        int i = stmt.executeUpdate();
        if (i > 0) {
            System.out.println("success");
        } else {
            System.out.println("stuck somewhere");
        }

Try this and check it out whether insert is happening or not

score 0 · Answer 3 · answered Jun 24 '14 at 04:25

0

If you don't get a exception I think query is went ok. Or, you might be able to use executeUpdate() (http://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html#executeUpdate() )

You can do a select count(*) do validate number of records if you want.

answered Jun 24 '14 at 04:25

Viraj

5,083
6
35
76

I'm currently running a delete query which should remove 1 record in my test. The query does pass, however it does return 0 changed records. Therefor this isn't correct. SELECT count(*) would work, but is not required. – Mathieu Brouwers Nov 15 '17 at 14:42
1

@Mathieu Brouwers It is not suitable for update and delete statement, but for insert statement it should work. If you don't get a exception, that means insert query went OK. Please, let me know if I am missing anything about insert queries. – S Kumar Jul 04 '18 at 11:06

score 0 · Answer 4 · answered Jun 29 '16 at 07:00

0

Try this, whether you want to know whether the data is inserted or not , if the record is inserted it return true or else false.

  if(action > 0){
      return true;
    }else{
      return false;
      }

answered Jun 29 '16 at 07:00

Pradeep Ramisetti

77
1
1
7

How do you determine if an insert or update was successful using Java and MySQL?

4 Answers4

Linked