import java.sql.*;

public class DBTesterApp {

    private static Connection connection;

    public static void main(String args[]) {        
        // open connection
        try {
            String dbUrl = "jdbc:sqlite:products.sqlite";
            connection = DriverManager.getConnection(dbUrl);        
        } catch (SQLException e) {
            System.err.println(e);
            return;
        }        
       
        // select data from database
        printProducts();
        printFirstProduct();
        printProductByCode("jsp");

        // modify data in the database
        Product p = new Product("test", "Test Product", 49.50);
        insertProduct(p);
        printProducts();
        deleteProduct(p);
        printProducts();

        // close connection
        try {
            connection.close();
        } catch (SQLException e) {
            System.out.println(e);            
        }
    }

    public static void printProducts() {
        try (Statement statement = connection.createStatement();
             ResultSet rs = statement.executeQuery("SELECT * FROM Products")) {
            Product p;

            System.out.println("Product list:");
            while (rs.next()) {
                String code = rs.getString("ProductCode");
                String description = rs.getString("Description");
                double price = rs.getDouble("Price");

                p = new Product(code, description, price);

                printProduct(p);
            }
            System.out.println();
        } catch (SQLException e) {
            System.out.println(e);
        }
    }

    public static void printFirstProduct() {
        Product p = new Product();

        // add code that prints the record for the first product in the Products table
        System.out.println("First product:");
        printProduct(p);
        System.out.println();
    }

    public static void printProductByCode(String productCode) {
        Product p = new Product();

        // add code that prints the product with the specified code
        System.out.println("Product by code: " + productCode);
        printProduct(p);
        System.out.println();
    }

    public static void insertProduct(Product p) {
        System.out.println("Insert test: ");

        // add code that inserts the specified product into the database
        // if a product with the specifed code already exists, display an error message
        printProduct(p);
        System.out.println();
    }

    private static void deleteProduct(Product p) {
        System.out.println("Delete test: ");

        // add code that deletes the specified product from the database
        // if a product with the specified code doesn't exist, display an error message
        printProduct(p);
        System.out.println();
    }

    // use this method to print a Product object on a single line
    private static void printProduct(Product p) {
        String productString
                = StringUtil.padWithSpaces(p.getCode(), 12)
                + StringUtil.padWithSpaces(p.getDescription(), 38)
                + p.getFormattedPrice();

        System.out.println(productString);
    }
}
 

import java.sql.*;

public class MurachDB
{
    public static Connection getConnection()
    {
        Connection connection = null;
        try
        {
            // if necessary, set the home directory for Derby
            String dbDirectory = "c:/murach/java/db";
            System.setProperty("derby.system.home", dbDirectory);

            // set the db url, username, and password
            String url = "jdbc:derby:MurachDB";
            String username = "";
            String password = "";

            connection = DriverManager.getConnection(url, username, password);
            return connection;
        }
        catch (SQLException e)
        {
            for (Throwable t : e)
                t.printStackTrace();   // for debugging
            return null;
        }
    }

    public static boolean disconnect()
    {
        try
        {
            // On a successful shutdown, this throws an exception
            String shutdownURL = "jdbc:derby:;shutdown=true";
            DriverManager.getConnection(shutdownURL);
        }
        catch (SQLException e)
        {
            if (e.getMessage().equals("Derby system shutdown."))
                return true;
        }
        return false;
    }
}
 

import java.text.NumberFormat;

public class Product
{
   private String code;
   private String description;
   private double price;

   public Product()
   {
       this("", "", 0);
   }

   public Product(String code, String description, double price)
   {
       this.code = code;
       this.description = description;
       this.price = price;
   }

   public void setCode(String code)
   {
       this.code = code;
   }

   public String getCode(){
       return code;
   }

   public void setDescription(String description)
   {
       this.description = description;
   }

   public String getDescription()
   {
       return description;
   }

   public void setPrice(double price)
   {
       this.price = price;
   }

   public double getPrice()
   {
       return price;
   }

   public String getFormattedPrice()
   {
       NumberFormat currency = NumberFormat.getCurrencyInstance();
       return currency.format(price);
   }

    public boolean equals(Object object)
    {
        if (object instanceof Product)
        {
            Product product2 = (Product) object;
            if
            (
                code.equals(product2.getCode()) &&
                description.equals(product2.getDescription()) &&
                price == product2.getPrice()
            )
                return true;
        }
        return false;
    }

   public String toString()
   {
       return "Code:        " + code + "\n" +
               "Description: " + description + "\n" +
               "Price:       " + this.getFormattedPrice() + "\n";
   }
}
 

public class StringUtil {

    public static String padWithSpaces(String s, int length)
    {
        if (s.length() < length)
        {
            StringBuilder sb = new StringBuilder(s);
            while(sb.length() < length)
            {
                sb.append(" ");
            }
            return sb.toString();
        }
        else
        {
            return s.substring(0, length);
        }
    }
}

write JDBC code that works with the SQLite database named products.sqlite that was described in the previous chapter. Review the code and test the application 1. Open the project named ch21_ex1_DBTester that's in the ex_starts folder. 2. Expand the Libraries folder for this project and note that it includes a JAR file for the SQLite database driver. If it doesn't include this driver, add the SQLite driver that's in the java/db directory of the download for this book. 3. Open the DB Tester class and review its code. Note that it connects to the database named products.sqlite that's in the application's root directory. 4. Run the project. It should print all of the rows in the Products table to the console three times with some additional messages. Write and test code that uses JDBC 5. Write the code for the printFirstProduct() method. This method should print the first product in the list of products to the console. Use column names to retrieve the column values. 6. Run this application to make sure it's working correctly. 7. Write the code for the printProductByCode() method. This method should print the product with the specified code to the console. Use a prepared statement to create the result set, and use indexes to retrieve the column values. 8. Run this application to make sure it's working correctly. 9. Write the code for the insert Product() method. This method should add the product to the database and print that product to the console. 10. Run this application to make sure it's working correctly. If you run this application multiple times, it should display an error message that indicates that the product can't be added because of a duplicate key. 11. Write the code for the deleteProduct() method. This method should delete the product that was added by the insert Product() method. 12. Run this application to make sure it's working correctly. You should be able to run this application multiple times without it displaying any error messages.