JDBC

1
JDBC

• Java and Databases, including Postgress

2
JDBC

• Developed by Industry leaders
• Three main goals
• JDBC should be an SQL-level API
• JDBC should capitalize on existing DB APIs
• JDBC should be simple

3
SQL level

• you should be able to construct SQL statements
  and embed them in Java API calls
• jdbc vs odbc C based and overly complex
• jdbc portability is a big asset

4
Developing JDBC Applications

• Import the jdbc classes
• load the jdbc driver
• Connect to the database
• Submit queries, inserts, updates and process
  responses
• Dont forget to commit any changes!
• Disconnect from the database

5
Import the jdbc files

• import java.sql.
• will import all of the sql classes for jdbc
• This will include Drivers, Driver Managers,
  Connections, Statements, ResultSets, etc.

6
Load the Driver

• PC new JdbcOdbcDriver()
• Others
• try // load driver
• Class.forName( string name of driver )
• catch ( Exception e )
• e.printStackTrace()
• return

7
Postgress

• Driver available at URL
• http//www.postgresql.org/ftp/
• Install, then download JDBC JAR file at
• http//jdbc.postgresql.org/download.html
• and place it in your classpath (including the
  jar name)
• try // load driver
• Class.forName(org.postgresql.Driver )
• catch ( Exception e )
• e.printStackTrace()
• return

8
The Connection Interface

• represents a session with a specific DB
• all SQL statements executed and results returned
  within the context of the connection
• A Java application can have any number of
  connections to one or more DBs
• through the connection we have access to the DB
  and its stored information, including metadata
• autocommit is the default be aware of
  transactions. Turn off autocommit and commit
  explicitly.
• use DriverManager.getConnection(url) to create a
  connection
• See Java Doc on Connection

9
Connect to Database

• try
• connection DriverManager.getConnection
• (url,usercode,password)
• System.out.println(Connected to Database)
• // do some db stuff here
• catch ( SQLException e )
• e.printStackTrace()
• Postgress URL would start with
  jdbcpostgresql//
• followed by database.
• last two arguments are database login user,
  password

10
Database Interaction

• All information henceforth is common to all
  databases. JDBC handles the different databases
  transparently
• Statement
• used for SQL statements without parameters
• PreparedStatement
• same statement with different explict data
  values. Precompiled for efficiency with most
  drivers
• CallableStatement
• used for executing stored functions or
  procedures
• See Java Doc on each of the above

11
Statement

• connection.createStatement() creates a statement
  for SQL.
• Statement queryStmt
• conn.createStatement()
• statement.executeQuery(SQLstatement as a string)
• ResultSet result queryStmt.executeQuery(queryS
  tr)

12
PeparedStatement

• connection.prepareStatement(SQL statement as a
  String with parameters)
• PreparedStatement q
• conn.prepareStatement(qString)
• // set parameters then execute statement
• statement.execute()
• ResultSet result
• q.executeQuery()
• See PrepStatement.java

13
CallableStatement

• connection.prepareCall(String representing call
  to a stored procedure/function)
• CallableStatement verify
• conn.prepareCall( " ? call
  gord7932.verifyLogin(?,?,?,?,?,?) " )
• The ? represents parameter in numeric order
  starting at 1
• See javaCallable.java

14
ResultSet

• public abstract boolean next()
• public abstract void close()
• public abstract boolean wasNULL()
• public abstract XXX getXXX( int column)
• public abstract XXX getXXX(String
  columnname) where XXX is a type like String,
  Int, etc.
• ResultSet may be scrollable (navigated in both
  directions)
• See Java Doc and the previous examples

15
More Connection Methods

• public abstract void close()
• public abstract void setAutoCommit(boolean val)
• public abstract void commit()
• public abstract void rollback()
• each of these methods throws SQLException

16
Insert, Delete, Update

• use executeUpdate method in either a Statement,
  PreparedStatement, or CallableStatement