JDBC

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:

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

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)

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)

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)

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.

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)

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)

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.

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.

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. :)

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.