Google
 

 

up previous next contents
Up: 4. Day 4: Databases, Previous: 4. Day 4: Databases, Next: 4.2 JUnit   Contents

Subsections

4.1 Databases and JDBC

4.1.1 Getting things set up

What you need to get started:
  • Java JDK (and JDBC).
  • JDBC driver for your database.
  • A database server (Oracle, Informix, DB2, Postgres, etc.).
  • Create the database and tables you want to use.

4.1.2 Connecting to the database

4.1.2.1 Load the driver

  • Class.forName("jdbc.DriverXYZ");
  • Class.forName("org.postgresql.Driver");

4.1.2.2 Create the connection

  • Connection conn = DriverManager.getConnection(url,"loginID", "password");
  • The URL:
    • jdbc:postgresql:database
    • jdbc:postgresql://host/database
    • jdbc:postgresql://host:port/database

4.1.3 Statements

  • A Statement object is used to send an SQL statement to the DBMS.
  • Create a Statement object and then execute it, using the proper execute method:
    • For SELECT statements use executeQuery.
    • For statements that create or modify tables use executeUpdate.
  • Example: 
      Statement stmt = conn.createStatement();
  String query = "SELECT username, password FROM user";
  ResultSet rs = stmt.executeQuery(query);
  while ( rs.next() ) 
  {
    String user = rs.getString("username");
    String password = rs.getString("password");
    System.out.println( "Username: " + user );
    System.out.println( "Password: " + password );
  }

4.1.4 getXXX methods

With ResultSet objects:
  • getByte
  • getShort
  • getInt
  • getLong
  • getFloat
  • getDouble
  • getBigDecimal
  • getBoolean
  • getString
  • getBytes
  • getDate
  • getTime
  • getTimestamp
  • getAsciiStream
  • getUnicodeStream
  • getBinaryStream
  • getObject

4.1.5 Updating the database

Use executeUpdate when using SQL UPDATE commands: 
  String update = "UPDATE user SET password='bar' WHERE user='foo'";
  stmt.executeUpdate(update);

4.1.6 PreparedStatements

  • Developers often use PreparedStatements because the syntax is easier.
  • Example: 
      PreparedStatement update = 
     conn.prepareStatement("UPDATE user SET password = ? WHERE user = ?");
  updateSales.setString(1, "bar");
  updateSales.setString(2, "foo");
  • Intended for, most useful, and high performance, inside of loops where many INSERT's or UPDATE's need to be done at one time.

4.1.7 A real method

The following method was copied from a production software application. Note that it uses a PreparedStatement for the syntactical ease of use that the PreparedStatement offers. 

  public String getPurchaserEmailAddress(int orderId)
  throws SQLException
  {
    Connection connection = null;
    try
    {
      String email = null;
      connection = ConnectionPool.getConnection();
      String query = "SELECT email FROM orders WHERE order_id = ?";
      PreparedStatement emailQuery = connection.prepareStatement(query);
      emailQuery.setInt(1, orderId);
      ResultSet rs = emailQuery.executeQuery();
      if ( rs.next() )
      {
        email = rs.getString(1);
      }
      return email;
    }
    finally
    {
      ConnectionPool.freeConnection(connection);
    }
  }