dbaccess.java

学生信息管理系统 jsp + sql + javabean 实现

package sms.db;

/**
 * <p>Title: </p>
 * <p>Description: </p>
 * <p>Copyright: Copyright (c) 2006</p>
 * <p>Company: </p>
 * @author not attributable
 * @version 1.0
 */
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.sql.DataSource;
import java.sql.DriverManager;
import javax.naming.*;

public class DBAccess {
    /**
     * 数据库连接
     */
    private Connection conn = null;

    /**
     * jdbc的Statement类型变量
     */
    private Statement stmt = null;

    /**
     *jdbc的结果集类型变量
     */
    private ResultSet rs = null;

    /**
     * 用于实现数据库连接的数据源
     */
    private DataSource dataSource = null;

    /**
     * 是否用于调试
     */
    private boolean debug = true;

    /**
     * 直接使用JDBC连接数据据库时的连接字符串
     */
    private String dbUrlStr =  "jdbc:mysql://localhost:3306/smsdb?user=root&password=root&useUnicode=true&characterEncoding=gbk";

    /**
     * 直接使用JDBC连接数据据库时的驱动程序名
     */
    private String driverName = "org.gjt.mm.mysql.Driver";


    /**
     * 构造函数
     */
    public DBAccess() {}

    public DBAccess(boolean debug) {
        this.debug = debug;
    }

    /**
     * 返回一个数据库连接
     * @return Connection
     */
    public void setDbUrlParameter(String driverName, String urlStr) {
        if (driverName == null) {
            driverName = "org.gjt.mm.mysql.Driver";
        } else {
            this.driverName = driverName;
        }
        if (urlStr == null) {
            this.dbUrlStr = "jdbc:mysql://localhost:3306/smsdb?user=root&password=root&useUnicode=true&characterEncoding=gbk";
        } else {
            this.dbUrlStr = urlStr;
        }
    }

    /**
     * 返回一个数据库连接
     * @return Connection
     */
    public Connection getConn() {
        if (conn == null) {
            getConnection();
        }
        return conn;
    }

    /**
     * 取得一个数据库连接
     */
    public void getConnection() {
        try {
            if (this.debug) {
                Class.forName(this.driverName);
                this.conn = DriverManager.getConnection(dbUrlStr);
                this.stmt = this.conn.createStatement();
                //System.out.println("数据库连接建立！");
            } else {
                javax.naming.Context ctx = new javax.naming.InitialContext();
                javax.sql.DataSource ds = (javax.sql.DataSource) ctx.lookup(
                        "java:comp/env/jdbc/smsdb");
                this.conn = ds.getConnection();
                this.stmt = this.conn.createStatement();
                //System.out.println("数据库连接建立(连接池)！");
            }
        } catch (NamingException ex1) {
            System.out.println("请检查数据库连接池配置是否正确！");
            ex1.printStackTrace();
        } catch (SQLException ex2) {
            System.out.println("请检查数据库是否启动！");
            ex2.printStackTrace();
        } catch (ClassNotFoundException ex3) {
            System.out.println("请检查数据库驱动程序是否正确！");
            ex3.printStackTrace();
        }
    }

    /**
     * 通过连接池建立连接
     * @param dataSource DataSource
     */
    public void getConnection(DataSource dataSource) {
        try {
            conn = dataSource.getConnection();
            stmt = conn.createStatement();
        } catch (SQLException ex) {
            System.out.println("请检查数据库是否启动！");
            ex.printStackTrace();
        }
    }

    /**
     * 按sql查询数据，返回数据结果集
     */
    public ResultSet query(String strSql) {
        //System.out.println("sql:" + strSql);
        ResultSet rs = null;
        try {
            rs = stmt.executeQuery(strSql);
            return rs;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return rs;
        }
    }

    /**
     * 插入多条数据
     * @param sqls insert语句数组
     * @return 成功返回true，失败返回false
     */
    public boolean insert(String[] sqls) {
        boolean breturn = false;
        try {
            conn.setAutoCommit(false);
            for (int i = 0; i < sqls.length; i++) {
                if (sqls[i] != null) {
                    stmt.addBatch(sqls[i]);
                }
            }
            stmt.executeBatch();
            conn.commit();
            conn.setAutoCommit(true);
            breturn = true;
        } catch (SQLException ex) {
        }
        return breturn;
    }

    /**
     * 数据库中数据更新的方法
     * @param strSql String
     * @return int
     */
    public int executeSql(String strSql) {
        System.out.println("sql:" + strSql);
        int result = 0;
        try {
            stmt = conn.createStatement();
            result = stmt.executeUpdate(strSql);
        } catch (SQLException ex) {
            System.out.println("产生异常,：at DBAccess.executeSql()");
            ex.printStackTrace();
        }
        return result;
    }

    /**
     * 多条update操作
     * @param sqls update语句数组
     * @return 成功返回ture，失败false 若数据没有修改返回true
     */
    public boolean executeSql(String[] sqls) {
        boolean breturn = false;
        try {
            conn.setAutoCommit(false);
            stmt = conn.createStatement();
            for (int i = 0; i < sqls.length; i++) {
                if (sqls[i] != null) {
                    //System.out.println("sqls[0]="+sqls[i]);
                    stmt.addBatch(sqls[i]);
                }
            }
            int[] aa = stmt.executeBatch();
            conn.commit();
            conn.setAutoCommit(true);
            breturn = true;
        } catch (SQLException ex) {
            System.out.println("产生异常：at DBAccess.executeSql()");
            ex.printStackTrace();
        }
        return breturn;
    }

    /**
     * 关闭数据库连接
     */
    public void closeConnection() {
        try {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }
            //System.out.println("数据库连接关闭！");
        } catch (SQLException ex) {
            System.out.println("产生异常：at DBAccess.closeConnection()");
            ex.printStackTrace();
        }
    }

    /**
     * 得到系统时间的方法
     * @return String
     */
    public static String getSysDate() {
        DBAccess dba = new DBAccess();
        String sql = "select sysdate() sysdate;";
        try {
            dba.getConnection();
            ResultSet rs = dba.query(sql);
            String currentDate = null;
            if (rs.next()) {
                currentDate = rs.getString("sysdate");
            }
            return currentDate;
        } catch (SQLException ex) {
            System.out.println("产生异常：at DBAccess.getSysDate()");
            ex.printStackTrace();
            return null;
        } finally {
            dba.closeConnection();
        }
    }

    public static void main(String[] args) {
        DBAccess dba = new DBAccess();
    }
}