Server connection failure during transaction. Due to underlying exception: 'java.net.ConnectException: Connection refused'. Attempted reconnect 3 time

Just would like to remind anyone who refering to both Remote Access To MySQL Database and Setup Java, Tomcat, MySQL on Ubuntu (JSP Hosting), do remember to change the datasource property which defined via JNDI.

If you are attempting to connect to the database which sit on localhost or other host, make sure the JDBC driver URL is defined exact same on both JNDI and my.cnf. So, the JNDI would able to point to the right host where the database is sit.



Else not, your web application would not able to connect to the database and you will get error similar as below:

javax.servlet.ServletException: Unable to get connection, DataSource invalid: "org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Server connection failure during transaction. Due to underlying exception: 'java.net.ConnectException: Connection refused'.Attempted reconnect 3 times. Giving up.)"

Database Navigation using Next and Previous link - Part 2

This is enhanced sample of codes from previous post at http://www.leejeok.com/2008/06/navigate-database-record-using-next-and.html

I have reduce some JSP code by using the SQL script to get and return the number of rows from database. I am using MySQL as the database and using the LIMIT clause to retrieve the number of rows to display on single page.

<%@ page import="javax.sql.DataSource" %>
<%@ page import="java.sql.*" %>
<%@ page import="javax.naming.*" %>

<%!
static final String ctxstr = "java:comp/env/jdbc/navigationDB";
%>
<html>
    <head>
        <title>Database Navigation using Next and Previous button</title>
    </head>
    <body>
        <form>
        <h1>Database Navigation using Next and Previous link</h1>
       
            <%
            int id = 0;
            String fullname = "";
            String country = "";
            int recordPerPage = 2;
            int gotoPage = 0;
           
            boolean moreResult = false;
           
            Connection conn = null;
            Statement  stat = null;
           
            //get page
            try {
                gotoPage = Integer.parseInt(request.getParameter("gotoPage"));
            } catch (NumberFormatException e ) {
                gotoPage = 0;
            }
           
            try {
                //lookup for the JNDI datasource
                InitialContext ctx = new InitialContext();
                DataSource ds = (DataSource)ctx.lookup(ctxstr);
               
                //create the database connection
                conn = ds.getConnection();
                stat = conn.createStatement();
               
                String offset = String.valueOf(gotoPage * recordPerPage);
                String rows = String.valueOf(recordPerPage);
               
                //execute query using SELECT and LIMIT clause.
                //LIMIT accept 2 argument.
                //the first represent the offset of the first row to return
                //the second represent the maximum number of rows to return
                String sql = "SELECT id, fullname, country FROM customer LIMIT "+offset+" , "+rows;
                ResultSet resultSet = stat.executeQuery(sql);
               
                out.println("<table border='1'><th>ID</th><th>Name</th><th>Country</th>");
               
                int totalRecord = 0;
                for (; resultSet.next(); totalRecord++) {
                    id = (int)resultSet.getInt("id");
                    fullname = (String)resultSet.getString("fullname");
                    country = (String)resultSet.getString("country");
                    out.println("<tr><td>"+id+"</td><td>"+fullname+"</td><td>"+country+"</td></tr>");
                }
                out.println("</table>");
                out.println("<br>");
               
                if(totalRecord == recordPerPage)
                    moreResult = true;
               
                //close connection
                resultSet.close();
                stat.close();
                stat = null;
                conn.close();
                conn = null;
               
            } catch(SQLException e) {
                out.println("An error occured: " + e.toString());
                return;
            } finally{
                if(conn != null)
                    conn.close();
            }
           
           
            if ( gotoPage == 0 ) {
                out.print("Previous");
            } else {
                out.print("<a href=\"limit.jsp?gotoPage="+(gotoPage - 1)+"\"><b>Previous</b></font></a>");
            }
           
            out.print("&nbsp;&nbsp;");
           
            if (!moreResult) {
                out.print("Next");
            } else {
                out.print("<a href=\"limit.jsp?gotoPage="+(gotoPage + 1)+"\"><b>Next</b></a>");
            }
           
            %>
            
        </form>
    </body>
</html>

Navigate Database using First, Previous, Next and Last Button

Here are some simple codes to display record within a page. User will click on the button to browse through the specific records.

<%@ page import="javax.sql.DataSource" %>
<%@ page import="java.sql.*" %>
<%@ page import="javax.naming.*" %>

<%!
static final String ctxstr = "java:comp/env/jdbc/navigationDB";
%>

<html>
    <head>
        <title>Database Navigation using Next and Previous button</title>
    </head>
    <body>
        <form>
        <h1>Database Navigation using Next and Previous link</h1>
           
            <%
            String fullname = "";
            String country = "";
           
            int counter = 0;

            //total result for each page
            int recordsPerPage = 5;

            //the default page
            int gotoPage = 1;
           
            //get page
            try {
                gotoPage = Integer.parseInt(request.getParameter("gotoPage"));
            } catch (NumberFormatException e ) {
                gotoPage = 1;
            }
           
            Connection conn = null;
            Statement  stat = null;
           
            try {
                //lookup for the JNDI datasource
                InitialContext ctx = new InitialContext();
                DataSource ds = (DataSource)ctx.lookup(ctxstr);
               
                //create the database connection
                conn = ds.getConnection();
                stat = conn.createStatement();
               
                //execute query
                String sql = "SELECT fullname, country FROM customer";
                ResultSet resultSet = stat.executeQuery(sql);
               
                //skip the records which not require within the page
                int row = (gotoPage - 1) * recordsPerPage + 1;
                for(int x=1; x<row; x++)
                    resultSet.next();
               
                out.println("<table border='1'><th>Name</th><th>Country</th>");
               
                //display only record which require within the page
                while ( (counter < recordsPerPage) && resultSet.next() ) {
                    fullname = (String)resultSet.getString("fullname");
                    country = (String)resultSet.getString("country");
                    out.println("<tr><td>"+fullname+"</td><td>"+country+"</td></tr>");
                    counter++;
                }

                out.println("</table>");
                out.println("<br>");
               
                //create next and previous link
                boolean moreResult = resultSet.next();
                if ( gotoPage == 1 ) {
                    out.print("Previous");
                } else {
                    out.print("<a href=\"index.jsp?gotoPage="+(gotoPage - 1)+"\"><b>Previous</b></font></a>");
                }
               
                out.print("&nbsp;&nbsp;");
               
                if (!moreResult) {
                    out.print("Next");
                } else {
                    out.print("<a href=\"index.jsp?gotoPage="+(gotoPage + 1)+"\"><b>Next</b></a>");
                }
               
                //close connection
                resultSet.close();
                stat.close();
                stat = null;
                conn.close();
                conn = null;
               
            } catch(SQLException e) {
                out.println("An error occured: " + e.toString());
                return;
            } finally{
                if(conn != null)
                    conn.close();
            }
            %>
           
        </form>
    </body>
</html>

If you are building the GUI application which similar to above functionalities, below are some quick ideas to what you can do to the ResultSet.

Next Record
if (!rs.isLast()) {
rs.next();
//retrieve the value
}

Previous Record
if (!rs.isFirst()) {
rs.previous();
//retrieve the value
}

Last Record
rs.last();

First Record
rs.first();

Goto Record
rs.absolute();

By keeping the ResultSet open, each code above can be define within the respective buttons’ ActionListener().

Navigate Database Record using Next and Previous Link

Here are some simple codes to display the fixed number of records within single page. Click on the previous and next link to browse through the records.

<%@ page import="javax.sql.DataSource" %>
<%@ page import="java.sql.*" %>
<%@ page import="javax.naming.*" %>

<%!
static final String ctxstr = "java:comp/env/jdbc/navigationDB";
%>

<html>
    <head>
        <title>Database Navigation using Next and Previous button</title>
    </head>
    <body>
        <form>
        <h1>Database Navigation using Next and Previous link</h1>
            
            <%
            String fullname = "";
            String country = "";
            
            int counter = 0;

            //total result for each page
            int recordsPerPage = 5;

            //the default page
            int gotoPage = 1;
            
            //get page 
            try {
                gotoPage = Integer.parseInt(request.getParameter("gotoPage"));
            } catch (NumberFormatException e ) {
                gotoPage = 1;
            }
            
            Connection conn = null;
            Statement  stat = null;
            
            try {
                //lookup for the JNDI datasource
                InitialContext ctx = new InitialContext();
                DataSource ds = (DataSource)ctx.lookup(ctxstr);
                
                //create the database connection
                conn = ds.getConnection();
                stat = conn.createStatement();
                
                //execute query
                String sql = "SELECT fullname, country FROM customer";
                ResultSet resultSet = stat.executeQuery(sql);
                
                //skip the records which not require within the page
                int row = (gotoPage - 1) * recordsPerPage + 1;
                for(int x=1; x<row; x++)
                    resultSet.next();
                
                out.println("<table border='1'><th>Name</th><th>Country</th>");
                
                //display only record which require within the page
                while ( (counter < recordsPerPage) && resultSet.next() ) {
                    fullname = (String)resultSet.getString("fullname");
                    country = (String)resultSet.getString("country");
                    out.println("<tr><td>"+fullname+"</td><td>"+country+"</td></tr>");
                    counter++;
                }

                out.println("</table>");
                out.println("<br>");
                
                //create next and previous link
                boolean moreResult = resultSet.next();
                if ( gotoPage == 1 ) {
                    out.print("Previous");
                } else {
                    out.print("<a href=\"index.jsp?gotoPage="+(gotoPage - 1)+"\"><b>Previous</b></font></a>");
                }
                
                out.print("&nbsp;&nbsp;");
                
                if (!moreResult) {
                    out.print("Next");
                } else {
                    out.print("<a href=\"index.jsp?gotoPage="+(gotoPage + 1)+"\"><b>Next</b></a>");
                }
                
                //close connection
                resultSet.close();
                stat.close();
                stat = null;
                conn.close();
                conn = null;
                
            } catch(SQLException e) {
                out.println("An error occured: " + e.toString());
                return;
            } finally{
                if(conn != null)
                    conn.close();
            }
            %>
            
        </form>
    </body>
</html>

Simple JNDI Accessing MySQL Datasource

Base on the previous post, I have used the struts-config.xml to define the datasource to access to the MySQL database. The datasource class is code within the Action class (controller layer) and map to the struts-config.xml.

In order to improve the programming pratice, the datasource can be created via JNDI and the java code should be sit within the model layer. As a result, business logic will separate from the controller layer and making the Action class without knowing any relation with business class at all.

Click here to read more...

Java MySQL - Mass Update Using Batch Updating

Some sample of the code to perform the mass update of database.

The ideas behind of this code is to improve the database performance by reduce the cost of query executions. This is refer to the INSERT, UPDATE and DELETE of any rows within the database.

Some developer may execute the query statement within the WHILE or FOR loop condition.

With batch updating, a set of SQL query can be writen and execute together to improve the performance.

Example of the code:

//Create the prepared statement
String updateSQL = “UPDATE customer SET country = ? WHERE id = ?”;
stat = conn.prepareStatement(updateSQL);

//Insert the country and hardcoded id.
for(int i = 0; i <= 5; i++){
stat.setString(1, “Malaysia”);
stat.setInt(2, i);
stat.addBatch();
}

//Execute the batch.
int [] updateCounts = stat.executeBatch();

Full code available here: http://leejeok.wordpress.com/2008/05/21/java-mysql-mass-update-using-batch-updating/