dbcon.java

员工管理系统 在一个公司里员工之间都有工作上的联系。对方的联系方式

package com.worker.db;

import java.awt.*;
import java.awt.event.*;
import java.awt.print.*;
import java.awt.datatransfer.*;
import java.io.*;
import javax.swing.*;
import javax.swing.event.*;
import javax.swing.text.*;
import java.util.*;
import java.sql.*;

import com.tool.*;
import com.worker.information.*;
import com.worker.main.*;
import com.worker.userpan.*;
/**
 *
 * @author Administrator
 */
public class DbCon {
    private static DbCon dbconins;
    private Connection connection;
    private Statement sta;
    private PreparedStatement sqlFind;
    private PreparedStatement sqlUpdate;
    private PreparedStatement sqlInsert;
    private PreparedStatement sqlDelete;
    private PreparedStatement sqlLastId;
    
    /** Creates a new instance of DbCon */
    private DbCon() {
        try {
            dbconins=this;
            Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
            connection = DriverManager.getConnection(
                    "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=worker","sa","the" );
            
            sqlFind = connection.prepareStatement("SELECT * FROM workerin WHERE workerID=?");
            sqlUpdate= connection.prepareStatement("UPDATE workerin SET name=?,sex=?,age=?,position=?,cellphone=?,salary=?,userl=?,userp=?,classify=?  WHERE workerID=? ");
            sqlInsert= connection.prepareStatement(
                    "INSERT INTO workerin (name,sex,age,position,cellphone,salary,userl,userp,classify) VALUES (?,?,?,?,?,?,?,?,?)");
            sqlDelete= connection.prepareStatement(
                    "DELETE FROM workerin WHERE workerID=?");
            sqlLastId= connection.prepareStatement(
                    "SELECT IDENT_CURRENT('workerin')");
            
            sta=connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            
        } catch ( ClassNotFoundException classNotFound ) {
            JOptionPane.showMessageDialog( null,
                    "sql 2000 jdbc driver not found", "Driver not found",
                    JOptionPane.ERROR_MESSAGE );
            
            System.exit( 1 );   // terminate application
        }
        
        // catch SQLException thrown by ResultSetTableModel
        // if problems occur while setting up database
        // connection and querying database
        catch ( SQLException sqlException ) {
            sqlException.printStackTrace();
            JOptionPane.showMessageDialog( null,
                    sqlException.toString(),
                    "Database error", JOptionPane.ERROR_MESSAGE );
            
            System.exit( 1 );   // terminate application
        }
    }
    
    public static void buildDbCon() {
        new DbCon();
    }
    
    public static DbCon getinstance() {
        return dbconins;
    }
    
    public Connection getconnection() {
        return connection;
    }
    
    public Statement getstatement() {
        return sta;
    }
    
    public void close() {
        try {
            sqlFind.close();
            sqlUpdate.close();
            sqlInsert.close();
            sqlDelete.close();
            sqlLastId.close();
            sta.close();
            connection.close();
        }
        // catch SQLExceptions and print error message
        catch ( SQLException sqlException ) {
            sqlException.printStackTrace();
        }
    }
    
    public ResultSet setSqlQuery(String que) {
        try {
            return sta.executeQuery(que);
        }
        // catch SQLExceptions and print error message
        catch ( SQLException sqlException ) {
            sqlException.printStackTrace();
            System.exit( 1 );   // terminate application
            return null;
        }
    }
    
    public void setSqlUpdate(String que) {
        try {
            sta.executeUpdate(que);
        }
        // catch SQLExceptions and print error message
        catch ( SQLException sqlException ) {
            sqlException.printStackTrace();
            System.exit( 1 );   // terminate application
            return;
        }
    }
    
    public Worker checkI(String user,String pass) {
        try {
            ResultSet loginC=sta.executeQuery("SELECT * FROM workerin WHERE userl='"+user+"'");
            while ( loginC.next() ) {
                Worker userW=extractResWorker(loginC);
                if ( pass.equals(userW.getuserp()) )
                    return userW;
            }
            JOptionPane.showMessageDialog(null,
                    " 用户不存在或密码错误 ", " 错误 " , JOptionPane.ERROR_MESSAGE);
            return new Worker();
        }
        // catch SQLExceptions and print error message
        catch ( SQLException sqlException ) {
            sqlException.printStackTrace();
            System.exit( 1 );   // terminate application
            return null;
        }
    }
    
    public Worker extractResWorker(ResultSet re) {
        try {
            Worker eW=new Worker();
            eW.setID(re.getInt("workerID"));
            eW.setname(re.getString("name"));
            eW.setsex(re.getString("sex"));
            eW.setposition(re.getString("position"));
            eW.setage(re.getInt("age"));
            eW.setcellphone(re.getString("cellphone"));
            eW.setsalary(re.getInt("salary"));
            eW.setuserl(re.getString("userl"));
            eW.setuserp(re.getString("userp"));
            eW.setclassify(re.getInt("classify"));
            return eW;
        }
        // catch SQLExceptions and print error message
        catch ( SQLException sqlException ) {
            sqlException.printStackTrace();
            return new Worker();
        }
    }
    
    public Worker findWorker(int wId) {
        try {
            // set query parameter and execute query
            sqlFind.setInt(1, wId );
            ResultSet resId=sqlFind.executeQuery();
            if (resId.next())
                return extractResWorker(resId);
            else {
                JOptionPane.showMessageDialog(null, " 没找到用户 ", " 错误 " , JOptionPane.ERROR_MESSAGE);
                return null;
            }
        } catch ( SQLException sqlException ) {
            sqlException.printStackTrace();
            return null;
        }
    }
    
    public void updateWorker(Worker wo) {
        try {
            sqlUpdate.setString(1,wo.getname());
            sqlUpdate.setString(2,wo.getsex());
            sqlUpdate.setInt(3,wo.getage());
            sqlUpdate.setString(4,wo.getposition());
            sqlUpdate.setString(5,wo.getcellphone());
            sqlUpdate.setInt(6,wo.getsalary());
            sqlUpdate.setString(7,wo.getuserl());
            sqlUpdate.setString(8,wo.getuserp());
            sqlUpdate.setInt(9,wo.getclassify());
            sqlUpdate.setInt(10,wo.getID());
            int p=wo.getID();
            sqlUpdate.executeUpdate();
        } catch ( SQLException sqlException ) {
            sqlException.printStackTrace();
            JOptionPane.showMessageDialog( null,
                    sqlException.toString(),
                    "Database error", JOptionPane.ERROR_MESSAGE );
            System.exit( 1 );   // terminate application
        }
    }
    
    public void insertWorker(Worker wo) {
        try {
            sqlInsert.setString(1,wo.getname());
            sqlInsert.setString(2,wo.getsex());
            sqlInsert.setInt(3,wo.getage());
            sqlInsert.setString(4,wo.getposition());
            sqlInsert.setString(5,wo.getcellphone());
            sqlInsert.setInt(6,wo.getsalary());
            sqlInsert.setString(7,wo.getuserl());
            sqlInsert.setString(8,wo.getuserp());
            sqlInsert.setInt(9,wo.getclassify());
            sqlInsert.executeUpdate();
        } catch ( SQLException sqlException ) {
            sqlException.printStackTrace();
            JOptionPane.showMessageDialog( null,
                    sqlException.toString(),
                    "Database error", JOptionPane.ERROR_MESSAGE );
            System.exit( 1 );   // terminate application
        }
    }
    
    public void deleteWorker(int wId) {
        try {
            sqlDelete.setInt(1,wId);
            sqlDelete.executeUpdate();
        } catch ( SQLException sqlException ) {
            sqlException.printStackTrace();
            JOptionPane.showMessageDialog( null,
                    sqlException.toString(),
                    "Database error", JOptionPane.ERROR_MESSAGE );
            System.exit( 1 );   // terminate application
        }
    }
    
    public int getLastId() {
        try {
            ResultSet lastIdR=sqlLastId.executeQuery();
            if (lastIdR.next())
                return lastIdR.getInt(1);
            else {
                JOptionPane.showMessageDialog(null, " 获取新输入员工资料错误 ", " 错误 " , JOptionPane.ERROR_MESSAGE);
                return -1;
            }
        } catch ( SQLException sqlException ) {
            sqlException.printStackTrace();
            JOptionPane.showMessageDialog( null,
                    sqlException.toString(),
                    "Database error", JOptionPane.ERROR_MESSAGE );
            System.exit( 1 );   // terminate application
            return -1;
        }
    }
}