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
No comments:
Post a Comment
NO JUNK, Please try to keep this clean and related to the topic at hand.
Comments are for users to ask questions, collaborate or improve on existing.