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 Don’t 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: jdbc:postgresql:// 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(queryStr);

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