databaseinitializer.java

java 和flex的整合,主要是spring和flex的整合,不可多得啊

/*
 * Copyright 2002-2009 the original author or authors.
 * 
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * 
 *   http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package org.springframework.flex.samples.util;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;

/**
 * 
 * @author Christophe Coenraets
 * @author Jeremy Grelle
 */
public class DatabaseInitializer {

    private final JdbcTemplate template;

    public DatabaseInitializer(DataSource ds) {
        this.template = new JdbcTemplate(ds);
        createTableProduct();
        insertProducts();
        createTableContact();
        insertContacts();
        createTableIndustry();
        insertIndustries();
        createTableCompany();
        insertCompanies();
        createTableAccount();
        insertAccount();
    }

    public void createTableContact() {
        String sql = "CREATE TABLE IF NOT EXISTS CONTACT (" + "ID INT AUTO_INCREMENT PRIMARY KEY, " + "FIRST_NAME VARCHAR(50), "
            + "LAST_NAME VARCHAR(50), " + "ADDRESS VARCHAR(50), " + "CITY VARCHAR(50), " + "STATE VARCHAR(20), " + "ZIP VARCHAR(20), "
            + "PHONE VARCHAR(50), " + "EMAIL VARCHAR(50), " + "DOB DATE)";
        this.template.execute(sql);
    }

    public void createTableCompany() {
        String sql = "CREATE TABLE IF NOT EXISTS COMPANY (" + "ID INT AUTO_INCREMENT PRIMARY KEY, " + "NAME VARCHAR(50), " + "ADDRESS VARCHAR(50), "
            + "CITY VARCHAR(50), " + "STATE VARCHAR(20), " + "ZIP VARCHAR(20), " + "PHONE VARCHAR(50), " + "INDUSTRY_ID INT)";
        this.template.execute(sql);
    }

    public void createTableIndustry() {
        String sql = "CREATE TABLE IF NOT EXISTS INDUSTRY (" + "ID INT AUTO_INCREMENT PRIMARY KEY, " + "NAME VARCHAR(50))";
        this.template.execute(sql);
    }

    public void createTableProduct() {
        String sql = "CREATE TABLE IF NOT EXISTS PRODUCT (" + "ID INT AUTO_INCREMENT PRIMARY KEY, " + "NAME VARCHAR(50), " + "CATEGORY VARCHAR(50), "
            + "DESCRIPTION CLOB, " + "IMAGE VARCHAR(255), " + "PRICE DOUBLE, " + "QTY INT)";
        this.template.execute(sql);
    }

    public void createTableAccount() {
        String sql = "CREATE TABLE IF NOT EXISTS ACCOUNT (" + "ID INT AUTO_INCREMENT PRIMARY KEY, " + "NAME VARCHAR(50)," + "TYPE INT,"
            + "INDUSTRY INT," + "OWNER INT," + "PHONE VARCHAR(30)," + "FAX VARCHAR(30)," + "TICKER VARCHAR(10)," + "OWNERSHIP VARCHAR(20),"
            + "NUMBER_EMPLOYEES INT," + "ANNUAL_REVENUE DOUBLE," + "PRIORITY INT," + "RATING INT," + "ADDRESS1 VARCHAR(50),"
            + "ADDRESS2 VARCHAR(50)," + "CITY VARCHAR(50)," + "STATE VARCHAR(50)," + "ZIP VARCHAR(20)," + "URL VARCHAR(50)," + "NOTES CLOB,"
            + "CURRENT_YEAR_RESULTS DOUBLE," + "LAST_YEAR_RESULTS DOUBLE)";
        this.template.execute(sql);
    }

    public void insertContacts() {
        int rowCount = this.template.queryForInt("SELECT COUNT(*) FROM CONTACT");
        if (rowCount > 0) {
            System.out.println("Contacts already exist");
            return;
        }
        System.out.println("Inserting sample data in table CONTACT...");
        String sql = "INSERT INTO CONTACT (FIRST_NAME, LAST_NAME, ADDRESS, CITY, STATE, ZIP, PHONE, EMAIL) VALUES (?,?,?,?,?,?,?,?)";
        this.template.update(sql, new Object[] { "Christophe", "Coenraets", "275 Grove St", "Newton", "MA", "02476", "617-219-2000",
            "[e-mail]" });
        this.template.update(sql, new Object[] { "John", "Smith", "1 Main st", "Boston", "MA", "01744", "617-219-2001", "[e-mail]" });
        this.template.update(sql, new Object[] { "Lisa", "Taylor", "501 Townsend st", "San Francisco", "CA", "", "415-534-7865", "[e-mail]" });
        this.template.update(sql, new Object[] { "Noah", "Jones", "1200 5th Avenue ", "New York", "NY", "", "212-764-2345", "[e-mail]" });
        this.template.update(sql, new Object[] { "Bill", "Johnson", "1345 6th street", "Chicago", "IL", "", "", "[e-mail]" });
        this.template.update(sql, new Object[] { "Chloe", "Rodriguez", "34 Elm street", "Dallas", "TX", "", "415-534-7865", "[e-mail]" });
        this.template.update(sql, new Object[] { "Jorge", "Espinosa", "23 Putnam Avenue", "Seattle", "WA", "", "", "[e-mail]" });
        this.template.update(sql, new Object[] { "Amy", "King", "11 Summer st", "Miami", "FL", "", "", "[e-mail]" });
        this.template.update(sql, new Object[] { "Boris", "Jefferson", "222 Spring st", "Denver", "CO", "", "415-534-7865", "[e-mail]" });
        this.template.update(sql, new Object[] { "Linda", "Madison", "564 Winter st", "Washington", "DC", "", "", "[e-mail]" });
    }

    public void insertCompanies() {
        int rowCount = this.template.queryForInt("SELECT COUNT(*) FROM COMPANY");
        if (rowCount > 0) {
            System.out.println("Companies already exist");
            return;
        }
        System.out.println("Inserting sample data in table COMPANY...");
        String sql = "INSERT INTO COMPANY (NAME, ADDRESS, CITY, STATE, ZIP, PHONE, INDUSTRY_ID) VALUES (?,?,?,?,?,?,?)";
        this.template.update(sql, new Object[] { "Adobe", "", "San Jose", "CA", "", "408", 1 });
        this.template.update(sql, new Object[] { "SpringSource", "", "New York", "NY", "", "212", 2 });
        this.template.update(sql, new Object[] { "Allaire", "", "Cambridge", "MA", "", "212", 3 });
        this.template.update(sql, new Object[] { "Acme", "", "Denver", "CO", "", "212", 4 });
        this.template.update(sql, new Object[] { "Macromedia", "", "San Francisco", "CA", "", "212", 1 });
        this.template.update(sql, new Object[] { "Alpha Corp", "", "Chicago", "IL", "", "", 1 });
    }

    public void insertIndustries() {
        int rowCount = this.template.queryForInt("SELECT COUNT(*) FROM INDUSTRY");
        if (rowCount > 0) {
            System.out.println("Industries already exist");
            return;
        }
        System.out.println("Inserting sample data in table INDUSTRY...");
        String sql = "INSERT INTO INDUSTRY (NAME) VALUES (?)";
        this.template.update(sql, new Object[] { "Telecommunication" });
        this.template.update(sql, new Object[] { "Government" });
        this.template.update(sql, new Object[] { "Financial Services" });
        this.template.update(sql, new Object[] { "Life Sciences" });
        this.template.update(sql, new Object[] { "Manufacturing" });
        this.template.update(sql, new Object[] { "Education" });
    }

    public void insertProducts() {
        int rowCount = this.template.queryForInt("SELECT COUNT(*) FROM PRODUCT");
        if (rowCount > 0) {
            System.out.println("Products already exist");
            return;
        }
        System.out.println("Inserting sample data in table PRODUCT...");
        String sql = "INSERT INTO PRODUCT (NAME, CATEGORY, IMAGE, PRICE, DESCRIPTION, QTY) VALUES (?,?,?,?,?,?)";
        this.template.update(
            sql,
            new Object[] {
                "Nokia 6010",
                "6000",
                "Nokia_6010.gif",
                99.0E0,
                "Easy to use without sacrificing style, the Nokia 6010 phone offers functional voice communication supported by text messaging, multimedia messaging, mobile internet, games and more.",
                21 });
        this.template.update(
            sql,
            new Object[] {
                "Nokia 3100 Blue",
                "9000",