· 8 years ago · Mar 26, 2018, 03:04 PM
1/*
2 * To change this license header, choose License Headers in Project Properties.
3 * To change this template file, choose Tools | Templates
4 * and open the template in the editor.
5 */
6package MyWeb.Database;
7
8import Database.AuthenticationInfo;
9import Database.IConnectionsPool;
10import Database.IUuidToAuthenticationInfo;
11import Database.IUuidToLastActive;
12import java.sql.Connection;
13import java.sql.SQLException;
14import java.sql.Statement;
15import Database.Table;
16import Database.UUID;
17import java.sql.ResultSet;
18import java.sql.CallableStatement;
19import org.mindrot.jbcrypt.BCrypt;
20
21/**
22 *
23 * @author EngineeringStudent
24 */
25public class TableUuidToLastActive extends Table implements IUuidToLastActive {
26
27 public TableUuidToLastActive(IConnectionsPool iConnectionsPool) {
28 super(iConnectionsPool);
29 }
30
31 @Override
32 public void createIfNotExists() {
33 Connection conn = null;
34 Statement st = null;
35 String[] strs = {"CREATE TABLE IF NOT EXISTS `uuid_to_last_active`"
36 + "("
37 + "`userId` BINARY(16) NOT NULL,"
38 + "`lastActive` BIGINT(20), "
39 + "INDEX ` indexUserId` (`userId`),"
40 + "INDEX ` indexLastActive` (`lastActive`),"
41 + "PRIMARY KEY (`userId`)"
42 + ")",
43
44 "DROP PROCEDURE IF EXISTS `uuid_to_last_active_get`; ",
45 "CREATE PROCEDURE `uuid_to_last_active_get`("
46 + "IN userIdIn VARCHAR(32)"
47 + ")"
48 + "BEGIN "
49 + "select * from uuid_to_last_active where userId=UNHEX(userIdIn);"
50 + " END;",
51
52 "DROP PROCEDURE IF EXISTS `uuid_to_last_active_set`; ",
53 "CREATE PROCEDURE `uuid_to_last_active_set` ("
54 + "IN userIdIn VARCHAR(32),"
55 + "IN lastActiveIn BIGINT(20)"
56 + ")INSERT INTO uuid_to_last_active(userId, lastActive) VALUES(UNHEX(userIdIn) , lastActiveIn)ON DUPLICATE KEY UPDATE lastActive=lastActiveIn;",
57
58 "DROP PROCEDURE IF EXISTS `uuid_to_last_active_delete`; ",
59 "CREATE PROCEDURE `uuid_to_last_active_delete` ("
60 + "IN userIdIn VARCHAR(32)"
61 + ")DELETE FROM uuid_to_last_active WHERE userId =UNHEX(userIdIn);"};
62 try {
63 conn = getConnection();
64 st = conn.createStatement();
65 for (String str : strs)
66 st.executeUpdate(str);
67 } catch (SQLException se) {
68 se.printStackTrace();
69 } catch (Exception e) {
70 e.printStackTrace();
71 } finally {
72 try {
73 if (st != null) {
74 st.close();
75 }
76 } catch (SQLException se) {
77 }
78 try {
79 if (conn != null) {
80 conn.close();
81 }
82 } catch (SQLException se) {
83 se.printStackTrace();
84 }
85 }
86 }
87
88 @Override
89 public Long get(UUID uuid) throws Exception {
90 Connection conn = null;
91 CallableStatement st = null;
92 try {
93 conn = getConnection();
94 String str = "CALL `uuid_to_last_active_get`(?);";
95 st = conn.prepareCall(str);
96 st.setString(1, uuid.getShortVersion());
97 ResultSet rS = st.executeQuery();
98 if (rS.next()) {
99 return rS.getLong("lastActive");
100 }
101 return null;
102 } catch (SQLException se) {
103 se.printStackTrace();
104 throw se;
105 } catch (Exception e) {
106 e.printStackTrace();
107 throw e;
108 } finally {
109 try {
110 if (st != null) {
111 st.close();
112 }
113 } catch (SQLException se) {
114 }
115 try {
116 if (conn != null) {
117 conn.close();
118 }
119 } catch (SQLException se) {
120 se.printStackTrace();
121 }
122 }
123 }
124
125 @Override
126 public void set(UUID u, long millis) throws Exception {
127
128 Connection conn = null;
129 CallableStatement st = null;
130 try {
131 conn = getConnection();
132 String str = "{CALL `uuid_to_last_active_set`(?, ?)}";
133 st = conn.prepareCall(str);
134 st.setString(1, u.getShortVersion());
135 st.setLong( 2, millis);
136 st.executeUpdate();
137 } catch (SQLException se) {
138 se.printStackTrace();
139 throw se;
140 } catch (Exception e) {
141 e.printStackTrace();
142 throw e;
143 } finally {
144 try {
145 if (st != null) {
146 st.close();
147 }
148 } catch (SQLException se) {
149 }
150 try {
151 if (conn != null) {
152 conn.close();
153 }
154 } catch (SQLException se) {
155 se.printStackTrace();
156 }
157 }
158 }
159
160 public void delete(UUID u) throws Exception {
161 Connection conn = null;
162 CallableStatement st = null;
163 try {
164 conn = getConnection();
165 String str = "{CALL `uuid_to_last_active_delete`(?)}";
166 st = conn.prepareCall(str);
167 st.setString(1, u.getShortVersion());
168 st.executeUpdate();
169 } catch (SQLException se) {
170 se.printStackTrace();
171 throw se;
172 } catch (Exception e) {
173 e.printStackTrace();
174 throw e;
175 } finally {
176 try {
177 if (st != null) {
178 st.close();
179 }
180 } catch (SQLException se) {
181 }
182 try {
183 if (conn != null) {
184 conn.close();
185 }
186 } catch (SQLException se) {
187 se.printStackTrace();
188 }
189 }
190 }
191
192 public Boolean test() throws Exception {
193 UUID uuid = new UUID("abcdefabcdefabcdefabcdefabcdefab");
194 try {
195 if (get(uuid) != null) {
196 delete(uuid);
197 }
198 set(uuid, 10000);
199 Long millis = get(uuid);
200 if (millis==10000) {
201 delete(uuid);
202 return true;
203 }
204 } catch (Exception ex) {
205 ex.printStackTrace();
206 }
207 return false;
208 }
209}