dboperate.java

connect DATABASE by java

package dbControl;

import java.sql.*;
import javax.sql.*;
import java.util.*; 
import java.io.*;
import herbObject.*;
// import com.microsoft.jdbc.sqlserver.sqlserverdriiver.*;
public class dbOperate
{
    private String dbname;
    private String user;
    private String pw;
    private String tn;
    private Vector results = new Vector();
    public dbOperate()
    {
    }
    public Vector searchDB(String dbname,String user,String pw,String command)
    {
        this.dbname=dbname;
        this.user=user;
        this.pw=pw;
        Vector searchResult =new Vector();
        analyseCommand ana = new analyseCommand(command);
        try 
        {
            // This is where we load the driver
            //com.microsoft.jdbc.sqlserver.sqlserverdriiver
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//             Class.forName("com.microsoft.jdbc.sqlserver.sqlserverdriiver");
        } 
        catch (ClassNotFoundException e) 
        {
            System.out.println("Unable to load Driver Class");
            //return;
        }              
        Statement stmt = null;
        Connection con=null;
        try
        {
            con =  DriverManager.getConnection("jdbc:odbc:"+dbname,user, pw);//206.246.91.2
//             String   dburl   =   "jdbc:microsoft:sqlserver://www.chinahealthcareinc.com:1433;user=sa;password=!admin4great;databasename=chci"; 
//             con =  DriverManager.getConnection(dburl);
            stmt = con.createStatement();
            Vector items = ana.searchAna();
            try
            {
                //stmt.execute("update " + tn + " set customerfname = 'alex' where " + tn + ".customerfname = 'alec'");               
                ResultSet rs;//Select employee.fname,employee.sname From employee
                String comm = ana.getCommand();
                System.out.println("comm = "+comm);
                rs = stmt.executeQuery(comm);
                ResultSetMetaData rsmd = rs.getMetaData();
                while(rs.next())
                {
                    Vector col = new Vector();
                    for(int i=0;i<items.size();i++)
                    {
                        col.addElement(rs.getString((String)items.elementAt(i)));
                    }
                    searchResult.addElement(col);
                }
                stmt.close();
            }
            catch (SQLException se) 
            {
                // Inform user of any SQL errors
                System.out.println("SQL Exception: " + se.getMessage(  ));
                se.printStackTrace(System.out);
                searchResult.removeAllElements();
            }
            catch (Exception e)
            {
                System.err.println("problems with SQL sent to "+ dbname+
                               ": "+e.getMessage());
                searchResult.removeAllElements();
            }
            con.close();
        }
        catch (Exception e)
        {
            System.err.println("problems connecting to "+dbname);
        }
        return searchResult;
    }
    public Vector searchDBDistinct(String dbname,String user,String pw,String command,String item)
    {
        this.dbname=dbname;
        this.user=user;
        this.pw=pw;
        Vector searchResult =new Vector();
//         analyseCommand ana = new analyseCommand(command);
        try 
        {
            // This is where we load the driver
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        } 
        catch (ClassNotFoundException e) 
        {
            System.out.println("Unable to load Driver Class");
            //return;
        }              
        Statement stmt = null;
        Connection con=null;
        try
        {
            con =  DriverManager.getConnection("jdbc:odbc:"+dbname,user, pw);//206.246.91.2
            stmt = con.createStatement();
            try
            {
                //stmt.execute("update " + tn + " set customerfname = 'alex' where " + tn + ".customerfname = 'alec'");               
                ResultSet rs;//Select employee.fname,employee.sname From employee
                rs = stmt.executeQuery(command);
                ResultSetMetaData rsmd = rs.getMetaData();
                int id=0;
                while(rs.next())
                {
                    id++;
                    System.out.println(item);
                    System.out.println(rs.getString(item));
                    searchResult.addElement(rs.getString(item));
                }
                System.out.println(id);
                stmt.close();
            }
            catch (SQLException se) 
            {
                // Inform user of any SQL errors
                System.out.println("SQL Exception: " + se.getMessage(  ));
                se.printStackTrace(System.out);
                searchResult.removeAllElements();
            }
            catch (Exception e)
            {
                System.err.println("problems with SQL sent to "+ dbname+
                               ": "+e.getMessage());
                searchResult.removeAllElements();
            }
            con.close();
        }
        catch (Exception e)
        {
            System.err.println("problems connecting to "+dbname);
        }
        return searchResult;
    }
    public void display()
    {
        for(int i=0;i<results.size();i++)
        {
            Vector tm = (Vector)results.elementAt(i);
            for(int j=0;j<tm.size();j++)
            {
                System.out.println((String)tm.elementAt(j));
            }
        }
    }
    //this method will be used to update the db table with special requests
    public void updateDB(String dbname,String user,String pw,String command)
    {
        try 
        {
            // This is where we load the driver
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        } 
        catch (ClassNotFoundException e) 
        {
            System.out.println("Unable to load Driver Class");
            //return;
        }              
        Statement stmt = null;
        Connection con=null;
        try
        {
            con =  DriverManager.getConnection("jdbc:odbc:"+dbname,user, pw);
            stmt = con.createStatement();
            try
            {
                stmt.execute(command);
                stmt.close();
            }
            catch (SQLException se) 
            {
                // Inform user of any SQL errors
                System.out.println("SQL Exception: " + se.getMessage(  ));
                se.printStackTrace(System.out);
            }
            catch (Exception e)
            {
                System.err.println("problems with SQL sent to "+ dbname+
                               ": "+e.getMessage());
            }
            con.close();
        }
        catch (Exception e)
        {
            System.err.println("problems connecting to "+dbname);
        }
    }
    //this method will be used to delete some information from different DB table according to some special request.
    public void deleteDB(String dbname,String user,String pw,String command)
    {
        try 
        {
            // This is where we load the driver
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        } 
        catch (ClassNotFoundException e) 
        {
            System.out.println("Unable to load Driver Class");
            //return;
        }              
        Statement stmt = null;
        Connection con=null;
        try
        {
            con =  DriverManager.getConnection("jdbc:odbc:"+dbname,user, pw);
            stmt = con.createStatement();
            try
            {
                stmt.execute(command);
                //stmt.execute("DELETE FROM "+ tn +" WHERE customerid = "+1);
                stmt.close();
            }
            catch (SQLException se) 
            {
                // Inform user of any SQL errors
                System.out.println("SQL Exception: " + se.getMessage(  ));
                se.printStackTrace(System.out);
            }
            catch (Exception e)
            {
                System.err.println("problems with SQL sent to "+ dbname+
                               ": "+e.getMessage());
            }
            con.close();
        }
        catch (Exception e)
        {
            System.err.println("problems connecting to "+dbname);
        }        
    }
    //this method will be used to store all kinds of information into different DB table
    public void storeDB(String dbname,String user,String pw,String tn,Vector storeDB)//,Vector storeDB
    {
        this.dbname=dbname;
        this.user=user;
        this.pw=pw;
        this.tn=tn;
        int index=0;
        if(tn.compareTo("branch")==0)