Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import java.sql.*;
- import java.util.*;
- import java.util.Date;
- public class Main {
- // JDBC driver oraz database URL
- static final String DB_URL = "jdbc:mysql://ricky.heliohost.org/cackoa_people";
- // parametry bazy danych
- static final String USER = "cackoa_user";
- static final String PASS = "cackoa_password";
- public static void main(String[] args) {
- try(
- Connection conn = DriverManager.getConnection(DB_URL,USER,PASS);
- Statement stmt = conn.createStatement()
- )
- {
- /*
- która kobieta jest najmłodsza, najstarsza?
- który mężczyzna ma najdłuższe nazwisko?
- Ile jest mężczyzn a ile kobiet (patrz po pesel)?
- Jaka jest mediana wieku tych osób?
- Z którego miasta pochodzi najwięcej osób?
- */
- //która kobieta jest najmłodsza, najstarsza?
- String kobietaSql = "SELECT pe.name_surname, par.pesel FROM person as pe JOIN parameters as par ON pe.id = par.person_id " +
- "WHERE SUBSTRING(par.pesel,10,1) IN ('0','2','4','6','8')";
- ResultSet kobietaResult = stmt.executeQuery(kobietaSql);
- Date earliestDate = new Date(2300);
- Date oldestDate = new Date(1000);
- String oldestWoman = null;
- String youngestWoman = null;
- int kobietsCounter = 0;
- ArrayList<Date> median = new ArrayList<>();
- while(kobietaResult.next()) {
- kobietsCounter++;
- String surname = kobietaResult.getString("name_surname");
- String pesel = kobietaResult.getString("pesel");
- Date birthdate = calculateBirthdate(pesel);
- median.add(birthdate);
- if(birthdate.before(earliestDate)){
- earliestDate = birthdate;
- oldestWoman = surname;
- }
- if(birthdate.after(oldestDate)){
- oldestDate = birthdate;
- youngestWoman = surname;
- }
- }
- System.out.println("Najstarsza jest " + oldestWoman + " data urodzenia: " + earliestDate);
- System.out.println("Najmłodsza jest " + youngestWoman + " data urodzenia: " + oldestDate);
- //który mężczyzna ma najdłuższe nazwisko?
- String facetSql = "SELECT pe.name_surname, par.pesel FROM person as pe JOIN parameters as par ON pe.id = par.person_id " +
- "WHERE SUBSTRING(par.pesel,10,1) IN ('1','3','5','7','9')";
- ResultSet facetResult = stmt.executeQuery(facetSql);
- String longestSurname = "";
- String fullLongestSurname = null;
- int facetsCounter = 0;
- while (facetResult.next()){
- facetsCounter++;
- String surname = facetResult.getString("name_surname");
- String birthdate = facetResult.getString("pesel");
- median.add(calculateBirthdate(birthdate));
- String[] splited = surname.split("\\s+");
- if(splited[1].length() > longestSurname.length()){
- longestSurname = splited[1];
- fullLongestSurname = surname;
- }
- }
- System.out.println("Najdłuższe ma: " + fullLongestSurname);
- //Ile jest mężczyzn a ile kobiet (patrz po pesel)?
- //Przepraszam za lenistwo
- System.out.println("Kobiety: " + kobietsCounter + " Faceci: " + facetsCounter);
- //Jaka jest mediana wieku tych osób?
- long totalSeconds = 0L;
- for (Date date : median) {
- totalSeconds += date.getTime() / 1000L;
- }
- long averageSeconds = totalSeconds / median.size();
- Date averageDate = new Date(averageSeconds * 1000L);
- System.out.println("Mediana wieku: " + averageDate);
- //Z którego miasta pochodzi najwięcej osób?
- String miastaSql = "SELECT city, COUNT(city) as quantity FROM parameters GROUP BY city " +
- "ORDER BY quantity DESC " +
- "LIMIT 1";
- ResultSet miastaResult = stmt.executeQuery(miastaSql);
- while(miastaResult.next()) {
- String city = miastaResult.getString("city");
- int quantity = miastaResult.getInt("quantity");
- System.out.println("Najwięcej pochodzi z Miasto: " + city + " ilosć wystąpień: " + quantity);
- }
- stmt.close();
- conn.close();
- }catch(SQLException se){
- se.printStackTrace();
- }catch(Exception e){
- e.printStackTrace();
- }
- System.out.println("Koniec!");
- }//end main
- static Date calculateBirthdate(String pesel){
- Date mDate = new Date(Integer.parseInt(pesel.substring(0,2)), Integer.parseInt(pesel.substring(2,4)), Integer.parseInt(pesel.substring(4,6)));
- return mDate;
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement