/**
* Update User information where user egn is the entered egn.
*
* @param user is the user who is updated.
* @throws SQLException
*/
public void updateUser(User user) throws SQLException {
PreparedStatement statementUpdate = null;
try {
statementUpdate =
database
.getConnection()
.prepareStatement(
"UPDATE users SET user_age = ?, user_name = ?, user_email = ? WHERE user_egn = ? ");
statementUpdate.setInt(1, user.age);
statementUpdate.setString(2, user.name);
statementUpdate.setString(3, user.email);
statementUpdate.setString(4, user.egn);
statementUpdate.executeUpdate();
} catch (SQLException exc) {
exc.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
try {
if (statementUpdate != null) {
statementUpdate.close();
}
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
/**
* Find the user by enter some characters.
*
* @param character is the character which enter to find the person.
* @return list of users where find in to a database.
* @throws SQLException
*/
public List<User> findUserByEnterSomeCharacter(String character) throws SQLException {
List<User> listOfUsers = new ArrayList<User>();
PreparedStatement statementFindByChar = null;
ResultSet resultSet = null;
try {
statementFindByChar =
database
.getConnection()
.prepareCall("SELECT * FROM users WHERE user_name LIKE '" + character + "%'");
resultSet = statementFindByChar.executeQuery();
while (resultSet.next()) {
User tempUser = convertRowToUser(resultSet);
listOfUsers.add(tempUser);
}
return listOfUsers;
} catch (SQLException exc) {
exc.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
if (statementFindByChar != null) {
statementFindByChar.close();
}
if (resultSet != null) {
resultSet.close();
}
}
return null;
}
/**
* Get the count of visiting on the users in city.
*
* @return the list of users who are visit this city.
* @throws SQLException
*/
public List<String> getCountOfVisitedCities() throws SQLException {
List<String> listOfVisited = new ArrayList<String>();
PreparedStatement statementCountVisited = null;
ResultSet resultSet = null;
try {
statementCountVisited =
database
.getConnection()
.prepareStatement("SELECT city FROM trip GROUP BY city ORDER BY COUNT(city) desc");
resultSet = statementCountVisited.executeQuery();
while (resultSet.next()) {
String city = resultSet.getString("city");
listOfVisited.add(city);
}
return listOfVisited;
} catch (SQLException exc) {
exc.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
if (statementCountVisited != null) {
statementCountVisited.close();
}
if (resultSet != null) {
resultSet.close();
}
}
return null;
}
/**
* Find User Trips by city.
*
* @param city is the fended city.
* @return list of trips which are in database.
* @throws SQLException
*/
public List<Trip> findTrip(String city) throws SQLException {
List<Trip> listOfTrips = new ArrayList<Trip>();
PreparedStatement statementFindTrip = null;
ResultSet resultSet = null;
try {
statementFindTrip =
database.getConnection().prepareStatement("SELECT * FROM trip WHERE city = ? ");
statementFindTrip.setString(1, city);
resultSet = statementFindTrip.executeQuery();
while (resultSet.next()) {
Trip tempTrip = convertRowToTrip(resultSet);
listOfTrips.add(tempTrip);
}
return listOfTrips;
} catch (SQLException exc) {
exc.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
if (statementFindTrip != null) {
statementFindTrip.close();
}
if (resultSet != null) {
resultSet.close();
}
}
return null;
}
/**
* Find user by EGN.
*
* @param egn is the egn on the user to find.
* @return fended user.
* @throws SQLException
*/
public User findUser(String egn) throws SQLException {
PreparedStatement statementFindUser = null;
ResultSet resultSet = null;
try {
statementFindUser =
database.getConnection().prepareStatement("SELECT * FROM users WHERE user_egn = ?");
statementFindUser.setString(1, egn);
resultSet = statementFindUser.executeQuery();
if (resultSet.next()) {
User tempUser = convertRowToUser(resultSet);
return tempUser;
}
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
if (statementFindUser != null) {
statementFindUser.close();
}
if (resultSet != null) {
resultSet.close();
}
}
return null;
}
/**
* Get all entered users in to the database.
*
* @return the list of users.
* @throws SQLException
*/
public List<User> getUsers() throws SQLException {
List<User> listOfUsers = new ArrayList<User>();
PreparedStatement statementGetUsers = null;
ResultSet resultSet = null;
try {
statementGetUsers = database.getConnection().prepareStatement("SELECT * FROM users");
resultSet = statementGetUsers.executeQuery();
while (resultSet.next()) {
User tmpUser = convertRowToUser(resultSet);
listOfUsers.add(tmpUser);
}
return listOfUsers;
} catch (SQLException exc) {
exc.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
if (statementGetUsers != null) {
statementGetUsers.close();
}
}
return null;
}
public PersistentTravelRepository() {
try {
database = new DatabaseConf();
database.getConnection();
} catch (SQLException exc) {
exc.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
* Add new travel to the each user and execute the query.
*
* @param user is the user witch set the travel.
* @param userTrip is the trip on the user.
*/
public void addUserTravel(User user, Trip userTrip) throws SQLException {
PreparedStatement statementAddUserTravel = null;
PreparedStatement statementAddTripInformation = null;
try {
statementAddUserTravel =
database.getConnection().prepareStatement("INSERT INTO users values(?, ?, ?, ?)");
statementAddUserTravel.setInt(1, user.age);
statementAddUserTravel.setString(2, user.name);
statementAddUserTravel.setString(3, user.email);
statementAddUserTravel.setString(4, user.egn);
statementAddUserTravel.executeUpdate();
statementAddTripInformation =
database
.getConnection()
.prepareStatement(
"INSERT INTO trip(egn, date_of_arrived, departure_date, city) values(?, ?, ?, ?)");
statementAddTripInformation.setString(1, userTrip.egn);
statementAddTripInformation.setDate(2, userTrip.dateOfArrived);
statementAddTripInformation.setDate(3, userTrip.departureDate);
statementAddTripInformation.setString(4, userTrip.city);
statementAddTripInformation.executeUpdate();
} catch (SQLException exc) {
exc.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
if (statementAddTripInformation != null) {
statementAddTripInformation.close();
}
if (statementAddUserTravel != null) {
statementAddUserTravel.close();
}
}
}
/**
* Drop table from database.
*
* @param tableName is the name of the table which is to drop from database.
* @throws SQLException
*/
public void deleteTableFromDatabase(String tableName) throws SQLException {
PreparedStatement statementDeleteTable = null;
try {
statementDeleteTable =
database.getConnection().prepareStatement(String.format("DROP table %s", tableName));
statementDeleteTable.executeUpdate();
} catch (SQLException exc) {
exc.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
if (statementDeleteTable != null) {
statementDeleteTable.close();
}
}
}
/**
* Find the User which are in the some city on same time.
*
* @param date is the start date from where to start searching.
* @param dateTwo is the last date , the date to stop searching.
* @param trip is the entered trip.
* @return the list of users which are in the city.
* @throws SQLException
*/
public List<User> findUserInSameCity(Date date, Date dateTwo, Trip trip) throws SQLException {
List<User> listOfUserInSomeCity = new ArrayList<User>();
PreparedStatement statementFindSomeCity = null;
ResultSet resultSet = null;
try {
statementFindSomeCity =
database
.getConnection()
.prepareStatement(
"SELECT * FROM users INNER JOIN trip ON users.user_egn = trip.egn WHERE not trip.date_of_arrived > ? and not trip.departure_date < ? and trip.city = ? ORDER BY user_name");
statementFindSomeCity.setDate(1, dateTwo);
statementFindSomeCity.setDate(2, date);
statementFindSomeCity.setString(3, trip.city);
resultSet = statementFindSomeCity.executeQuery();
while (resultSet.next()) {
User tempUser = convertRowToUser(resultSet);
listOfUserInSomeCity.add(tempUser);
}
return listOfUserInSomeCity;
} catch (SQLException exc) {
exc.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
if (statementFindSomeCity != null) {
statementFindSomeCity.close();
}
if (resultSet != null) {
resultSet.close();
}
}
return null;
}
