JDBC 4: (WHERE CLAUSE, INSERTING, RETREIVING)


  






public class User {
    private Long id;
    private String name;
    private String mobileNumber;
    private String username;
    private String password;
    public User(){}
    public User(Long id,String name, String mobileNumber, String username, String password){
        this.id=id;
        this.name=name;
        this.mobileNumber=mobileNumber;
        this.username=username;
        this.password=password;
    }
    public User(Long id) {
        this.id=id;
    }
    public Long getId()
    {
        return this.id;
    }
    public String getName(){
        return this.name;
    }
    public String getMobileNumber(){
        return this.mobileNumber;
    }
    public String getUsername(){
        return this.username;
    }
    public String getPassword(){
        return this.password;
    }
    
    public void setId(Long id)
    {
        this.id=id;
    }
    public void setName(String name){
        this.name=name;
    }
    public void setMobileNumber(String mobileNumber){
        this.mobileNumber=mobileNumber;
    }
    public void setUsername(String username){
        this.username=username;
    }
    public void setPassword(String password){
        this.password=password;
    }
}
______________________________________________________________
public class Hall {
private Long id;
    private String name;
    private String contactNumber;
    private Double costPerDay;
    private User owner;
    public Hall(){}
   
    public Hall(String name, String contactNumber, Double costPerDay, User owner){
        this.name=name;
        this.contactNumber=contactNumber;
        this.costPerDay=costPerDay;
        this.owner=owner;
    }
    public Long getId()
    {
        return this.id;
    }
    public String getName(){
        return this.name;
    }
    public String getContactNumber(){
        return this.contactNumber;
    }
    public Double getCostPerDay(){
        return this.costPerDay;
    }
    public User getOwner(){
        return this.owner;
    }
    
    public void setId(Long id)
    {
        this.id=id;
    }
    public void setName(String name){
        this.name=name;
    }
    public void setContactNumber(String contactNumber){
        this.contactNumber=contactNumber;
    }
    public void setCostPerDay(Double costPerDay){
        this.costPerDay=costPerDay;
    }
    public void setOwner(User owner){
        this.owner=owner;
    }
    
}
_____________________________________________________________________________
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class UserDAO {
    public User getUser(String username) throws ClassNotFoundException, SQLException {
User user = null;
Connection conn = DBConnection.getConnection();
    Statement stmt = conn.createStatement();
String query = "select * from \"user\" where username ='"+username+"'";
ResultSet rs = stmt.executeQuery(query);
if(rs.next()) {
Long id = rs.getLong(1);
String name = rs.getString(2);
String contact_detail = rs.getString(3);
String uname = rs.getString(4);
String pwd = rs.getString(5);
user = new User(id,name,contact_detail,uname,pwd);
}else {
user=null;
}
return user;
}
public User getUser2(Long id) throws ClassNotFoundException, SQLException {
User user = null;
Connection conn = DBConnection.getConnection();
    Statement stmt = conn.createStatement();
String query = "select * from \"user\" where id ='"+id+"'";
ResultSet rs = stmt.executeQuery(query);
if(rs.next()) {
Long i_d = rs.getLong(1);
String name = rs.getString(2);
String contact_detail = rs.getString(3);
String uname = rs.getString(4);
String pwd = rs.getString(5);
user = new User(i_d,name,contact_detail,uname,pwd);
}else {
user=null;
}
return user;
}
}
_______________________________________________________________________________
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.List;
public class HallDAO {
    public void saveHall(Hall hall) throws ClassNotFoundException, SQLException {
Connection conn = DBConnection.getConnection();
String query = "insert into hall (name,contact_detail,cost_per_day,owner_id) values(?,?,?,?)";
PreparedStatement pr = conn.prepareStatement(query);
pr.setString(1, hall.getName());
pr.setString(2, hall.getContactNumber());
pr.setDouble(3, hall.getCostPerDay());
pr.setLong(4, hall.getOwner().getId());
pr.executeUpdate();
conn.commit();
pr.close();
}
public List<Hall> getAllHall() throws SQLException, ClassNotFoundException {
List<Hall> hallList = new ArrayList<Hall>();
        Connection conn = DBConnection.getConnection();
        Statement st = conn.createStatement();
        String query = "select * from hall order by id";
        ResultSet rs = st.executeQuery(query);
        while(rs.next()) {
        String nm = rs.getString(2);
        String con = rs.getString(3);
        Double cost = rs.getDouble(4);
        Long owner_id = rs.getLong(5);
       
        UserDAO dao2 = new UserDAO();
User u = dao2.getUser2(owner_id);
        Hall hall = new Hall(nm,con,cost,u);
            hallList.add(hall);
            hall=null;
        }
        return hallList;
}
}
_______________________________________________________________________________
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ResourceBundle;
public class DBConnection {
    public static Connection getConnection() throws ClassNotFoundException, SQLException {        
        ResourceBundle rb = ResourceBundle.getBundle("oracle");
        String url = rb.getString("db.url");
        String username = rb.getString("db.username");
        String password = rb.getString("db.password");
        Connection conn = DriverManager.getConnection(url,username,password);
        return conn;
    }
}
_______________________________________________________________________________
import java.lang.reflect.Array;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;



public class Main {

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
Scanner sc = new Scanner(System.in);
System.out.println("Enter the details of hall in csv format:");
String inp = sc.nextLine();
String arr[]=inp.split(",");
HallDAO dao1 = new HallDAO();
System.out.println("Enter the username:");
String a="y";
while(a=="y") {
String username = sc.nextLine();
UserDAO dao2 = new UserDAO();
User u = dao2.getUser(username);
if(u!=null) {
//User user = new User(u.getId());
Hall hall = new Hall(arr[0],arr[1],Double.parseDouble(arr[2]),u);
dao1.saveHall(hall);
a="n";
}else {
System.out.println("Username seems to be wrong!! Enter the correct username:");
}
}
HallDAO dao3 = new HallDAO();
List<Hall> hallList = new ArrayList<Hall>();
hallList = dao3.getAllHall();
System.out.println("The hall details are:");
System.out.format("%-15s%-15s%-15s%-15s\n","Name","Mobile","Cost","Owner");
for (Hall h : hallList) {
System.out.format("%-15s%-15s%-15s%-15s\n", h.getName(), h.getContactNumber(), h.getCostPerDay(), h.getOwner().getName());
}
}

}










Comments

Post a Comment

Popular posts from this blog

  Primitive Wrappers | java.lang | java.util CUSTOMER ADDRESS USING STRING BUILDER Sample Input & Output: Enter Address Details : Enter Line 1 : 152, South Block Enter Line 2 : Raisina Hill Enter City : New Delhi Enter Country : India Enter Zip Code : 110011 Address Details : 152, South Block, Raisina Hill, New Delhi - 110011 India CODE: // fill your code here public class Address {      private String line1; private String line2; private String city; private String country; private int zipCode; public String getLine1() { return line1; } public void setLine1(String line1) { this.line1 = line1; } public String getLine2() { return line2; } public void setLine2(String line2) { this.line2 = line2; } public String getCity() { return city; } public void setCity(String city) { this.city = city; } public String getCountry() { return country; } public void setCountry(String country) { this.country = country...
Read more
Collections: ARRAYLIST - INTRODUCTION Sample Input and Output 1: Enter the username 1 John Do you want to continue?(y/n) y Enter the username 2 Joe Do you want to continue?(y/n) n The Names entered are: John Joe CODE: import java.util.ArrayList; import java.util.Scanner; public class Main {     public static void main(String args[]) { String user; Scanner sc = new Scanner(System.in); ArrayList<String> al = new ArrayList<String>(); int i = 1; do { System.out.println("Enter the username "+i); al.add(sc.nextLine()); System.out.println("Do you want to continue?(y/n)"); user = sc.nextLine(); i++; }while(user.equalsIgnoreCase("y")); System.out.println("The Names entered are:"); for(String a:al) { System.out.println(a); } } }
Read more
Collections: TREEMAP To assist Event organizers, you need to develop a console application that shows the number of tickets sold in a particular price category. Sample Input and Output : [All Texts in bold corresponds to the input and rest are output] Enter the number of events: 3 Enter event details in CSV(Customer Name,Ticket Price,No of Seats Booked) Emily ,100,5 Natalie ,200,10 Grace ,100,3 Ticket Price    Tickets Booked 100                 8 200                 10 CODE: //package com.mycom.collections.map; import java.util.Scanner; import java.util.Set; import java.util.TreeMap; public class Main {     public static void main(String[] args) { Scanner sc = new Scanner(System.in); System.out.println("Enter the number of events:"); int num = Integer.parseInt(sc.nextLine()); TreeMap<I...
Read more