图书管理系统，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;
}
}