dbconn.java

GPRS数据通讯软件源码

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 * 数据库连接类
 */

package documenteditor;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.util.Date;
import java.util.Properties;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.tree.DefaultMutableTreeNode;

/**
 *
 * @author wangzhi
 */
public class DBConn extends Thread{
    
    private Connection conn = null;//数据库主连接
    
    private Statement Stm = null;//执行SQL的对象
    
    private Vector tbmv = new Vector();//向数据库连接发送的数据集合
    
    private DocumentEditorView dev = null;//视图
    
    private MessageQueue DBConnMessage = null;//数据库返回信息队列
    
    private LogClass logClass = null;//写日志线程
    
    private Properties ItmeProp = new Properties();//检测项目对应表
    
    public DBConn(DocumentEditorView dev,MessageQueue DBConnMessage,LogClass logClass) {        
        this.dev = dev;
        this.DBConnMessage = DBConnMessage;
        this.logClass = logClass;
        try {
            InputStream in = getClass().getResourceAsStream("ItmeConfig.properties");
            ItmeProp.load(in);
            in.close();
        } catch (IOException ex) {
            Logger.getLogger(DBConn.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            InputStream in = getClass().getResourceAsStream("DBConfig.properties");
            Properties p = new Properties();
            p.load(in);
            in.close();
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            conn = DriverManager.getConnection("jdbc:sqlserver://"
                    +p.getProperty("dbserver")+":1433;databaseName="
                    +p.getProperty("dbname")+";",p.getProperty("user"),p.getProperty("password"));
//            conn = DriverManager.getConnection("jdbc:sqlserver://LHY:1433;databaseName=lngprs;",p.getProperty("user"),p.getProperty("password"));
            
            Stm = conn.createStatement();
            /*
             * 查询MN号和站点对应的编号，格式如下：
             * MN号、站点编号、站点名称、烟道分割数
             * 按照站点名称排序
             */
            Vector vResult = getvResult("SELECT TOP 100 PERCENT dbo.T_DIC_MNSITESET.MN, dbo.T_DIC_MNSITESET.SITEID, dbo.T_DIC_SUBSITE.SITE_NAME, dbo.T_DIC_MNSITESET.split,dbo.T_DIC_SUBSITE.WASTE_CATALOG_ID "
                           + "FROM dbo.T_DIC_MNSITESET INNER JOIN dbo.T_DIC_SUBSITE ON dbo.T_DIC_MNSITESET.SITEID = dbo.T_DIC_SUBSITE.SITE_ID "
                           + "ORDER BY dbo.T_DIC_SUBSITE.SITE_NAME");
            for(int i=0;i<vResult.size();i++){
                Vector vTemp = (Vector)vResult.get(i);
                String MN = (String)vTemp.get(0);
                String SITEID = (String)vTemp.get(1);
                String SITE_NAME = (String)vTemp.get(2);
                String split = (String)vTemp.get(3);
                String WASTE_CATALOG_ID = (String)vTemp.get(4);
                GPRSClientThread gct = new GPRSClientThread();
                gct.getID().delete(0, gct.getID().length());
                gct.getID().append(MN);
                gct.setSite_id(SITEID);
                gct.setName(SITE_NAME);
                gct.setSplit(Integer.parseInt(split));
                if(WASTE_CATALOG_ID.equals("4")){
                    gct.setST("31");
                }else{
                    gct.setST("32");
                }
                DefaultMutableTreeNode tsn = new DefaultMutableTreeNode(gct);
                dev.getOfflineNode().add(tsn);
            }
            /*
             * 查询所有监测项目编号
             */
            vResult = getvResult("SELECT TOP 100 PERCENT ItemID "
                           + "FROM dbo.T_DIC_ITEMSET "
                           + "ORDER BY ItemID");
            Vector ItmeVector = new Vector();
            for(int i=0;i<vResult.size();i++){
                Vector vTemp = (Vector)vResult.get(i);
                String itemid = (String)vTemp.get(0);
                itemid += "--"+ItmeProp.getProperty(itemid);
                ItmeVector.add(itemid);
            }
            //System.out.println(ItmeVector);
            dev.getSetChannelJDialog().InitDialog(ItmeVector);
            //dev.getJTree1().updateUI();
            //dev.getJTree1().expandRow(1);
        } catch (SQLException ex) {
            Logger.getLogger(DBConn.class.getName()).log(Level.SEVERE, null, ex);
            DBConnMessage.Enqueue("数据库联接失败，请检查。");
            logClass.SendWriteLog("数据库联接失败，请检查。", "DBError");
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DBConn.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IOException ex) {
            Logger.getLogger(DBConn.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    //查询语句执行，返回集合类型
    public Vector getvResult(String sql) {
        Vector vResult = new Vector();
        try {            
            ResultSet rs = Stm.executeQuery(sql);
            int columnCount=rs.getMetaData().getColumnCount();
            while (rs.next()) {
                Vector vTemp = new Vector();
                for (int i = 0; i < columnCount; i++) {
                    String sTemp = rs.getString(i + 1);
                    vTemp.addElement(sTemp == null ? "" : sTemp.trim());
                }
                vResult.addElement(vTemp);
            }
        } catch (SQLException ex) {
            //Logger.getLogger(DBConn.class.getName()).log(Level.SEVERE, null, ex);
            logClass.SendWriteLog(sql, "DBError");
        }
        return vResult;
    }
    
    //非查询语句执行
    public void update(String Str) {
        try {
            Stm.executeUpdate(Str);
        } catch (SQLException ex) {
            DBConnMessage.Enqueue("SQL语句有错：");
            DBConnMessage.Enqueue(Str);
            logClass.SendWriteLog(Str, "DBError");
        } catch (NullPointerException ex) {            
        }
    }