Transcript 09_JDBC

JDBC
Java and Databases, including
Postgress
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
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

Developing JDBC
Applications
Import the jdbc classes
 load the jdbc driver
 Connect to the database
 Submit queries, inserts, updates and
process responses
Don’t forget to commit any changes!
 Disconnect from the database

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.

Load the Driver
PC: new JdbcOdbcDriver();
 Others:
try // load driver
{ Class.forName( “string name of driver” );
}
catch ( Exception e )
{
e.printStackTrace();
return;
}

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;
}
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
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:
jdbc:postgresql://
followed by database.
last two arguments are database login: user, password
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
Statement


connection.createStatement() creates a statement
for SQL.
Statement queryStmt =
conn.createStatement();
statement.executeQuery(SQLstatemen
t as a string);
ResultSet result =
queryStmt.executeQuery(queryStr);
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
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
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
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

Insert, Delete, Update

use executeUpdate method in either a
Statement, PreparedStatement, or
CallableStatement