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 TITLEDataBase 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(brMessage: + ex.getMessage ()); out.println(brSQLState: + ex.getSQLState ()); out.println(brErrorCode: + 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(brQuery 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!+
Re: Trouble with JDBC (I need a little help)
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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Trouble with JDBC (I need a little help)
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 TITLEDataBase 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(brQuery 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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Trouble with JDBC (I need a little help)
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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]