SELECT-statement

The SELECT-statement has the following format:

SELECT column_name, column_name
FROM table_name WHERE some_column = some_value;

When all the columns have to be selected, it looks like this:

SELECT * FROM table_name WHERE some_column=some_value;

Here is a more general definition of WHERE-clause:

WHERE column_name operator value
Operator:
   =   Equal
   <>  Not equal. In some versions of SQL, !=
   >   Greater than
   <   Less than
   >=  Greater than or equal
   <=  Less than or equal
   IN  Specifies multiple possible values for a column
   LIKE  Specifies the search pattern
   BETWEEN  Specifies the inclusive range of vlaues in a column

The column_name operator value constructs can be combined using the AND and OR logical operator and grouped by brackets, ( ).

In the previous statement, we have executed a select first_name from person SELECT-statement that returns all the first names recorded in the person table. Let's now execute it again and print out the results:

Connection conn = getConnection();
try (conn; Statement st = conn.createStatement()) {
  ResultSet rs = st.executeQuery("select first_name from person");
  while (rs.next()){
    System.out.print(rs.getString(1) + " "); //prints: Jim Bill
  }
} catch (SQLException ex) {
  ex.printStackTrace();
}

The getString(int position) method of the ResultSet interface extracts the String value from position 1 (the first in the list of columns in the SELECT-statement). There are similar getters for all primitive types, such as getInt() and getByte().

It is also possible to extract the value from the ResultSet object by column name. In our case, it would be getString("first_name"). It is especially useful when the SELECT-statement looks like the following:

select * from person;

But bear in mind that extracting values from the ResultSet object by the column name is less efficient. The difference in performance is very small and becomes important only when the operation happens many times. Only the actual measuring and testing can tell if the difference is significant for your application or not. Extracting values by column name is especially attractive because it provides better code readability, which pays well in a long time during the application maintenance.

There are many other useful methods in the ResultSet interface. If your application reads data from a database, we highly recommend you read the documentation of the SELECT-statement and the ResultSet interface.