Hi,
I'm beginning JDBC and I'm conversant in SQL. But I need to Connect to database and that where the problem lies. All I have done is adding mysql/j connector Jar to Netbeans project and wrote code below to test. It cannot connect to database. Also IDE throws error:

package dbclass;
//imports
import java.sql.*;

public class DBClass {

    public ResultSet changeDBInfo() throws SQLException {
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            conn = DriverManager.getConnection(this.host, this.userName, this.passWord);
            if (!conn.isClosed()) {
                System.out.println("Connected Successfully");
            }
            Statement st = conn.createStatement();
            return st;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            conn.close();
        }
        return null;
    }
    //credentials
    private String host = "jdbc:mysql://localhost:3306/mysql";
    private String userName = "root";
    private String passWord = "ilovejesus";
}

Recommended Answers

All 34 Replies

Since I'm learning, I googled a little and rewrote my code. I want at the end to write a generic class I will use for all my application to connect to database. For now, All I want is to test make sure that things are working. Here is my test class

MainApp.java

package dbclass;

/**
 *Testing JDBC
 */
public class MainApp {
    public static void main(String[] args){
        String query = "Select * FROM logintable";
        DBClass db = new DBClass();
        db.getDbInfo(query);
    }

}

DBClass.java

package dbclass;
//imports
import java.sql.*;

class DBClass {
    //private

    private String dbHost = "jdbc:mysql://localhost:3306";
    private String dbUser = "root";
    private String dbPassword = "ilovejesus";
    private String dbClass = "com.mysql.jdbc.Driver";

    public void getDbInfo(String sql) {
        try {
            Class.forName(this.dbClass).newInstance();
            Connection con = DriverManager.getConnection(this.dbHost, this.dbUser, this.dbPassword);
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            String dbtime;
            while (rs.next()) {
                dbtime = rs.getString(1);
                System.out.println(dbtime);
            } 
        }
        catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        catch (SQLException e) {
            e.printStackTrace();
        }

    }
}  //end class

I just complete the above code:
put main function....

package a;
//imports
import java.sql.*;

public class DBClass {
    //private

    private String dbHost = "jdbc:mysql://localhost/phonebook";
    private String dbUser = "root";
    private String dbPassword = "";
    private String dbClass = "com.mysql.jdbc.Driver";

    public void getDbInfo(String sql) throws InstantiationException, IllegalAccessException {
        try {
            Class.forName(this.dbClass).newInstance();
            Connection con = DriverManager.getConnection(this.dbHost, this.dbUser, this.dbPassword);
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            String dbtime;
            while (rs.next()) {
                dbtime = rs.getString(1);
                System.out.println(dbtime);
            } 
        }
        catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        catch (SQLException e) {
            e.printStackTrace();
        }

    }
    
    public static void main(String[] args) {
    	DBClass disp = new DBClass();
    	try {
			disp.getDbInfo("SELECT * FROM phonebook");
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    }
}  //end class

Now I have re-written the code and I get error:

debug:
Have no FileObject for C:\Program Files\Java\jdk1.6.0\jre\lib\sunrsasign.jar
Have no FileObject for C:\Program Files\Java\jdk1.6.0\jre\classes
Table Rows
java.sql.SQLException: Operation not allowed after ResultSet closed
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929)
at com.mysql.jdbc.ResultSetImpl.checkClosed(ResultSetImpl.java:795)
at com.mysql.jdbc.ResultSetImpl.checkRowPos(ResultSetImpl.java:839)
at com.mysql.jdbc.ResultSetImpl.getStringInternal(ResultSetImpl.java:5657)
at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5577)
at dbclass.MainApp.main(MainApp.java:16)
BUILD SUCCESSFUL (total time: 3 seconds)

Here is the code, I wonder what went wrong

//DBClass.java
package dbclass;
//imports
import java.sql.*;

class DBClass {
    //private

    private String dbHost = "jdbc:mysql://localhost:3306/";
    private String dbUser = "root";
    private String dbPassword = "ilovejesus";
    private String dbClass = "com.mysql.jdbc.Driver";
    private String dbName = "sitedb";

    public Connection conn = null; //holds connection
    public ResultSet rs = null; //holds result

    //informartive query
    public ResultSet dbSelect(String sql) throws SQLException {
        try {
            Class.forName(this.dbClass).newInstance();
            conn = DriverManager.getConnection(this.dbHost+this.dbName, this.dbUser, this.dbPassword);
            Statement stmt = this.conn.createStatement();
            rs = stmt.executeQuery(sql);
        } catch (Exception e) {
            //should email admin the error message
            e.printStackTrace();
            rs = null;
        }
        finally{
            this.conn.close();
            return rs;
        }        
    }

    //All update methods
     public int dbChange(String sql) throws SQLException {
         int i=24;
        try {
            Class.forName(this.dbClass).newInstance();
            conn = DriverManager.getConnection(this.dbHost+this.dbName, this.dbUser, this.dbPassword);
            PreparedStatement preparedSQL = this.conn.prepareStatement(sql);
            i = preparedSQL.executeUpdate();

        } catch (Exception e) {
            //should email admin the error message
            e.printStackTrace();
            i= -24; //error somewhere
        }
        finally{
            this.conn.close();
            return i; //error somewhere
        }
    }


    
}//end class
//MainApp.java
package dbclass;

import java.sql.*;

public class MainApp {

    public static void main(String[] args) {
        String query = "Select * FROM logintable";
        DBClass db = new DBClass();
        try {
            ResultSet rs = db.dbSelect(query);
            System.out.println("Table Rows");

           do {
                for (int i = 1; i < 3; i++) {
                    System.out.println("Table Rows\t"+rs.getString(i));
                }
                 System.out.println(" ");
            } while (rs.next());
            
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Now I have re-written the code and I get error:

debug:
Have no FileObject for C:\Program Files\Java\jdk1.6.0\jre\lib\sunrsasign.jar
Have no FileObject for C:\Program Files\Java\jdk1.6.0\jre\classes
Table Rows
java.sql.SQLException: Operation not allowed after ResultSet closed
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929)
at com.mysql.jdbc.ResultSetImpl.checkClosed(ResultSetImpl.java:795)
at com.mysql.jdbc.ResultSetImpl.checkRowPos(ResultSetImpl.java:839)
at com.mysql.jdbc.ResultSetImpl.getStringInternal(ResultSetImpl.java:5657)
at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5577)
at dbclass.MainApp.main(MainApp.java:16)
BUILD SUCCESSFUL (total time: 3 seconds)

Here is the code, I wonder what went wrong

//DBClass.java
package dbclass;
//imports
import java.sql.*;

class DBClass {
    //private

    private String dbHost = "jdbc:mysql://localhost:3306/";
    private String dbUser = "root";
    private String dbPassword = "ilovejesus";
    private String dbClass = "com.mysql.jdbc.Driver";
    private String dbName = "sitedb";

    public Connection conn = null; //holds connection
    public ResultSet rs = null; //holds result

    //informartive query
    public ResultSet dbSelect(String sql) throws SQLException {
        try {
            Class.forName(this.dbClass).newInstance();
            conn = DriverManager.getConnection(this.dbHost+this.dbName, this.dbUser, this.dbPassword);
            Statement stmt = this.conn.createStatement();
            rs = stmt.executeQuery(sql);
        } catch (Exception e) {
            //should email admin the error message
            e.printStackTrace();
            rs = null;
        }
        finally{
            this.conn.close();
            return rs;
        }        
    }

    //All update methods
     public int dbChange(String sql) throws SQLException {
         int i=24;
        try {
            Class.forName(this.dbClass).newInstance();
            conn = DriverManager.getConnection(this.dbHost+this.dbName, this.dbUser, this.dbPassword);
            PreparedStatement preparedSQL = this.conn.prepareStatement(sql);
            i = preparedSQL.executeUpdate();

        } catch (Exception e) {
            //should email admin the error message
            e.printStackTrace();
            i= -24; //error somewhere
        }
        finally{
            this.conn.close();
            return i; //error somewhere
        }
    }


    
}//end class
//MainApp.java
package dbclass;

import java.sql.*;

public class MainApp {

    public static void main(String[] args) {
        String query = "Select * FROM logintable";
        DBClass db = new DBClass();
        try {
            ResultSet rs = db.dbSelect(query);
            System.out.println("Table Rows");

           do {
                for (int i = 1; i < 3; i++) {
                    System.out.println("Table Rows\t"+rs.getString(i));
                }
                 System.out.println(" ");
            } while (rs.next());
            
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Haven't anyone come across this error?

debug:
Have no FileObject for C:\Program Files\Java\jdk1.6.0\jre\lib\sunrsasign.jar
Have no FileObject for C:\Program Files\Java\jdk1.6.0\jre\classes
Table Rows
java.sql.SQLException: Operation not allowed after ResultSet closed
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929)
at com.mysql.jdbc.ResultSetImpl.checkClosed(ResultSetImpl.java:795)
at com.mysql.jdbc.ResultSetImpl.checkRowPos(ResultSetImpl.java:839)
at com.mysql.jdbc.ResultSetImpl.getStringInternal(ResultSetImpl.java:5657)
at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5577)
at dbclass.MainApp.main(MainApp.java:16)
BUILD SUCCESSFUL (total time: 3 seconds)

Its because you are closing the connection in the finally block.

So where should I close my connection?
Or should I leave that and it will clean for me?
Won't it cause leaks?
Please someone who knows lend me a help

you need to redesign your DBClass.
put the creation of the connection in the Constructor. (you need to define one)
And use this connection in the functions subsequently.
You can close this connection in a destructor, or write a separate function which destroys/disconnects the connection.

Thanks A lot
It makes sense of error now

See this and Criticize because It still throws errors

java.sql.SQLException: Before start of result set
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929)
        at com.mysql.jdbc.ResultSetImpl.checkRowPos(ResultSetImpl.java:842)
        at com.mysql.jdbc.ResultSetImpl.getStringInternal(ResultSetImpl.java:5657)
        at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5577)
        at dbclass.MainApp.main(MainApp.java:16)
BUILD SUCCESSFUL (total time: 1 second)
package dbclass;
//imports
import java.sql.*;

class DBClass {
    
    //Ctor
    DBClass()  {
        try {
            Class.forName(this.dbClass).newInstance();
            conn = DriverManager.getConnection(this.dbHost + this.dbName, this.dbUser, this.dbPassword);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //private
    private String dbHost = "jdbc:mysql://localhost:3306/";
    private String dbUser = "root";
    private String dbPassword = "ilovejesus";
    private String dbClass = "com.mysql.jdbc.Driver";
    private String dbName = "sitedb";
    public Connection conn = null; //holds connection
    public ResultSet rs = null; //holds result

    //informartive query
    public ResultSet dbSelect(String sql){
        try {
            Statement stmt = this.conn.createStatement();
            rs = stmt.executeQuery(sql);
        } catch (Exception e) {
            //should email admin the error message
            e.printStackTrace();
            rs = null;
        } finally {
            return rs;
        }
    }

    //All update methods
    public int dbChange(String sql){
        int i = 24;
        try {
            Class.forName(this.dbClass).newInstance();
            conn = DriverManager.getConnection(this.dbHost + this.dbName, this.dbUser, this.dbPassword);
            PreparedStatement preparedSQL = this.conn.prepareStatement(sql);
            i = preparedSQL.executeUpdate();

        } catch (Exception e) {
            //should email admin the error message
            e.printStackTrace();
            i = -24; //error somewhere
        } finally {
            return i; //error somewhere
        }
    }


  //Dtor
  @Override
  protected void finalize()throws Throwable{
  //do finalization here
  super.finalize(); //not necessary if extending Object.
  this.conn.close();

}



}//end class

Cool, Just change loop to while loop and it works. You need to call next before retrieve from resultset

And due to new design, I cannot see "Solved" button

See this and Criticize because It still throws errors

java.sql.SQLException: Before start of result set
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929)
        at com.mysql.jdbc.ResultSetImpl.checkRowPos(ResultSetImpl.java:842)
        at com.mysql.jdbc.ResultSetImpl.getStringInternal(ResultSetImpl.java:5657)
        at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5577)
        at dbclass.MainApp.main(MainApp.java:16)
BUILD SUCCESSFUL (total time: 1 second)
package dbclass;
//imports
import java.sql.*;

class DBClass {
    
    //Ctor
    DBClass()  {
        try {
            Class.forName(this.dbClass).newInstance();
            conn = DriverManager.getConnection(this.dbHost + this.dbName, this.dbUser, this.dbPassword);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //private
    private String dbHost = "jdbc:mysql://localhost:3306/";
    private String dbUser = "root";
    private String dbPassword = "ilovejesus";
    private String dbClass = "com.mysql.jdbc.Driver";
    private String dbName = "sitedb";
    public Connection conn = null; //holds connection
    public ResultSet rs = null; //holds result

    //informartive query
    public ResultSet dbSelect(String sql){
        try {
            Statement stmt = this.conn.createStatement();
            rs = stmt.executeQuery(sql);
        } catch (Exception e) {
            //should email admin the error message
            e.printStackTrace();
            rs = null;
        } finally {
            return rs;
        }
    }

    //All update methods
    public int dbChange(String sql){
        int i = 24;
        try {
            Class.forName(this.dbClass).newInstance();
            conn = DriverManager.getConnection(this.dbHost + this.dbName, this.dbUser, this.dbPassword);
            PreparedStatement preparedSQL = this.conn.prepareStatement(sql);
            i = preparedSQL.executeUpdate();

        } catch (Exception e) {
            //should email admin the error message
            e.printStackTrace();
            i = -24; //error somewhere
        } finally {
            return i; //error somewhere
        }
    }


  //Dtor
  @Override
  protected void finalize()throws Throwable{
  //do finalization here
  super.finalize(); //not necessary if extending Object.
  this.conn.close();

}



}//end class

Though the concept of `finalize` sounds pretty slick, they are rarely used and for a good reason. A better way would be to carefully manage your connection/statement/resultset resource cleanup or use a library which already does that for you.

So, what approach would be best if I want to implement my Own?
In C++ I would do all cleaning at destructor.

I have made this function which I call Manually after playing with my Queries. Is it good on your opinion?

public void CleanUp(ResultSet rs) throws Throwable {
        if(rs!=null){
            rs.close();
        }
        //do finalization here
        super.finalize(); //not necessary if extending Object.
        this.conn.close();

    }

And then called it in finally

DBClass db = new DBClass();
        
String sql = "SELECT * FROM user ";       
try{
           ResultSet rs = db.dbSelect(sql);
           return rs;
       }
catch(Exception e){
           e.printStackTrace();
           return null;
       }
finally{
           db.CleanUp(rs);
       }

And then called it in finally

Did you read the links I posted in my previous post? Don't use `finalize` for cleaning up connections/resultsets etc. Get in the habit of reading source code of libraries. Reading the source code of DBUtils would give you a fair idea as to how resource cleanup needs to be done when dealing with JDBC.

I forgotten to update.
I have removed super.finalize.
is it right?

public void CleanUp(ResultSet rs) throws Throwable {
        if(rs!=null){
            rs.close();
        }
        //do finalization here
        this.conn.close();

    }

I'd have something like this:

class DbUtils {

    public static void cleanupSilently(ResultSet rs, Statement stmt,
            Connection conn) {
        closeResultSetSilently(rs);
        closeStatementSilently(stmt);
        closeConnectionSilently(conn);
    }

    public static void closeResultSetSilently(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void closeStatementSilently(Statement stmt) {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void closeConnectionSilently(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static Connection getConnection() {
        // TODO: Get a pooled connection or retrieve it from the DriverManager
        return null;
    }

}

class SomethingGotScrewed extends RuntimeException {

    private static final long serialVersionUID = 1L;

    public SomethingGotScrewed() {
        super();
    }

    public SomethingGotScrewed(String message, Throwable cause) {
        super(message, cause);
    }

    public SomethingGotScrewed(String message) {
        super(message);
    }

    public SomethingGotScrewed(Throwable cause) {
        super(cause);
    }

}

public class DbUtilsTest {

    public static void main(final String[] args) {
        executeSampleQuery();
    }

    private static void executeSampleQuery() {
        final String query = "SELECT * FROM MY_TABLE";
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            try {
                conn = DbUtils.getConnection();
                stmt = conn.prepareStatement(query);
                rs = stmt.executeQuery();
                // Iterate over the result set and get the data
            } catch (Exception e) {
                throw new SomethingGotScrewed(e);
            }
        } finally {
            DbUtils.cleanupSilently(rs, stmt, conn);
        }
    }

}

That is cool,
I have one question though: How pooled connection differ from the other one?
Thanks for such big example :)

In simple terms, Connection pooling is an optimization technique which initializes the connection objects in advance so that a request for database connection can be fulfilled as soon as possible without going through the expensive and time consuming activity of initializing a connection.

This job is typically done by a Connection pooling library like DBCP, Commons Pooling etc. The concept here is pretty simple; instead of giving the requesting object (for e.g. a DAO) a handle/reference to an actual database Connection object, the pooling library hands out a wrapper Connection object. The basic difference between this wrapped connection and the actual connection object is that a Connection.close() operation in the case of the former sends the actual connection back to the pool and doesn't actually close it but in case of the latter, the actual database connection is closed. For more information, search around for 'connection pooling' and I'm sure you would get a lot of good material to read.

For production deployments, *always* prefer pooled connections. Consider it as an industry de-facto standard. :)

Thanks again for such clear explanation.
What pool library do you use or recommend for production enviroment?

When using an application server like Websphere, Weblogic etc. I normally go with the default connection pooling implementation which comes bundled with the server. When using a web container like Tomcat, Jetty etc., Commons DBCP is a pretty decent production grade connection pooling implementation.

Yes

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.