maindao.java

简单的书店管理系统,是课设期间教师指导完成的,希望会对大家有帮助!

package book;

//********************************************************************
//主数据库操作窗体，实现信息的添加，删除，修改，搜索等
//********************************************************************
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Calendar;
import java.util.Date;

import javax.swing.table.DefaultTableModel;


public class MainDAO {
	private Connection Ex1Con;
	private ResultSet rs;
	private Statement Ex1Stmt;
	private ResultSetMetaData md;
	private NowEditableModel dtm;
	private PreparedStatement pstmt=null;
	//构造时候连接数据源
	MainDAO() throws SQLException{
		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		} catch (ClassNotFoundException e){}
		Ex1Con = DriverManager.getConnection("jdbc:odbc:book", "","");
	}
	//查询数据
	public ResultSet getSelect(String str) throws SQLException{
		Statement Ex1Stmt = Ex1Con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
		rs = Ex1Stmt.executeQuery(str);
		return rs;
	}
	
	public ResultSet getSelect1(String str) throws SQLException{
		Statement Ex1Stmt = Ex1Con.createStatement();
		rs = Ex1Stmt.executeQuery(str);
		return rs;
	}
	
	//判断数据是否存在
	public boolean Judge(String str,String user,String coum) throws SQLException{
		Statement Ex1Stmt = Ex1Con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
		rs = Ex1Stmt.executeQuery(str);
		boolean boo = false;
		boolean boo1 = false;
		String name = null;
		while ((boo = rs.next()) == true) {
			name = rs.getString(coum);
			if(name.equals(user)){
				boo1=true;
				break;
			}
		}
		return boo1;	
	}
	//数据修改
	public void update(String str) throws SQLException {
		Ex1Stmt = Ex1Con.createStatement();
	try {
		Ex1Stmt.executeUpdate(str);
	} catch (SQLException e) {
		e.printStackTrace();
	}
	close();
	}
	
	//添加数据
	public void insert(String str) throws SQLException{
		Ex1Stmt = Ex1Con.createStatement();
		try {
			Ex1Stmt.executeUpdate(str);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			close();	
		}
	}
	
	//多项数据添加
	public PreparedStatement getPreparedStatement(String sql) throws SQLException{
		pstmt=Ex1Con.prepareStatement(sql);
		return pstmt;
	}
	
	//删除数据
	public void del(String str) throws SQLException{
		Ex1Stmt = Ex1Con.createStatement();
		try {
			Ex1Stmt.executeUpdate(str);
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		close();
	}
	
	
	//刷新所有超期的图书
	public void getCtimeBook() throws SQLException{
		boolean boo=false;
		ResultSet rs=null;
		String str = "select * from jiechutushu where 是否超期='否'";
		pstmt = this.getPreparedStatement(str);
		rs=pstmt.executeQuery();
		while((boo=rs.next())==true){
			if(Integer.parseInt(cTime(rs.getString("应还日期")))>0){
				this.update("update jiechutushu set 是否超期='是' where 借书编号= "+rs.getString("借书编号"));
			}
		}
	  pstmt.close();
	  this.close();
	}
	
   //	检测是否超期和超期天数
	String  cTime(String time){
		int year,month,day;
		
		year=Integer.parseInt(time.substring(0,4));
		month=Integer.parseInt(time.substring(5,7));
		day=Integer.parseInt(time.substring(8,10));
		if(!(month==0)){
			month--;
		}
		
		Calendar calendar=Calendar.getInstance();
		calendar.set(year,month,day);
	    long time1=calendar.getTimeInMillis();
	    calendar.setTime(new Date());
	    long time2=calendar.getTimeInMillis();
		
	    long xtime=(time2-time1)/(1000*60*60*24);
		
	    
		return String.valueOf(xtime);
	}
	
	//返回tabel表需要的Model
	public DefaultTableModel getTableModel(String s) throws SQLException {
		
		Statement Ex1Stmt = Ex1Con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
		rs = Ex1Stmt.executeQuery(s);
		
		md = rs.getMetaData(); //获得元数据信息
		
		int cols = md.getColumnCount(); //获得列数
		
		int rows = 0; //获得行数
		rs.last();
		rows = rs.getRow();
		
		Object head[] = new String[cols]; //保存表头数据
		Object data[][] = new String[rows][cols]; //保存表体数据
		
		for (int t = 0; t < cols; t++) {
		  head[t] = md.getColumnName(t + 1); //表头数据初始化
		}
		rs.beforeFirst(); //移动到第一行记录前面
		
		int k = 0;
		while (rs.next()) {
			for (int h = 0; h < cols; h++) {
				data[k][h] = rs.getString(h + 1); //表体数据数组初始化
			}
			k++;	
		}
		dtm = new NowEditableModel(data, head);
		this.close();
		return dtm;
	}
	
	public void resetResult() throws SQLException {
        // 将结果集置空
        this.rs = null;
    }

	
	public void close() throws SQLException{
		//关闭数据库
		this.Ex1Con.close();
	}
}