dao.java

图书管理系统java+swing+SQL Server实现 包含了图书管理系统的所有功能 纯JAVA实现

package com.lishan.dao;

import java.sql.Connection; //导入数据库连接时所使用的类
import java.sql.DriverManager; //导入数据库连接时所使用的驱动管理类
import java.sql.ResultSet; //导入数据库查询时所使用的java.sql.ResultSEet集合类;
import java.sql.SQLException; //数据库操作时发生错误的异常处理类
import java.sql.Date; //表示数据库时间的类
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import com.lishan.model.Back;
import com.lishan.model.BookInfo;
import com.lishan.model.BookType;
import com.lishan.model.Borrow;
import com.lishan.model.Operater;
import com.lishan.model.OrderAndBookInfo;
import com.lishan.model.Reader;
import com.lishan.model.user;

/**
 * 数据库连接及其操作
 * 
 * @author lishan
 * @date 2008/09/14
 */

public class Dao {
	protected static String dbClassName = // 驱动包 定义数据库连接驱动
	"com.microsoft.jdbc.sqlserver.SQLServerDriver";
	protected static String dbUrl = "jdbc:microsoft:sqlserver://127.0.0.1:1433;"
			+ "DatabaseName=db_library;SelectMethod=Cursor"; // 定义数据库连接地址
	protected static String dbUser = "sa"; // 定义数据库连接用户名
	protected static String dbPwd = "57lishan"; // 定义数据库连接密码
	protected static String second = null; // 确认密码
	protected static Connection conn = null; // 定义数据库连接

	private Dao() {
		/**
		 * 数据库连接步骤:1、装载驱动 2、获取连接 3、操作Statement
		 */
		try { // 捕捉数据库连接异常
			if (conn == null) { // 如果连接为空
				Class.forName(dbClassName).newInstance();// 装载SQL Server驱动
				conn = DriverManager.getConnection(dbUrl, dbUser, dbPwd);// 连接SQL
				// Server数据库
				// Server数据库的连接
			} else
				return; // 如果连接不为空则，则返回此连接
		} catch (Exception ee) {
			ee.printStackTrace();
		}
	}

	private static ResultSet executeQuery(String sql) {// 执行查询语句的方法
		try {
			if (conn == null)
				new Dao(); // 如果数据库的连接为空，则创建连接
			// 返回一个可滚动，且受其他更新影响的，可更新结果集
			return conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE).executeQuery(sql);// 返回Statement语句对象，该语句对象通过SQL语句操作数据库
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {

		}
	}

	private static int executeUpdate(String sql) { // 执行更新操作的方法
		try {
			if (conn == null)// 如果连接为空则创建连接,并生成一个状态对象,进行更新
				new Dao();
			return conn.createStatement().executeUpdate(sql);
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			// if(e.getMessage().equals("[Microsoft][SQLServer 2000 Driver for
			// JDBC][SQLServer]DELETE语句与
			// COLUMN REFERENCE 约束 'FK_TB_BORRO_REFENCE_TB_BOOKI'
			// 冲突.该冲突发生于数据库'db_librar',表'tb_borrow',
			// column 'bookISBN'."))
			return -1;
		} finally {

		}
	}

	private static void close() { // 关闭数据库连接
		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			conn = null; // 在最终执行块中将数据库连接置空
		}
	}

	/**
	 * 管理员登入方法
	 */
	public static Operater check(String name, String password) {
		int i = 0;
		Operater operater = new Operater();
		String sql = "select * from lee_operator where name = '" + name
				+ "' and password = '" + password + "' and admin = 1";
		ResultSet rs = Dao.executeQuery(sql);
		try {
			while (rs.next()) {
				String names = rs.getString(1);
				operater.setId(rs.getString("id"));
				operater.setName(rs.getString("name"));
				operater.setGrade(rs.getString("admin"));
				operater.setPassword(rs.getString("password"));
				if (name != null) {
					i = 1;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		Dao.close();
		return operater;
	}
	
	public static Reader Readercheck(String ISBN, String password) {
		int i = 0;
		Reader reader = new Reader();
		String sql = "select * from lee_reader where name = '" + ISBN
				+ "' and password = '" + password + "'";
		ResultSet rs = Dao.executeQuery(sql);
		try {
			while (rs.next()) {
				String name = rs.getString("name");
				reader.setName(rs.getString("name"));
				reader.setSex(rs.getString("sex"));
				reader.setAge(rs.getString("age"));
				reader.setIdentityCard(rs.getString("identityCard"));
				reader.setDate(rs.getDate("date"));
				reader.setMaxNum(rs.getString("maxNum"));
				reader.setTel(rs.getString("tel"));
				reader.setKeepMoney(rs.getDouble("keepMoney"));
				reader.setZhengjian(rs.getInt("zhengjianType"));
				reader.setZhiye(rs.getString("zhiye"));
				reader.setISBN(rs.getString("ISBN"));
				reader.setBztime(rs.getDate("banzhengtime"));
				if (name != null) {
					i = 1;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		Dao.close();
		return reader;
	}

	/**
	 * 查询类别方法
	 */
	public static List selectBookCategory() {// 查询所有的图书类别元素
		List list = new ArrayList();
		String sql = "select * from lee_bookType";
		ResultSet rs = Dao.executeQuery(sql);// 将数据库中符合条件的元素存放在结果集rs中
		try {
			while (rs.next()) {
				BookType bookType = new BookType();
				bookType.setId(rs.getString("id"));
				bookType.setTypeName(rs.getString("typeName"));
				bookType.setDays(rs.getString("days"));
				bookType.setFakuan(rs.getString("fakuan"));
				list.add(bookType);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		Dao.close();
		return list;
	}

	public static List selectBookCategory(String bookType) {// 查询类型为bookType的图书信息
		List list = new ArrayList();
		String sql = "select * from lee_bookType where typeName='" + bookType
				+ "'";
		ResultSet rs = Dao.executeQuery(sql);
		try {
			while (rs.next()) {
				BookType type = new BookType();
				type.setDays(rs.getString("days"));
				list.add(type);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		Dao.close();
		return list;
	}

	/**
	 * 图书类别表相关操作--
	 */
	public static int InsertBookType(String bookTypeName, String days,
			Double fakuan) { // 插入书籍类型
		int i = 0;
		try {
			String sql = "insert into lee_bookType(typeName,days,fakuan) "
					+ "values('" + bookTypeName + "','" + days + "','" + fakuan
					+ "')";
			i = Dao.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		Dao.close();
		return i;// 返回插入的个数
	}

	public static int UpdatebookType(String id, String typeName, String days,
			String fakuan) { // 更新书籍类型
		int i = 0;
		try {
			String sql = "update lee_bookType set typeName='" + typeName
					+ "',days = '" + days + "',fakuan='" + fakuan
					+ "' where id='" + id + "'";
			i = Dao.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		Dao.close();
		return i;
	}

	// public static int DelbookType(String id) { // 删除书籍类型
	// int i = 0;
	// try{
	// String sql = "delete from lee_bookType where id='" + "'";
	// i = Dao.executeUpdate(sql);
	// }catch(Exception e){
	// e.printStackTrace();
	// }
	// Dao.close();
	// return i;
	// }

	public static List selectBookTypeFk(String bookType) {// 取每种书超过规定时间罚款金额
		List list = new ArrayList();
		String sql = "select * from lee_bookType where typeName='" + bookType
				+ "'";
		ResultSet rs = Dao.executeQuery(sql);
		try {
			while (rs.next()) {
				BookType booktype = new BookType();
				booktype.setTypeName(rs.getString("typeName"));
				booktype.setDays(rs.getString("days"));
				booktype.setFakuan(rs.getString("fakuan"));
				list.add(booktype);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		Dao.close();
		return list;
	}

	/**
	 * 图书信息表相关操作
	 */
	/**
	 * 插入图书信息方法
	 */
	public static int insertBook(String ISBN, String typeId, String bookName,
			String writer, String translator, String publisher, Date date,
			Double price) {
		int i = 0;
		try {
			String sql = "insert into lee_bookInfo(ISBN,typeId,bookname,writer,translator,publisher,date,price) values('"
					+ ISBN
					+ "','"
					+ typeId
					+ "','"
					+ bookName
					+ "','"
					+ writer
					+ "','"
					+ translator
					+ "','"
					+ publisher
					+ "','"
					+ date
					+ "'," + price + ")";
			i = Dao.executeUpdate(sql);
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
		Dao.close();
		return i;
	}

	/**
	 * 查询图书相关信息
	 */
	public static List selectBookInfo() {
		List list = new ArrayList();
		String sql = "select * from lee_BookInfo";
		ResultSet rs = Dao.executeQuery(sql);
		try {
			while (rs.next()) {
				BookInfo bookinfo = new BookInfo();
				bookinfo.setISBN(rs.getString("ISBN"));
				bookinfo.setTypeid(rs.getString("typeid"));
				bookinfo.setBookname(rs.getString("bookname"));
				bookinfo.setWriter(rs.getString("writer"));
				bookinfo.setTranslator(rs.getString("translator"));
				bookinfo.setPublisher(rs.getString("publisher"));
				bookinfo.setDate(rs.getDate("date"));
				bookinfo.setPrice(rs.getDouble("price"));
				list.add(bookinfo);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		Dao.close();
		return list;
	}

	public static List selectBookInfo(String ISBN) {
		List list = new ArrayList();
		String sql = "select * from lee_BookInfo where ISBN = '" + ISBN + "'";
		ResultSet rs = Dao.executeQuery(sql);
		try {
			while (rs.next()) {
				BookInfo bookinfo = new BookInfo();
				bookinfo.setISBN(rs.getString("ISBN"));
				bookinfo.setTypeid(rs.getString("typeid"));
				bookinfo.setBookname(rs.getString("bookname"));
				bookinfo.setWriter(rs.getString("writer"));
				bookinfo.setTranslator(rs.getString("translator"));
				bookinfo.setPublisher(rs.getString("publisher"));
				bookinfo.setDate(rs.getDate("date"));
				bookinfo.setPrice(rs.getDouble("price"));
				list.add(bookinfo);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		Dao.close();
		return list;
	}

	/**
	 * 修改图书信息管理方法
	 */
	public static int updateBook(String ISBN, String typeId, String bookname,
			String writer, String translator, String publisher, Date date,
			Double price) {
		int i = 0;
		try {
			String sql = "update lee_bookInfo set ISBN='" + ISBN + "',typeId='"
					+ typeId + "',bookname='" + bookname + "',writer='"
					+ writer + "',translator='" + translator + "',publisher='"
					+ publisher + "',date='" + date + "',price=" + price
					+ " where ISBN='" + ISBN + "'";
			i = Dao.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		Dao.close();
		return i;
	}

	// /**
	// * 删除图书信息方法
	// */
	// public static int delBook(String ISBN) {
	// int i = 0;
	// try{
	// String sql = "delete from lee_bookInfo where ISBN = '" + ISBN + "'";
	// i = Dao.executeUpdate(sql);
	// }catch(Exception e){
	// e.printStackTrace();
	// }
	// Dao.close();
	// return i;
	// }

	/**
	 * 对读者信息表执行相关操作
	 */
	public static int InsertReader(String name, String sex, String age,
			String identityCard, Date date, String maxNum, String tel,
			Double keepMoney, String zhengjian, String zhiye, Date bztime,
			String ISBN) {// 插入读者
		int i = 0;
		try {
			String sql = "insert into lee_reader(name,sex,age,identityCard,date,maxNum,tel,keepMoney,zhengjianType,zhiye,banzhengtime,ISBN,remainBorrowNum,password) values('"
					+ name
					+ "','"
					+ sex
					+ "','"
					+ age
					+ "','"
					+ identityCard
					+ "','"
					+ date
					+ "','"
					+ maxNum
					+ "','"
					+ tel
					+ "',"
					+ keepMoney
					+ ",'"
					+ zhengjian
					+ "','"
					+ zhiye
					+ "','"
					+ bztime
					+ "','"
					+ ISBN
					+ "','"
					+ maxNum
					+ "','"
					+ ISBN
					+ "')";
			System.out.println(sql);
			i = Dao.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		Dao.close();
		return i;
	}

	public static List selectReader() {// 查询读者信息
		List list = new ArrayList();
		String sql = "select * from lee_reader";
		ResultSet rs = Dao.executeQuery(sql);
		try {
			while (rs.next()) {
				Reader reader = new Reader();
				reader.setName(rs.getString("name"));
				reader.setSex(rs.getString("sex"));
				reader.setAge(rs.getString("age"));
				reader.setIdentityCard(rs.getString("identityCard"));
				reader.setDate(rs.getDate("date"));
				reader.setMaxNum(rs.getString("maxNum"));
				reader.setTel(rs.getString("tel"));
				reader.setKeepMoney(rs.getDouble("keepMoney"));
				reader.setZhengjian(rs.getInt("zhengjianType"));
				reader.setZhiye(rs.getString("zhiye"));
				reader.setISBN(rs.getString("ISBN"));
				reader.setBztime(rs.getDate("banzhengtime"));
				list.add(reader);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		Dao.close();
		return list;
	}

	public static List selectReader(String readerISBN) {// 查询条形编码为readerISBN的读者的信息
		List list = new ArrayList();
		String sql = "select * from lee_reader where ISBN = '" + readerISBN
				+ "'";
		ResultSet rs = Dao.executeQuery(sql);
		System.out.println(sql);
		try {
			while (rs.next()) {
				Reader reader = new Reader();
				reader.setName(rs.getString("name"));
				reader.setSex(rs.getString("sex"));
				reader.setAge(rs.getString("age"));
				reader.setZhiye(rs.getString("zhiye"));
				reader.setIdentityCard(rs.getString("identityCard"));
				reader.setDate(rs.getDate("date"));
				reader.setMaxNum(rs.getString("maxNum"));
				reader.setTel(rs.getString("tel"));
				reader.setKeepMoney(rs.getDouble("keepMoney"));
				reader.setZhengjian(rs.getInt("zhengjianType"));
				reader.setISBN(rs.getString("ISBN"));
				reader.setBztime(rs.getDate("banzhengtime"));
				reader.setRemainBorrowNum(rs.getString("remainBorrowNum"));
				list.add(reader);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		Dao.close();
		return list;
	}

	public static List selectReaderByName(String readerName) {// 查询条形编码为readerISBN的读者的信息
		List list = new ArrayList();
		String sql = "select * from lee_reader where name like '%" + readerName
				+ "%'";
		ResultSet rs = Dao.executeQuery(sql);
		System.out.println(sql);
		try {
			while (rs.next()) {
				Reader reader = new Reader();
				reader.setName(rs.getString("name"));
				reader.setSex(rs.getString("sex"));
				reader.setAge(rs.getString("age"));
				reader.setZhiye(rs.getString("zhiye"));
				reader.setIdentityCard(rs.getString("identityCard"));
				reader.setDate(rs.getDate("date"));
				reader.setMaxNum(rs.getString("maxNum"));
				reader.setTel(rs.getString("tel"));