dbtools.java

连接数据库的程序~!里面有利用ODBC的方法,也有JDBC的方法!

import java.awt.*;
import java.util.*;
import java.sql.*;
//import com.microsoft.*;

public class DBTools
{
 private Connection connection;
 private Statement statement;
 private ResultSet resultset;
 private boolean debug = true;

 // Connect to a specified ODBC database
 // @databasename: the name of the ODBC database
 public DBTools(String databasename) {
   openDB(databasename);
 }

 //----------------------------------------------------------------------
 //open database
 public void openDB(String databasename) {
                                                      // The URL specifying the database's name to which this program connects
                                                      // using JDBC to connect to a Microsoft ODBC database
    String url = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=databasename";
    String username = "linzhu";
    String password = "123";
    

   // Load the driver to allow connection to the database
   try {
    Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
    System.out.println("add driver successfully"); 
    
    connection = DriverManager.getConnection(url, username, password);

    System.out.println("\n Database (" + databasename + ") is connected.");

   




   }
   catch (ClassNotFoundException cnfex){
     System.out.println("Failed to load JDBC/ODBC driver.");
         System.exit(1);
   }
   catch (SQLException sqlex){
         System.out.println("Unable to connect required database");
   }
}

//--------------------------------------------------------------------------------
// Disconnect database
  public void closeDB()
  {
    try {
      connection.close();
      System.out.println("\n Database is closed!");
    }
    catch ( SQLException sqlex ) {
      System.err.println( "Unable to disconnect required database" );
      sqlex.printStackTrace();
    }
  }

//---------------------------------------------------------------------------
  /** create a table into database */
  // @tablename: the name of the table to be created
  // @query: the SQL statement to create the table
  public void createTable(String tablename, String query) {
       try {
               // create scrollable and readonly statement
               statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                               ResultSet.CONCUR_READ_ONLY);

               // check if there is a table of old results
               ResultSet table = connection.getMetaData().getTables(null,null,
                                                   tablename, null);
               // if table does not exist, then create
               if(!table.next())
                   statement.executeUpdate("CREATE TABLE " + tablename + query);
               // if table exists, drop it and create a new one
               else {
                   statement.addBatch("DROP TABLE " + tablename);
                   statement.addBatch("CREATE TABLE " + tablename + query);
                   statement.executeBatch();
               }

             statement.close();
        }catch ( SQLException sqlex ) {
            sqlex.printStackTrace();
        }
  }

  //---------------------------------------------------------------------------
  public void createTables(String[] query, String[] tablename) {
    for( int i=0; i<tablename.length; i++)
       createTable(tablename[i], query[i]);
  }

  //---------------------------------------------------------------------------
  // insert one or more than one record to the end of the table
  public void insertrecords(String tablename, String[] query) {
    try {
               // create scrollable and readonly statement
               statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                               ResultSet.CONCUR_READ_ONLY);

               //  add the record to the end of the table
               for(int i=0; i<query.length; i++)
                  statement.addBatch("INSERT INTO " + tablename + query[i]);
               statement.executeBatch();
               statement.close();
        }catch ( SQLException sqlex ) {
            sqlex.printStackTrace();
        }
  }

//-----------------------------------------------------------------------------------
/** retrieve a table */
  public String[][] retrieveTable(String query) {
       String[][] record = null;
       try {
               // create scrollable and readonly statement
               statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                               ResultSet.CONCUR_READ_ONLY);

               resultset = statement.executeQuery(query);

               // parse retrieved entries and convert into matix objects
               int column = resultset.getMetaData().getColumnCount();
               resultset.last();
               int row = resultset.getRow();
               record = new String[row][column];
               
               for(int i=1; i<=row; i++) {
                 resultset.absolute(i);
                 for(int j=0; j<column; j++)
                    record[i-1][j]=resultset.getString(j+1);
               }

              statement.close();
        }catch ( SQLException sqlex ) {
            sqlex.printStackTrace();
            record = null;
        }
       return record;
  }



}