Transcript Java Queries

Databases: Queries with Java
Dr Andy Evans
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.
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()
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();
}
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.
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$]
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()) {
Can also use
int i = rs.getInt("a");
column index
number
or
String s = rs.getString("b"); (starting 1).
or
Object o = rs.getObject("c");
}
Use object if unsure, and cast.
A new SQL query will close the current ResultsSet.
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_CURSORS_OVER_COMMIT);
Only need to worry about
this if you’re not autocommitting.
Scrollable ResultSet
rs.beforeFirst();
rs.afterLast();
rs.next()
rs.previous()
Looping:
while (!rs.isAfterLast()) {
Finding number of rows:
rs.last();
int numberOfRows = rs.getRow();
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.
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();
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));
}
Further info
Online guide:
http://docs.oracle.com/javase/tutorial/jdbc/basics/