java 数据库 课程设计 图书馆管理信息系统

java 数据库 重点是实现了预约 违章等功能 public static int checkIt(String name, String password,String purview)// 验证用户名 密码 { interFace s = new interFace(); String temp = "select * from user_info " + "where user_name='" + name + "'" + " and " + "user_psw='" + password + "'" + " and " + "user_purview='" + purview + "'"; s.search(temp); int i = 0; try { while (s.C.next()) { i++; } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return i; } public static void update_book_number(String s4){ String sql="update book_info set book_num_in=book_num_in+1 where book_info.book_id='"+s4+"'"; try{ interFace.edit(sql); }catch(Exception e){ e.printStackTrace(); } } public static int book_num_in(String s){ String sql="select book_num_in from book_info where book_name= '"+s+"'"; int count=0; try{ rs=B.executeQuery(sql); if(rs.next()){ count=Integer.parseInt(rs.getString(1)); } } catch(Exception e){ e.printStackTrace(); } return count; } public static void day_number(String s1,String s2,int s3){ float days=0;String s=null; int mum=0; try{ SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd"); s = df.format(new Date()); String date=s1; Date d1 = df.parse(s); Date d2 = df.parse(date); long diff = d1.getTime() - d2.getTime(); days= diff / (1000 * 60 * 60 * 24); }catch(Exception e){ e.printStackTrace(); }System.out.println(days); days=days-30; if(s2.equals("是")){ days=days-30; } if(days>0){ days=days/10;} else{ days=0; } if(days>0){ write_dirty(s3,days);//写入不良信息 } try{//把borrow 中的borrow_returned 将return 改成"是" String ss="是"; String sql="update borrow_info set borrow_returned='"+ss+ "' , borrow_info.return_date= '"+s+"'where borrow_info.borrow_id='"+s3+"'"; interFace.edit(sql); }catch(Exception e ){ e.printStackTrace(); } } public static void write_dirty(int s,float days){ try{ int count=0; rs=B.executeQuery("select count(*) from dirty_info "); if(rs.next()) count=Integer.parseInt(rs.getString(1)); count++; String des="过期"+days*10+"天，罚款"+days+"元"; interFace.edit("insert into dirty_info values('"+ count+"','"+s+"','"+des+"') "); JOptionPane.showMessageDialog(null,des,"提示！",JOptionPane.PLAIN_MESSAGE); String descri=Adminfrm.jTextArea.getText(); if(descri.trim().equals(null)){ //do nothing } else{ count++; descri=descri+"罚款一元"; interFace.edit("insert into dirty_info values('"+ count+"','"+s+"','"+descri+"') "); JOptionPane.showMessageDialog(null,descri,"提示！",JOptionPane.PLAIN_MESSAGE); } } catch(Exception e ){ e.printStackTrace(); } } public int user_num(){ interFace s = new interFace(); String temp = "select * from user_info " ; s.search(temp); int i1 = 0; try { while (s.C.next()) { i1++; } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return i1; } public static int sum_borrow(){ int count=0; try{ String sql1="select max(borrow_id) from borrow_info"; rs=null; rs=B.executeQuery(sql1); while(rs.next()){ count= Integer.parseInt(rs.getString(1)); } } catch (Exception e ){ e.printStackTrace(); } count++; return count; } public static void edit(String command) { interFace s=new interFace (); s.C=s.search(command); return; } public static void book_inquire(Object [] [] data,String string1,String sql){ try{ rs=null; rs=B.executeQuery(sql); int i=0; initialize(data,30,9); while(rs.next()){ s1=rs.getString("book_id"); data[i][0]=s1; s2=rs.getString("book_name");data[i][1]=s2; s3=rs.getString("book_price").trim(); data[i][2] =s3; s4=rs.getString("book_publishing").trim();data[i][3]=s4; s5=rs.getString("book_author").trim();data[i][4]=s5; s6=rs.getString("book_date").trim();data[i][5]=s6; s7=rs.getString("book_num").trim();data[i][6]=s7; s8=rs.getString("book_num_in").trim();data[i][7]=s8; s9=rs.getString("lib_name");data[i][8]=s9; i++; } } catch(Exception e ){ e.printStackTrace(); } } public static String[][] doSearch(String command, String[] colName) { interFace s = new interFace(); String temp = command; s.C = s.search(temp); int col = colName.length, i = 0; s.rec = new String[100][col]; try { while (s.C.next()) { for (int j = 1; j <= col; j++) { s.rec[i][j - 1] = s.C.getString(j); } i++; s.i++; } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } String[][] result = new String[s.i][col]; for (int k = 0; k < s.i; k++) { for (int l = 0; l < col; l++) result[k][l] = s.rec[k][l]; } return result; } public static void inquire_history(String string1){ String m=string1; String sql="select * from borrow_info ,book_info,user_info where borrow_info.borrow_bid=book_info.book_id and borrow_uid=user_id and user_name='"+m+"'"; try{ rs=null; rs=B.executeQuery(sql); // System.out.println(rs); int i=0; initialize(Userfrm.rowData1,30,9); while(rs.next()){ if(!rs.getString("borrow_returned").trim().equals("否")){ s1=rs.getString("borrow_id"); Userfrm.rowData1[i][0]=s1; s2=rs.getString("borrow_uid");Userfrm.rowData1[i][1]=s2; s3=rs.getString("borrow_bid").trim(); Userfrm.rowData1[i][2]=s3; s4=rs.getString("book_name").trim();Userfrm.rowData1[i][3]=s4; s5=rs.getString("borrow_time").trim();Userfrm.rowData1[i][4]=s5; s6=rs.getString("borrow_reborrow").trim(); Userfrm.rowData1[i][5]=s6; s7=rs.getString("borrow_returned").trim(); Userfrm.rowData1[i][6]=s7; s8=rs.getString("return_date").trim();Userfrm.rowData1[i][7]=s8;} else{ continue; } i++; } } catch(Exception e ){ e.printStackTrace(); } } public static void inquire_current_history(String string1){ String m=string1; String sql="select * from borrow_info ,book_info,user_info where borrow_info.borrow_bid=book_info.book_id and borrow_uid=user_id and user_name='"+m+"'"; try{ rs=null; rs=B.executeQuery(sql); // System.out.println(rs); int i=0; initialize(Userfrm.rowData4,30,9); while(rs.next()){ if(rs.getString("borrow_returned").trim().equals("否")){ s1=rs.getString("borrow_id"); Userfrm.rowData4[i][0]=s1; s2=rs.getString("borrow_uid");Userfrm.rowData4[i][1]=s2; s3=rs.getString("borrow_bid").trim(); Userfrm.rowData4[i][2]=s3; s4=rs.getString("book_name").trim();Userfrm.rowData4[i][3]=s4; s5=rs.getString("borrow_time").trim();Userfrm.rowData4[i][4]=s5; s6=rs.getString("borrow_reborrow").trim(); Userfrm.rowData4[i][5]=s6; s7=rs.getString("borrow_returned").trim(); Userfrm.rowData4[i][6]=s7; s8=rs.getString("return_date").trim(); Userfrm.rowData4[i][7]=s8; } else{ continue; } i++; } } catch(Exception e ){ e.printStackTrace(); } } public static void inquire_weizhang(String string1){ String sql="select * from book_info ,borrow_info,dirty_info where book_info.book_id=borrow_info.borrow_bid " + "and borrow_info.borrow_id=dirty_info.dirty_borrow_id"; try{ rs=null; rs=B.executeQuery(sql); int i=0; initialize(Userfrm.rowData2,13,6); /**if(rs==null){ System.out.println("恭喜您，没有不良记录，请继续保持"); }*/ while(rs.next()){ s1=rs.getString("dirty_id"); Userfrm.rowData2[i][0]=s1; s2=rs.getString("borrow_id"); Userfrm.rowData2[i][1]=s2; s3=rs.getString("borrow_bid"); Userfrm.rowData2[i][2]=s3; s4=rs.getString("book_name"); Userfrm.rowData2[i][3]=s4; s5=rs.getString("borrow_time"); Userfrm.rowData2[i][4]=s5; s6=rs.getString("dirty_description"); Userfrm.rowData2[i][5]=s6; i++; } } catch(Exception e ){ e.printStackTrace(); } } //得出uid号 public static void book_request(String string1){ int count=0; int uid = 0; String s="是"; try{ String sql="select count (*) from request_info "; rs=null; rs=B.executeQuery(sql); while(rs.next()){ count= Integer.parseInt(rs.getString(1)); } String username=Login.jTextField.getText(); String sql1="select user_id from user_info where user_name='"+username+"'"; rs=null; rs=B.executeQuery(sql1); //System.out.println(rs); while(rs.next()){ uid= Integer.parseInt(rs.getString(1)); } Calendar cal=Calendar.getInstance(); String date=Integer.toString(cal.get(Calendar.YEAR))+"-"+ Integer.toString(cal.get(Calendar.MONTH)+1)+"-"+ Integer.toString(cal.get(Calendar.DATE)); count++; sql="insert into request_info values ( '"+count+"','"+uid+"','"+string1+"','"+date+"','"+s+"' )"; interFace.edit(sql); } catch (Exception e){ e.printStackTrace(); } } public static void yuyue_history(){ String username=Login.jTextField.getText(); int uid=0; String sql1="select user_id from user_info where user_name='"+username+"'"; try{rs=null; rs=B.executeQuery(sql1); //System.out.println(rs); while(rs.next()){ uid= Integer.parseInt(rs.getString(1)); } String sql="select * from request_info where request_uid ='"+uid+"'"; rs=null;int i=0; rs=B.executeQuery(sql); initialize(Userfrm.rowData3,10,5); // System.out.println(rs); while(rs.next()){ s1=rs.getString("request_id"); Userfrm.rowData3[i][0]=s1; s2=rs.getString("request_uid"); Userfrm.rowData3[i][1]=s2; s3=rs.getString("request_description"); Userfrm.rowData3[i][2]=s3; s4=rs.getString("request_date"); Userfrm.rowData3[i][3]=s4; s5=rs.getString("request_finished"); Userfrm.rowData3[i][4]=s5; i++; } } catch (Exception e){ e.printStackTrace(); } } public static void information(){ //System.out.println(Login.jTextField.getText()); String m=Login.jTextField.getText(); String sql="select * from user_info where user_name= '"+m+"'"; try{ rs=null; rs=B.executeQuery(sql); while(rs.next()){ s1=rs.getString("user_name"); Userfrm.jTextField3.setText(s1); s2=rs.getString("user_psw"); Userfrm.jTextField4.setText(s2); s3=rs.getString("user_age"); Userfrm.jTextField5.setText(s3); s4=rs.getString("user_sex"); Userfrm.jTextField6.setText(s4); } } catch (Exception e){ e.printStackTrace(); } } public static void info_modify(String s1,int a ,String b){ String m=Login.jTextField.getText(); String sql="update user_info set user_psw='"+s1 +"',user_age='"+a+"' , user_sex='"+b+"' where user_name='"+m+"'"; try{ interFace.edit(sql); } catch (Exception e ){ e.printStackTrace(); } } public static int whichlib(String s){ int w=0; try{ String sql1="select * from lib_info where lib_name='"+s+"'"; rs=null; rs=B.executeQuery(sql1); while(rs.next()){ w= Integer.parseInt(rs.getString(1)); } } catch (Exception e ){ e.printStackTrace(); } return w; } public static int book_sum(){ int count=0; try{ String sql1="select count(*) from book_info"; rs=null; rs=B.executeQuery(sql1); while(rs.next()){ count= Integer.parseInt(rs.getString(1)); } } catch (Exception e ){ e.printStackTrace(); } count++; return count; } public static void insert_book(String sql){ try{ rs=null; interFace.edit(sql); } catch (Exception e ){ e.printStackTrace(); } } public static void user_inquire(String sql){ try{ int i=0; rs=B.executeQuery(sql); while(rs.next()){ s1=rs.getString(2); Adminfrm.rowdata1[i][0]=s1; s2=rs.getString(10); Adminfrm.rowdata1[i][1]=s2; s3=rs.getString(5); Adminfrm.rowdata1[i][2]=s3; s4=rs.getString(6); Adminfrm.rowdata1[i][3]=s4; s5=rs.getString(7); Adminfrm.rowdata1[i][4]=s5; s6=rs.getString(8); Adminfrm.rowdata1[i][5]=s6; i++; } } catch (Exception e ){ e.printStackTrace(); } } public static void weizhang(){ String sql="select * from dirty_info"; try{ int i=0; rs=B.executeQuery(sql); initialize(Adminfrm.rowdata3,15,3); while(rs.next()){ s1=rs.getString(1); Adminfrm.rowdata3[i][0]=s1; s2=rs.getString(2);Adminfrm.rowdata3[i][1]=s2; s3=rs.getString(3).trim(); Adminfrm.rowdata3[i][2]=s3; i++; } } catch(Exception e ){ e.printStackTrace(); } } public static Boolean notrequest(String s){ Boolean re=false; String sql="select * from request_info where request_description='"+s+"' and request_finished='否'"; try{ rs=B.executeQuery(sql); if(!(rs.next())){ re=true; } }catch(Exception e){ e.printStackTrace(); } return re; } public static void request(){ String sql="select * from request_info"; try{ int i=0; rs=B.executeQuery(sql); initialize(Adminfrm.rowdata2,15,5); while(rs.next()){ s1=rs.getString(1); Adminfrm.rowdata2[i][0]=s1; s2=rs.getString(2);Adminfrm.rowdata2[i][1]=s2; s3=rs.getString(3).trim(); Adminfrm.rowdata2[i][2]=s3; s4=rs.getString(4).trim();Adminfrm.rowdata2[i][3]=s4; s5=rs.getString(5).trim();Adminfrm.rowdata2[i][4]=s5; i++; } } catch(Exception e ){ e.printStackTrace(); } } @SuppressWarnings("deprecation") public static void book_reborrow(String s){ String sql=null; String sql1=null; String username=Login.jTextField.getText(); //得出用户ID号 int u_id=0; String b_id=null; sql="select book_id from book_info where book_name='"+s+"'"; sql1="select user_id from user_info where user_name='"+username+"'"; try{ rs=null; rs=B.executeQuery(sql1); //System.out.println(rs); while(rs.next()){ u_id= Integer.parseInt(rs.getString(1)); } rs=null; rs=B.executeQuery(sql); if(rs.next()) b_id=rs.getString(1).trim(); int count=book_num_in(s); if((count>0)||(count==0&¬request(s) )){ String date; sql="select borrow_reborrow,return_date from borrow_info where borrow_bid='"+b_id+"' and borrow_uid='"+u_id+"'"; rs=B.executeQuery(sql); if(rs.next()){ if (rs.getString(1).trim().equals("否")){ //System.out.println(rs.getString(1)); date=rs.getString("return_date"); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); Date d=sdf.parse(date); System.out.println(d.toString()); /*String date=Integer.toString(cal.get(Calendar.YEAR))+"-"+ Integer.toString(cal.get(Calendar.MONTH))+"-"+ Integer.toString(cal.get(Calendar.DATE));*/ Calendar cal = new GregorianCalendar(); cal.setTime(d); String d1=Integer.toString(cal.get(Calendar.YEAR))+"-"+ Integer.toString(cal.get(Calendar.MONTH)+2)+"-"+ Integer.toString(cal.get(Calendar.DATE)); //System.out.println(d.getYear()+" "+d.getDay()); System.out.println(d1); sql="update borrow_info set borrow_reborrow= '是' , return_date='"+d1+"' where borrow_bid='"+b_id+"' and borrow_uid='"+u_id+"'"; System.out.println(sql); interFace.edit(sql); JOptionPane.showMessageDialog(null,"续借成功","提示！",JOptionPane.PLAIN_MESSAGE); } else JOptionPane.showMessageDialog(null,"已经续借，不得续借","提示！",JOptionPane.PLAIN_MESSAGE); } } else{ JOptionPane.showMessageDialog(null,"不得续借"+count + notrequest(s),"提示！",JOptionPane.PLAIN_MESSAGE); } }catch(Exception e){ e.printStackTrace(); } } @SuppressWarnings("static-access") public static void book_borrow(String b_id,int u_id){ interFace s=new interFace(); String sql="select count(*)from request_info where (request_finished='否' and request_uid<>'"+u_id+"' and " + " request_description in (select book_info.book_name " + "from book_info where book_id='"+b_id+"'))"; try{ rs=null;int count=0;int count1=0; rs=B.executeQuery(sql); if(rs.next()){ count=rs.getInt(1); rs=B.executeQuery("select book_num_in from book_info where book_id='"+b_id+"'"); if(rs.next()) count1=Integer.parseInt(rs.getString(1)); if(count>=count1||count1==0){System.out.println(count + " "+count1); JOptionPane.showMessageDialog(null,"全部书被预约或部借出","提示！",JOptionPane.PLAIN_MESSAGE); } else{ sql="update request_info set request_finished='是' where (request_uid='"+u_id+"' and " + "request_info.request_description=(select book_info.book_name " + "from book_info where book_id='"+b_id+"'))"; s.edit(sql); Calendar cal=Calendar.getInstance(); String date=Integer.toString(cal.get(Calendar.YEAR))+"-"+ Integer.toString(cal.get(Calendar.MONTH)+1)+"-"+ Integer.toString(cal.get(Calendar.DATE)); cal.add(Calendar.DATE, 30); String date1=Integer.toString(cal.get(Calendar.YEAR))+"-"+ Integer.toString(cal.get(Calendar.MONTH)+1)+"-"+ Integer.toString(cal.get(Calendar.DATE)); int num=s.sum_borrow(); sql="insert into borrow_info values('"+num+"','"+u_id+"','"+b_id+"','"+date+"','否','否','"+date1+"')"; s.edit(sql); sql="update book_info set book_info.book_num_in=book_info.book_num_in-1 " + "where book_id='"+b_id+"'"; s.edit(sql); JOptionPane.showMessageDialog(null,"成功借出","提示！",JOptionPane.PLAIN_MESSAGE); } } } catch (Exception e){ e.printStackTrace(); } } 课程设计 图书馆管理信息系统