impl.java

是一个简单的图书馆管理系统

package com.yourcompany.struts.Impl;

import java.sql.Array;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import com.mysql.jdbc.PreparedStatement;
import com.yourcompany.struts.form.BookForm;
import com.yourcompany.struts.form.LoginForm;

import com.yourcompany.struts.form.Student_BookForm;

import data.Books;
import data.Student_Books;



public class Impl {
	private static Connection conn;

		public Impl(Connection conn){
			this.conn=conn;
		 
	 }
		public static class ConnDB {
			 public static Connection getconn() throws SQLException, ClassNotFoundException{
			  Connection conn=null;
			  try{
			   Class.forName("com.mysql.jdbc.Driver");
			   conn=(Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/library","root","123");
			   if(conn!=null)return conn;
			  }catch(ClassNotFoundException e){
				  e.printStackTrace();
			  }catch(SQLException e)
			  {
				  e.printStackTrace();
			  }
			  return conn;
			 }
			}
	  public static boolean check(LoginForm loginform) throws Exception {
			boolean flag = false;			
			ResultSet rs = null;			
			String sql = null;		
			sql = "SELECT s_no,password FROM student WHERE s_no=? and password=?";			
			try {			
				PreparedStatement	pstmt =  (PreparedStatement) conn.prepareStatement(sql);			
				pstmt.setString(1,loginform.getS_no());
				pstmt.setString(2, loginform.getPassword());			
				rs = pstmt.executeQuery();				
				if (rs.next()) {				
					if (rs.getString(1).equals(loginform.getS_no())
							&& rs.getString(2).equals(loginform.getPassword())) {
						flag = true;
					}
				}				
				rs.close();
				pstmt.close();
			} catch (Exception e) {
				 System.out.println("数据库链接错误2：" + e.getMessage());
				e.printStackTrace();
			} finally {				
		     conn.close();
			}
			return flag;
		}
	 
	  public static void RegStudent(LoginForm form) throws ClassNotFoundException{
			String DMLsql="insert into student values(?,?,?,?)";
			  try{
			
			   PreparedStatement pst=(PreparedStatement) conn.prepareStatement(DMLsql);
			   pst.setString(1,form.getS_no());
			   pst.setString(2,form.getPassword());
			   pst.setString(3,form.getS_name());
			   pst.setString(4,form.getS_dept());
			  
			   pst.executeUpdate();
			   pst.close();
			   conn.close();
			  }catch(SQLException e){
				  e.printStackTrace();
			  }
		}

	public static void InsertBook(BookForm form) {
		String DMLsql="insert into book values(?,?,?,?)";
		  try{		
		   PreparedStatement pst=(PreparedStatement) conn.prepareStatement(DMLsql);
		   pst.setString(1,form.getBook_no());
		   pst.setString(2,form.getB_name());
		   pst.setString(3,form.getAuthor());		 	
		   pst.setInt(4,(int)form.getNumber());
		   pst.executeUpdate();
		   pst.close();
		   conn.close();
		  }catch(SQLException e){
			  e.printStackTrace();
		  }
	}
	
	public List<Books> gerAll() {
		List<Books>list=new ArrayList<Books>();
		try {
			PreparedStatement ps= (PreparedStatement) conn.prepareStatement("Select * from book");
            ResultSet rs = ps.executeQuery();
            while(rs.next())
            		{Books books=new Books();
            		books.setBook_no(rs.getString(1));
            		books.setB_name(rs.getString(2));
            		books.setAuthor(rs.getString(3));
            		books.setNumber(rs.getInt(4));            			
            		list.add(books);
            	
            		}
            ps.close();
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
			
		return list;
	}
	
	public List<Student_Books> getAll() {
		List<Student_Books>list1=new ArrayList<Student_Books>();
		try {
			PreparedStatement ps= (PreparedStatement) conn.prepareStatement("Select * from student_book ");
            ResultSet rs = ps.executeQuery();
            while(rs.next())
            		{Student_Books s_books=new Student_Books();
            		s_books.setS_no(rs.getString(1));
            		s_books.setBook_no(rs.getString(2));
            		s_books.setB_name(rs.getString(3));
            		s_books.setBorrow_time(rs.getString(4));
            		s_books.setReturn_time(rs.getString(5));
            	      			
            		list1.add(s_books);
            	
            		}
            ps.close();
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
			
		return list1;
	}
	
	public List<Student_Books> getShowSB(String s_no) {
		List<Student_Books>list2=new ArrayList<Student_Books>();
		try {
			PreparedStatement ps= (PreparedStatement) conn.prepareStatement("Select * from student_book where s_no='"+s_no+"' ");
            ResultSet rs = ps.executeQuery();
            while(rs.next())
            		{Student_Books s_books=new Student_Books();
            		s_books.setS_no(rs.getString(1));
            		s_books.setBook_no(rs.getString(2));
            		s_books.setB_name(rs.getString(3));
            		s_books.setBorrow_time(rs.getString(4));
            		s_books.setReturn_time(rs.getString(5));  			
            		list2.add(s_books);
            	
            		}
            ps.close();
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
			
		return list2;
	}
	public static String ShowB_name(String book_no) throws SQLException{
		String 	sql = "SELECT b_name FROM book WHERE book_no='"+book_no +"'";
		PreparedStatement	pstmt =  (PreparedStatement) conn.prepareStatement(sql);		
		
		String book_name=null;
		System.out.println("书号2是:"+book_no);
		ResultSet rs  = pstmt.executeQuery();	
		if (rs.next()){	  		
	    book_name = rs.getString(1);
	    System.out.println("书名是:"+book_name);
		}     	  
	    rs.close();
	    pstmt.close();
		return  book_name;	
		
	}
	public static void BorrowBook(Student_BookForm form) throws SQLException {		
		  try{	
		   String DMLsql="insert into student_book values(?,?,?,?,?)";
		   PreparedStatement pst=(PreparedStatement) conn.prepareStatement(DMLsql);
		   pst.setString(1,form.getS_no());
		   pst.setString(2,form.getBook_no());
		   pst.setString(3,form.getB_name());
		   pst.setString(4, Impl.getDate());
		   pst.setString(5, Impl.getDate3());
		  
		   pst.executeUpdate();
		   pst.close();
		   conn.close();
		  }catch(SQLException e){
			  e.printStackTrace();
		  }
	}

	 public static void returnbook(Student_BookForm form ) throws Exception {
	       
			String	sqlStr = "delete from student_book where book_no='"+form.getBook_no()+"'and s_no='"+form.getS_no()+"'";
				try
				{ 
					Statement stm=conn.createStatement();
					stm.executeUpdate(sqlStr);
					stm.close();
		//			conn.close();
					
				}
				catch (SQLException e)
				 {
					System.out.println(e);
					
				 }
		        }
   
	 public static String getDate() {//得到当前日期时刻字符串
		 Calendar cal = Calendar.getInstance();
		 int month=(int)(cal.get(Calendar.MONTH))+1;
		 String date = cal.get(Calendar.YEAR) + "-" +month + "-" +cal.get(Calendar.DATE);
		 return date;
		 }

	 public static String getDate3() {//得到当前日期时刻字符串
		 Calendar cal = Calendar.getInstance();
		 int month=(int)(cal.get(Calendar.MONTH))+2;
		 int year= month/13;
		 int mod=month%13+1;
		 String date = cal.get(Calendar.YEAR)+year + "-" +mod + "-" +cal.get(Calendar.DATE);
		 return date;
		 }
 public static int searchB_number(Student_BookForm form) throws SQLException
 {  
	 int number=0;
	 String  SQL= "SELECT number from book where book_no='"+form.getBook_no()+"'";
 
	  Statement ps=(Statement) conn.createStatement(); 
	  ResultSet rs= ps.executeQuery(SQL);
	if (rs.next()){	  		
	    number = rs.getInt(1);
	    System.out.println("书的数量是:"+number);
		}     	  
	    rs.close();
	    ps.close();		 
 
     return   number;	
 }
	 public static void updateBook_no(int number, Student_BookForm form) throws SQLException{
		 
	  
		 String sql="UPDATE book set number=? where book_no=? " ;
		 PreparedStatement pst=(PreparedStatement) conn.prepareStatement(sql);
		   pst.setInt(1,+number-1);
		   pst.setString(2,form.getBook_no());
		   pst.executeUpdate();
		   pst.close();
	//	   conn.close();
	 }
	 public static void updateBook_no2(int number, Student_BookForm form) throws SQLException{
		 
		  
		 String sql="UPDATE book set number=? where book_no=? " ;
		 PreparedStatement pst=(PreparedStatement) conn.prepareStatement(sql);
		   pst.setInt(1,+number+1);
		   pst.setString(2,form.getBook_no());
		   pst.executeUpdate();
		   pst.close();
	//	   conn.close();
	 }
}