dbsql.java

一个简单的图书馆的管理系统,该系统主要是针对学校的图书馆而做的

package librarymanagement.dao.common;

public interface DbSql {
  /**
   * 雇員操作sql
   *
   */
  public static final String MANAGER_SELECT_ALL = "select * from manager";
  /*employee_id,employee_name,employee_sex,department_name," +
  "job_name, to_char(hire_date,'yyyy-mm-dd'), salary,mail,mobile
  */  
  public static final String MANAGER_PASSWORD_SELECT = "select manager_password from manager";
  public static final String WHERE = " where ";
  public static final String BY_NAME = " manager_name = ?";

  public static final String UPDATE_READER_NOTRETURN_NUMBER="update reader set not_return_num=not_return_num+1 where reader_id=?";
  //向借阅表中加入数据
  public static final String INSERTBORROW_BOOK_INFO="insert into reader_info values(?,?,?,?,to_date(?,'yyyy-mm-dd'),to_date(?,'yyyy-mm-dd'))";
  //借到书后书表中在馆数量减去一
  public static final String BOOKNUMBER_DECREASE="update book set in_amount=in_amount-1 where book_id=?";
  //从读者表和书表分别找出借书所需信息
  public static final String SELECT_BOOK_AND_READER ="select book_id,book_name,reader_id,reader_name,acd,condition,book_location,not_return_num" +
  		                        ",price from book cross join reader where reader_id=? and book_id=?";
  //找出读者借书信息表中的数据
  public static final String SELECT_READER_BORROW_INFO =" select * from reader_info where book_id=? and reader_id=?";
  //续借图书
  public static final String UPDATE_RETUTN_DATE="update reader_info set should_return_date=to_date(?,'yyyy-mm-dd') where book_id=?";
  //搜索輸入的圖書存放位置
  public static final String SECLECT_BOOK_LOCATION="select * from book_location where location_name=?";
  //根据读者借书信息表中的读者编号和所借图书编号查找具体信息
  public static final String SELECT_BOOKLEND_COUNT_DETAILS="select * from reader_info";
  //按照图书类型查找图书
  public static final String SELECT_BOOK_BY_KIND="select book_id,book_name,kind,author,publis_com,book_location,book_amount," +
  		                     "price,edition,in_amount from book where kind=?";
  //按照图书编号查找图书
  public static final String SELECT_BOOK_BY_ID="select book_id,book_name,kind,author,publis_com,book_location,book_amount," +
  		                     "price,edition,in_amount from book where book_id=?";
  //按照图书名称模糊查询图书
  public static final String SELECT_BOOK_BY_NAME="select book_id,book_name,kind,author,publis_com,book_location,book_amount," +
    "price,edition,in_amount from book where book_name like '%?%'";
  //按照圖書作者查詢
  public static final String SELECT_BOOK_BY_AUTHOR="select book_id,book_name,kind,author,publis_com,book_location,book_amount," +
    "price,edition,in_amount from book where author=?";
  //根据图书名称和种类查询图书
  public static final String SELECT_BOOK_BY_NAME_AND_KIND="select book_id,book_name,kind,author,publis_com,book_location,book_amount," +
    "price,edition,in_amount from book where book_name like '&?%' and kind=?";
  //根据图书名称和作者查询图书
  public static final String SELECT_BOOK_BY_NAME_AND_AUTHOR="select book_id,book_name,kind,author,publis_com,book_location,book_amount," +
    "price,edition,in_amount from book where book_name like '%?%' and author=?";
  //根据图书作者和种类查询图书
  public static final String SELECT_BOOK_BY_AUTHOR_AND_KIND="select book_id,book_name,kind,author,publis_com,book_location,book_amount," +
    "price,edition,in_amount from book where author=? and kind=?";
  //根据所获得信息查找读者信息
  public static final String READER_SELECT_ALL_BY_ID ="select * from reader where reader_id = ? ";
  //查找读者曾今借阅的书籍的本应还日期
  public static final String SELECT_FORMER_SHOULD_RETURN_DATE="select should_return_date from reader_info where book_id=?";
  
  //重新设置图书馆信息
  public static final String UPDATE_LIBRARY_INFO="update  libraryinfoset set name=?,address=?,mail=?,tel=?,sponsorman=?,fajin_perday=?," +
  		                                     " num_limit=?,date_limit=?,reborrow_date=?";
  
  //查找出所有的图书馆信息
  public static final String SELECT_LIBRARY_INFO="select * from libraryinfoset";
  //获得所有读者的信息
  public static final String READER_SELECT_ALL = "select * from reader";
  //根据读者id删除读者信息
  public static final String DELETEBy_READER_ID="delete from reader where reader_id = ? ";

	// 通过读者编号（卡号）来查找读者的所有的借阅信息
	public static final String LOOKFOUNDBYREADER = "select reader_id 借书证号,reader_name 读者姓名,book_id 图书编号,"
			+ "book_name 图书名称,to_char(borrow_date,'yyyy/mm/dd') 借阅日期,"
			+ "to_char(should_return_date,'yyyy/mm/dd') 应还日期"
			+ " from readerreturnbook where reader_id=?";
	// 通过书籍编号来查找这本书被谁借去了
	public static final String LOOKFOUNDBYBOOK = "select reader_id 借书证号,reader_name 读者姓名,book_id 图书编号,"
			+ "book_name 图书名称,to_char(borrow_date,'yyyy/mm/dd') 借阅日期,"
			+ "to_char(should_return_date,'yyyy/mm/dd') 应还日期"
			+ " from readerreturnbook where book_id=?";
	// 通过读者编号和书籍编号来删除借书表中的信息
	public static final String BACKBOOK = "delete from readerreturnbook where reader_id=? and book_id=?";
	// 读者注册
	public static final String REGISTER_READER = "insert into reader(reader_id ,reader_name ,sex ,"
			+ "job ,acd ,tell ,certificate ,"
			+ "zheng_id ,e_mail ,regist_date"
			+ ")" + "values(?,?,?,?,?,?,?,?,?,to_date(?,'yyyy-mm-dd'))";
	// 在“书籍存储位置”表中增加存储位置
	public static final String INSERT_BOOK_STORELOCATION = "insert into storeLocation values(?,?,?,?)";
	// 通过阅览室名称和书籍位置来查找此存放位置上存放的书籍信息
	public static final String FOUND_STORE = "select * from storeLocation where reading_room = ? and BookLocation = ?";
	// 通过书籍信息和书籍类型来查找这本书放在哪个阅览室哪个书架
	public static final String FOUND_BOOK_STORELOCATION = "select * from storeLocation where BOOKINFORMATION = ? and BOOKCLASS = ?";
	// 修改书籍的存放位置
	public static final String CHANG_DATE = "update storeLocation set reading_room = ?,bookLocation = ?,bookInformation = ?,bookClass = ?";
	// 删除书籍存放位置（书架或是阅览室）
	public static final String DELETE_STORE = "delete from storeLocation where BOOKLOCATION = ? ";
	// 查找读者的所有信息根据读者编号
	public static final String FOUN_READER_INFORMATION = "select reader_id  读者编号, reader_name  读者姓名, acd  所在院系,"
			+ "to_char(regist_date,'yyyy/mm/dd')  注册日期, "
			+ "to_char(available_date,'yyyy/mm/dd')  有效日期至,"
			+ " condition  状态信息,not_return_num  图书未还数量,tell  电话,sex  性别,certificate  有效证件,"
			+ "zheng_id  证件号码,e_mail E_mail,job  读者身份 from reader where reader_id = ?";
  //更改读者的信息，此时读者只能更改“所在院系、电话、有效证件，有效证件号码”
	public static final String CHANGE_READER_INFORMATION = "update reader set acd = ?,tell = ?,certificate = ?,zheng_id = ? where reader_id = ?";
  //从借书表中通过读者编号找出读者的所有借阅信息
	public static final String BORROW_FOUND = "select reader_id 读者编号 ,reader_name 读者姓名,book_id 图书编号,book_name 图书名称,"
			+ " to_char(borrow_date,'yyyy/mm/dd day am' ) 借书日期,to_char(should_return_date,'yyyy/mm/dd day am') 应还日期 from readerreturnbook where reader_id = ?";
	
	  public static final String MANAGER_SELECT_ALLBook = "select * from book";

	  public static final String By_AUTHOR1=" author =?";
	  public static final String By_AUTHOR2=" author2 =%?%";
	  public static final String By_BOOK_ID1=" book_id =?";
	  public static final String By_BOOK_ID2=" book_id =%?%";
	  public static final String By_BOOK_LOCATION1=" book_location =?";
	  public static final String By_BOOK_LOCATION2=" book_location=%?%";
	  public static final String By_BOOK_NAME1=" book_name=?";
	  public static final String By_BOOK_NAME2=" book_name =%?%";
	  public static final String INSERT_INFO="insert into book values(?,?,?,?,?,?,?,?,?,?)";

	  public static final String DELETEBy_BOOK_ID="delete from Book where book_id = ? ";
	  public static final String UPDATE_EMPLOYEE_BY_ID = " update book set " +
	  " book_name = ?, kind= ?,author = ?, publis_com = ?," +
	  "book_location = ?,book_amount = ?,price = ? ,edition = ?,in_amount=? where book_id=?";

}