trunk/tools/ClusterConnection.java

package tools;
import java.sql.*;

/**
 * ClusterConnection class to wrap the master/node mysql servers so that is transparent to other programs
 * @author Jose Duarte
 */
//TODO Maybe we should always pass the key for CreateStatement and the constructor and drop the 2 methods without key.
//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
//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

public class ClusterConnection {

        private final String url= "jdbc:mysql://";
        private final String masterDb="key_master"; 
        private final String masterHost="white";
        private Connection nCon;
        private Connection mCon;
        public String keyTable;
        public String key;
        public String idxColumn;
        public String host;
        public String db;
        private String user;
        private String password;
        
        //create a ClusterConnection passing a key. Then we need to call createNConStatement only passing the idx as argument.
        public ClusterConnection (String db,String key, String user,String password) {
                loadMySQLDriver();
                setDb(db);
                setKey(key);
                setIdxColumn();
                setMasterTable(db);
                setUser(user);
                setPassword(password);
                try {
                        // For nCon we create a connection to the master too. 
                        // This is just a place holder because the actual node connection is not created until we create the statement
                        // 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
                        this.nCon = DriverManager.getConnection(url+masterHost+"/"+masterDb,user,password); 
                        this.mCon = DriverManager.getConnection(url+masterHost+"/"+masterDb,user,password);
                }
                catch(SQLException e){
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState:     " + e.getSQLState());
            System.out.println("VendorError:  " + e.getErrorCode());
                        System.out.println("Couldn't get connection to master host "+masterHost+", db="+masterDb+", exiting.");
                        System.exit(2);                 
                }
        }

        // create a cluster connection with an empty key field. In this case we need to call createNConStatement passing the key as argument 
        public ClusterConnection (String db, String user,String password) { 
                this.loadMySQLDriver();
                setDb(db);
                setUser(user);
                setPassword(password);
                try {
                        // For nCon we create a connection to the master too. 
                        // This is just a place holder because the actual node connection is not created until we create the statement
                        // 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
                        this.nCon = DriverManager.getConnection(url+masterHost+"/"+masterDb,user,password);
                        this.mCon = DriverManager.getConnection(url+masterHost+"/"+masterDb,user,password);
                }
                catch(SQLException e){
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState:     " + e.getSQLState());
            System.out.println("VendorError:  " + e.getErrorCode());
                        System.out.println("Couldn't get connection to master host "+masterHost+", db="+masterDb+", exiting.");
                        System.exit(2);                 
                }
        }

        
        public void loadMySQLDriver() {
                try {
                        Class.forName("com.mysql.jdbc.Driver");
                }
                catch(Exception e) {
                        System.out.println(e.getMessage());
                        System.out.println("An exception occurred while loading the mysql jdbc driver, exiting.");
                        System.exit(1);
                }
        }
        public void close() {
                try {
                        this.nCon.close();
                        this.mCon.close();
                }
                catch(SQLException e) {
                        System.out.println("Couldn't close database connections for master: "+masterHost+" and node: "+this.host+", exiting.");
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState:     " + e.getSQLState());
                        System.exit(3);                                         
                }
        }
        
        public String getHost4Idx (int idx) {
                String host="";
                Statement S;
                ResultSet R;
                try {
                        S=mCon.createStatement();
                        String query="SELECT client_name FROM "+keyTable+" AS m INNER JOIN clients_names AS c "+
                                                "ON (m.client_id=c.client_id) WHERE "+idxColumn+"="+idx+";";
                        R=S.executeQuery(query);
                        if (R.next()){
                                host=R.getString(1);
                        }
                        S.close();
                        R.close();
                }
                catch(SQLException e) {
                        System.out.println("Couldn't get the host name for idx "+idx+", exiting");
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState:     " + e.getSQLState());
                        System.exit(3);                                                                 
                }
                return host;
        }
        
        public void setHostFromIdx(int idx){
                setHost(getHost4Idx(idx));
        }
        
        public void setHost(String host) {
                this.host=host;
                try {
                        this.nCon=DriverManager.getConnection(url+host+"/"+db,user,password);
                }
                catch (SQLException e){
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState:     " + e.getSQLState());
                        System.out.println("Couldn't get connection to node "+host+", exiting.");
                        System.exit(2);                                         
                }
        }
        
        public Statement createStatement(int idx) { // to use when the field "key" is already set
                setMasterTable();
                setIdxColumn();
                Statement S=null;
                this.setHostFromIdx(idx);
                try {
                        S=this.nCon.createStatement();
                }
                catch (SQLException e){
                        System.out.println("SQLException: " + e.getMessage());
                        System.out.println("SQLState:     " + e.getSQLState());
                        System.out.println("Couldn't create statement for the node connection, idx= "+idx+", exiting.");
                        System.exit(2);                                                                 
                }
                return S;
        }
        
        public Statement createStatement(String key,int idx) {
                setKey(key);
                setMasterTable();
                setIdxColumn();
                return createStatement(idx);
        }
        
        public void setMasterTable(String db) { // to set masterTable field in constructor (i.e. first time)
                this.keyTable=db+"_"+this.key+"_list_master";
        }
        
        public void setMasterTable() { // to set masterTable field when db is already set 
                this.keyTable=this.db+"_"+this.key+"_list_master";
        }
        
        public String getMasterTable() {
                return this.keyTable;
        }
        
        public void setIdxColumn() {
                this.idxColumn=this.key+"_id";
        }

        public void setUser(String user) {
                this.user=user;
        }

        public void setPassword(String password) {
                this.password=password;
        }
        
        public void setDb(String db){
                this.db=db;
        }
        
        public void setKey(String key){
                this.key=key;
        }

        public void switchKey(String key){
                this.key=key;
                setMasterTable();
                setIdxColumn();
        }

        public Statement createMasterStatement() { 
                Statement S=null;
                try {
                        S=this.mCon.createStatement();
                }
                catch (SQLException e){
                        System.out.println("SQLException: " + e.getMessage());
                        System.out.println("SQLState:     " + e.getSQLState());
                        System.out.println("Couldn't create statement for the master connection, exiting.");
                        System.exit(2);                                                                 
                }
                return S;
        }

        public ResultSet getAllIdxFromMaster(String key) { 
                this.setKey(key);
                this.setIdxColumn();
                this.setMasterTable();
                String query;
                Statement S;
                ResultSet R=null;
                try {
                        query="SELECT "+idxColumn+" FROM "+keyTable+";";
                        S=this.mCon.createStatement();
                        R=S.executeQuery(query);
                        //S.close(); // apparently it doesn't work if we close the Statement!! Don't know why!
                }
                catch (SQLException e){
                        System.out.println("SQLException: " + e.getMessage());
                        System.out.println("SQLState:     " + e.getSQLState());
                        System.out.println("Couldn't get all indices from columnn "+idxColumn+" in table "+keyTable+" from "+masterDb+" database in "+masterHost+", exiting.");
                        System.exit(2);                                                                 
                }
                return R;
        }

        //       to get client_id for a certain idx and key
        //TODO will need to change the query. In general this method would return more than 1 client_id if the idx is not unique
        public int getHostId4Idx (String key,int idx) { 
                int hostId=0;
                this.setKey(key);
                this.setIdxColumn();
                this.setMasterTable();
                Statement S;
                ResultSet R;
                String query;
                int countCids=0;
                try {
                        S=mCon.createStatement();
                        query="SELECT count(client_id) FROM "+keyTable+" WHERE "+idxColumn+"="+idx+";";
                        R=S.executeQuery(query);
                        if (R.next()){
                                countCids=R.getInt(1);
                        }
                        if (countCids!=1){
                                System.out.println("Error! the number of client_id for idx "+idxColumn+"= "+idx+" is bigger than 1. Check what's wrong! Exiting now.");
                                System.exit(2);
                        }
                        else {
                                query="SELECT client_id FROM "+keyTable+" WHERE "+idxColumn+"="+idx+";";
                                R=S.executeQuery(query);
                                if (R.next()){
                                        hostId=R.getInt(1);
                                }
                        }
                        S.close();
                        R.close();
                }
                catch(SQLException e) {
                        System.out.println("Couldn't get the host id for idx "+idxColumn+"="+idx+", exiting");
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState:     " + e.getSQLState());
                        System.exit(3);                                                                 
                }
                return hostId;
        }
        
        public void insertIdxInMaster(String key, int clientId) {
                Statement S;
                String query;
                this.setKey(key);
                this.setMasterTable();
                this.setIdxColumn();
                try {
                        S=this.mCon.createStatement();
                        query="INSERT INTO "+this.keyTable+" (client_id) VALUES ("+clientId+");";
                        S.executeUpdate(query);
                        S.close();
                } 
                catch (SQLException E) {
                        System.out.println("SQLException: " + E.getMessage());
                        System.out.println("SQLState:     " + E.getSQLState());
                        System.out.println("Couldn't insert new "+this.idxColumn+" in master table "+this.getMasterTable()+". The client_id for it was "+clientId+". Exiting.");
                        System.exit(2);
                } 
        }

        public void insertIdxInMaster(String keySrc,String keyDest,int idxSrc) {
                this.setKey(keySrc);
                this.setMasterTable();
                this.setIdxColumn();
                int clientId=0;
                clientId=this.getHostId4Idx(keySrc,idxSrc);
                insertIdxInMaster(keyDest,clientId);
        }

        public int getLastInsertId(String key) {
                int lastIdx=0;
                this.setKey(key);
                this.setMasterTable();
                this.setIdxColumn();
                Statement S;
                ResultSet R;
                String query = "";
                try {
                        S = this.mCon.createStatement();
                        query = "SELECT LAST_INSERT_ID() FROM "+this.keyTable+" LIMIT 1;";
                        R = S.executeQuery(query);
                        if (R.next()) {
                                lastIdx=R.getInt(1);
                        }
                        R.close();
                        S.close();
                } 
                catch (SQLException E) {
                        System.out.println("Couldn't get the last insert id for key type "+this.idxColumn+" from table "+this.keyTable+". Exiting");
                        System.out.println("SQLException: " + E.getMessage());
                        System.out.println("SQLState:     " + E.getSQLState());
                        System.exit(3);
                } // end try/catch connection 
                return lastIdx;
        } // end getGraphId
}
Revision:	16
Committed:	Mon Mar 6 08:32:55 2006 UTC (18 years, 6 months ago) by duarte
File size:	10595 byte(s)
Log Message:	Added package tools.
Line	User	Rev	File contents
1	duarte	16	package tools;
2	duarte	15	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			}