mydb.java

简单的书店管理系统,是课设期间教师指导完成的,希望会对大家有帮助!

            return bool;
        }

    public boolean hasUser(String userName)
    {
        boolean bool = true;
        String sql = "select count(*) from users where userName=?";
        try
        {
            prepar = conn.prepareStatement(sql);
            prepar.setString(1, userName);
            res = prepar.executeQuery();
            res.next();
            int flag = res.getInt(1);
            if (flag > 0)
            {
                bool = true;
            }
            else
            {
                bool = false;
            }
        }
        catch (SQLException ex)
        {
            ex.printStackTrace();
            bool = true;
        }

        return bool;
    }

    public BookBean getBookBean(String bookId)
    {
        BookBean bbean = new BookBean();
        String sql = "select * from information where BookId=?";
        try
        {
            prepar = conn.prepareStatement(sql);
            prepar.setString(1, bookId);
            res = prepar.executeQuery();
            while (res.next())
            {
                bbean.setBookCompany(res.getString("BookCompany"));
                bbean.setBookCount(1);
                bbean.setBookData(res.getDate("BookDate"));
                bbean.setBookId(Integer.parseInt(bookId));
                bbean.setBookImage(res.getString("BookImage"));
                bbean.setBookName(res.getString("BookName"));
                bbean.setBookPenster(res.getString("BookPenster"));
                bbean.setBookPrice(res.getInt("BookPrice"));
                bbean.setBookSell(res.getInt("BookSell"));
                bbean.setBookStep(res.getInt("BookStep"));
                bbean.setBookStorage(res.getInt("BookStorage"));
                bbean.setBookSynopsis(res.getString("BookSynopsis"));
                bbean.setBookType(this.queryType("" + res.getInt("BookType")));
                bbean.setBookAllPrice(bbean.getBookPrice());
            }
        }
        catch (SQLException ex)
        {
            ex.printStackTrace();
            return null;
        }
        return bbean;
    }

    public String queryType(String typeId)
    {
        try
        {

            java.sql.PreparedStatement prepar1 = conn.prepareStatement("select Display from Type where TypeId=?");
            prepar1.setInt(1, Integer.parseInt(typeId));
            ResultSet res1 = prepar1.executeQuery();
            res1.next();
            return res1.getString("Display");
        }
        catch (SQLException ex)
        {

            return null;
        }
    }


    public int getPageCount(int page, int bookNum, int bookType, int bookStep)
    {
        int count = 0;
        try
        {
            proc = conn.prepareCall("{call proc_page (?,?,?,?,?)}");

            proc.setInt(1, page);
            proc.setInt(2, bookNum);
            proc.setInt(3, bookType);
            proc.setInt(4, bookStep);
            proc.registerOutParameter(5, Types.INTEGER);
            proc.execute();
            count = proc.getInt(5);

        }
        catch (SQLException ex)
        {
            ex.printStackTrace();
        }
        return count;
    }


    public ArrayList getPageData(int page, int bookNum, int bookType, int bookStep) //取出分页的记录
    {
        ArrayList bookList = new ArrayList(); //返回的分页的List数据
        System.out.println(bookNum + "    ");
        //需要填充的bookBean book的信息
        try
        {
            proc = conn.prepareCall("{call proc_page(?,?,?,?,?)}"); //存储过程 分页 proc_page(?,?,?,?)
            proc.setInt(1, page); //参数1 第几页
            proc.setInt(2, bookNum); //参数2 多少个结果
            proc.setInt(3, bookType); //参数3 书的类型
            proc.setInt(4, bookStep);
            proc.registerOutParameter(5, Types.INTEGER); //OUTPUT参数 返回结构共多少页
            res = proc.executeQuery(); //接收存储过程的结果集
            while (res.next()) //提取结果集的每条记录(主要问题在这里!!!!!!)
            {

                BookBean bbean = new BookBean();
                bbean.setBookName(res.getString("bookName"));
                bbean.setBookId(res.getInt("BookId"));

                bbean.setBookPenster(res.getString("BookPenster"));
                bbean.setBookCompany(res.getString("BookCompany"));
                bbean.setBookData(res.getDate("BookDate"));
                bbean.setBookImage(res.getString("BookImage"));

                bbean.setBookPrice(res.getInt("BookPrice"));
                bbean.setBookSell(res.getInt("BookSell"));
                bbean.setBookStep(res.getInt("BookStep"));
                bbean.setBookStorage(res.getInt("BookStorage"));
                bbean.setBookSynopsis(res.getString("BookSynopsis"));
                bbean.setBookType(queryType("" + res.getInt("BookType")));
                /*   */
                bookList.add(bbean); //加入到booklist
            }
        }
        catch (SQLException ex)
        {
            ex.printStackTrace();
        }
        return bookList;

    }

    public int getSearchCountPage()
    {
        return this.pagecount;
    }

    public int getSearchCountdata()
    {
        return this.pagedata;
    }


    public ArrayList getSearchPage(int page, int bookNum, String action, String searchkey, String stepid, String typeid)
    {

        ArrayList bookList = new ArrayList();
        try
        {
            proc = conn.prepareCall("{call proc_searchpage(?,?,?,?,?,?,?,?)}");
            proc.setInt(1, page); //参数1 第几页
            proc.setInt(2, bookNum); //参数2 多少个结果
            proc.setInt(3, Integer.parseInt(typeid)); //参数3 书的类型
            proc.setInt(4, Integer.parseInt(stepid));
            proc.setString(5, searchkey);
            proc.setString(6, action);
            proc.registerOutParameter(7, Types.INTEGER); //OUTPUT参数 返回结构共多少页
            proc.registerOutParameter(8, Types.INTEGER);
            res = proc.executeQuery(); //接收存储过程的结果集
            while (res.next()) //提取结果集的每条记录(主要问题在这里!!!!!!)
            {
                BookBean bbean = new BookBean();
                bbean.setBookId(res.getInt("BookId"));
                bbean.setBookName(res.getString("BookName"));
                bbean.setBookPenster(res.getString("BookPenster"));
                bbean.setBookCompany(res.getString("BookCompany"));
                bbean.setBookData(res.getDate("BookDate"));
                bbean.setBookImage(res.getString("BookImage"));
                bbean.setBookPrice(res.getInt("BookPrice"));
                bbean.setBookSell(res.getInt("BookSell"));
                bbean.setBookStep(res.getInt("BookStep"));
                bbean.setBookStorage(res.getInt("BookStorage"));
                bbean.setBookSynopsis(res.getString("BookSynopsis"));
                bbean.setBookType(queryType("" + res.getInt("BookType")));
                /*   */
                bookList.add(bbean); //加入到booklist
            }
            pagecount = proc.getInt(7);
            pagedata = proc.getInt(8);
        }
        catch (SQLException ex)
        {
            ex.printStackTrace();
        }

        return bookList;
    }

    public String getDate()
    {
        SimpleDateFormat lFormat;
        Calendar MyDate = Calendar.getInstance();
        MyDate.setTime(new java.util.Date());
        java.util.Date date = MyDate.getTime();
        //格式可以自己根据需要修改
        lFormat = new SimpleDateFormat("yyyyMMddHHmmss");
        String gRtnStr = lFormat.format(date);
        return gRtnStr;
    }

    public boolean insertOrder(OrderFormBean ofb)
    {
        int flag = 0;
        try
        {
            prepar = conn.prepareStatement("insert into OrderForm(UserId,BookId,amount,OrderData,money) values(?,?,?,?,?)");
            prepar.setInt(1, ofb.getUserId());
            prepar.setInt(2, ofb.getBookId());
            prepar.setInt(3, ofb.getAmount());
            prepar.setString(4, ofb.getOrderData());
            prepar.setInt(5, ofb.getMoney());
            flag = prepar.executeUpdate();
        }
        catch (SQLException ex)
        {
            ex.printStackTrace();
        }
        if (flag > 0)
        {
            return true;
        }
        else
        {
            return false;
        }
    }

    //关闭连接
    public void Close()
    {
        try
        {
            if (res != null)
            {
                res.close();
            }
            if (prepar != null)
            {
                prepar.close();
            }
            if (conn != null)
            {
                conn.close();
            }
        }
        catch (SQLException ex)
        {
            ex.printStackTrace();
        }
        System.out.println("关闭成功");
    }

}