You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tomcat.apache.org by Maxime <ma...@free.fr> on 2005/03/16 09:29:10 UTC
Trouble with JDBC (I need a little help)
Hello Everybody;
I'am asking for help because I have a problem that it's making me crazy.
To do some query to the Database, I am using a Class named DBConnection.
It's like around 1 week, I didn't do anything on the code (perhaps I did because it doesn't working now).
Well... , in order to find the problem I did some test function, but I still can't find it.
Here we go, it's not very long, it's just some basic code and 2 logs
This the JSP where my test start.
Test.jsp
<HTML>
<HEAD>
<TITLE>DataBase Test!</TITLE>
</HEAD>
DataBase Test Button
<FORM ACTION="http://localhost:8080/Training/TEST2">
<INPUT TYPE="SUBMIT" VALUE = "test">
</FORM>
DataBase Test Button2
<FORM ACTION="http://localhost/Training/TEST3">
<INPUT TYPE="SUBMIT" VALUE = "test">
</FORM>
</BODY>
</HTML>
We have 2 button to access on servlet Test2 and Test3
Here they go :
TEST2.java :
package Training;
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;
import java.sql.DriverManager;
public class TEST2 extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html");
PrintWriter out = response.getWriter();
Statement stmt;
ResultSet result;
String url = "jdbc:mysql://localhost:3306/HeroDB";
String user = "Login";
String password = "Password";
try{
Class.forName("com.mysql.jdbc.Driver");
out.println("<br> DRIVERS JDBC : OK!");
Connection connection = DriverManager.getConnection(url,user,password);
out.println("<br> Databse Connexion : OK!");
String req ="Select * FROM User_Table";
stmt = connection.createStatement();
result = stmt.executeQuery(req);
// This part was added to see if we can catch the total row
int numRows = 0;
result.last();
numRows = result.getRow();
result.first();
out.println(numRows);
result.beforeFirst();
while (result.next())
{
out.println(result.getString("Login"));
}
}
catch (ClassNotFoundException e)
{
out.println("PB with Drivers");
}
catch(SQLException ex) {
out.println("<br> Error Message <br>");
while (ex != null) {
out.println("<br>Message: " + ex.getMessage ());
out.println("<br>SQLState: " + ex.getSQLState ());
out.println("<br>ErrorCode: " + ex.getErrorCode ());
ex = ex.getNextException();
out.println("");
}
}
}
}
This code works perfectly, all users are displayed + the total row of the resultset.
But now, here the interested part (where it give me some headache:
TEST3.java :
package Training;
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;
import java.sql.DriverManager;
public class TEST3 extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html");
PrintWriter out = response.getWriter();
// New Instance : DBConnection
DBConnection db = new DBConnection();
String req ="Select * FROM User_Table";
try{
db.Connect();
out.println("<br> DRIVERS JDBC : OK!");
out.println("<br> Connexion TO DB : OK!");
ResultSet Result = db.QueryDataBase(req);
// This part was added to see if we can catch the total row
int col = db.TotalRows(Result);
if (col == 0)
{
out.println("<br>Query to DB is not ok!<br>");
}
else{
out.println(col);
}
}
catch (ClassNotFoundException e)
{
out.println("PB with Drivers");
}
catch (Exception x)
{
out.println(x);
}
}
}
DBConnection.java
package Training;
import java.sql.*;
import java.sql.DriverManager;
public class DBConnection{
Connection connection;
Statement stmt;
ResultSet result;
public DBConnection(){
}
public void Connect() throws Exception, SQLException{
String url = "jdbc:mysql://localhost:3306/HeroDB";
String user = "Login";
String password = "Password";
try {
// Load JDBC Drivers
Class.forName("com.mysql.jdbc.Driver");
// make the connection with the database
Connection connection = DriverManager.getConnection(url,user,password);
}
catch(SQLException sqle){
System.out.println(sqle.getMessage());
}
catch(Exception e){
System.out.println("The Connection Failed !"+ e.getMessage());
}
}
public ResultSet QueryDataBase(String SQLFunc)throws SQLException, Exception{
try{
stmt = connection.createStatement();
result = stmt.executeQuery(SQLFunc);
}
catch(SQLException sqle){
System.out.println("Problem with getting Result1!"+ sqle.getMessage());
System.out.println("Problem with getting Result2!"+ sqle.getSQLState());
System.out.println("Problem with getting Result3!"+ sqle.getErrorCode());
}
catch(Exception e){
System.out.println("Problem with getting Result4!"+ e.getMessage());
}
return result;
}
public int TotalRows (ResultSet rs) throws SQLException, Exception
{
int numRows = 0;
try{
rs.last();
numRows = rs.getRow();
rs.first();
}
catch(SQLException sqle)
{
System.out.println("Problem with getting Row1"+ sqle.getMessage());
System.out.println("Problem with getting Row2"+ sqle.getSQLState());
System.out.println("Problem with getting Row3"+ sqle.getErrorCode());
}
catch(Exception e){
System.out.println("Problem with getting getting Row4!"+ e.getMessage());
}
return numRows;
}
}
I don't understand why it doesn't work! The result is :
DRIVERS JDBC : OK!
Connexion TO DB : OK!
Query to DB is not ok! (look like the getRow function give me 0 Row)
So, why the first code works and this one doesn't ?
Here's the error logs for MySQL and TomCat:
MySQL :
Version: '4.0.20a-nt' socket: '' port: 3306
050315 19:15:52 Aborted connection 4 to db: 'herodb' user: 'Login' host: `localhost' (Got an error reading communication packets)
050315 19:16:42 Aborted connection 1 to db: 'herodb' user: 'Login' host: `localhost' (Got an error reading communication packets)
TOMCAT :
Mar 15, 2005 7:10:46 PM org.apache.catalina.core.StandardContext reload
INFO: Reloading this Context has started
Mar 15, 2005 7:10:46 PM org.apache.catalina.logger.LoggerBase stop
INFO: unregistering logger Catalina:type=Logger,path=/monk2,host=localhost
NotifyUtil::java.net.ConnectException: Connection refused: connect
at java.net.PlainSocketImpl.socketConnect(Native Method)
at java.net.PlainSocketImpl.doConnect(Unknown Source)
at java.net.PlainSocketImpl.connectToAddress(Unknown Source)
at java.net.PlainSocketImpl.connect(Unknown Source)
at java.net.Socket.connect(Unknown Source)
at java.net.Socket.connect(Unknown Source)
at sun.net.NetworkClient.doConnect(Unknown Source)
at sun.net.www.http.HttpClient.openServer(Unknown Source)
at sun.net.www.http.HttpClient.openServer(Unknown Source)
at sun.net.www.http.HttpClient.<init>(Unknown Source)
at sun.net.www.http.HttpClient.New(Unknown Source)
at sun.net.www.http.HttpClient.New(Unknown Source)
at sun.net.www.protocol.http.HttpURLConnection.getNewHttpClient(Unknown Source)
at sun.net.www.protocol.http.HttpURLConnection.plainConnect(Unknown Source)
at sun.net.www.protocol.http.HttpURLConnection.connect(Unknown Source)
at sun.net.www.protocol.http.HttpURLConnection.getOutputStream(Unknown Source)
at org.netbeans.modules.web.monitor.server.NotifyUtil$RecordSender.run(NotifyUtil.java:237)
NotifyUtil::java.net.ConnectException: Connection refused: connect
at java.net.PlainSocketImpl.socketConnect(Native Method)
at java.net.PlainSocketImpl.doConnect(Unknown Source)
at java.net.PlainSocketImpl.connectToAddress(Unknown Source)
at java.net.PlainSocketImpl.connect(Unknown Source)
at java.net.Socket.connect(Unknown Source)
at java.net.Socket.connect(Unknown Source)
at sun.net.NetworkClient.doConnect(Unknown Source)
at sun.net.www.http.HttpClient.openServer(Unknown Source)
at sun.net.www.http.HttpClient.openServer(Unknown Source)
at sun.net.www.http.HttpClient.<init>(Unknown Source)
at sun.net.www.http.HttpClient.New(Unknown Source)
at sun.net.www.http.HttpClient.New(Unknown Source)
at sun.net.www.protocol.http.HttpURLConnection.getNewHttpClient(Unknown Source)
at sun.net.www.protocol.http.HttpURLConnection.plainConnect(Unknown Source)
at sun.net.www.protocol.http.HttpURLConnection.connect(Unknown Source)
at sun.net.www.protocol.http.HttpURLConnection.getOutputStream(Unknown Source)
at org.netbeans.modules.web.monitor.server.NotifyUtil$RecordSender.run(NotifyUtil.java:237)
NotifyUtil::java.net.ConnectException: Connection refused: connect
at java.net.PlainSocketImpl.socketConnect(Native Method)
at java.net.PlainSocketImpl.doConnect(Unknown Source)
at java.net.PlainSocketImpl.connectToAddress(Unknown Source)
at java.net.PlainSocketImpl.connect(Unknown Source)
at java.net.Socket.connect(Unknown Source)
at java.net.Socket.connect(Unknown Source)
at sun.net.NetworkClient.doConnect(Unknown Source)
at sun.net.www.http.HttpClient.openServer(Unknown Source)
at sun.net.www.http.HttpClient.openServer(Unknown Source)
at sun.net.www.http.HttpClient.<init>(Unknown Source)
at sun.net.www.http.HttpClient.New(Unknown Source)
at sun.net.www.http.HttpClient.New(Unknown Source)
at sun.net.www.protocol.http.HttpURLConnection.getNewHttpClient(Unknown Source)
at sun.net.www.protocol.http.HttpURLConnection.plainConnect(Unknown Source)
at sun.net.www.protocol.http.HttpURLConnection.connect(Unknown Source)
at sun.net.www.protocol.http.HttpURLConnection.getOutputStream(Unknown Source)
at org.netbeans.modules.web.monitor.server.NotifyUtil$RecordSender.run(NotifyUtil.java:237)
Problem with getting Result4!null
Problem with getting getting Row4!null
NotifyUtil::java.net.ConnectException: Connection refused: connect
at java.net.PlainSocketImpl.socketConnect(Native Method)
at java.net.PlainSocketImpl.doConnect(Unknown Source)
at java.net.PlainSocketImpl.connectToAddress(Unknown Source)
at java.net.PlainSocketImpl.connect(Unknown Source)
at java.net.Socket.connect(Unknown Source)
at java.net.Socket.connect(Unknown Source)
at sun.net.NetworkClient.doConnect(Unknown Source)
at sun.net.www.http.HttpClient.openServer(Unknown Source)
at sun.net.www.http.HttpClient.openServer(Unknown Source)
at sun.net.www.http.HttpClient.<init>(Unknown Source)
at sun.net.www.http.HttpClient.New(Unknown Source)
at sun.net.www.http.HttpClient.New(Unknown Source)
at sun.net.www.protocol.http.HttpURLConnection.getNewHttpClient(Unknown Source)
at sun.net.www.protocol.http.HttpURLConnection.plainConnect(Unknown Source)
at sun.net.www.protocol.http.HttpURLConnection.connect(Unknown Source)
at sun.net.www.protocol.http.HttpURLConnection.getOutputStream(Unknown Source)
at org.netbeans.modules.web.monitor.server.NotifyUtil$RecordSender.run(NotifyUtil.java:237)
Info : I'am using Tomcat 5.0.28, Mysql 4.0.20 and Java 1.5.0_01.
Thank you very much for the help, it will be very appreciated !
Cya later
Maxime
Re: Trouble with JDBC (I need a little help)
Posted by Jon Wingfield <jo...@mkodo.com>.
It's a NullPointerException causing all your woes. In the Connect method
of DBConnection you are assigning to a local variable of type Connection
instead of the instance variable. The instance variable is always null,
which causes issues in the QueryDataBase and TotalRows methods.
The code as it stands is rather flaky, I'm afraid.
Look into connection pooling (docs on the tomcat site and many posts
related to it on this list) and defensive programming (checking for null
values etc, etc). Also, Wolfgang's advice is good: always close
ResultSets and Statements after you're done with them (and connections
for that matter).
HTH,
Jon
Maxime wrote:
> Hello Everybody;
> I'am asking for help because I have a problem that it's making me crazy.
> To do some query to the Database, I am using a Class named DBConnection.
>
> It's like around 1 week, I didn't do anything on the code (perhaps I did because it doesn't working now).
> Well... , in order to find the problem I did some test function, but I still can't find it.
> Here we go, it's not very long, it's just some basic code and 2 logs
>
>
>
> This the JSP where my test start.
>
> Test.jsp
> <HTML>
> <HEAD>
> <TITLE>DataBase Test!</TITLE>
> </HEAD>
>
> DataBase Test Button
> <FORM ACTION="http://localhost:8080/Training/TEST2">
> <INPUT TYPE="SUBMIT" VALUE = "test">
> </FORM>
> DataBase Test Button2
> <FORM ACTION="http://localhost/Training/TEST3">
> <INPUT TYPE="SUBMIT" VALUE = "test">
> </FORM>
>
> </BODY>
> </HTML>
>
> We have 2 button to access on servlet Test2 and Test3
>
> Here they go :
>
>>
> TEST3.java :
>
> package Training;
>
> import java.io.*;
> import javax.servlet.*;
> import javax.servlet.http.*;
> import java.sql.*;
> import java.sql.DriverManager;
>
> public class TEST3 extends HttpServlet {
> public void doGet(HttpServletRequest request, HttpServletResponse response)
> throws ServletException, IOException {
>
> response.setContentType("text/html");
> PrintWriter out = response.getWriter();
>
> // New Instance : DBConnection
> DBConnection db = new DBConnection();
>
> String req ="Select * FROM User_Table";
>
> try{
>
> db.Connect();
> out.println("<br> DRIVERS JDBC : OK!");
> out.println("<br> Connexion TO DB : OK!");
>
> ResultSet Result = db.QueryDataBase(req);
>
> // This part was added to see if we can catch the total row
> int col = db.TotalRows(Result);
> if (col == 0)
> {
> out.println("<br>Query to DB is not ok!<br>");
> }
> else{
> out.println(col);
> }
>
> }
> catch (ClassNotFoundException e)
> {
> out.println("PB with Drivers");
> }
>
> catch (Exception x)
> {
> out.println(x);
> }
>
> }
> }
>
>
>
> DBConnection.java
>
> package Training;
>
> import java.sql.*;
> import java.sql.DriverManager;
>
> public class DBConnection{
>
> Connection connection;
> Statement stmt;
> ResultSet result;
>
>
> public DBConnection(){
> }
>
> public void Connect() throws Exception, SQLException{
>
> String url = "jdbc:mysql://localhost:3306/HeroDB";
> String user = "Login";
> String password = "Password";
>
> try {
> // Load JDBC Drivers
> Class.forName("com.mysql.jdbc.Driver");
> // make the connection with the database
The problem's right here:
> Connection connection = DriverManager.getConnection(url,user,password);
> }
> catch(SQLException sqle){
>
> System.out.println(sqle.getMessage());
>
>
> }
> catch(Exception e){
>
> System.out.println("The Connection Failed !"+ e.getMessage());
>
> }
>
>
> }
>
> public ResultSet QueryDataBase(String SQLFunc)throws SQLException, Exception{
> try{
> stmt = connection.createStatement();
> result = stmt.executeQuery(SQLFunc);
> }
> catch(SQLException sqle){
>
> System.out.println("Problem with getting Result1!"+ sqle.getMessage());
> System.out.println("Problem with getting Result2!"+ sqle.getSQLState());
> System.out.println("Problem with getting Result3!"+ sqle.getErrorCode());
>
> }
> catch(Exception e){
>
> System.out.println("Problem with getting Result4!"+ e.getMessage());
>
> }
> return result;
> }
>
>
> public int TotalRows (ResultSet rs) throws SQLException, Exception
> {
> int numRows = 0;
> try{
>
> rs.last();
> numRows = rs.getRow();
> rs.first();
> }
>
> catch(SQLException sqle)
> {
> System.out.println("Problem with getting Row1"+ sqle.getMessage());
> System.out.println("Problem with getting Row2"+ sqle.getSQLState());
> System.out.println("Problem with getting Row3"+ sqle.getErrorCode());
> }
> catch(Exception e){
> System.out.println("Problem with getting getting Row4!"+ e.getMessage());
> }
>
> return numRows;
> }
>
> }
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
Re: Trouble with JDBC (I need a little help)
Posted by Maxime <ma...@free.fr>.
Fenderbosch Eric, Wolfgang Hackl and Jon Wingfield. Hi and thank you!
It's looks like I have a lot of thing to learn (it's nice to learn everyday
hehe). For closing connection + ResultSet and Statement, I wanted to include
them later.
Anyway, thank you very very much for these advices and answers. I read them
very carefully ! (Thank you for the explanation Jon, you were completly
right about the Connection)
It's looks like I have to completly rewrite de DBConnection Class, surely a
clean code (not a fuzzy like that old one) !
Thank you again, if you have some advices about that, I will gracefully take
them !
Cya later
Maxime
---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
Re: Trouble with JDBC (I need a little help)
Posted by Wolfgang Hackl <wo...@gmx.at>.
Maxime wrote:
>I'am asking for help because I have a problem that it's making me crazy.
>
>
[Program code and logs snipped]
>Thank you very much for the help, it will be very appreciated !
>
I am not familiar with MySQL therefore just a guess: I do not see a
single close() statement. Close your ResultSets and Statements after
usage (read some JDBC tutorials)! If this does not help with your
current problem, this might be the next problem you run into anyway.
Kind regards,
Wolfgang
---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org