recorddao.java

我做的第一个java MIS项目

package server.dao;

import java.sql.*;

import server.entity.*;
import server.entity.Time;

public class Recorddao
{
    private Connection con = null;
    private Connectionmanager cm=null;
    private PreparedStatement ps = null;
    private ResultSet rs = null;

    //借出业务
    public int cdout(Card cardtemp,Cd cdtemp)
    {
        //初始化变量
        String testcardid=cardtemp.getcardid();
        String testcardpwd=cardtemp.getcardpwd();
        String testcdid=cdtemp.getcdid();

        //创建一个连接并修改记录
        cm = new Connectionmanager();
        con = cm.getconnection();
        try
        {
            //检查客户帐户和密码有效性
            ps = con.prepareStatement("select cardpwd from card where cardid='" +testcardid+ "'");
            rs=ps.executeQuery();
            rs.next();
            String pwd=rs.getString(1);
            if (!pwd.equals(testcardpwd)){return 1;}

            //检查光碟编号是否有效
            ps = con.prepareStatement("select cdid from cd where cdid='" +testcdid + "'");
            rs=ps.executeQuery();
            String cdhas="";
            while(rs.next())
            {cdhas=rs.getString(1);}
            if (cdhas.equals("")){return 2;}

            //检查客户帐户借出数量是否达到上限
            ps = con.prepareStatement("select reccardid from record where reccardid=" +testcardid +"and recendtime='1970-01-01'");
            rs = ps.executeQuery();
            int cdoutnum = 0;
            while (rs.next()) {cdoutnum++;}
            if (cdoutnum >= Sysconfig.getcdoutmaxnum()) {return 3;}

            //检查客户帐户是否还可以借阅
            ps = con.prepareStatement("select cardrestnum from card where cardid='" +testcardid+ "'");
            rs=ps.executeQuery();
            rs.next();
            int testcardrestnum=0;
            testcardrestnum=rs.getInt(1);
            if (testcardrestnum<=0){return 4;}

            //检查碟片是否在铺
            ps = con.prepareStatement("select cdexist from cd where cdid='" +testcdid + "'");
            rs = ps.executeQuery();
            rs.next();
            int cdexist = 0;
            cdexist = rs.getInt(1);
            if (cdexist == 0) {return 5;}

            //调用借出业务
               //查找record表中recid中最大项
               int maxrecid=0;int maxtemprecid=0;
               ps = con.prepareStatement("select recid from record");
               rs=ps.executeQuery();
               while(rs.next())
               {
                   maxtemprecid=rs.getInt(1);
                   if (maxrecid<maxtemprecid){maxrecid=maxtemprecid;}
               }
               maxrecid++;
               //设置一个当前时间
               String loctime=new Time().getloctime();
               //增加一条借出记录（设置借出时间为当前时间，设置归还时间为null）
               String strtempsql="insert record values ("+maxrecid+",'"+testcardid+"','"+testcdid+"','"+loctime+"','1970-01-01')";
               ps = con.prepareStatement(strtempsql);
               ps.execute();
               //修改相应CD的记录（cdexist置为0）
               ps = con.prepareStatement("update cd set cdexist=0 where cdid='"+testcdid+"'");
               ps.execute();
               //修改card表相应记录（cardrestnum减1）
               ps = con.prepareStatement("select cardrestnum from card where cardid='"+testcardid+"'");
               rs=ps.executeQuery();
               rs.next();
               int temprestnum=rs.getInt(1);
               temprestnum--;
               ps = con.prepareStatement("update card set cardrestnum="+temprestnum+" where cardid='"+testcardid+"'");
               ps.execute();
           } catch (SQLException ex) {}
        //断开数据连接
        finally
        {
            cm.closestatement(ps);
            cm.closeconnection(con);
        }
        return 6;
    }

    //归还业务
    public int cdin(Cd cdtemp) {
        //初始化变量
        String testcdid = cdtemp.getcdid();
        String endtime = new Time().getloctime();
        boolean outtime=false;
        //创建一个连接并得到查询结果
        cm = new Connectionmanager();
        con = cm.getconnection();
        try {
            //检查碟片编号的有效性
            ps = con.prepareStatement("select cdid from cd where cdid='" +testcdid + "'");
            rs = ps.executeQuery();
            String cdhas="";
            while(rs.next())
            {cdhas= rs.getString(1);}
            if (cdhas.equals("")) {
                return 1;
            }
            //检查碟片是否借出
            ps = con.prepareStatement("select cdexist from cd where cdid='" +testcdid + "'");
            rs = ps.executeQuery();
            rs.next();
            int cdexist = rs.getInt(1);
            if (cdexist == 1) {
                return 2;
            }

            //设置临时的recid
            ps=con.prepareStatement("select recid from record where reccdid='"+testcdid+"' and recendtime='1970-01-01'");
            rs=ps.executeQuery();
            while(rs.next())
            {temp.setrecid(rs.getString(1));}


            //检查碟片是否超期
            String strchkdate="update temp set t=(select datediff(day,recstarttime,getdate()) from record where reccdid='"+testcdid+"' and recendtime='1970-01-01') select * from temp";
            ps = con.prepareStatement(strchkdate);
            rs = ps.executeQuery();
            int overterm=0;
            while(rs.next())
            {overterm=java.lang.Integer.parseInt(rs.getString(1));}
            if (overterm>0)
            {outtime=true;}
            else
            {outtime=false;}

            //碟片归还操作
            ps = con.prepareStatement("update record set recendtime='" +endtime + "' where reccdid='" + testcdid +"' and recendtime='1970-01-01'");
            ps.execute();
            ps = con.prepareStatement("update cd set cdexist=1 where cdid ='" + testcdid +"' ");
            ps.execute();

            //写入处罚表
            if(outtime)
            {
                String forfei="insert penalty values ("+temp.getrecid()+","+overterm*Sysconfig.getforfei()+" )  select * from penalty";
                ps = con.prepareStatement(forfei);
                ps.execute();
            }
        } catch (SQLException ex) {}
        //断开数据连接
        finally
        {
            cm.closeresultset(rs);
            cm.closestatement(ps);
            cm.closeconnection(con);
        }
        if(outtime)
        {return 3;}
        else
        {return 4;}

    }

    //得到最大的rec编号
    public int getmaxrecid() {
        //初始化变量
        int maxrecid=0;
        int t=0;
        //创建一个连接并得到查询结果
        cm =new Connectionmanager();
        con= cm.getconnection();
        try
        {
            ps = con.prepareStatement("select recid from record");
            rs = ps.executeQuery();
            while (rs.next())
            {
                t=rs.getInt(1);
                if (maxrecid<t)
                {maxrecid=t;}
            }
        }
        catch (SQLException ex) {}
        //断开数据连接
        finally
        {
            cm.closeresultset(rs);
            cm.closestatement(ps);
            cm.closeconnection(con);
        }
        //返回值
        {return maxrecid;}
    }
}