Solutions to the Try It Out Exercises in Java For Dummies, 7th Edition
by Barry Burd

Chapter 17: Using Java Database Connectivity

In this chapter:

"select * from ACCOUNTS where BALANCE > 30"

The output includes only those accounts whose balance is greater than 30.

Run the AddData program two times in a row

An error message tells you that can't run AddData a second time. When you created the table, your SQL statement said NAME VARCHAR(32) NOT NULL PRIMARY KEY. A primary key is a value that can't have duplicates in the table.

The first time you run the AddData program, you add a row with the name Barry Burd to the table. That's okay. But the second time you run the AddData program, you try to add a second row with the same name Barry Burd to the table. But since NAME is a primary key, having two rows with the same NAME (Barry Burd) is not allowed. So the program refuses to add the data.

Item name, price, and tax rate

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class CreateTable {

  public static void main(String args[]) {

    final String CONNECTION = "jdbc:derby:ItemsDatabase;create=true";

    try (Connection conn = DriverManager.getConnection(CONNECTION);
         Statement statement = conn.createStatement()) {

      statement.executeUpdate
          ("create table ITEMS                     " +
           "  (NAME VARCHAR(32) NOT NULL PRIMARY KEY, " +
           "   PRICE FLOAT,                   " +
           "   TAXRATE FLOAT                         )");
      System.out.println("ITEMS table created.");

    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
}

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class AddData {

  public static void main(String args[]) {

    final String CONNECTION = "jdbc:derby:ItemsDatabase";

    try (Connection conn = DriverManager.getConnection(CONNECTION);
         Statement statement = conn.createStatement()) {

      statement.executeUpdate
          ("insert into ITEMS values               " +
           "  ('Food', 10.00, 0.05) ");

      statement.executeUpdate
          ("insert into ITEMS values               " +
           "  ('Clothing', 20.00, 0.07)");

      System.out.println("Rows added.");

    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
}

import static java.lang.System.out;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.NumberFormat;

public class GetData {

  public static void main(String args[]) {
    NumberFormat currency = NumberFormat.getCurrencyInstance();
    final String CONNECTION = "jdbc:derby:ItemsDatabase";

    try (Connection conn = DriverManager.getConnection(CONNECTION);
         Statement statement = conn.createStatement();
         ResultSet resultset =
             statement.executeQuery("select * from ITEMS"))
    {

      while (resultset.next()) {
        out.print(resultset.getString("NAME"));
        out.print(", ");
        double price = resultset.getFloat("PRICE");
        double taxRate = resultset.getFloat("TAXRATE");
        out.println(currency.format(price * (1 + taxRate)));
      }

    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
}

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class DropTable {

  public static void main(String[] args) {
    final String CONNECTION = "jdbc:derby:ItemsDatabase";

    try (Connection conn = DriverManager.getConnection(CONNECTION);
         Statement statement = conn.createStatement()) {

      statement.executeUpdate("drop table ITEMS");

      System.out.println("ITEMS table dropped.");
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
}