MVC模式的Web电信报表系统

package com.neusoft.pre_fee_gatherDao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import com.neusoft.hr.dao.DbUtils; public class Pre_Fee_GatherRecordDao { //���� public boolean insert(Pre_Fee_GatherRecord csr){ //1.��ȡ����' Connection conn = DbUtils.getConnection(); //2.Ԥ���� String sql = "TEAM101_RP_PRE_FEE_GATHER_T_3(account_month,city_code,product_code,write_off_type_code,write_off_fee,update_time) values(?,?,?,?,?,to_date(?,'yyyy-mm-dd'))"; try { PreparedStatement ps = conn.prepareStatement(sql); //3.���ò��� ps.setString(1,csr.getAccount_month()); ps.setString(2,csr.getCity_code()); ps.setString(3, csr.getProduct_code()); ps.setString(4, csr.getWrite_off_type_code()); ps.setString(5, csr.getWrite_off_fee()); ps.setString(6, csr.getUpdate_time()); //4.ִ��sql��� int x = ps.executeUpdate(); if(x>0){ System.out.println("添加成功！！！"); return true; } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ DbUtils.closeConnection(); } return false; } //���� public boolean update(Pre_Fee_GatherRecord csr){ //1.��ȡ����' Connection conn = DbUtils.getConnection(); //2.Ԥ���� String sql = "update TEAM101_RP_CARD_SALE_5 set getSale_date=to_date(?,'yyyy-mm-dd'),City_code=?,Product_code=?,Card_sale_amount=?,Card_par_value_fee=?,Total_fee=?,Discount_fee=? where ID=?"; try { PreparedStatement ps = conn.prepareStatement(sql); //3.���ò��� ps.setString(1,csr.getAccount_month()); ps.setString(2,csr.getCity_code()); ps.setString(3, csr.getProduct_code()); ps.setString(4, csr.getWrite_off_type_code()); ps.setString(5, csr.getWrite_off_fee()); ps.setString(6, csr.getUpdate_time()); //4.ִ��sql��� int x = ps.executeUpdate(); if(x>0){ return true; } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ DbUtils.closeConnection(); } return false; } //ɾ�� public boolean delete(String ACCOUNT_MONTH){ //1.��ȡ����' Connection conn = DbUtils.getConnection(); //2.Ԥ���� String sql = "delete from TEAM101_RP_PRE_FEE_GATHER_T_3 where ACCOUNT_MONTH=?"; try { PreparedStatement ps = conn.prepareStatement(sql); //3.���ò��� ps.setString(1,ACCOUNT_MONTH); //4.ִ��sql��� int x = ps.executeUpdate(); if(x>0){ return true; } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ DbUtils.closeConnection(); } return false; } //��������̬��ѯ public ArrayList<Pre_Fee_GatherRecord> query(Pre_Fee_GatherRecord csr){ ArrayList<String> params = new ArrayList<String>(); //����ж��Ǹ��ʲô������ѯ??? String Account_Month=csr.getWrite_off_type_code(); String City_code=csr.getCity_code(); String Product_code=csr.getProduct_code(); StringBuffer sql = new StringBuffer("select to_char(sale_date,'yyyy-mm-dd') as update_time,city_code,product_code from TEAM101_RP_PRE_FEE_GATHER_T_3 e,TEAM101_RP_PRE_FEE_GATHER_T_3_state s where e.state = s.skey"); //��̬ƴ�Ӳ�ѯ��� if(Account_Month !=null && !Account_Month .equals("")){ sql.append(" and Account_Month=?"); params.add(Account_Month); } if(City_code !=null && !City_code.equals("")){ sql.append(" and City_code=?"); params.add("%"+City_code +"%"); } if(Product_code !=null && !Product_code.equals("") && !Product_code.equals("0")){ sql.append(" and Product_code=?"); params.add(Product_code); } ArrayList<Pre_Fee_GatherRecord> csrs = new ArrayList<Pre_Fee_GatherRecord>(); Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; conn = DbUtils.getConnection(); try { ps = conn.prepareStatement(sql.toString()); //������δ��?���� for(int i=0;i<params.size();i++){ ps.setString(i+1, params.get(i)); } rs = ps.executeQuery(); //����rs while(rs.next()){ Pre_Fee_GatherRecord csrr = new Pre_Fee_GatherRecord(); csrr.setAccount_month("Account_Month"); csrr.setCity_code(rs.getString("City_code")); csrr.setProduct_code(rs.getString("Product_code")); csrr.setWrite_off_type_code(rs.getString("Write_off_type_code")); csrr.setWrite_off_fee(rs.getString("Write_off_fee")); csrr.setUpdate_time(rs.getString("Update_time")); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally{ DbUtils.closeConnection(); } return csrs; } public Pre_Fee_GatherRecord queryById(String id){ Pre_Fee_GatherRecord csr = null; Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; conn = DbUtils.getConnection(); String sql = "select to_char(birth,'yyyy-mm-dd') as birth from t_emp where empno=?"; try { ps = conn.prepareStatement(sql); ps.setString(1,id); rs = ps.executeQuery(); //����rs if(rs.next()){ csr = new Pre_Fee_GatherRecord(); csr.setAccount_month("Account_Month"); csr.setCity_code(rs.getString("City_code")); csr.setProduct_code(rs.getString("Product_code")); csr.setWrite_off_type_code(rs.getString("Write_off_type_code")); csr.setWrite_off_fee(rs.getString("Write_off_fee")); csr.setUpdate_time(rs.getString("Update_time")); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ DbUtils.closeConnection(); } return csr; } //遍历 public ArrayList<Pre_Fee_GatherRecord> display(){ ArrayList<String> params = new ArrayList<String>(); //����ж��Ǹ��ʲô������ѯ??? String sql = "select * from TEAM101_RP_CARD_SALE_5"; ArrayList<Pre_Fee_GatherRecord> csrs = new ArrayList<Pre_Fee_GatherRecord>(); Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; conn = DbUtils.getConnection(); try { ps = conn.prepareStatement(sql); rs = ps.executeQuery(); while(rs.next()){ Pre_Fee_GatherRecord csr = new Pre_Fee_GatherRecord(); csr.setAccount_month("Account_Month"); csr.setCity_code(rs.getString("City_code")); csr.setProduct_code(rs.getString("Product_code")); csr.setWrite_off_type_code(rs.getString("Write_off_type_code")); csr.setWrite_off_fee(rs.getString("Write_off_fee")); csr.setUpdate_time(rs.getString("Update_time")); csrs.add(csr); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally{ DbUtils.closeConnection(); } return csrs; } }