Created
January 4, 2012 09:37
-
-
Save mlesikov/1559323 to your computer and use it in GitHub Desktop.
JDBC task
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| package com.mlesikov.peopletripproper; | |
| import java.sql.Connection; | |
| import java.sql.DriverManager; | |
| import java.sql.PreparedStatement; | |
| import java.sql.ResultSet; | |
| import java.sql.SQLException; | |
| import java.util.ArrayList; | |
| import java.util.Iterator; | |
| import java.util.List; | |
| public class Main { | |
| public static void main(String[] args) { | |
| try { | |
| Class.forName("com.mysql.jdbc.Driver").newInstance(); | |
| Connection connection = DriverManager | |
| .getConnection("jdbc:mysql://localhost/peopletrip?" | |
| + "user=admin&password=1234"); | |
| if (!connection.isClosed()) | |
| System.out.println("Successfully connected to " | |
| + "MySQL server ..."); | |
| DatabaseHelper dbHelper = new DatabaseHelperJDBC(connection); | |
| List<People> people = new ArrayList<People>(); | |
| RowMapper mapper = new PeopleRowMapper(); | |
| List<Trip> trip = new ArrayList<Trip>(); | |
| RowMapper tripMapper = new TripRowMapper(); | |
| RowMapper tripMapperCity = new TripRowMapperCity(); | |
| // dbHelper.exequteQuery("CREATE TABLE IF NOT EXISTS people " | |
| // + "(id bigint(20) NOT NULL auto_increment, " | |
| // + "name varchar(64) NOT NULL, " | |
| // + "egn varchar(8) NOT NULL, " | |
| // + "age varchar(3) default NULL, " | |
| // + "email varchar(30) default NULL, " + "INDEX egn (egn), " | |
| // + " PRIMARY KEY (id)) " + "ENGINE=INNODB "); | |
| // | |
| int i; | |
| /* Inserts new rows in the related tables */ | |
| // connection.setAutoCommit(false); | |
| // i = dbHelper.exequteQuery( | |
| // "INSERT INTO people(name,egn,age,email) VALUES (?,?,?,?) ", | |
| // "Ivan", "1", "22", "[email protected]"); | |
| // System.out.println("affected rows : " + i); | |
| // i = dbHelper | |
| // .exequteQuery( | |
| // "INSERT INTO trip (egn,arrivingDate,departure,city) VALUES (?,?,?,?)", | |
| // "1", "2009-03-02", "2009-03-09", "Tyrnovo"); | |
| // System.out.println("affected rows : " + i); | |
| // | |
| // i = dbHelper.exequteQuery( | |
| // "INSERT INTO people(name,egn,age,email) VALUES (?,?,?,?) ", | |
| // "Pe6o", "2", "22", "[email protected]"); | |
| // System.out.println("affected rows : " + i); | |
| // i = dbHelper | |
| // .exequteQuery( | |
| // "INSERT INTO trip (egn,arrivingDate,departure,city) VALUES (?,?,?,?)", | |
| // "2", "2009-03-06", "2009-03-15", "Tyrnovo"); | |
| // System.out.println("affected rows : " + i); | |
| // | |
| // i = dbHelper.exequteQuery( | |
| // "INSERT INTO people(name,egn,age,email) VALUES (?,?,?,?) ", | |
| // "Mi6o", "3", "22", "[email protected]"); | |
| // System.out.println("affected rows : " + i); | |
| // i = dbHelper | |
| // .exequteQuery( | |
| // "INSERT INTO trip (egn,arrivingDate,departure,city) VALUES (?,?,?,?)", | |
| // "3", "2009-03-12", "2009-03-18", "Tyrnovo"); | |
| // System.out.println("affected rows : " + i); | |
| // i = dbHelper | |
| // .exequteQuery( | |
| // "INSERT INTO trip (egn,arrivingDate,departure,city) VALUES (?,?,?,?)", | |
| // "3", "2009-03-25", "2009-03-29", "Sofia"); | |
| // System.out.println("affected rows : " + i); | |
| // | |
| // | |
| // | |
| // i = dbHelper.exequteQuery( | |
| // "INSERT INTO people(name,egn,age,email) VALUES (?,?,?,?) ", | |
| // "Miro", "4", "22", "[email protected]"); | |
| // System.out.println("affected rows : " + i); | |
| // i = dbHelper | |
| // .exequteQuery( | |
| // "INSERT INTO trip (egn,arrivingDate,departure,city) VALUES (?,?,?,?)", | |
| // "4", "2009-03-18", "2009-03-20", "Tyrnovo"); | |
| // System.out.println("affected rows : " + i); | |
| // i = dbHelper | |
| // .exequteQuery( | |
| // "INSERT INTO trip (egn,arrivingDate,departure,city) VALUES (?,?,?,?)", | |
| // "4", "2009-03-21", "2009-03-22", "London"); | |
| // System.out.println("affected rows : " + i); | |
| // i = dbHelper | |
| // .exequteQuery( | |
| // "INSERT INTO trip (egn,arrivingDate,departure,city) VALUES (?,?,?,?)", | |
| // "4", "2009-03-24", "2009-03-28", "Sofia"); | |
| // System.out.println("affected rows : " + i); | |
| // | |
| // connection.commit(); | |
| // connection.setAutoCommit(true); | |
| // ///////////////////////////////////////////////////////////////////////// | |
| // | |
| // /*Updates a specified column in the table */ | |
| // connection.setAutoCommit(false); | |
| // people = dbHelper.executeQuery( | |
| // "SELECT * FROM people WHERE egn = ? ", mapper, "1234"); | |
| // trip = trip = dbHelper.executeQuery( | |
| // "SELECT * FROM trip WHERE egn = ? ", tripMapper, "1234"); | |
| // | |
| // i = dbHelper | |
| // .exequteQuery("DELETE FROM trip WHERE egn = ? ","4321"); | |
| // System.out.println("affected rows : " + i); | |
| // i = dbHelper | |
| // .exequteQuery("DELETE FROM people WHERE egn =? ","4321"); | |
| // System.out.println("affected rows : " + i); | |
| // | |
| // i = dbHelper.exequteQuery( | |
| // "INSERT INTO people(name,egn,age,email) VALUES (?,?,?,?) ", | |
| // "Miro", "4321", "22", "[email protected]"); | |
| // System.out.println("affected rows : " + i); | |
| // i = dbHelper | |
| // .exequteQuery( | |
| // "INSERT INTO trip (egn,arrivingDate,departure,city) VALUES (?,?,?,?)", | |
| // "4321", "2009-03-25", "2009-03-28", "Tyrnovo"); | |
| // System.out.println("affected rows : " + i); | |
| // connection.commit(); | |
| // connection.setAutoCommit(true); | |
| // ////////////////////////////////////////////////////////////////////////////////// | |
| /* Selects everything from table people and type it on the console */ | |
| // people = dbHelper.executeQuery("SELECT * FROM people", mapper); | |
| // for (People arg : people) { | |
| // System.out.println("name : " + arg.getName()); | |
| // System.out.println("age : " + arg.getAge()); | |
| // System.out.println("egn : " + arg.getEgn()); | |
| // System.out.println("email : " + arg.getEmail()); | |
| // } | |
| // //////////////////////////////////////////////////////////////// | |
| /* | |
| * Selects any person with name starts with a specified symbol(s) | |
| * from table people and type it on the console | |
| */ | |
| // people = | |
| // dbHelper.executeQuery("SELECT * FROM people WHERE name LIKE ? ", | |
| // mapper,"d%"); | |
| // for (People arg : people) { | |
| // System.out.println("name : " + arg.getName()); | |
| // System.out.println("age : " + arg.getAge()); | |
| // System.out.println("egn : " + arg.getEgn()); | |
| // System.out.println("email : " + arg.getEmail()); | |
| // } | |
| // ///////////////////////////////////////////////////////////////////////////// | |
| /* | |
| * Deletes all rows from trip first because there is a foreign key | |
| * and after that we can delete the parent table- people | |
| */ | |
| // i = dbHelper | |
| // .exequteQuery("DELETE FROM trip "); | |
| // System.out.println("affected rows : " + i); | |
| // i = dbHelper | |
| // .exequteQuery("DELETE FROM people "); | |
| // System.out.println("affected rows : " + i); | |
| // ///////////////////////////////////////////////////////////////////////////////////// | |
| /* | |
| * Deletes the table from the database first trip because there is a | |
| * foreign key and after that we can delete the parent table- people | |
| */ | |
| // i = dbHelper | |
| // .exequteQuery("DROP trip "); | |
| // System.out.println("affected rows : " + i); | |
| // i = dbHelper | |
| // .exequteQuery("DROP people "); | |
| // System.out.println("affected rows : " + i); | |
| // ///////////////////////////////////////////////////////////////////////////////////// | |
| /* Shows all cities sorted by how many people have visited them */ | |
| // | |
| // trip = dbHelper | |
| // .executeQuery( | |
| // "SELECT city,COUNT(city) as counter FROM trip GROUP BY city ORDER BY counter DESC ", | |
| // tripMapperCity); | |
| // | |
| // for (Trip arg : trip) { | |
| // // System.out.println("egn : " + arg.getEgn()); | |
| // // System.out.println("arrivingDate : " + | |
| // // arg.getArrivingDate()); | |
| // // System.out.println("departure : " + arg.getDeparture()); | |
| // System.out.println("city : " + arg.getCity()); | |
| // } | |
| // /////////////////////////////////////////////////////////////////////////////////////////// | |
| /* | |
| * Selects all the people that are in the same period of time in the | |
| * same city | |
| */ | |
| // | |
| // people = dbHelper | |
| // .executeQuery( | |
| // // "SELECT * FROM people WHERE people.egn = (SELECT egn FROM trip WHERE (city = ?)AND(departure BETWEEN ? AND ?)OR(arrivingDate BETWEEN ? AND ? )) ", | |
| // // mapper,"Tyrnovo","2009-03-05","2009-03-17","2009-03-05","2009-03-17"); | |
| // // "SELECT * FROM people INNER JOIN (SELECT egn FROM trip WHERE (city = ?)AND(departure BETWEEN ? AND ?)OR(arrivingDate BETWEEN ? AND ? )) ", | |
| // // mapper,"Tyrnovo","2009-03-05","2009-03-17","2009-03-05","2009-03-17"); | |
| // "SELECT people.name,people.age,people.egn,people.email FROM people,trip WHERE (people.egn = trip.egn)AND (trip.city = ?) AND((trip.departure BETWEEN ? AND ?)OR(trip.arrivingDate BETWEEN ? AND ? )) ", | |
| // mapper, "Tyrnovo", "2009-03-05", "2009-03-17", | |
| // "2009-03-05", "2009-03-17"); | |
| //// | |
| // for (People arg : people) { | |
| // System.out.println("name : " + arg.getName()); | |
| // System.out.println("age : " + arg.getAge()); | |
| // System.out.println("egn : " + arg.getEgn()); | |
| // System.out.println("email : " + arg.getEmail()); | |
| // } | |
| } catch (InstantiationException e) { | |
| // TODO Auto-generated catch block | |
| e.printStackTrace(); | |
| } catch (IllegalAccessException e) { | |
| // TODO Auto-generated catch block | |
| e.printStackTrace(); | |
| } catch (ClassNotFoundException e) { | |
| // TODO Auto-generated catch block | |
| e.printStackTrace(); | |
| } catch (SQLException e) { | |
| // TODO Auto-generated catch block | |
| e.printStackTrace(); | |
| } | |
| } | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment