JDBC对数据表的基本操作（JDBC之二）

创建一个数据库表

try {

        Statement stmt = connection.createStatement();

        // Create table called my_table 
   
   

        String sql = "CREATE TABLE my_table(col_string VARCHAR(254))";

        stmt.executeUpdate(sql);

    } catch (SQLException e) {

    }

删除数据库表

try {

        Statement stmt = connection.createStatement();

        stmt.executeUpdate("DROP TABLE my_table");

    } catch (SQLException e) {

    }

列出数据库中全部的表名称

try {

        // Gets the database metadata
   
   

        DatabaseMetaData dbmd = connection.getMetaData();

        // Specify the type of object; in this case we want tables
   
   

        String[] types = {"TABLE"};

        ResultSet resultSet = dbmd.getTables(null, null, "%", types);

        // Get the table names 获取数据库表名
   
   

        while (resultSet.next()) {

            // Get the table name 
   
   

            String tableName = resultSet.getString(3);

            // Get the table's catalog and schema names (if any)
   
   

            String tableCatalog = resultSet.getString(1);

            String tableSchema = resultSet.getString(2);

        }

    } catch (SQLException e) {

    }

创建用于java数据类型的MYSQL数据表

try {

        Statement stmt = connection.createStatement();

        String sql = "CREATE TABLE mysql_all_table("

            + "col_boolean       BOOL, "              // boolean

            + "col_byte          TINYINT, "           // byte

            + "col_short         SMALLINT, "          // short

            + "col_int           INTEGER, "           // int

            + "col_long          BIGINT, "            // long

            + "col_float         FLOAT, "             // float

            + "col_double        DOUBLE PRECISION, "  // double

            + "col_bigdecimal    DECIMAL(13,0), "     // BigDecimal

            + "col_string        VARCHAR(254), "      // String

            + "col_date          DATE, "              // Date

            + "col_time          TIME, "              // Time

            + "col_timestamp     TIMESTAMP, "         // Timestamp

            + "col_asciistream   TEXT, "              // AsciiStream (< 2^16 bytes)

            + "col_binarystream  LONGBLOB, "          // BinaryStream (< 2^32 bytes)

            + "col_blob          BLOB)";              // Blob (< 2^16 bytes)

        stmt.executeUpdate(sql);

    } catch (SQLException e) {

    }

Creating an Oracle Table to Store Java Types

try {

        Statement stmt = connection.createStatement();

        // Create a VARRAY type; see e301 Creating a VARRAY Type in an Oracle Database

        stmt.execute("CREATE TYPE number_varray AS VARRAY(10) OF NUMBER(12, 2)");

        // Create an OBJECT type; e296 Creating an OBJECT Type in an Oracle Database

        stmt.execute ("CREATE TYPE my_object AS OBJECT(col_string2 VARCHAR(30), col_int2 INTEGER)");

        // Note that Oracle database only allows at most one column of LONG type in a table.

        //    Column Name          Oracle Type             Java Type

        String sql = "CREATE TABLE oracle_all_table("

            + "col_short           SMALLINT, "          // short

            + "col_int             INTEGER, "           // int

            + "col_float           REAL, "              // float; can also be NUMBER

            + "col_double          DOUBLE PRECISION, "  // double; can also be FLOAT or NUMBER

            + "col_bigdecimal      DECIMAL(13,0), "     // BigDecimal

            + "col_string          VARCHAR2(254), "     // String; can also be CHAR(n)

            + "col_characterstream LONG, "              // CharacterStream or AsciiStream

            + "col_bytes           RAW(2000), "         // byte[]; can also be LONG RAW(n)

            + "col_binarystream    RAW(2000), "         // BinaryStream; can also be LONG RAW(n)

            + "col_timestamp       DATE, "              // Timestamp

            + "col_clob            CLOB, "              // Clob

            + "col_blob            BLOB, "              // Blob; can also be BFILE

            + "col_array           number_varray, "     // oracle.sql.ARRAY

            + "col_object          my_object)";         // oracle.sql.OBJECT

        stmt.executeUpdate(sql);

    } catch (SQLException e) {

    }

创建适合java数据存储的SQL 2000数据表

try {

        Statement stmt = connection.createStatement();

        //     Column Name          SQLServer Type           Java Type
   
   

        String sql = "CREATE TABLE sqlserver_all_table("

            + "col_boolean          BIT, "                // boolean

            + "col_byte             TINYINT, "            // byte

            + "col_short            SMALLINT, "           // short

            + "col_int              INTEGER, "            // int

            + "col_float            REAL, "               // float

            + "col_double           DOUBLE PRECISION, "   // double

            + "col_bigdecimal       DECIMAL(13,0), "      // BigDecimal; can also be NUMERIC(p,s)

            + "col_string           VARCHAR(254), "       // String

            + "col_date             DATETIME, "           // Date

            + "col_time             DATETIME, "           // Time

            + "col_timestamp        TIMESTAMP, "          // Timestamp

            + "col_characterstream  TEXT, "               // CharacterStream or AsciiStream (< 2 GBytes)

            + "col_binarystream     IMAGE)";              // BinaryStream (< 2 GBytes)

        stmt.executeUpdate(sql);

    } catch (SQLException e) {

    }