databasemanagercommon.java

纯Java的数据库

     *
     * @param i
     *
     * @return
     */
    static int random(int i) {

        i = rRandom.nextInt() % i;

        return i < 0 ? -i
                     : i;
    }

    /**
     * Method declaration
     *
     */
    static void createTestTables(Statement sStatement) {

        String[] demo = {
            "DROP TABLE Item IF EXISTS;", "DROP TABLE Invoice IF EXISTS;",
            "DROP TABLE Product IF EXISTS;", "DROP TABLE Customer IF EXISTS;",
            "CREATE TABLE Customer(ID INTEGER PRIMARY KEY,FirstName VARCHAR(20),"
            + "LastName VARCHAR(20),Street VARCHAR(20),City VARCHAR(20));",
            "CREATE TABLE Product(ID INTEGER PRIMARY KEY,Name VARCHAR(20),"
            + "Price DECIMAL(10,2));",
            "CREATE TABLE Invoice(ID INTEGER PRIMARY KEY,CustomerID INTEGER,"
            + "Total DECIMAL(10,2), FOREIGN KEY (CustomerId) "
            + "REFERENCES Customer(ID) ON DELETE CASCADE);",
            "CREATE TABLE Item(InvoiceID INTEGER,Item INTEGER,"
            + "ProductID INTEGER,Quantity INTEGER,Cost DECIMAL(10,2),"
            + "PRIMARY KEY(InvoiceID,Item), "
            + "FOREIGN KEY (InvoiceId) REFERENCES "
            + "Invoice (ID) ON DELETE CASCADE, FOREIGN KEY (ProductId) "
            + "REFERENCES Product(ID) ON DELETE CASCADE);"
        };

        for (int i = 0; i < demo.length; i++) {

            // drop table may fail
            try {
                sStatement.execute(demo[i]);
            } catch (SQLException e) {
                ;
            }
        }
    }

    /**
     * Method declaration
     *
     */
    static String createTestData(Statement sStatement) throws SQLException {

        String[] name = {
            "White", "Karsen", "Smith", "Ringer", "May", "King", "Fuller",
            "Miller", "Ott", "Sommer", "Schneider", "Steel", "Peterson",
            "Heiniger", "Clancy"
        };
        String[] firstname = {
            "Mary", "James", "Anne", "George", "Sylvia", "Robert", "Janet",
            "Michael", "Andrew", "Bill", "Susanne", "Laura", "Bob", "Julia",
            "John"
        };
        String[] street = {
            "Upland Pl.", "College Av.", "- 20th Ave.", "Seventh Av."
        };
        String[] city   = {
            "New York", "Dallas", "Boston", "Chicago", "Seattle",
            "San Francisco", "Berne", "Oslo", "Paris", "Lyon", "Palo Alto",
            "Olten"
        };
        String[] product = {
            "Iron", "Ice Tea", "Clock", "Chair", "Telephone", "Shoe"
        };
        int      max     = 50;

        sStatement.execute("SET REFERENTIAL_INTEGRITY FALSE");

        for (int i = 0; i < max; i++) {
            sStatement.execute("INSERT INTO Customer VALUES(" + i + ",'"
                               + random(firstname) + "','" + random(name)
                               + "','" + random(554) + " " + random(street)
                               + "','" + random(city) + "')");
            sStatement.execute("INSERT INTO Product VALUES(" + i + ",'"
                               + random(product) + " " + random(product)
                               + "'," + (20 + 2 * random(120)) + ")");
            sStatement.execute("INSERT INTO Invoice VALUES(" + i + ","
                               + random(max) + ",0.0)");

            for (int j = random(20) + 2; j >= 0; j--) {
                sStatement.execute("INSERT INTO Item VALUES(" + i + "," + j
                                   + "," + random(max) + ","
                                   + (1 + random(24)) + ",1.5)");
            }
        }

        sStatement.execute("SET REFERENTIAL_INTEGRITY TRUE");
        sStatement.execute("UPDATE Product SET Price=ROUND(Price*.1,2)");
        sStatement.execute(
            "UPDATE Item SET Cost=Cost*"
            + "(SELECT Price FROM Product prod WHERE ProductID=prod.ID)");
        sStatement.execute("UPDATE Invoice SET Total=(SELECT SUM(Cost*"
                           + "Quantity) FROM Item WHERE InvoiceID=Invoice.ID)");

        return ("SELECT * FROM Customer");
    }

    /**
     * Method declaration
     * Redid this file to remove sizing requirements and to make it faster
     * Speeded it up 10 fold.
     * @param file
     * @return
     */
    static String readFile(String file) {

        try {
            FileReader     reader = new FileReader(file);
            BufferedReader read   = new BufferedReader(reader);
            StringBuffer   b      = new StringBuffer();
            String         s      = null;
            int            count  = 0;

            while ((s = read.readLine()) != null) {
                count++;

                b.append(s);
                b.append('\n');
            }

            read.close();
            reader.close();

            return b.toString();
        } catch (IOException e) {
            return e.getMessage();
        }
    }

    /**
     * Method declaration
     *
     *
     * @param file
     * @param text
     */
    static void writeFile(String file, String text) {

        try {
            FileWriter write = new FileWriter(file);

            write.write(text.toCharArray());
            write.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * Method declaration
     *
     *
     * @param sql
     * @param max
     *
     * @return
     *
     * @throws SQLException
     */
    static long testStatement(Statement sStatement, String sql,
                              int max) throws SQLException {

        long start = System.currentTimeMillis();

        if (sql.indexOf('#') == -1) {
            max = 1;
        }

        for (int i = 0; i < max; i++) {
            String s = sql;

            while (true) {
                int j = s.indexOf("#r#");

                if (j == -1) {
                    break;
                }

                s = s.substring(0, j) + ((int) (Math.random() * i))
                    + s.substring(j + 3);
            }

            while (true) {
                int j = s.indexOf('#');

                if (j == -1) {
                    break;
                }

                s = s.substring(0, j) + i + s.substring(j + 1);
            }

            sStatement.execute(s);
        }

        return (System.currentTimeMillis() - start);
    }

    private DatabaseManagerCommon() {}
}