Working with record from database with JDBC

Question

It is for the first time I am working with record from database using JDBC. I was able to insert the data successfully but I have some problem in retrieving them.

I am querying some records with a select Statement. When I print the result with System.out.print directly in the if(rs.next) section, everything works fine. However, my goal is to send the result to a method which will return a Arraylist. In the method listDis() below I wanted to test the code but only the first record found is displayed infinitely.

Below is my code.

package kbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Random;
import java.util.function.Consumer;
import javax.persistence.Basic;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.OneToMany;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;
import static kbc.LoginSubClass.getPassword;
import static kbc.LoginSubClass.getUserName;

/**
 *
 * @author mac
 */
public class Discount {

    List<Discount> disCode = new ArrayList<Discount>();

    private Integer discountId;
    //@Basic(optional = false)
    //@Column(name = "discount_code")
    private static String discountCode;
    //@Basic(optional = false)
    //@Column(name = "discount_status")
    private static String discountStatus;
    //@Basic(optional = false)
    //@Column(name = "end_date")
    //@Temporal(TemporalType.DATE)
    private static Date endDate;
    //@OneToMany(mappedBy = "discount")
    private static Double discountRate;

    static DatabaseConnection dbc = new DatabaseConnection();
    static Connection c = dbc.con;

    public Discount() {
    }

    public Discount(Integer discountId) {
        this.discountId = discountId;
    }

    public Discount(String discountCode, String discountStatus, Date endDate, Double discountRate) {
        this.discountCode = discountCode;
        this.discountStatus = discountStatus;
        this.endDate = endDate;
        this.discountRate = discountRate;
    }

    public Integer getDiscountId() {
        return discountId;
    }

    public void setDiscountId(Integer discountId) {
        this.discountId = discountId;
    }

    public static String getDiscountCode() {
        return discountCode;
    }

    public void setDiscountCode(String discountCode) {
        this.discountCode = discountCode;
    }

    public static String getDiscountStatus() {
        return discountStatus;
    }

    public void setDiscountStatus(String discountStatus) {
        this.discountStatus = discountStatus;
    }

    public static Date getEndDate() {
        return endDate;
    }

    public void setEndDate(Date endDate) {
        this.endDate = endDate;
    }

    public static Double getDiscountRate() {
        return discountRate;
    }

    public void setDiscountRate(Date endDate) {
        this.discountRate = discountRate;
    }

   
    /**
     * Methode to Query the Discount list from the database
     *
     * @return
     * @throws SQLException
     */
    public List discountList() throws SQLException {

        String dicountCodeList = "SELECT * FROM Discount WHERE discount_status = 'Active'";

        Statement stmt = c.createStatement();

        ResultSet rs = stmt.executeQuery(dicountCodeList);

        Discount d = new Discount();

        while (rs.next()) {
            try {

                String code = rs.getString("discount_code");
                String status = rs.getString("discount_status");
                Date endDate = rs.getDate("end_date");
                Double rate = rs.getDouble("discountRate");

                d = (new Discount(code, status, endDate, rate));
                disCode.add(d);

            } catch (Exception ex) {
                Validation val = new Validation();
                val.unexpectedError(ex.toString());

            }

        }
        return disCode;

    }
/**
 * Display data
 * @throws SQLException 
 */
    public void listDis() throws SQLException {

        for (int i = 0; i < discountList().size(); i++) {

            System.out.println("The descount Code are:" + disCode.get(i).getDiscountCode() + " "
                    + disCode.get(i).getDiscountStatus() + " " + disCode.get(i).getEndDate()
                    + " " + disCode.get(i).getDiscountRate());

        }
    }
}

Answer 1

You should divide the work you do in one class into three classes. I think the Discount class should not contain a discount list. This problem creates confusion. As you can see from my solution below, DiscountService has a discount list and I can query the required database queries in a separate class using DatabaseConnection. This class is a class with the singleton design pattern so it can be used by other service classes.

DatabaseConnection.java

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class DiscountService {

    private List<Discount> disCode;

    private Connection connection;

    public DiscountService() {
        disCode = new ArrayList<>();
        DatabaseConnection databaseConnection = DatabaseConnection.getInstance();
        this.connection = databaseConnection.getConnection();
    }

    /**
     * Methode to Query the Discount list from the database
     *
     * @return
     * @throws SQLException
     */
    public List discountList() throws SQLException {

        String dicountCodeList = "SELECT * FROM Discount WHERE discount_status = 'Active'";

        Statement stmt = connection.createStatement();

        ResultSet rs = stmt.executeQuery(dicountCodeList);

        while (rs.next()) {
            try {

                String code = rs.getString("discount_code");
                String status = rs.getString("discount_status");
                Date endDate = rs.getDate("end_date");
                Double rate = rs.getDouble("discountRate");

                Discount d = new Discount(code, status, endDate, rate);
                disCode.add(d);
            } catch (Exception ex) {
                    /*Validation val = new Validation();
                    val.unexpectedError(ex.toString());*/
            }
        }
        return disCode;

    }
    /**
     * Display data
     * @throws SQLException
     */
    public void listDis() throws SQLException {

        for (int i = 0; i < discountList().size(); i++) {

            System.out.println("The descount Code are:" + disCode.get(i).getDiscountCode() + " "
                    + disCode.get(i).getDiscountStatus() + " " + disCode.get(i).getEndDate()
                    + " " + disCode.get(i).getDiscountRate());

        }
    }

}

Discount.java

import java.util.Date;

public class Discount {

    private Integer discountId;
    //@Basic(optional = false)
    //@Column(name = "discount_code")
    private static String discountCode;
    //@Basic(optional = false)
    //@Column(name = "discount_status")
    private static String discountStatus;
    //@Basic(optional = false)
    //@Column(name = "end_date")
    //@Temporal(TemporalType.DATE)
    private static Date endDate;
    //@OneToMany(mappedBy = "discount")
    private static Double discountRate;

    public Discount() {
    }

    public Discount(Integer discountId) {
        this.discountId = discountId;
    }

    public Discount(String discountCode, String discountStatus, Date endDate, Double discountRate) {
        this.discountCode = discountCode;
        this.discountStatus = discountStatus;
        this.endDate = endDate;
        this.discountRate = discountRate;
    }

    public Integer getDiscountId() {
        return discountId;
    }

    public void setDiscountId(Integer discountId) {
        this.discountId = discountId;
    }

    public static String getDiscountCode() {
        return discountCode;
    }

    public void setDiscountCode(String discountCode) {
        this.discountCode = discountCode;
    }

    public static String getDiscountStatus() {
        return discountStatus;
    }

    public void setDiscountStatus(String discountStatus) {
        this.discountStatus = discountStatus;
    }

    public static Date getEndDate() {
        return endDate;
    }

    public void setEndDate(Date endDate) {
        this.endDate = endDate;
    }

    public static Double getDiscountRate() {
        return discountRate;
    }

    public void setDiscountRate(Date endDate) {
        this.discountRate = discountRate;
    }

}

DiscountService.java

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class DiscountService {

    private List<Discount> disCode;

    private Connection connection;

    public DiscountService() {
        disCode = new ArrayList<>();
        DatabaseConnection databaseConnection = DatabaseConnection.getInstance();
        this.connection = databaseConnection.getConnection();
    }

    /**
     * Methode to Query the Discount list from the database
     *
     * @return
     * @throws SQLException
     */
    public List discountList() throws SQLException {

        String dicountCodeList = "SELECT * FROM Discount WHERE discount_status = 'Active'";

        Statement stmt = connection.createStatement();

        ResultSet rs = stmt.executeQuery(dicountCodeList);

        while (rs.next()) {
            try {

                String code = rs.getString("discount_code");
                String status = rs.getString("discount_status");
                Date endDate = rs.getDate("end_date");
                Double rate = rs.getDouble("discountRate");

                Discount d = new Discount(code, status, endDate, rate);
                disCode.add(d);
            } catch (Exception ex) {
                    Validation val = new Validation();
                    val.unexpectedError(ex.toString());
            }
        }
        return disCode;

    }
    /**
     * Display data
     * @throws SQLException
     */
    public void listDis() throws SQLException {

        for (int i = 0; i < discountList().size(); i++) {

            System.out.println("The descount Code are:" + disCode.get(i).getDiscountCode() + " "
                    + disCode.get(i).getDiscountStatus() + " " + disCode.get(i).getEndDate()
                    + " " + disCode.get(i).getDiscountRate());

        }
    }

}

Main.java

import stackoverflow.DiscountService;
import java.sql.SQLException;

public class Main {

    public static void main(String[] args) {
        DiscountService discountService = new DiscountService();
        try {
            discountService.listDis();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

}