In this example, a sales-oriented database is created.
package ianywhere.ultralitej.demo; import ianywhere.ultralitej.*; /** * CreateDb: sample program to demonstrate creation of simple sales Database and * load it with some data. * <p>The program also illustrates the use of ordinals when inserting rows into tables. */ public class CreateSales { static int ORDINAL_INVOICE_INV_NO; static int ORDINAL_INVOICE_NAME; static int ORDINAL_INVOICE_DATE; static int ORDINAL_INV_ITEM_INV_NO; static int ORDINAL_INV_ITEM_ITEM_NO; static int ORDINAL_INV_ITEM_PROD_NO; static int ORDINAL_INV_ITEM_QUANTITY; static int ORDINAL_INV_ITEM_PRICE; static int ORDINAL_PROD_NO; static int ORDINAL_PROD_NAME; static int ORDINAL_PROD_PRICE; /** Create the Database. * @return connection for a new Database */ private static Connection createDatabase() throws ULjException { Configuration config = DatabaseManager.createConfigurationFile( "Sales.ulj" ); Connection conn = DatabaseManager.createDatabase( config ); conn.schemaCreateBegin(); TableSchema table_schema = conn.createTable( "Product" ); table_schema.createColumn( "prod_no", Domain.INTEGER ); table_schema.createColumn( "prod_name", Domain.VARCHAR, 32 ); table_schema.createColumn( "price", Domain.NUMERIC, 9, (short)2 ); IndexSchema index_schema = table_schema.createPrimaryIndex( "prime_keys" ); index_schema.addColumn( "prod_no", IndexSchema.ASCENDING ); table_schema = conn.createTable( "Invoice" ); table_schema.createColumn( "inv_no", Domain.INTEGER ); table_schema.createColumn( "name", Domain.VARCHAR, 50 ); table_schema.createColumn( "date", Domain.DATE ); index_schema = table_schema.createPrimaryIndex( "prime_keys" ); index_schema.addColumn( "inv_no", IndexSchema.ASCENDING ); table_schema = conn.createTable( "InvoiceItem" ); table_schema.createColumn( "inv_no", Domain.INTEGER ); table_schema.createColumn( "item_no", Domain.INTEGER ); table_schema.createColumn( "prod_no", Domain.INTEGER ); table_schema.createColumn( "quantity", Domain.INTEGER ); table_schema.createColumn( "price", Domain.NUMERIC, 9, (short)2 ); index_schema = table_schema.createPrimaryIndex( "prime_keys" ); index_schema.addColumn( "inv_no", IndexSchema.ASCENDING ); index_schema.addColumn( "item_no", IndexSchema.ASCENDING ); conn.schemaCreateComplete(); return conn; } /** Populate the Database. * @param conn connection to Database */ private static void populateDatabase( Connection conn ) throws ULjException { PreparedStatement ri_product = conn.prepareStatement( "INSERT INTO Product( prod_no, prod_name, price ) VALUES( ?, ?, ? )" ); ORDINAL_PROD_NO = 1; ORDINAL_PROD_NAME = 2; ORDINAL_PROD_PRICE = 3; addProduct( ri_product, 2001, "blue screw", ".03" ); addProduct( ri_product, 2002, "red screw", ".09" ); addProduct( ri_product, 2004, "hammer", "23.99" ); addProduct( ri_product, 2005, "vice", "39.99" ); ri_product.close(); PreparedStatement ri_invoice = conn.prepareStatement( "INSERT INTO Invoice( inv_no, name, \"date\" )" + " VALUES( :inv_no, :name, :inv_date )" ); ORDINAL_INVOICE_INV_NO = ri_invoice.getOrdinal( "inv_no" ); ORDINAL_INVOICE_NAME = ri_invoice.getOrdinal( "name" ); ORDINAL_INVOICE_DATE = ri_invoice.getOrdinal( "inv_date" ); PreparedStatement ri_item = conn.prepareStatement( "INSERT INTO InvoiceItem( inv_no, item_no, prod_no, quantity, price )" + " VALUES( ?, ?, ?, ?, ? )" ); ORDINAL_INV_ITEM_INV_NO = 1; ORDINAL_INV_ITEM_ITEM_NO = 2; ORDINAL_INV_ITEM_PROD_NO = 3; ORDINAL_INV_ITEM_QUANTITY = 4; ORDINAL_INV_ITEM_PRICE = 5; addInvoice( ri_invoice, 2006001, "Jones Mfg.", "2006/12/23" ); addInvoiceItem( ri_item, 2006001, 1, 2001, 3000, ".02" ); addInvoiceItem( ri_item, 2006001, 2, 2002, 5000, ".08" ); addInvoice( ri_invoice, 2006002, "Smith Inc.", "2006/12/24" ); addInvoiceItem( ri_item, 2006002, 1, 2004, 2, "23.99" ); addInvoiceItem( ri_item, 2006002, 2, 2005, 3, "39.99" ); addInvoice( ri_invoice, 2006003, "Lee Ltd.", "2006/12/24" ); addInvoiceItem( ri_item, 2006003, 1, 2004, 5, "23.99" ); addInvoiceItem( ri_item, 2006003, 2, 2005, 4, "39.99" ); addInvoiceItem( ri_item, 2006003, 3, 2001, 800, ".03" ); addInvoiceItem( ri_item, 2006003, 4, 2002, 700, ".09" ); ri_item.close(); ri_invoice.close(); conn.commit(); } /** * mainline for program. * * @param args command-line arguments * */ public static void main ( String[] args ) { try { Connection conn = createDatabase(); populateDatabase( conn ); conn.release(); Demo.display( "CreateSales completed successfully" ); } catch( ULjException exc ) { Demo.displayExceptionFull( exc ); } } /** Add an invoice row. * @param conn connection to Database * @param inv_no invoice number * @param name name to whom invoice was sent */ private static void addInvoice( PreparedStatement ri, int inv_no, String name , String date ) throws ULjException { ri.set( ORDINAL_INVOICE_INV_NO, inv_no ); ri.set( ORDINAL_INVOICE_NAME, name ); ri.set( ORDINAL_INVOICE_DATE, date ); ri.execute(); } /** Add an invoice-item row. * @param conn connection to Database * @param inv_no invoice number * @param item_no line number for item * @param prod_no product number sold * @param quantity quantity sold * @param price price of one item */ private static void addInvoiceItem( PreparedStatement ri, int inv_no, int item_no , int prod_no, int quantity, String price ) throws ULjException { ri.set( ORDINAL_INV_ITEM_INV_NO, inv_no ); ri.set( ORDINAL_INV_ITEM_ITEM_NO, item_no ); ri.set( ORDINAL_INV_ITEM_PROD_NO, prod_no ); ri.set( ORDINAL_INV_ITEM_QUANTITY, quantity ); ri.set( ORDINAL_INV_ITEM_PRICE, price ); ri.execute(); } /** Add a product row. * @param conn connection to Database * @param prod_no product number * @param prod_name product name * @param price selling price */ private static void addProduct( PreparedStatement ri, int prod_no, String prod_name, String price ) throws ULjException { ri.set( ORDINAL_PROD_NO, prod_no ); ri.set( ORDINAL_PROD_NAME, prod_name ); ri.set( ORDINAL_PROD_PRICE, price ); ri.execute(); } } |
Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |