?? cdstore.java
字號:
PreparedStatement prepStmt=null;
ResultSet rs =null;
try {
con=getConnection();
String selectStatement = "select * " + "from customer where username = ? ";
prepStmt = con.prepareStatement(selectStatement);
prepStmt.setString(1, cdId);
rs = prepStmt.executeQuery();
if (rs.next()) {
Customer bd = new Customer(rs.getString(1), rs.getString(2), rs.getInt(3),rs.getString(4), rs.getString(5)
);
prepStmt.close();
return bd;
}
else {
return null;
}
}finally{
closeResultSet(rs);
closePrepStmt(prepStmt);
closeConnection(con);
}
}
public boolean deleteCd (String cdId) throws Exception {
Connection con=null;
PreparedStatement prepStmt=null;
try {
con=getConnection();
String updateStatement = " delete from cd where id = ? ";
prepStmt = con.prepareStatement(updateStatement);
prepStmt.setString(1, cdId);
prepStmt.executeUpdate();
prepStmt.close();
return true;
}finally{
closePrepStmt(prepStmt);
closeConnection(con);
}
}
public boolean deleteUser (String cdId) throws Exception {
Connection con=null;
PreparedStatement prepStmt=null;
try {
con=getConnection();
String updateStatement = " delete from customer where username = ? ";
prepStmt = con.prepareStatement(updateStatement);
prepStmt.setString(1, cdId);
prepStmt.executeUpdate();
prepStmt.close();
return true;
}finally{
closePrepStmt(prepStmt);
closeConnection(con);
}
}
public boolean deleteOrder (String cdId) throws Exception {
Connection con=null;
PreparedStatement prepStmt=null;
try {
con=getConnection();
String updateStatement = " delete from cdorder where id = "+cdId+" ";
prepStmt = con.prepareStatement(updateStatement);
prepStmt.executeUpdate();
prepStmt.close();
return true;
}finally{
closePrepStmt(prepStmt);
closeConnection(con);
}
}
public void buyCds(ShoppingCart cart,String username,String thedate)throws Exception {
Connection con=null;
Collection items = cart.getItems();
Iterator i = items.iterator();
try {
con=getConnection();
con.setAutoCommit(false);
while (i.hasNext()) {
ShoppingCartItem sci = (ShoppingCartItem)i.next();
CdDetails bd = (CdDetails)sci.getItem();
String id = bd.getCdId();
int quantity = sci.getQuantity();
buyCd(id, quantity,con,username,thedate);
}
con.commit();
con.setAutoCommit(true);
} catch (Exception ex) {
con.rollback();
throw ex;
}finally{
closeConnection(con);
}
}
public void buyCd(String cdId, int quantity,Connection con,String username ,String thedate) throws Exception {
PreparedStatement prepStmt=null;
ResultSet rs=null;
String cdname;
try{
String selectStatement = "select * " + "from cd where id = ? ";
prepStmt = con.prepareStatement(selectStatement);
prepStmt.setString(1, cdId);
rs = prepStmt.executeQuery();
cdname=getCdDetails(cdId).getTitle();
if (rs.next()) {
prepStmt.close();
String updateStatement =
"update cd set saleamount = saleamount + ? where id = ?";
prepStmt = con.prepareStatement(updateStatement);
prepStmt.setInt(1, quantity);
prepStmt.setString(2, cdId);
prepStmt.executeUpdate();
String updateStatement1 =
"update cd set warehouse = warehouse - ? where id = ?";
prepStmt = con.prepareStatement(updateStatement1);
prepStmt.setInt(1, quantity);
prepStmt.setString(2, cdId);
prepStmt.executeUpdate();
prepStmt.close();
String insertStatement =
"insert into account (username,cdname,saleAmount,thedate) values('"+username+"','"+cdname+"',"+quantity+",'"+thedate+"')";
prepStmt = con.prepareStatement(insertStatement);
prepStmt.executeUpdate();
prepStmt.close();
}
}catch (SQLException e)
{
e.printStackTrace();
}finally{
closeResultSet(rs);
closePrepStmt(prepStmt);
}
}
public boolean checkMemberExist(String username) throws Exception {
Connection con=null;
PreparedStatement prepStmt=null;
ResultSet rs =null;
try {
con=getConnection();
String selectStatement = "select * from customer where username='" + username + "'";
prepStmt = con.prepareStatement(selectStatement);
rs = prepStmt.executeQuery();
if(rs==null)return false;
if (rs.next())
return true;}
catch (SQLException e) {
e.printStackTrace();
}finally{
closeResultSet(rs);
closePrepStmt(prepStmt);
closeConnection(con);
}
return false;
}
public boolean addUser(Customer customer) throws Exception {
Connection con=null;
PreparedStatement prepStmt=null;
con=getConnection();
try{
String updateStatement =
"insert into customer values('"+customer.getUsername()+"','"+customer.getPassword()+"',0,'"+customer.getTel()+"','"+customer.getEmail()+"')";
prepStmt = con.prepareStatement(updateStatement);
prepStmt.executeUpdate();
prepStmt.close();
return true;}
catch (SQLException e)
{
e.printStackTrace();
return false;
}
finally{
closeConnection(con);
closePrepStmt(prepStmt);
}
}
public boolean addOrder(Order order) throws Exception {
Connection con=null;
PreparedStatement prepStmt=null;
con=getConnection();
try{
String updateStatement =
"insert into cdorder (username,name,amount,thedate,isorder) values('"+order.getUsername()+"','"+order.getName()+"','"+order.getAmount()+"','"+order.getThedate()+"','1')";
prepStmt = con.prepareStatement(updateStatement);
prepStmt.executeUpdate();
prepStmt.close();
return true;}
catch (SQLException e)
{
e.printStackTrace();
return false;
}
finally{
closeConnection(con);
closePrepStmt(prepStmt);
}
}
public boolean verifyUser (String username,String password) throws Exception {
Connection con=null;
PreparedStatement prepStmt=null;
ResultSet rs =null;
try {
con=getConnection();
String selectStatement = "select * from customer where username='" + username + "'and password='"+password+"' and isadmin=0";
prepStmt = con.prepareStatement(selectStatement);
rs = prepStmt.executeQuery();
if (rs.next())
return true;
}
catch (SQLException e) {
e.printStackTrace();
}finally{
closeResultSet(rs);
closePrepStmt(prepStmt);
closeConnection(con);
}
return false;
}
public boolean verifyAdmin (String username,String password) throws Exception {
Connection con=null;
PreparedStatement prepStmt=null;
ResultSet rs =null;
try {
con=getConnection();
String selectStatement = "select * from customer where username='" + username + "'and password='"+password+"' and isadmin=1";
prepStmt = con.prepareStatement(selectStatement);
rs = prepStmt.executeQuery();
if (rs.next())
return true;
}
catch (SQLException e) {
e.printStackTrace();
}finally{
closeResultSet(rs);
closePrepStmt(prepStmt);
closeConnection(con);
}
return false;
}
public boolean updateCd(String cdId,String title,String name,String description,String saletype,String warehouse,String price ,String year ,String saleamount) throws Exception {
Connection con=null;
PreparedStatement prepStmt=null;
ResultSet rs=null;
try{
con=getConnection();
String selectStatement = "select * " + "from cd where id = ? ";
prepStmt = con.prepareStatement(selectStatement);
prepStmt.setString(1, cdId);
rs = prepStmt.executeQuery();
if (!rs.next()) {
return false;
}
else
{
String updateStatement =
"update cd set id='"+cdId+"',title='"+title+"',name='"+name+"',description='"+description+"',saletype='"+saletype+"',warehouse="+warehouse+",price="+price+" , yr="+year+",saleAmount="+saleamount+" where id=?";
prepStmt = con.prepareStatement(updateStatement);
prepStmt.setString(1, cdId);
prepStmt.executeUpdate();
prepStmt.close();
return true;
}
}
catch (SQLException e)
{
return false;
}
finally{
closeConnection(con);
closePrepStmt(prepStmt);
}
}
public boolean insertCd(String cdId,String title,String name,String description,String saletype,String warehouse,String price ,String year ,String saleamount) throws Exception {
Connection con=null;
PreparedStatement prepStmt=null;
con=getConnection();
try{
String updateStatement =
"insert into cd values('"+cdId+"','"+name+"','"+title+"',"+price+","+year+",'"+description+"',"+saleamount+",'"+saletype+"',"+warehouse+")";
prepStmt = con.prepareStatement(updateStatement);
prepStmt.executeUpdate();
prepStmt.close();
return true;}
catch (SQLException e)
{
e.printStackTrace();
return false;
}
finally{
closeConnection(con);
closePrepStmt(prepStmt);
}
}
public boolean updateUser(String username,String password,String isadmin,String tel ,String email) throws Exception {
Connection con=null;
PreparedStatement prepStmt=null;
ResultSet rs=null;
try{
con=getConnection();
String selectStatement = "select * " + "from customer where username = ? ";
prepStmt = con.prepareStatement(selectStatement);
prepStmt.setString(1, username);
rs = prepStmt.executeQuery();
if (!rs.next()) {
return false;
}
else
{
String updateStatement =
"update customer set username='"+username+"',password='"+password+"',isadmin="+isadmin+",tel='"+tel+"',email='"+email+"' where username=?";
prepStmt = con.prepareStatement(updateStatement);
prepStmt.setString(1, username);
prepStmt.executeUpdate();
prepStmt.close();
return true;
}
}
catch (SQLException e)
{
return false;
}
finally{
closeConnection(con);
closePrepStmt(prepStmt);
}
}
public boolean insertUser(String username,String password,String isadmin,String tel,String email) throws Exception {
Connection con=null;
PreparedStatement prepStmt=null;
con=getConnection();
try{
String updateStatement =
"insert into customer values('"+username+"','"+password+"',"+isadmin+",'"+tel+"','"+email+"')";
prepStmt = con.prepareStatement(updateStatement);
prepStmt.executeUpdate();
prepStmt.close();
return true;}
catch (SQLException e)
{
e.printStackTrace();
return false;
}
finally{
closeConnection(con);
closePrepStmt(prepStmt);
}
}
}?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -