Database Table Setup

First, create a sample MySQL table for demonstration:

DROP TABLE IF EXISTS products;

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(50),
    vendor_name VARCHAR(50),
    price INT,
    details VARCHAR(200),
    available INT
);

INSERT INTO products (product_name, vendor_name, price, details, available)
VALUES ('Wireless Mouse', 'TechGear Inc', 49, 'Ergonomic design with precision tracking', 1),
       ('Mechanical Keyboard', 'KeyPro Solutions', 129, 'RGB backlit with cherry mx switches', 1),
       ('USB-C Hub', 'ConnectTech Ltd', 35, '7-in-1 multiport adapter', 0);

SELECT * FROM products;

Entity Class Implementation

public class Product {
    private Integer id;
    private String productName;
    private String vendorName;
    private Integer price;
    private String details;
    private Integer available;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getProductName() {
        return productName;
    }

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

    public String getVendorName() {
        return vendorName;
    }

    public void setVendorName(String vendorName) {
        this.vendorName = vendorName;
    }

    public Integer getPrice() {
        return price;
    }

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

    public String getDetails() {
        return details;
    }

    public void setDetails(String details) {
        this.details = details;
    }

    public Integer getAvailable() {
        return available;
    }

    public void setAvailable(Integer available) {
        this.available = available;
    }

    @Override
    public String toString() {
        return "Product{" +
                "id=" + id +
                ", productName='" + productName + '\'' +
                ", vendorName='" + vendorName + '\'' +
                ", price=" + price +
                ", details='" + details + '\'' +
                ", available=" + available +
                '}';
    }
}

JDBC Query Implementation

The following test demonstrates rertieving all records from the database table:

public class ProductQueryTest {

    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<Product> productList = null;

        try {
            Properties config = new Properties();
            config.load(new FileInputStream("src/database.properties"));

            DataSource dataSource = DruidDataSourceFactory.createDataSource(config);
            connection = dataSource.getConnection();

            String query = "SELECT * FROM products";
            statement = connection.prepareStatement(query);

            resultSet = statement.executeQuery();

            productList = new ArrayList<>();
            Product product = null;

            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("product_name");
                String vendor = resultSet.getString("vendor_name");
                int price = resultSet.getInt("price");
                String desc = resultSet.getString("details");
                int status = resultSet.getInt("available");

                product = new Product();
                product.setId(id);
                product.setProductName(name);
                product.setVendorName(vendor);
                product.setPrice(price);
                product.setDetails(desc);
                product.setAvailable(status);

                productList.add(product);
            }

            System.out.println(productList);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeResources(resultSet, statement, connection);
        }
    }

    private static void closeResources(ResultSet rs, PreparedStatement ps, Connection conn) {
        try {
            if (rs != null) rs.close();
            if (ps != null) ps.close();
            if (conn != null) conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Database Configuration

Create a properties file named database.properties and place it in the src directory:

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?useSSL=false&useServerPrepStmts=true
username=root
password=your_password
initialSize=5
maxActive=10
maxWait=3000

Required Dependencies

Ensure the following JAR files are included in your project classpath:

• MySQL JDBC driver (mysql-connector-java*.jar)
• Druid connection pool (druid*.jar)

Place these libraries in a lib folder and add them to the project build path.