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) {
                new ClusterConnection(db,user,password);
                this.key=key; //can't use the setKey method here before we've got the db field initialized
                setIdxColumn();
                setMasterTable(db);
        }

        // 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) { 
                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);
                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;
                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);
                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);
                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);
                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);
                int clientId=0;
                clientId=this.getHostId4Idx(keySrc,idxSrc);
                insertIdxInMaster(keyDest,clientId);
        }

        public int getLastInsertId(String key) {
                int lastIdx=0;
                this.setKey(key);
                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:	17
Committed:	Mon Mar 6 09:20:16 2006 UTC (18 years, 6 months ago) by duarte
File size:	9453 byte(s)
Log Message:	Grouped all the setIdxColumn and setMasterTable into setKey. Also rationalised a bit the constructors by calling one from the other.
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	duarte	17	new ClusterConnection(db,user,password);
30			this.key=key; //can't use the setKey method here before we've got the db field initialized
31	duarte	15	setIdxColumn();
32			setMasterTable(db);
33			}
34
35			// create a cluster connection with an empty key field. In this case we need to call createNConStatement passing the key as argument
36			public ClusterConnection (String db, String user,String password) {
37	duarte	17	loadMySQLDriver();
38	duarte	15	setDb(db);
39			setUser(user);
40			setPassword(password);
41			try {
42			// For nCon we create a connection to the master too.
43			// This is just a place holder because the actual node connection is not created until we create the statement
44			// 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
45			this.nCon = DriverManager.getConnection(url+masterHost+"/"+masterDb,user,password);
46			this.mCon = DriverManager.getConnection(url+masterHost+"/"+masterDb,user,password);
47			}
48			catch(SQLException e){
49			System.out.println("SQLException: " + e.getMessage());
50			System.out.println("SQLState: " + e.getSQLState());
51			System.out.println("VendorError: " + e.getErrorCode());
52			System.out.println("Couldn't get connection to master host "+masterHost+", db="+masterDb+", exiting.");
53			System.exit(2);
54			}
55			}
56
57
58			public void loadMySQLDriver() {
59			try {
60			Class.forName("com.mysql.jdbc.Driver");
61			}
62			catch(Exception e) {
63			System.out.println(e.getMessage());
64			System.out.println("An exception occurred while loading the mysql jdbc driver, exiting.");
65			System.exit(1);
66			}
67			}
68			public void close() {
69			try {
70			this.nCon.close();
71			this.mCon.close();
72			}
73			catch(SQLException e) {
74			System.out.println("Couldn't close database connections for master: "+masterHost+" and node: "+this.host+", exiting.");
75			System.out.println("SQLException: " + e.getMessage());
76			System.out.println("SQLState: " + e.getSQLState());
77			System.exit(3);
78			}
79			}
80
81			public String getHost4Idx (int idx) {
82			String host="";
83			Statement S;
84			ResultSet R;
85			try {
86			S=mCon.createStatement();
87			String query="SELECT client_name FROM "+keyTable+" AS m INNER JOIN clients_names AS c "+
88			"ON (m.client_id=c.client_id) WHERE "+idxColumn+"="+idx+";";
89			R=S.executeQuery(query);
90			if (R.next()){
91			host=R.getString(1);
92			}
93			S.close();
94			R.close();
95			}
96			catch(SQLException e) {
97			System.out.println("Couldn't get the host name for idx "+idx+", exiting");
98			System.out.println("SQLException: " + e.getMessage());
99			System.out.println("SQLState: " + e.getSQLState());
100			System.exit(3);
101			}
102			return host;
103			}
104
105			public void setHostFromIdx(int idx){
106			setHost(getHost4Idx(idx));
107			}
108
109			public void setHost(String host) {
110			this.host=host;
111			try {
112			this.nCon=DriverManager.getConnection(url+host+"/"+db,user,password);
113			}
114			catch (SQLException e){
115			System.out.println("SQLException: " + e.getMessage());
116			System.out.println("SQLState: " + e.getSQLState());
117			System.out.println("Couldn't get connection to node "+host+", exiting.");
118			System.exit(2);
119			}
120			}
121
122			public Statement createStatement(int idx) { // to use when the field "key" is already set
123			setMasterTable();
124			setIdxColumn();
125			Statement S=null;
126			this.setHostFromIdx(idx);
127			try {
128			S=this.nCon.createStatement();
129			}
130			catch (SQLException e){
131			System.out.println("SQLException: " + e.getMessage());
132			System.out.println("SQLState: " + e.getSQLState());
133			System.out.println("Couldn't create statement for the node connection, idx= "+idx+", exiting.");
134			System.exit(2);
135			}
136			return S;
137			}
138
139			public Statement createStatement(String key,int idx) {
140			setKey(key);
141			return createStatement(idx);
142			}
143
144			public void setMasterTable(String db) { // to set masterTable field in constructor (i.e. first time)
145			this.keyTable=db+"_"+this.key+"_list_master";
146			}
147
148			public void setMasterTable() { // to set masterTable field when db is already set
149			this.keyTable=this.db+"_"+this.key+"_list_master";
150			}
151
152			public String getMasterTable() {
153			return this.keyTable;
154			}
155
156			public void setIdxColumn() {
157			this.idxColumn=this.key+"_id";
158			}
159
160			public void setUser(String user) {
161			this.user=user;
162			}
163
164			public void setPassword(String password) {
165			this.password=password;
166			}
167
168			public void setDb(String db){
169			this.db=db;
170			}
171
172			public void setKey(String key){
173			this.key=key;
174			setMasterTable();
175			setIdxColumn();
176			}
177
178			public Statement createMasterStatement() {
179			Statement S=null;
180			try {
181			S=this.mCon.createStatement();
182			}
183			catch (SQLException e){
184			System.out.println("SQLException: " + e.getMessage());
185			System.out.println("SQLState: " + e.getSQLState());
186			System.out.println("Couldn't create statement for the master connection, exiting.");
187			System.exit(2);
188			}
189			return S;
190			}
191
192			public ResultSet getAllIdxFromMaster(String key) {
193			this.setKey(key);
194			String query;
195			Statement S;
196			ResultSet R=null;
197			try {
198			query="SELECT "+idxColumn+" FROM "+keyTable+";";
199			S=this.mCon.createStatement();
200			R=S.executeQuery(query);
201			//S.close(); // apparently it doesn't work if we close the Statement!! Don't know why!
202			}
203			catch (SQLException e){
204			System.out.println("SQLException: " + e.getMessage());
205			System.out.println("SQLState: " + e.getSQLState());
206			System.out.println("Couldn't get all indices from columnn "+idxColumn+" in table "+keyTable+" from "+masterDb+" database in "+masterHost+", exiting.");
207			System.exit(2);
208			}
209			return R;
210			}
211
212			// to get client_id for a certain idx and key
213			//TODO will need to change the query. In general this method would return more than 1 client_id if the idx is not unique
214			public int getHostId4Idx (String key,int idx) {
215			int hostId=0;
216			this.setKey(key);
217			Statement S;
218			ResultSet R;
219			String query;
220			int countCids=0;
221			try {
222			S=mCon.createStatement();
223			query="SELECT count(client_id) FROM "+keyTable+" WHERE "+idxColumn+"="+idx+";";
224			R=S.executeQuery(query);
225			if (R.next()){
226			countCids=R.getInt(1);
227			}
228			if (countCids!=1){
229			System.out.println("Error! the number of client_id for idx "+idxColumn+"= "+idx+" is bigger than 1. Check what's wrong! Exiting now.");
230			System.exit(2);
231			}
232			else {
233			query="SELECT client_id FROM "+keyTable+" WHERE "+idxColumn+"="+idx+";";
234			R=S.executeQuery(query);
235			if (R.next()){
236			hostId=R.getInt(1);
237			}
238			}
239			S.close();
240			R.close();
241			}
242			catch(SQLException e) {
243			System.out.println("Couldn't get the host id for idx "+idxColumn+"="+idx+", exiting");
244			System.out.println("SQLException: " + e.getMessage());
245			System.out.println("SQLState: " + e.getSQLState());
246			System.exit(3);
247			}
248			return hostId;
249			}
250
251			public void insertIdxInMaster(String key, int clientId) {
252			Statement S;
253			String query;
254			this.setKey(key);
255			try {
256			S=this.mCon.createStatement();
257			query="INSERT INTO "+this.keyTable+" (client_id) VALUES ("+clientId+");";
258			S.executeUpdate(query);
259			S.close();
260			}
261			catch (SQLException E) {
262			System.out.println("SQLException: " + E.getMessage());
263			System.out.println("SQLState: " + E.getSQLState());
264			System.out.println("Couldn't insert new "+this.idxColumn+" in master table "+this.getMasterTable()+". The client_id for it was "+clientId+". Exiting.");
265			System.exit(2);
266			}
267			}
268
269			public void insertIdxInMaster(String keySrc,String keyDest,int idxSrc) {
270			this.setKey(keySrc);
271			int clientId=0;
272			clientId=this.getHostId4Idx(keySrc,idxSrc);
273			insertIdxInMaster(keyDest,clientId);
274			}
275
276			public int getLastInsertId(String key) {
277			int lastIdx=0;
278			this.setKey(key);
279			Statement S;
280			ResultSet R;
281			String query = "";
282			try {
283			S = this.mCon.createStatement();
284			query = "SELECT LAST_INSERT_ID() FROM "+this.keyTable+" LIMIT 1;";
285			R = S.executeQuery(query);
286			if (R.next()) {
287			lastIdx=R.getInt(1);
288			}
289			R.close();
290			S.close();
291			}
292			catch (SQLException E) {
293			System.out.println("Couldn't get the last insert id for key type "+this.idxColumn+" from table "+this.keyTable+". Exiting");
294			System.out.println("SQLException: " + E.getMessage());
295			System.out.println("SQLState: " + E.getSQLState());
296			System.exit(3);
297			} // end try/catch connection
298			return lastIdx;
299			} // end getGraphId
300			}