Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- package com.company;
- import java.sql.*;
- import java.util.List;
- import java.util.ArrayList;
- public class Model {
- private Connection connection;
- public void connect() {
- String DBURL = "jdbc:mysql://sql.wiki07.nazwa.pl:3306/wiki07";
- String DBUSER = "wiki07";
- String DBPASS = "Porygon2!";
- String DBDRIVER = "com.mysql.jdbc.Driver";
- try {
- Class.forName(DBDRIVER).newInstance();
- connection = DriverManager.getConnection(DBURL, DBUSER, DBPASS);
- } catch (ClassNotFoundException | InstantiationException | IllegalAccessException | SQLException e) {
- e.printStackTrace();
- }
- }
- public void disconnect() {
- try {
- connection.close();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- }
- public int get_user(String username, String password) {
- try {
- String query = "SELECT user_id FROM account WHERE login = \""
- + username + "\" AND password = \"" + password + "\" LIMIT 0, 1";
- Statement statement = connection.createStatement();
- ResultSet result = statement.executeQuery(query);
- int user_id;
- if(!result.next())
- user_id = 0;
- else
- user_id = result.getInt("user_id");
- statement.close();
- return user_id;
- } catch (SQLException | NullPointerException e) {
- e.printStackTrace();
- return 0;
- }
- }
- public List<String[]> get_finished_visits(int user_id) {
- List<String[]> list = new ArrayList<String[]>();
- try {
- String query = "SELECT visit.start_date, visit.end_date, visit.description, personal_data.first_name, "
- + "personal_data.last_name FROM visit JOIN client ON visit.client_id JOIN account ON client.user_id "
- + "JOIN employee ON visit.employee_id JOIN personal_data ON employee.personal_id "
- + "WHERE client.user_id = " + user_id + " AND visit.status = \"zakończona\"";
- Statement statement = connection.createStatement();
- ResultSet result = statement.executeQuery(query);
- while(result.next()) {
- String[] str = new String[5];
- str[0] = result.getString("start_date");
- str[1] = result.getString("end_date");
- str[2] = result.getString("description");
- str[3] = result.getString("first_name");
- str[4] = result.getString("last_name");
- list.add(str);
- }
- statement.close();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- return list;
- }
- public List<String[]> get_active_visits(int user_id) {
- List<String[]> list = new ArrayList<String[]>();
- try {
- String query = "SELECT visit.start_date, visit.end_date, visit.description, personal_data.first_name, "
- + "personal_data.last_name FROM visit JOIN client ON visit.client_id JOIN account ON client.user_id "
- + "JOIN employee ON visit.employee_id JOIN personal_data ON employee.personal_id "
- + "WHERE client.user_id = " + user_id + " AND visit.status = \"aktywna\"";
- Statement statement = connection.createStatement();
- ResultSet result = statement.executeQuery(query);
- while(result.next()) {
- String[] str = new String[5];
- str[0] = result.getString("start_date");
- str[1] = result.getString("end_date");
- str[2] = result.getString("description");
- str[3] = result.getString("first_name");
- str[4] = result.getString("last_name");
- list.add(str);
- }
- statement.close();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- return list;
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement