Course Module 4. Working with databases - Lecture: Executing queries

Introduction to Statements

All SQL queries can be conditionally divided into two groups:

Retrieving data − These include the SELECT statement .
Modifying data − These include INSERT , UPDATE , and DELETE statements .

For the first group, the Statement interface method already familiar to us is used - executeQuery() . In principle, this method is quite enough to get started. It covers a very large percentage of queries that you will need to use in a real job.

We'll get into more options later, but for now, I advise you to remember - if you just want to get data from a table, then the executeQuery () method in the vast majority of cases will be the right choice.

For the second group of requests, you need to use another method of the Statement interface - executeUpdate() . Unlike the executeQuery() method , which returns a ResultSet, this method returns an integer that tells you how many rows in the table were changed when your query was executed .

For example, you can delete all rows with the DELETE FROM employee statement (so be very careful). In this case, the executeUpdate() method will return the number of deleted rows. In some situations, knowing the number of changed rows can be useful for building algorithms for working with data.

In principle, we can finish this question - we have already seen the main thing. For data fetching - executeQuery() . To change the data - executeUpdate() .

Get the number of users in the user table using the executeQuery() method :


	ResultSet results = statement.executeQuery("SELECT Count(*) FROM user");
	results.next();
    int count = results.getInt(1);

executeUpdate()

The executeUpdate() method is used when your query changes something in a table. It could be anything:

UPDATE statement
INSERT statement
DELETE statement
CALL PROCEDURE statement

This method returns the number of changed (or deleted) rows.

Let's write a request that will increase the salary of all our employees by 1000 rubles.


   int rowsCount = statement.executeUpdate("UPDATE  employee SET salary = salary+1000");

After I ran this code, it returned the number 6 on my table. My table had 6 rows. So all the lines have been changed.

execute()

Sometimes situations may arise in your life when you don’t know exactly what request you have to execute - a selection or a data change. In this case, the creators of JDBC added another generic method to it - execute() .

This method can be used in place of the executeQuery() and executeUpdate() methods. Do you remember how these methods differed? Right. Result type. Therefore, the creators of JDBC came up with such a solution.

The execute() method returns boolean. If this value is true , then a data fetch request was in progress, and you need to call the getResultSet() method to get the data. Example:


    boolean hasResults = statement.execute("SELECT Count(*) FROM user");
    if ( hasResults ) {
        	ResultSet results =  statement.getResultSet();
        	results.next();
        	int count = results.getInt(1);
 	}

If this value is false , then a data change request was in progress, and you need to call the getUpdateCount() method to get the number of rows changed. Example:


    boolean hasResults = statement.execute("UPDATE  employee SET salary = salary+1000");
    if ( !hasResults ) {
      	int count = statement.getUpdateCount();
 	}

Let's write a method that displays the result of a query:


	public void executeAndPrintSQLQuery(String sqlQuery) {
 
        boolean hasResults = statement.execute(sqlQuery);
        if ( hasResults ) {
 	          ResultSet results =  statement.getResultSet();
           	System.out.println(“Your request lines below: ”);
 
           	while (results.next()) {
 	                 Integer id = results.getInt(1);
     	             String name = results.getString(2);
                      System.out.println(results.getRow() + ". " + id + "\t"+ name);
  	        }
       }
 	  else {
            int count = statement.getUpdateCount();
        	System.out.println(“Number of name strings: ” + count);
  	}
}

This is how the execute() method is usually used in practice.