14. Accessing Databases using JDBC

About this Tutorial –

Objectives –

This course is aimed at object-oriented developers (e.g. C++ or C#) who need to transition into Java. It is also aimed at those learning to program for the first time; the course covers the Java programming constructs and APIs quickly, focussing on the differences between Java and other OO languages.

Audience

This training course is aimed at OO developers who need to transition into Java.

Prerequisites

No previous experience in Java programming is required. But any experience you do have in programming will help. Also no experience in eclipse is required. But again any experience you do have with programming development environments will be a valuable.

Contents

The Java course cover these topics and more:

  • Flow Control: Decision making: if and if-else; The switch statement; Looping: for loops; while loops; do-while loops; for-each style loops; Assertionsv
  • Concurrency: Overview of multithreading; Creating new threads; Object locking; Using wait, notify, and notifyAll
  • Collections: Overview of Java SE collection classes; Generics; Using List-based collection classes; Using Set-based collection classes; Using Map-based collection classes; Collection techniques

Exam Preparation

The Java course will help you prepare for these certifications:

  • Oracle Certified Java Associate – Exam 1Z0-803
  • Oracle Certified Java Professional – Exam 1Z0-804

Download Solutions

HTML tutorial


Overview

Estimated Time – 2 Hours

Not what you are looking? Try the next tutorial – Multithreading

Lab 1: JDBC drivers and connections

Lab 1: JDBC drivers and connections
  1. What is JDBC?
    • JDBC is a standard Java API for accessing relational data
    • JDBC code typically resides at the server
      • Scalability, security, and access control
      • Ease of maintenance and deployment
      • Centralized business rules

        • T14P1

  2. JDBC Classes and Interfaces
    • JDBC is a set of standard Java classes and interfaces
      • Defined in the java.sql package
        import java.sql.*;
    • The JDBC interfaces specify a standard programming model, to access any RDBMS

      • T14P2

  3. JDBC Architecture
    • Each database vendor provides its own driver(s), to access a particular RDBMS
      • For example, there are JDBC drivers for Oracle, Sybase, DB2, etc.
      • Each driver implements the aforementioned Java interfaces, to provide access to a particular RDBMS product
    • The java.sql package defines a class named DriverManager, to coordinate JDBC drivers
      • Use DriverManager to load a driver, and connect to a database

        • T14P3

  4. Using the Derby Database
    • We use the Derby database engine
      • An open-source, free, pure Java Database Management System
      • Downloadable from https://db.apache.org/derby
    • We’ve already downloaded and unzipped it
      • See C:\JavaDev\Databases\Derby
      • Contains Derby JARS and runtime engine
    • We’ve provided a batch file to start the Derby engine
      • Run C:\JavaDev\Databases\StartDerby.bat

        • T14P4

  5. Load JDBC Drivers
    • Each JDBC driver is a Java class
      • You must load this class into the JVM first, before you can connect to the database or execute any SQL statements
    • For example, the following code loads the Derby client driver class
      • When the driver is loaded, it automatically registers itself with the JDBC Driver Manager
        try
        {
         Class.forName("org.apache.derby.jdbc.ClientDriver");
        }
        catch (ClassNotFoundException e)
        {
         System.out.println("Error loading JDBC driver: " + e);
        }
  6. Connecting to a Database
    • Once you have loaded the JDBC driver, you can use DriverManager to open a connection to a database
      • Call DriverManager.getConnection()
    • JDBC uses URI syntax to denote database names
      • The URI syntax is “jdbc::DatabaseName”
    • For example, the following code connects to the Derby sample database for this course
      Connection cnEmps = null;
      try
      {
       cnEmps = DriverManager.getConnection(
            "jdbc:derby://localhost:1527/C:/JavaDev/Databases/MyDatabase");
      }
      catch (SQLException e)
      {
       System.out.println("Error connecting to a database: " + e);
      }
Lab
  1. Connect to the database, and create a new Books table
    • Open the student project, StudentJDBC, and take a look at Main.java
    • In the main() method:
      • Load the JDBC driver to allow you to work with Derby databases. Note that you must also configure the project classpath to include the JAR file for the Derby JDBC driver (i.e. C:\JavaDev\Databases\Derby\lib\derbyclient.jar).
         Class.forName("org.apache.derby.jdbc.ClientDriver");
      • View code file.
      • Then get a connection to the database; use the global connection variable to hold this connection.
         connection = DriverManager.getConnection("jdbc:derby://localhost:1527/C:/JavaDev/Databases/MyDatabase");
      • View code file.
    • Now implement createTable(), so that the user can create a MySchema.Books table in the test database. Here’s a Java String variable that contains the SQL you need to execute.
       public static void createTable() throws SQLException {
        String sql = "CREATE TABLE MySchema.Books ( " +
        "Isbn INTEGER  NOT NULL, " +
        "Title VARCHAR(50) NOT NULL, " +
        "Price DECIMAL(6,2) NOT NULL, " +
        "CONSTRAINT PK_Books PRIMARY KEY (Isbn) " +
        ")";
        Statement st = connection.createStatement();
        st.executeUpdate(sql);
        System.out.println("Books table created");
       }
    • View code file.
    • Run the application. In the Console window at the bottom of the Eclipse IDE, verify that the application displays the list of options. Select option 1 to create the MySchema.Books table.

Lab 2: Statements and results

Lab 2: Statements and results
  1. Representing SQL statements in JDBC
    • JDBC defines three separate interfaces, to enable you to execute SQL statements in various ways

      • T14P5

  2. Executing a SELECT Statement
    • You can use a Statement object to execute a SQL SELECT statement
      • This is the simplest and most common task in many Java apps
        ResultSet rsEmps = null;
        try
        {
         Statement st = cnEmps.createStatement();
         rsEmps = st.executeQuery("SELECT Name, Salary FROM MySchema.Employees");
        }
        catch (SQLException e)
        {
         System.out.println("Error executing query: " + e);
        }
    • executeQuery() returns a ResultSet object
      • The ResultSet object holds the selected rows and columns
  3. Processing Query Results
    • ResultSet gives cursor-like access to the selected rows
      • JDBC 1.0 supports forward-only cursors
      • JDBC 2.0 adds support for backwards cursors
    • ResultSet has a suite of methods named getXxxx(), to get a column’s value as a specific data type
      • Access columns by name, or by column number (starting at 1!)
        ResultSet rsEmps =
         st.executeQuery("SELECT Name, Salary FROM MySchema.Employees");
        while (rsEmps.next() != false)
        {
         String   name  = rsEmps.getString(1);
         BigDecimal salary = rsEmps.getBigDecimal(2);
         // String   name  = rsEmps.getString("Name");
         // BigDecimal salary = rsEmps.getBigDecimal("Salary");
        }
  4. Mapping SQL Types to Java Types

    5. T14P6

  5. INSERT, DELETE, UPDATE
    • You can use a Statement object to execute a SQL INSERT, DELETE, or UPDATE statement
      • You can also execute DDL statements (such as CREATE TABLE) and DCL statements (such as GRANT PERMISSION)
        int rowsAffected = st.executeUpdate(
                      "INSERT MySchema.Employees " +
                      "VALUES ('Ryan', 750000, 'Wales')" );
        int rowsAffected = st.executeUpdate(
                      "DELETE MySchema.Employees " +
                      "WHERE Salary > 750001" );
        int rowsAffected = st.executeUpdate(
                      "UPDATE MySchema.Employees " +
                      "SET Salary = Salary * 1.25 " +
                      "WHERE Region = 'Wales'" );
    • Using Prepared Statements
      • A prepared statement is a statement whose SQL is only compiled the first time it is executed
        • Useful for optimization purposes, if you need to execute the same SQL statement several times with different parameters
      • Create a PreparedStatement in Java
        • Supply parameters each time you execute the statement
          PreparedStatement ps = cnEmps.prepareStatement(
                        "UPDATE MySchema.Employees " +
                        "SET Salary = Salary * ? " +
                        "WHERE Region = ?" );
          ps.setDouble(1, 1.25);
          ps.setString(2, "Wales");
          ps.executeUpdate();
          ps.setDouble(1, 1.10);
          ps.setString(2, "London");
          ps.executeUpdate();
    • Using Stored Procedures
      • Many enterprise Java apps use stored procedures to encapsulate SQL statements
        • Performance, security, access control, centralized business rules
      • Create a CallableStatement in Java
        • Supply parameters if necessary
          CallableStatement cs = cnEmps.prepareCall(
                        "{ call updateSalaries(?,?) }" );
          cs.setDouble(1, 1.25);
          cs.setString(2, "Wales");
          cs.executeUpdate();
        • cs.executeUpdate() should form the following SQL code:
          CREATE PROCEDURE updateSalaries(@rate DOUBLE, @reg VARCHAR)
          AS
           UPDATE MySchema.Employees
            SET Salary = Salary * @rate
            WHERE Region = @reg
    • Handling Output Parameters and Results
      • A stored procedure can assign output parameters, and can also return a result value
        • You must register these output params/result in your Java call
          CallableStatement cs = cnEmps.prepareCall(
               "{? = call getRegionInfo(?,?) }" );
          cs.registerOutParameter(1, Types.INTEGER);
          cs.setString(2, "Wales");
          cs.registerOutParameter(3, Types.NUMERIC);
          cs.execute();
          System.out.println("Number of emps in Wales: " + cs.getInt(1));
          System.out.println("Average salary is :" + cs.getBigDecimal(3));
        • And the SQL produced:
          CREATE PROCEDURE getRegionInfo(@reg VARCHAR,
                          @avgsal NUMERIC OUTPUT)
          AS
           DECLARE @count INTEGER
           SELECT @count = COUNT(Salary), @avgsal = AVG(Salary)
            FROM MySchema.Employees WHERE(Region = @reg) GROUP BY Region
           RETURN @count
    • Using Transactions
      • Transactions are essential in enterprise applications
        • Transactions ensure the database remains consistent, by grouping related SQL statements into “all-or-nothing” bundles
        • Commit or rollback all the statements within a transaction
      • There are several ways to manage transactions
        • Using JDBC function calls in your Java app
        • Using SQL in a stored proc (ROLLBACK/COMMIT TRANSACTION)
      • Using transactions in Java
        cnEmps.setAutoCommit(false);
        ...
        if (someError)
         cnEmps.rollback();
        else
         cnEmps.commit();
        cnEmps.setAutoCommit(true);
Lab
  1. Query the Books table
    • Implement the queryData() method, so that it gets the Isbn, Title, and Price for every book in the MySchema.Books table. Display the details for each book on the console window.
       public static void queryData() throws SQLException {
        String sql = "SELECT Isbn, Title, Price FROM MySchema.Books";
        Statement st = connection.createStatement();
        ResultSet rs = st.executeQuery(sql);
        while (rs.next() != false) {
       System.out.println("ISBN: " + rs.getInt(1));
       System.out.println("Title: " + rs.getString(2));
       System.out.println("Price: " + rs.getBigDecimal(3));
       System.out.println();
        }
       }
    • View code file.
    • Run your application when you are ready. Select option 2 to query the MySchema.Books table. It should be empty at the moment.
  2. Insert new books into the Books table
    • Implement the insertData() method, so that it inserts a series of new books into the MySchema.Books table. Here are some hints:
      • Create a PreparedStatement object containing the following SQL:”INSERT INTO MySchema.Books VALUES (?, ?, ?)”
         public static void insertData() throws SQLException, IOException {
          String sql = "INSERT INTO MySchema.Books VALUES (?, ?, ?)";
          PreparedStatement ps = connection.prepareStatement(sql);
      • View code file.
      • Set up a loop that gives the user an opportunity to enter data for many new books. On each loop iteration, ask the user to enter the isbn, title, and price for the new book. Convert the isbn into an int; leave the title as a String; and convert the price into a java.math.BigDecimal
          int isbn;
          while (true) {
         System.out.println("Enter book ISBN (-1 to quit): ");
         isbn = Integer.parseInt(br.readLine());
         if (isbn == -1) {
          break;
         }
         System.out.println("Enter book title: ");
         String title = br.readLine();
         System.out.println("Enter price: ");
         BigDecimal price = new BigDecimal(br.readLine());
      • View code file.
      • Assign these values to the parameters in the PreparedStatement, and then execute PreparedStatement
         ps.setInt(1, isbn);
         ps.setString(2, title);
         ps.setBigDecimal(3, price);
         int rowsAffected = ps.executeUpdate();
      • View code file.
      • Test the result of the insertion, to verify that the new row has been inserted successfully
         if (rowsAffected == 0) {
          System.out.println("The row was not inserted.");
         } else {
          System.out.println("The row was inserted.");
         }
      • View code file.
    • Run your application. Select option 3 to insert some new rows into the MySchema.Books table. Then select option 2, to ensure that the new rows have indeed been inserted.

Lab 3: Obtaining metadata

Lab 3: Obtaining metadata
  1. What is Metadata?
    • Metadata = “data about data”
    • JDBC defines two Java interfaces, to allow you to obtain metadata about the database or a particular result set

      • T14P7

  2. Getting Metadata for the Database
    • To obtain metadata for the database as a whole, call the getMetaData() method on a Connection object
      • This method returns a DatabaseMetaData object
        Connection cnEmps;
        ...
        try
        {
         DatabaseMetaData dbmd = cnEmps.getMetaData();
         String prodName   = dbmd.getDatabaseProductName();
         String driverName  = dbmd.getDriverName();
         String keywords   = dbmd.getSQLKeywords();
         boolean transAllowed = dbmd.supportsTransactions();
        }
        catch (SQLException e)
        {
         System.out.println("Error getting database metadata: " + e);
        }
  3. Getting Metadata for a Result Set
    • To obtain metadata for a particular result set, call the getMetaData() method on a ResultSet object
      • This method returns a ResultSetMetaData object
        try
        {
         ResultSet rsEmps = st.executeQuery("SELECT * FROM MySchema.Employees");
         ResultSetMetaData rsmd = rsEmps.getMetaData();
         int columnCount = rsmd.getColumnCount();
         for (int i = 1; i <= columnCount; i++)  {   System.out.println("Col name: " + rsmd.getColumnName(i));   System.out.println("Type: "   + rsmd.getColumnTypeName(i));   System.out.println("Nullable? " + rsmd.isNullable(i));  } } catch (SQLException e) {  System.out.println("Error getting resultset metadata: " + e); }

Lab 4: Additional techniques

Lab 4: Additional techniques
  1. Overview
    • Additional features:
      • Savepoints, for transactional control
      • Scrollable and updatable result sets, for programming simplicity
      • Batch updates, to reduce network costs
      • Rowsets
      • New SQL data types, e.g. to support BLOBs and CLOBs
    • Also, the JDBC Standard Extension offers optional extensions in a new package, javax.sql
      • Pooled database connections, for scalability
      • Named data sources, for ease of deployment and administration
      • Distributed transactions, for enterprise application integration
  2. Savepoints
    • JDBC provides a java.sql.Savepoint interface
      • Gives you additional transactional control
      • Supported by most modern database systems
    • When you set a savepoint, you define a logical rollback point within a transaction
      • If an error occurs past a savepoint:
      • You can use the rollback() method to undo either all the changes or only the changes made after the savepoint
    • The Connection object has several methods that help you manage savepoints:
      • Create a new savepoint, and return it as a Savepoint object
        SavePoint setSavepoint(String savepointName)
      • Delete the specified savepoint
        void releaseSavepoint(Savepoint savepoint)
      • Rollback to the specified savepoint
        void rollback(Savepoint savepoint)

      • Connection cn = ...;
      Savepoint savepoint1 = null;
      try {
       cn.setAutoCommit(false);
       Statement stmt = cn.createStatement();
       // Execute a SQL statement.
       String sql = "INSERT INTO MySchema.Employees (Name, Salary, Region) " +
              "VALUES ('Claire', 1000.00, 'UK')";
       stmt.executeUpdate(sql); 
       // Create a savepoint.
       savepoint1 = cn.setSavepoint("Savepoint1");
       // Execute another SQL statement.
       sql = "INSERT INTO MySchema.Employees (Name, Salary, Region) " +
          "VALUES ('Ruth', 2000.00, 'USA')";
       stmt.executeUpdate(sql);
       // If there is no error, commit the changes.
       cn.commit();
      }
      catch (SQLException se){
        // If there is any error, rollback to the savepoint.
        cn.rollback(savepoint1);
      }

  3. Scrollable and Updatable Result Sets
    • JDBC supports scrollable and/or updatable result sets
      • 1st parameter to createStatement() indicates the scrollability
      • 2nd parameter to createStatement() indicates the updatability
        Connection cn = DriverManager.getConnection(...);
        Statement st = cn.createStatement(
         ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        ResultSet rs = st.executeQuery(
         "SELECT EmployeeID, Name, Region FROM MySchema.Employees " +
         "WHERE REGION='London'");
        rs.afterLast();
        while (rs.previous() != false) {
         System.out.println("Employee name: " + rs.getString("Name"));
         rs.updateString("Region", "Bracknell");
         rs.updateRow();
        }
  4. RowSets
    • JDBC has a RowSet interface
      • Defined in the javax.sql.rowset package
      • Follows the JavaBean convention for properties and events
      • Also supports scrollable and updateable result sets
    • There are two categories of RowSet:

      • T14P8

    • Example of how to use a rowset in Java SE 7:
      import java.sql.*;
      import javax.sql.rowset.JdbcRowSet;
      import javax.sql.rowset.RowSetFactory;
      import javax.sql.rowset.RowSetProvider;
      ...
      // Create a JdbcRowSet object.
      RowSetFactory rowSetFactory = RowSetProvider.newFactory();
      JdbcRowSet jdbcRowSet = rowSetFactory.createJdbcRowSet();
      // Configure the JdbcRowSet object.
      jdbcRowSet.setUrl("jdbc:derby://localhost:1527/C:/JavaDev/Databases/MyDatabase");
      jdbcRowSet.setType(ResultSet.TYPE_SCROLL_INSENSITIVE);
      jdbcRowSet.setCommand("SELECT Name, Salary FROM MySchema.Employees");
      // Execute the command in the JdbcRowSet, and iterate through the results.
      jdbcRowSet.execute();
      while (jdbcRowSet.next()) {
       System.out.printf("%s earns %.2f\n", jdbcRowSet.getString(1),
                          jdbcRowSet.getBigDecimal(2));
      }
  5. Batch Updates
    • JDBC supports batch updates
      • Use metadata to see if a driver/database supports batch updates
      • Call addBatch() to add SQL statements to a batch
      • Call executeBatch() to execute the batched statements
        Connection cnEmps = DriverManager.getConnection(...);
        DatabaseMetaData dbmd = cnEmps.getMetaData();
        if (dbmd.supportsBatchUpdates()) {
         cnEmps.setAutoCommit(false);
         Statement st = cnEmps.createStatement();
         st.addBatch("INSERT MySchema.Employees VALUES('Craig', 99000, 'Wales')");
         st.addBatch("DELETE MySchema.Employees WHERE Salary > 750001");
         int[] rowsAffected = st.executeBatch();
         cnEmps.commit();
        }
  6. Additional SQL Data Types
    • JDBC supports the following additional SQL data types
      • java.sql.Blob : BLOB (binary large object)
      • java.sql.Clob : CLOB (character large object)
      • java.sql.Array : SQL array of primitive/structured types
      • java.sql.Struct : Structured type
      • java.sql.Ref : Reference to structured type
    • Corresponding get/set methods, for example:
      • getBlob() and setBlob()
      • getClob() and setClob()
  7. Summary
    • JDBC drivers and connections
      • JDBC is a standard API, for accessing data in any RDMBS
      • Load a JDBC driver, and use the driver manager to get a connection to a database
    • Statements and results
      • JDBC defines three statement-related interfaces: Statement, PreparedStatement, CallableStatement
    • Obtaining metadata
      • Call aConnection.getMetaData() to get database metadata
      • Call aResultSet.getMetaData() to get resultset metadata
    • Additional techniques
      • Additional APIs, offer new features such as scrollable/updatable result sets

 

Well done. You have completed the tutorial in the Java course. The next tutorial is

15. Multithreading


Back to beginning
Copyright © 2016 TalkIT®






If you liked this post, please comment with your suggestions to help others.
If you would like to see more content like this in the future, please fill-in our quick survey.
Scroll to Top