001    /**
002     * 
003     */
004    package org.apache.geronimo.samples.inventory.dao;
005    
006    import java.sql.Connection;
007    import java.sql.PreparedStatement;
008    import java.sql.ResultSet;
009    import java.sql.SQLException;
010    import java.util.ArrayList;
011    import java.util.List;
012    
013    import org.apache.geronimo.samples.inventory.Item;
014    import org.apache.geronimo.samples.inventory.util.DBManager;
015    
016    
017    public class ItemDAO {
018            
019            public List getItems(){
020                    ArrayList items = new ArrayList();
021                    
022                    Connection con = DBManager.getConnection();
023                    try {
024                            
025                            PreparedStatement pStmt = con.prepareStatement("SELECT i.item_id as item_id, i.item_name as item_name, i.description as description, im.quantity as quantity FROM item i, item_master im WHERE im.item_id = i.item_id");
026                            ResultSet rs = pStmt.executeQuery();
027                            int j = 0;
028                            while(rs.next()){
029                                    ArrayList item = new ArrayList();
030                                    
031                                    String itemId = rs.getString("item_id");
032                                    String itemName = rs.getString("item_name");
033                                    String description = rs.getString("description");
034                                    int quantity = rs.getInt("quantity");
035                                    
036                                    item.add(0, itemId);
037                                    item.add(1, itemName);
038                                    item.add(2, description);
039                                    item.add(3, quantity+"");
040                                    
041                                    items.add(j,item);                              
042                                    j++;
043                                    
044                            }
045                    } catch (SQLException e) {
046                            e.printStackTrace();
047                    }finally{
048                            if(con != null){
049                                    try {
050                                            con.close();
051                                    } catch (SQLException e) {
052                                            e.printStackTrace();
053                                    }
054                            }
055                    }               
056                    
057                    return items;
058            }
059            
060            public int getQOH(String itemNo){
061                    
062                    int qoh = 0;
063                    Connection con = DBManager.getConnection();
064                    try {
065                            
066                            PreparedStatement pStmt = con.prepareStatement("SELECT quantity FROM item_master WHERE item_id =? ");
067                            pStmt.setString(1, itemNo);
068                            
069                            ResultSet rs = pStmt.executeQuery();
070                            while(rs.next()){
071                                    qoh = rs.getInt("quantity");
072                            }
073                    } catch (SQLException e) {
074                            e.printStackTrace();
075                    }finally{
076                            if(con != null){
077                                    try {
078                                            con.close();
079                                    } catch (SQLException e) {
080                                            e.printStackTrace();
081                                    }
082                            }
083                    }               
084                    return qoh;
085            }
086            
087            public boolean isItemIdExists(String itemNo){
088                    boolean isExists = false;
089                    Connection con = DBManager.getConnection();
090                    try {
091                            
092                            PreparedStatement pStmt = con.prepareStatement("SELECT item_id FROM item WHERE item_id =? ");
093                            pStmt.setString(1, itemNo);
094                            
095                            ResultSet rs = pStmt.executeQuery();
096                            if(rs.next()){
097                                    isExists = true;
098                            }
099                    } catch (SQLException e) {
100                            e.printStackTrace();
101                    }finally{
102                            if(con != null){
103                                    try {
104                                            con.close();
105                                    } catch (SQLException e) {
106                                            e.printStackTrace();
107                                    }
108                            }
109                    }               
110                    return isExists;
111            }
112            
113            public void updateQOH(String itemNo, int qoh){
114                    Connection con = DBManager.getConnection();
115                    try {
116                            
117                            PreparedStatement pStmt = con.prepareStatement("UPDATE item_master SET quantity=? WHERE item_id=?");
118                            pStmt.setInt(1, qoh);
119                            pStmt.setString(2, itemNo);
120                            
121                            pStmt.executeUpdate();
122                    } catch (SQLException e) {
123                            e.printStackTrace();
124                    }finally{
125                            if(con != null){
126                                    try {
127                                            con.close();
128                                    } catch (SQLException e) {
129                                            e.printStackTrace();
130                                    }
131                            }
132                    }               
133                    
134            }
135            
136            public void addItem(Item item) {
137                    Connection con = DBManager.getConnection();
138                    try {
139                            
140                            PreparedStatement pStmt = con.prepareStatement("INSERT INTO item VALUES(?, ?, ?)");
141                            pStmt.setString(1, item.getItemNo());
142                            pStmt.setString(2, item.getItemName());
143                            pStmt.setString(3, item.getDescription());
144                            
145                            pStmt.executeUpdate();
146                            
147                            pStmt = con.prepareStatement("INSERT INTO item_master VALUES(?, ?)");
148                            pStmt.setString(1, item.getItemNo());
149                            pStmt.setInt(2, 0);
150                            
151                            pStmt.executeUpdate();
152                            
153                    } catch (SQLException e) {
154                            e.printStackTrace();
155                    }finally{
156                            if(con != null){
157                                    try {
158                                            con.close();
159                                    } catch (SQLException e) {
160                                            e.printStackTrace();
161                                    }
162                            }
163                    }               
164            }
165    
166    }