Struts: Build HTML Options Tag Elements from MySQL data

Some sample program developed using STRUTS framework to access the MySQL database and retrieve data to build the html options elements. This post is consider as an enhancement from the previous post Struts: HTML Options Tag Sample Code.

 

The previous post was hardcoded to build the html options tag. Meanwhile, this post will build the html options’ elements by retrieving data from MySQL database.

 

I have used the struts-config.xml to define the datasouce to access to the MySQL database. Some simple explanation will be define within this blog here.

 

1. Defining the datasource within the struts-config.xml.

 

    <data-sources>

        <data-source type=”org.apache.commons.dbcp.BasicDataSource” key=”dbSource”>

            <set-property property=”driverClassName” value=”com.mysql.jdbc.Driver” />

            <set-property property=”url” value=”jdbc:mysql://localhost:3306/itemdb” />

            <set-property property=”username” value=”someuser” />

            <set-property property=”password” value=”somepassword ” />

        </data-source>

</data-sources>

 

2. Code the ActionForm and Action class.

 

Within this tutorial, the ActionForm and Action class will not do any much of process. In real world programming, you may want to use both class for building you validation process here.

 

I have use the Action class to create the datasource and map to the struts-config.xml.

 

    public ActionForward execute(ActionMapping mapping, ActionForm  form,

            HttpServletRequest request, HttpServletResponse response)

            throws Exception {

       

        /** Here ‘dbSource’ maps to the datasource key defined in struts-config.xml: */

        DataSource dataSource = (DataSource)servlet.getServletContext().getAttribute(“dbSource”);

       

        ArrayList menuList = new ArrayList();

        MenuData menuData = new MenuData();

       

        //pass the dataSource to menuList within MenuData class.

        //assign elements to menuList.

        menuList = menuData.menuList(dataSource);

 

        //set the attribute to be use for the menu.jsp

        HttpSession session = request.getSession();

        session.setAttribute( “menuList”, menuList);

       

        return mapping.findForward(SUCCESS);

       

    }

 

3. Code the database access and business logic.

 

The created datasource will be pass to the MenuData class to be override by menuList. The business logic and database access will be developed within the MenuData class.

 

public ArrayList menuList(DataSource dataSource) {

       

        Connection conn = null;

        Statement  stmt = null;

        PreparedStatement prpStmt = null;

        ResultSet rs = null;

       

        ArrayList menuList = new ArrayList();

        Menu menu = null;

       

        try{

           

            conn = dataSource.getConnection();

            String sqlQuery = “SELECT itemid, itemname FROM itemtable”;

            prpStmt = conn.prepareStatement(sqlQuery);

            rs = prpStmt.executeQuery();

           

            while (rs.next()) {

                menu = new Menu(rs.getString(1), rs.getString(2));

                menuList.add(menu);

            }

           

            rs.close();

            prpStmt.close();

            conn.close();

           

        } catch ( SQLException e ) {

            System.err.println(“SQL Exception occured while accessing the table.” );

            e.printStackTrace();

            return null;

           

        } finally {

            try {

                conn.close();

            } catch (SQLException e) {

                System.err.println(“Unable to close connection.” );

                e.printStackTrace();

            }

        }

       

        return menuList;

       

    }

 

4. Remark

• I am using the NetBeans IDE tool to developed the code and MySQL as the database.
• Create the database, table and insert some data. I had included the database and please use the MySQLAdministrator to restore the data.
• Within the struts-config.xml, you may want to change the value of username and password. Remember within the struts-config.xml, the element type of <data-sources> must define earlier <form-beans>.

Overall, there is 6 pieces code have been used for this Struts tutorial.

 

View Layer

• index.jsp
• begin.jsp
• ManuForm.java

 

Controller Layer

• MenuAction.java

 

Data Layer

• MenuData.java
• Menu.java

Download Sample Code and Database: Click Here