JClass QuickTable
1 /*
2 * Copyright (c) 1998-2000, SITRAKA INC. All Rights Reserved.
3 * http://www.sitraka.com 
4 *
5 * This file is provided for demonstration and educational uses only.
6 * Permission to use, copy, modify and distribute this file for
7 * any purpose and without fee is hereby granted, provided that the
8 * above copyright notice and this permission notice appear in all
9 * copies, and that the name of Sitraka Software not be used in
10 * advertising or publicity pertaining to this material without the
11 * specific, prior written permission of an authorized representative of
12 * Sitraka Software.
13 *
14 * SITRAKA SOFTWARE MAKES NO REPRESENTATIONS OR WARRANTIES ABOUT THE
15 * SUITABILITY OF THE SOFTWARE, EITHER EXPRESS OR IMPLIED, INCLUDING
16 * BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS
17 * FOR A PARTICULAR PURPOSE, OR NON-INFRINGEMENT. SITRAKA SOFTWARE SHALL
18 * NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY USERS AS A RESULT OF USING,
19 * MODIFYING OR DISTRIBUTING THIS SOFTWARE OR ITS DERIVATIVES.
20 */
21
22 package examples.datasource.jdbc;
23
24 import com.klg.jclass.datasource.BaseVirtualColumn;
25 import com.klg.jclass.datasource.MetaDataModel;
26
27 import com.klg.jclass.datasource.TreeData;
28 import com.klg.jclass.datasource.jdbc.DataTableConnection;
29 import com.klg.jclass datasource.jdbc.MetaData;
30
31 /**
32 * This is an implementation of the JClass DataSource DataModel which
33 * relies on the our own JDBC wrappers (rather than IDE-specific data
34 * binding).
35 *
36 * It models a database for a fictitious bicycle company. The same
37 * schema has been implemented using an MS Access database
38 * and a SQLAnywhere database (demo.mdb and demo.db respectively).
39 * They contain the same table structures and data.
40 *
41 * The default is to use the jdbc-odbc bridge to connect to the Access
42 * implementation of the data base. You can change which data base is
43 * accessed by changing the dataBase variable to either SA or SYB below.
44 *
45 * This is the tree hierarchy for the data:
46 * Orders
47 * Customers
48 * Territory
49 * OrderDetails
50 * Products-Categories
51 *
52 */
53 public class DemoData extends TreeData {
54
55 public static final int MS = 1;
56 public static final int SA = 2;
57 public static final int SYB = 3;
58 Change the definition of database to any of the above constants.
59 int dataBase = MS;
60 DataTableConnection c;
61
62 public DemoData() {
63 String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
64 if (System.getProperty("java.vendor").indexOf("Microsoft") != -1) {
65 // use the driver that Microsoft Internet Explorer wants
66 driver = "com.ms.jdbc.odbc.JdbcOdbcDriver";
67 }
68 try {
69 switch (dataBase) {
70 case MS:
71 // This connection uses the jdbc-odbc bridge to
72 // connect to the Access implementation of the
73 // data base.
74 c = new DataTableConnection(
75 driver, // driver
76 "jdbc:odbc:JClassDemo", // url
77 "Admin", // user
78 "", // password
79 null); // database
80 break;
81
82 // This connection uses the jdbc-odbc bridge to connect
83 // to the SQLAnywhere implementation of the data base.
84 case SA:
85 c = new DataTableConnection(
86 "sun.jdbc.odbc.JdbcOdbcDriver", // driver
87 "jdbc:odbc:JClassDemoSQLAnywhere",// url
88 "dba", // user
89 "sql", // password
90 null); // database
91 break;
92
93 // This connection uses Sybase's jConnect type 4
94 // driver to connect to the SQLAnywhere implementation
95 // of the data base.
96 case SYB:
97 c = new DataTableConnection(
98 "com.sybase.jdbc.SybDriver", // driver
99 "jdbc:sybase:Tds:localhost:1498", // url
100 "dba", // user
101 "sql", // password
102 "HiGridDemoSQLAnywhere"); // database
103 break;
104 default:
105 System.out.println("No database chosen");
106 }
107
108 // Create the Orders MetaData
109 MetaData Orders = new MetaData(this, c,
" select * from Orders order by OrderID asc");
110 Orders.setDescription("Orders");
111
112 // Create the Customer MetaData
113 MetaData Customers = new MetaData(this, Orders, c);
114 Customers.setDescription("Customers");
115 Customers.setStatement(
"select * from Customers where CustomerID = ?");
116 Customers.joinOnParentColumn(
"CustomerID","CustomerID");
117 Customers.open();
118
119 // Create the Territory MetaData
120 MetaData Territory = new MetaData(this, Customers, c);
121 Territory.setDescription("Territory");
122 String t = "select TerritoryID,
TerritoryName from Territories
where TerritoryID = ?";
123 Territory.setStatement(t);
124 Territory.joinOnParentColumn("TerritoryID","TerritoryID");
125 Territory.open();
126
127 // Create the OrderDetails MetaData
128 // Three virtual columns are used:
129 //
130 // TotalLessTax (Quantity * UnitPrice),
131 // SalesTax (TotalLessTax * TaxRate) and
132 // LineTotal (TotalLessTax + SalesTax).
133 //
134 // Thus, when Quantity and/or UnitPrice is changed, these derived
135 // values reflect the changes immediately.
136 // Note 1: TaxRate is not a real column either, it is a
137 // constant returned by the sql statement.
138 // Note 2: Virtual columns can themselves be used to derive other
139 // virtual columns. They are evaluated from left to right.
140 MetaData OrderDetails = new MetaData(this, Orders, c);
141 OrderDetails.setDescription("OrderDetails");
142 String detail_query =
"select OrderDetailID, OrderID, ProductID, ";
143 detail_query += " DateSold, Quantity, UnitPrice, ";
144 detail_query += " '0.15' AS TaxRate ";
145 detail_query += " from OrderDetails where OrderID = ?";
146 OrderDetails.setStatement(detail_query);
147 OrderDetails.joinOnParentColumn("OrderID","OrderID");
148 OrderDetails.open();
149
150 Extend the row with some calculated values.
151 BaseVirtualColumn TotalLessTax = new BaseVirtualColumn(
152 "TotalLessTax",
153 java.sql.Types.FLOAT,
154 BaseVirtualColumn.PRODUCT,
155 new String[] {"Quantity", "UnitPrice"});
156 BaseVirtualColumn SalesTax = new BaseVirtualColumn(
157 "SalesTax",java.sql.Types.FLOAT,
158 BaseVirtualColumn.PRODUCT,
159 new String[] {"TotalLessTax", "TaxRate"});
160 BaseVirtualColumn LineTotal = new BaseVirtualColumn(
161 "LineTotal",java.sql.Types.FLOAT,
162 BaseVirtualColumn.SUM,
163 new String[] {"TotalLessTax", "SalesTax"});
164
165 OrderDetails.addColumn(TotalLessTax);
166 OrderDetails.addColumn(SalesTax);
167 OrderDetails.addColumn(LineTotal);
168
169 // Create the Products MetaData
170 MetaData Products = new MetaData(this, OrderDetails, c);
171 Products.setDescription("Products");
172 String query = "select a.ProductID,
a.ProductDescription,a.ProductName,";
173 query += " a.CategoryID, a.UnitPrice, a.Picture, ";
174 query += " b.CategoryName";
175 query += " from Products a, Categories b";
176 query += " where a.ProductID = ?";
177 query += " and a.CategoryID = b.CategoryID";
178 Products.setStatement(query);
179 Products.joinOnParentColumn("ProductID","ProductID");
180 Products.open();
181
182 // Override the table-column associations for the Products table
183 // to exclude the Picture column so it is not included as part of
184 // the update. Precision problems cause the server to think it's
185 // changed.
186 Products.setColumnTableRelations("Products",
new String[] {"ProductID",
"ProductDescription",
"ProductName",
"CategoryID",
"UnitPrice"});
187
188 // Override the default commit policy COMMIT_LEAVING_ANCESTOR
189 Orders.setCommitPolicy(MetaDataModel.COMMIT_LEAVING_RECORD);
190 OrderDetails.setCommitPolicy(
MetaDataModel.COMMIT_LEAVING_ANCESTOR);
191 Customers.setCommitPolicy(MetaDataModel.COMMIT_LEAVING_ANCESTOR);
192 Products.setCommitPolicy(MetaDataModel.COMMIT_MANUALLY);
193 Territory.setCommitPolicy(MetaDataModel.COMMIT_LEAVING_ANCESTOR);
194
195 } catch (Exception e) {
196 System.out.println(
"DemoData failed to initialize " + e.toString());
197 }
198 }
199
200 }

import javax.swing.*;
import quick.dbtable.*;
import java.sql.*;

public class QuickTableFrame extends JFrame
{
   public QuickTableFrame()
   {
      //set Frame properties
      setSize(300,200);
      setVisible(true);

      //create a new quicktable
      quick.dbtable.DBTable dBTable1 = new quick.dbtable.DBTable();

      //add to frame
      getContentPane().add(dBTable1);

      //set the database driver to be used, we are using jdbc-odbc driver
      dBTable1.setDatabaseDriver("sun.jdbc.odbc.JdbcOdbcDriver");

      /*
      set the jdbc url,"quicktabledemo" is the data source we have created
      for the database
      */
      dBTable1.setJdbcUrl("jdbc:odbc:quicktabledemo");

      // set the select statement which should be used by the table
      dBTable1.setSelectSql("select * from employee");

      //to create the navigation bars for the table
      dBTable1.createControlPanel();
     
      try
      {
         //connect to database & create a connection
         dBTable1.connectDatabase();
  
         //fetch the data from database to fill the table
         dBTable1.refresh();
      }
      catch(SQLException e)
      {
         e.printStackTrace();
      }
      catch(ClassNotFoundException e)
      {
         e.printStackTrace();
      }

   }

   public static void main(String[] args)
   {
      //create a new table frame
      QuickTableFrame myframe = new QuickTableFrame();
   }
}