askIT

Select many rows into domain from Java database table

document-save.png Download Java select many rows into domain from Java database table code

We can get all information from a database table by first creating a domain for that table. Then we give a query and store the result as a list of that domain.

We want to get all rows from the products table and print it.

Our products table:

product_id product_name product_price
1 Espresso $0.40
2 Americano $0.65
3 Latte $2.00
3 Cappuccino $2.40
5 Mocha Coffe $1.30
4 Almond Cappuccino $1.90
6 Mint Latte $4.90
5 Coffee Frappe $0.60
7 Ice Coffee $2.10

Create domain

First create a package that will hold the domain.
Right click on your main package and select New -> Package.

new_package.png

The package name should be domain.

new_package_name.png

Add a new Java class in the package domain. Right click on your domain package and select New -> Java class.

new_java_class.png

The name should be Product.

create_new_class_name.png

The product class should contain the same info as the table products. It should also have a constructor and getter and setter methods.

package com.nettport.coffeshop.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;
    }
}

Create method for query products

Now we can create the method that gets the products and puts them into the Product class.


    /**
    * Gives all products as object list Product
    * @return
    */

    public List queryProducts(){

        List productList = new ArrayList<>();

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

            while (rs.next()){

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

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

                productList.add(productTemp);
            }
            rs.close();
            st.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return productList;
    } // queryProducts

Now call the method from your main class:

package com.nettport.coffeshop;

import com.nettport.coffeshop.dao.CoffeShopDbImpl;

public class Main {

    public static void main(String[] args) {
        // Connect
        CoffeShopDbImpl coffeShopDbImpl = new CoffeShopDbImpl();
        coffeShopDbImpl.connect();

        // Create tables
        coffeShopDbImpl.createTables();

        // Insert products
        /*
        coffeShopDbImpl.insertProduct("Espresso", 0.40);
        coffeShopDbImpl.insertProduct("Americano", 0.65);
        coffeShopDbImpl.insertProduct("Latte", 2.00);
        coffeShopDbImpl.insertProduct("Cappuccino", 2.40);
        coffeShopDbImpl.insertProduct("Almond Cappuccino", 1.90);
        coffeShopDbImpl.insertProduct("Mint Latte", 4.90);
        coffeShopDbImpl.insertProduct("Coffee Frappe", 0.60);
        coffeShopDbImpl.insertProduct("Ice Coffee", 2.10);
        */


        // Update price
        /*
        coffeShopDbImpl.updateProductPrice("Espresso", 0.50);
        coffeShopDbImpl.updateProductPrice("Cappuccino", 2.60);
        */


        // Get all products
        List allProducts = coffeShopDbImpl.queryProducts();
        int i = 0;
        while (i < allProducts.size()) {
            System.out.print("" + allProducts.get(i).getProductId() + "\t\t");
            System.out.print("" + allProducts.get(i).getProductName() + "\t\t");
            System.out.print("" + allProducts.get(i).getProductPrice() + "\n");
            i++;
        }


        // Disconnect
        coffeShopDbImpl.disconnect();

    }
}

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.