leejeok

The Remarkable Everyday

Archive for the ‘JDBC’ Category

Simple JNDI Accessing MySQL Datasource

Posted by leejeok on May 27, 2008

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.

 

I am using Apache Tomcat 5.x and so, I will define the datasource resource via JNDI within the context.xml.

 

<Context path=”/bookstore”>

<ResourceLink global=”jdbc/bookstoreDB” name=”jdbc/bookstoreDB” type=”javax.sql.DataSource”/>

</Context>

 

The java code to make the database connection accessing the JNDI would be look like this:

 

    Connection cn(){

        DataSource ds = null;

       

        try {

            Context ctx = new InitialContext();

            if ( ctx == null ) {

                throw new RuntimeException(“JNDI Context could not be found.”);

            }

            ds = (DataSource)ctx.lookup(“java:comp/env/jdbc/bookstorDB”);

           

            if ( ds == null ) {

                throw new RuntimeException(“DataSource could not be found.”);

            }

            return ds.getConnection();

            // Handle any JNDI errors

        } catch (NamingException ne) {

            throw new RuntimeException(“A JNDI error occured. ” + ne.getMessage());

           

        }catch(Exception e){

            System.err.println(“Boom – No Context”);

        }

        return null;

}

 

Alternative, you can use JSP Standard Tag Library (JSTL) without embedding any Java code. I am using JSTL SQL tag here and may look like this:

 

<%@ taglib prefix=”sql” uri=”http://java.sun.com/jsp/jstl/sql&#8221; %>

<%@ taglib prefix=”c” uri=”http://java.sun.com/jsp/jstl/core&#8221; %>

 

<sql:setDataSource dataSource=”jdbc/bookstoreDB” />

 

<sql:query var=”qryBooks” >

    SELECT bookID, bookTitle, bookAuthor, bookPublisher FROM book

</sql:query>

 

<table>

    <c:forEach var=”row” items=”${qryBooks.rows}”>

        <tr>

            <td>${row.bookID}</td>

            <td>${row.bookTitle}</td>

            <td>${row.bookAuthor}</td>

            <td>${row.bookPublisher}</td>

        </tr>

    </c:forEach>

</table>

Posted in JDBC, JSTL, MySQL, Struts | 1 Comment »

Java MySQL – Mass Update Using Batch Updating

Posted by leejeok on May 21, 2008

//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.

 

 

import java.sql.*;

 

public class Main {

   

    public static void main(String[] args) {

        Connection conn = null;

        PreparedStatement stat = null;

       

        int result = 0;

        int totalRowUpdate = 0;

        boolean updateAll = false;

        boolean updateEmpty = false;

        boolean updateFail = false;

       

        try {

            // Load the JDBC driver

            String driver = “org.gjt.mm.mysql.Driver”;

            Class.forName(driver);

           

            // Create a connection to the database

            String serverName = “localhost”;

            String database = “navigation”;

            String url = “jdbc:mysql://” + serverName +  “/” + database;

            String username = “someroot”;

            String password = “somepassword”;

           

            conn = DriverManager.getConnection(url, username, password);

           

            //Disabled the auto commit. By default, it is always true.

            conn.setAutoCommit(false);

            

            //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();

           

            //Validate update.

            //If the row value in the updateCounts array is 0 or greater,

            //the update was successfully executed.

            //A value of SUCCESS_NO_INFO means update was successfully executed

            //but MySQL server unable to determind the number of rows affected.

            //A value of EXECUTE_FAILED means MySQL server rejected the query for error.

            for (int i=1; i<updateCounts.length; i++) {

                if (updateCounts[i] >= 1) {

                    totalRowUpdate = totalRowUpdate + 1;

                    updateAll = true;

                } else if (updateCounts[i] == Statement.SUCCESS_NO_INFO) {

                    updateEmpty = true;

                } else if (updateCounts[i] == Statement.EXECUTE_FAILED) {

                    updateFail = true;

                }

            }

           

            //Error found. Rollback.

            //No error, Commit the batch

            if(updateEmpty || updateFail){

                conn.rollback();

            } else if (updateAll){

                conn.commit();

                System.out.println(“Total Row Update: “+totalRowUpdate);

            }

           

            //Once the batch is executed, it is a good pratice to clear the batch

            //before adding any new query.

            stat.clearBatch();

           

            // Throw BatchUpdateException for any queries fail.

            // Throw ClassNotFoundException for database driver error.

            // Throw SQLException for database error.

        } catch (BatchUpdateException  e) {

            System.err.println(e);

        } catch (ClassNotFoundException e) {

            System.err.println(e);

        } catch (SQLException e) {

            System.err.println(e);

        }

    }

}

Posted in Java, JDBC, MySQL | 1 Comment »