JDBC 3: Queries

Fetch ResultSet

Once you have a connection, you can now fetch data from the database. Add the following method to the DBase class.

  • The method creates a statement attached to the connection con.
  • The statement executes a simple MySQL query. The query results are stored in a ResultSet rs.
  • A pointer is set to before the first record in the ResultSet.
  • A while statement is used to traverse the set.
  • Finally, the connection is closed.
public static void printPupils() {

    Statement statement;
    makeConnection();
    try {
        statement = con.createStatement();
        ResultSet rs = statement.executeQuery("SELECT * FROM Student");

        while (rs.next()) {
        System.out.println("Forename: " + rs.getString("Forename") 
                  + " Surname: " + rs.getString("Surname")
                  + " Gender: " + rs.getString("Gender") 
                  + " Form: " + rs.getString("Form"));
        }

        con.close();

    } catch (SQLException ex) {
        System.err.println(ex);
    }
}
package dbase2016;

/**
 *
 * @author ajb
 */
public class DBase2016 {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {

    DBase.printPupils();
    }

}

Add a record using a PreparedStatement

Note that we use executeQuery() to retrieve data and executeUpdate() to update the database.  Here we’ve hard coded the student we’re adding.   A later section will show how to add any student.

public static void addStudent() {

       makeConnection();
       try {
       PreparedStatement prep 
           = con.prepareStatement("INSERT INTO Student
        (Forename, Surname, Gender, Form) 
           VALUES (?,?,?,?)");
       prep.setString(1, "Last");
       prep.setString(2, "James");
       prep.setString(3, "M");
       prep.setString(4, "9C");

       prep.executeUpdate();

       con.close();

       } catch (SQLException ex) {
       System.err.println(ex);
       }

   }
package dbase2016;

/**
 *
 * @author ajb
 */
public class DBase2016 {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
    DBase.addStudent();
    DBase.printPupils();
    }

}

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.