Blakes 21 Days Chapter 18 Document


Day 18, Accessing Databases with JDBC 4.2 and Derby

Java Database Connectivity

Database Drivers

Examining a Database

Figure 18.1 - Starting the Java DB database server. - goes here

540

Figure 18.2 - Dealing with a Security Manager error. - goes here

542

Figure 18.3 - Launching a Java DB server with NetBeans. - goes here

544

Figure 18.4 - Examining tables in a Java DB database. - goes here

546

Figure 18.5 - Displaying database records in a table. - goes here

548

Reading Records from a Database

First Program - Listing 18.1

Listing 18.1 - The Full Text of CustomerReporter.java

An Explanation

Figure 18.6 - Reading records from a Java DB database. - goes here

550

Writing Records to a Database

Figure 18.7 - Creating a new database table in NetBeans. - goes here

554

Second Program - Listing 18.2

Listing 18.2 - The Full Text of QuoteData.java

package com.java21days;

import java.io.*;
import java.net.*;
import java.sql.*;
import java.util.*;

public class QuoteData {
    private String ticker;

    public QuoteData(String inTicker) {
        ticker = inTicker;
    }

    private String retrieveQuote() {
        StringBuilder builder = new StringBuilder();
        try {
            URL page = new URL(
                "http://quote.yahoo.com/d/quotes.csv?s=" +
                ticker + "&f=sl1d1t1c1ohgv&e=.csv");
            String line;
            URLConnection conn = page.openConnection();
            conn.connect();
            InputStreamReader in = new InputStreamReader(
                conn.getInputStream());
            BufferedReader data = new BufferedReader(in);
            while ((line = data.readLine()) != null) {
                builder.append(line);
                builder.append("\n");
            }
        } catch (MalformedURLException mue) {
            System.out.println("Bad URL: " + mue.getMessage());
        } catch (IOException ioe) {
            System.out.println("IO Error:" + ioe.getMessage());
        }
        return builder.toString();
    }

    private void storeQuote(String data) {
        StringTokenizer tokens = new StringTokenizer(data, ",");
        String[] fields = new String[9];
        for (int i = 0; i < fields.length; i++) {
            fields[i] = stripQuotes(tokens.nextToken());
        }
        String datasource = "jdbc:derby://localhost:1527/sample";
        try (
            Connection conn = DriverManager.getConnection(
                datasource, "app", "app")
            ) {

            Class.forName("org.apache.derby.jdbc.ClientDriver");
            PreparedStatement prep2 = conn.prepareStatement(
                "insert into " +
                "APP.STOCKS(TICKER, PRICE, DATE, CHANGE, LOW, " +
                "HIGH, PRICEOPEN, VOLUME) " +
                "values(?, ?, ?, ?, ?, ?, ?, ?)");
            prep2.setString(1, fields[0]);
            prep2.setString(2, fields[1]);
            prep2.setString(3, fields[2]);
            prep2.setString(4, fields[4]);
            prep2.setString(5, fields[5]);
            prep2.setString(6, fields[6]);
            prep2.setString(7, fields[7]);
            prep2.setString(8, fields[8]);
            prep2.executeUpdate();
            prep2.close();
            conn.close();
        } catch (SQLException sqe) {
            System.out.println("SQL Error: " + sqe.getMessage());
        } catch (ClassNotFoundException cnfe) {
            System.out.println(cnfe.getMessage());
        }
    }

    private String stripQuotes(String input) {
        StringBuilder output = new StringBuilder();
        for (int i = 0; i < input.length(); i++) {
            if (input.charAt(i) != '\"') {
                output.append(input.charAt(i));
            }
        }
        return output.toString();
    }

    public static void main(String[] arguments) {
        if (arguments.length < 1) {
            System.out.println("Usage: java QuoteData ticker");
            System.exit(0);
        }
        QuoteData qd = new QuoteData(arguments[0]);
        String data = qd.retrieveQuote();
        qd.storeQuote(data);
    }
}

The Explanation

Figure 18.8 - Records in the STOCKS table. - goes here

556

Moving Through ResultSets



Summary

Q & A

Quiz - Questions

  1. What does a Statement object represent in a database program ?
    1. A connection to a database
    2. A database query written in Structured Query Language
    3. A data source
  2. Which Java class represents SQL statements that are compiled before they are executed ?
    1. Statement
    2. PreparedStatement
    3. ResultSet
  3. What does the Class.forName(String) method accomplish ?
    1. It provides the name of a class.
    2. It loads a database driver that can be used to access a database.
    3. It deletes an object.

Answers

  1. B. The class, part of the java.sql package, represents a SQL statement.
  2. B. Because it is compiled, PreparedStatement is a better choice when you need to execute the same SQL query numerous times.
  3. B. This static method loads a database driver.

Certification Practice

  1. Which line in this program prevents it from compiling successfully ?
    1. count++;
    2. return new ArrayClass();
    3. public static void main(String arguments[]) {
    4. int count = -1;

Exercise