Databases

Primary key is unique value (id) in a column. The combination of columns forming an unqiue ID is called Alternative key. The column of a table referring to the key of another table is called reference key. We can display the structure of a database with a strokendiagram.

  • (p)rimary key: two-sided arrow above column
  • (a)lternative key: two-sided arrow above column(s)
  • name of column that references to different table can best get value of reference table name.

Code for use of different JDBC Drivers is almost the same. To connect to it, URL has t be known. Connection protocol is jdbc.

Interfaces of the java.sql package:

  • Driver: JDBC driver
  • Connection: the connection
  • PreparedStatement: the SQL
  • ResultSet: a result table
  • DriverManager: manager of one or more JDBC drivers

Database specific settings should be defined as ┬┤static final┬┤ variables in a class called DBConst.

Connect to a database

  1. Start with Driver registration: Class.forName(DBConst.drivername)
  2. Get connection with db: DriverManager.getConnection()
  3. Log problems: DriverManager.setLogWriter(new PrintWriter(System.out))

Alternatively one can connect to database using a DataSource object which requires the database to be registered at a JNDI naming service.

Connection con = null;
    try {
    // log errors  
    DriverManager.setLogWriter(new PrintWriter(System.out));
    // get driver class
    Class.forName(DBConst.DRIVERNAAM);
    // make connection
    con = DriverManager.getConnection(DBConst.URL, DBConst.GEBRUIKERSNAAM, DBConst.WACHTWOORD);
    } catch (ClassNotFoundException e) {
     System.out.println("Could not find driver with name " + DBConst.DRIVERNAAM); 
    } catch (SQLException e) {
      System.out.println(e.getMessage());
}

SELECT and UPDATE

Use PreparedStatement is more efficient than use of Statement as later needs to be recompiled with every new query.

SELECT works with the executeQuery method call.

String sql = "SELECT artist FROM Track WHERE CD = ?";
PreparedStatement prepStd = con.preparedStatement(sql);
sql.setString(1, 'Help!');
ResultSet res = prepStd.executeQuery();
while (res.next()){
    System.out.println(res.getString("artist"))
}

For updating, inserting and deleing we have to use the executeUpdate call.

String newTitle = ...;
String sql = "UPDATE CD SET artist = ?  WHERE artist = ?";
PreparedStatement prepStd = con.preparedStatement(sql);
sql.setString(1, newTitle);
sql.setString(2, oldTitle);
ResultSet res = prepStd.executeUpdate();

Retreiving metadata

The result table metadata can be accessed using the ResultSetMetaData class.

ResultSetMetaData rmd = res.getMetaData();
for (int i = 1; i <= rmd.getColumnCount(); i++){
    System.out.print(rmd.getColumnName());
}

Metadata of the database can be accessed using the DatabaseMetaData class. Creation of this class is implemented in the Connection interface.

ResultSetMetadata rmd = res.getMetaData();
for (int i = 1; i <= rmd.getColumnCount(); i++){
    System.out.print(rmd.getColumnName());
}

Important other aspects

Changes of the database need to be timed well. Sometimes they need to be bundled together using transactions . Important methods in this context are setAutoCommit, commit and rollBack of the interface Connection. Also Savepoint is important.

Also important are stored procedures. These implement rules for calls to the database.

Design aspects

New package for database layer. Datalaag has to return objects and not results!!! Translate resultsets into objects. Do not propagate SQLExceptions to domain layer of your program.

Important that object oriented design and relational design are not completely compatible. E.g. rel database needs ID per row, the object with all attrributes stored in the database does not have that ID.

for more info look at *o*bject-*r*elation-*m*apping (ORM).

Date transform

SQL date fields have different values than e.g. Java GregorianCalendar dates. But, they can be easily transformed in each other.

//Transform from java to MySQL
GregorianCalendar javaDate = new GregorianCalendar();
java.sql.Date sqlDatum = new java.sql.Date(javaDate.getTimeInMillis()); 
//Transform from  MySQL to Java
java.sql.Date sqlDatum2 = res.getDate("datum");

GregorianCalendar javaDate2 = new GregorianCalendar();
datum2.setTimeInMillis(sqlDatum2.getTime());