yMvnbij8

1package com.szinn.employee.server.service;
2
3import com.mchange.v2.c3p0.ComboPooledDataSource;
4import com.szinn.employee.shared.Employee;
5
6import java.beans.PropertyVetoException;
7import java.sql.*;
8import java.util.ArrayList;
9
10public class EmployeeDAOImpl implements EmployeeDAO {
11
12    private static final EmployeeDAOImpl instance = new EmployeeDAOImpl();
13    private Connection connection;
14    private ComboPooledDataSource dataSource;
15
16    private EmployeeDAOImpl() {
17        this.dataSource = getDataSource();
18    }
19
20    public static EmployeeDAOImpl getInstance() {
21        return instance;
22    }
23
24    public long addEmployee(String firstName, String middleName, String lastName, Date birthday) throws SQLException {
25        connection = dataSource.getConnection();
26
27        PreparedStatement pstmt = connection.prepareStatement(
28                "INSERT INTO employees_db.employees (first_name, middle_name, last_name, birthday) " +
29                        "VALUES (?, ?, ?, ?);", Statement.RETURN_GENERATED_KEYS);
30
31
32        pstmt.setString(1, firstName);
33        pstmt.setString(2, middleName);
34        pstmt.setString(3, lastName);
35        pstmt.setDate(4, birthday);
36
37        pstmt.execute();
38
39        ResultSet generatedKeys = pstmt.getGeneratedKeys();
40        if (generatedKeys.next()) {
41            long id = generatedKeys.getLong(1);
42            pstmt.close();
43            return id;
44        } else {
45            pstmt.close();
46            throw new SQLException();
47        }
48    }
49
50    public ArrayList<Employee> getAll() throws SQLException {
51        ArrayList<Employee> list = new ArrayList<>();
52
53        connection = dataSource.getConnection();
54
55        Statement stmt = connection.createStatement();
56        ResultSet rs = stmt.executeQuery("SELECT * FROM employees_db.employees;");
57        while (rs.next()) {
58            int id = rs.getInt(1);
59            String firstName = rs.getString(2);
60            String middleName = rs.getString(3);
61            String lastName = rs.getString(4);
62            Date birthday = rs.getDate(5);
63            list.add(new Employee(id, firstName, middleName, lastName, birthday));
64        }
65        return list;
66    }
67
68    public void updateByID(int id, String first, String middle, String last, Date birthday) throws SQLException {
69        connection = dataSource.getConnection();
70
71        PreparedStatement pstmt = connection.prepareStatement(
72                "UPDATE employees_db.employees SET first_name = ?, middle_name = ?, last_name = ?, birthday = ? where id = ?;");
73        pstmt.setString(1, first);
74        pstmt.setString(2, middle);
75        pstmt.setString(3, last);
76        pstmt.setDate(4, birthday);
77        pstmt.setLong(5, id);
78        pstmt.execute();
79        pstmt.close();
80    }
81
82    public void deleteById(int id) throws SQLException {
83        connection = dataSource.getConnection();
84
85        PreparedStatement pstmt  = connection.prepareStatement("DELETE FROM employees_db.employees WHERE ID = ?");
86        pstmt.setLong(1, id);
87        pstmt.execute();
88        pstmt.close();
89    }
90
91    public void create() {
92        try {
93            connection = dataSource.getConnection();
94        } catch (SQLException e) {
95            e.printStackTrace();
96        }
97
98        try (Statement stmt = connection.createStatement()) {
99            stmt.execute("CREATE TABLE IF NOT EXISTS `employees` (\n" +
100                    "  `id` int(11) NOT NULL AUTO_INCREMENT,\n" +
101                    "  `first_name` varchar(45) NOT NULL,\n" +
102                    "  `middle_name` varchar(45) NOT NULL,\n" +
103                    "  `last_name` varchar(45) NOT NULL,\n" +
104                    "  `birthday` date DEFAULT NULL,\n" +
105                    "  PRIMARY KEY (`id`)\n" +
106                    ") ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;");
107        } catch (SQLException e) {
108            e.printStackTrace();
109        }
110    }
111
112    public void clearTable() throws SQLException {
113        connection = dataSource.getConnection();
114        Statement stmt = connection.createStatement();
115        stmt.execute("TRUNCATE employees_db.employees;");
116        stmt.close();
117    }
118
119    private static Connection getMysqlConnection() {
120        try {
121            DriverManager.registerDriver((Driver) Class.forName("com.mysql.jdbc.Driver").newInstance());
122            StringBuilder url = new StringBuilder();
123            url.
124                    append("jdbc:mysql://").        //db type
125                    append("localhost:").           //host name
126                    append("3306/").                //port
127                    append("employees_db?").        //db name
128                    append("user=root&").           //login
129                    append("password=password&").   // password
130                    append("characterEncoding=utf8&"). // encoding
131                    append("relaxAutoCommit=true"); // relaxAutoCommit
132
133            System.out.println("URL: " + url + "\n");
134
135            return DriverManager.getConnection(url.toString());
136        } catch (SQLException | InstantiationException | IllegalAccessException | ClassNotFoundException e) {
137            e.printStackTrace();
138        }
139        return null;
140    }
141
142    public static ComboPooledDataSource getDataSource() {
143        ComboPooledDataSource cpds = new ComboPooledDataSource();
144        cpds.setJdbcUrl("jdbc:mysql://localhost:3306/employees_db");
145        cpds.setUser("root");
146        cpds.setPassword("password");
147
148        // Optional Settings
149        cpds.setInitialPoolSize(5);
150        cpds.setMinPoolSize(5);
151        cpds.setAcquireIncrement(5);
152        cpds.setMaxPoolSize(20);
153        cpds.setMaxStatements(100);
154        cpds.setMaxIdleTime(10000);
155
156        return cpds;
157    }
158
159}