• You will use the classes in the java.sql package.

• You will follow these basic steps:
  1. Connect to the database - use the Connection class
  2. Create the query - use the Statement or PreparedStatement class.
  3. Execute the query - use the query statement and call executeQuery() or executeUpdateQuery().
  4. Process the query results - store the results from executing the query in an object of class ResultSet and iterate through it to access each tuple (row of data in DB-speak).
  5. Close the DB resources.

Common JDBC Database URLs

• When connecting to the database, you will specify the database URL: "protocol:subprotocol:dbname"

  RDBM System	URL Format
  MySql	"jdbc:mysql://hostname:portNumber/databaseName"
  Oracle	"jdbc:oracle:thin://@hostname:portNumber/databaseName"
  DB2	"jdbc:db2://hostname:portNumber/databaseName"
  SQL Server	"jdbc:sqlserver://hostname:portNumber;databaseName=>i?dataBaseNumber"
  Java DB/Apache Derby
  Embedded	"jdbc:derby:databaseName"
  Network	"jdbc:derby://hostname:portNumber/databaseName"

Code Snippet

String URL = "dbprotocol";

// This uses the try-with-resources syntax, so cleanup is automatic

try (Connect dbConnection = DriverManager.getConnection(DBURL, "userame", "password");
     Statement stmnt = dbConnection.createStatement();
     ResultSet results = stmnt.executeQuery("Select colName, colName from tableName");
    )
{
    ResultSetMetaData metaData;
    int numCols;

    metaData = results.getMetaData();
    numCols = metaData.getColumnCount();

    double amount;
    String name;
    Object obj;

    while (results.next())
    {
       d = results.getDouble(1);            // if the first column is guaranteed to be a double
       d = results.getDouble("Amount");     // You could hard-code the column name, or use the metaData

       name = results.getString(2);         // if the second column is guaranteed to be a double
       name = results.getString("Name");    // You could hard-code the column name, or use the metaData

       obj = results.getObject(3);          // Gets the 3rd colum, you figure out the type
       obj = results.getObject("ColName");  // Gets ColName, you  figure out the the type

    }
} // Note: resources are closed for you at the end of the try-block
catch (SQLException e)
{
}

• Note what is happening with the numbers - they do NOT start at 0. A ResultSet uses a column number, not an index.

• I strongly recommend that if you loop, do NOT use i.

• Each Statement can only have a single ResultSet, so if you need to process more than one query at a time, you must use separate Statement objects.

• To manage the resources yoursef (rather than using the try-with-resources), you will need to close them yourself:

  results.close();
  stmnt.close();
  dbConnection.close();