Database programming in Java

1. Database programming in Java An introduction to Java Database Connectivity (JDBC)

2. Introduction • Two standard ways to work with databases in Java • JDBC • A Call level interface similar to ODBC • SQLJ • SQL code imbedded in Java, like SQL embedded in C • JDBC is the most common way and it’s supported by almost all database vendors

3. Java Database Connectivity • JDBC is a specification from Sun and part of Java 2 • We will talk about JDBC 2 • JDBC applications are portable • Switch database without rewriting your program • If there is a driver for the database • If you use only standard SQL (i.e. no vendor specific code) • JDBC is the Java version of ODBC • There are four levels for JDBC drivers

4. Level 1 Drivers • Level 1 is a JDBC-ODBC bridge • The actual database communication is done via a ODBC driver • Requires the Database client library to be installed • The ODBC drivers and all libraries that the driver needs • Suns JDBC-ODBC bridge is single threaded

5. Level 2 Drivers • This is a partly Java solution • All JDBC calls are converted from to calls in the vendor specific client API • The library must be installed on the client machine

6. Level 3 Drivers • Level 3 is a multi tier solution • On the client it’s all Java • No vendor specific client library is needed • The connection is made to a server that connects to the database • The server can use ODBC or some other technology • Several databases can be supported by the server

7. Level 4 Drivers • Level 4 is an all Java solution • No client API is needed besides the JDBC Driver • This is the most common type, and the one that we will use • All JDBC calls are directly transformed to the vendor specific protocoll • Direct calls from the client to the database server

8. Important JDBC Classes/Interfaces • java.sql.DriverManager • java.sql.Driver • java.sql.Connection • java.sql.Statement • java.sql.PreparedStatement • java.sql.CallableStatement • java.sql.ResultSet • Scrollable or not • Updateable or not • javax.sql.DataSource

9. java.sql.DriverManager • The DriverManager is responsible for loading the correct Driver • The DriverManager is used to get a connection to the database

10. java.sql.Driver • This is the actual implementation of the JDBC Driver • The only part that’s vendor specific • Used if DriverManager is used to get connection • Loaded with Class.forName(“driverclass”) • The driver name for Mimer SQL is “com.mimer.jdbc.Driver”

11. java.sql.Connection • A Connection represent an actual connection to the database • The Connection is used to create statements (queries) • A Connection is returned from the DriverManager • DriverManger.getConnection(url, username, password) • DriverManager.getConnection(url)

12. java.sql.Connection – important methods • setAutoCommit(boolean) • createStatement() • prepareStatement(“SQL query”) • commit() • rollback() • close() • ALLWAYS close your connections

13. java.sql.Connection – important methods • getMetaData() returns a DatabaseMetaData object • From the DatabaseMetaData you can get information about the database • Vendor name • Version • Supported functions

14. java.sql.Statement • A Statement is the simplest of the statement types • It’s used to pass a query to the database and to return a ResultSet

15. java.sql.Statement - important methods • executeQuery(“sql query”) • Returns a ResultSet • execute(“sql query”) • Mostly used when the type of query is unknown • executeUpdate(“sql query”) • getResultSet() • close() • ALLWAYS close your Statements

16. java.sql.PreparedStatement • A prepared statement is a Statement with parameters • The prefered way if you have conditions in your query • Will be compiled once at the server and then cached • Give you an easier to read code

17. java.sql.PreparedStatement – important methods • Can do all that a Statement can • setXXX() is used to set the different parameters pstmt = con.prepareStatement(“select * from person where cars=`?”); pstmt.setInt(1,carId); pstmt.executeQuery();

18. java.sql.CallableStatement • CallableStatement is used to prepare and call stored procedures in the database • prepareCall(“statement”) • execute()

19. java.sql.ResultSet • The ResultSet is used to get the information from the Database • Retured from executeQuery() or getResultSet() • Like a cursor in embedded SQL • Just like with Connections and Statements, ALLWAYS close when you’re done

20. java.sql.ResultSet • Before the first fetch, the position is before the first row • ResultSet can be of several types • Updateable • Can be used to perform updates in the database directly • Rows can be inserted • Scrollable • The cursor can be moved forward and backwards

21. java.sql.ResultSet – important methods • next() • Used when looping over the result • Returns true if there was a row to fetch and false otherwise • Moves the cursor one step forward • The classic loop is while(rs.next()) where rs is a ResultSet • getXXX(position) • Gets the column with postion • getXXX(name) • Gets the column with the matching name • The name is the same as in the select list

22. java.sql.ResultSet – important methods • getMetaData() returns a ResultSetMeta where you can get information about the ResultSet • Number of columns • Type of ResultSet • NOT the number of rows

23. javax.sql.DataSource • DataSource can be used instead of DriverManager and Driver • If possible, use it • Retrieved via JNDI (Java Naming and Directory Interface) DataSource ds = (DataSource)context.lookup(“java:com/env/jdbc/multi1”); ds.getConnection();

24. Simple example • package com.mimer.kurs.uu.jdbc; • import java.sql.*; • public class JdbcOne { • public static void main(String[] args) { • try{ • Class.forName("com.mimer.jdbc.Driver"); • Connection con = DriverManager.getConnection("jdbc:mimer:multi1","fredrik","fredrik"); • Statement stmt = con.createStatement(); • ResultSet rs = stmt.executeQuery("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES"); • while(rs.next()){ • System.out.println(rs.getString("TABLE_NAME")); • } • } • catch(Exception e){ • System.out.println("Error:" + e.getMessage()); • } • } • }

25. More advanced example • package com.mimer.kurs.uu.jdbc; • import java.sql.*; • import java.io.*; • public class JdbcTwo { • public static void main(String[] args) { • String driver="com.mimer.jdbc.Driver"; • String url="jdbc:mimer:multi1"; • String username="fredrik"; • String password="fredrik"; • ResultSet rs = null; • PreparedStatement pstmt = null; • Connection con = null; • //All accessible tables for the current ident • String query="SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=?"; • try{ • Class.forName("com.mimer.jdbc.Driver"); • con = DriverManager.getConnection(url,username,password); • pstmt = con.prepareStatement(query); • pstmt.setString(1, "BASE TABLE"); • rs = pstmt.executeQuery(); • while(rs.next()){ • System.out.println(rs.getString("TABLE_NAME")); • } • }

26. More advanced example, continued • catch(ClassNotFoundException cnfe){ • System.out.println("Could not load Driver"); • } • catch(SQLException sqle){ • System.out.println("SQL Error: " + sqle.getMessage()); • } • catch(Exception e){ • System.out.println("Error:" + e.getMessage()); • } • finally{ • try{ • rs.close(); • } • catch(Exception e){ • } • try{ • pstmt.close(); • } • catch(Exception e){ • } • try{ • con.close(); • } • catch(Exception e){ • } • } • } • }

27. Assignment • Create a table in the database: create table PERSON( PNR INTEGER, NAME CHARACTER(10default 'Unknown', SURNAME CHARACTER(10), SEX CHARACTER(4) not null, AGE INTEGER, primary key(PNR));

28. Assignment • Create a simple Java program that adds persons to the database. • It can be interactive or it can take all the arguments on the commandline • Tip: use PreparedStatement • Create a simple Java program that lists all persons older than a given age • It can be interactive or it can take all the arguments on the commandline • Tip: use PreparedStatement