askIT

Select specific data from Java database table

document-save.png Download Java select single row PostgreSQL code

We can select specific data from a single table row. Lets say we want the product_price for Americano in products table.

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

To fetch the product price for Americana we will use the query
SELECT product_price FROM products WHERE product_name='Americano'
in a SQL program. To use the same in Java we need to create a method that has the product name as input value.

We create a method named queryProductPriceForProductName

/**
* Get the price of a product by giving the product name.
* @param productName
* @return
*/

public Double queryProductPriceForProductName(String productName){
    try {
        PreparedStatement st = conn.prepareStatement(
                "SELECT product_price FROM products WHERE product_name=?");
        st.setString(1, productName);
        //System.out.print("SQL = " + st.toString());
        ResultSet rs = st.executeQuery();
        while (rs.next()){
            return rs.getDouble(1);
        }
        rs.close();
        st.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }

    return -1.0;
} // queryProductPriceForProductName

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 product with name Americano
        Double americanoPrice = coffeShopDbImpl.queryProductPriceForProductName("Americano");
        System.out.println("Americano price is $" + americanoPrice);


        // 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.