aglappe-0.6/tools/MySQLConnection.java

package tools;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.StringTokenizer;
import java.io.IOException;

public class MySQLConnection {

        /*--------------------- constants -----------------------*/
        
    // -- constants for database connection --
    static final String    HOST =                       "white";        
    static final String    USER =                   "";
    static final String    PASSWORD =           "nieve";
        
        /*------------------- member variables --------------------*/
        
        public Connection conn; 
        private String host;
        private String user;
        private String password=PASSWORD;
        private String port;
        private String dbname;
        
        /*-------------------- constructors -----------------------*/

        /**
         * Connect to database using the given server, user and password
         */
        public MySQLConnection(String dbServer, String dbUserName, String dbPassword) {
                loadMySQLDriver();
                host=dbServer;
                user=dbUserName;
                password=dbPassword;
                port="";
                dbname="";              
                String connStr="jdbc:mysql://"+host+port+"/"+dbname;
                try {
                        conn = DriverManager.getConnection(connStr, user, password);
                } catch (SQLException e) {
                        System.err.println("SQLException: " + e.getMessage());
                        System.err.println("SQLState:     " + e.getSQLState());
                        System.err.println("VendorError:  " + e.getErrorCode());
                        e.printStackTrace();
                } // end try/catch connection                            
        }

        /**
         * Connect to database using the given server, user, password and dbname
         */
        public MySQLConnection(String dbServer, String dbUserName, String dbPassword, String dbName) {
                loadMySQLDriver();
                host=dbServer;
                user=dbUserName;
                password=dbPassword;
                port="";
                dbname=dbName;          
                String connStr="jdbc:mysql://"+host+port+"/"+dbname;
                try {
                        conn = DriverManager.getConnection(connStr, user, password);
                } catch (SQLException e) {
                        System.err.println("SQLException: " + e.getMessage());
                        System.err.println("SQLState:     " + e.getSQLState());
                        System.err.println("VendorError:  " + e.getErrorCode());
                        e.printStackTrace();
                } // end try/catch connection                            
        }

        /**
         * Connect to database using the given server, user, password, dbname and port
         */
        public MySQLConnection(String dbServer, String dbUserName, String dbPassword, String dbName, int portNum) {
                loadMySQLDriver();
                host=dbServer;
                user=dbUserName;
                password=dbPassword;
                port=":"+portNum;
                dbname=dbName;          
                String connStr="jdbc:mysql://"+host+port+"/"+dbname;
                try {
                        conn = DriverManager.getConnection(connStr, user, password);
                } catch (SQLException e) {
                        System.err.println("SQLException: " + e.getMessage());
                        System.err.println("SQLState:     " + e.getSQLState());
                        System.err.println("VendorError:  " + e.getErrorCode());
                        e.printStackTrace();
                } // end try/catch connection                            
        }
                
        /**
         * Connect to database giving a connection file 
         */
        public MySQLConnection(String connFile) {               
                loadMySQLDriver();
                readConnectionFile(connFile);
                String connStr="jdbc:mysql://"+host+port+"/"+dbname;
                try {
                        conn = DriverManager.getConnection(connStr, user, password);
                } catch (SQLException e) {
                        System.err.println("SQLException: " + e.getMessage());
                        System.err.println("SQLState:     " + e.getSQLState());
                        System.err.println("VendorError:  " + e.getErrorCode());
                        e.printStackTrace();
                } // end try/catch connection                            
        }
        
        
        /*---------------------- methods -------------------------*/
        
        public void loadMySQLDriver() {
                try {
                        Class.forName("com.mysql.jdbc.Driver").newInstance();
                }
                catch(Exception e) {
                        e.printStackTrace();
                        System.err.println("An exception occurred while loading the mysql jdbc driver, exiting.");
                        System.exit(1);
                }
        }

        public Statement createStatement() throws SQLException {
                return this.conn.createStatement();
        }
        
        public void executeSql(String query) {
                Statement stmt;
                try {
                    stmt = conn.createStatement();      
                    stmt.execute(query);
                        stmt.close();               
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                    System.err.println("SQLState:     " + e.getSQLState());
                    System.err.println("VendorError:  " + e.getErrorCode());
                    e.printStackTrace();
                } // end catch
        }
        
        /** 
         * @param query
         * @return the first column of the first row of the result of the given query as a string
         * or null if no results were returned
         */     
        public String getStringFromDb(String query) {
                Statement    stmt;
                ResultSet    rs;
                String       result = null;
                
                try { 
                        
                    stmt = conn.createStatement();
                    rs = stmt.executeQuery(query);
                    if(rs.next()) {
                        result = rs.getString(1);
                    }
                    rs.close();
                    stmt.close(); 
                    
                } // end try
                catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                    System.err.println("SQLState:     " + e.getSQLState());
                    System.err.println("VendorError:  " + e.getErrorCode());
                    e.printStackTrace();
                } // end catch                          
                
                return result;
        }
        
        /** 
         * @param query
         * @return the first column of the first row of the result of the given query as an integer
         * or -1 if no results were returned
         */
        public int getIntFromDb(String query) {
                Statement    stmt;
                ResultSet    rs;
                int          result = -1;

                try { 
                        
                    stmt = conn.createStatement();
                    rs = stmt.executeQuery(query);
                    if(rs.next()) {
                        result = rs.getInt(1);
                    }
                    rs.close();
                    stmt.close(); 
                    
                } // end try
                catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                    System.err.println("SQLState:     " + e.getSQLState());
                    System.err.println("VendorError:  " + e.getErrorCode());
                    e.printStackTrace();
                } // end catch                  
                
                return result;
        }
        
        public void close() {
                
                try {
                        conn.close();
            } catch (SQLException e) {
                        System.err.println("SQLException: " + e.getMessage());
                        System.err.println("SQLState:     " + e.getSQLState());
                        System.err.println("VendorError:  " + e.getErrorCode());
                        e.printStackTrace();
            } // end try/catch connection 
        }

        /**
         * 
         * @param connFile
         */
        public void readConnectionFile(String connFile) {
                // reads the values of the connFile into the static variables; 
                String homedir = System.getProperty("user.home"); 
                if (connFile.length()==0) { // no file was specified
                        connFile=homedir+"/.my.cnf"; // assume default configuration file 
                }               
                // else the location of the connection file was given           
                // Open the configuration file          
                BufferedReader fileIn = null;
                StringTokenizer str;
                String item, oneLine;
                // to control if the minimum necessary 3 parameters are given in file
                int cfgParsPresent=0; 
                // setting default blank values for port and dbname, they are set to blank unless fields specified in file
                port="";
                dbname="";
                // list the entries in the file and decompose them 
                try {
                        fileIn = new BufferedReader(new FileReader(new File(connFile))); // open BufferedReader to file connFile 
                        while ((oneLine = fileIn.readLine()) != null ) {
                                // Construct a stringTokenizer for the line that we read with : delimited
                                str = new StringTokenizer(oneLine, "="); // true sets returnDelimiters flag 
                                while ( str.hasMoreTokens()) {
                                        item = str.nextToken();
                                        if( item.equals("host")) { // mandatory parameter
                                                host=str.nextToken();
                                                cfgParsPresent++;
                                                break; 
                                        } // end if host 
                                        if( item.equals("port")) { // optional parameter
                                                port=":"+str.nextToken();
                                                break; 
                                        } // end if port
                                        if( item.equals("user")) { // mandatory parameter
                                                user=str.nextToken();
                                                cfgParsPresent++;
                                                break; 
                                        } // end if password 
                                        if( item.equals("password")) { // mandatory parameter
                                                password=str.nextToken();
                                                cfgParsPresent++;
                                                break; 
                                        } // end if password
                                        if( item.equals("database")) { // optional parameter
                                                dbname=str.nextToken();
                                                break; 
                                        } // end if password                                    
                                } // next token in this line 
                        } // next line in the file
                        if (cfgParsPresent<3){
                                System.err.println("Not all mandatory parameters are given in connection file "+connFile+". Can't connect to mysql server, exiting.");
                                System.exit(1);
                        }                       
                } 
                catch (IOException e) {
                        System.err.println("Couldn't open file "+connFile);                     
                        e.printStackTrace();
                        System.exit(1);
                }  
                
                try { // closing the file
                        if (fileIn != null) fileIn.close();                     
                } catch (IOException e) {
                        System.err.println("Couldn't close file "+connFile);
                        e.printStackTrace(); 
                }
                
        }

        
}
Revision:	55
Committed:	Tue Apr 4 15:27:49 2006 UTC (18 years, 6 months ago) by duarte
Original Path:	*trunk/tools/MySQLConnection.java*
File size:	8697 byte(s)
Log Message:	Initial commit. Class to create a connection to mysql db with basic functionality. Cann take parameters for connection as argument in constructor or from connection file (passing the filename to the constructor) Using code from both DBConnection (henning) and mySQLConnect (ioannis)
Line	File contents
1	package tools;
2
3	import java.io.BufferedReader;
4	import java.io.File;
5	import java.io.FileReader;
6	import java.sql.Connection;
7	import java.sql.DriverManager;
8	import java.sql.ResultSet;
9	import java.sql.SQLException;
10	import java.sql.Statement;
11	import java.util.StringTokenizer;
12	import java.io.IOException;
13
14	public class MySQLConnection {
15
16	/--------------------- constants -----------------------/
17
18	// -- constants for database connection --
19	static final String HOST = "white";
20	static final String USER = "";
21	static final String PASSWORD = "nieve";
22
23	/------------------- member variables --------------------/
24
25	public Connection conn;
26	private String host;
27	private String user;
28	private String password=PASSWORD;
29	private String port;
30	private String dbname;
31
32	/-------------------- constructors -----------------------/
33
34	/**
35	* Connect to database using the given server, user and password
36	*/
37	public MySQLConnection(String dbServer, String dbUserName, String dbPassword) {
38	loadMySQLDriver();
39	host=dbServer;
40	user=dbUserName;
41	password=dbPassword;
42	port="";
43	dbname="";
44	String connStr="jdbc:mysql://"+host+port+"/"+dbname;
45	try {
46	conn = DriverManager.getConnection(connStr, user, password);
47	} catch (SQLException e) {
48	System.err.println("SQLException: " + e.getMessage());
49	System.err.println("SQLState: " + e.getSQLState());
50	System.err.println("VendorError: " + e.getErrorCode());
51	e.printStackTrace();
52	} // end try/catch connection
53	}
54
55	/**
56	* Connect to database using the given server, user, password and dbname
57	*/
58	public MySQLConnection(String dbServer, String dbUserName, String dbPassword, String dbName) {
59	loadMySQLDriver();
60	host=dbServer;
61	user=dbUserName;
62	password=dbPassword;
63	port="";
64	dbname=dbName;
65	String connStr="jdbc:mysql://"+host+port+"/"+dbname;
66	try {
67	conn = DriverManager.getConnection(connStr, user, password);
68	} catch (SQLException e) {
69	System.err.println("SQLException: " + e.getMessage());
70	System.err.println("SQLState: " + e.getSQLState());
71	System.err.println("VendorError: " + e.getErrorCode());
72	e.printStackTrace();
73	} // end try/catch connection
74	}
75
76	/**
77	* Connect to database using the given server, user, password, dbname and port
78	*/
79	public MySQLConnection(String dbServer, String dbUserName, String dbPassword, String dbName, int portNum) {
80	loadMySQLDriver();
81	host=dbServer;
82	user=dbUserName;
83	password=dbPassword;
84	port=":"+portNum;
85	dbname=dbName;
86	String connStr="jdbc:mysql://"+host+port+"/"+dbname;
87	try {
88	conn = DriverManager.getConnection(connStr, user, password);
89	} catch (SQLException e) {
90	System.err.println("SQLException: " + e.getMessage());
91	System.err.println("SQLState: " + e.getSQLState());
92	System.err.println("VendorError: " + e.getErrorCode());
93	e.printStackTrace();
94	} // end try/catch connection
95	}
96
97	/**
98	* Connect to database giving a connection file
99	*/
100	public MySQLConnection(String connFile) {
101	loadMySQLDriver();
102	readConnectionFile(connFile);
103	String connStr="jdbc:mysql://"+host+port+"/"+dbname;
104	try {
105	conn = DriverManager.getConnection(connStr, user, password);
106	} catch (SQLException e) {
107	System.err.println("SQLException: " + e.getMessage());
108	System.err.println("SQLState: " + e.getSQLState());
109	System.err.println("VendorError: " + e.getErrorCode());
110	e.printStackTrace();
111	} // end try/catch connection
112	}
113
114
115	/---------------------- methods -------------------------/
116
117	public void loadMySQLDriver() {
118	try {
119	Class.forName("com.mysql.jdbc.Driver").newInstance();
120	}
121	catch(Exception e) {
122	e.printStackTrace();
123	System.err.println("An exception occurred while loading the mysql jdbc driver, exiting.");
124	System.exit(1);
125	}
126	}
127
128	public Statement createStatement() throws SQLException {
129	return this.conn.createStatement();
130	}
131
132	public void executeSql(String query) {
133	Statement stmt;
134	try {
135	stmt = conn.createStatement();
136	stmt.execute(query);
137	stmt.close();
138	} catch (SQLException e) {
139	System.err.println("SQLException: " + e.getMessage());
140	System.err.println("SQLState: " + e.getSQLState());
141	System.err.println("VendorError: " + e.getErrorCode());
142	e.printStackTrace();
143	} // end catch
144	}
145
146	/**
147	* @param query
148	* @return the first column of the first row of the result of the given query as a string
149	* or null if no results were returned
150	*/
151	public String getStringFromDb(String query) {
152	Statement stmt;
153	ResultSet rs;
154	String result = null;
155
156	try {
157
158	stmt = conn.createStatement();
159	rs = stmt.executeQuery(query);
160	if(rs.next()) {
161	result = rs.getString(1);
162	}
163	rs.close();
164	stmt.close();
165
166	} // end try
167	catch (SQLException e) {
168	System.err.println("SQLException: " + e.getMessage());
169	System.err.println("SQLState: " + e.getSQLState());
170	System.err.println("VendorError: " + e.getErrorCode());
171	e.printStackTrace();
172	} // end catch
173
174	return result;
175	}
176
177	/**
178	* @param query
179	* @return the first column of the first row of the result of the given query as an integer
180	* or -1 if no results were returned
181	*/
182	public int getIntFromDb(String query) {
183	Statement stmt;
184	ResultSet rs;
185	int result = -1;
186
187	try {
188
189	stmt = conn.createStatement();
190	rs = stmt.executeQuery(query);
191	if(rs.next()) {
192	result = rs.getInt(1);
193	}
194	rs.close();
195	stmt.close();
196
197	} // end try
198	catch (SQLException e) {
199	System.err.println("SQLException: " + e.getMessage());
200	System.err.println("SQLState: " + e.getSQLState());
201	System.err.println("VendorError: " + e.getErrorCode());
202	e.printStackTrace();
203	} // end catch
204
205	return result;
206	}
207
208	public void close() {
209
210	try {
211	conn.close();
212	} catch (SQLException e) {
213	System.err.println("SQLException: " + e.getMessage());
214	System.err.println("SQLState: " + e.getSQLState());
215	System.err.println("VendorError: " + e.getErrorCode());
216	e.printStackTrace();
217	} // end try/catch connection
218	}
219
220	/**
221	*
222	* @param connFile
223	*/
224	public void readConnectionFile(String connFile) {
225	// reads the values of the connFile into the static variables;
226	String homedir = System.getProperty("user.home");
227	if (connFile.length()==0) { // no file was specified
228	connFile=homedir+"/.my.cnf"; // assume default configuration file
229	}
230	// else the location of the connection file was given
231	// Open the configuration file
232	BufferedReader fileIn = null;
233	StringTokenizer str;
234	String item, oneLine;
235	// to control if the minimum necessary 3 parameters are given in file
236	int cfgParsPresent=0;
237	// setting default blank values for port and dbname, they are set to blank unless fields specified in file
238	port="";
239	dbname="";
240	// list the entries in the file and decompose them
241	try {
242	fileIn = new BufferedReader(new FileReader(new File(connFile))); // open BufferedReader to file connFile
243	while ((oneLine = fileIn.readLine()) != null ) {
244	// Construct a stringTokenizer for the line that we read with : delimited
245	str = new StringTokenizer(oneLine, "="); // true sets returnDelimiters flag
246	while ( str.hasMoreTokens()) {
247	item = str.nextToken();
248	if( item.equals("host")) { // mandatory parameter
249	host=str.nextToken();
250	cfgParsPresent++;
251	break;
252	} // end if host
253	if( item.equals("port")) { // optional parameter
254	port=":"+str.nextToken();
255	break;
256	} // end if port
257	if( item.equals("user")) { // mandatory parameter
258	user=str.nextToken();
259	cfgParsPresent++;
260	break;
261	} // end if password
262	if( item.equals("password")) { // mandatory parameter
263	password=str.nextToken();
264	cfgParsPresent++;
265	break;
266	} // end if password
267	if( item.equals("database")) { // optional parameter
268	dbname=str.nextToken();
269	break;
270	} // end if password
271	} // next token in this line
272	} // next line in the file
273	if (cfgParsPresent<3){
274	System.err.println("Not all mandatory parameters are given in connection file "+connFile+". Can't connect to mysql server, exiting.");
275	System.exit(1);
276	}
277	}
278	catch (IOException e) {
279	System.err.println("Couldn't open file "+connFile);
280	e.printStackTrace();
281	System.exit(1);
282	}
283
284	try { // closing the file
285	if (fileIn != null) fileIn.close();
286	} catch (IOException e) {
287	System.err.println("Couldn't close file "+connFile);
288	e.printStackTrace();
289	}
290
291	}
292
293
294	}