Zinh1NSn

1package DB;
2
3import java.sql.Connection;
4import java.sql.DriverManager;
5import java.sql.SQLException;
6import java.util.Stack;
7
8//we handle all the connection no more then 10.....
9public class ConnectionPool {
10    private static final int NUM_OF_CONS = 10;
11    private static ConnectionPool instance = null;
12    private Stack<Connection> connections = new Stack<>();
13
14    private ConnectionPool() throws SQLException {
15        //open all connections
16        openAllConnections();
17    }
18
19    public static ConnectionPool getInstance() {
20        //before locking the critical code...
21        if (instance == null) {
22            //create the connection pool
23            synchronized (ConnectionPool.class) {
24                //before creating the code.....
25                if (instance == null) {
26                    try {
27                        instance = new ConnectionPool();
28                    } catch (SQLException throwables) {
29                        throwables.printStackTrace();
30                    }
31                }
32            }
33        }
34        return instance;
35    }
36
37    public Connection getConnection() throws InterruptedException{
38        synchronized (connections){
39            if (connections.isEmpty()){
40                //wait until we will get a connection back
41                connections.wait();
42            }
43            return connections.pop();
44        }
45    }
46
47    public void returnConnection(Connection connection){
48        synchronized (connections){
49            connections.push(connection);
50            //notify that we got back a connection from the user...
51            connections.notify();
52        }
53    }
54
55    /*
56        this method will open 10 connection in advanced
57        @throws SQLException
58     */
59
60    private void openAllConnections() throws SQLException{
61        for (int index=0;index < NUM_OF_CONS;index+=1){
62            Connection connection = DriverManager.getConnection(DatabaseManager.url,DatabaseManager.username,DatabaseManager.password);
63            connections.push(connection);
64        }
65    }
66
67    public void closeAllConnection() throws InterruptedException{
68        synchronized (connections){
69            while (connections.size()<NUM_OF_CONS){
70                connections.wait();
71            }
72            connections.removeAllElements();
73        }
74    }
75}
76===============================
77package DB;
78
79import java.sql.SQLException;
80
81public class DatabaseManager {
82    //conection string for connection to the mysql/mariadb server
83    public static final String url="jdbc:mysql://localhost:3306?createDatabaseIfNotExist=FALSE&useTimezone=TRUE&serverTimezone=UTC";
84    public static final String username = "root";
85    public static final String password = "";
86
87    private static final String CREATE_DB = "CREATE DATABASE vets";
88    private static final String DROP_DB = "DROP DATABASE vets";
89    private static final String CREATE_TABLE_CATS = "CREATE TABLE IF NOT EXISTS `vets`.`cats` (`id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT, " +
90            "`name` VARCHAR(30) NOT NULL, `age` FLOAT NOT NULL , `color` VARCHAR(30) NOT NULL)";
91    private static final String DROP_TABLE_CATS = "DROP TABLE `vets`.`cats`";
92    //create table and schema
93
94
95    public static void createDataBase() throws SQLException{
96        DBUtils.runQuery(CREATE_DB);
97    }
98
99    public static void dropDataBase() throws SQLException{
100        DBUtils.runQuery(DROP_DB);
101    }
102
103    public static void createTableCats() throws SQLException{
104        DBUtils.runQuery(CREATE_TABLE_CATS);
105    }
106
107    public static void dropTableCats() throws SQLException{
108        DBUtils.runQuery(DROP_TABLE_CATS);
109    }
110
111
112}
113==========================
114package DB;
115
116import java.sql.Connection;
117import java.sql.PreparedStatement;
118import java.sql.ResultSet;
119
120public class DBUtils {
121    public static void runQuery(String sql){
122        //drop table , delete , alter
123        Connection connection = null;
124        try{
125            //taking a connection from connection pool
126            connection = ConnectionPool.getInstance().getConnection();
127
128            //run the sql command
129            PreparedStatement statement = connection.prepareStatement(sql);
130            statement.execute();
131        } catch (Exception e){
132            e.printStackTrace();
133        } finally {
134            ConnectionPool.getInstance().returnConnection(connection);
135        }
136    }
137
138    public static ResultSet runQueryWithResult(String sql){
139        //drop table , delete , alter
140        ResultSet resultSet=null;
141        Connection connection = null;
142        try{
143            //taking a connection from connection pool
144            connection = ConnectionPool.getInstance().getConnection();
145
146            //run the sql command
147            PreparedStatement statement = connection.prepareStatement(sql);
148            resultSet = statement.executeQuery();
149        } catch (Exception e){
150            e.printStackTrace();
151        } finally {
152            ConnectionPool.getInstance().returnConnection(connection);
153        }
154        return resultSet;
155    }
156}
157