2020 12 2

图书管理系统，dao层：

package bean;
import java.sql.*;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.*;
public class dao {
    public Connection getConnection()
    {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        String url = "jdbc:mysql://localhost:3306/test?serverTimezone=GMT%2B8";
        String username = "root";
        String password="123456";
        Connection a=null;
        try {
            a = DriverManager.getConnection(url,username,password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return a;
    }
    public boolean checkM(String name,String password) throws SQLException
    {
        Connection coon=getConnection();
        String sql="select* from manager";
        Statement stmt=coon.createStatement();
        ResultSet rs=stmt.executeQuery(sql);
        int TEMP=0;
        while(rs.next())
        {
            if(name.equals(rs.getString("name"))&&password.equals(rs.getString("password")))
                {coon.close();return true;}
        }
        return false;
    }
    public boolean checkR(String name,String password) throws SQLException
    {
        Connection coon=getConnection();
        String sql="select* from readerpass";
        Statement stmt=coon.createStatement();
        ResultSet rs=stmt.executeQuery(sql);
        while(rs.next())
        {
            if(name.equals(rs.getString("num"))&&password.equals(rs.getString("password")))
            {coon.close();return true;}
        }
        return false;
    }
    public boolean addReader(reader p,String password) throws SQLException
    {
        Connection coon=getConnection();
        int row,temp=0;
        String sql1="insert into readerpass(num,password)values(?,?)";
        String sql2="insert into mybook(name,book,date1,date2)values(?,?,?,?)";
        String sql="insert into reader(num,name,sex,school)values(?,?,?,?)";
        PreparedStatement b,c,d;
        try {
            b = coon.prepareStatement(sql);
            b.setString(1,p.getNum());
            b.setString(2,p.getName());
            b.setString(3,p.getSex());
            b.setString(4,p.getSchool());
            row=b.executeUpdate();
            if(row<0) temp=1;
            b.close();
        } catch (SQLException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }
        try {
            c = coon.prepareStatement(sql1);
            c.setString(1,p.getNum());
            c.setString(2,password);
            row=c.executeUpdate();
            if(row<0) temp=1;
            c.close();
        } catch (SQLException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }
        try {
            d = coon.prepareStatement(sql2);
            d.setString(1,p.getName());
            d.setString(2,"");
            d.setString(3,"");
            d.setString(4,"");
            row=d.executeUpdate();
            if(row<0) temp=1;
            d.close();
        } catch (SQLException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }
        coon.close();
        if(temp==0) return true;
        else return false;
    }
    public boolean addBook(book p) throws SQLException
    {
        Connection coon=getConnection();
        int row,temp=0;
        String sql="insert into book(ID,name,writter,home,num)values(?,?,?,?,?)";
        PreparedStatement b;
        try {
            b = coon.prepareStatement(sql);
            b.setString(1,p.getID());
            b.setString(2,p.getName());
            b.setString(3,p.getWritter());
            b.setString(4,p.getHome());
            b.setString(5,p.getNum());
            row=b.executeUpdate();
            if(row<0) temp=1;
            b.close();
        } catch (SQLException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }
        coon.close();
        if(temp==0) return true;
        else return false;
    }
    public reader Rlook(String num) throws SQLException
    {
        reader q=new reader();
        Connection coon=getConnection();
        Statement stmt=coon.createStatement();
        String sql1="select* from reader";
        ResultSet rs=stmt.executeQuery(sql1);
        while(rs.next())
        {
            String namep=rs.getString("num");
            if(num.equals(namep)==true)
            {
                q.setNum(rs.getString("num"));
                q.setName(rs.getString("name"));
                q.setSex(rs.getString("sex"));
                q.setSchool(rs.getString("school"));
                break;
            }
        }
        rs.close();
        stmt.close();
        coon.close();
        return q;
    }
    public List<book> findb() throws SQLException
    {
        List<book> list=new ArrayList<book>();
        Connection coon=getConnection();
        String sql="select* from book";
        Statement stmt=coon.createStatement();
        ResultSet rs=stmt.executeQuery(sql);
        while(rs.next())
        {
            book a=new book();
            a.setID(rs.getString("ID"));
            a.setName(rs.getString("name"));
            a.setWritter(rs.getString("writter"));
            a.setHome(rs.getString("home"));
            a.setNum(rs.getString("num"));
            list.add(a);
        }
        rs.close();
        stmt.close();
        coon.close();
        return list;
    }
    public List<book> findb1(String A,String B) throws SQLException
    {
        List<book> list=new ArrayList<book>();
        Connection coon=getConnection();
        String sql="select * from book where "+A+" like '%"+B+"%'";
        Statement stmt=coon.createStatement();
        ResultSet rs=stmt.executeQuery(sql);
        while(rs.next())
        {
            book a=new book();
            a.setID(rs.getString("ID"));
            a.setName(rs.getString("name"));
            a.setWritter(rs.getString("writter"));
            a.setHome(rs.getString("home"));
            a.setNum(rs.getString("num"));
            list.add(a);
        }
        rs.close();
        stmt.close();
        coon.close();
        return list;
    }
    public void borrow(String ID,reader a,String b,String d) throws SQLException
    {
        Connection coon=getConnection();
        String sql="select* from book";
        String sql3="select* from mybook";
        Statement stmt=coon.createStatement();
        Statement stmt1=coon.createStatement();
        ResultSet rs=stmt.executeQuery(sql);
        ResultSet rs1=stmt1.executeQuery(sql3);
        while(rs.next())
        {
            System.out.println(rs.getString("ID"));
            if(rs.getString("ID").equals(ID)&&!rs.getString("num").equals("0"))
            {
                String sql1="update book set num=? where ID=?";
                PreparedStatement a1=coon.prepareStatement(sql1);
                String name2=Integer.toString(Integer.parseInt(rs.getString("num"))-1);
                a1.setString(1, name2);
                a1.setString(2, ID);
                a1.executeUpdate();
                a1.close();
                String name5=rs.getString("name");
                rs.close();
                while(rs1.next())
                {
                    if(rs1.getString("name").equals(a.getName()))
                    {
                        String sql2="update mybook set book=?,date1=?,date2=? where name=?";
                        PreparedStatement a2=coon.prepareStatement(sql2);
                        String name3=rs1.getString("book");
                        String name4=name3+name5+"+";
                        String name7=rs1.getString("date1")+b+"+";
                        String name6=rs1.getString("date2")+d+"+";
                        a2.setString(1, name4);
                        a2.setString(2, name7);
                        a2.setString(3, name6);
                        a2.setString(4, a.getName());
                        a2.executeUpdate();
                        a2.close();
                        rs1.close();
                        break;
                    }
                }
                break;
            }
        }
        stmt.close();
        stmt1.close();
        coon.close();
    }
    public String lasttime(String A)
    {
        String t[]=A.split("\-");
        int d[]= {31,28,31,30,31,30,31,31,30,31,30,31};
        int a=Integer.parseInt(t[0]);
        int b=Integer.parseInt(t[1]);
        int c=Integer.parseInt(t[2]);
        if(c%4==0)
        {
            d[1]=29;
        }
        for(int i=1;i<=90;i++)
        {
            if(a==d[b-1])
            {
                a=0;
                b++;
            }
            if(b==13)
            {
                b=1;
                c++;
            }
            a++;    
        }
        A=Integer.toString(a)+"-"+Integer.toString(b)+"-"+Integer.toString(c);
        return A;
    }
    public List<book> findbp(String name) throws SQLException
    {
        List<book> list=new ArrayList<book>();
        List<book> list2=new ArrayList<book>();
        Connection coon=getConnection();
        LocalDate date = LocalDate.now();
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("dd-MM-yyyy");
        String time1 = date.format(formatter);
        String sql="select* from mybook";
        String sql1="select* from book";
        Statement stmt=coon.createStatement();
        Statement stmt1=coon.createStatement();
        ResultSet rs=stmt.executeQuery(sql);
        ResultSet rs1=stmt1.executeQuery(sql1);
        while(rs1.next())
        {
                book a=new book();
                a.setID(rs1.getString("ID"));
                a.setName(rs1.getString("name"));
                a.setWritter(rs1.getString("writter"));
                a.setHome(rs1.getString("home"));
                a.setNum(rs1.getString("num"));
                list.add(a);
        }
        while(rs.next())
        {
            
            if(rs.getString("name").equals(name))
            {
                
                String A[]=rs.getString("date2").split("\+");
                String B[]=rs.getString("book").split("\+");
                for(int i=0;i<A.length;i++)
                {
                
                    if(!time(time1,A[i]))
                    {
                        for(book O:list)
                        {
                
                            if(O.getName().equals(B[i]))
                            {
                                list2.add(O);
                            }
                        }
                    }
                }
            }
        }
        rs1.close();
        rs.close();
        stmt.close();
        stmt1.close();
        coon.close();
        return list2;
    }
    public List<book> findbm(String name) throws SQLException
    {
        List<book> list=new ArrayList<book>();
        List<book> list2=new ArrayList<book>();
        Connection coon=getConnection();
        String sql="select* from mybook";
        String sql1="select* from book";
        Statement stmt=coon.createStatement();
        Statement stmt1=coon.createStatement();
        ResultSet rs=stmt.executeQuery(sql);
        ResultSet rs1=stmt1.executeQuery(sql1);
        while(rs1.next())
        {
                book a=new book();
                a.setID(rs1.getString("ID"));
                a.setName(rs1.getString("name"));
                a.setWritter(rs1.getString("writter"));
                a.setHome(rs1.getString("home"));
                a.setNum(rs1.getString("num"));
                list.add(a);
        }
        while(rs.next())
        {
            if(rs.getString("name").equals(name))
            {
                String name1[]=rs.getString("book").split("\+");
                for(int i=0;i<name1.length;i++)
                {
                    for(book O:list)
                    {
                        if(O.getName().equals(name1[i]))
                        {
                            list2.add(O);
                        }
                    }
                }
            }
        }
        rs.close();
        rs1.close();
        stmt.close();
        stmt1.close();    
        coon.close();
        return list;
    }
    public List<book> findbp1() throws SQLException
    {
        LocalDate date = LocalDate.now();
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("dd-MM-yyyy");
        String time1 = date.format(formatter);
        List<book> list=new ArrayList<book>();
        List<book> list2=new ArrayList<book>();
        String name=new String();
        Connection coon=getConnection();
        String sql="select* from mybook";
        String sql1="select* from book";
        Statement stmt=coon.createStatement();
        Statement stmt1=coon.createStatement();
        ResultSet rs=stmt.executeQuery(sql);
        ResultSet rs1=stmt1.executeQuery(sql1);

        while(rs1.next())
        {
                book a=new book();
                a.setID(rs1.getString("ID"));
                a.setName(rs1.getString("name"));
                a.setWritter(rs1.getString("writter"));
                a.setHome(rs1.getString("home"));
                a.setNum(rs1.getString("num"));
                a.setReader("wite");
                list.add(a);
        }
    
        while(rs.next())
        {
            if(rs.getString("book")!=null)
            {
                String A[]=rs.getString("date2").split("\+");
                String B[]=rs.getString("book").split("\+");
                for(int i=0;i<A.length;i++)
                {
                
                    if(!time(time1,A[i]))
                    {
                        System.out.println("1");
                        for(book O:list)
                        {
                            if(O.getName().equals(B[i]))
                            {
                                System.out.println(rs.getString("name"));
                                O.setReader(rs.getString("name"));
                                list2.add(O);
                            }
                        }
                    }
                }
            }
            else break;
        }

        rs.close();
        rs1.close();
        stmt.close();
        stmt1.close();    
        coon.close();
        return list2;
    }
    public void huan(String ID,reader a) throws SQLException
    {
        String name="";
        String num="";
        String bookname="";
        String date1="";
        String date2="";
        Connection coon=getConnection();
        String sql="select* from book";
        String sql1="select* from mybook";
        Statement stmt=coon.createStatement();
        Statement stmt1=coon.createStatement();
        ResultSet rs=stmt.executeQuery(sql);
        ResultSet rs1=stmt1.executeQuery(sql1);
        while(rs.next())
        {
            if(rs.getString("ID").equals(ID))
            {
                name=rs.getString("name");
                num=rs.getString("num");
            }
        }
        while(rs1.next())
        {
            if(rs1.getString("name").equals(a.getName()))
            {
                String A[]=rs1.getString("date2").split("\+");
                String B[]=rs1.getString("book").split("\+");
                String C[]=rs1.getString("date1").split("\+");
                for(int i=0;i<B.length;i++)
                {
                    if(!name.equals(B[i]))
                    {
                        bookname=bookname+B[i]+"+";
                        date1=date1+C[i]+"+";
                        date2=date2+A[i]+"+";
                    }
                }
            }
            String Sql="update book set num=? where ID=?";
            PreparedStatement a1=coon.prepareStatement(Sql);
            String name2=Integer.toString(Integer.parseInt(num)+1);
            a1.setString(1, name2);
            a1.setString(2, ID);
            a1.executeUpdate();
            String sql2="update mybook set book=?,date1=?,date2=? where name=?";
            PreparedStatement a2=coon.prepareStatement(sql2);
            a2.setString(1, bookname);
            a2.setString(2, date1);
            a2.setString(3, date2);
            a2.setString(4, a.getName());
            a2.executeUpdate();
            a2.close();
            a1.close();
        }
        rs.close();
        rs1.close();
        stmt.close();
        stmt1.close();
        coon.close();
    }
    public boolean time(String A,String B)
    {
        String a[]=A.split("\-");
        String b[]=B.split("\-");
        int a1=Integer.parseInt(a[0]);
        int a2=Integer.parseInt(a[1]);
        int a3=Integer.parseInt(a[2]);
        int b1=Integer.parseInt(b[0]);
        int b2=Integer.parseInt(b[1]);
        int b3=Integer.parseInt(b[2]);
        if(a3>b3) return false;
        else if(a3==b3&&a2>b2) return false;
        else if(a3==b3&&a2==b2&&a1>b1) return false;
        else return true;
    }
}