sqlhelper.java

大量java源程序

/*
 * @(#)SQLHelper
 *
 * Copyright (c) 1998 Karl Moss. All Rights Reserved.
 *
 * You may study, use, modify, and distribute this software for any
 * purpose provided that this copyright notice appears in all copies.
 *
 * This software is provided WITHOUT WARRANTY either expressed or
 * implied.
 *
 * @author  Karl Moss
 * @version 1.0
 * @date    11Mar98
 *
 */

package javaservlets.db;

import java.sql.*;

/**
 * <p>This contains helper methods for building SQL tables
 */

public class SQLHelper
{
  // Connection object
  java.sql.Connection m_con;

  // Quote character for Strings
  String m_quote;

  /**
    * <p>Construct a new helper class
    *
    * @param connection SQL connection
    */
  public SQLHelper(java.sql.Connection con) throws Exception
    {
      m_con = con;

      // Cache the indentifier quote string
      java.sql.DatabaseMetaData md = con.getMetaData();
      m_quote = md.getIdentifierQuoteString();
    }

  /**
    * <p>Given a JDBC SQL type, return the type name as known by
    * the database and any creation parameters that it requires
    *
    * @param type JDBC SQL type
    * @return SQL type name
    */
  public String getTypeName(int type) throws Exception
    {
      if (m_con == null) {
        return null;
      }

      String name = null;

      // Get the database meta data
      java.sql.DatabaseMetaData md = m_con.getMetaData();

      // Get a list of all of the SQL types supported by
      // the data source
      java.sql.ResultSet rs = md.getTypeInfo();

      // Now loop through and find the first type name for the
      // given JDBC type
      while (rs.next()) {

        // Get the name of the column. We may not need it but
        // to ensure that we work against all types of data sources
        // we have to get the columns in order
        String typeName = rs.getString(1);

        // Get the type of the column
        int sqlType = rs.getInt(2);

        // Get the creation parameters
        String params = rs.getString(6);

        // Compare
        if (sqlType == type) {
          name = typeName.trim();

          if (params != null) {
            name += "(" + params.trim() + ")";
          }

          break;
        }
      }

      return name;
    }

  /**
    * <p>Drops the given table name
    *
    * @param table Table name to drop
    */
  public void drop(String name)
    {
      java.sql.Statement stmt = null;

      try {
        String sql = "drop table " + name;

        // Create a statement object
        stmt = m_con.createStatement();

        // Submit the drop table statement to the database
        stmt.executeUpdate(sql);
      }
      catch (Exception ex) {
        // Ignore errors
      }
      finally {

        // Always ensure that the statement gets closed properly
        if (stmt != null) {
          try {
            stmt.close();
          }
          catch (Exception ex) {
          }
        }
      }
    }

  /**
    * <p>Creates the given table with the given column descriptions
    *
    * @param name Table name to create
    * @param colDesc Array of ColumnDesc objects describing each
    * column in the table
    */
  public void create(String name, ColumnDesc cols[])
    throws Exception
    {
      if (cols == null) {
        return;
      }

      boolean didCreate = false;

      String sql = "create table " + name + " (";

      // Loop for each column description

      for (int i = 0; i < cols.length; i++) {
        ColumnDesc c = cols[i];

        if (c == null) {
          continue;
        }

        // Get the type name
        String typeName = getTypeName(c.getType());

        // Format the SQL string

        // If this isn't the first column, separate with a comma
        if (i > 0) {
          sql += ",";
        }

        // Add the column name
        sql += c.getName() + " ";

        // Add the length and decimal places if necessary
        sql += formatType(typeName, c);
      }
      sql += ")";

      java.sql.Statement stmt = null;

      try {
        // Create a statement object
        stmt = m_con.createStatement();

        System.out.println("Executing " + sql);

        // Submit the drop table statement to the database
        stmt.executeUpdate(sql);
        didCreate = true;
      }
      finally {
        if (!didCreate) {

          // The table did not get created. Display the sql
          // statement for debugging purposes
          System.out.println(sql);
        }

        // Ensure that we always close the statement properly
        if (stmt != null) {
          try {
            stmt.close();
          }
          catch (Exception ex) {
          }
        }
      }
    }

  /**
    * <p>Prepares a SQL insert statement
    *
    * @param name Table name
    * @param colDesc Array of ColumnDesc objects describing each
    * column in the table
    * @return Prepared statement
    */
  public java.sql.PreparedStatement prepareInsert(String name,
                                                  ColumnDesc cols[])
    throws Exception
    {
      if (cols == null) {
        return null;
      }

      String sql = "insert into " + name + "(";
      String values = "(";

      // Loop for each column description
      for (int i = 0; i < cols.length; i++) {

        ColumnDesc c = cols[i];

        // Append a comma if not the first column
        if (i > 0) {
          sql += ",";
          values += ",";
        }

        // Append the column name and a parameter marker
        sql += c.getName();
        values += "?";
      }

      // Complete the SQL statement
      sql += ") values " + values + ")";

      // Prepare the statement
      java.sql.PreparedStatement ps = m_con.prepareStatement(sql);

      return ps;
    }

  /**
    * <p>Given a type name and the creation parameters, format
    * the type appropriately for a create statement
    *
    * @param name Type name with creation parameters
    * @param col ColumnDesc object
    * @return Formatted creation type
    */
  private String formatType(String name, ColumnDesc c)
    {
      String type = "";

      // If there are no creation parameters, just return
      // the name
      int index = name.indexOf("(");
      if (index < 0) {
        return name;
      }

      // We have one creation parameter that we will assume is
      // the length. Only add the length if a length was
      // given

      type = name.substring(0, index);

      if (c.getLength() > 0) {
        type += "(" + c.getLength();

        // Any more parameters?
        index = name.indexOf(",", index);
        if (index > 0) {
          // Found another parameter. Assume it is the number of
          // decimal places
          if (c.getDecimals() > 0) {
            type += "," + c.getDecimals();
          }
        }
        type += ")";
      }

      return type;
    }
}