Databases: Queries with Java

1
Databases Queries with Java

• Dr Andy Evans

2
JDBC SQL

• Three methods
• Statements Standard, simple, SQL.
• PreparedStatements Compiled SQL statements that
  are altered to new data through input parameters.
  Useful, for example, in looped structures.
• CallableStatements for SQL procedures stored in
  the database.
• Produce ResultSet objects.

3
Example Select

• Statement st conn.createStatement()
• ResultSet rs st.executeQuery
• ("SELECT a,b,c FROM Table1")
• Three different execution methods
• executeQuery simple SQL queries.
• executeUpdate anything that changes or creates
  a Table, e.g. UPDATE. Returns number of rows
  effected.
• execute Complex, multi-return queries.
• st.close() efficient, but will happen at
  conn.close()

4
Example Creating tables

• String createTable
• "CREATE TABLE Results ("
• "Address varchar(255),"
• "Burglaries int"
• ")"
• Statement st null
• try
• st conn.createStatement()
• st.execute (createTable)
• catch (SQLException ex)
• ex.printStackTrace()

5
Auto-commit

• You can run multiple queries on the same
  statement.
• By default Connection objects are set to
  auto-commit all changes are solidified after
  single statements are run.
• conn.setAutoCommit(booleanAutoCommit)
• If set to false (off) the changes will only be
  solidified when commit called
• conn.commit()
• Until then you can rollback all changes since the
  last commit, by calling
• conn.rollback()
• Also options to setup and rollback to savepoints.

6
Escape characters

• Remember that some characters, like _, are
  wildcards.
• If we want to use these literally, they need a
  backslash infront of them \_.
• However, backslash is a String escape character
  in Java, so you can define what is a special
  escape character to ignore in statement
  execution
• stmt.executeQuery(
• "SELECT name FROM Table1 WHERE Id LIKE '\_'
  escape '\'")
• In some cases the escape occurs in a table name,
  in which case treat literally by enclosing in
  e.g. Sheet1

7
ResultSets

• Links to a cursor on the database and converts
  data to Java types.
• ResultSet rs st.executeQuery
• ("SELECT a, b, c FROM Table1")
• while (rs.next())
• int i rs.getInt("a")
• or String s rs.getString("b")
• or Object o rs.getObject("c")
• Use object if unsure, and cast.
• A new SQL query will close the current ResultsSet.

Can also use column index number (starting 1).
8
ResultSets

• Standard results sets only let you read from
  beginning to end a line at a time.
• If you want to write to the data (without using
  SQL UPDATE) or go back and forwards, you need a
  scrollable statement.
• Statement st conn.createStatement(
  ResultSet.TYPE_SCROLL_SENSITIVE,
  ResultSet.CONCUR_UPDATABLE, ResultSet.HOLD_CURSO
  RS_OVER_COMMIT)

Only need to worry about this if youre not
auto-committing.
9
Scrollable ResultSet

• rs.beforeFirst()
• rs.afterLast()
• rs.next()
• rs.previous()
• Looping
• while (!rs.isAfterLast())
• Finding number of rows
• rs.last()
• int numberOfRows rs.getRow()

10
Scrollable ResultSet

• st.setFetchSize(25) Fetch 25 rows at a time.
• rs.absolute(2) Move to row 2
• rs.updateInt(3, 10) Update col 3 of current row.
  
• rs.updateInt("Name",10) Update by col name.
• also updateObject, updateString, updateFloat and
  others.
• rs.updateRow() Must call this after updating
  data in all the columns you want to change for
  that row. You also need to call rs.close() to
  commit all changes.

11
Inserting a row

• rs.moveToInsertRow()
• rs.updateString(1, "Bob")
• rs.updateObject(2, someObject)
• rs.insertRow()
• rs.first()
• (In general the cursor points at the row used
  prior to insert.)
• conn.commit() If needed.
• rs.close()

12
Database metadata

• Especially useful in debugging is getting
  metadata about the database
• DatabaseMetaData md conn.getMetaData()
• ResultSet rs md.getTables(
• null, null, "", null)
• while (rs.next())
• System.out.println(rs.getString(3))

13
Further info

• Online guide
• http//docs.oracle.com/javase/tutorial/jdbc/basics
  /