askIT

Database tutorial with menu

document-save.png Download Java java_en_coffee_corner code

CoffeeCornerDbImpl

package com.nettport.coffeecorner.dao;

import com.nettport.coffeecorner.domain.Product;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CoffeeCornerDbImpl extends DbAdapter {

    public void createTables(){
        try {
            stmt = conn.createStatement();

            String sql = "CREATE TABLE IF NOT EXISTS products (" +
                    "product_id SERIAL PRIMARY KEY NOT NULL, " +
                    "product_name VARCHAR(100) NOT NULL, " +
                    "product_price REAL)";

            stmt.executeUpdate(sql);
            stmt.close();

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

    } // createTables

    public void setup (){
        // First count rows
        int numberOfRows = -1;
        try {
            PreparedStatement st = conn.prepareStatement(
                    "SELECT count(*) FROM products");
            ResultSet rs = st.executeQuery();
            while (rs.next()) {
                numberOfRows = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        if (numberOfRows == 0) {
            // Do setup
            System.out.println("Number of rows=" + numberOfRows);


            // Setup products
            insertProduct("Espresso", 1.30);
            insertProduct("Americano", 1.20);
            insertProduct("Latte", 2.30);
            insertProduct("Cappuchino", 2.10);
            insertProduct("Mocha Coffee", 2.40);

        }


    } // setup

    public void insertProduct(String productName, Double productPrice) {
        System.out.println("Insert product " + productName + " with the price " + productPrice);

        try {
            PreparedStatement st = conn.prepareStatement("INSERT INTO PRODUCTS " +
                    "(product_name, product_price) " +
                    "VALUES(?,?)");
            st.setString(1, productName);
            st.setDouble(2, productPrice);
            st.executeUpdate();
            st.close();

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

    } // insertProduct


    public List selectAllProducts(){

        List allProductsList = new ArrayList<>();

        try {
            PreparedStatement st = conn.prepareStatement("SELECT product_id, product_name, product_price FROM products" +
                    " ORDER BY product_id ASC");
            ResultSet rs = st.executeQuery();

            while(rs.next()){
                Long productId = rs.getLong(1);
                String productName = rs.getString(2);
                Double productPrice = rs.getDouble(3);

                Product tempProduct = new Product(productId, productName, productPrice);

                allProductsList.add(tempProduct);

            }


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


        return allProductsList;
    } // selectAllProducts


    public void updateProduct(long productId, String productName, double productPrice){
        System.out.println("Updating item number " + productId + " with name = " + productName +
                            " price = " + productPrice);

        try {
            if (!(productName.equals(""))) {
                PreparedStatement st = conn.prepareStatement("UPDATE products SET product_name=? WHERE product_id=?");
                st.setString(1, productName);
                st.setLong(2, productId);
                st.executeUpdate();
                st.close();
            }
            if(productPrice != -1.0) {
                PreparedStatement st = conn.prepareStatement("UPDATE products SET product_price=? WHERE product_id=?");
                st.setDouble(1, productPrice);
                st.setLong(2, productId);
                st.executeUpdate();
                st.close();
            }

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


    public void deleteProduct(long productId){
        System.out.println("Deleting product number " + productId);
        try {
            PreparedStatement st = conn.prepareStatement("DELETE FROM products WHERE product_id=?");
            st.setLong(1, productId);
            st.executeUpdate();
            st.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }


    } // deleteProduct

}

DbAdapter

package com.nettport.coffeecorner.dao;

import java.sql.*;

public class DbAdapter {

    /*- 01 Variables ------------------------------------- */
    String jdbUrl = "jdbc:postgresql://localhost:5432/coffee_corner";
    String username = "postgres";
    String password = "postgre";

    /*- 02 Database variables ---------------------------- */
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;

    /*- 03 Constructor ------------------------------------ */
    public DbAdapter() {
    }

    /**
    * Connect to database
    */

    public void connect () {
        try {
            // Connect to database
            conn = DriverManager.getConnection(jdbUrl, username, password);

            // Print success
            System.out.println("Database connection established");

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

    /**
    * Disconnect from database
    */

    public void disconnect() {
        try {
            if (stmt != null) {
                stmt.close();
            }

            if (rs != null) {
                rs.close();
            }

            if (conn != null) {
                conn.close();
            }

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


    } // disconnect


}

Product

package com.nettport.coffeecorner.domain;

public class Product {
    Long productId;
    String productName;
    Double productPrice;

    public Product(Long productId, String productName, Double productPrice) {
        this.productId = productId;
        this.productName = productName;
        this.productPrice = productPrice;
    }


    public Long getProductId() {
        return productId;
    }

    public void setProductId(Long productId) {
        this.productId = productId;
    }

    public String getProductName() {
        return productName;
    }

    public void setProductName(String productName) {
        this.productName = productName;
    }

    public Double getProductPrice() {
        return productPrice;
    }

    public void setProductPrice(Double productPrice) {
        this.productPrice = productPrice;
    }
}

Main

package com.nettport.coffeecorner;

import com.nettport.coffeecorner.dao.CoffeeCornerDbImpl;
import com.nettport.coffeecorner.domain.Product;

import java.util.List;
import java.util.Locale;
import java.util.Scanner;

public class Main {

    public static void main(String[] args) {
// write your code here
        System.out.println("-- Welcome to Coffee Corner --");

        // Connect
        CoffeeCornerDbImpl coffeeCornerDb = new CoffeeCornerDbImpl();
        coffeeCornerDb.connect();

        // Create tables
        coffeeCornerDb.createTables();

        // Run setup
        coffeeCornerDb.setup();

        // Menu
        int stopProgram = 0;
        int menu = 0;
        Scanner scanner = new Scanner(System.in);
        while(stopProgram < 1) {
            if (menu == 0) {
                System.out.println("\n\n--- MENU ---");
                System.out.println(" [0] Print this menu");
                System.out.println(" [1] Print all products");
                System.out.println(" [2] New product");
                System.out.println(" [3] Update product");
                System.out.println(" [4] Delete product");
                System.out.println(" [Other] Stop program");
                System.out.print("Menu choice: ");

            } else if (menu == 1) {
                System.out.println("\n\n--- PRINT ALL PRODUCTS ---");
                printAllProducts(coffeeCornerDb);

            } else if (menu == 2) {
                System.out.println("\n\n--- NEW PRODUCT ---");
                newProduct(coffeeCornerDb);

            } else if (menu == 3) {
                System.out.println("\n\n--- UPDATE PRODUCT ---");
                updateProduct(coffeeCornerDb);

            } else if (menu == 4) {
                System.out.println("\n\n--- DELETE PRODUCT ----");
                deleteProduct(coffeeCornerDb);

            } else {
                System.out.println("\n\n--- STOP PROGRAM ---");
                stopProgram = 1;
            }

            if (stopProgram != 1) {
                menu = scanner.nextInt();
            }
        } // stopProgram

        // Disconnect from db
        coffeeCornerDb.disconnect();
    }

    public static void printAllProducts(CoffeeCornerDbImpl coffeeCornerDb){
        // List all products
        List allProducts = coffeeCornerDb.selectAllProducts();
        int i = 0;
        while (i < allProducts.size()) {
            System.out.print("" + allProducts.get(i).getProductId());
            System.out.print(" " + allProducts.get(i).getProductName());
            System.out.println(" $" + allProducts.get(i).getProductPrice());
            i++;
        }
    } // printAllProducts

    public static void newProduct(CoffeeCornerDbImpl coffeeCornerDb){
        Scanner scanner = new Scanner(System.in).useLocale(Locale.US);

        System.out.print("\n\nPlease enter the name of product: ");
        String name = scanner.nextLine();

        System.out.print("\n\nPlease give the price of the product: ");
        Double price = scanner.nextDouble();

        coffeeCornerDb.insertProduct(name, price);

    } // newProduct

    public static void updateProduct(CoffeeCornerDbImpl coffeeCornerDb){
        // First print all products
        printAllProducts(coffeeCornerDb);

        // Ask what product id you want to change
        System.out.print("\n\nEnter product ID that you want to change: ");
        Scanner scanner = new Scanner(System.in).useLocale(Locale.US);
        int productId = scanner.nextInt();
        scanner.nextLine();

        // Ask for new name
        System.out.print("\n\nNew name (to keep the existing name hit Enter: ");
        String name = scanner.nextLine();

        // Ask for new price
        System.out.print("\n\nNew price (to keep existing price write -1.0: ");
        double price = scanner.nextDouble();

        coffeeCornerDb.updateProduct(productId, name, price);

    }


    public static void deleteProduct(CoffeeCornerDbImpl coffeeCornerDb) {
        // First print all products
        printAllProducts(coffeeCornerDb);

        // Ask what product id you want to change
        System.out.print("\n\nEnter product ID that you want to delete: ");
        Scanner scanner = new Scanner(System.in).useLocale(Locale.US);
        int productId = scanner.nextInt();
        scanner.nextLine();

        coffeeCornerDb.deleteProduct(productId);


    }
}

Add comment

avatar_blank_60.png

Comment:

Name:

Email:

Comments

No comments yet. You can be the first one to comment. Just write your comment in the form and click on the submit button.