planBean

package ModelClass;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.RequestScoped;

import java.util.Date;
import java.util.List;

@ManagedBean(name = "PlanBean")
@RequestScoped
public class PlanBean {

    private final DBConnection dbconnection = new DBConnection();


    public List<Plan> getPlanList() throws ClassNotFoundException, SQLException {
        List<Plan> list = new ArrayList<>();
        List<String> lands = new ArrayList<>();
        List<String> emergency = new ArrayList<>();
        List<String> market = new ArrayList<>();
        List<IntraRegionTransport> intra = new ArrayList<>();
        IntraRegionTransport temp1 = new IntraRegionTransport();
        String sql;
        Statement stmt = null;
        Statement stmt1 = null;
        PreparedStatement ps = null;
        Connection con = null;
        ResultSet rs = null;
        ResultSet rs1 = null;
        System.out.print("In RegionBean");

        try {

            //Class.forName("oracle.jdbc.driver.OracleDriver");
            DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

            String dbURL2 = "jdbc:oracle:thin:@localhost:1521:orcl";
            con = DriverManager.getConnection(dbURL2, dbconnection.user, dbconnection.password);
            if (con != null) {
                System.out.println("Connected with connection #2 in regionbean");
                stmt = con.createStatement();
                stmt1 = con.createStatement();
                try {
                    rs1 = stmt1.executeQuery("SELECT USER_ID FROM USER_INFO WHERE USER_NAME = '" + PageController.user+ "'");
                    int id=0;
                    if(rs1.next())
                        id = rs1.getInt(1);
                    rs = stmt.executeQuery("SELECT * FROM PLANS WHERE USER_ID ="+id);
                    while (rs.next()) {
                        Plan plan = new Plan();
                        plan.setPLAN_ID(rs.getInt(1));
                        plan.setUSER_ID(rs.getInt(2));
                        plan.setHOTEL_ID(rs.getInt(3));
                        plan.setRESTAURANT_ID(rs.getInt(4));
                        plan.setINTER_REGION_TRANSPORT_ID(rs.getInt(5));
                        plan.setSHOPPING_ID(rs.getInt(6));
                        plan.setEMERGENCY_ID(rs.getInt(7));
                        rs1 = stmt1.executeQuery("SELECT HOTEL_NAME FROM HOTELS_AND_RESORTS WHERE PLACE_ID= " + plan.getHOTEL_ID());
                        if(rs1.next())
                        plan.setHOTEL_NAME(rs1.getString(1));
                        rs1 = stmt1.executeQuery("SELECT RESTAURANT_NAME FROM RESTAURANTS WHERE RESTAURANT_ID= " + plan.getRESTAURANT_ID());
                        if(rs1.next())
                        plan.setRESTAURANT_NAME(rs1.getString(1));
                        rs1 = stmt1.executeQuery("SELECT VEHICLE_NAME FROM INTER_REGION_TRANSPORT WHERE INTER_REGION_TRANSPORT_ID= " + plan.getINTER_REGION_TRANSPORT_ID());
                        if(rs1.next())
                        plan.setINTER_REGION_NAME(rs1.getString(1));
                        //add intra to the list
                        sql = "SELECT I.MODE_OF_TRANSPORT,I.FROM_DEST,I.TO_DEST FROM USED_INTRA_REGION_TRANSPORT U,INTRA_REGION_TRANSPORT I WHERE U.PLAN_ID ="+plan.getPLAN_ID()
                                +" AND USER_ID="+id;
                        rs1 = stmt.executeQuery(sql);
                        while(rs1.next())
                        {
                            temp1.setMODE_OF_TRANSPORT(rs1.getString(1));
                            temp1.setFROM_DEST(rs1.getString(2));
                            temp1.setTO_DEST(rs1.getString(3));

                            intra.add(temp1);
                        }
                        plan.setSelectedintratransport(intra);
                        //add landmarks
                        sql = "SELECT L.LANDMARK_NAME FROM LANDMARKS L, VISITED_LANDMARKS V WHERE V.PLAN_ID="
                                +plan.getPLAN_ID()+" AND V.USER_ID="+id+" AND V.LANDMARK_ID = L.LANDMARK_ID";
                        rs1 = stmt.executeQuery(sql);
                        while(rs1.next())
                        {
                            lands.add(rs1.getString(1));
                        }
                        plan.setSelectedlandmarks(lands);

                        //add visited emergency
                        sql = "SELECT E.EMERGENCY_NAME FROM EMERGENCY E, AVAILED_EMERGENCY A WHERE A.PLAN_ID="
                                +plan.getPLAN_ID()+" AND A.USER_ID="+id+" AND A.EMERGENCY_ID = E.EMERGENCY_ID";
                        rs1 = stmt.executeQuery(sql);
                        while(rs1.next())
                        {
                            emergency.add(rs1.getString(1));
                        }
                        plan.setEmergency(emergency);
                        //add visited shopping malls
                        sql = "SELECT S.MARKET_NAME FROM SHOPPING S, VISITED_MARKETS V WHERE V.PLAN_ID="
                                +plan.getPLAN_ID()+" AND V.USER_ID="+id+" AND V.SHOPPING_ID = S.SHOPPING_ID";
                        rs1 = stmt.executeQuery(sql);
                        while(rs1.next())
                        {
                            emergency.add(rs1.getString(1));
                        }
                        plan.setMarket(market);
                        /*region.setRegionID(rs.getInt(1));
                        region.setRegionName(rs.getString(2));*/
                        list.add(plan);
                    }
                } catch (SQLException e) {
                    System.out.println("No data found");
                }
            }
        } catch (SQLException e) {
            System.out.println(e.toString());
        } finally {
            try {
                con.close();
                stmt.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        return list;
    }
}