Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- package uver;
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.ResultSet;
- import java.util.ArrayList;
- public class Uver {
- public static void main(String[] args) {
- String url = "jdbc:postgresql://bdd.inf.udec.cl:5432/isw1a";
- String usuario = "isw1a";
- String contraseña = "matiasmora18";
- try{
- Class.forName("org.postgresql.Driver");
- Connection conexion = DriverManager.getConnection(url,usuario,contraseña);
- java.sql.Statement st = conexion.createStatement();
- /////Pruebas/////
- String sql;
- String driver="";
- //sql=addNewUser(12121212, "Robert", 1996, 3, 16, 98767698, "Callese Viejo Lesbiano", "arbochanpion@cc.ru", "1234567", true, false, false, false);
- //sql=addNewRoute("Temuco", "Valdivia", 16, 45, 0);
- //Saca id de viajes para ir de ciudad origen a destino o intermedios y los guarda en idtravels//
- sql=searchTravel("Iquique", "Concepción");
- ResultSet result=st.executeQuery(sql);
- ArrayList<String> idtravels = new ArrayList<>();
- ArrayList<String> nseatss = new ArrayList<>();
- while(result.next()){
- String idtravel = result.getString("idtravel");
- String nseats = result.getString("seats");
- idtravels.add(idtravel);
- nseatss.add(nseats);
- }
- result.close();
- if(idtravels.isEmpty())System.out.println("No hay viajes que satisfagan su consulta");
- //Da la info de los viajes obtenidos
- while(!idtravels.isEmpty()){
- String aux=searchTravel4(idtravels.get(0));
- result=st.executeQuery(aux);
- while(result.next())driver=result.getString("fullname");
- aux=searchTravel2(idtravels.get(0));
- result=st.executeQuery(aux);
- while(result.next()){
- String ocity=result.getString("ocity");
- String dcity=result.getString("dcity");
- System.out.println("El viaje con Conductor:"+driver+", Origen:"+ocity+" y Destino:"+dcity+ " dispone "+nseatss.get(0)+" asientos totales y tiene el siguiente itinerario:");
- }
- nseatss.remove(0);
- result.close();
- //Da la info de las rutas seguidas por los viajes obtenidos
- aux=searchTravel3(idtravels.get(0));
- result=st.executeQuery(aux);
- while(result.next()){
- String ocity=result.getString("ocity");
- String dcity=result.getString("dcity");
- String ohour=result.getString("ohour");
- System.out.println(ocity+ "-" +dcity+ " -> " +ohour);
- }
- idtravels.remove(0);
- }
- ////Fin////
- /* String sql = "SELECT * FROM uver.login WHERE username = 'dantzig' and pass = 1234";
- ResultSet result = st.executeQuery(sql);
- while(result.next()){
- String id = result.getString("username");
- String pas= result.getString("pass");
- System.out.println("id: " + id + " pass: " + pas);
- }
- result.close();*/
- st.close();
- conexion.close();
- } catch(Exception e){
- System.out.println("ERROR DE CONEXION" + e.getMessage());
- }
- }
- ////Métodos////
- public static String addNewUser(int rut, String name, int bornyear, int bornmonth, int bornday, int phone, String description, String mail, String password, boolean smoke, boolean pet, boolean talk, boolean music){
- String addNewUser = "INSERT INTO uver.person (rut, fullname, born, phone, description, mail, password, smoke, pet, talk, music) VALUES (" +rut+ ", '" +name+ "', '" +bornyear+ "-" +bornmonth+ "-" +bornday+ "', " +phone+ ", '" +description+ "', '" +mail+ "', '" +password+ "', " +smoke+ ", " +pet+ ", " +talk+ ", " +music+ ")";
- return addNewUser;
- }
- public static String addNewRoute(String src, String dst, int hour, int minute, int second){
- String addNewRoute = "INSERT INTO uver.route (idroute, ocity, dcity, ohour) VALUES ( " +Math.random()*100+ ", '" +src+ "', '" +dst+ "', '" +hour+ ":" +minute+ ":" +second+ "')";
- return addNewRoute;
- }
- public static String searchTravel(String src, String dst){
- String searchTravel="SELECT DISTINCT uver.travel_route.idtravel, uver.travel.seats\n" +
- "FROM uver.travel_route\n" +
- "INNER JOIN uver.route \n" +
- "ON uver.travel_route.idroute = uver.route.idroute\n" +
- "INNER JOIN uver.travel \n" +
- "ON uver.travel.idtravel = uver.travel_route.idtravel \n" +
- "WHERE uver.travel.ocity='"+src+"' AND uver.travel.dcity='"+dst+"' OR uver.route.ocity='"+src+"' AND uver.route.dcity='"+dst+"'";
- return searchTravel;
- }
- public static String searchTravel2(String idtravel){
- String searchTravel2="SELECT uver.travel.ocity, uver.travel.dcity\n" +
- "FROM uver.travel\n" +
- "WHERE uver.travel.idtravel="+idtravel+";";
- return searchTravel2;
- }
- public static String searchTravel3(String idtravel){
- String searchTravel3="SELECT uver.route.ocity, uver.route.dcity, uver.route.ohour\n" +
- "FROM uver.travel_route\n" +
- "INNER JOIN uver.route\n" +
- "ON uver.travel_route.idroute=uver.route.idroute\n" +
- "WHERE uver.travel_route.idtravel="+idtravel+";";
- return searchTravel3;
- }
- public static String searchTravel4(String idtravel){
- String searchTravel4="SELECT uver.person.fullname\n" +
- "FROM uver.person_driver\n" +
- "INNER JOIN uver.driver_travel \n" +
- "ON uver.person_driver.rut=uver.driver_travel.rut AND uver.driver_travel.idtravel="+idtravel+"\n" +
- "INNER JOIN uver.person\n" +
- "ON uver.person.rut=uver.person_driver.rut";
- return searchTravel4;
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement