Database programming in Java An introduction to Java

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

Introduction n Two standard ways to work with databases in Java q JDBC n q SQLJ n n A Call level interface similar to ODBC 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

Java Database Connectivity n n n JDBC is a specification from Sun and part of Java 2 We will talk about JDBC 2 JDBC applications are portable q Switch database without rewriting your program n n 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

Level 1 Drivers n n n 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 q n The ODBC drivers and all libraries that the driver needs Suns JDBC-ODBC bridge is single threaded

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

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

Level 4 Drivers n n n 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

Important JDBC Classes/Interfaces n n n n java. sql. Driver. Manager java. sql. Driver java. sql. Connection java. sql. Statement java. sql. Prepared. Statement java. sql. Callable. Statement java. sql. Result. Set q q n Scrollable or not Updateable or not javax. sql. Data. Source

java. sql. Driver. Manager n n The Driver. Manager is responsible for loading the correct Driver The Driver. Manager is used to get a connection to the database

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

java. sql. Connection n A Connection represent an actual connection to the database The Connection is used to create statements (queries) A Connection is returned from the Driver. Manager q q Driver. Manger. get. Connection(url, username, password) Driver. Manager. get. Connection(url)

java. sql. Connection – important methods n n n set. Auto. Commit(boolean) create. Statement() prepare. Statement(“SQL query”) commit() rollback() close() q ALLWAYS close your connections

java. sql. Connection – important methods n get. Meta. Data() returns a Database. Meta. Data object q From the Database. Meta. Data you can get information about the database n n n Vendor name Version Supported functions

java. sql. Statement n n A Statement is the simplest of the statement types It’s used to pass a query to the database and to return a Result. Set

java. sql. Statement - important methods n execute. Query(“sql query”) q n execute(“sql query”) q n n n Returns a Result. Set Mostly used when the type of query is unknown execute. Update(“sql query”) get. Result. Set() close() q ALLWAYS close your Statements

java. sql. Prepared. Statement n n 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

java. sql. Prepared. Statement – important methods Can do all that a Statement can n set. XXX() is used to set the different parameters pstmt = con. prepare. Statement(“select * from person where cars=`? ”); pstmt. set. Int(1, car. Id); pstmt. execute. Query(); n

java. sql. Callable. Statement n n n Callable. Statement is used to prepare and call stored procedures in the database prepare. Call(“statement”) execute()

java. sql. Result. Set n n The Result. Set is used to get the information from the Database Retured from execute. Query() or get. Result. Set() Like a cursor in embedded SQL Just like with Connections and Statements, ALLWAYS close when you’re done

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

java. sql. Result. Set – important methods n next() q q n get. XXX(position) q n 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 Result. Set Gets the column with postion get. XXX(name) q q Gets the column with the matching name The name is the same as in the select list

java. sql. Result. Set – important methods n get. Meta. Data() returns a Result. Set. Meta where you can get information about the Result. Set q q q Number of columns Type of Result. Set NOT the number of rows

javax. sql. Data. Source n Data. Source can be used instead of Driver. Manager and Driver q If possible, use it Retrieved via JNDI (Java Naming and Directory Interface) Data. Source ds = (Data. Source)context. lookup(“java: com/env/jd bc/multi 1”); ds. get. Connection(); n

Simple example 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. package com. mimer. kurs. uu. jdbc; import java. sql. *; public class Jdbc. One { public static void main(String[] args) { try{ Class. for. Name("com. mimer. jdbc. Driver"); Connection con = Driver. Manager. get. Connection("jdbc: mimer: multi 1", "fredrik"); Statement stmt = con. create. Statement(); Result. Set rs = stmt. execute. Query("SELECT TABLE_NAME FROM INFORMATION_SCHEMA. TABLES"); while(rs. next()){ System. out. println(rs. get. String("TABLE_NAME")); } } catch(Exception e){ System. out. println("Error: " + e. get. Message()); } } }

More advanced example 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. 20. 21. 22. package com. mimer. kurs. uu. jdbc; import java. sql. *; import java. io. *; public class Jdbc. Two { public static void main(String[] args) { String driver="com. mimer. jdbc. Driver"; String url="jdbc: mimer: multi 1"; String username="fredrik"; String password="fredrik"; Result. Set rs = null; Prepared. Statement 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. for. Name("com. mimer. jdbc. Driver"); con = Driver. Manager. get. Connection(url, username, password); pstmt = con. prepare. Statement(query); pstmt. set. String(1, "BASE TABLE"); rs = pstmt. execute. Query(); while(rs. next()){ System. out. println(rs. get. String("TABLE_NAME")); 23. } 24. 25. n }

More advanced example, continued 26. 27. 28. 29. 30. 31. 32. 33. 34. 35. 36. 37. 38. 39. 40. 41. 42. 43. 44. 45. 46. 47. 48. 49. 50. 51. catch(Class. Not. Found. Exception cnfe){ System. out. println("Could not load Driver"); } catch(SQLException sqle){ System. out. println("SQL Error: " + sqle. get. Message()); } catch(Exception e){ System. out. println("Error: " + e. get. Message()); } finally{ try{ rs. close(); } catch(Exception e){ } try{ pstmt. close(); } catch(Exception e){ } try{ con. close(); } catch(Exception e){ } } 52. } 53. 54. }

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

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