表样分配规则的数据封装实现

来源：互联网发布：快手伴奏软件下载编辑：程序博客网时间：2024/05/01 01:13

//表样分配规则insert load

public final class SsSheetAllotDao {

   private static final String SQL_INSERT = "INSERT INTO SSSHEETALLOT(SSKeyID,SSSheetId)VALUES(?,?)";
   private static final String SQL_UPDATE = "UPDATE SSSHEETALLOT SET SSSheetId=? WHERE SSKeyID=?";
   private static final String SQL_DELETE = "DELETE FROM SSSHEETALLOT WHERE SSSheetId=?";
   public static final String SQL_QUERY = "SELECT a.sskeyid||a.sssheetid AS F_ID,a.sskeyid,a.sssheetid,t.strsscustomertypename,e.strsssheetname FROM SSSHEETALLOT a " +
   "left join sscustomertype t on a.sskeyid=t.lngsscustomertypeid " +
   "left join sssheet e on a.sssheetid=e.sssheetid";
   private static final String SQL_INSERT_F = "insert into ssfileprop (ssfileid,sspropdefineid,strvalue,lngvalueid) values (?,?,?,?)";
   private static final String SQL_DELETE_F = "delete from ssfileprop where ssfileid=?";
   //数据库引擎
   private static DbEngine dbEngine;

   static {
   dbEngine = DbUtils.getDb();
   }

   //插入到数据库

   /**
   *
   * @param bean
   * @param fileid ssfile.ssfileid
   * @param menu 页面的单选按钮可用表单分配表单
   * @param typesheetid 单位类型的ID和表样类型的ID 组合的字符串
   * such as 101:83d1,3851,8352,|104:83d1,3851,|
   * @throws Exception
   */
   public static void insert(SsSheetAllotBean bean, String fileid, String menu, String typesheetid) throws Exception {
   dbEngine.beginTrans();
   try {

   String sspropdefineid = "";
   if (menu == "0" && "0".equals(menu)) {
   sspropdefineid = getPropdefineid("可用全部表单");
   } else {
   String customertypeid = "";
   sspropdefineid = getPropdefineid("表单分配准则");
   String[] tempid = typesheetid.split("\\|");
   for (int k = 0; k < tempid.length; k++) {
   String[] typeid = tempid[k].split("\\:");
   customertypeid = typeid[0];
   String[] sheetid = typeid[1].split(",");
   for (int j = 0; j < sheetid.length; j++) {
   if (null != sheetid[j] && !"".equals(sheetid[j]) && !"-".equals(sheetid[j])) {
   dbEngine.update(SQL_INSERT, customertypeid, sheetid[j]);
   }
   }
   }

   dbEngine.update(SQL_INSERT_F, fileid, sspropdefineid, customertypeid, 0);

   }

   dbEngine.commit();
   } catch (Exception e) {
   dbEngine.rollback(e);
   }
   }

   //更新数据库
   public static void update(SsSheetAllotBean bean, String fileid, String menu, String typesheetid) throws Exception {
   dbEngine.beginTrans();
   try {
   dbEngine.update(SQL_DELETE_F, fileid);
   String sspropdefineid = "";
   String customertypeid = "";
   if (menu == "0" && "0".equals(menu)) {
   sspropdefineid = getPropdefineid("可用全部表单");
   } else {
   sspropdefineid = getPropdefineid("表单分配准则");
   String[] tempid = typesheetid.split("\\|");
   for (int k = 0; k < tempid.length; k++) {
   String[] typeid = tempid[k].split("\\:");
   customertypeid = typeid[0];
   String[] sheetid = typeid[1].split("\\,");
   for (int j = 0; j < sheetid.length; j++) {
   if (null != sheetid[j] && !"".equals(sheetid[j]) && !"-".equals(sheetid[j])) {
   dbEngine.update(SQL_DELETE, sheetid[j]);
   dbEngine.update(SQL_INSERT, customertypeid, sheetid[j]);
   }

   }
   }
   dbEngine.update(SQL_INSERT_F, fileid, sspropdefineid, customertypeid, 0);
   }

   dbEngine.commit();
   } catch (Exception e) {
   dbEngine.rollback(e);
   }
   }

   //删除
   public static void delete(String id) throws Exception {
   dbEngine.beginTrans();
   try {
   dbEngine.update(SQL_DELETE_F, id);

   dbEngine.query("select sssheetid from sssheet where ssfileid = ?",
   new IResultSetExtractor<String>() {
   public String extractData(ResultSet resultSet) throws Exception {
   UtilRs urs = new UtilRs(resultSet);
   while (urs.next()) {
   dbEngine.update(SQL_DELETE, urs.getStringIgnoreNull("sssheetid"));
   }
   return null;
   }
   }, id);
   dbEngine.commit();
   } catch (Exception e) {
   dbEngine.rollback(e);
   }
   }

   //加载指定id的bean
   public static SsSheetAllotBean load(String sId) throws Exception {
   return dbEngine.query(SQL_QUERY + " where e.ssfileid=? ",
   new IResultSetExtractor<SsSheetAllotBean>() {
   public SsSheetAllotBean extractData(ResultSet resultSet) throws SQLException {
   UtilRs urs = new UtilRs(resultSet);
   String resutl = "";
   String temp;
   ArrayList list=new ArrayList();
   Iterator it = null;
   while (urs.next()) {
   resutl = urs.getStringIgnoreNull("sssheetid");
   temp = urs.getStringIgnoreNull("sskeyid");
   boolean pd=true;
   for(int i=0;i<list.size();i++){
   if(list.get(i).toString().indexOf(temp+":")==0){
   list.set(i,list.get(i).toString()+","+resutl);
   pd=false;
   break;
   }
   }

   if(pd){
   list.add(temp+":"+resutl);
   }
   }
   it = list.iterator();
   resutl="";
   while(it.hasNext()){
   String entry=(String)it.next();
   resutl+=entry+"|";
   }
   SsSheetAllotBean bean = new SsSheetAllotBean();
   bean.setTypesheetid(resutl);
   return bean;
   }
   }, sId);
   }

   //加载所有bean
   public static void loadAll(final IEnumWorker<SsSheetAllotBean> worker) throws Exception {
   dbEngine.query(SQL_QUERY,
   new IResultSetExtractor<SsSheetAllotBean>() {
   public SsSheetAllotBean extractData(ResultSet resultSet) throws SQLException {
   UtilRs urs = new UtilRs(resultSet);
   try {
   while (urs.next()) {
   SsSheetAllotBean node = new SsSheetAllotBean();
   node.readFromRs(urs);
   worker.doWork(node);
   }
   } catch (Exception e) {
   throw new SQLException(e);
   }
   return null;
   }
   });
   }

   protected static String getPropdefineid(String code) throws Exception {
   return dbEngine.query("select sspropdefineid from sspropdefine where strpropcode = ?",
   new IResultSetExtractor<String>() {
   public String extractData(ResultSet resultSet) throws SQLException {
   UtilRs urs = new UtilRs(resultSet);
   if (urs.next()) {
   return urs.getStringIgnoreNull("sspropdefineid");
   }
   return null;
   }
   }, code);
   }

}