UFCE4Y-20-3

1. UFCE4Y-20-3Components and Services Julia Dawson UFCE4Y-20-3 1

2. JDBC Java Database Connectivity UFCE4Y-20-3 2

3. What is JDBC • Java Database Connectivity (JDBC) is an API for the Java programming language that defines how a client may access a database. • It provides methods for querying and updating data in a database. • JDBC is oriented towards relational databases. The Java Platform, Standard Edition includes the JDBC API together with an ODBC implementation of the API enabling connections to any relational database that supports ODBC. • This driver is native code and not Java. http://en.wikipedia.org/wiki/JDBC UFCE4Y-20-3 3

4. Middleware • Middleware is computer software that connects software components or applications. • It is used most often to support complex, distributed applications. • It includes web servers, application servers, content management systems, and similar tools that support application development and delivery. • Middleware is especially integral to modern information technology based on XML, SOAP, Web services, and service-oriented architecture. http://en.wikipedia.org/wiki/Middleware UFCE4Y-20-3 4

5. RDBMS • Relational Database Management System • Database is under the control of a DBA • Application logic is isolated from data • Many views from same data • Computing power distributed > 90% of VB applications are DB access UFCE4Y-20-3 5

6. GUI HTML Code GUI SQL SQL SQL RDBMS RDBMS Code Code RDBMS Files Browser, Java & Oracle VB & Oracle Oracle Cobol Client Server Model UFCE4Y-20-3 6

7. DB Access • Objectives • Database Vendor Independent • Not bound to a supplier • Database Technology Independent • Use X/Open SQL Call Level Interface • Medium Level Interface • Without support for full SQL/Java mapping • Hardware Platform Independent • Compiled code can run on other platforms UFCE4Y-20-3 7

8. JDBC Structure Java Application JDBC Manager JDBC Bridge Vendor Tech. Java & Native Java - Protocol Java – Net Database http://java.sun.com/products/jdbc/driverdesc.html UFCE4Y-20-3 8

9. JDBC 1.0 Classes (1) • java.sql.* • DriverManager • The basic service for managing a set of JDBC drivers. When the method getConnection is called, the DriverManager will attempt to locate a suitable driver • Connection • A connection (session) with a specific database. SQL statements are executed and results are returned within the context of a connection. UFCE4Y-20-3 9

10. JDBC 1.0 Classes (2) • java.sql.* • Statement • The object used for executing a static SQL statement and returning the results it produces. • ResultSet • A table of data representing a database result set, which is usually generated by executing a statement that queries the database. • A ResultSet object maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row. UFCE4Y-20-3 10

11. Summary • JDBC is a simple way to access a DB • JDBC programs are portable • JDBC programs are not vendor specific • The GUI can be separated from the DB code • Component use encourages separate location for the DB server, application code and the GUI UFCE4Y-20-3 11

12. Building a DB Saved as student.mdb UFCE4Y-20-3 12

13. Add ODBC UFCE4Y-20-3 13

14. Example import java.sql.*; public class DBStudent { public static void main(String[ ] args) { Connection con; Statement st; ResultSetrs; String query = "select * from students"; try { // Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); con = DriverManager.getConnection("jdbc:odbc:class", "", ""); st = con.createStatement(); rs = st.executeQuery(query); while (rs.next()) { System.out.println(rs.getString(2) + ":" + s.getString(3)); } rs.close(); st.close(); con.close(); } catch (Exception e) { System.err.println("Trouble at mill ..." +e); } } //main } //class UFCE4Y-20-3 14

15. Example Code (1) import java.sql.*; public class DBStudent { public static void main(String[] args) { Connection con; Statement st; ResultSet rs; String query = "select * from students"; try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); con = DriverManager.getConnection("jdbc:odbc:class", "", ""); st = con.createStatement(); rs = st.executeQuery(query); Locate SQL classes Add Driver to Driver List Make Connection to ODBC database called class Run Query and get pointer/cursor to results UFCE4Y-20-3 15

16. Example Code (2) Iterate (move cursor) over ResultSet while (rs.next( ) ) { System.out.println( rs.getString(2) +" : "+ rs.getString(3) ) ; } rs.close( ); st.close( ); con.close( ); } catch ( Exception e ) { System.err.println("Trouble at mill ..." + e ); } } //main } //class Get column contents Releases object's database and JDBC resources immediately Jane:Brown Jo:Bloggs Sophie:Smith OUTPUT UFCE4Y-20-3 16

17. Connecting .... The driver must be registered with the JDBC DriverManager Load the driver class using : Class.forName() try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); Class.forName("com.oracle.jdbc.OracleDriver"); } catch (ClassNotFoundException e) { /* Do exception stuff .. */} Can load database drivers dynamically! UFCE4Y-20-3 17

18. JDBC URLs The JDBC Driver use a JDBC URL to identify and connect to particular database. Eg: jdbc.odbc.studb jdbc.oracle.thin:@ivor:1526:csm1 jdbc.hsql:c/temp/appleDB jdbc.hsql.http://www..... Each driver is able to recognises it's own URLs UFCE4Y-20-3 18

19. Making the Connection • Connection con = DriverManager.getConnection( • "url", "user", "password"); • User and password may be blank for some databases • Registered drivers are quizzed to see if they recognise the url • Url, user and password can be obtained from (for example): • a properties file • a Servlet init() method • an HTML form Always explicitly close the connection after use - otherwise others processes may be blocked from connecting. UFCE4Y-20-3 19

20. SQL Statements (3 types) • Statement • A basic SQL statement • PreparedStatement • A precompiled SQL statement - can improve efficiency (eg. repeat INSERT) • CallableStatement • To access procedures within the stored database (eg. PL/SQL) UFCE4Y-20-3 20

21. Statement Get a statement object for the connection thus: Statement stmt = con.createStatement(); Now perform an SQL statement and get the result like this: ResultSet rs = stmt.executeQuery("select * from names"); If a statement should return no result (eg. UPDATE or DELETE) Get the number of rows affected (for example): int count = stmt.executeUpdate("update from names where ...."); See also execute() NOTE: These calls will close any other ResultSet associated with this Statement. UFCE4Y-20-3 21

22. Processing ResultSet Statement st = con.createStatement(); ResultSet rs = st.executeQuery( "select id, name, phone from company"); while (rs.next()) { System.out.print("Id is " + rs.getString("ID") + ", Name is " + rs.getString("Name") + ", Phone is " + rs.getString("PHONE"); } //while rs.close(); st.close(); NOTE: Column name is case insensitive Could also get columns by position (starting at 1). System.out.print("Id is " + rs.getString(1) UFCE4Y-20-3 22

23. SQL Data types <mapping> Java types SQL type Java Type getXXX() CHAR, VARCHAR ...StringgetString() NUMERIC, DECIMALjava.Math.BigDecimal getBigDecimal() BITboolean getBoolean() INTEGERint getInt() FLOAT, DOUBLEdoublegetDouble() BINARYbyte [ ]getBytes() DATEjava.sql.DategetDate() TIME java.sql.Time getTime() NOTE: Not all drivers honour these. The getString() method returns a String representation of most of types. UFCE4Y-20-3 23

24. Beware of Nulls (Empty fields) If a column has null (empty) values - unpredictable results can occur. Best to trap these - first getXXX() the value and then test for null using wasNull() int amount = rs.getInt("STOCK"); if (rs.wasNull()) { System.out.print("Null"); } else { System.out.print(amount); } UFCE4Y-20-3 24

25. ResultSetMetaData Gives information about the structure of a particular ResultSet: number of columns rsmd.getColumnCount() names of these columns rsmd.getColumnName(i) kind of data in each column rsmd.getColumnTypeName(i) ResultSetrs = st.executeQuery("SELECT .... ResultSetMetaDatarsmd = rs.getMetaData(); intcolumnCount = rsmd.getColumnCount(); for (inti=1; i<= rsmd.getColumnCount(); i++) { System.out.print(rsmd.getColumnLabel(col) ... ... See Also: DatabaseMetaData UFCE4Y-20-3 25

26. Escape Sequences … make special characters literal. Wildcards st.executeQuery( "SELECT * from NAMES where id like 'SM\_%' {escape '\'}“ ); The "_" character is normally a wildcard for single character. Special Characters For example escaping the single quote inside a string. UFCE4Y-20-3 26

27. Where to go from here … • Oracle JDBC tutorial See Linkson the module home page • Next CAS practical will use material from here … UFCE4Y-20-3 27