Java Course 13: JDBC & Logging

Java course - IAG0040

JDBC & Logging

Anton Keks 2011

JDBC
●
Java DataBase Connectivity
– The API for DB access from Java
– Oriented towards relational databases
– java.sql package
– JDBC is DB vendor neutral
● Versions
– Exists since Java 1.1
– Java 1.4 & 1.5 ships with JDBC 3
– Java 1.6 introduced JDBC 4
Java course - IAG0040 Lecture 13
Anton Keks Slide 2

JDBC drivers
●
java.sql.Driver - the driver side of the JDBC
layer is the part that interfaces with the
actual database, and therefore is generally
written by database vendors
●
Most developers only need to know how to
install and use drivers. The JDBC Driver API
defines a set of interfaces which have to be
implemented by a vendor

Anton Keks Slide 3

JDBC driver types
●
Type 1 use a bridge technology to connect a Java client to
ODBC system. The JDBC-ODBC bridge from Sun is one example
of a Type 1 driver
● Type 2 use native code library to access a database, wrapping
a thin layer of Java around the native library, e.g. Oracle OCI
driver
● Type 3 drivers define a generic network protocol that
interfaces with a piece of custom middleware. The
middleware component might use any other type of driver to
provide the actual database access.
● Type 4 drivers are implemented entirely in Java. They
understand database-specific networking protocols and can
access the database directly without any additional software.
Anton Keks Slide 4

JDBC driver summary

Type 1 ODBC
ODBC bridge diver

Type 2 Native API
Native API

Type 3 Middleware
DB
Network server

Type 4
Pure Java

Anton Keks Slide 5

JDBC API basics
ResultSet

Statement PreparedStatement CallableStatement

Connection
Application

DriverManager

Oracle, MySQL,
Concrete Driver PostgreSQL,
HSQLDB, etc

Concrete DB

Anton Keks Slide 6

JDBC basic usage
●
Load the driver (was needed before JDBC 4)
– Class.forName(“driverClassName”);
– System property -Djdbc.drivers=driverClassName
●
Use DriverManager to create connection
– DriverManager.getConnection(url, user, password);
●
Create statement for execution
– connection.createStatement();
●
Execute the query and get a ResultSet
– statement.executeQuery(sql);
● Iterate over ResultSet: rs.next() and rs.getXXX()
● Free resources with close() methods
Anton Keks Slide 7

Connection
●
java.sql.Connection
● Represents an open connection to the DB
● Obtained via a DriverManager
– DriverManager.getConnection(url, user, password)
●
URL starts with jdbc:
– The exact format depends on the vendor
– jdbc:mysql://server:port/dbname
– jdbc:hsqldb:mem:dbname
– jdbc:oracle:thin:@server:port:sid
Anton Keks Slide 8

Statement & PreparedStatement
●
java.sql.Statement
– for execution of simple statements without
parameters
– s = conn.createStatement();
s.execute(“CREATE TABLE ..”);

●
java.sql.PreparedStatement
– for execution of parametrized statements via
'parameter binding'
– s = conn.prepareStatement(“SELECT .. WHERE ID = ?”)
s.setInt(1, value); ResultSet rs = s.executeQuery();
– allows for reuse of pre-compiled statements
Anton Keks Slide 9

ResultSet
●
java.sql.ResultSet
– represents result of a SQL query, containing
multiple rows, similar to an Iterator
– ResultSet rs = s.executeQuery(“SELECT ...”);
while (rs.next()) {
rs.getString(1); or rs.getString(“COLUMN”);
}
rs.close();
– cursor movement by default is
ResultSet.TYPE_FORWARD_ONLY
● Some drivers may support bidirectional
movement
Anton Keks Slide 10

CallableStatement
●
java.sql.CallableStatement
– extends PreparedStatement
– intended for calling stored procedures in the DB
– allows reading of OUT parameters instead of
getting a ResultSet
– s = conn.prepareCall(“{call SOME_PROC(?, ?, ?)}”);
s.setString(1, “some value”);
s.registerOutParameter(2, Types.VARCHAR);
s.registerOutParameter(3, Types.NUMERIC);

s.execute();
String result1 = s.getString(2);
int result2 = s.getInt(3);

Anton Keks Slide 11

Resourse Management
●
All DB objects have the close() method
– higher-level DB objects automatically close the
lower-level ones
– conn.close() will close all underlying statements
● Don't forget proper closing
– same pattern applies as with java.io
– it is a good idea to put close() into a finally block!

Anton Keks Slide 12

Metadata
●
DatabaseMetaData, ResultSetMetaData,
ParameterMetaData
– Metadata provides additional information about
the respective DB objects
– Can be used for discovering of DB structure and
other 'advanced' or non-standard code
– DatabaseMetaData metadata = conn.getMetaData();
String name = metadata.getDatabaseProductName();
– ResultSet rs = s.executeQuery(“SELECT ...”);
ResultSetMetaData metadata = rs.getMetaData();
int columns = metadata.getColumnCount();

Anton Keks Slide 13

Transactions
●
Connection auto-commit is ON by default
– Use conn.setAutoCommit(false) to control
transactions manually
●
Transaction control
– connection.commit() persists the changes
– connection.rollback() cancels the changes
– connection.setSavepoint() bookmarks transactions
– exact behaviour depends on the concrete DB

Anton Keks Slide 14

DataSource
●
java.sql.DataSource
– a bean-style alternative to DriverManager
– implemented by a DB vendor
● Is usually initialized in a vendor-specific way in
the application container
– provides getConnection() method
● Spring Framework and Commons-DBCP have
useful implementations, e.g. for connection
pooling
Anton Keks Slide 15

Data Access Patterns
●
Define where to put the JDBC-related code in
an application
● In general:
– Isolate and encapsulate JDBC code
– Very few classes should know where the data
comes from
– Pass data around as domain objects, not ResultSets
or a mix of Strings or primitive types

Anton Keks Slide 16

Active Domain Object Pattern
●
aka ActiveRecord
● Wraps a row in a table or view, encapsulates
DB access, and adds domain logic
– JDBC only used internally, not visible from the
outside
– Person person = Person.create();
person.setName(“John Doe”);
person.save();
– Person person = Person.load(“John Doe”);

Anton Keks Slide 17

Data Accessor Pattern
●
aka Data Access Object (DAO)
● Encapsulates physical data access in a single
component, exposing logical operations
– Application code maintains knowledge about the
underlying data model, but is decoupled from the
data access possibilities
– Domain objects know nothing about the DB
– PersonAccessor dao = new JDBCPersonAccessor();
Person person = dao.loadPerson(“John Doe”);
person.setName(“John Smith”);
dao.save(person);

Anton Keks Slide 18

Testing
●
Clear separation of DB access logic from
business logic makes testing and maintenance
a lot easier
●
All JDBC interfaces can be easily mocked
– Connection conn = createMock(Connection.class);
●
Sometimes it is wise to test the full-cycle
– Use in-memory database, e.g. HSQLDB
– Initialize the data there and substitute DB
connection with the fake in-memory one
– DBUnit can help with that
Anton Keks Slide 19

Logging
● When writing more complex applications, you need logging in
your code
– you don't want to show low-level crash info to end-users
– debugging of bugs on production is usually not possible
● There are many possibilities to implement logging:
– System.out / System.err or other java.io classes – usually
primitive and not flexible solution
– java.util.logging, e.g. Logger class – logging API included in Java
since 1.4, configurable and extensible
– Log4J – very popular de-facto standard framework, very
powerful, has a very good API
– Jakarta commons-logging – the facade for different logging APIs
Anton Keks Slide 20

java.util.logging
● Logger LOG = Logger.getLogger(this.getClass().getName());
– Loggers have hierarchical structure ('.' separated), it is a good idea to use
full class name as a logger name
– Logger often used as a static member (for convenience)
● Loggers can be used for logging information with various levels
– SEVERE, WARNING, INFO, CONFIG, FINE, FINER, FINEST
– LOG.severe(“We have a problem!”);
– LOG.log(Level.SEVERE, “We have a problem”, exception);
● java.util.logging is configurable
– by default it uses $JAVA_HOME/jre/lib/logging.properties
– specific file is specified with sys. property java.util.logging.config.file

Anton Keks Slide 21

java.util.logging (cont)
● Logger is used to produce LogRecords
– every LogRecord has a specified logging Level
– every Logger may have its own Level assigned
– or they inherit Level from their parent
● LogRecords are passed to one or more Handlers
– e.g. ConsoleHandler, FileHandler, MemoryHandler, SocketHandler, etc
– every handler writes logs greater or equal to their assigned Level
● Formatters are used for formatting of LogRecords
– SimpleFormatter or XMLFormatter
● a LogRecord is written only if its Level is greater than of its Logger's and if
there is a Handler configured to write at this Level
● Filters may be used for more fine-grained control of what should be logged

Anton Keks Slide 22

Log4J (org.apache.log4j)
● Logger LOG = Logger.getLogger(this.getClass());
– Loggers have hierarchical structure ('.' separated), same as util.logging
● Loggers can be used for logging information with various levels
– FATAL, ERROR, WARN, INFO, DEBUG are default levels
– LOG.error(“We have a problem!”, exception);
● Log4J is fully configurable with external files
– there is no default configuration
– it automatically looks for log4j.xml or log4j.properties in classpath
– can be overriden with log4j.configuration system property
– every named logger can have its own configuration
– different appenders can be used for writing the actual data

Anton Keks Slide 23

Java Course 13: JDBC & Logging

More Related Content

What's hot

Similar to Java Course 13: JDBC & Logging

More from Anton Keks

Recently uploaded

Java Course 13: JDBC & Logging