INSERT statement

The SQL statement that creates (populates) data in the database has the following format:

INSERT INTO table_name (column1,column2,column3,...)
   VALUES (value1,value2,value3,...);

When several tables records have to be added, it looks like this:

INSERT INTO table_name (column1,column2,column3,...)
 VALUES (value1,value2,value3,...), (value11,value21,value31,...), ...;

Before writing a program, let's test our INSERT statement:

It worked without an error and returned the number of inserted rows as 1, so we are going to create the following method:

void executeStatement(String sql){
  Connection conn = getConnection();
  try (conn; Statement st = conn.createStatement()) {
    st.execute(sql);
  } catch (SQLException ex) {
    ex.printStackTrace();
  }
}

We can execute the preceding method and insert another row:

executeStatement("insert into person (first_name, last_name, dob)" +
                             " values ('Bill', 'Grey', '1980-01-27')");

We will see the result of this and previous INSERT-statement execution in the next section, when we demonstrate SELECT-statement.

Meanwhile, we would like to discuss the most popular methods of the java.sql.Statement interface:

• boolean execute(String sql): It returns true if the executed statement returns data (as a java.sql.ResultSet object) that can be retrieved using the ResultSet getResultSet() method of the java.sql.Statement interface. It returns false if the executed statement does not return data (the SQL statement was probably updating or inserting some rows) and the subsequent call to the int getUpdateCount() method of the java.sql.Statement interface returns the number of the affected rows. For example, if we have added the print statements to our executeStatement() method, we would see the following results after inserting a row:

        void executeStatement(String sql){
          Connection conn = getConnection();
          try (conn; Statement st = conn.createStatement()) {
            System.out.println(st.execute(sql));      //prints: false
            System.out.println(st.getResultSet());    //prints: null
            System.out.println(st.getUpdateCount());  //prints: 1
          } catch (SQLException ex) {
            ex.printStackTrace();
          }
        }

• ResultSet executeQuery(String sql): It returns data as a java.sql.ResultSet object (the executed SQL statement is expected to be SELECT-statement). The same data can be also retrieved by the subsequent call to the ResultSet getResultSet() method of the java.sql.Statement interface. The int getUpdateCount() method of the java.sql.Statement interface returns -1. For example, if we change our executeStatement() method and use executeQuery(), the results of executeStatement("select first_name from person") would be:

        void executeStatement(String sql){
          Connection conn = getConnection();
          try (conn; Statement st = conn.createStatement()) {
             System.out.println(st.executeQuery(sql)); //prints: ResultSet
             System.out.println(st.getResultSet());    //prints: ResultSet
             System.out.println(st.getUpdateCount());  //prints: -1
          } catch (SQLException ex) {
             ex.printStackTrace();
          }
        }

• int executeUpdate(String sql): It returns the number of the affected rows (the executed SQL statement is expected to be UPDATE-statement). The same number returns the subsequent call to the int getUpdateCount() method of the java.sql.Statement  interface. The subsequent call to the ResultSet getResultSet() method of the java.sql.Statement interface returns null. For example, if we change our executeStatement() method and use executeUpdate(), the results of executeStatement("update person set first_name = 'Jim' where last_name = 'Adams'") would be:

        void executeStatement4(String sql){
          Connection conn = getConnection();
          try (conn; Statement st = conn.createStatement()) {
            System.out.println(st.executeUpdate(sql));//prints: 1
            System.out.println(st.getResultSet());    //prints: null
            System.out.println(st.getUpdateCount());  //prints: 1
          } catch (SQLException ex) {
            ex.printStackTrace();
          }
        }