SQL to Java Code Generator Code

CREATE DATABASE IF NOT EXISTS jpetstore;

USE jpetstore;



DROP TABLE IF EXISTS LINEITEM;
DROP TABLE IF EXISTS ORDERSTATUS;
DROP TABLE IF EXISTS ORDERS;
DROP TABLE IF EXISTS BANNERDATA;
DROP TABLE IF EXISTS PROFILE;
DROP TABLE IF EXISTS SIGNON;
DROP TABLE IF EXISTS INVENTORY;
DROP TABLE IF EXISTS ITEM;
DROP TABLE IF EXISTS PRODUCT;
DROP TABLE IF EXISTS ACCOUNT;
DROP TABLE IF EXISTS CATEGORY;
DROP TABLE IF EXISTS SUPPLIER;
DROP TABLE IF EXISTS SEQUENCE;



CREATE TABLE SUPPLIER (
    suppid int NOT NULL,
    name varchar(80) null,
    status varchar(2) not null,
    addr1 varchar(80) null,
    addr2 varchar(80) null,
    city varchar(80) null,
    state varchar(80) null,
    zip varchar(5) null,
    phone varchar(80) null,
    PRIMARY KEY (suppid))
TYPE=MYISAM

PACK_KEYS=DEFAULT

ROW_FORMAT=DEFAULT;



CREATE TABLE SIGNON (
    username varchar(25) not null,
    password varchar(25)  not null,
    PRIMARY KEY (username))
TYPE=INNODB;

CREATE TABLE ACCOUNT (
    userid varchar(80) not null,
    email varchar(80) not null,
    firstname varchar(80) not null,
    lastname varchar(80) not null,
    status varchar(2)  null,
    addr1 varchar(80) not null,
    addr2 varchar(40) null,
    city varchar(80) not  null,
    state varchar(80) not null,
    zip varchar(20) not null,
    country varchar(20) not null,
    phone varchar(80) not null,
    PRIMARY KEY (userid))
TYPE=INNODB;

CREATE TABLE PROFILE (
    userid varchar(80) not null,
    langpref varchar(80) not null,
    favcategory varchar(30),
    mylistopt bool,
    banneropt bool,
    PRIMARY KEY (userid))
TYPE=INNODB;

CREATE TABLE BANNERDATA (
    favcategory varchar(80) not null,
    bannername varchar(255)  null,
    PRIMARY KEY (favcategory))
TYPE=MYISAM
PACK_KEYS=DEFAULT
ROW_FORMAT=DEFAULT;

CREATE TABLE ORDERS (
      orderid int not null,
      userid varchar(80) not null,
      orderdate date not null,
      shipaddr1 varchar(80) not null,
      shipaddr2 varchar(80) null,
      shipcity varchar(80) not null,
      shipstate varchar(80) not null,
      shipzip varchar(20) not null,
      shipcountry varchar(20) not null,
      billaddr1 varchar(80) not null,
      billaddr2 varchar(80)  null,
      billcity varchar(80) not null,
      billstate varchar(80) not null,
      billzip varchar(20) not null,
      billcountry varchar(20) not null,
      courier varchar(80) not null,
      totalprice decimal(10,2) not null,
      billtofirstname varchar(80) not null,
      billtolastname varchar(80) not null,
      shiptofirstname varchar(80) not null,
      shiptolastname varchar(80) not null,
      creditcard varchar(80) not null,
      exprdate varchar(7) not null,
      cardtype varchar(80) not null,
      locale varchar(80) not null,
      PRIMARY KEY (orderid) )
TYPE=INNODB;

CREATE TABLE ORDERSTATUS (
      orderid int not null,
      linenum int not null,
      timestamp date not null,
      status varchar(2) not null,
      PRIMARY KEY (orderid, linenum) )
TYPE=INNODB;

CREATE TABLE LINEITEM (
      orderid int not null,
      linenum int not null,
      itemid varchar(10) not null,
      quantity int not null,
      unitprice decimal(10,2) not null,
      PRIMARY KEY (orderid, linenum) )
TYPE=INNODB;

CREATE TABLE CATEGORY (
            catid varchar(10) not null,
            name varchar(80) null,
            descn varchar(255) null,
        PRIMARY KEY (catid))
TYPE=MYISAM
PACK_KEYS=DEFAULT
ROW_FORMAT=DEFAULT;

CREATE TABLE PRODUCT (
    productid varchar(10) not null,
    category varchar(10) not null,
    name varchar(80) null,
    descn varchar(255) null,
    PRIMARY KEY (productid))
TYPE=MYISAM
MAX_ROWS=1000
PACK_KEYS=DEFAULT
ROW_FORMAT=DEFAULT;
CREATE INDEX IX_PRODUCT_CAT  ON PRODUCT (category);
CREATE INDEX IX_PRODUCT_NAME ON PRODUCT (name);
ALTER TABLE PRODUCT ADD CONSTRAINT FK_PRODUCT_CATEGORY FOREIGN KEY (category) references CATEGORY (catid)
          ON DELETE RESTRICT
          ON UPDATE RESTRICT;

CREATE TABLE ITEM (
    itemid varchar(10) not null,
    productid varchar(10) not null,
    listprice decimal(10,2) null,
    unitcost decimal(10,2) null,
    supplier int null,
    status varchar(2) null,
    attr1 varchar(80) null,
    attr2 varchar(80) null,
    attr3 varchar(80) null,
    attr4 varchar(80) null,
    attr5 varchar(80) null,
PRIMARY KEY (itemid) )
TYPE=MYISAM
MAX_ROWS=10000
PACK_KEYS=DEFAULT
ROW_FORMAT=DEFAULT;
CREATE INDEX IX_ITEM_PRODUCT ON ITEM (productid);
CREATE INDEX IX_ITEM_SUPPLIER ON ITEM (supplier);

ALTER TABLE ITEM ADD CONSTRAINT FK_ITEM_PRODUCTID FOREIGN KEY (productid) references PRODUCT (productid);
ALTER TABLE ITEM ADD CONSTRAINT FK_ITEM_SUPPLIERID FOREIGN KEY (supplier) references SUPPLIER (suppid);

CREATE TABLE INVENTORY (
    itemid varchar(10) not null,
    qty int not null,
    PRIMARY KEY (itemid))
TYPE=INNODB;

CREATE TABLE SEQUENCE (
    name               varchar(30)  not null,
    nextid             int          not null,
    PRIMARY KEY (name))
TYPE=INNODB;