JDBC and Database Programming in Java

1. JDBC and Database Programming in Java Seree Chinodom seree@buu.ac.th

2. Agenda • Overview of Databases and Java • Overview of JDBC • JDBC APIs • Other Database Techniques

3. Overview Of Database and Java JDBC TCP/IP RMI Java.net TCP/IP Network OS

4. Why Java? • Write once, run anywhere • Multiple client and server platforms • Object-relational mapping • databases optimized for searching/indexing • objects optimized for engineering/flexibility • Network independence • Works across Internet Protocol • Database independence • Java can access any database vendor • Ease of administration • zero-install client

5. Database Architectures • Two-tier • Three-tier • N-tier

6. Two-Tier Architecture • Client connects directly to server • e.g. HTTP, email • Pro: • simple • client-side scripting offloads work onto the client • Con: • fat client • inflexible

7. Three-Tier Architecture • Application Server sits between client and database • Three-Tier Pros • flexible: can change one part without affecting others • can connect to different databases without changing code • specialization: presentation / business logic / data management • can cache queries • can implement proxies and firewalls • Three-Tier Cons • higher complexity • higher maintenance • lower network efficiency • more parts to configure (and buy)

8. N-Tier Architecture • Design your application using as many "tiers" as you need • Use Object-Oriented Design techniques • Put the various components on whatever host makes sense • Java allows N-Tier Architecture, especially with RMI and JDBC

9. Database Technologies • Hierarchical • obsolete (in a manner of speaking) • any specialized file format can be called a hierarchical DB • Relational (aka SQL) (RDBMS) • row, column • most popular • Object-relational DB (ORDBMS) • add inheritance, blobs to RDB • NOT object-oriented -- "object" is mostly a marketing term • Object-oriented DB (OODB) • data stored as objects • high-performance for OO data models

10. Relational Databases • invented by Dr. E.F.Codd • data stored in records which live in tables • maps row (record) to column (field) in a single table • "relation" (as in "relational") means row to column (not table to table)

11. Joining Tables • you can associate tables with one another • allows data to nest • allows arbitrarily complicated data structures • not object-oriented

12. Join example • People • name • homeaddress • workaddress • Addresses • id • street • state • zip

13. SQL • Structured Query Language • Standardized syntax for "querying" (accessing) a relational database • Supposedly database-independent • Actually, there are important variations from DB to DB

14. SQL Syntax INSERT INTO table ( field1, field2 ) VALUES ( value1, value2 ) • inserts a new record into the named table UPDATE table SET ( field1 = value1, field2 = value2 ) WHERE condition • changes an existing record or records DELETE FROM table WHERE condition • removes all records that match condition SELECT field1, field2 FROM table WHERE condition • retrieves all records that match condition

15. Transactions • Transaction = more than one statement which must all succeed (or all fail) together • If one fails, the system must reverse all previous actions • Also can't leave DB in inconsistent state halfway through a transaction • COMMIT = complete transaction • ROLLBACK = abort

16. JDBC Goals • SQL-Level • 00% Pure Java • Keep it simple • High-performance • Leverage existing database technology • why reinvent the wheel? • Use strong, static typing wherever possible • Use multiple methods to express multiple functionality

17. JDBC Architecture • Java code calls JDBC library • JDBC loads a driver • Driver talks to a particular database • Can have more than one driver -> more than one database • Ideal: can change database engines without changing any application code Application JDBC Driver

18. JDBC Drivers • Type I: "Bridge" • Type II: "Native" • Type III: "Middleware" • Type IV: "Pure"

19. JDBC Drivers Type I “Bridge” ODBC Driver ODBC Type II “Native” CLI(.lib) JBC Type III “Middleware” Middleware Server Type IV “Pure”

20. Type I Drivers • Use bridging technology • Requires installation/configuration on client machines • Not good for Web • e.g. ODBC Bridge

21. Type II Drivers • Native API drivers • Requires installation/configuration on client machines • Used to leverage existing CLI libraries • Usually not thread-safe • Mostly obsolete now • e.g. Intersolv Oracle Driver, WebLogic drivers

22. Type III Drivers • Calls middleware server, usually on database host • Very flexible -- allows access to multiple databases using one driver • Only need to download one driver • But it's another server application to install and maintain • e.g. Symantec DBAnywhere

23. Type IV Drivers • 100% Pure Java -- the Holy Grail • Use Java networking libraries to talk directly to database engines • Only disadvantage: need to download a new driver for each database engine • e.g. Oracle, mSQL,MySQL

24. JDBC Limitations • No scrolling cursors • No bookmarks

25. Related Technologies • ODBC • Requires configuration (odbc.ini) • RDO, ADO • Requires Win32 • OODB • e.g. ObjectStore from ODI • JavaBlend • maps objects to tables transparently (more or less)

26. The JDBC interfaces • JDBC is a set of interfaces which provide a consistant API for accessing databases from different vendors. • Vendors provide classes for using their database which implement these interfaces: • CallableStatement • Connection • DatabaseMetaData • Driver • PreparedStatement • ResultSet • ResultSetMetaData • Statement

27. Accessing a database using JDBC • JDBC is a set of interfaces designed to allow access to third party database products and defined in java.sql. • Before accessing a database we must load the driver class for communicating with the database. Simply refering to the driver will load it into the JVM: Class.forName(driverName).newInstance(); • For our assignment we will be using the SimpleText Driver so the statement appears as follows: Class.forName("jdbc.SimpleText.SimpleTextDriver").newInstance();

28. Accessing a database using JDBC • The location of a database is specified using a URL of the following form: < protocol>:< subprotocol>:< subname> • For example to access an ODBC (Open Database Connectivity) database could use the following: jdbc:odbc:databasename

29. Connecting to the database • Once the Driver is loaded and the URL constructed we can connect to database specified in the URL using the DriverManager class method getConnection(): Connection jdbcConnection = DriverManager.getConnection(url, prop); • The first argument is the URL constructed above, the second is a Properties object, possibly containing configuration for the database (eg. date format, location of tables).

30. Using JDBC to Connect to a Database Class.forName([LOCATION OF DRIVER]); Connection jdbcConnection = DriverManager.getConnection ([LOCATION OF DATASOURCE]);

31. Example • to connect to Access datasource ( "Access” is DataSource Name in the 32-bit ODBC control panel. The name "Access" corresponds to Test_db.mdb file created by MsAccess): Class.forName(sun.jdbc.odbc.JdbcOdbcDriver); Connection jdbcConnection = DriverManager.getConnection (jdbc:odbc:Access); • Note that the JdbcOdbcDriver is part of the basic JDK distribution

32. Executing SQL statements • There are three different classes for constructing and executing SQL statements in JDBC: • Statementallows the execution of a static SQL command in a string format. • PreparedStatement allows repeated execution of a compiled statement. • CallableStatement allows you use stored procedures and take advantage of optimisation in the database system.

33. Statement class • An object of the Statement class takes a parameter string containing the SQL to be executed. • A Statement is created using the createStatement method of the Connection class, which returns a Statement object. • SQL is then executed by constructing a string of the SQL required and passing as an argument to a Statement method which will pass it the database and return a result.

34. Statement class • There are three possible Statement methods which can be used to execute an SQL statement string: • executeUpdate(String sql) - used for SQL statements which will update the database, such as INSERT, UPDATE or DELETE. • executeQuery(String sql) - used for SELECT statements • execute(String sql) - used when the SQL statement may be an update or query.

35. An example of executing a query Statement stmt = dbCon.createStatement(); String sql = "select * from " + table; ResultSet = stmt.executeQuery(sql);

36. Preparing a Statement Object • Creating a statement object is simple and follows the form: Class.forName(sun.jdbc.odbc.JdbcOdbcDriver); Connection jdbcConnection = DriverManager.getConnection(jdbc:odbc:Access); Statement sql = jdbcConnection.createStatement();

37. Using JDBC to Query a Database • use the statement handle to send standard SQL to the database and then parse through the results returned from the database. • Take a look at the following example. Class.forName(sun.jdbc.odbc.JdbcOdbcDriver); Connection jdbcConnection = DriverManager.getConnection (jdbc:odbc:Access); Statement sqlStatement = jdbcConnection.createStatement();

38. // We have seen all of the above before. // No surprises so far. in the next line, we // will simply create a string of SQL. String sql = "SELECT * FROM CUSTOMERS"; // Next we will attempt to send the SQL command to // the database. If it works, the database will // return to us a set of results that JDBC will // store in a ResultSet object. try { ResultSet results = sqlStatement.executeQuery(sql); // We simply go through the ResultSet object // one element at a time and print out the // fields. In this example, we assume that // the result set will contain three fields

39. while (results.next()) { System.our.println("Field One: " + results.getString(1) + "Field Two: " + results.getString(2) + "Field Three: " + results.getString(3)); } } // If there was a problem sending the SQL, // we will get this error. catch (Exception e) { System.out.println("Problem with Sending Query: " + e); } • Note that if the field is an Integer, you should use the getInt() method in ResultSet instead of getString()

40. Using JDBC to Modify a Database • Modifying a database is just as simple as querying a database However, instead of using executeQuery(), you use executeUpdate() and you don't have to worry about a result set. • example: Class.forName(sun.jdbc.odbc.JdbcOdbcDriver); Connection jdbcConnection = DriverManager.getConnection (jdbc:odbc:Access); Statement sqlStatement = jdbcConnection.createStatement(); // We have seen all of the above before. // No surprises so far. in the next line, we // will simply create a string of SQL.

41. String sql = "INSERT INTO CUSTOMERS + " (CustomerID, Firstname, LastName, Email)" + " VALUES (004, 'Selena', 'Sol' " + "'selena@extropia.com')"; // Now submit the SQL.... sqlStatement.executeUpdate(sql); • As you can see, there is not much to it. Add, modify and delete are all handled by the executeUpdate() method. You compose the SQL and send it through JDBC in one simple call.

42. Accessing column data • As each column may contain a different datatype we may need to use a ResultSet method specific to that datatype. • To retrieve a column with an integer value we could use getInt(int column, ), while to retrieve a column with a string value we would use getString(int column).

43. An example of accessing columns within rows: Statement stmt = dbCon.createStatement(); String sql = "select * from " + table; ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { String name = rs.getString(1); int age = rs.getInt(2); System.out.println(name + " " + age); } rs.close();

44. PreparedStatement • To avoid constructing a new Statement for each different SQL statement, PreparedStatements can be used. • These allow the creation of a parameterized statement for which values can be inserted in allowing the the same SQL statement to be used multiple times with different parameters.

45. An example of a PreparedStatement: String sql = "insert into " + table + " values(?, ?)"; PreparedStatement pstmt = dbCon.prepareStatemen(sql); while (!finished) { String name = getName(br); if (name.equals("end")) finished = true; else { int age = getAge(br) ; pstmt.setString(1, name); pstmt.setInt(2, age); pstmt.execute(); } } pstmt.close();

46. Commit and autoCommit • By default database will automatically commit the changes to the database. • This may not be desirable if you want to execute a number of statements and treat these as one transaction. • The Connection interface provides a method setAutoCommit() to allow to specify if you want the connection to automatically commit any transactions or if you prefer to do it manually. • If you turn off autoCommit, you can invoke the Connection method commit to commit changes to the database, or rollback to undo any changes since the last commit.

47. Mapping database types to Java types Java Type SQL Types String CHAR, VARCHAR, LONGVARCHAR int INTEGER double DOUBLE, FLOAT byte[] BINARY, VARBINARY, LONGVARBINARY sql.Date DATE sql.Time TIME sql.Timestamp TIMESTAMP