INTRODUCTION TO JDBC - Select Statement

 Select Statement

 

JDBC is an Application Programming Interface(API) that describes how a client may access a database. Here this program provides you the basic knowledge about the SELECT statement in Oracle.

The SELECT statement is used to retrieve the records from the database based on the client's needs. The retrieved data will be stored in a ResultSet and this ResultSet is used to display those selected records. Here, use a SELECT statement to display the records from the ItemType table.   

Strictly adhere to the Object-Oriented specifications given in the problem statement. All class names, attribute names, and method names should be the same as specified in the problem statement.
 

The class ItemType with the following attributes is provided

Attribute	Datatype
id	Long
name	String
deposit	Double
costPerDay	Double

The methods for getters, setters and constructors are given in the template code.

Consider the class ItemTypeDAO with the following method declaration. Fill in the method definition in accordance with the method description

Method & Description

public List<ItemType> getAllItemTypes()

This method should find all records of the item_type table and return the ItemType objects in a List.

Consider the class DBConnection and define the following static methods,

Method	Description
static Connection getConnection()	This method uses "oracle.properties" file as a resource file and gets a connection object for the MySQL database and returns the connection object.

 

 

The driver class Main is provided with the main method. Fill in the main method to display the list of ItemType objects. Display the ItemType Objects as displayed in the Sample Input and Output.

Table Properties:

 

CREATE TABLE item_type(
id number(19) NOT NULL,
name varchar2(255) NOT NULL,
deposit BINARY_DOUBLE NOT NULL,
cost_per_day BINARY_DOUBLE NOT NULL,
PRIMARY KEY (id));

CREATE SEQUENCE item_type_seq START WITH 1 INCREMENT BY 1;

The following code snippet is provided to establish DBConnection:

import java.util.ResourceBundle;
ResourceBundle rb = ResourceBundle.getBundle("oracle");
String url = rb.getString("db.url");
String username = rb.getString("db.username");
String password = rb.getString("db.password");

oracle.properties:

db.url = jdbc:oracle:thin:@localhost:1521:xe
db.username = root
db.password = student

Download the oracle jar file in the below link.
Oracle jar

Download the template code in the below link.
Code Template

 

Note:

• The oracle jar is provided inside the lib folder of the code template.
• All double values should be formatted to 1 decimal place.
• Use System.out.format("%-5s %-15s %-10s %s\n","ID","Name","Deposit","Cost per day") to display ItemType details as Output.

Sample Input and Output:

Id	Name	Deposit	Cost per day
1	Food	50000.0	10000.0
2	Electronics	85000.0	15000.0
3	Fashion	36000.0	8000.0
4	Grooming	15000.0	5000.0
5	Books	20000.0	7500.0

 

PROGRAM:

CLASS 1:

public class ItemType{
    
    Long id;
    String name;
    Double deposit;
    Double costPerDay;
    
    ItemType(){ }
    ItemType(Long id, String name, Double deposit, Double costPerDay){        
this.id = id;
this.name = name;
this.deposit = deposit;
this.costPerDay = costPerDay;    
    }
    
    public String getName() {
    return name;
    }
    public void setName(String name) {
    this.name = name;
    }
    
    
    public Double getDeposit() {
    return deposit;
    }
    public void setDeposit(Double deposit) {
    this.deposit = deposit;
    }
    
    public Long getId() {
    return id;
    }
    public void setId(Long id) {
    this.id = id;
    }
    
    public Double getCostPerDay() {
    return costPerDay;
    }
    public void setCostPerDay(Double costPerDay) {
    this.costPerDay = costPerDay;
    }
    
}

CLASS 2: DBConnection 

import java.util.ResourceBundle;
import java.sql.*;
public class DBConnection {
    public static Connection getConnection() throws Exception {        
        ResourceBundle rb = ResourceBundle.getBundle("oracle");
        String url = rb.getString("db.url");
        String username = rb.getString("db.username");
        String password = rb.getString("db.password");
        Connection con = DriverManager.getConnection(url,username,password);
        
        return con;
    }
}

CLASS 3:   ItemTypeDAO

import java.util.*;
import java.sql.*;
public class ItemTypeDAO {
    public List<ItemType> getAllItemTypes() throws SQLException {
    List<ItemType> itemTypeList = new ArrayList<>();
    
        Connection con =null;
        Statement st = null;
        ResultSet rs = null;
        
        try{
            
            con = DBConnection.getConnection();
            st = con.createStatement();
            String query = "Select * from item_type";
            rs = st.executeQuery(query);
            while(rs.next()){
                Long id = (long)rs.getInt(1);
                String name = rs.getString(2);
                Double deposit = rs.getDouble(3);
                Double costPerDay = rs.getDouble(4);
                
                ItemType i = new ItemType(id,name,deposit,costPerDay);
                
                itemTypeList.add(i);
            
                }
            
            }catch(Exception e){
                System.out.println(e);
            
            }
            
return itemTypeList;
}
}


CLASS 4:

import java.util.*;
public class Main {
    public static void main(String[] args) throws Exception {
    ItemTypeDAO i = new ItemTypeDAO();
        
        List<ItemType> alist = new ArrayList<>(); 
        alist = i.getAllItemTypes();
        
        System.out.printf("%-5s %-15s %-10s %s\n","ID","Name","Deposit","Cost per day");
        
        for(ItemType it:alist){
            Long id = it.getId();
            String name = it.getName();
            Double deposit = it.getDeposit();
            Double costPerDay = it.getCostPerDay();
            
            System.out.printf("%-5s %-15s %-10s %s\n",id,name,deposit,costPerDay);
      
        }
        
        
}
}

MOTIVATION :

    “The way we spend our time defines who we are ”

Although we may not see each other as often as we’d like, distance is no match for the bond that we share. Thank you for coming to visit. It was fantastic to catch up.

VISITE MORE BLOGS