bookinfodaoimp.java

适合学生课程设计使用,这是我自己编写的一个系统.

package persistence;

import java.util.ArrayList;
import java.util.List;
import java.util.Iterator;
import java.util.Vector;

import java.sql.*;
import javax.sql.rowset.CachedRowSet;
import com.sun.rowset.CachedRowSetImpl;
import com.microsoft.jdbc.sqlserver.*;
import java.sql.CallableStatement;
import java.sql.ResultSetMetaData;

import java.beans.PropertyChangeListener;
import java.beans.PropertyChangeSupport;

import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.Query;
import org.hibernate.Criteria;
import org.hibernate.criterion.Restrictions;
import org.hibernate.criterion.MatchMode;
import org.hibernate.Transaction;
import org.hibernate.exception.*;
import org.hibernate.criterion.Example;

import bean.bookInfo;
import tools.help;
import tools.CreateXL;

public class bookInfoDAOImp {
	private static String drv = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
	private static String url = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=LMSDataBase";
	private static String user = "sa";
	private static String pwd = "";
	
	private static Connection conn = null;
	private static Statement stmt = null;
	private static ResultSet rs = null;
	private static PreparedStatement pstmt = null;
	private static CallableStatement cstmt = null;
	private static ResultSetMetaData rsmd = null;
	private static CachedRowSet crs;
	
	static Session session = null;
	/**
	 * @param args
	 */
	public bookInfoDAOImp() {						//初始化，装载数据库驱动
		try {
			Class.forName(drv);
			System.out.println("login succee");
		} catch(ClassNotFoundException cnfe) {
			System.out.println("Error:ClassNotFound");
		}
	}
	
	public static void setConnection() {				//打开数据库连接
		try {
			conn = DriverManager.getConnection(url,user,pwd);
			System.out.println("connection succee");
			//stmt = conn.createStatement();
			conn.setAutoCommit(false);
		} catch(SQLException sqle) {
			System.out.println("Error:" + sqle.getMessage());
		}
	}
	
	public static void clossSession() {					//关闭session
		try {
			if(conn != null) {
				conn.close();	
			}
		} catch(SQLException e) {
			System.out.println("Error:" + e.toString());
		}
	} 
	
	public boolean searchBookRows(String str) {			//模糊查询,是否有此图书记录
		boolean bool = false;
		str = "\'%" + str + "%\'";
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("select * from bookInfo where name like ");
			sql.append(str);
			sql.append(" or description like ");
			sql.append(str);
			sql.append(" or ISBN like ");
			sql.append(str);
			sql.append(" or bookIndex like ");
			sql.append(str);
			sql.append(" or author like ");
			sql.append(str);
			sql.append(" or publishTime like ");
			sql.append(str);
			sql.append(" or ectype like ");
			sql.append(str);
			sql.append(" or pageNum like ");
			sql.append(str);
			setConnection();
			stmt = conn.createStatement();
			System.out.println(sql.toString());
			rs = stmt.executeQuery(sql.toString());
			while(rs.next()) {
				bool = true;
				System.out.println(rs.getString(4));
			}
		} catch(SQLException sqle) {
			sqle.printStackTrace();
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			clossSession();
		}
		return bool;
	}

	public List getBookRows(String str) {			//模糊查询,返回图书记录
		List list = new ArrayList();
		Vector<bookInfo> vs = new Vector<bookInfo>();
		str = "\'%" + str + "%\'";
		try {
			session = HibernateUtil.currentSession();
			Transaction tx = session.beginTransaction();
			StringBuffer sql = new StringBuffer();
			sql.append("select * from bookInfo where name like ");
			sql.append(str);
			sql.append(" or description like ");
			sql.append(str);
			sql.append(" or ISBN like ");
			sql.append(str);
			sql.append(" or bookIndex like ");
			sql.append(str);
			sql.append(" or author like ");
			sql.append(str);
			sql.append(" or publishTime like ");
			sql.append(str);
			sql.append(" or ectype like ");
			sql.append(str);
			sql.append(" or pageNum like ");
			sql.append(str);
			setConnection();
			stmt = conn.createStatement();
			System.out.println(sql.toString());
			rs = stmt.executeQuery(sql.toString());
			while(rs.next()) {
				String id = rs.getString(1);
				//System.out.println(id);
				//vs.addElement(id);
				bookInfo bi = (bookInfo)session.get(bookInfo.class,id);
				System.out.println(bi.getAuthor());
				vs.add(bi);
			}
			if(!vs.isEmpty()) {
				Iterator iter = vs.iterator();
				while(iter.hasNext()) {
					list.add(iter.next());
				}
			}
			tx.commit();
		} catch(HibernateException e) {
			e.printStackTrace();
		}  catch(SQLException sqle) {
			sqle.printStackTrace();
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			clossSession();
			HibernateUtil.closeSession(session);
		}
		return list;
	}
	
	public static List getAllBookRows() {			//返回图书记录
		List list = null;
		try {
			session = HibernateUtil.currentSession();
			Transaction tx = session.beginTransaction();
			Criteria crit = session.createCriteria(bookInfo.class);
			list = crit.list();
			tx.commit();
		} catch(HibernateException e) {
			e.printStackTrace();
		} finally {
			HibernateUtil.closeSession(session);
		}
		return list;
	}
	
	public void getBookRs(String fileName,String tableName) {			//返回图书详细记录，用于生成报表
		try {
			//crs = new CachedRowSetImpl();
			setConnection();
			stmt = conn.createStatement();
			String sql = "select * from viewBookInfo";
			System.out.println(sql);
			rs = stmt.executeQuery(sql);
			if(rs.next())
				System.out.println("view OK");
			
			//crs.populate(rs);
			CreateXL.resultSetToExcel(rs,fileName,tableName);
		} catch(HibernateException e) {
			e.printStackTrace();
		}  catch(SQLException sqle) {
			sqle.printStackTrace();
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			//HibernateUtil.closeSession(session);
		}
		//return rs;
	}
	
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		bookInfoDAOImp bidaoimp = new bookInfoDAOImp();
		System.out.println(bidaoimp.searchBookRows("Java"));
		//ResultSet rst = bidaoimp.getBookRs();
	}
}