Package com.ablia.db description

Provide an architecture for database access.
Here is an example about the use of the classes under this package. We start with a simple database like this:

Here we have:

• 2 kernel entity (SUPPLIER and ITEM)
• 1 association entity (ITEM_SUPPLIER) with 2primary/foreign key
• 1 attribute entity (SUPPLIER_TYPE)

You should name your entity like this:

• all the entity (tables) singolar and capitalized;
• the primary key like "id_[name_of_the_entity_lowercased]". If an entity has a two-part name (like SUPPLIER_TYPE), the primary key should be "id_[first_part_lowercased][second_part_with_first_letter_capitalized][and so on]".

If you use these rules, you can use com.ablia.tools.Db2Bean class to automatically create your beans from a created database. Otherwhise you should create your bean manually inheriting the class com.ablia.DBAdapter and writing by hands all the abstract method.
The create statements for the database we use as example are (for a mysql database):
CREATE TABLE ITEM(
id_item VARCHAR(30) NOT NULL,
price DECIMAL(18,4),
description VARCHAR(30),
nota TEXT,
PRIMARY KEY (id_item),
UNIQUE UC_id_item (id_item));


CREATE TABLE SUPPLIER_TYPE(
id_supplierType VARCHAR(4) NOT NULL,
description VARCHAR(60),
PRIMARY KEY (id_supplierType),
UNIQUE UC_id_supplierType (id_supplierType));


CREATE TABLE SUPPLIER(
id_supplier INT NOT NULL AUTO_INCREMENT,
name VARCHAR(60),
address VARCHAR(60),
tel VARCHAR(30),
fax VARCHAR(30),
eMail VARCHAR(30),
city VARCHAR(30),
state VARCHAR(30),
nota TEXT,
id_supplierType VARCHAR(4) NOT NULL,
FOREIGN KEY (id_supplierType) REFERENCES SUPPLIER_TYPE (id_supplierType),
PRIMARY KEY (id_supplier));


CREATE TABLE ITEM_SUPPLIER(
price DECIMAL(18,4),
id_item VARCHAR(30) NOT NULL,
id_supplier INT NOT NULL,
FOREIGN KEY (id_item) REFERENCES ITEM (id_item),
FOREIGN KEY (id_supplier) REFERENCES SUPPLIER (id_supplier),
PRIMARY KEY (id_item,id_supplier));

Just cut and paste on the mysql client console to create it (You have to create a demo database with a statement like this

mysqladmin -uroot -proot create demo

Once you have created the database, lets use Db2Bean class to create the access bean. Here is the console on a Feebsd:

test# /usr/local/linux-jdk1.2.2/bin/java -cp /usr/local/linux-jdk1.2.2/jre/lib/rt.jar:/usr/local/ablia/java/lib/ablia.jar:/usr/local/ablia/java/lib/mysql.jar:/usr/local/ablia/java/classes/ com.ablia.tools.Db2Bean
Jdbc connection.....
Choose the Jdbc driver (it must be in your classpath):
(0) JDBC-ODBC:
(1) ORACLE THIN:
(2) IDB DRIVER:
(3) TWZ MYSQL:
(4) POSTGRES:
(5) MM MYSQL: //[hostname]/[dbname]:
(6) SYBASE:
(7) INTERBASE:
(8) DB2:
(9) INFORMIX IFX: [//][host]:[port]/[dbname]:INFORMIXSERVER=[dbservername]:
(10) ZYH DBF-FOXPRO: /[path to dbf files] or [//][host][:port]/[DatabasePath]:
Driver=5
jdbc resource: MM MYSQL: //[hostname]/[dbname]: //localhost/demo
Login: root
Password: root
Output path:(c:/000) /tmp
Package: com.demo
creating table bean ITEM.....Done
creating table bean ITEM_SUPPLIER.....Done
creating table bean SUPPLIER.....Done
creating table bean SUPPLIER_TYPE.....Done
Creating key classes ITEM_SUPPLIER ........Done
bye

In /tmp we should have these classes:

Item.java
ItemSupplier.java
ItemSupplierKey.java
Supplier.java
SupplierType.java

These classes ar just a start point. You should develop your own method just like a find method with a corresponding [bean]CR class (CR stands for search criteria) like this:

public com.ablia.util.Vectumerator findItemsByCR(
com.demo.ItemCR CR,
int pageNumber,
int pageRows)
throws com.ablia.db.DBAdapterException, java.sql.SQLException
{
//////////////////////////////////////////////////
// serach statement
//////////////////////////////////////////////////
String s_Sql_Find = "select DISTINCT A.* from ITEM AS A";
//////////////////////////////////////////////////
String s_Sql_Order = " order by A.description";

// search condition
String wc = ""; // where dataFineVld is null";
if (!CR.getDescription().equals(""))
    wc = buildWc(wc, "A.description like'" + CR.getDescription()+"%'");
//other search criteria


java.sql.PreparedStatement stmt =
getConn().prepareStatement(s_Sql_Find + wc + s_Sql_Order);

return findRows(stmt, pageNumber, pageRows);

}

The findRows(stmt, pageNumber, pageRows); return a Vectumerator (something like a Vector and an enumeration) with information about the tot number of records, the page we ar fetching and how many rows contain a page. This is usefull for page next / page previous command with jsp. You can use for this the nextVec, prevVec and pageList tag in com.ablia.taglib package.

To use the bean you just create you should:

• crate an com.ablia.db.ApplParm object with all necessary parameters to connect to a database (see the ApplParm constructor);
• Create the bean (com.demo.Item item = new com.demo.Item(ap);)
• Just use the bean: set some properties, save it, find all the objects/rows of the table, find by primary key, delete an object/row, and so on

Today, on march 2002, there are about 10/15 applications that use this architecture, for web site or for intranet application.