private List<ColumnOrSuperColumn> getNextSlice(String lastName) {
// TODO: Why aren't we passed the slide predicate? What if the column order is reversed?
SlicePredicate slicePredicate = CassandraDefs.slicePredicateStartCol(Utils.toBytes(lastName));
DBConn dbConn = ThriftService.instance().getDBConnection(m_keyspace);
try {
return dbConn.getSlice(m_columnParent, slicePredicate, ByteBuffer.wrap(m_rowKey));
} finally {
ThriftService.instance().returnDBConnection(dbConn);
}
} // getNextSlice
public static ArrayList<ProductPic> getProductPic(int product_id) throws SQLException, Exception {
Connection conn = DBConn.getConn();
PreparedStatement pstmt = null;
ResultSet rs = null;
pstmt =
conn.prepareStatement(
"SELECT * FROM `product_pic`where `product_id` = ? ORDER BY `product_pic`.`product_id` ASC");
pstmt.setInt(1, product_id);
rs = pstmt.executeQuery();
String[] pic = new String[300]; // arrey that use to store picture location
int n = 1; // use to locate picture in order
while (rs.next()) {
pic[n] = rs.getString("pic_location");
n++;
}
n--;
n = n / 2;
ArrayList<ProductPic> res = new ArrayList<ProductPic>();
for (int k = 1; k <= n; k++) {
ProductPic i = new ProductPic();
i.smallPic = pic[k];
i.largePic = pic[(k + n)];
res.add(i);
}
conn.close();
return res;
}
public static void productUpdate(
int product_id,
String product_name,
int product_price,
int product_discount,
String product_gender,
String product_type,
String product_manufacture,
String product_info,
String product_description,
String product_profile_pic)
throws SQLException, Exception {
Connection conn = DBConn.getConn();
PreparedStatement pstmt = null;
pstmt =
conn.prepareStatement(
"UPDATE `product_list` SET `product_name`=?, `product_price`=?, `product_discount`=?, `product_gender`=?, `product_type`=?, `product_manufacture`=?, `product_info`=?, `product_description`=?, `product_profile_pic`=? WHERE `product_id`=? ");
pstmt.setString(1, product_name);
pstmt.setInt(2, product_price);
pstmt.setInt(3, product_discount);
pstmt.setString(4, product_gender);
pstmt.setString(5, product_type);
pstmt.setString(6, product_manufacture);
pstmt.setString(7, product_info);
pstmt.setString(8, product_description);
pstmt.setString(9, product_profile_pic);
pstmt.setInt(10, product_id);
pstmt.executeUpdate();
conn.close();
}
/**
* Create a column batch and fetch the first set of columns using the given predicate. If no
* columns are found for the given row key and slice predicate, the first call to {@link
* #hasNext()} will return false.
*
* @param thisConn {@link DBConn} that provides the connection to perform the fetch. It also tells
* us what keyspace to use for subsequent column slices.
* @param columnParent Defines the column family to fetch columns from.
* @param rowKey Row of key to fetch columns from.
* @param slicePredicate Predicate that defines which columns to select.
*/
public CassandraColumnBatch(
DBConn thisConn, ColumnParent columnParent, byte[] rowKey, SlicePredicate slicePredicate) {
m_keyspace = thisConn.getKeyspace();
m_columnParent = columnParent;
m_rowKey = rowKey;
List<ColumnOrSuperColumn> columns =
thisConn.getSlice(m_columnParent, slicePredicate, ByteBuffer.wrap(m_rowKey));
m_sliceSize = 0;
m_iColumns = columns.iterator();
if (!m_iColumns.hasNext()) {
// tombstone? no columns to iterate
return;
}
// The slice is not empty, so we can simply get the first column
shiftColumn();
} // constructor
/** This method used to connect to the Conection pool */
public void connect() throws ConnectException, RemoteException {
try {
conn = null;
conn = dbconn.open();
USFEnv.getLog().writeDebug("created db connection(), ref: " + conn.toString(), this, null);
} catch (Exception e) {
throw new ConnectException("Database connection failure");
}
}
public static void deleteProductPic(int product_id, int pic_num) throws SQLException, Exception {
Connection conn = DBConn.getConn();
PreparedStatement pstmt = null;
pstmt =
conn.prepareStatement("DELETE FROM `product_pic` where `product_id` = ? and `pic_num`= ?");
pstmt.setInt(1, product_id);
pstmt.setInt(2, pic_num);
pstmt.executeUpdate();
}
public static ArrayList<Product> getProduct() throws SQLException, Exception {
Connection conn = DBConn.getConn();
PreparedStatement pstmt = null;
ResultSet rs = null;
pstmt = conn.prepareStatement("SELECT * FROM `product_list`");
rs = pstmt.executeQuery();
ArrayList<Product> res = productListFromRs(rs);
conn.close();
return res;
}
public static ArrayList<Product> getProductByKeyword(String key) throws SQLException, Exception {
Connection conn = DBConn.getConn();
PreparedStatement pstmt =
conn.prepareStatement(
"SELECT * FROM `product_list` WHERE LOWER(`product_name`) like LOWER(?)");
pstmt.setString(1, "%" + key + "%");
ResultSet rs = pstmt.executeQuery();
ArrayList<Product> res = productListFromRs(rs);
conn.close();
return res;
}
public static ArrayList<Product> getProductByCategory(String category)
throws SQLException, Exception {
Connection conn = DBConn.getConn();
PreparedStatement pstmt =
conn.prepareStatement("SELECT * FROM `product_list` WHERE product_type = ?");
pstmt.setString(1, category);
ResultSet rs = pstmt.executeQuery();
ArrayList<Product> res = productListFromRs(rs);
conn.close();
return res;
}
public static void main(String args[]) {
// get database connection
try {
conn = (Connection) DBConn.getConnection();
} catch (Exception e1) {
System.out.println("Error: No Database Connection. Exiting...");
return;
}
System.out.println("Welcome to the Hotel Database");
while (true) {
// console IO
System.out.println(
"\nMenu: \n(A) Add Guest \n(B) Delete Guest \n(C) Update Guest \n(D) Find Room "
+ "\n(E) Book a Room \n(F) Admin Menu");
try {
bufferRead = new BufferedReader(new InputStreamReader(System.in));
String s = bufferRead.readLine();
char a = s.toUpperCase().charAt(0);
System.out.println("Selected Option: " + a);
switch (a) {
case 'A': // add guest
addGuest();
break;
case 'B': // delete guest
deleteGuest();
break;
case 'C': // update guest
updateGuest();
break;
case 'D': // search hotels
searchHotels();
break;
case 'E': // book a room
registerRoom();
break;
case 'F': // admin menu - seperate
adminMenu();
break;
default:
System.out.println("That option does not exist.");
break;
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
public static void deleteProduct(int product_id) throws SQLException, Exception {
Connection conn = DBConn.getConn();
PreparedStatement pstmt = null;
pstmt = conn.prepareStatement("DELETE FROM `product_list` where `product_id` = ?");
pstmt.setInt(1, product_id);
pstmt.executeUpdate();
pstmt = conn.prepareStatement("DELETE FROM `item_info` where `product_id` = ?");
pstmt.setInt(1, product_id);
pstmt.executeUpdate();
}
public static ArrayList<String> getCategory() throws SQLException, Exception {
Connection conn = DBConn.getConn();
PreparedStatement pstmt = null;
ResultSet rs = null;
pstmt = conn.prepareStatement("SELECT DISTINCT `product_type` FROM `product_list`");
rs = pstmt.executeQuery();
ArrayList<String> res = new ArrayList();
while (rs.next()) {
res.add(rs.getString("product_type"));
}
conn.close();
return res;
}
public static Product getProductByProductId(int product_id) throws SQLException, Exception {
Connection conn = DBConn.getConn();
PreparedStatement pstmt = null;
ResultSet rs = null;
pstmt = conn.prepareStatement("SELECT * FROM `product_list` WHERE product_id = ?");
pstmt.setInt(1, product_id);
rs = pstmt.executeQuery();
Product i = new Product();
if (rs.next()) {
getProductRs(i, rs);
}
conn.close();
return i; // get arreylist like get product but there are onty 1 product in list
}
// add new product
public static void addProduct(
int product_id,
String product_name,
int product_price,
int product_discount,
String product_gender,
String product_category,
String product_manufacture,
String product_info,
String product_description)
throws SQLException, Exception {
Connection conn = DBConn.getConn();
PreparedStatement pstmt = null;
ResultSet rs = null;
String product_profile_pic = "";
pstmt = conn.prepareStatement("SELECT * FROM `product_list` where `product_id` = ?");
pstmt.setInt(1, product_id);
rs = pstmt.executeQuery();
int cg = 0; // use to check did product_id already have or not
while (rs.next()) {
cg++;
}
pstmt = conn.prepareStatement("insert into product_list values(?,?,?,?,?,?,?,?,?,?)");
if (cg == 0) {
pstmt.setInt(1, product_id);
pstmt.setString(2, product_name);
pstmt.setInt(3, product_price);
pstmt.setInt(4, product_discount);
pstmt.setString(5, product_gender);
pstmt.setString(6, product_category);
pstmt.setString(7, product_manufacture);
pstmt.setString(8, product_info);
pstmt.setString(9, product_description);
pstmt.setString(10, product_profile_pic);
pstmt.executeUpdate();
// add done
} else {
throw new Exception("Can not add same product id");
// add fail,already add product_id
}
conn.close();
}
// add picture
public static void addProductPic(int product_id, String pic_location)
throws SQLException, Exception {
Connection conn = DBConn.getConn();
PreparedStatement pstmt = null;
ResultSet rs = null;
int pic_num = 0;
pstmt = conn.prepareStatement("SELECT * FROM `product_list` where `product_id` = ?");
pstmt.setInt(1, product_id);
rs = pstmt.executeQuery();
String ch = "can not found this product id"; // use to check that have product_id and location
while (rs.next()) {
ch = "ok"; // change to ok if find product in product_list
}
// get picture number
pstmt = conn.prepareStatement("SELECT * FROM `product_pic` where `product_id` = ?");
pstmt.setInt(1, product_id);
rs = pstmt.executeQuery();
while (rs.next()) {
pic_num = rs.getInt("pic_num");
}
// add picture to data base
pstmt = conn.prepareStatement("insert into product_pic values(?,?,?)");
if (ch.equals("ok")) {
pic_num++;
pstmt.setInt(1, product_id);
pstmt.setInt(2, pic_num);
pstmt.setString(3, pic_location);
pstmt.executeUpdate();
// add done
} else {
throw new Exception(ch);
// add fail
}
conn.close();
}
// Para obtener una conexion.
private Connection getConnection() {
DBConn cnx = new DBConn();
return cnx.getConnection();
}
