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



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




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










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”;



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



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




            //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){


            } else if (updateAll){


                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.



            // Throw BatchUpdateException for any queries fail.

            // Throw ClassNotFoundException for database driver error.

            // Throw SQLException for database error.

        } catch (BatchUpdateException  e) {


        } catch (ClassNotFoundException e) {


        } catch (SQLException e) {





Posted in Java, JDBC, MySQL | 1 Comment »