sqloperate.java

学员信息管理系统

package com.javasme.data;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.util.Date;

import javax.swing.JComboBox;
import javax.swing.JLabel;
import javax.swing.JTable;

import com.javasme.longs.ObjectClasses;
import com.javasme.table.ResultSetModelForTable;

public class SQLOperate {

    private static JLabel     labelStudentsNumber = ObjectClasses.labelStudentsNumber;

    private static JLabel     labelTeachersNumber = ObjectClasses.labelTeachersNumber;

    private static JLabel     labelCoursesNumber  = ObjectClasses.labelCoursesNumber;

    private static JLabel     labelClassesNumber  = ObjectClasses.labelClassesNumber;

    /**
     * 操作数据库 有关对象及变量的声明
     */

    /**
     * 数据库登陆设置 sqlUserName 登陆用户名 sqlUserPassWord 登陆密码
     */
    // private static String SqlUserName = "sa";
    // private static String SqlUserPassWord = "bianjiang";
    /**
     * 桥连接 需要设置数据源
     */
    // private static String SqlDriverName = "sun.jdbc.odbc.JdbcOdbcDriver";
    // private static String SqlUrl = "jdbc:odbc:StudentSystem";
    /**
     * 直连接 直接连接SQL Server数据库
     */
    // private static String SqlDriverName =
    // "com.microsoft.jdbc.sqlserver.SQLServerDriver";
    // private static String SqlUrl =
    // "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=StudentSystem";
    /**
     * 直接连接 直接连接Access数据库
     */
    private static String     SqlDriverName       = "sun.jdbc.odbc.JdbcOdbcDriver";

    private static String     PackagePath         = System.getProperty("user.dir");                                                                     // 获取程序运行的路径(包所在的路径)

    private static String     SqlUrl              = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=" + PackagePath + "\\Data\\Information.bj";

    private static Connection CONN                = null;

    private static Statement  ST                  = null;

    private static ResultSet  RS                  = null;

    /**
     * 数据库操作————连接
     * 
     * @return
     */
    public static Connection getConnection() {
        try {
            Class.forName(SqlDriverName);
            CONN = DriverManager.getConnection(SqlUrl, "", "");
        } catch (Exception e) {
            e.printStackTrace();
        }

        return CONN;
    }

    /**
     * 数据库操作————添加，修改，删除
     * 
     * @return
     */
    public static Statement getStatement() {
        try {
            ST = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
        } catch (Exception e) {
            // e.printStackTrace();
        }
        return ST;
    }

    /**
     * 数据库操作————查询
     * 
     * @param sqlStr
     * @return
     */
    public static ResultSet getResultSet(String sqlStr) {
        try {
            RS = getStatement().executeQuery(sqlStr);
        } catch (Exception e) {
            e.printStackTrace();
            // JOptionPane.showMessageDialog(null , "数据库连接错误！");
        }

        return RS;
    }

    public static void closeRS() {
        try {
            if (RS != null) {
                RS.close();
            }
            closeST();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void closeST() {
        try {
            if (ST != null) {
                ST.close();
            }
            closeCONN();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void closeCONN() {
        try {
            if (!CONN.isClosed()) {
                CONN.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取时间的方法 应用的类：Date , DateFormat DateFormat方法 getInstance() 格式：86-1-15
     * 下午4:04 getDateInstance() 格式：1986-06-23 getTimeInstance() 格式：16:10:54
     * getDateTimeInstance() 格式：1986-6-23 08:08:08
     * 
     * @return
     */
    public static String getDate() {
        Date now = new Date();
        DateFormat dateFormat = DateFormat.getDateInstance(); // 格式：1986-06-23
        String date = dateFormat.format(now); // 将一个 Date 格式化为日期/时间字符串。
        return date;
    }

    public static String getPreciseDate() {
        Date now = new Date();
        DateFormat dateFormat = DateFormat.getDateTimeInstance(); // 格式：1986-6-23
                                                                    // 08:08:08
        String date = dateFormat.format(now); // 将一个 Date 格式化为日期/时间字符串。
        return date;
    }

    /**
     * 查询数据
     * 
     * @param tableStr
     * @param table
     */
    public static void selectAllFromData(String tableStr, JTable table) {
        int tableStudentsRowCount = 0;
        int tableTeachersRowCount = 0;
        int tableCoursesRowCount = 0;
        int tableClassesRowCount = 0;
        ResultSet rs = SQLOperate.getResultSet("Select * from " + tableStr);
        ResultSetModelForTable rsmd = new ResultSetModelForTable(rs);
        table.setModel(rsmd);
        // System.out.println("将学生数据添加到表格中!");
        if (tableStr.equals("Students")) {
            tableStudentsRowCount = rsmd.getRowCount();
            labelStudentsNumber.setText("" + tableStudentsRowCount);
            ObjectClasses.tableStudentsRowCount = tableStudentsRowCount;
            // System.out.println("学生表记录数为" + tableStudentsRowCount);
        }
        if (tableStr.equals("Teachers")) {
            tableTeachersRowCount = rsmd.getRowCount();
            labelTeachersNumber.setText("" + tableTeachersRowCount);
            ObjectClasses.tableTeachersRowCount = tableTeachersRowCount;
        }
        if (tableStr.equals("Courses")) {
            tableCoursesRowCount = rsmd.getRowCount();
            labelCoursesNumber.setText("" + tableCoursesRowCount);
            ObjectClasses.tableCoursesRowCount = tableCoursesRowCount;
        }
        if (tableStr.equals("Classes")) {
            tableClassesRowCount = rsmd.getRowCount();
            labelClassesNumber.setText("" + tableClassesRowCount);
            ObjectClasses.tableClassesRowCount = tableClassesRowCount;
        }
    }

    /**
     * 《信息检索》查询数据库方法
     * 
     * @param tableStr
     * @param table
     * @param sqlStr
     */
    public static void selectAllFromDataForRetrieval(String tableStr, JTable table, String sqlStr) {
        int tableRetrievalRowCount = 0;
        ResultSet rs = SQLOperate.getResultSet(sqlStr);
        ResultSetModelForTable rsmd = new ResultSetModelForTable(rs);
        table.setModel(rsmd);
        tableRetrievalRowCount = rsmd.getRowCount();
        ObjectClasses.tableRetrievalRowCount = tableRetrievalRowCount;
    }

    /**
     * 为 comboBox 添加选项
     */
    public static void addItem(JComboBox comboBox, String item, String tableName, String columnName, String str) {
        String sqlStr = "";
        if (columnName.equals("") || str.equals("")) {
            sqlStr = "Select * from " + tableName;
        } else {
            sqlStr = "Select * from " + tableName + " where " + columnName + " = '" + str + "'";
        }
        comboBox.removeAllItems();
        comboBox.addItem("请选择...");
        if (item.equals("Teacher_Name")) {
            comboBox.addItem("<添加新教师>");
        }
        if (item.equals("Class_Name")) {
            comboBox.addItem("<添加新班级>");
        }
        ResultSet rs = SQLOperate.getResultSet(sqlStr);
        try {
            while (rs.next()) {
                if (item.equals("Teacher_Name")) {
                    comboBox.addItem("[" + rs.getString("Teacher_ID") + "]" + rs.getString(item));
                } else {
                    comboBox.addItem(rs.getString(item));
                }
            }
        } catch (SQLException e) {
            // e.printStackTrace();
        }
        SQLOperate.closeRS();
    }

    /**
     * 查询数据库，生成结果集数组的方法实现
     */

    public static Object[] getArray(String tableName, String columnName, String className) {
        Object[] objectArray = new Object[ObjectClasses.tableClassesRowCount - 1];
        String sqlStr = "Select * from Classes";// + tableName;
        ResultSet rs = SQLOperate.getResultSet(sqlStr);// 获取结果集
        int i = 0;
        try {
            while (rs.next()) {
                String classNameStr = rs.getString("Class_Name");
                if (!classNameStr.equals(className)) {
                    objectArray[i] = classNameStr;
                    i++;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return objectArray;
    }
}