sqlutil.java~10~

一个简单图书管理系统(非常适合于初学者)

package edu.njust.cs;

import java.sql.*;
import javax.swing.*;
import java.util.*;
import java.io.*;

public class SqlUtil {
    //读入配置文件
    public static Properties loadProperty(String fileName) {
        Properties prop = new Properties();
        try {
            FileInputStream in = new FileInputStream(
                    System.getProperties().get("user.dir") + "/" + fileName);
            prop.load(in);
            in.close();
        } catch (IOException e) {
            e.printStackTrace();
            JOptionPane.showMessageDialog(null,
                                          "配置文件丢失!\n建议重新安装程序",
                                          "信息",
                                          JOptionPane.ERROR_MESSAGE);
            prop = null;
        }
        return prop;
    }

    //建立和MS SQL SERVER的连接
    public static Connection acquireConnection(
            String host, String port, String dbName, String user,
            String pwd) throws ClassNotFoundException, SQLException {
        Connection connection = null;
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            String url = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)}; DBQ=d:\\bookTest.mdb";
            connection = DriverManager.getConnection(url);
            connection.setCatalog(dbName);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            throw e;
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        }
        return connection;
    }

    //使用指定的SQL语句和数据,向数据库插入一条记录
    public static boolean addRowToDB(
            Connection con, String insertSql, Object[] lineForDBAdd) {
        boolean flag = true;
        PreparedStatement update = null;
        try {
            update = con.prepareStatement(insertSql);
            if (lineForDBAdd != null) {
                for (int i = 0; i < lineForDBAdd.length; i++) {
                    update.setObject(i + 1, lineForDBAdd[i]);
                }
            }
            update.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            flag = false;
            JOptionPane.showMessageDialog(null,
                                          "从数据库插入数据时发生错误" + e,
                                          "信息",
                                          JOptionPane.ERROR_MESSAGE);
        } finally {
            if (update != null) {
                try {
                    update.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        }
        return flag;
    }

    //将适合数据库存储的一行数据(LineForDB)
    //转化为适合表格显示的数据(LineForTable)
    //依据数据类型的不同，将代表空值的特殊值转化为空值
    public static Object[] getLineForTableFromLineForDB(
            Object[] lineForDB, Class[] dataType) {
        Object[] lineForTable = new Object[lineForDB.length];
        for (int i = 0; i < lineForDB.length; i++) {
            if (dataType[i] == java.lang.String.class) {
                lineForTable[i] = lineForDB[i];
            } else if (dataType[i] == java.lang.Double.class) {
                if (((Double) lineForDB[i]).doubleValue() ==
                    Double.NEGATIVE_INFINITY) {
                    lineForTable[i] = null;
                } else {
                    lineForTable[i] = lineForDB[i];
                }
            } else if (dataType[i] == java.lang.Integer.class) {
                if (((Integer) lineForDB[i]).doubleValue() == Integer.MIN_VALUE) {
                    lineForTable[i] = null;
                } else {
                    lineForTable[i] = lineForDB[i];
                }
            }
        }
        return lineForTable;
    }

    //清空表格中所有的数据
    public static void clearAllRowsInTable(CustomTableModel model) {
        while (model.getRowCount() > 0) {
            model.removeRow(0);
        }
    }

    //从数据库读取数据到表格, dataType指明表格中每一列的数据类型
    //当前只考虑了String, Double ,Integer,Boolean ,Timestamp类型.
    public static void readDBToTable(Connection con, String readSql,
                                     CustomTableModel model, Class[] dataType) {
        clearAllRowsInTable(model);
        PreparedStatement query = null;
        try {
            query = con.prepareStatement(readSql);
            query.clearParameters();
            ResultSet rs = query.executeQuery();
            while (rs.next()) {
                int column = model.getColumnCount();
                Object[] line = new Object[column];
                for (int i = 0; i < column; i++) {
                    if (dataType[i] == java.lang.String.class) {
                        line[i] = rs.getString(i + 1).trim();
                    } else if (dataType[i] == java.lang.Double.class) {
                        Double ob = rs.getDouble(i + 1);
                        if (ob == Double.NEGATIVE_INFINITY) {
                            line[i] = null;
                        } else {

                            line[i] = new Double(ob);
                        }
                    } else if (dataType[i] == java.lang.Integer.class) {
                        if (rs.getInt(i + 1) == Integer.MIN_VALUE) {
                            line[i] = null;
                        } else {
                            line[i] = new Integer(rs.getInt(i + 1));
                        }
                    }
                }
                model.addRow(line);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            JOptionPane.showMessageDialog(null,
                                          "从数据库读取数据时发生错误!" +
                                          "SQL语句为:" + readSql + e, "提示",
                                          JOptionPane.ERROR_MESSAGE);
        } finally {
            if (query != null) {
                try {
                    query.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        }
    }

    //使用指定的SQL语句删除数据库中的记录
    public static boolean deleteFromDB(
            Connection con, String deleteSql, Object[] keys) {
        boolean flag = true;
        PreparedStatement update = null;
        try {
            update = con.prepareStatement(deleteSql);
            if (keys != null) {
                for (int i = 0; i < keys.length; i++) {
                    update.setObject(i + 1, keys[i]);
                }
            }
            update.executeUpdate();
        } catch (SQLException e) {
            flag = false;
            JOptionPane.showMessageDialog(null,
                                          "从数据库删除时发生错误!\n" + e,
                                          "提示",
                                          JOptionPane.ERROR_MESSAGE);
        } finally {
            if (update != null) {
                try {
                    update.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        }
        return flag;
    }
}