Nội dung

© 2012 Marty Hall Database Access with JDBC Originals of Slides and Source Code for Examples: http://courses.coreservlets.com/Course-Materials/java.html Customized Java EE Training: http://courses.coreservlets.com/ Java, JSF 2, PrimeFaces, Servlets, JSP, Ajax, jQuery, Spring, Hibernate, RESTful Web Services, Hadoop, Android. Developed and taught by well-known author and developer. At public venues or onsite at your location. © 2012 Marty Hall For live Java EE training, please see training courses at http://courses.coreservlets.com/. JSF 2, PrimeFaces, Servlets, JSP, Ajax (with jQuery), GWT, Android development, Java 6 and 7 programming, SOAP-based and RESTful Web Services, Spring, Hibernate/JPA, XML, Hadoop, and customized combinations of topics. Taught by the author of Core Servlets and JSP, More Servlets and JSP, and this tutorial. Available at public venues,Customized or customized versions can be held on-site at your Java EE Training: http://courses.coreservlets.com/ Java, JSF 2, PrimeFaces, Servlets, JSP, Ajax, jQuery, Spring, Hibernate, RESTful Web Services, Hadoop, Android. organization. Contact hall@coreservlets.com for details. Developed and taught by well-known author and developer. At public venues or onsite at your location. Overview • • • • • • • Overview of JDBC technology JDBC design strategies Using Apache Derby (Java DB) Seven basic steps in using JDBC Using JDBC from desktop Java apps Using JDBC from Web apps Prepared statements (parameterized commands) • Meta data • Transaction control 5 © 2012 Marty Hall Overview Customized Java EE Training: http://courses.coreservlets.com/ Java, JSF 2, PrimeFaces, Servlets, JSP, Ajax, jQuery, Spring, Hibernate, RESTful Web Services, Hadoop, Android. Developed and taught by well-known author and developer. At public venues or onsite at your location. JDBC Introduction • JDBC provides a standard library for accessing relational databases – API standardizes • • • • Way to establish connection to database Approach to initiating queries Method to create stored (parameterized) queries The data structure of query result (table) – Determining the number of columns – Looking up metadata, etc. – API does not standardize SQL syntax • You send strings; JDBC is not embedded SQL – JDBC classes are in the java.sql package – JDBC stands for “Java DataBase Connectivity” 7 On-line Resources • Sun’s JDBC Site – http://java.sun.com/javase/6/docs/technotes/guides/jdbc/ • JDBC Tutorial – http://java.sun.com/docs/books/tutorial/jdbc/ • API for java.sql – http://java.sun.com/javase/6/docs/api/java/sql/ package-summary.html • List of Available JDBC Drivers – http://developers.sun.com/product/jdbc/drivers • Or, just look in your database vendor’s documentation 8 JDBC Drivers • JDBC consists of two parts: – JDBC API, a purely Java-based API – JDBC Driver Manager,which communicates with vendor-specific drivers that perform the real communication with the database. • Point: translation to vendor format is performed on the client – No changes needed to server – Driver (translator) needed on client Java Application JDBC API JDBC Driver Manager JDBC Driver API JDBC-ODBC Bridge Vendor Specific ODBC Driver Vendor Specific JDBC Driver Database Database 9 JDBC Data Types JDBC Type BIT TINYINT SMALLINT INTEGER BIGINT REAL FLOAT DOUBLE BINARY VARBINARY LONGVARBINARY CHAR VARCHAR LONGVARCHAR 10 Java Type boolean byte short int long float double byte[] String JDBC Type NUMERIC DECIMAL DATE TIME TIMESTAMP CLOB BLOB ARRAY DISTINCT STRUCT REF JAVA_OBJECT Java Type BigDecimal java.sql.Date java.sql.Timestamp Clob Blob Array mapping of underlying type Struct Ref underlying Java class © 2012 Marty Hall Steps for Using JDBC Customized Java EE Training: http://courses.coreservlets.com/ Java, JSF 2, PrimeFaces, Servlets, JSP, Ajax, jQuery, Spring, Hibernate, RESTful Web Services, Hadoop, Android. Developed and taught by well-known author and developer. At public venues or onsite at your location. JDBC Design Strategies • In general, plan for changes to data access – Limit the data access to single area of code • Don’t distribute JDBC calls throughout the code • Plan ahead for changing from JDBC to Hibernate or another tool – Don’t return JDBC-specific (or Hibernate-specific) objects from the data-access layer • Return ordinary Java objects instead • In JDBC, plan for changes – Limit the definition of driver, URL, etc. to single location • Let database experts do their stuff – If database is complex, let database expert design the database and design the queries 12 Seven Basic Steps in Using JDBC 1. Load the driver – 2. 3. 4. 5. 6. 7. Not required in Java 6, so Java 6 needs only 6 steps. Define the Connection URL Establish the Connection Create a Statement object Execute a query Process the results Close the connection 13 JDBC Step 1: Load the Driver • Not required in Java 6 – In Java SE 6.0 and later (JDBC 4.0 and later), the driver is loaded automatically. • Java 5 and earlier – Load the driver class only. The class has a static initialization block that makes an instance and registers it with the DriverManager. try { Class.forName("com.mysql.jdbc.Driver"); Class.forName("oracle.jdbc.driver.OracleDriver"); } catch (ClassNotFoundException cnfe) { System.out.println("Error loading driver: " + cnfe); } 14 JDBC Step 2: Define the Connection URL • Remote databases – Format is “jdbc:vendorName:…” • Address contains hostname, port, and database name • Exact details given by supplier of JDBC driver • Embedded Derby database – The “Java DB” (i.e., Apache Derby) is bundled with Java 6 and can be used for a database embedded in the same Java VM that runs the app server. – Format is “jdbc:derby:databaseName” • Examples String host = "dbhost.yourcompany.com"; String dbName = "someName"; int port = 1234; String mySqlUrl = "jdbc:mysql//" + host + ":" + port + "/" + dbName; String embeddedDerbyUrl = "jdbc:derby" + dbName; 15 JDBC Step 3: Establish the Connection • Get the main connection Properties userInfo = new Properties(); userInfo.put("user", "jay_debesee"); userInfo.put("password", "secret"); Connection connection = DriverManager.getConnection(mySqlUrl, userInfo); • Optionally, look up info about the database DatabaseMetaData dbMetaData = connection.getMetaData(); String productName = dbMetaData.getDatabaseProductName(); System.out.println("Database: " + productName); String productVersion = dbMetaData.getDatabaseProductVersion(); System.out.println("Version: " + productVersion); 16 JDBC Step 4: Make a Statement • Idea – A Statement is used to send queries or commands • Statement types – Statement, PreparedStatement, CallableStatement • Details on other types given later • Example Statement statement = connection.createStatement(); 17 JDBC Step 5: Execute a Query • Idea – statement.executeQuery("SELECT … FROM …"); • This version returns a ResultSet – – – – – statement.executeUpdate("UPDATE …"); statement.executeUpdate("INSERT …"); statement.executeUpdate("DELETE…"); statement.execute("CREATE TABLE…"); statement.execute("DROP TABLE …"); • Example String query = "SELECT col1, col2, col3 FROM sometable"; ResultSet resultSet = statement.executeQuery(query); 18 JDBC Step 6: Process the Result • Important ResultSet methods – resultSet.next() • Goes to the next row. Returns false if no next row. – resultSet.getString("columnName") • Returns value of column with designated name in current row, as a String. Also getInt, getDouble, getBlob, etc. – resultSet.getString(columnIndex) • Returns value of designated column. First index is 1 (ala SQL), not 0 (ala Java). – resultSet.beforeFirst() • Moves cursor before first row, as it was initially. Also first – resultSet.absolute(rowNum) • Moves cursor to given row (starting with 1). Also last and afterLast. 19 JDBC Step 6: Process the Result • Assumption – Query was “SELECT first, last, address FROM…” • Using column names while(resultSet.next()) { System.out.printf( "First name: %s, last name: %s, address: %s%n", resultSet.getString("first"), resultSet.getString("last"), resultSet.getString("address")); } • Using column indices 20 while(resultSet.next()) { System.out.printf( "First name: %s, last name: %s, address: %s%n", resultSet.getString(1), resultSet.getString(2), resultSet.getString(3)); } JDBC Step 7: Close the Connection • Idea – When totally done, close the database connection. However, opening a new connection is typically much more expensive than sending queries on existing connections, so postpone this step as long as possible. – Many JDBC drivers do automatic connection pooling – There are also many explicit connection pool utilities • Example connection.close(); 21 © 2012 Marty Hall Using Apache Derby Customized Java EE Training: http://courses.coreservlets.com/ Java, JSF 2, PrimeFaces, Servlets, JSP, Ajax, jQuery, Spring, Hibernate, RESTful Web Services, Hadoop, Android. Developed and taught by well-known author and developer. At public venues or onsite at your location.