op_cd.java

JSP在线CD销售系统管理员登录后

     } else {
      if (!classid.equals("")){
           sqlStr = "select top "+ pageSize+" from (select top "+(recordCount-pageSize * (page-1)) +"from ("+sqlStr + " and a.cdclass='" +
           classid + "' and (upper(a.cdname) like '%" +
           keyword+ "%' or a.musiclist like '%" +
           keyword + "%') ))";
      } else {
            sqlStr = "select top "+ pageSize+" from (select top "+(recordCount-pageSize * (page-1)) +"from ("+sqlStr + " and (upper(a.cdname) like '%" +
            keyword+ "%' or a.musiclist like '%" +
            keyword + "%') ))";
            }
                        }
  } else {//非查询，也非分类浏览
    if (page == 1) {
      sqlStr = sqlStr + "  order by a.Id desc ";
    }
    else {
      sqlStr = "select top " + pageSize + " from (select top " +
          (recordCount - pageSize * (page - 1)) + "from (" + sqlStr + "))";
    }
  }
        try  {

         // System.out.println(sqlStr);

              rs = stmt.executeQuery(sqlStr);



              cdlist = new Vector(rscount);
              while (rs.next()){
                                cd cd = new cd();
                                cd.setId(rs.getLong("id"));
                                cd.setcdName(rs.getString("cdname"));
                                cd.setcdClass(rs.getInt("cdclass"));
                                cd.setClassname(rs.getString("classname"));
                                cd.setSinger(rs.getString("Singer"));
                                cd.setPublish(rs.getString("publish"));
                                cd.setcdNo(rs.getString("cdno"));
                                cd.setmusiclist(rs.getString("musiclist"));
                                cd.setPrice(rs.getFloat("Price"));
                                cd.setAmount(rs.getInt("amount"));
                                cd.setLeav_number(rs.getInt("leav_number"));
                                cd.setRegTime(rs.getString("regtime"));
                                cd.setPicture(rs.getString("picture"));
                                cdlist.addElement(cd);
                        }
                        close();
                        return true;
                }catch (Exception e){
                        System.out.println(e.getMessage());
                        return false;
                }
        }
        /**
         * 完成CD添加
         * @return
         * @throws java.lang.Exception
         */
 public boolean insert() throws Exception {
  sqlStr = "insert into cd (cdname,cdclass,Singer,Publish,cdno,"+
           "musiclist,Price,Amount,Leav_number,Regtime,picture) values ('";
           sqlStr = sqlStr + dataFormat.toSql(acds.getcdName()) + "','";
           sqlStr = sqlStr + acds.getcdClass() + "','";
           sqlStr = sqlStr + dataFormat.toSql(acds.getSinger()) + "','";
           sqlStr = sqlStr + dataFormat.toSql(acds.getPublish()) + "','";
           sqlStr = sqlStr + dataFormat.toSql(acds.getcdNo()) + "','";
           sqlStr = sqlStr + dataFormat.toSql(acds.getmusiclist()) + "','";
           sqlStr = sqlStr + acds.getPrice() + "','";
           sqlStr = sqlStr + acds.getAmount() + "','";
           sqlStr = sqlStr + acds.getAmount() + "',";
           sqlStr = sqlStr + "GETDATE()"+ ",'";
           sqlStr = sqlStr + acds.getPicture()+"')";
                try{
                        System.out.print(sqlStr);
                        DataBase db = new DataBase();
                        db.connect();
                        stmt =db.conn.createStatement ();
                        stmt.execute(sqlStr);
                        return true;
                }catch (SQLException sqle){
                  System.out.print(sqle.getMessage());
                        return false;
                }
        }
        /**
         * 完成CD修改
         * @return
         * @throws java.lang.Exception
         */
public boolean update() throws Exception {
  sqlStr = "update cd set ";
  sqlStr = sqlStr + "cdname = '" + dataFormat.toSql(acds.getcdName()) + "',";
  sqlStr = sqlStr + "cdclass = '" + acds.getcdClass() + "',";
  sqlStr = sqlStr + "Singer = '" + dataFormat.toSql(acds.getSinger()) + "',";
  sqlStr = sqlStr + "publish = '" + dataFormat.toSql(acds.getPublish()) + "',";
  sqlStr = sqlStr + "cdno = '" + dataFormat.toSql(acds.getcdNo()) + "',";
  sqlStr = sqlStr + "musiclist = '" + dataFormat.toSql(acds.getmusiclist()) + "',";
  sqlStr = sqlStr + "Price = '" + acds.getPrice() + "',";
  sqlStr = sqlStr + "Amount = '" + acds.getAmount() + "',";
  sqlStr = sqlStr + "picture = '" + acds.getPicture() + "' ";
  sqlStr = sqlStr + "where id = " + acds.getId();
                try{
                        DataBase db = new DataBase();
                        db.connect();
                        stmt =db.conn.createStatement ();
                        stmt.execute(sqlStr);
                        return true;
                } catch (SQLException e){
                        System.out.print(e.getMessage());
                        return false;
                }

        }
        /**
         * 完成CD删除
         * @param aid
         * @return
         * @throws java.lang.Exception
         */
        public boolean delete( int aid ) throws Exception {
                sqlStr = "delete from cd where id = "  + aid ;
                try
                {         DataBase db = new DataBase();
                          db.connect();
                          stmt =db.conn.createStatement ();
                          stmt.execute(sqlStr);
                         return true;
                }
                catch (SQLException e)
                {
                        System.out.println(e);
                        return false;
                }
              }
              /**
               * 完成CD单本查询，用于支持页面的查看CD详细资料
               * @param newid
               * @return
               * @throws java.lang.Exception
               */
 public boolean getOnecd(int newid ) throws Exception {
     DataBase db = new DataBase();
     db.connect();
     stmt =db.conn.createStatement ();
     try {
        sqlStr="select  a.id,a.cdname,a.cdclass,b.classname,a.Singer,"+
        "a.publish,a.cdno,a.musiclist,a.Price,a.amount,a.Leav_number,"+
        "a.regtime,a.picture from cd a,cdclass b where a.cdclass="+
        "b.Id and a.Id = " + newid ;
                        rs = stmt.executeQuery(sqlStr);
                        if (rs.next())
                        {	cdlist = new Vector(1);
                                cd cd = new cd();
                                cd.setId(rs.getLong("id"));
                                cd.setcdName(rs.getString("cdname"));
                                cd.setcdClass(rs.getInt("cdclass"));
                                cd.setClassname(rs.getString("classname"));
                                cd.setSinger(rs.getString("Singer"));
                                cd.setPublish(rs.getString("publish"));
                                cd.setcdNo(rs.getString("cdno"));
                                cd.setmusiclist(rs.getString("musiclist"));
                                cd.setPrice(rs.getFloat("Price"));
                                cd.setAmount(rs.getInt("amount"));
                                cd.setLeav_number(rs.getInt("leav_number"));
                                cd.setRegTime(rs.getString("regtime"));
                                cd.setPicture(rs.getString("picture"));
                                cdlist.addElement(cd);
                        } else {
                                close();
                                return false;
                        }
                        close();
                        return true;
                }
                catch (SQLException e)
                {
                        return false;
                }
        }
        public int getPage() {				//显示的页码
                return page;
        }
        public void setPage(int newpage) {
                page = newpage;
        }
        public int getPageSize(){			//每页显示的CD数
                return pageSize;
        }
        public void setPageSize(int newpsize) {
                pageSize = newpsize;
        }
        public int getPageCount() {				//页面总数
                return pageCount;
        }
        public void setPageCount(int newpcount) {
                pageCount = newpcount;
        }
        public long getRecordCount() {
                return recordCount;
        }
        public void setRecordCount(long newrcount) {
                recordCount= newrcount;
        }
  public op_cd() {
       }
}