Blog Archive

ExtJs 4 MVC Architecture tutorial using Java Servlets - Part 4

Click here for previous Chapter
So far we have discussed the complete ExtJs 4 MVC architecture and needed sources for our Item Maintenance application. We have started to work on the Server Side and created our Item bean.

Step 11: Define the Item utility function that will work with our database - ItemInformation.java

package com.as400samplecode.util;

import java.sql.Connection;          
import java.sql.ResultSet;           
import java.sql.SQLException;        
import java.sql.PreparedStatement;   
import java.util.ArrayList;

import javax.naming.Context;         
import javax.naming.InitialContext;  
import javax.sql.DataSource;         

public class ItemInformation {            

    Connection conn = null;             
    PreparedStatement stmt = null;      
    String sql = null;
    String company = null;

    int numberOfRows = 0;


    public ItemInformation(String company) {
        this.company = company;
    }

    //this method gets us a list of items based on the parameters start and limit
    //these parameters are sent to use by the ExtJs Grid paging toolbar
    //we use a scrollable resultset and position it to start from where we need our records
    public ArrayList<Item> getItems(String start, String limit) {  

        ArrayList<Item> itemList = new ArrayList<Item>();    

        try {       
            Context ctx = (Context) new InitialContext().lookup("java:comp/env");
            conn = ((DataSource) ctx.lookup("jdbc/mydb2")).getConnection(); 

            int skipRows = Integer.parseInt(start);
            numberOfRows = skipRows + Integer.parseInt(limit);

            sql = "Select ITEMNO,DESC1,DESC2,WEIGHT,STATUS " + 
            " from PRODUCTS where COMPANY = ? order by COMPANY,ITEMNO FETCH FIRST " + numberOfRows + " ROWS ONLY";                       

            stmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            stmt.setInt(1,Integer.parseInt(company)); 

            ResultSet rs = stmt.executeQuery();  
            rs.relative(skipRows);
            
            while(rs.next()){ 
                Item item = new Item();
                item.setItem(rs.getString("ITEMNO").trim());
                item.setDesc1(rs.getString("DESC1").trim());
                item.setDesc2(rs.getString("DESC2").trim());
                item.setWeight(Double.parseDouble(rs.getString("WEIGHT").trim()));
                item.setStatus(rs.getString("STATUS").trim());
                itemList.add(item);
            }                                                                          

            rs.close();                                                                
            stmt.close();                                                              
            stmt = null;                                                               


            conn.close();                                                              
            conn = null;                                                    

        }                                                                
        catch(Exception e){System.out.println(e);}                       

        finally {                                                        
            
            if (stmt != null) {                                             
                try {                                                          
                    stmt.close();                                                 
                } catch (SQLException sqlex) {                                 
                    // ignore -- as we can't do anything about it here            
                }                                                              

                stmt = null;                                             
            }                                                         

            if (conn != null) {                                       
                try {                                                    
                    conn.close();                                           
                } catch (SQLException sqlex) {                           
                    // ignore -- as we can't do anything about it here      
                }                                                        

                conn = null;                                             
            }                                                         
        }               

        return itemList;

    }   
    
    //this method gets us the Total Number of Items that we have in our database
    public int getTotalCount() {  

        int totalCount = 0; 

        try {       
            Context ctx = (Context) new InitialContext().lookup("java:comp/env");
            conn = ((DataSource) ctx.lookup("jdbc/mydb2")).getConnection(); 

            sql = "Select count(*) from PRODUCTS where COMPANY = ?";                       

            stmt = conn.prepareStatement(sql);
            stmt.setInt(1,Integer.parseInt(company)); 

            ResultSet rs = stmt.executeQuery();  
            while(rs.next()){ 
                totalCount = rs.getInt(1);
                break;
            }                                                                          

            rs.close();                                                                
            stmt.close();                                                              
            stmt = null;                                                               


            conn.close();                                                              
            conn = null;                                                    

        }                                                                
        catch(Exception e){System.out.println(e);}                       

        finally {                                                        
            
            if (stmt != null) {                                             
                try {                                                          
                    stmt.close();                                                 
                } catch (SQLException sqlex) {                                 
                    // ignore -- as we can't do anything about it here            
                }                                                              

                stmt = null;                                             
            }                                                         

            if (conn != null) {                                       
                try {                                                    
                    conn.close();                                           
                } catch (SQLException sqlex) {                           
                    // ignore -- as we can't do anything about it here      
                }                                                        

                conn = null;                                             
            }                                                         
        }               

        return totalCount;

    }   

    //this methods loops thru an arraylist of Items and update the changed information
    public boolean updateItems(ArrayList<Item> itemList) {  

        boolean success = true;
        
        try {       
            Context ctx = (Context) new InitialContext().lookup("java:comp/env");
            conn = ((DataSource) ctx.lookup("jdbc/mydb2")).getConnection(); 

            sql = "UPDATE PRODUCTS set DESC1 = ?,DESC2 = ?,WEIGHT = ? where COMPANY = ? and ITEMNO = ?";
            stmt = conn.prepareStatement(sql);
            
            for(int i=0;i<itemList.size();i++){
                Item item = itemList.get(i);
                stmt.setString(1,item.getDesc1()); 
                stmt.setString(2,item.getDesc2()); 
                stmt.setDouble(3,item.getWeight()); 
                stmt.setInt(4,Integer.parseInt(company)); 
                stmt.setString(5,item.getItem()); 
                stmt.addBatch();
            }
            
            stmt.executeBatch();
            
            
            stmt.close();                                                              
            stmt = null;                                                               
            conn.close();                                                              
            conn = null;                                                    

        }                                                                
        catch(Exception e){
            
            success = false;
            System.out.println(e);
            
        }                       

        finally {                                                        
            
            if (stmt != null) {                                             
                try {                                                          
                    stmt.close();                                                 
                } catch (SQLException sqlex) {                                 
                    // ignore -- as we can't do anything about it here            
                }                                                              

                stmt = null;                                             
            }                                                         

            if (conn != null) {                                       
                try {                                                    
                    conn.close();                                           
                } catch (SQLException sqlex) {                           
                    // ignore -- as we can't do anything about it here      
                }                                                        

                conn = null;                                             
            }                                                         
        }               

        return success;

    }   

    //this method creates a new Item in the database
    public boolean addItems(ArrayList<Item> itemList) {  

        boolean success = true;
        
        try {       
            Context ctx = (Context) new InitialContext().lookup("java:comp/env");
            conn = ((DataSource) ctx.lookup("jdbc/mydb2")).getConnection(); 

            sql = "INSERT into PRODUCTS (COMPANY,ITEMNO,DESC1,DESC2,WEIGHT,STATUS) VALUES(?,?,?,?,?,?)";
            stmt = conn.prepareStatement(sql);
            
            for(int i=0;i<itemList.size();i++){
                Item item = itemList.get(i);
                stmt.setInt(1,Integer.parseInt(company)); 
                stmt.setString(2,item.getItem()); 
                stmt.setString(3,item.getDesc1()); 
                stmt.setString(4,item.getDesc2()); 
                stmt.setDouble(5,item.getWeight()); 
                stmt.setString(6,"A");
                stmt.addBatch();
            }
            
            stmt.executeBatch();
            
            
            stmt.close();                                                              
            stmt = null;                                                               
            conn.close();                                                              
            conn = null;                                                    

        }                                                                
        catch(Exception e){
            
            success = false;
            System.out.println(e);
            
        }                       

        finally {                                                        
            
            if (stmt != null) {                                             
                try {                                                          
                    stmt.close();                                                 
                } catch (SQLException sqlex) {                                 
                    // ignore -- as we can't do anything about it here            
                }                                                              

                stmt = null;                                             
            }                                                         

            if (conn != null) {                                       
                try {                                                    
                    conn.close();                                           
                } catch (SQLException sqlex) {                           
                    // ignore -- as we can't do anything about it here      
                }                                                        

                conn = null;                                             
            }                                                         
        }               

        return success;

    }   
}

Click here for next Chapter