View Javadoc

1   /**
2    * 
3    */
4   package org.apache.geronimo.samples.inventory.dao;
5   
6   import java.sql.Connection;
7   import java.sql.PreparedStatement;
8   import java.sql.ResultSet;
9   import java.sql.SQLException;
10  import java.util.ArrayList;
11  import java.util.List;
12  
13  import org.apache.geronimo.samples.inventory.Item;
14  import org.apache.geronimo.samples.inventory.util.DBManager;
15  
16  
17  public class ItemDAO {
18  	
19  	public List getItems(){
20  		ArrayList items = new ArrayList();
21  		
22  		Connection con = DBManager.getConnection();
23  		try {
24  			
25  			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");
26  			ResultSet rs = pStmt.executeQuery();
27  			int j = 0;
28  			while(rs.next()){
29  				ArrayList item = new ArrayList();
30  				
31  				String itemId = rs.getString("item_id");
32  				String itemName = rs.getString("item_name");
33  				String description = rs.getString("description");
34  				int quantity = rs.getInt("quantity");
35  				
36  				item.add(0, itemId);
37  				item.add(1, itemName);
38  				item.add(2, description);
39  				item.add(3, quantity+"");
40  				
41  				items.add(j,item);				
42  				j++;
43  				
44  			}
45  		} catch (SQLException e) {
46  			e.printStackTrace();
47  		}finally{
48  			if(con != null){
49  				try {
50  					con.close();
51  				} catch (SQLException e) {
52  					e.printStackTrace();
53  				}
54  			}
55  		}		
56  		
57  		return items;
58  	}
59  	
60  	public int getQOH(String itemNo){
61  		
62  		int qoh = 0;
63  		Connection con = DBManager.getConnection();
64  		try {
65  			
66  			PreparedStatement pStmt = con.prepareStatement("SELECT quantity FROM item_master WHERE item_id =? ");
67  			pStmt.setString(1, itemNo);
68  			
69  			ResultSet rs = pStmt.executeQuery();
70  			while(rs.next()){
71  				qoh = rs.getInt("quantity");
72  			}
73  		} catch (SQLException e) {
74  			e.printStackTrace();
75  		}finally{
76  			if(con != null){
77  				try {
78  					con.close();
79  				} catch (SQLException e) {
80  					e.printStackTrace();
81  				}
82  			}
83  		}		
84  		return qoh;
85  	}
86  	
87  	public boolean isItemIdExists(String itemNo){
88  		boolean isExists = false;
89  		Connection con = DBManager.getConnection();
90  		try {
91  			
92  			PreparedStatement pStmt = con.prepareStatement("SELECT item_id FROM item WHERE item_id =? ");
93  			pStmt.setString(1, itemNo);
94  			
95  			ResultSet rs = pStmt.executeQuery();
96  			if(rs.next()){
97  				isExists = true;
98  			}
99  		} 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 }