An Easy Way to Create JSP’s and MySQL Web Application

Dear readers, this time I would like to share about how to create a JSP’s and MySQL web application. This time we will create it using easy way, means that we wouldn’t create this web application by coding java classes that we might needed to support this application. We only create JSP, yes only JSP, and of course few configuration that I hope easy enough too.
Before we start, let’s prepare a database and a table that will be one of our material of this creation. Let’s create a database and name it as “test_jsp” and for the table, the structure of the table is looked like this sketch.

or we can copy and paste sql query below:

create database test_jsp;
use test_jsp;
CREATE TABLE `identitas` (
  `NO` int(10) NOT NULL AUTO_INCREMENT,
  `NAMA` varchar(100) NOT NULL,
  `ALAMAT` varchar(255) DEFAULT NULL,
  `NO_TELP` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`NO`));

The above Sql Query is a query to create a database named as test_jsp, and creating a table named as identitas with the following field described above on the image. The please fill up the table with few necessary data, in order to make the table containing data like the picture below.

By that, one of our material for this application is created.

To do this development, actually we need or we will use a library, this library has an ability to support the creation of a web application only using jsp. The needed library is Jakarta Taglibs library.

Jakarta Taglibs is one of the open source project of Jakarta Tag Libraries which supporting a creation of a web application using JSP and without coding java classes. With the Taglibs concept means that will use this library supported API by calling it using the JSP’s tags. Since this web application using a database, so we need a library called DBTags (database tags), so please download the library here http://jakarta.apache.org/site/downloads/downloads_taglibs.html, and we will need other library which have a function as a MySQL database driver which is called as mysql-connector-java.jar, please also download this library here http://dev.mysql.com/downloads/connector/j/

After finished downloading those libraries, then please extract the both files. And the next step is preparing a web application on an Application Server or Servlet Container, for this exercise we using Tomcat as a Servlet Container.

  1. Create a folder on a WebApps folder on tomcat installation directory. Let’s give a name “MySimpleJSP”. This folder will be a folder path on our web application.
  2. Inside the folder is MySimpleJSP again, we make two more folders named WEB-INF and libs.
  3. Copy mysql-connector-java.5.1.6.jar file (I use the connector 5.1.6 here) from the mysql connector library, and taglibs-dbtags.jar files from libraries Jakarta Tablibs which we downloaded earlier into the libs folder.
  4. And also copy the taglibs-dbtags.tld file from Jakarta Taglibs library that we have extracted onto WEB-INF folder.
  5. Create a file called web.xml and then save the file also in the WEB-INF folder, while the contents of the web.xml file write the following directions:
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://java.sun.com/xml/ns/javaee"
           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
           xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
          http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
           version="2.5">

      <jsp-config>
           <taglib>
               <taglib-uri>http://jakarta.apache.org/taglibs/dbtags</taglib-uri>
               <taglib-location>/WEB-INF/taglibs-dbtags.tld</taglib-location>
           </taglib>
       </jsp-config>
</web-app>

Until here, we have completed preparations for web-applications for our experiment this time. And the next step is to simply make jsp files that we need to be able to access the MySQL database we have made earlier, all jsp files which later we will create will be placed in MySimpleJSP folder or parallel with WEB-INF folder and the libs folder. Let’s create a jsp file.

The first JSP file we will create is get_identity.jsp, this file is a jsp file in charge of taking the data in the tables located on the identitas table on test_jsp database. Let’s look at the codes below get_identity.jsp file:

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@taglib uri="http://jakarta.apache.org/taglibs/dbtags" prefix="sql" %>
<html>
<head><title>My Simple JSP MYSQL Page</title></head>
<body>
<%-- Step 1) create a database connection --%>
<sql:connection id="connect">
    <sql:url>jdbc:mysql://localhost/test_jsp</sql:url>
    <sql:driver>com.mysql.jdbc.Driver</sql:driver>
    <sql:userId>root</sql:userId>
    <sql:password>admin99</sql:password>
</sql:connection>
<%--End Creating database Connection--%>

<%-- Step 2) Create a SQL query --%>
<sql:statement id="statement" conn="connect">
    <sql:query>
        SELECT * FROM identitas
    </sql:query>

    <table border="1">
        <tr>
            <th>NO</th>
            <th>NAMA</th>
            <th>ALAMAT</th>
            <th>NO TELP</th>
        </tr>
            <%--Step 2.1) Loop the result--%>
        <sql:resultSet id="data">
            <tr>
                <td><sql:getColumn position="1"/> </td>
                <td><sql:getColumn position="2"/> </td>
                <td><sql:getColumn position="3"/> </td>
                <td><sql:getColumn position="4"/> </td>
            </tr>
        </sql:resultSet>
    </table>
</sql:statement>

<%--Step 3) Close Database Connection--%>
<sql:closeConnection conn="connect"/>
</body>
</html>

Let’s make an attention the JSP’s code above:

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@taglib uri="http://jakarta.apache.org/taglibs/dbtags" prefix="sql" %>

On the second line we make a declaration tag that we shall use in this jsp file. Uri must be in line with taglib-uri which we declared in the web.xml file before. And if this web application run, the web.xml file have a responsibilities to find tags reference with sql prefix on taglibs-dbtags.tld file.

<%-- Step 1) create a database connection --%>
<sql:connection id="connect">
    <sql:url>jdbc:mysql://localhost/test_jsp</sql:url>
    <sql:driver>com.mysql.jdbc.Driver</sql:driver>
    <sql:userId>root</sql:userId>
    <sql:password>admin99</sql:password>
</sql:connection>
<%--End Creating database Connection--%>

While the above code block, we make declarations tags to connect to the mysql database that we created earlier.Please make attention each line above, the database is test_jsp then the driver is com.mysql.jdbc.Driver, and userid to connect to the mysql database is the root, and password to connect to the mysql database is admin99. My mysql database username is root and password is admin99, please adjust your database with the settings of each. Please make more attention in the code above, all of this configuration should be in block sql: connection

<%-- Step 2) Create a SQL query --%>
<sql:statement id="statement" conn="connect">
    <sql:query>
        SELECT * FROM identitas
    </sql:query>

    <table border="1">
        <tr>
            <th>NO</th>
            <th>NAMA</th>
            <th>ALAMAT</th>
            <th>NO TELP</th>
        </tr>
            <%--Step 2.1) Loop the result--%>
        <sql:resultSet id="data">
            <tr>
                <td><sql:getColumn position="1"/> </td>
                <td><sql:getColumn position="2"/> </td>
                <td><sql:getColumn position="3"/> </td>
                <td><sql:getColumn position="4"/> </td>
            </tr>
        </sql:resultSet>
    </table>
</sql:statement>

Then look at the rows of the following code, we create a block of tag sql:statement. Where in this block we write the block tag sql:query and tag sql:ResultSet, and in the block sql:ResultSet, we extract the data from the database to be displayed to the web page using tag sql:getColumn. If we try to understand the tags, the description may look like this: tag sql:query is a tag to write sql query which will be executed by the application. Then tag sql:ResultSet is a tag where the captured data from the database placed in the form to an array collection. And the last is the tag sql:getColumn is a way to extract the collection array and then sorted based on the basic view of our application web pages.

<%--Step 3) Close Database Connection--%>
<sql:closeConnection conn="connect"/>

And finally the last in this code is, that we then terminate the connection to our MySQL database.

Then after that, we can test this MySimpleJSP application by running our Tomcat and then call the get_identity.jsp files on your favorite browser pointed to the address: http://localhost:8000/MySimpleJSP/get_identity.jsp (I have configured tomcat to port 8000, please replace with the appropriate port in your tomcat configuration). If successful, it should display the web page is like this

With the appearance of the data into a web page of our JSP application, then we can describe that this exercise is successful done. For further step you can create more jsp pages, such as a jsp file to insert the data into the table, etc.

I Hope this usefull.

Thanks

Josescalia

Using Spring Framework on Creating DAO Layer

On J2EE or J2SE project development, sometimes there’s one block which have a function to manage everything related with the database, result fetching of supplied queries was managed on this block. With this type of design, we’re easier to track if there’s an error on our java application.
For Java Developer which familiar to use database as a part of the application, doesn’t have any difficulty to create a block which have a function as a database access, and then place the block inside the architecture of the application.

When creating this database access block, there’s some way that we can use, one of them is using Spring Framework. Yes, now we will try to use Spring Framework to access database, by creating database access or more familiar as DAO Layer. And for this try-it-out we will use MySQL Database (Again….? Oh God…). First thing we have to do to try-this-out is create a table on a database on MySQL Database and complete this table with the data needed. We will name this table as web_account and containing about 10 or 20 data inside. For the field of this table, let’s just create with our desire. Or maybe we can copy-paste this below SQL Code:

CREATE TABLE `web_account` (
  `USERNAME` varchar(30) NOT NULL DEFAULT '',
  `PASSWORD` varchar(30) NOT NULL DEFAULT '',
  `CREATE_TIME` datetime DEFAULT NULL,
  `CREATE_USER` varchar(30) DEFAULT NULL,
  `UPDATE_TIME` datetime DEFAULT NULL,
  `UPDATE_USER` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`USERNAME`)
);

INSERT INTO `web_account` VALUES ('admin','adminya33','2009-03-15 08:32:21','admin','2009-03-15 08:32:21','admin'),('andre','andre456','2009-03-15 08:34:53','admin','2009-03-15 08:34:53','admin'),('fitri','fitri54','2009-03-15 08:33:22','admin','2009-03-15 08:33:22','admin'),('ganteng','ganteng99','2009-03-15 08:34:21','admin','2009-03-15 08:34:21','admin'),('gerry','gerry87','2009-03-15 08:33:41','admin','2009-03-15 08:33:41','admin'),('gue','guelagi','2009-03-15 08:33:56','admin','2009-03-15 08:33:56','admin'),('hebat','hebatlah','2009-03-15 08:34:36','admin','2009-03-15 08:34:36','admin'),('heru','heur334','2009-03-15 08:32:54','admin','2009-03-15 08:32:54','admin'),('joe','joe77','2009-03-15 08:32:39','admin','2009-03-15 08:32:39','admin'),('kasep','sekap25','2009-03-15 08:33:08','admin','2009-03-15 08:33:08','admin'),('linux','linux09','2009-03-15 08:35:22','admin','2009-03-15 08:35:22','admin'),('mojo','monk123','2009-03-15 08:31:50','admin','2009-03-15 08:31:50','admin');

From the structure of the table above, we have to create a Java Class which has class properties that can represent the fields of the table above. The purpose of this class creation is this class will be the container of the data table. On usage of java code, this class will be an object variable that can be accessed as Array of Object or Single Object.

We will create this class by using simple POJO (Plain Old Java Object), below is the source code of this class:

package org.mojo.spring.entity;

import java.util.Date;

/**
 * Created by IntelliJ IDEA.
 * User: Mojo
 * Date: Mar 15, 2009
 * Time: 8:45:10 AM
 * To change this template use File | Settings | File Templates.
 */
public class WebAccount {
    private String userName;
    private String password;
    private Date createDate;
    private String createUser;
    private Date updateDate;
    private String updateUser;

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public Date getCreateDate() {
        return createDate;
    }

    public void setCreateDate(Date createDate) {
        this.createDate = createDate;
    }

    public String getCreateUser() {
        return createUser;
    }

    public void setCreateUser(String createUser) {
        this.createUser = createUser;
    }

    public Date getUpdateDate() {
        return updateDate;
    }

    public void setUpdateDate(Date updateDate) {
        this.updateDate = updateDate;
    }

    public String getUpdateUser() {
        return updateUser;
    }

    public void setUpdateUser(String updateUser) {
        this.updateUser = updateUser;
    }

    public String toString() {
        return "WebAccount{" +
                "userName='" + userName + '\'' +
                ", password='" + password + '\'' +
                ", createDate=" + createDate +
                ", createUser='" + createUser + '\'' +
                ", updateDate=" + updateDate +
                ", updateUser='" + updateUser + '\'' +
                '}';
    }
}

And now we are ready to create a DAO Layer using Spring Framework, don’t forget to download the needed libraries such as Spring Library, MySQL Connector library, and common-logging library, because these libraries is needed to follow this try-it-out. The common-logging library has to include too in this try-it-out, because the Spring Framework have a very high dependencies to this library. And the MySQL Connector library is needed too, because we use MySQL Database on this try-it-out (Again… :P).

For the DAO Layer that we will create below, we just limit it on fetching how many rows data on the table (SELECT COUNT(1) ) and fetching all data (SELECT *) only. The others method such as insert, update, or delete, we can add it later. For the select count(1) method the run-down scenario is: this method will have a return data type as integer, while the select * method will have a return data type as a list. Let’s see below source code:

package org.mojo.spring.dao.JdbcDao;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.mojo.spring.entity.WebAccount;

import javax.sql.DataSource;
import java.util.List;
import java.util.ArrayList;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.ResultSet;

/**
 * Created by IntelliJ IDEA.
 * User: Mojo
 * Date: Mar 15, 2009
 * Time: 8:46:57 AM
 * To change this template use File | Settings | File Templates.
 */
public class WebAccountJDBCDAO extends JdbcDaoSupport {
    private DataSource ds;
    private JdbcTemplate tpl;

    public int getNumOfWebAccount() {
        logger.info("getNumOfWebAccount");
        int iRes = 0;
        ds = getDataSource();
        tpl = new JdbcTemplate(ds);
        String sQuery = "SELECT COUNT(1) from web_account";
        try {
            iRes = tpl.queryForInt(sQuery);
            logger.info("Result of getNumOfWebAccount : " + iRes);
        } catch (Exception e) {
            logger.info("Exception in getNumOfWebAccount()" + e.getMessage());
        }
        return iRes;
    }

    public List<WebAccount> getWebAccountList(boolean flagLimit, int limit) {
        final List<WebAccount> result = new ArrayList();
        final int iLimit = limit;
        ds = getDataSource();
        JdbcTemplate tpl = new JdbcTemplate(ds);
        String sQuery = "SELECT * FROM rss_feeder";

         if (flagLimit) {
            sQuery += " LIMIT ?";
        }

        class PreparedStatementHandler implements PreparedStatementSetter {
            public void setValues(PreparedStatement ps) throws SQLException {
                int n = 0;
                ps.setInt(++n, iLimit);
            }
        }

        class ServiceHandler implements RowCallbackHandler {
            public void processRow(ResultSet rs) throws SQLException {
                WebAccount wa = new WebAccount();
                wa.setUserName(rs.getString("WEB_ACCOUNT"));
                wa.setPassword(rs.getString("PASSWORD"));
                wa.setCreateDate(rs.getDate("CREATE_DATE"));
                wa.setCreateUser(rs.getString("CREATE_USER"));
                wa.setUpdateDate(rs.getDate("UPDATE_DATE"));
                wa.setUpdateUser(rs.getString("UPDATE_USER"));
                result.add(wa);
            }
        }

        if (flagLimit) {
            tpl.query(sQuery, new PreparedStatementHandler(), new ServiceHandler());
        } else {
            tpl.query(sQuery, new ServiceHandler());
        }
        return result;
    }
}

Let’s discuss above codes. On the top of the source code, there’s a declaration where this class is located, and the next lines, we just import the needed class of Spring Framework library and other Java default library.

package org.mojo.spring.dao.JdbcDao;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.mojo.spring.entity.WebAccount;

import javax.sql.DataSource;
import java.util.List;
import java.util.ArrayList;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.ResultSet;
………

We have named this class as WebAccountJDBDDAO class, marked by the class declaration on the next line after comment lines (I’m Using IntelliJidea as my favorite IDE). After the class declaration, we create 2 variables, ds as DataSource type and tpl as JdbcTemplate type.

………
public class WebAccountJDBCDAO extends JdbcDaoSupport {
    private DataSource ds;
    private JdbcTemplate tpl;
………

DataSources is a type of object that we picked up from javax.sql package, while the JdbcTemplate is a type of object that we picked up form Spring package, located on org.springframework.jdbc.core.JdbcTemplate. And then, let’s take look one by one the methods that exist on the codes.

Method getNumOfWebAccount:

……
 public int getNumOfWebAccount() {
        logger.info("getNumOfWebAccount");
        int iRes = 0;
        ds = getDataSource();
        tpl = new JdbcTemplate(ds);
        String sQuery = "SELECT COUNT(1) from web_account";
        try {
            iRes = tpl.queryForInt(sQuery);
            logger.info("Result of getNumOfWebAccount : " + iRes);
        } catch (Exception e) {
            logger.info("Exception in getNumOfWebAccount()" + e.getMessage());
        }
        return iRes;
    }
……

On this method we can see, the query that will be execute is only simple query which is “select count(1) from web_account”. If we execute this query on MySQL Console, the result of this query is the number of how much rows are exists on web_account table. On this method we put the result of the query into a variable iRes, this iRes variable has a integer as it’s data type. If we want to arrange the steps of this method, the steps will be seen as:

  1. Create integer type variable and name it as iRes.

  2. Initialize the ds variable by calling getDataSource interface.

  3. Create new JdbcTemplate object and the parameter is ds.

  4. Create a String variable, named as sQuery and directly fill the string by the MySQL query.

  5. Execute the query on try-catch block and then put the result of the query into iRes variable.

  6. Crate a return statement of the methods.

While the lines containing logger, is our way to mark the process flow, so if there’s any error occurred on the codes we can trace it easily.

And then let’s discuss the 2nd method which is getWebAccountList method. On this method, we create 2 inner class, the purpose of this inner class creation is to make our codes look more orderly:

…….
       class PreparedStatementHandler implements PreparedStatementSetter {
            public void setValues(PreparedStatement ps) throws SQLException {
                int n = 0;
                ps.setInt(++n, iLimit);
            }
        }

     class ServiceHandler implements RowCallbackHandler {
            public void processRow(ResultSet rs) throws SQLException {
                WebAccount wa = new WebAccount();
                wa.setUserName(rs.getString("USERNAME"));
                wa.setPassword(rs.getString("PASSWORD"));
                wa.setCreateDate(rs.getDate("CREATE_TIME"));
                wa.setCreateUser(rs.getString("CREATE_USER"));
                wa.setUpdateDate(rs.getDate("UPDATE_TIME"));
                wa.setUpdateUser(rs.getString("UPDATE_USER"));
                result.add(wa);
            }
        }
…….

If we take a look on the codes of both inner classes, each of this inner class handled two important functions. The function on the first inner class handled PrepareStatement, this class implements PrepareStatementSetter belongs to Spring Framework. And the 2nd inner class handled the localization of query result onto WebAccount object variable, this inner class implements RowcallbackHandler belongs to Spring Framework also.

On the 1st inner class we override setValues methods that owned by the PreparedStatementHandler class as a requirement of implementation. And the 2nd class we also have to override processRow method as a requirement of RowCallBackHandler class implementation. And then let’s take a look at the main code of the getWebAccountList method that we’ve already created before.

……
 public List<WebAccount> getWebAccountList(boolean flagLimit, int limit) {
        final List<WebAccount> result = new ArrayList();
        final int iLimit = limit;
        ds = getDataSource();
        JdbcTemplate tpl = new JdbcTemplate(ds);
        String sQuery = "SELECT * FROM rss_feeder";

         if (flagLimit) {
            sQuery += " LIMIT ?";
        }
……..
……..
……..
        if (flagLimit) {
            tpl.query(sQuery, new PreparedStatementHandler(), new ServiceHandler());
        } else {
            tpl.query(sQuery, new ServiceHandler());
        }
        return result;
    }
……

On the main code of the above method we have return value as a list and 2 parameters which is flagLimit as boolean type and limit as integer type. The steps of this method arrangement is same with the first method that we have created before on getNumOfWebAccount method, just because that we have 2 parameters on this method, so let’s create a little programming logic here, which is on branching lines. On the branching lines we create a logic if the value on flagLimit is true then the query will be added by “LIMIT ?” so the query will be “SELECT * FROM web_account limit ?”, and if the value of flagLimit is false then the query will not be change, but it still same just like the first initialization.

The dots that exist in the middle of the main codes are the codes of inner classes that we have discussed before. And then we have arrived to the code where the lines of query execute. In this lines there’s also a branch that will decide whether the code will use the inner PreparedStatementHandler class of not, the decision of this usage is depends on flagLimit variable value, go head, do your own analyze the branching codes. And the end of this method is the existence of the return statement declared as a list that represented by the result variable. That’s it, that’s how we use Spring Framework on creating DAO Layer. And the next step are we will try to use the DAO Layer in the Java Consol Application.

In order to use this DAO Layer, we have to make a Spring Framework configuration file. Ok, let’s just create this file, let’s name it as “applicationContext.xml” file, just like another article posted before which using Spring Framework also. We will place this configuration file on a config folder which is a line with the src folder on this try-it out project. Below is the source code of Spring Configuration file.

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans-2.0.dtd">
<beans>
    <!--Data Source-->
    <bean id="ds" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost/java_spring"/>
        <property name="username" value="root"/>
        <property name="password" value="root99"/>
    </bean>

    <bean id="webAccountDAO" class="org.mojo.spring.dao.JdbcDao.WebAccountJdbcDAO">
        <property name="dataSource" ref="ds"/>
    </bean>
</beans>

Let’s take a look of this configuration file, the first bean that we have to create is ds, this bean is a bean which have a function to access MySQL Database, this bean use the class that belongs to Spring Framework which is DriverManagerDataSource class. And then, just because this is the Spring access block to connect to database, so we have to supply the property that related with the connection to database creation, such as username, url, password, dan driverClassName of the database that we will use and it’s property given is depends on the setting of our MySQL database settings.

And then the 2nd bean is a bean that will represent DAO Layer that we have created above. The property that has to be filled up by this bean is dataSource property, while the reference of this bean property is ds bean that we have cerated before.

And the usage of this DAO Layer on Java Console Application, shown as the codes below:

package org.mojo.spring.application;

import org.mojo.spring.dao.JdbcDao.WebAccountJdbcDAO;
import org.mojo.spring.entity.WebAccount;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.FileSystemXmlApplicationContext;

import java.util.List;

/**
 * Created by IntelliJ IDEA.
 * User: Mojo
 * Date: Mar 15, 2009
 * Time: 11:05:55 AM
 * To change this template use File | Settings | File Templates.
 */
public class SpringReadDatabase {

    private WebAccountJdbcDAO dao = null;
    public static void main(String[] args) {
        //call SpringConfiguration File
        ApplicationContext ctx = new FileSystemXmlApplicationContext("config/applicationContext.xml");
        SpringReadDatabase app = new SpringReadDatabase();

        //call Desired bean
        app.dao = (WebAccountJdbcDAO) ctx.getBean("webAccountDAO");

        //declare Variable
        WebAccount[] waList = null;

        //getNumOfWebAccount
        int i = app.dao.getNumOfWebAccount();
        System.out.println("Num Rows : " + i);

        //getWebAccountList
        List<WebAccount> list = app.dao.getWebAccountList(false,0);

        //convert List to ObjectArray
        if (list.size() > 0) {
            waList = new WebAccount[list.size()];
            for (short j = 0; j < list.size(); j++) {
                waList[j] = list.get(j);
            }
        }

        //iterate ObjectArray to PrintOut to console
        for (int j = 0; j < waList.length; j++) {
            WebAccount webAccount = waList[j];
            System.out.println("Name : " + webAccount.getUserName());
            System.out.println("Password : " + webAccount.getPassword());
            System.out.println("FullWebAccount : " + webAccount);
        }

    }

    public void setDao(WebAccountJdbcDAO dao) {
        this.dao = dao;
    }
}

That’s it, that’s how we use the DAO Layer that we have created before by using Spring Framework in Java Console Application. To discuss this java console application, please do your own analyzing in order to get more understanding of the codes that we have created.

 

I hope this will helpful.

Menteng, March 15th 2009

 

josescalia

Accessing Property File Using Spring Framework

On the previous post in this blog using Indonesian language, we tried to use property file to access database in order to avoid hardcode mechanism, and the accessing of the property file by using a class named ConfigProperties, now we will try to access property file without using those class but we will try to use Spring Framework to access property file.

As we knew, the existence of Spring Framework on Java technology is really-really help us the Java Developer, the modularity of this framework has made the enterprise java project development is easier to rebuild or modify.

We will not discuss much more about the Spring Framework here, we only will try to use the Spring Framework one of this framework feature to access property file. And of course the values of the property file to be used on our application chain later.

Let’s go directly to try this out. On Spring Framework, the usage of bean became a must, that’s why we will create an object as a representation of a bean, the scenario that we will make is every item that exist on property file will become a property of the object that that we have created. Let’s make the property file just like below:

application.properties file:

application.name=mojo
application.password=mojo123
application.url=http://localhost:8080/test_doang/test.jsp

We will put this file on config folder, a line with the src folder, just like below picture shown:

foldermacam2spring

With the item of the property file, we will create a simple object of Java pojo which will represent each item that exist on the property file above. The source code of this object is below:

PropertyHolder.java file:

package org.mojo.spring.entity;
/**
 * Created by IntelliJ IDEA.
 * User: Mojo
 * Date: Mar 9, 2009
 * Time: 7:04:46 PM
 * To change this template use File | Settings | File Templates.
 */
public class PropertyHolder {
    private String name;
    private String password;
    private String url;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public String toString() {
        return "PropertyHolder{" +
                "name='" + name + '\'' +
                ", password='" + password + '\'' +
                ", url='" + url + '\'' +
                '}';
    }
}

 

 

 

 

On above source code, we create a file named PropertyHolder.java. On this file we only create 3 property of the object, because there’s only 3 item on the property file. On the usage, each property of this object will hold values that exist on the property file.

In order to make each item on the property file can be inject into each object property, we have to define the object on Spring configuration file, so when the application run, this configuration will be called at first line and finally each item on the property file can be injected into each property of the PropertyHolder object that we have created before. Below is the definition of the object that we define as a bean on spring configuration file, this file will also put on same folder with the property file location which is on config folder.

applicationContext-app.xml file:

 

 

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans-2.0.dtd">
<beans>

    <bean id="propertyConfigurer"          class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
    <property name="location" value="config/application.properties"/>
   </bean>

       <!-- Local Data Holder -->
    <bean id="propertyHolder" class="org.mojo.spring.entity.PropertyHolder">
        <property name="name" value="${application.name}"/>
        <property name="password" value="${application.password}"/>
        <property name="url" value="${application.url}"/>
    </bean>
</beans>

Basically, when an application that using Spring Framework start running, then all the bean that already configured on Spring configuration file will also called one by one, even maybe one bean will depends on another bean. On the defined bean above, we can see that we have defined two beans there, the first bean have an id called “propertyConfigurer”, this bean have a function to give information to the Spring Framework the location of the property file. While the other bean given an id as “propertyHolder”, this bean is a map of the object that we have created before. Let’s make an attention to the defined class of the second bean, and then each property that defined on the block of this defined bean, this is how we map each item of the property file to each property on the object.

Actually, this is the main focus of our try-it-out today, here’s how we access each items on the property file using Spring Framework. And if when we want to use the value of the property file items, all we need is just called the PropertyHolder object only, a line before we set the PropertyHolder object for sure. The usage example is just like below:

package org.mojo.spring.application;

import org.mojo.spring.entity.PropertyHolder;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.FileSystemXmlApplicationContext;

/**
 * Created by IntelliJ IDEA.
 * User: Mojo
 * Date: Mar 9, 2009
 * Time: 7:06:35 PM
 * To change this template use File | Settings | File Templates.
 */
public class TestReadProperty {
    private PropertyHolder holder =null;

    public void setHolder(PropertyHolder holder) {
        this.holder = holder;
    }

    public static void main(String[] args) {
        ApplicationContext context    = new FileSystemXmlApplicationContext("config/applicationContext-app.xml");
        TestReadProperty app = new TestReadProperty();
        app.setHolder((PropertyHolder) context.getBean("propertyHolder"));

        System.out.println("Name : " + app.holder.getName());
        System.out.println("Password : " + app.holder.getPassword());
        System.out.println("Url : " + app.holder.getUrl());
        System.out.println("full : " + app.holder);
    }
}

On the source code of usage example above, we can describe it just like this:

At first PropertyHolder object defined as a private variable named as holder. Then there’s a method which have a function as a setter of the holder object. And then on the main method of this sample program, we have defined an ApplicationContext as an object, this context called the Spring Framework configuration file, and then we have to define this program as an object too, in order to make all the property of the object can be called on the main method.

The next step is, set the PropertyHolder object by calling setter method of the PropertyHolder that we have made before on this example program. When this method called, then automatically Spring Framework will fill up the property of PropertyHolder object with the value of each item that exists on the property file. And the last step is we tried to print-out the value of object property that owned by PropertyHolder class to console. And don’t forget to import all the necessary class and library that needed by this example application on the top line of this example program as we seen above.

That’s it, our try-it-out today, a little try-out that using one the feature of Spring Framework that has a lot and useful feature that we can use on our development project. And if you want to try-it too, don’t forget to download the library of Spring Framework and other libraries that become dependency libraries for the Spring Framework it self

Have a nice try ….!!!

 

 

I Hope this will helpfull

Menteng, March 10, 2009

 

Josescalia

Accessing Ms Access Database with Java

Microsoft Access is a software database which still has been used until now. This database is very light, so this is an advantage for it’s user to chose this type of database in order to save the data. Microsoft Access is also come with a lot and usable feature. Starting from creating table, form, queries, report, and even the macros, everything became simple when we want a do it on Microsoft Access, and its typically often that this Microsoft Access is used by everyone to make a database application where the data will be save on its self.

We will not discuss a lot about what Microsoft Access can do here. On this time we will try to accessing a database that created using Microsoft Access with our beloved programming language which is Java.

Now, let’s arrange a scenario to discuss this topic:

  1. Create a database file named Java_MsAccess using Microsoft Access.

  2. Create a table on that database which contains fields:

  • Field name: CustomerID, type Number (single).

  • Field name: CustomerName, type Text (50).

  • Field name: CustomerAddress, type Text (250).

  • Field name: CustomerPhone, type Text (20).

  1. Fill up the table with necessary data.

  2. Create a DSN Data source DSN for this database.

  3. Create a Java Application for accessing this database using the Data source.

For the step 1 until the step 4, we will attach a screen shot only here, because I believe that we can do these steps without written reference here.

Okay, for the 1st step, below is the screen capture:

createmsaccessdb

And for the 2nd step, below is screen capture:

createmsaccessdb

 

And for the step 3rd, this is how we do it:

fillupmsaccesstable

And for the step 4th, okay for this step we will explain a little bit, so we can understand how to create a data source on windows platform for this database.

  • Click start Control Panel Administrative Tools Data Sources(ODBC)

  • And then on ODBC Database Administrator window, click Add button and then choose Driver do Microsoft Access (*.mdb), and then click finish button.

  • And then on ODBC Microsoft Access Setup window, give a name for this database JavaMsAccess, and then choose the database file by clicking select button, and find the database file that we have crated before. This is the image looks like:

createodbcdsn

And the DSN data source named by JavaMsAccess created successfully. Now we arrive to the step that we are cannot be patient to wait which is creating the Java application. Okay, here’s the process flow of our Java Application looks like:

  1. Choose and initialize the driver for this ODBC data source.

  2. Create a database connection to this ODBC data source.

  3. Create a Statement for the SQL Queries.

  4. Pick up the result of Query Statement executiom onto ResultSet

  5. Printout the ResultSet.

From these scenarios, let’s implement it on to Java Code. Take a look this Java source code below:

package org.mojo.blog.Java_depth.aboutMsOffice;

import Java.sql.*;

/**
* Created by IntelliJ IDEA.<br>
* User: Mojo<br>
* Date: 08 Feb 09<br>
* Time: 22:30:08<p>
*/
public class MsAccessJava {
private static String driverName = "sun.jdbc.odbc.JdbcOdbcDriver";
private static String url = "jdbc:odbc:JavaMsAccess";
 public static void main(String[] args) {
     String sql ="SELECT * from tblCustomer";
       try {
          Class.forName(driverName);
          Connection conn = DriverManager.getConnection(url);
          System.out.println("Connection Established..");
          Statement stmt = conn.createStatement();
          ResultSet rs = stmt.executeQuery(sql);
          while (rs.next()){
             System.out.println("+------------- Detail Customer ----------------------+");
             System.out.println("Customer ID : "+ rs.getInt(1));
             System.out.println("Customer Name : "+ rs.getString(2));
             System.out.println("Customer Address : "+ rs.getString(3));
             System.out.println("Customer Phone : "+ rs.getString(4));
             System.out.println("+----------------------------------------------------+");
             System.out.println("\n");
         }
       } catch (SQLException e) {
          e.printStackTrace();
       } catch (ClassNotFoundException e) {
          e.printStackTrace();
     }
  }
}

Let’s discuss line per line the above code.

The first line is a line where this Java file located on its package. While the 3rd line is a line where all the classes of Java.sql library has imported. And then we can see inside the program that there are two private static string variable defined which is:

………..
   private static String driverName = "sun.jdbc.odbc.JdbcOdbcDriver";
   private static String url = "jdbc:odbc:JavaMsAccess";
………..

The first variable is a driver name that we will use it later to accessing ODBC data source, while the second variable is an url of the data source that we would like to access, just remember this data source name have to equals with the data source that we have created before which is: JavaMsAccess.

And then let’s take a look the codes inside the main method. The first line on this main method is a line where we define a SQL query which we will use to access these Microsoft Access database. And then we create a try-catch block, this block is the block which all main process of the accessing process is happen. First in this block we initialize the driver that we have defined in the header of the program. And then we create a database connection and the print out the result of it’s connection.

…….
   Class.forName(driverName);
   Connection conn = DriverManager.getConnection(url);
   System.out.println("Connection Established..");
……….

The continuous steps are, create a statement, then execute the SQL Statement using the SQL query that we have defined before.

…….
     Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery(sql);
……….

And the print out the result by loops the rs variable, which these ResultSet variable values had filled up before.

…….
   while (rs.next()){
        System.out.println("+----Detail Customer ------------------+");
        System.out.println("Customer ID : "+ rs.getInt(1));
        System.out.println("Customer Name : "+ rs.getString(2));
        System.out.println("Customer Address : "+ rs.getString(3));
        System.out.println("Customer Phone : "+ rs.getString(4));
        System.out.println("+--------------------------------------+");
        System.out.println("\n");
   }
…….

And then we add some catch statement to prevent error appear on runtime, and this catch is also can be use to trace the problem when error occured that can make our application cannot run properly.

If the program executed, and then the result will show just like image below – i have executed it on my favourite IDE, IntelliJidea 7.0.4

executeodbc_java

That’s it, that’s all our try out has finished. Please try and modify for your own need and your own desire.


Hope this will usefull

Menteng, 09th February, 2009


josescalia

Kata “DESC” dalam database MySQL

Database dalam sebuah aplikasi adalah salah satu alternatif media penyimpanan yang bisa kita gunakan. Pemilihan database yang tepat untuk aplikasi yang kita buat menjadi sedemikian penting mengingat adanya kelemahan dan kelebihan masing-masing database yang ada. Baik database open source maupun database yang komersil pasti ada kelebihan dan kekurangannya masing-masing.

Kali ini kita akan membahas sesuatu tentang database MySQL yang kebetulan adalah database yang biasa saya pilih sebagai partner dalam aplikasi yang saya buat. Saya sendiri tidak begitu tahu, apakah ini sebuah bugs atau memang preserve keyword yang tidak boleh dilakukan dalam database MySQL.

Kita langung saja. Buatlah suatu database dalam MySQL dengan nama test dengan query seperti berikut ini:

create database test_doang;
use test_doang;

Dalam barisan query diatas kita mencoba membuat sebuah database dengan nama test_doang, dan kemudian kira gunakan database test_doang tersebut. Kemudian kita akan membuat sebuah tabel dalam database tersebut, kita beri nama misalnya table_pengertian dengan 2 buah field dalam table tersebut. Yaitu :

  1. Field ISTILAH dengan tipe data varchar, dengan jumlah karakter maksimal 100 karakter.

  2. Field DESC dengan tipe data varchar juga, dan dengan jumlah karakter 255 karakter.

Atau bisa juga anda copy paste query berikut ini:

CREATE TABLE `table_pengertian` (
`ISTILAH` VARCHAR( 100 ) NOT NULL ,
`DESC` VARCHAR( 255 ) NOT NULL
);

Jika sudah, maka kita akan mempunyai sebuah tabel dalam database test_doang tersebut. Kemudian kita akan mencoba menambah suatu record dalam tabel tersebuat misalnya, untuk field ISTILAH kita kasih satu kata CPU dan untuk DESC kita isi field tersebut dengan data “Sebuah mesin yang memproses data”, atau bisa juga kita copy paste query berikut ini:

insert into table_pengertian values('CPU','Sebuah mesin yang memproses data');

Setelah itu kemudian kita lihat isi dalam tabel table_pengertian tersebut jika anda lihat seperti gambar dibawah ini, berarti semuanya normal-normal saja kan ?

Jika ya, kita akan melanjutkan langkan berikutnya, yaitu kita kembali memasukkan suatu data kedalam tabel table_pengertian tersebut, kali ini datanya adalah untuk kolom ISTILAH kita isi dengan data “Monitor” dan untuk kolom DESC kita isi dengan data “Media yang dipakai untuk menampilkan data”, namun kali ini kita akan mengisi data tersebut dengan cara menyebutkan nama kolom / Field dalam query, seperti contoh berikut :

iinsert into table_pengertian(ISTILAH,DESC) values('Monitor', 'Media yang dipakai untuk menampilkan data');

Jika anda eksekusi query tersebut, apa yang terjadi? Error..? Ok kita akan coba dengan query lain untuk memasukkan data tersebut. Jika anda menghilangkan penyebutan kolom pada query diatas tadi, maka data bisa masuk, sementara dalam MySQL sangat dianjurkan untuk menyebutkan nama field yang ingin dimasukkan data, meski dengan tanpa menyebutkan nama field query juga bisa dieksekusi.

Ok, sekarang kita akan coba query lain selain query INSERT. Yaitu query UPDATE. Sekarang kita coba update data diatas tadi dengan query seperti dibawah ini:

update table_pengertian set DESC='Sebuah mesin yang memproses data-data yang dimasukkan' where ISTILAH='CPU';

Apa yang terjadi? Error..lagi..? hmm, apakah query yang kita bikin salah? Silahkan anda coba-coba lagi? Tapi menurut saya query itu tidak ada yang salah, query sudah mengikuti petunjuk-petunjuk manual yang memang dianjurkan oleh MySQL sendiri, sepertinya ada yang salah dengan MySQL-nya…

Ok kita coba lagi, kali ini kita akan mencoba mengganti nama field dalam table_pengertian tersebut. Field yang akan kita ganti adalah nama field DESC, kita akan menggantinya dengan nama ISTILAH_DESC atau apa sajalah terserah anda yang penting tidak memakain kata DESC sebagai nama dari field tersebut. Query untuk merubah nama field tersebut seperti dibawah ini:

ALTER TABLE `table_pengertian` CHANGE `DESC` `ISTILAH_DESC` VARCHAR( 255 ) ;

Setelah nama field tersebut berganti sekarang kita coba eksekusi query yang diatas tadi mengalami error, tentunya dengan merubah nama field DESC yang disebutkan dalam query dengan nama field yang sudah kita ganti.

Apa yang terjadi? Apakah semua query diatas yang tadinya error bisa dengan sukses di eksekusi dengan baik tanpa error yang keluar? Apa yang bisa kita ambil dari percobaan kecil ini?

Hmm.. ternyata MySQL tidak mengijinkan kita memakai kata DESC untuk kita jadikan sebuah nama field dalam table. Kita bebas memakai nama field dalam table asal jangan memakai kata DESC, terbukti dari percobaan diatas, ada beberapa error yang terjadi jika kita menggunakan kata DESC sebagai sebuah nama field dalam sebuah tabel.

Sekali lagi, saya sendiri tidak tahu apakah ini sebuah bugs dalam MySQL atau memang sudah diumumkan dalam release MySQL versi yang saya pakai, oh ya versi MySQL yang saya pakai adalah versi 5.0.15 yang dibundel dengan software XAMPP. Yang jelas kita dapat sebuah pengetahuan baru tentang hal ini.

Silahkan anda mencari kembali pengetahuan-pengetahuan lainnya tentang database yang gratis dan open source ini.

Semoga Bermanfaat

Josescalia

Menggunakan Property File dalam Java untuk Koneksi Database

Keputusan melakukan hardcode (menulis suatu nilai properti) langsung dalam aplikasi kita bukanlah sebuah keputusan yang bijak. Misalnya properti-properti yang berhubungan dengan database. Ketika suatu saat kita mencoba mengganti user atau password dalam database, maka secara otomatis kita juga harus membongkar kembali program kita untuk memperbaiki nilai user atau password. Sungguh suatu yang lumayan merepotkan dan makan waktu.

Di sini saya akan mencoba membuat sebuah program java yang menggunakan properti file sebagai settingan untuk koneksi ke database. Dalam properti file ini nilai-nilai yang mudah berubah seperti user, password, database url, nama database, dan lain-lain akan kita tulis dalam properti file ini, sehingga jika suatu kali ada perubahan dari segi settingan database kita tidak perlu lagi membongkar ulang program-program kita. Cukup hanya dengan mengganti nilai dalam properti file saja.
Langsung saja, Kita memerlukan dua class dan satu file dalam percobaan ini :

  1. Class yang berisi method untuk memanggil file konfigurasi.
  2. Class yang berisi program utama (main program).
  3. File properti yang berisi settingan database.

File pertama ConfigProperties.java

import java.util.Properties;
import java.io.*;


public final class ConfigProperties {
private static ConfigProperties instance;

private static String file_properties = null;
private static Properties ppty = null;

private ConfigProperties() {
// private constructor
}
public static ConfigProperties getInstance( String filename )
throws FileNotFoundException, IOException {

if( instance == null ) {

if ( null == ppty ) {
try {
System.out.println( "LOADING properties......" );

ppty = new Properties();
FileInputStream in = new FileInputStream( filename );
ppty.load( in );
in.close();

}
catch( FileNotFoundException e ) {
throw new FileNotFoundException(
"File - " + filename + " not found in the working directory" );
}
}
}

return instance;
}

/**
* Get the value of the property name.
*
* @param key the property name specified in the properties file.
*
* @return the value of the property name.
*/
static public String getProperty( String key ) {

String str = ppty.getProperty( key );
if ( null == str ) {
System.out.println(
"Warning: attempt to get an non-existant value in property file: " + key );
}

return str;
}
}

Pada file tersebut dapat kita perhatikan bahwa pertama kita mengimport 2 package langsung yaitu package java.io dan package java.util

import java.util.Properties;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.FileInputStream;

Kemudian kita membuat satu method bernama getInstance yang mempunyai parameter String filename,

static  public String getProperty(String key) {
String str = ppty.getProperty(key);
if (null == str) {
System.out.println("Warning: "+
"attempt to get an non-existant value in "+
"property file:" + key);
}
return str;
}
}

dalam method ini kita meng-instansiasi class properties sebagai sebuah obyek baru dalam variable ppty, dimana variable ppty mempunyai tipe data turunan class properties

private  static Properties ppty = null;   //Variabel
// turunan dari Class Properties
ppty = new Properties(); //instansiasi class Properties

Kemudian kita mulai membaca file configuration yang diambil dari inputan parameter dengan menginstansiasi class FileInputStream untuk kemudian dilanjutkan dengan me-load filename tsb ke dalam variabel properties dengan menggunakan method load yang dimiliki oleh class properties tsb

FileInputStream in = new FileInputStream(filename);
// membaca filename dari inputan
// parameter class getInstance
ppty.load(in);
//me-load filename ke dalam Class Properties

Kita dapat melengkapi method getInstance ini dengan cara meletakkan pembacaan file dan loading filename ke dalam block try catch sebagai salah satu usaha kita untuk menjaga kalau-kalau filename yang akan dibaca tidak ada pada tempat yg telah ditentukan.

Selanjutnya kita buat satu method lagi dalam file Class ConfigProperties ini, dimana method ini akan membaca isi dari file yang telah diload. Method ini kita beri nama getProperty dengan parameter String key. Method ini akan mengembalikan nilai String pada penggunaannya nanti.

Dalam method ini kita dapat melihat bahwa jika key yang akan dibaca tidak ada dalam properti filenya maka kita tandai dengan cara memberi tahu user bahwa key tsb tidak exist dalam properti file.

Sampai disini sudah selesai kita membuat file ConfigProperties yang berguna untuk membaca file properties.
Untuk selanjutnya kita akan membuat main class (Kelas Utama) sebagai salah satu cara kita menguji class ConfigProperties yang kita buat di atas.

Kita mulai saja. Main class ini kita beri nama TestConnection.java. berikut penjelasannya:

Pertama kita import dulu package java standar yg kita butuhkan untuk berkomunikasi dengan database yaitu :

  • package java.sql.Connection
  • package java.sql.Driver
  • package java.sql.SQLException


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

Lalu kemudian kita mulai deklarasikan variable yg kita butuhkan:

private  static String db_driver = "db_driver";
private static String db_url = db_url";
private static String db_user = "db_user";
private static String db_pass = "db_pass";
private String sDbDriver;
private String sDbUrl;
private String sDbUser;
private String sDbPass;
private Connection dbConnection;

Keempat variabel yang pertama diatas merupakan variabel static dan variabel ini juga bersifat private dengan tujuan supaya variable ini tidak dapat diakses oleh class lain, karena static maka variabel ini langsung kita isi nilainya dengan string yang akan kita tuliskan ke dalam file configuration nantinya, yaitu “db_driver”, “db_url”, “db_user”, “db_pass”. Sementara keempat variabel kedua juga kita beri sifat private juga dengan tujuan agar variabel ini tidak dapat di akses oleh class lain yang sifatnya public, namun dia tidak static sehingga nanti bisa kita inisialisasi variabel ini. Apa sih bedanya sifat public dengan private? Pertanyaan ini akan saya jawab mungkin dalam tulisan saya berikutnya. Kita lanjutkan…Setelah selesai mendefinisikan variabel, kita akan membuat default constructor dari main class ini. Dimana default constructor ini nantinya akan memanggil sebuah method yang bernama init.

Dalam main class ini, sementara dalam method init ini kita akan menginisialisasikan variable sDbDriver, sDbUrl, sDbUser, sDbPass dengan mengambil nilai dari variable static yang didefinisikan sebelumnya.

public TesConnection() {
init();
}
public void init() {
//inisialisasi variabel
sDbDriver = ConfigProperties.getProperty(db_driver);
sDbUrl = ConfigProperties.getProperty(db_url);
sDbUser = ConfigProperties.getProperty(db_user);
sDbPass = ConfigProperties.getProperty(db_pass);
}

Sekarang kita akan membuat method utama (Main Method) dari class ini. Main method ini secara berurutan akan memanggil

  1. File fisik properti, dalam contoh ini file properti nya akan kita letakkan dalam folder config.
  2. Kita akan mencoba connect ke database.
  3. putuskan koneksi dari database
oleh karena kita akan coba melakukan koneksi ke database, agar lebih rapih kita letakkan code koneksi ke database ini dalam sebuah method dengan nama method connectDB, sehingga pada penggunaannya tinggal kita panggil saja method tsb. Berikut codenya

Dapat kita lihat pada method ini kita menginisialisasi driver database, kemudian melakukan koneksi diwakili dengan variabel dbConnection didalam block try catch.Demikian juga dengan perintah putuskan koneksi dari database kita juga buat saja dalam satu method yang nantinya tinggal kita panggil dalam method utama, berikut code method untuk melakukan pemutusan dari database.

private void disconnectDB() {
System.out.println("Trying to Disconnect DB....");
if(dbConnection !=null){
try{
dbConnection.close();
System.out.println("Disconnecting DB Done..");

}catch(SQLException e){
System.out.println("Exception in Disconnecting DB");
}
}
}
Dalam method ini simple saja hal-hal yang akan kita lakukan yaitu jika dbConnection nya ada atau tidak sama dengan null maka putuskanlah.

Nah sekarang tibalah saatnya kita membuat method utama dari class ini, seperti yang kita tadi rencanakan bahwa method utama dari kelas ini berisi tiga langkah yaitu panggil file fisik configurationnya, lakukan koneksi ke database dan terkahir putuskan hubungan dari database.

Untuk memanggil file fisik dari configuration kita cukup meng-instansiasi class pertama yang kita buat yaitu class ConfigProperties dengan cara meletakkannya di dalam blok try catch. Berikut codenya keseluruhan dalam method utama.


public static void main(String[] args) {
try {
ConfigProperties.getInstance
("config/database_conn.properties");
} catch (Exception e) {
System.out.println("Exception in getProperties=" +
e.getMessage());
}
//initialize Class
TesConnection test = new TesConnection();
//panggil method connectDB
test.connectDB();
// panggil method disconnect
test.disconnectDB();
}
Jika kita gabungkan semua method-method tsb dalam suatu class utama maka akan seperti ini jadinya:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
* Created by IntelliJ IDEA.
* User: Mojo
* Date: Nov 18, 2007
* Time: 7:27:09 PM
* To change this template use File | Settings
* | File Templates.
*/
public class TesConnection {
private static String db_driver = "db_driver";
private static String db_url = "db_url";
private static String db_user = "db_user";
private static String db_pass = "db_pass";

private String sDbDriver;
private String sDbUrl ;
private String sDbUser;
private String sDbPass;
private Connection dbConnection;

public TesConnection() {
init();
}

public void init() {
//inisialisasi variabel
sDbDriver = ConfigProperties.getProperty(db_driver);
sDbUrl = ConfigProperties.getProperty(db_url);
sDbUser = ConfigProperties.getProperty(db_user);
sDbPass = ConfigProperties.getProperty(db_pass);
}
public static void main(String[] args) {
try {
ConfigProperties.getInstance("config"+
"/database_conn.properties");
} catch (Exception e) {
System.out.println("Exception in getProperties=" +
e.getMessage());
}

//initialize Class
TesConnection test = new TesConnection();

//try to Connect
test.connectDB();

//disconnect
test.disconnectDB();

}
private void disconnectDB() {
System.out.println("Trying to Disconnect DB....");
if(dbConnection !=null){
try{
dbConnection.close();
System.out.println("Disconnecting DB Done..");

}catch(SQLException e){
System.out.println("Exception in Disconnecting DB");
}
}


}

private void connectDB() {

System.out.println("Trying to Connect to DataBase...");
try {
Class.forName(sDbDriver);
dbConnection =
DriverManager.getConnection(sDbUrl,sDbUser,sDbPass);
System.out.println(
"Connecting Database Done....");
} catch (SQLException sqle) {
System.out.println("SQLException in ConnectDB=" +
sqle.getMessage());
} catch(Exception e){
System.out.println("Exception in ConnectDB = " +
e.getMessage());

}

}
}
Nah sekarang yg akan kita buat adalah file terakhir yaitu file configurationnya dimana file ini hanya terdiri dari baris-baris yang menjadi properti key yang natinya akan dibaca oleh class ConfigProperties, isi baris tersebut adalah :
  1. db_driver
  2. db_url
  3. db_user
  4. db_pass
Nilai-nilai yang ada dalam File configurasi inilah yg bisa kita ganti-ganti sesuai dengan setting database yang ada pada kita. Berikut isi dari File database_conn.properties
db_url=jdbc:mysql://localhost/test_doang?autoReconnect=true
db_user=root
db_pass=root123
db_driver=com.mysql.jdbc.Driver
Dalam file configuration ini kita bisa lihat bahwa beberapa settingan database kita simpan disini sehingga sewaktu-waktu nanti ada yang berubah dari settingan database tsb, kita hanya merubah nilai-nilai dalama file database_conn.properties ini saja tanpa harus membongkar seluruh aplikasi yang sudah kita compile.
Catatan jangan lupa menambahkan library mysql-connector dalam project ini dan juga menambahkan variable class mysql-connector dalam setting environment Operating System komputer kita ketika kita akan mendeploy aplikasi ini.

Semoga Bermanfaat

josescalia