I wrote the following class DBManager to access the mySQL DB server from java programs.
-----------------------------------------
// DBManager.java
import java.sql.*;
public class DBManager{
Connection connection=null;
Statement statement=null;
public DBManager(){ // constructor
initConnection();
}
void initConnection(){ // initialize DB connection and statement
if(connection != null) return;
try{
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://localhost/cf?user=scott&password=tiger");
statement = connection.createStatement();
} catch (ClassNotFoundException e){
System.err.println(e);
} catch (SQLException sqle){
System.err.println(sqle);
}
}
public void closeConnection(){
try{
if(statement != null){ statement.close(); }
if(connection != null){ connection.close(); }
} catch (SQLException sqle){
System.err.println(sqle);
}
}
public static void updateDB(String updateSQL){ // given the updateSQL, execute it
try {
new DBManager().statement.executeUpdate(updateSQL);
} catch (SQLException sqle){
System.err.println(sqle);
}
}
// given the querySQL, return the ResultSet
public static ResultSet getQueryResultSet(String querySQL) {
ResultSet resultSet=null;
try {
resultSet = new DBManager().statement.executeQuery(querySQL);
} catch (SQLException sqle){
System.err.println(sqle);
}
return resultSet;
}
} // class DBManager
-----------------------------------------
And use it in the following way:
String sqlQuery = "SELECT age FROM users WHERE userID ='Mary'";
ResultSet rs = DBManager.getQueryResultSet(sqlQuery);
while(rs.next()){
age = rs.getInt(1);
//do something with age here...
}
Is there any problem in this approach?
Will the connection created in getQueryResultSet() closed automatically after I call this method?
There seems to be a limit on the number of available connections. As my program query the DB using the above approach continously, I am concerned whether the available connections would be exhausted. I started my program this afternoon and went out for a hour or so, when I came back, I found the following error message:
--------------------------
java.sql.SQLException: Server configuration denies access to data source
** Exception
java.lang.NullPointerException
at DBManager.getQueryResultSet(DBManager.java:97)
--------------------------
What could be the reason of this error?
To see whether it's the problem of connections, I wrote the following code to test it,
int count=1;
String sql = "select userID from users";
while(true){
try{
System.out.println("\ncount="+ count++);
ResultSet rs = DBManager.getQueryResultSet(sql);
while(rs.next()){
System.out.print(" " + rs.getString(1));
}
}catch(Exception e){e.printStackTrace();}
}
And it didn't generate any error so far: the count is already 68000!
I am confused. Anybody can help me?
Thanks.