jdbc

1: JDBC/ODBC Overview Introduction to JDBC/ODBC

3: Open ODBC Standards The X/Open Group and ISO have made ODBC a standard, though there are differences from this standard and the Microsoft implementation. ODBC can be used on UNIX/Linux systems using products provided by third party software vendors and the Free Software Foundation. OpenAccess, from http://www.atinet.com provides UNIX clients ODBC connectivity to Windows based databases. unixODBC, from http://www.unixodbc.org is a freeware implementation of ODBC available for Linux/FreeBSD systems. ODBC relies on data drivers to convert the ODBC calls to different database formats. At development time, the application developer only needs to know the ODBC calls to connect to a database, execute SQL statements, and retrieve results.

4: n-Tier Architectures 1-tier 2-tier 3-tier

5: Overview Setup a database for Web access Load a driver Connect to database Create Statement Execute Statement Process the results Remember to catch errors

6: Web access to a Database Basic steps Create a Database (e.g., MS Access or FoxPro) Create the data source using the ODBC administrator In Windoze 98, etc.: Open Start?Control Panel?ODBC Data Sources (32bit) OR in WinXP, etc.: Open Start?Control Panel?Administrative Tools?ODBC Data Source Administrator Click System DSN tab then Add button

9: Give new Data Source Name, Description and path of the Database then press OK

10: To Configure/Re-Configure a Connection press File DSN tab, Add, choose Driver and give same data source as System DSN

11: Give the path of the database

12: JDBC Java DB connectivity API Similar to ODBC Why do you need it: Pure Java Simple API Well….Multi-platform

13: JDBC Architecture Supports n-Tier architectures Tabular data handling SQL but not only SQL

16: The Driver manager

17: Java JDBC Programming Model

18: Java JDBC Programming Steps

19: The JDBC Steps 1. Importing Packages 2. Registering the JDBC Drivers 3. Opening a Connection to a Database 4. Creating a Statement Object 5. Executing a Query and Returning a Result Set Object 6. Processing the Result Set 7. Closing the Result Set and Statement Objects 8. Closing the Connection

20: 1: Importing Packages // // Program name: LecExample_1a.java // Purpose: Basic selection using prepared statement ////Import packagesimport java.sql.*; //JDBC packages import java.math.*; import java.io.*;import oracle.jdbc.driver.*;

21: 2: Registering JDBC Drivers class LecExample_1a {  public static void main (String args []) throws SQLException { // Load Oracle driver DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());

22: JDBC Universal Resource Locators (URLs) Provides a way of identifying a database Allow different drivers to use different schemes for naming database Allow driver writers to encode all necessary connection information within them Allow a level of indirection

24: Step 3: Connecting to a Database, part II The DriverManager allows you to connect to a database using the specified JDBC driver, database location, database name, username and password. It returns a Connection object which can then be used to communicate with the database. The password is passed to the database in plain text and therefore anyone who can see the .java or .class file can find out the password! Connection connection = DriverManager.getConnection("jdbc:mysql://www3.unl.ac.uk:3306/kingj1","kingj1","secret");

25: 3: Opening connection to a Database //Prompt user for username and password String user; String password; user = readEntry("username: "); password = readEntry("password: ");  // Connect to the local database Connection conn = DriverManager.getConnection ("jdbc:oracle:thin:@aardvark:1526:teach", user, password);

26: 4. Creating a Statement Object // Query the hotels table for resort = 'palma nova’ // Please notice the essential trim PreparedStatement pstmt = conn.prepareStatement ("SELECT hotelname, rating FROM hotels WHERE trim(resort) = ?"); pstmt.setString(1, "palma nova");

27: Step 5: Executing SQL (Querying the Database) SQL which queries the database uses the executeQuery method of Statement ExecuteQuery returns a ResultSet which we can then look at to see what records matched the query Lets assume the database has a table called test with a single column called value We will list the entire table in SQL this would be SELECT * FROM test ResultSet rs=statement.executeQuery("SELECT * FROM test");

30: 5. Executing a Query,  Returning a Result Set Object & 6. Processing the Result Set ResultSet rset = pstmt.executeQuery ();  // Print query results while (rset.next ())  System.out.println (rset.getString (1)+" "+ rset.getString(2));

31: 7. Closing the Result Set and Statement Objects 8. Closing the Connection // close the result set, statement, and the connection rset.close(); pstmt.close(); conn.close(); }

33: Appendix:ODBC JDBC Example using Microsoft Access From Core Java 2 by Cay Horstmann Screen displays by Dongchan Choo

34: Example: Make DB Create a MS Access file called “mssql1” Create DSN for “mssql1” Go to Start (in MS window) Go to control panel Go to Data Source ODBC

35: Example: Make DB(con’t) Select “System DSN” Select “Add”

36: Example: Make DB(con’t) Select “MS Access Driver”

37: Example: Make DB(con’t) Input Data Source Name: mssql1 Select “source”

38: Example: Make DB(con’t) Select Database Name by directory

39: Example: Make DB Create “Books1.dat” file using Note Pad

40: Example: Make DB Create “MakeDB1.java” file import java.net.*; import java.sql.*; import java.io.*; import java.util.*; class MakeDB1 { public static void main (String args[]) { try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); Connection con = DriverManager.getConnection( "jdbc:odbc:mssql1", "", "");

41: Example: Make DB Statement stmt = con.createStatement(); String tableName = ""; if (args.length > 0) tableName = args[0]; else { System.out.println("Usage: MakeDB TableName"); System.exit(0); } BufferedReader in = new BufferedReader(new FileReader(tableName + ".dat"));

42: Example: Make DB createTable(tableName, in, stmt); showTable(tableName, stmt); in.close(); stmt.close(); con.close(); }

43: Example: Make DB } } catch (IOException ex) { System.out.println("Exception: " + ex); ex.printStackTrace ();} catch (Exception e) { System.out.println("Error: " + e.toString() + e.getMessage());} }

44: Example: Make DB public static void createTable(String tableName, BufferedReader in, Statement stmt) throws SQLException, IOException { String line = in.readLine(); String command = "CREATE TABLE " + tableName + "(" + line + ")"; stmt.executeUpdate(command); while ((line = in.readLine()) != null) { command = "INSERT INTO " + tableName + " VALUES (" + line + ")"; stmt.executeUpdate(command); } }

45: public static void showTable(String tableName, Statement stmt) throws SQLException { String query = "SELECT * FROM " + tableName; ResultSet rs = stmt.executeQuery(query); ResultSetMetaData rsmd = rs.getMetaData(); int columnCount = rsmd.getColumnCount(); while (rs.next()) { for (int i = 1; i <= columnCount; i++) { if (i > 1) System.out.print(", "); System.out.print(rs.getString(i)); } System.out.println(); } rs.close(); } } Example: Make DB

47: Standard Query Language (SQL) Composed of two categories: Data Manipulation Language (DML) used to manipulate the data select delete update Data Definition Language (DDL) create database create table drop database

48: Data Manipulation Language SELECT - query the database select * from customer where id > 1001 INSERT - adds new rows to a table. Insert into customer values (1009, ‘John Doe’) DELTE - removes a specified row delete UPDATE - modifies an existing row update customers set amount = 10 where id > 1003

49: Data Definition Language CREATE DATABASE - allows you to create a database CREATE TABLE - allows you to create a table definition in a database DROP TABLE - removes a table from a database ALTER TABLE - modifies the definition of a table in a database

50: Transactions A transaction consists of one or more statements that have been executed, completed, and then either committed or rolled back (commit or rollback). A new connection is in auto-commit mode by default. If auto-commit mode is disabled, the transaction will not terminate until the method commit or rollback is invoked JDBC-compliant drivers must support transactions DatabaseMetaData gives information on the level of transaction support

51: Stored Procedures A Stored Procedure is written in a metalanguage defined by the DBMS vendor Used to batch or group multiple SQL statements that are stored in executable form at the database Written in some internal programming language of the DBMS: Oracle’s PL/SQL Sybase’s Transact-SQL THESE LANGUAGES ARE NON-PORTABLE from one DBMS to another (with the exception of the SQLJ standard, which allows you to write SQL in standard Java and have that understood by any DBMS that supports the SQLJ standard).

52: Why Use Stored Procedures? Faster Execution of SQL (compiled and in-memory stored query plan) Reduced Network Traffic Modular Programming Automation of complex or sensitive transactions Syntax checking at time of creation of SP Syntax supports if, else, while loops, goto, local variables, etc., all of which dynamic SQL doesn’t have

53: SQLJ vs JDBC comparison Java and SQLJ versus PL/SQL Java (including SQLJ) in Oracle applications does not replace PL/SQL. Java and PL/SQL are complementary to each other in the needs they serve. While PL/SQL and Java can both be used to build database applications, the two languages were designed with different intents and, as a result, are suited for different kinds of applications: PL/SQL is a better solution for SQL-intensive applications. PL/SQL is optimized for SQL, and so SQL operations are faster in PL/SQL than in Java. Also, PL/SQL uses SQL datatypes directly, while Java applications must convert between SQL datatypes and Java types. Java, with its superior programming model, is a better solution for logic-intensive applications. Furthermore, Java's more general type system is better suited than PL/SQL for component-oriented applications. Oracle provides easy interoperability between PL/SQL and Java, ensuring that you can take advantage of the strengths of both languages. PL/SQL programs can transparently call Java stored procedures, enabling you to build component-based Enterprise JavaBeans and CORBA applications. PL/SQL programs can have transparent access to a wide variety of existing Java class libraries through trivial PL/SQL call specifications. Java programs can call PL/SQL stored procedures and anonymous blocks through JDBC or SQLJ. In particular, SQLJ provides syntax for calling stored procedures and functions from within a SQLJ statement, and also supports embedded PL/SQL anonymous blocks within a SQLJ statement. Java and SQLJ versus PL/SQL Java (including SQLJ) in Oracle applications does not replace PL/SQL. Java and PL/SQL are complementary to each other in the needs they serve. While PL/SQL and Java can both be used to build database applications, the two languages were designed with different intents and, as a result, are suited for different kinds of applications: PL/SQL is a better solution for SQL-intensive applications. PL/SQL is optimized for SQL, and so SQL operations are faster in PL/SQL than in Java. Also, PL/SQL uses SQL datatypes directly, while Java applications must convert between SQL datatypes and Java types. Java, with its superior programming model, is a better solution for logic-intensive applications. Furthermore, Java's more general type system is better suited than PL/SQL for component-oriented applications. Oracle provides easy interoperability between PL/SQL and Java, ensuring that you can take advantage of the strengths of both languages. PL/SQL programs can transparently call Java stored procedures, enabling you to build component-based Enterprise JavaBeans and CORBA applications. PL/SQL programs can have transparent access to a wide variety of existing Java class libraries through trivial PL/SQL call specifications. Java programs can call PL/SQL stored procedures and anonymous blocks through JDBC or SQLJ. In particular, SQLJ provides syntax for calling stored procedures and functions from within a SQLJ statement, and also supports embedded PL/SQL anonymous blocks within a SQLJ statement.

54: Use SQLJ to write your program when you want to be able to check your program for errors at translation-time rather than at run-time. you want to write an application that you can deploy to another database. Using SQLJ, you can customize the static SQL for that database at deployment-time. you are working with a database that contains compiled SQL. You will want to use SQLJ because you cannot compile SQL statements in a JDBC program.

55: Use JDBC to write your program when your program uses dynamic SQL. For example, you have a program that builds queries on-the-fly or has an interactive component. you do not want to have a SQLJ layer during deployment or development. For example, you might want to download only the JDBC Thin driver and not the SQLJ runtime libraries to minimize download time over a slow link.

56: SQLJ static and non-static SQL The standard covers only static SQL operations those that are predefined and do not change in real-time as a user runs the application of course the data values that are transmitted can change dynamically! Oracle SQLJ offers extensions to support dynamic SQL operations those that are not predefined, where the operations themselves can change in real-time. It is possible to use dynamic SQL operations through JDBC code or PL/SQL code within a SQLJ application. Typical applications contain much more static SQL than dynamic SQL.

57: Java and SQLJ versus PL/SQL I Java and PL/SQL are complementary. Suited for different kinds of applications. PL/SQL is better for SQL-intensive applications. Optimized for SQL, and so SQL operations are faster in PL/SQL than in Java. Uses SQL datatypes directly, while Java applications must convert between SQL datatypes and Java types. Java, is better for logic-intensive applications. Superior programming model. Java's more general type system is better suited than PL/SQL for component-oriented applications. Java (including SQLJ) in Oracle applications does not replace PL/SQL. Java and PL/SQL are complementary to each other in the needs they serve. While PL/SQL and Java can both be used to build database applications, the two languages were designed with different intents and, as a result, are suited for different kinds of applications: PL/SQL is a better solution for SQL-intensive applications. PL/SQL is optimized for SQL, and so SQL operations are faster in PL/SQL than in Java. Also, PL/SQL uses SQL datatypes directly, while Java applications must convert between SQL datatypes and Java types. Java, with its superior programming model, is a better solution for logic-intensive applications. Furthermore, Java's more general type system is better suited than PL/SQL for component-oriented applications. Java (including SQLJ) in Oracle applications does not replace PL/SQL. Java and PL/SQL are complementary to each other in the needs they serve. While PL/SQL and Java can both be used to build database applications, the two languages were designed with different intents and, as a result, are suited for different kinds of applications: PL/SQL is a better solution for SQL-intensive applications. PL/SQL is optimized for SQL, and so SQL operations are faster in PL/SQL than in Java. Also, PL/SQL uses SQL datatypes directly, while Java applications must convert between SQL datatypes and Java types. Java, with its superior programming model, is a better solution for logic-intensive applications. Furthermore, Java's more general type system is better suited than PL/SQL for component-oriented applications.

58: Interoperability: SQLJ and PL/SQL PL/SQL programs transparently call Java stored procedures, enabling you to build component-based Enterprise JavaBeans and CORBA applications. have transparent access to a wide variety of existing Java class libraries through trivial PL/SQL call specifications. Java programs call PL/SQL stored procedures and anonymous blocks through JDBC or SQLJ. SQLJ provides syntax for calling stored procedures and functions from within a SQLJ statement, and also supports embedded PL/SQL anonymous blocks within a SQLJ statement. Oracle provides easy interoperability between PL/SQL and Java, ensuring that you can take advantage of the strengths of both languages. PL/SQL programs can transparently call Java stored procedures, enabling you to build component-based Enterprise JavaBeans and CORBA applications. PL/SQL programs can have transparent access to a wide variety of existing Java class libraries through trivial PL/SQL call specifications. Java programs can call PL/SQL stored procedures and anonymous blocks through JDBC or SQLJ. In particular, SQLJ provides syntax for calling stored procedures and functions from within a SQLJ statement, and also supports embedded PL/SQL anonymous blocks within a SQLJ statement. Oracle provides easy interoperability between PL/SQL and Java, ensuring that you can take advantage of the strengths of both languages. PL/SQL programs can transparently call Java stored procedures, enabling you to build component-based Enterprise JavaBeans and CORBA applications. PL/SQL programs can have transparent access to a wide variety of existing Java class libraries through trivial PL/SQL call specifications. Java programs can call PL/SQL stored procedures and anonymous blocks through JDBC or SQLJ. In particular, SQLJ provides syntax for calling stored procedures and functions from within a SQLJ statement, and also supports embedded PL/SQL anonymous blocks within a SQLJ statement.

59: ReadEntry method (for completeness) // Method: readEntry // Purpose: to read a string from the user and return it // Input: The prompt string // Output: User entry static String readEntry (String prompt) { try{ StringBuffer buffer = new StringBuffer (); System.out.print (prompt); System.out.flush (); int c = System.in.read (); while (c != '\n' && c != -1){ buffer.append ((char)c); c = System.in.read (); } return buffer.toString ().trim (); } catch (IOException e){ return ""; } }}

62: Outline Java servlets Advantages of servlets Servlet structure Servlet examples Handling the client request Form Data HTTP request headers

63: A Servlet’s Job Read explicit data sent by client (form data) Read implicit data sent by client (request headers) Generate the results Send the explicit data back to client (HTML) Send the implicit data to client(status codes and response headers)

64: Why Build Web Pages Dynamically? The Web page is based on data submitted by the user E.g., results page from search engines and order-confirmation pages at on-line stores The Web page is derived from data that changes frequently E.g., a weather report or news headlines page The Web page uses information from databases or other server-side sources E.g., an e-commerce site could use a servlet to build a Web page that lists the current price and availability of each item that is for sale.

65: The Advantages of Servlets Over “Traditional” CGI Efficient Threads instead of OS processes, one servlet copy, persistence Convenient Lots of high-level utilities Powerful Sharing data, pooling, persistence Portable Run on virtually all operating systems and servers Secure No shell escapes, no buffer overflows Inexpensive There are plenty of free and low-cost servers.

66: Simple Servlet Template import java.io.*; import javax.servlet.*; import javax.servlet.http.*; public class ServletTemplate extends HttpServlet { public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // Use "request" to read incoming HTTP headers // (e.g. cookies) and HTML form data (query data) // Use "response" to specify the HTTP response status // code and headers (e.g. the content type, cookies). PrintWriter out = response.getWriter(); // Use "out" to send content to browser } }

67: A Simple Servlet That Generates Plain Text import java.io.*; import javax.servlet.*; import javax.servlet.http.*; public class HelloWorld extends HttpServlet { public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { PrintWriter out = response.getWriter(); out.println("Hello World"); } }

68: A Servlet That Generates HTML public class HelloWWW extends HttpServlet { public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html"); PrintWriter out = response.getWriter(); String docType = "<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.0 " + "Transitional//EN\">\n"; out.println(docType + "<HTML>\n" + "<HEAD><TITLE>Hello WWW</TITLE></HEAD>\n" + "<BODY>\n" + "<H1>Hello WWW</H1>\n" + "</BODY></HTML>"); } }

69: The Servlet Life Cycle init Executed once when the servlet is first loaded. Not called for each request. service Called in a new thread by server for each request. Dispatches to doGet, doPost, etc. Do not override this method! doGet, doPost, doXxx Handles GET, POST, etc. requests. Override these to provide desired behavior. destroy Called when server deletes servlet instance. Not called after each request.

70: Handling the Client Request: Form Data

71: The Role of Form Data Example URL at online travel agent http://host/path?user=Marty+Hall&origin=bwi&dest=lax Names come from HTML author; values usually come from end user Parsing form (query) data in traditional CGI Read the data one way (QUERY_STRING) for GET requests, another way (standard input) for POST requests Chop pairs at ampersands, then separate parameter names (left of the equal signs) from parameter values (right of the equal signs) URL decode values (e.g., "%7E" becomes "~") Need special cases for omitted values(param1=val1¶m2=¶m3=val3) and repeated parameters (param1=val1¶m2=val2¶m1=val3)

72: Creating Form Data: HTML Forms <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML> <HEAD><TITLE>A Sample Form Using GET</TITLE></HEAD> <BODY BGCOLOR="#FDF5E6"> <H2 ALIGN="CENTER">A Sample Form Using GET</H2> <FORM ACTION="http://localhost:8088/SomeProgram"> <CENTER> First name: <INPUT TYPE="TEXT" NAME="firstName" VALUE="Joe"><BR> Last name: <INPUT TYPE="TEXT" NAME="lastName" VALUE="Hacker"><P> <INPUT TYPE="SUBMIT"> <!-- Press this to submit form --> </CENTER> </FORM> </BODY></HTML> See CSAJSP Chapter 16 for details on forms

73: HTML Form: Initial Result

74: Reading Form Data In Servlets request.getParameter("name") Returns URL-decoded value of first occurrence of name in query string Works identically for GET and POST requests Returns null if no such parameter is in query request.getParameterValues("name") Returns an array of the URL-decoded values of all occurrences of name in query string Returns a one-element array if param not repeated Returns null if no such parameter is in query request.getParameterNames() Returns Enumeration of request params

75: An HTML Form With Three Parameters <FORM ACTION="/servlet/coreservlets.ThreeParams"> First Parameter: <INPUT TYPE="TEXT" NAME="param1"><BR> Second Parameter: <INPUT TYPE="TEXT" NAME="param2"><BR> Third Parameter: <INPUT TYPE="TEXT" NAME="param3"><BR> <CENTER><INPUT TYPE="SUBMIT"></CENTER> </FORM>

76: Reading the Three Parameters public class ThreeParams extends HttpServlet { public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html"); PrintWriter out = response.getWriter(); String title = "Reading Three Request Parameters"; out.println(ServletUtilities.headWithTitle(title) + "<BODY BGCOLOR=\"#FDF5E6\">\n" + "<H1 ALIGN=CENTER>" + title + "</H1>\n" + "<UL>\n" + " <LI><B>param1</B>: " + request.getParameter("param1") + "\n" + " <LI><B>param2</B>: " + request.getParameter("param2") + "\n" + " <LI><B>param3</B>: " + request.getParameter("param3") + "\n" + "</UL>\n" + "</BODY></HTML>"); }}

77: Reading Three Parameters:Result

78: Filtering Strings for HTML-Specific Characters You cannot safely insert arbitrary strings into servlet output < and > can cause problems anywhere & and " can cause problems inside of HTML attributes You sometimes cannot manually translate The string is derived from a program excerpt or another source where it is already in some standard format The string is derived from HTML form data Failing to filter special characters from form data makes you vulnerable to cross-site scripting attack http://www.cert.org/advisories/CA-2000-02.html http://www.microsoft.com/technet/security/crssite.asp