dbo.java

Java Swing写的图书馆管理系统

package library.DBConnectOperation;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import library.model.Book;
import library.model.BookType;
import library.model.BorrowBookEntity;
import library.model.Operator;
import library.model.Reader;
import library.model.ReaderType;

public class Dbo {
	//protected static String dbClassName="sun.jdbc.odbc.JdbcOdbcDriver";
	protected static String dbClassName="com.microsoft.jdbc.sqlserver.SQLServerDriver";
	protected static String dbUrl="jdbc:microsoft:sqlserver://localhost:1433;databasename=db_library";   //设置数据库服务器URL和数据源名称
	protected static String dbUser="sa";
	protected static String dbPassword="";
	//protected static String second=null;
	private static Connection conn=null;
	private Dbo(){
		try{
			if(conn==null){
				Class.forName(dbClassName).newInstance();
				conn=DriverManager.getConnection(dbUrl,dbUser,dbPassword);
			}
			else
				return;
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	
	public static ResultSet executeQuery(String sql){		//Query Function
		try{
			if(conn==null)
				new Dbo();
			return
			conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE).executeQuery(sql);		
//在多用户情况下，ResultSet.TYPE_SCROLL_SENSITIVE表示可以滚动查询并且支持显示其他用户更新（用于并发）ResultSet.CONCUR_UPDATABLE（支持结果集更新）
		}
		catch(final SQLException e){
			e.printStackTrace();
			return null;
		}
	}
	public static int executeUpdate(String sql){
		try{
			if(conn==null)
			new Dbo();
			return conn.createStatement().executeUpdate(sql);
		}catch(final SQLException e){
			e.printStackTrace();
			return -1;
		}
	}
	public static void close(){	//close the database connection
		try{
			conn.close();
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			conn=null;
		}
	}
	
	//验证系统管理员登陆
	public static Operator check(String name,String password){
		Operator operator=new Operator();
		String sql="Select * from tb_operator where name='"+name
		+"' and password='"+password+"'";
		ResultSet rs=Dbo.executeQuery(sql);
		try{
			if(rs.next()){
				operator.setId(rs.getInt("operatorID"));
				operator.setName(rs.getString("name"));
				operator.setGrade(rs.getInt("admin"));
				operator.setPassword(rs.getString("password"));
				
			}
		}catch(Exception e){
				e.printStackTrace();
		}
		Dbo.close();
		return operator;
	}
	//书号查询方法，由AddBook类和BorrowBook类、ModifuAndDelBook类调用,
	public static String selectBookISBN(String ISBN){
		String sql="select ISBN from tb_bookinfo where ISBN='"
			+ISBN+"'";
		ResultSet rs=Dbo.executeQuery(sql);
		try{
			if(rs.next()){
				return rs.getString("ISBN");
			}
			else return null;
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}
	
	//查询图书类别的方法，有AddBook类调用
	public static ArrayList selectBookCategory(){
		ArrayList arrayList=new ArrayList();
		String sql="select * from tb_bookType";
		ResultSet rs=Dbo.executeQuery(sql);
		try{
			while(rs.next()){
				BookType bookType=new BookType();
				bookType.setId(Integer.parseInt(rs.getString("id")));
				bookType.setTypeName(rs.getString("typeName"));
				bookType.setDays(Integer.parseInt(rs.getString("days")));
				bookType.setFk(Integer.parseInt(rs.getString("fk")));
				bookType.setExpired(Integer.parseInt(rs.getString("expire")));
				arrayList.add(bookType);	//将元素加入数组链表中
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		Dbo.close();
		return arrayList;
	}
	//查询图书类别名称的方法，由AddBookType类调用
	public static String selectBookTypeName(String bookTypeName){
		String sql="select typeName from tb_bookType where typeName='"+bookTypeName+"'";
		ResultSet rs=Dbo.executeQuery(sql);
		try{
			if(rs.next())
			return rs.getString("typeName");
			else return null;
			}catch(Exception e){
				e.printStackTrace();
				return null;
		}
	}
	//查询借阅证编号，由类AddReader和BorrowBook类调用，由ModiFyandDelReader调用,由类ReturnBook调用
	public static String selectReaderId(String id){
		String sql="select id from tb_reader where id='"+id+"'";
		ResultSet rs=Dbo.executeQuery(sql);
		try{
			if(rs.next())
				return rs.getString("id");
			else return null;
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}
	//查询读者类型，由AddReader类调用，由ModiftAndDelReaderType调用
	public static ArrayList selectReaderType(){
		String sql="select * from tb_readerType";
		ResultSet rs=Dbo.executeQuery(sql);
		ArrayList arrayList=new ArrayList();
		try{
			while(rs.next()){
				ReaderType readerType=new ReaderType();
				readerType.setTypeId(rs.getInt("typeId"));
				readerType.setTypeName(rs.getString("typeName"));
				readerType.setMaxBorrowNumber(rs.getInt("maxBorrowNumber"));
				readerType.setmaxKeepDays(rs.getInt("maxKeepDays"));
				arrayList.add(readerType);
			}
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
		Dbo.close();
		return arrayList;
	}
	//查询某读者类型名称的方法
	public static String selectReaderTypeName(String readerTypeName){
		String sql="select typeName from tb_readerType where typeName='"+readerTypeName+"'";
		ResultSet rs=Dbo.executeQuery(sql);
		try{
			if(rs.next())
			return rs.getString("typeName");
			else return null;
			}catch(Exception e){
				e.printStackTrace();
				return null;
		}
	}
	//查询管理员昵称，由类BorrowBook调用
	public static String selectOperatorName(String name){
		String sql="select name from tb_operator where name='"+name+"'";
		ResultSet rs=Dbo.executeQuery(sql);
		try{
			if(rs.next())
				return rs.getString("name");
			else return null;
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}
	//查询读者信息,有类BorrowBook调用,由ModifyAndDelReader类调用
	public static Reader selectReader(String readerId){
		String sql="select * from tb_reader where id='"+readerId+"'";
		ResultSet rs=Dbo.executeQuery(sql);
		try{
			if(rs.next()){
				Reader reader=new Reader();
				reader.setAvailableNumber(rs.getInt("availableNumber"));
				reader.setBirthday(rs.getString("birthday"));
				reader.setBzTime(rs.getString("bztime"));
				reader.setCareer(rs.getString("career"));
				reader.setCertificateType(rs.getString("certificateType"));
				reader.setIdentityCardNumber(rs.getString("identityCardNumber"));
				reader.setKeepMoney(rs.getInt("keepmoney"));
				reader.setName(rs.getString("name"));
				reader.setReaderId(rs.getString("id"));
				reader.setSex(rs.getString("sex"));
				reader.setTel(rs.getString("tel"));
				reader.setTypeId(rs.getInt("typeid"));
				Dbo.close();
				return reader;
			}
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
		return null;
	}
	//查询图书信息，由类BorrowBook和ModifuAndDelBook类调用
	public static Book selectBook(String ISBN){
		String sql="select * from tb_bookinfo where ISBN='"+ISBN+"'";
		ResultSet rs=Dbo.executeQuery(sql);
		try{
			if(rs.next()){
				Book book=new Book();
				book.setBookname(rs.getString("bookname"));
				book.setDate(rs.getString("date"));
				book.setISBN(rs.getString("ISBN"));
				book.setPrice(rs.getFloat("price"));
				book.setPublisher(rs.getString("publisher"));
				book.setTypeid(rs.getInt("id"));
				book.setWriter(rs.getString("writer"));
				book.setTranslator(rs.getString("translator"));
				Dbo.close();
				return book;
			}
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
		return null;
	}
	//查询指定ID的图书类别名称，由类BorrowBook调用
	public static String selectBookTypeName(int id){
		String sql="select typeName from tb_bookType where id="+id;
		ResultSet rs=Dbo.executeQuery(sql);
		try{
			if(rs.next()){
				return rs.getString("typeName");
			}
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
		return null;
	}
	//查询指定ID的图书类别,有类ModifyAndDelBook调用
	public static BookType selectSelectedBookCategory(int id){
		String sql="select * from tb_bookType where id="+id;
		ResultSet rs=Dbo.executeQuery(sql);
		try{
			if(rs.next()){
				BookType bookType=new BookType();
				bookType.setId(Integer.parseInt(rs.getString("id")));
				bookType.setTypeName(rs.getString("typeName"));
				bookType.setDays(Integer.parseInt(rs.getString("days")));
				bookType.setFk(Integer.parseInt(rs.getString("fk")));
				bookType.setExpired(Integer.parseInt(rs.getString("expire")));
				return bookType;
			}
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
		Dbo.close();
		return null;
	}
	//查询某读者所述的读者类所允许的最大借阅图书天数,由类BorrowBook调用
	public static int selectKeepDaysofReader(int id){
		String sql="select maxKeepDays from tb_readerType where typeid="+id;
		ResultSet rs=Dbo.executeQuery(sql);
		try{
			if(rs.next()){
				return rs.getInt("maxKeepDays");
			}
			else return 0;
		}catch(Exception e){
			e.printStackTrace();
			return 0;
		}
	}
	//统计某读者的已借阅的图书数目
	public static int selectBorrowNumber(String readerid){
		String sql="select count(*) from tb_borrow where readerid='"+readerid+"'";
		ResultSet rs=Dbo.executeQuery(sql);
		try {
			if(rs.next()){
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;