package com.jxyd.sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class AccessDBConn {
	private Connection conn;            //連接對象
    private Statement stmt;                //語句對象
    private ResultSet rs;                //結果集對象
    private String accessdriver;        //保存Access驅動程序字符串
    private String accessURL;             //保存Access連接字符串
    private String dbpath="C:/tomcat/webapps/BookManager/data.mdb";

    public AccessDBConn()
    {
        //Access驅動程序
        accessdriver = "sun.jdbc.odbc.JdbcOdbcDriver";        
        //連接字符串
        accessURL = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";
        conn = null;
    }

    //該方法從參數得到數據庫路徑，并加到連接字符串后面,然后再建立連接
    public Connection getConnToAccess(){
        try{
            accessURL=accessURL+dbpath;
            Class.forName(accessdriver);
            conn = DriverManager.getConnection(accessURL);
            System.out.println("找到數據庫文件，連接成功...");
            }catch(Exception e){
                System.out.println("操作數據庫出錯，請仔細檢查文件路徑...");
                System.err.println(e.getMessage());
            }
        return conn;
    }
            
    
  //執行插入更新時的方法
	public boolean executeUpdate(String username,String mbno,String msg) {
		String sql="insert into OutBox (username,Mbno,Msg) values('"+username+"','"+mbno+"','"+msg+"')";
		boolean bupdate = false;
		try {
			// 建立數據庫連接，其它參數說明同上面的一樣
			Connection conn = this.getConnToAccess();
			Statement stmt = conn.createStatement();
			int rowCount = stmt.executeUpdate(sql);
			// 如果不成功，bupdate就會返回0
			if (rowCount != 0)
				bupdate = true;
			System.out.println("短信提醒已經成功發出...");
		} catch (SQLException ex) {
			// 打印出錯信息
			System.err.println("db.executeUpdate: " + ex.getMessage());
		}
		return bupdate;
	}
	
	
    //執行查詢時的方法
	public ResultSet executeQuery(String sql) {
		rs = null;
		try {
			Connection conn = this.getConnToAccess();
			Statement stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);

		} catch (Exception e) {
			System.err.println("db.executeQuery: " + e.getMessage());

		}
		return rs;

	}
	
	//關閉數據庫連接
    public void close()
    {
        try{
            rs.close();
            stmt.close();
            conn.close(); 
        }catch(SQLException sqlexception){
            sqlexception.printStackTrace();
        }
    }


}