1 |
duarte |
15 |
//package clusterconnection; |
2 |
|
|
import java.sql.*; |
3 |
|
|
|
4 |
|
|
/** |
5 |
|
|
* ClusterConnection class to wrap the master/node mysql servers so that is transparent to other programs |
6 |
|
|
* @author Jose Duarte |
7 |
|
|
*/ |
8 |
|
|
//TODO Maybe we should always pass the key for CreateStatement and the constructor and drop the 2 methods without key. |
9 |
|
|
//TODO I think that should make things easier. To have to remember to be switching keys is not ideal, I'd rather pass it always as a parameter in CreateStatement |
10 |
|
|
//TODO The only problem with this approach is when constructing the object the key must be passed. That shouldn't be a major problem anyway. Revise all this |
11 |
|
|
|
12 |
|
|
public class ClusterConnection { |
13 |
|
|
|
14 |
|
|
private final String url= "jdbc:mysql://"; |
15 |
|
|
private final String masterDb="key_master"; |
16 |
|
|
private final String masterHost="white"; |
17 |
|
|
private Connection nCon; |
18 |
|
|
private Connection mCon; |
19 |
|
|
public String keyTable; |
20 |
|
|
public String key; |
21 |
|
|
public String idxColumn; |
22 |
|
|
public String host; |
23 |
|
|
public String db; |
24 |
|
|
private String user; |
25 |
|
|
private String password; |
26 |
|
|
|
27 |
|
|
//create a ClusterConnection passing a key. Then we need to call createNConStatement only passing the idx as argument. |
28 |
|
|
public ClusterConnection (String db,String key, String user,String password) { |
29 |
|
|
loadMySQLDriver(); |
30 |
|
|
setDb(db); |
31 |
|
|
setKey(key); |
32 |
|
|
setIdxColumn(); |
33 |
|
|
setMasterTable(db); |
34 |
|
|
setUser(user); |
35 |
|
|
setPassword(password); |
36 |
|
|
try { |
37 |
|
|
// For nCon we create a connection to the master too. |
38 |
|
|
// This is just a place holder because the actual node connection is not created until we create the statement |
39 |
|
|
// If we don't do this then when closing the two connections an exception might occurr because we try to close a non existing object |
40 |
|
|
this.nCon = DriverManager.getConnection(url+masterHost+"/"+masterDb,user,password); |
41 |
|
|
this.mCon = DriverManager.getConnection(url+masterHost+"/"+masterDb,user,password); |
42 |
|
|
} |
43 |
|
|
catch(SQLException e){ |
44 |
|
|
System.out.println("SQLException: " + e.getMessage()); |
45 |
|
|
System.out.println("SQLState: " + e.getSQLState()); |
46 |
|
|
System.out.println("VendorError: " + e.getErrorCode()); |
47 |
|
|
System.out.println("Couldn't get connection to master host "+masterHost+", db="+masterDb+", exiting."); |
48 |
|
|
System.exit(2); |
49 |
|
|
} |
50 |
|
|
} |
51 |
|
|
|
52 |
|
|
// create a cluster connection with an empty key field. In this case we need to call createNConStatement passing the key as argument |
53 |
|
|
public ClusterConnection (String db, String user,String password) { |
54 |
|
|
this.loadMySQLDriver(); |
55 |
|
|
setDb(db); |
56 |
|
|
setUser(user); |
57 |
|
|
setPassword(password); |
58 |
|
|
try { |
59 |
|
|
// For nCon we create a connection to the master too. |
60 |
|
|
// This is just a place holder because the actual node connection is not created until we create the statement |
61 |
|
|
// If we don't do this then when closing the two connections an exception might occurr because we try to close a non existing object |
62 |
|
|
this.nCon = DriverManager.getConnection(url+masterHost+"/"+masterDb,user,password); |
63 |
|
|
this.mCon = DriverManager.getConnection(url+masterHost+"/"+masterDb,user,password); |
64 |
|
|
} |
65 |
|
|
catch(SQLException e){ |
66 |
|
|
System.out.println("SQLException: " + e.getMessage()); |
67 |
|
|
System.out.println("SQLState: " + e.getSQLState()); |
68 |
|
|
System.out.println("VendorError: " + e.getErrorCode()); |
69 |
|
|
System.out.println("Couldn't get connection to master host "+masterHost+", db="+masterDb+", exiting."); |
70 |
|
|
System.exit(2); |
71 |
|
|
} |
72 |
|
|
} |
73 |
|
|
|
74 |
|
|
|
75 |
|
|
public void loadMySQLDriver() { |
76 |
|
|
try { |
77 |
|
|
Class.forName("com.mysql.jdbc.Driver"); |
78 |
|
|
} |
79 |
|
|
catch(Exception e) { |
80 |
|
|
System.out.println(e.getMessage()); |
81 |
|
|
System.out.println("An exception occurred while loading the mysql jdbc driver, exiting."); |
82 |
|
|
System.exit(1); |
83 |
|
|
} |
84 |
|
|
} |
85 |
|
|
public void close() { |
86 |
|
|
try { |
87 |
|
|
this.nCon.close(); |
88 |
|
|
this.mCon.close(); |
89 |
|
|
} |
90 |
|
|
catch(SQLException e) { |
91 |
|
|
System.out.println("Couldn't close database connections for master: "+masterHost+" and node: "+this.host+", exiting."); |
92 |
|
|
System.out.println("SQLException: " + e.getMessage()); |
93 |
|
|
System.out.println("SQLState: " + e.getSQLState()); |
94 |
|
|
System.exit(3); |
95 |
|
|
} |
96 |
|
|
} |
97 |
|
|
|
98 |
|
|
public String getHost4Idx (int idx) { |
99 |
|
|
String host=""; |
100 |
|
|
Statement S; |
101 |
|
|
ResultSet R; |
102 |
|
|
try { |
103 |
|
|
S=mCon.createStatement(); |
104 |
|
|
String query="SELECT client_name FROM "+keyTable+" AS m INNER JOIN clients_names AS c "+ |
105 |
|
|
"ON (m.client_id=c.client_id) WHERE "+idxColumn+"="+idx+";"; |
106 |
|
|
R=S.executeQuery(query); |
107 |
|
|
if (R.next()){ |
108 |
|
|
host=R.getString(1); |
109 |
|
|
} |
110 |
|
|
S.close(); |
111 |
|
|
R.close(); |
112 |
|
|
} |
113 |
|
|
catch(SQLException e) { |
114 |
|
|
System.out.println("Couldn't get the host name for idx "+idx+", exiting"); |
115 |
|
|
System.out.println("SQLException: " + e.getMessage()); |
116 |
|
|
System.out.println("SQLState: " + e.getSQLState()); |
117 |
|
|
System.exit(3); |
118 |
|
|
} |
119 |
|
|
return host; |
120 |
|
|
} |
121 |
|
|
|
122 |
|
|
public void setHostFromIdx(int idx){ |
123 |
|
|
setHost(getHost4Idx(idx)); |
124 |
|
|
} |
125 |
|
|
|
126 |
|
|
public void setHost(String host) { |
127 |
|
|
this.host=host; |
128 |
|
|
try { |
129 |
|
|
this.nCon=DriverManager.getConnection(url+host+"/"+db,user,password); |
130 |
|
|
} |
131 |
|
|
catch (SQLException e){ |
132 |
|
|
System.out.println("SQLException: " + e.getMessage()); |
133 |
|
|
System.out.println("SQLState: " + e.getSQLState()); |
134 |
|
|
System.out.println("Couldn't get connection to node "+host+", exiting."); |
135 |
|
|
System.exit(2); |
136 |
|
|
} |
137 |
|
|
} |
138 |
|
|
|
139 |
|
|
public Statement createStatement(int idx) { // to use when the field "key" is already set |
140 |
|
|
setMasterTable(); |
141 |
|
|
setIdxColumn(); |
142 |
|
|
Statement S=null; |
143 |
|
|
this.setHostFromIdx(idx); |
144 |
|
|
try { |
145 |
|
|
S=this.nCon.createStatement(); |
146 |
|
|
} |
147 |
|
|
catch (SQLException e){ |
148 |
|
|
System.out.println("SQLException: " + e.getMessage()); |
149 |
|
|
System.out.println("SQLState: " + e.getSQLState()); |
150 |
|
|
System.out.println("Couldn't create statement for the node connection, idx= "+idx+", exiting."); |
151 |
|
|
System.exit(2); |
152 |
|
|
} |
153 |
|
|
return S; |
154 |
|
|
} |
155 |
|
|
|
156 |
|
|
public Statement createStatement(String key,int idx) { |
157 |
|
|
setKey(key); |
158 |
|
|
setMasterTable(); |
159 |
|
|
setIdxColumn(); |
160 |
|
|
return createStatement(idx); |
161 |
|
|
} |
162 |
|
|
|
163 |
|
|
public void setMasterTable(String db) { // to set masterTable field in constructor (i.e. first time) |
164 |
|
|
this.keyTable=db+"_"+this.key+"_list_master"; |
165 |
|
|
} |
166 |
|
|
|
167 |
|
|
public void setMasterTable() { // to set masterTable field when db is already set |
168 |
|
|
this.keyTable=this.db+"_"+this.key+"_list_master"; |
169 |
|
|
} |
170 |
|
|
|
171 |
|
|
public String getMasterTable() { |
172 |
|
|
return this.keyTable; |
173 |
|
|
} |
174 |
|
|
|
175 |
|
|
public void setIdxColumn() { |
176 |
|
|
this.idxColumn=this.key+"_id"; |
177 |
|
|
} |
178 |
|
|
|
179 |
|
|
public void setUser(String user) { |
180 |
|
|
this.user=user; |
181 |
|
|
} |
182 |
|
|
|
183 |
|
|
public void setPassword(String password) { |
184 |
|
|
this.password=password; |
185 |
|
|
} |
186 |
|
|
|
187 |
|
|
public void setDb(String db){ |
188 |
|
|
this.db=db; |
189 |
|
|
} |
190 |
|
|
|
191 |
|
|
public void setKey(String key){ |
192 |
|
|
this.key=key; |
193 |
|
|
} |
194 |
|
|
|
195 |
|
|
public void switchKey(String key){ |
196 |
|
|
this.key=key; |
197 |
|
|
setMasterTable(); |
198 |
|
|
setIdxColumn(); |
199 |
|
|
} |
200 |
|
|
|
201 |
|
|
public Statement createMasterStatement() { |
202 |
|
|
Statement S=null; |
203 |
|
|
try { |
204 |
|
|
S=this.mCon.createStatement(); |
205 |
|
|
} |
206 |
|
|
catch (SQLException e){ |
207 |
|
|
System.out.println("SQLException: " + e.getMessage()); |
208 |
|
|
System.out.println("SQLState: " + e.getSQLState()); |
209 |
|
|
System.out.println("Couldn't create statement for the master connection, exiting."); |
210 |
|
|
System.exit(2); |
211 |
|
|
} |
212 |
|
|
return S; |
213 |
|
|
} |
214 |
|
|
|
215 |
|
|
public ResultSet getAllIdxFromMaster(String key) { |
216 |
|
|
this.setKey(key); |
217 |
|
|
this.setIdxColumn(); |
218 |
|
|
this.setMasterTable(); |
219 |
|
|
String query; |
220 |
|
|
Statement S; |
221 |
|
|
ResultSet R=null; |
222 |
|
|
try { |
223 |
|
|
query="SELECT "+idxColumn+" FROM "+keyTable+";"; |
224 |
|
|
S=this.mCon.createStatement(); |
225 |
|
|
R=S.executeQuery(query); |
226 |
|
|
//S.close(); // apparently it doesn't work if we close the Statement!! Don't know why! |
227 |
|
|
} |
228 |
|
|
catch (SQLException e){ |
229 |
|
|
System.out.println("SQLException: " + e.getMessage()); |
230 |
|
|
System.out.println("SQLState: " + e.getSQLState()); |
231 |
|
|
System.out.println("Couldn't get all indices from columnn "+idxColumn+" in table "+keyTable+" from "+masterDb+" database in "+masterHost+", exiting."); |
232 |
|
|
System.exit(2); |
233 |
|
|
} |
234 |
|
|
return R; |
235 |
|
|
} |
236 |
|
|
|
237 |
|
|
// to get client_id for a certain idx and key |
238 |
|
|
//TODO will need to change the query. In general this method would return more than 1 client_id if the idx is not unique |
239 |
|
|
public int getHostId4Idx (String key,int idx) { |
240 |
|
|
int hostId=0; |
241 |
|
|
this.setKey(key); |
242 |
|
|
this.setIdxColumn(); |
243 |
|
|
this.setMasterTable(); |
244 |
|
|
Statement S; |
245 |
|
|
ResultSet R; |
246 |
|
|
String query; |
247 |
|
|
int countCids=0; |
248 |
|
|
try { |
249 |
|
|
S=mCon.createStatement(); |
250 |
|
|
query="SELECT count(client_id) FROM "+keyTable+" WHERE "+idxColumn+"="+idx+";"; |
251 |
|
|
R=S.executeQuery(query); |
252 |
|
|
if (R.next()){ |
253 |
|
|
countCids=R.getInt(1); |
254 |
|
|
} |
255 |
|
|
if (countCids!=1){ |
256 |
|
|
System.out.println("Error! the number of client_id for idx "+idxColumn+"= "+idx+" is bigger than 1. Check what's wrong! Exiting now."); |
257 |
|
|
System.exit(2); |
258 |
|
|
} |
259 |
|
|
else { |
260 |
|
|
query="SELECT client_id FROM "+keyTable+" WHERE "+idxColumn+"="+idx+";"; |
261 |
|
|
R=S.executeQuery(query); |
262 |
|
|
if (R.next()){ |
263 |
|
|
hostId=R.getInt(1); |
264 |
|
|
} |
265 |
|
|
} |
266 |
|
|
S.close(); |
267 |
|
|
R.close(); |
268 |
|
|
} |
269 |
|
|
catch(SQLException e) { |
270 |
|
|
System.out.println("Couldn't get the host id for idx "+idxColumn+"="+idx+", exiting"); |
271 |
|
|
System.out.println("SQLException: " + e.getMessage()); |
272 |
|
|
System.out.println("SQLState: " + e.getSQLState()); |
273 |
|
|
System.exit(3); |
274 |
|
|
} |
275 |
|
|
return hostId; |
276 |
|
|
} |
277 |
|
|
|
278 |
|
|
public void insertIdxInMaster(String key, int clientId) { |
279 |
|
|
Statement S; |
280 |
|
|
String query; |
281 |
|
|
this.setKey(key); |
282 |
|
|
this.setMasterTable(); |
283 |
|
|
this.setIdxColumn(); |
284 |
|
|
try { |
285 |
|
|
S=this.mCon.createStatement(); |
286 |
|
|
query="INSERT INTO "+this.keyTable+" (client_id) VALUES ("+clientId+");"; |
287 |
|
|
S.executeUpdate(query); |
288 |
|
|
S.close(); |
289 |
|
|
} |
290 |
|
|
catch (SQLException E) { |
291 |
|
|
System.out.println("SQLException: " + E.getMessage()); |
292 |
|
|
System.out.println("SQLState: " + E.getSQLState()); |
293 |
|
|
System.out.println("Couldn't insert new "+this.idxColumn+" in master table "+this.getMasterTable()+". The client_id for it was "+clientId+". Exiting."); |
294 |
|
|
System.exit(2); |
295 |
|
|
} |
296 |
|
|
} |
297 |
|
|
|
298 |
|
|
public void insertIdxInMaster(String keySrc,String keyDest,int idxSrc) { |
299 |
|
|
this.setKey(keySrc); |
300 |
|
|
this.setMasterTable(); |
301 |
|
|
this.setIdxColumn(); |
302 |
|
|
int clientId=0; |
303 |
|
|
clientId=this.getHostId4Idx(keySrc,idxSrc); |
304 |
|
|
insertIdxInMaster(keyDest,clientId); |
305 |
|
|
} |
306 |
|
|
|
307 |
|
|
public int getLastInsertId(String key) { |
308 |
|
|
int lastIdx=0; |
309 |
|
|
this.setKey(key); |
310 |
|
|
this.setMasterTable(); |
311 |
|
|
this.setIdxColumn(); |
312 |
|
|
Statement S; |
313 |
|
|
ResultSet R; |
314 |
|
|
String query = ""; |
315 |
|
|
try { |
316 |
|
|
S = this.mCon.createStatement(); |
317 |
|
|
query = "SELECT LAST_INSERT_ID() FROM "+this.keyTable+" LIMIT 1;"; |
318 |
|
|
R = S.executeQuery(query); |
319 |
|
|
if (R.next()) { |
320 |
|
|
lastIdx=R.getInt(1); |
321 |
|
|
} |
322 |
|
|
R.close(); |
323 |
|
|
S.close(); |
324 |
|
|
} |
325 |
|
|
catch (SQLException E) { |
326 |
|
|
System.out.println("Couldn't get the last insert id for key type "+this.idxColumn+" from table "+this.keyTable+". Exiting"); |
327 |
|
|
System.out.println("SQLException: " + E.getMessage()); |
328 |
|
|
System.out.println("SQLState: " + E.getSQLState()); |
329 |
|
|
System.exit(3); |
330 |
|
|
} // end try/catch connection |
331 |
|
|
return lastIdx; |
332 |
|
|
} // end getGraphId |
333 |
|
|
} |