data structure for database access

[f]

Hi,

Suppose I have tables in database,

Customer table with columns like customer ID, name, address...
Order table with columns like order ID, prices, total...

I am now writing java code to access database. I am thinking about two
approaches,

1. Define class like
class Customer{
int ID;
String name;
....
}

class Order{
int ID;
double price;
...
}

and write JDBC code to access database and fill these class
objects. I probably can use some code generation tool to write the
code. Or maybe even JDO?

2. Write a universal class
class Data{
String[] names;
....

int getIntColumn(String name);
double getDoubleColumn(String name);

static Data queryDatabase(String tableName, String[] columns,
String where);
};

and write JDBC code to access database and fill the data. I need
only wirte one class, and it works for any table or view for query.


Which is better?

Thanks,

ff

 

[Sudsy]

Hi,

Suppose I have tables in database,

Customer table with columns like customer ID, name, address...
Order table with columns like order ID, prices, total...

I am now writing java code to access database. I am thinking about two
approaches,

1. Define class like

2. Write a universal class

Which is better?

Thanks,

ff

I lean toward the first approach since there's less of an "impedance
mismatch" with the arguments supplied to the mutators and returned by
the accessors. There are supposedly some fairly capable OR (Object-
Relational) mapping tools available these days. I've written my own
so I haven't tried them out, but the underlying concepts are straight-
forward. I like extracting the information from the DatabaseMetaData
object and children, although you could also work from ERDs...
Just recognize that comprehending the underlying structure is also
important; it prevents you from trying something really dumb.

 

[Jose Rubio]

Option #1 is better. You're describing what is called the Value Object
pattern. You'll see these classes called something like CustomerVO.

then the JDBC access classes are what you called DAOs. Those classes can
instantiate the VOs with the data. This aproach is better because you can
pass the VO objects around, they can be auto generated fromt he database
structures, and the can be serialized.

Hope it helps,

Jose

 

[Guest]

Hi,

Suppose I have tables in database,

Customer table with columns like customer ID, name, address... Order table
with columns like order ID, prices, total...

Here's how I do it:

public class DataModel
{
public DataManager getManager() {...}
public DataManager createManager() {...}
public void setManager() {...}
public int getRowCount() {...}
public void clearRows() {...}
public int getColumnCount() {...}
public String getColumnName( int inCol ) {...}
public int getColumnType(int inCol) {...}
public String getKey( int inRow ) { ... }
public void setKey( int inRow, String inKey ) {...}
public Object getValueAt( int inRow, int inCol ) {...}
public String getStringAt( int inRow, int inCol)
{
Object value = getValueAt( inRow, inCol );
return ( value == null ) ? null : String.valueOf( value );
}
public void setValueAt( int inRow, int inCol, Object inValue ){...}
}

public class Customer extends DataModel
{
public static final int ID = 0;
public static final int NAME = 1;
public static final int ADDRESS = 2;

public DataManager createManager()
{
return new MySQLCustomerManager();
}

public DataSource getDataSource()
{
DataManager manager = getManager();
if ( manager == null )
{
setManager( manager = createManager() );
}
return (DataSource) manager;
}

public int getColumnCount()
{
return 3;
}

public String getColumnName( int inCol )
{
switch ( inCol )
{
case ID: return "ID";
case NAME: return "NAME";
case ADDRESS: return "ADDRESS";
}
return "Column " + inCol;
}

public void setKey( int inRow, String inKey )
{
setValueAt( inRow, ID, inKey );
}

public String getKey( int inRow )
{
return getStringAt( inRow, ID );
}

public void insert(String inName, String inAddress)
{
getDataSource().insert(this, inName, inAddress);
}
public void select( String inID)
{
getDataSource().select( this, inID );
}
public void delete( String inID )
{
getDataSource().delete( inID );
}
public static interface DataSource extends DataManager
{
public void insert( Customer inData, String inName, String inAddress);
...
}
}

public class MySQLCustomerManager extends MySQLDatabase
implements Customer.DataSource
{
...
}

I think you get the idea behind this abstraction. It's a heavy-duty
library for database-driven applications. By using this framework, you
can obtain your DataModels from different DataManagers. One client keeps
his user data in LDAP, his catalog in Oracle, and the tax rates in an XML
file.

I can't wait for J2SE1.5 and the enum for the column names.

-- La'ie Techie

 

[hilz]

This sounds interesting.

I like to have more details about how this works.
would you be willing to share more complete code? (an example that compiles
would be great!)

thanks
hilz

 

[f]

I like it too. But the problem is that every time you have a new
table, you need a new DAO and VO. Also, if you just need part of the
columns from one table, do you need new DAO or VO?

Thanks,

ff

Option #1 is better. You're describing what is called the Value Object
pattern. You'll see these classes called something like CustomerVO.

then the JDBC access classes are what you called DAOs. Those classes can
instantiate the VOs with the data. This aproach is better because you can
pass the VO objects around, they can be auto generated fromt he database
structures, and the can be serialized.

Hope it helps,

Jose

Hi,

Suppose I have tables in database,

Customer table with columns like customer ID, name, address...
Order table with columns like order ID, prices, total...

I am now writing java code to access database. I am thinking about two
approaches,

1. Define class like
class Customer{
int ID;
String name;
....
}

class Order{
int ID;
double price;
...
}

and write JDBC code to access database and fill these class
objects. I probably can use some code generation tool to write the
code. Or maybe even JDO?

2. Write a universal class
class Data{
String[] names;
....

int getIntColumn(String name);
double getDoubleColumn(String name);

static Data queryDatabase(String tableName, String[] columns,
String where);
};

and write JDBC code to access database and fill the data. I need
only wirte one class, and it works for any table or view for query.


Which is better?

Thanks,

ff

 

[Michael Borgwardt]

I like it too. But the problem is that every time you have a new
table, you need a new DAO and VO.

That's why you don't write them manually. Use a generator.

Also, if you just need part of the
columns from one table, do you need new DAO or VO?

OR mappers usually let you do this easily. I've had good experiences
with Hibernate: http://www.hibernate.org/

 

[f]

I want to know more, please,

Ff

Here's how I do it:

public class DataModel
{
public DataManager getManager() {...}
public DataManager createManager() {...}
public void setManager() {...}
public int getRowCount() {...}
public void clearRows() {...}
public int getColumnCount() {...}
public String getColumnName( int inCol ) {...}
public int getColumnType(int inCol) {...}
public String getKey( int inRow ) { ... }
public void setKey( int inRow, String inKey ) {...}
public Object getValueAt( int inRow, int inCol ) {...}
public String getStringAt( int inRow, int inCol)
{
Object value = getValueAt( inRow, inCol );
return ( value == null ) ? null : String.valueOf( value );
}
public void setValueAt( int inRow, int inCol, Object inValue ){...}
}

public class Customer extends DataModel
{
public static final int ID = 0;
public static final int NAME = 1;
public static final int ADDRESS = 2;

public DataManager createManager()
{
return new MySQLCustomerManager();
}

public DataSource getDataSource()
{
DataManager manager = getManager();
if ( manager == null )
{
setManager( manager = createManager() );
}
return (DataSource) manager;
}

public int getColumnCount()
{
return 3;
}

public String getColumnName( int inCol )
{
switch ( inCol )
{
case ID: return "ID";
case NAME: return "NAME";
case ADDRESS: return "ADDRESS";
}
return "Column " + inCol;
}

public void setKey( int inRow, String inKey )
{
setValueAt( inRow, ID, inKey );
}

public String getKey( int inRow )
{
return getStringAt( inRow, ID );
}

public void insert(String inName, String inAddress)
{
getDataSource().insert(this, inName, inAddress);
}
public void select( String inID)
{
getDataSource().select( this, inID );
}
public void delete( String inID )
{
getDataSource().delete( inID );
}
public static interface DataSource extends DataManager
{
public void insert( Customer inData, String inName, String inAddress);
...
}
}

public class MySQLCustomerManager extends MySQLDatabase
implements Customer.DataSource
{
...
}

I think you get the idea behind this abstraction. It's a heavy-duty
library for database-driven applications. By using this framework, you
can obtain your DataModels from different DataManagers. One client keeps
his user data in LDAP, his catalog in Oracle, and the tax rates in an XML
file.

I can't wait for J2SE1.5 and the enum for the column names.

-- La'ie Techie

 

[Guest]

This sounds interesting.

I like to have more details about how this works. would you be willing to
share more complete code? (an example that compiles would be great!)

thanks
hilz

I originally planned to post my code at
http://www.geocities.com/winget_solutions/ today, but I got sick over the
weekend. Please visit that site in the next few days.

Aloha,
La'ie Techie

 

[hilz]

That would be great.
I will definately check it out.

hope you'll get well soon!
cheers
hilz

 

[hilz]

OR mappers usually let you do this easily. I've had good experiences

with Hibernate: http://www.hibernate.org/

I am wondering about such mapping tools.
Do they work if you have an existing database and you want to create java
objects to access the existing database?

I looked at this website(hibernate.org), and i am unable to tell.
can you shed the light since you have already used it.

thanks
hilz.

 

[Christophe Vanfleteren]

I am wondering about such mapping tools.
Do they work if you have an existing database and you want to create java
objects to access the existing database?

Yes, hibernate can map any database, you just have to adjust the mapping
file. It can also generate mapping files for exisiting classes.

Look at http://www.hibernate.org/hib_docs/reference/html_single/#toolsetguide
to see some of those possibilities (the great thing about hibernate is that
it has really good documentation).

 

[Michael Borgwardt]

I am wondering about such mapping tools.
Do they work if you have an existing database and you want to create java
objects to access the existing database?

Yes, they can do that quite well. However, Hibernate does require you to
manually write a "mapping file" in that case, which describes the DB structure
in OO terms (and allows adjustments like renaming or not using some columns).
Since some important aspects like foreign key relationships are not
necessarily explicit in the DB schema, this task cannot be automated.

 

[Ben Jessel]

I have ValueObjects with injectors and selectors which I map to my
columns in my database, I have code written that automatically
populates my bean from a resultset:


public class DAOConvertor
{

/**
* <p>
* This converts a resultset into a value object dynamically via
the use
* of a set of bean-property / sql-column-name mappings.
* </p>
* @param a_Resultset Resultset used as the data source
* @param a_ColumnPropertyMap Map containing bean-property /
sql-column-name mappings.
* @param a_TargetValueObject Target value object to populate.
* @return Object A reference to the value object.
We have to use a return
* rather then rely on byRef because
this may be used
* in an EJB context. In this context
all results are
* byVal rather than byRef. Relying
on byRef is dangerous.
* @throws DAOException
*/
public static Object convert ( ResultSet a_Resultset, Map
a_ColumnPropertyMap, Object a_TargetValueObject )
throws DAOException
/*
-----------------------------------------------------------------------------
d1 26 Nov 03 Ben Jessel Created.
-----------------------------------------------------------------------------
*/
{
try
{

// Get an iterator to enable us to iterate through all
the properties
// in the hashmap.
Set stPropertyColumns = a_ColumnPropertyMap.keySet();
Iterator itPropertyColumns = stPropertyColumns.iterator();

// Iterate through each property / column-name pair
while ( itPropertyColumns.hasNext() )
{
// Get the next property
String nextKey = ( String ) itPropertyColumns.next();

// Get the SQL column corresponding to this property
// and query the resultset for the data in this column
Object obj = a_Resultset.getObject( ( String )
a_ColumnPropertyMap.get ( nextKey ) );

// Set the target object's property ( as pointed to by
the property
// key in the map ) with the data gleaned from the
resultset.

// As we are dealing with objects here, we let the
reflection
// API deal with type conversions.
BeanUtil.setProperty ( a_TargetValueObject, nextKey,
obj );
}

return a_TargetValueObject;
}
catch ( SQLException sql )
{
throw new DAOException ( sql );
}
catch ( AutoPopulationException ape )
{
throw new DAOException ( ape );
}

}


/**
* <p>Empty constructor</p>
*/
public DAOConvertor() {
super();
}

}

/*
*
* =============================================================================
*
* MobileInteractive Copyright (c) 2002
* <p>
* This code is copyright and should not be modified. Nor should the
* class files be decompiled, or recompiled with changed source
without
* the explicit permission from MobileInteractive.
* </p>
*
* Class Name : BeanUtil.java
*
* Log
* =============================================================================
* $Log$
*
*/
package com.mobileinteractive.util;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import com.mobileinteractive.exception.AutoPopulationException;
import com.mobileinteractive.exception.CheckedConfigurationException;

/**
* <P>
* Description : ++TO DO
* </P>
* @author $author$
* @version $revision$
*/
public class BeanUtil
{
public static final int SELECTORS = 1;
public static final int INJECTORS = 2;
public static final int INJECTORS_OR_SELECTORS = 3;

private BeanUtil() {}

//-- Constants -------------------------------------------------------------
public static final String c_ksClassName = "BeanUtil";

//-- Methods-- -------------------------------------------------------------

/**
* <p>
* This sets the property of the object supplied, with the
property and
* value supplied.
* </p>
* @param a_objTarget
* @param sPropertyField
* @param a_TargetValue
* @throws AutoPopulationException
*/
public static void setProperty ( Object a_objTarget, String
sPropertyField, Object a_TargetValue )
throws AutoPopulationException
/*
-----------------------------------------------------------------------------
d1 26 Nov 03 Ben Jessel Created.
-----------------------------------------------------------------------------
*/
{
final String ksLocation = "setProperty ( Object a_objTarget,
String sPropertyField, Object a_TargetValue )";

Map destProperties = getBeanProperties( a_objTarget, INJECTORS
);
try
{

// Can we find the property?
if ( destProperties.get( sPropertyField )!=null )
{
Object[] parameters = new Object [ 1 ] ;
parameters [ 0 ] = a_TargetValue;
( ( Method ) destProperties.get( sPropertyField )
).invoke ( a_objTarget, parameters );
}
else
{
throw new CheckedConfigurationException (
c_ksClassName + "." + ksLocation + " Cannot find field " +
sPropertyField);
}
}
catch ( InvocationTargetException ite )
{
throw new CheckedConfigurationException ( ite );
}
catch ( IllegalAccessException ile )
{
throw new CheckedConfigurationException ( ile );
}
}

/**
* <p>
* Populates a destination bean from a source bean.
* </p>
* @param a_Source
* @param a_Destination
* @return
*/
public static Object populate ( Object a_Source, Object
a_Destination )
{
Map srcProperties = getBeanProperties( a_Source, SELECTORS );
Map destProperties = getBeanProperties( a_Destination,
INJECTORS );


Iterator keySetIterator = srcProperties.keySet().iterator();
while ( keySetIterator.hasNext() )
{
String selectorKey = ( String ) ( keySetIterator.next()
);
Method selectorMethod = ( Method ) srcProperties.get(
selectorKey );

if ( destProperties.get( selectorKey )!=null )
{
Method injectorMethod = ( ( Method )
destProperties.get( selectorKey ) );

try
{

if ( injectorMethod.getParameterTypes().length ==
1 )
{
Object selectorResult[] = new Object[1];
selectorResult[0] = selectorMethod.invoke(
a_Source, null );

if (
injectorMethod.getParameterTypes()[0].isAssignableFrom(
selectorMethod.getReturnType()) )
{
// get the results from the selector
object
injectorMethod.invoke( a_Destination,
selectorResult );

}
else
{
if (
injectorMethod.getParameterTypes()[0].getName().equals(
"java.lang.String" ) )
{
Class clazz =
selectorMethod.getReturnType();
System.out.println(clazz.getName());

selectorResult[0] =
String.valueOf( selectorResult[0] );
injectorMethod.invoke(
a_Destination, selectorResult );
}

if (
injectorMethod.getParameterTypes()[0].getName().equals(
"java.lang.Long" ) )
{
Class clazz =
selectorMethod.getReturnType();
System.out.println(clazz.getName());

selectorResult[0] = Long.valueOf(
Sting.valueOf(selectorResult[0]) );
injectorMethod.invoke(
a_Destination, selectorResult );
}

if (
injectorMethod.getParameterTypes()[0].getName().equals( "long" ) )
{
Class clazz =
selectorMethod.getReturnType();
System.out.println(clazz.getName());

selectorResult[0] = Long.valueOf(
String.valueOf(selectorResult[0]==null ? "0" : selectorResult[0]) );
injectorMethod.invoke(
a_Destination, selectorResult );
}

if (
injectorMethod.getParameterTypes()[0].getName().equals( "int" ) )
{
Class clazz =
selectorMethod.getReturnType();
System.out.println(clazz.getName());

selectorResult[0] =
Integer.valueOf( String.valueOf(selectorResult[0]==null ? "0" :
selectorResult[0]) );
injectorMethod.invoke(
a_Destination, selectorResult );
}
if (
injectorMethod.getParameterTypes()[0].getName().equals( "byte" ) )
{
Class clazz =
selectorMethod.getReturnType();
System.out.println(clazz.getName());

selectorResult[0] = Byte.valueOf(
String.valueOf(selectorResult[0]==null ? "0" : selectorResult[0]) );
injectorMethod.invoke(
a_Destination, selectorResult );
}
if (
injectorMethod.getParameterTypes()[0].getName().equals( "float" ) )
{
Class clazz =
selectorMethod.getReturnType();
System.out.println(clazz.getName());

selectorResult[0] = Float.valueOf(
String.valueOf(selectorResult[0]==null ? "0" : selectorResult[0]) );
injectorMethod.invoke(
a_Destination, selectorResult );
}
if (
injectorMethod.getParameterTypes()[0].getName().equals( "double" ) )
{
Class clazz =
selectorMethod.getReturnType();
System.out.println(clazz.getName());

selectorResult[0] = Float.valueOf(
String.valueOf(selectorResult[0]==null ? "0" : selectorResult[0]) );
injectorMethod.invoke(
a_Destination, selectorResult );
}
if (
injectorMethod.getParameterTypes()[0].getName().equals( "char" ) )
{
// ??
}
if (
injectorMethod.getParameterTypes()[0].getName().equals(
"java.util.Date" ) )
{
selectorResult[0] = new Date(
String.valueOf(selectorResult[0]==null ? "" : selectorResult[0]) );
injectorMethod.invoke( a_Destination,
selectorResult );

}
}

}
}
catch (InvocationTargetException ite ) {}
catch (IllegalAccessException iea) {}
}

}
return a_Destination;
}




/**
* <p>
* This gets the bean properties for a supplied bean.
* A map is constructed where the key is the property name ( the
method
* name minus the starting set/get ). The value is the method
itself.
* This means that we can have fast searching by property name
on the key
* as well as quick method parameter type / return type.
* The options for the <code>a_PropertyType</code> are:
* <ul>
* <li> 1 - get SELECTOR bean properties. </ul>
* <li> 2 - get INJECTOR bean properties. </ul>
* </ul>
* Injector properties must have only one parameter.
* Selectors must have a non-void return type.
* </p>
* @param a_Bean
* @param a_PropertyType
* @return
*/
public static Map getBeanProperties ( Object a_Bean, int
a_PropertyType )
/*
---------------------------------------------------------------------------
d1 08 Sept 02 Ben Jessel Created.
---------------------------------------------------------------------------
*/
{
// get all the methods for this class
Method[] beanMethods = a_Bean.getClass().getMethods();
Map propertyMap = new HashMap();

for ( int i=0; i<beanMethods.length; i++ )
{
if ( ( a_PropertyType == SELECTORS ) &&
( beanMethods [ i ].getName().startsWith( "get" ) ) )
{
if ( !beanMethods [ i
].getReturnType().getName().equals("void"))
{
// Get the name of this method, and remove the
"get" to just
// give a property name
String propertyName = beanMethods [ i
].getName();
propertyName = propertyName.substring(
propertyName.indexOf("get")+3 );

propertyMap.put ( propertyName, beanMethods [ i ]
);
}
}
else if ( ( a_PropertyType == INJECTORS ) &&
( beanMethods [ i ].getName().startsWith( "set" ) ) )
{
if ( beanMethods [ i
].getParameterTypes().length==1 )
{
// Get the name of this method, and remove the
"set" to just
// give a property name
String propertyName = beanMethods [ i
].getName();
propertyName = propertyName.substring(
propertyName.indexOf("set")+3 );

propertyMap.put ( propertyName, beanMethods [ i
] );

}
}


}
return propertyMap;
}
}

Option #1 is better. You're describing what is called the Value Object
pattern. You'll see these classes called something like CustomerVO.

then the JDBC access classes are what you called DAOs. Those classes can
instantiate the VOs with the data. This aproach is better because you can
pass the VO objects around, they can be auto generated fromt he database
structures, and the can be serialized.

Hope it helps,

Jose

Hi,

Suppose I have tables in database,

Customer table with columns like customer ID, name, address...
Order table with columns like order ID, prices, total...

I am now writing java code to access database. I am thinking about two
approaches,

1. Define class like
class Customer{
int ID;
String name;
....
}

class Order{
int ID;
double price;
...
}

and write JDBC code to access database and fill these class
objects. I probably can use some code generation tool to write the
code. Or maybe even JDO?

2. Write a universal class
class Data{
String[] names;
....

int getIntColumn(String name);
double getDoubleColumn(String name);

static Data queryDatabase(String tableName, String[] columns,
String where);
};

and write JDBC code to access database and fill the data. I need
only wirte one class, and it works for any table or view for query.


Which is better?

Thanks,

ff

 

[Sudsy]

Yes, they can do that quite well. However, Hibernate does require you to
manually write a "mapping file" in that case, which describes the DB
structure
in OO terms (and allows adjustments like renaming or not using some
columns).
Since some important aspects like foreign key relationships are not
necessarily explicit in the DB schema, this task cannot be automated.

That's why I like to work from the DatabaseMetaData. Two very useful
methods are getImportedKeys and getExportedKeys, which address this
very issue.

 

[Hylander]

Hi,

Suppose I have tables in database,

Customer table with columns like customer ID, name, address...
Order table with columns like order ID, prices, total...

I am now writing java code to access database. I am thinking about two
approaches,

1. Define class like
class Customer{
int ID;
String name;
....
}

class Order{
int ID;
double price;
...
}

and write JDBC code to access database and fill these class
objects. I probably can use some code generation tool to write the
code. Or maybe even JDO?

2. Write a universal class
class Data{
String[] names;
....

int getIntColumn(String name);
double getDoubleColumn(String name);

static Data queryDatabase(String tableName, String[] columns,
String where);
};

and write JDBC code to access database and fill the data. I need
only wirte one class, and it works for any table or view for query.


Which is better?

Thanks,

ff

The logic, relationships, caching, versioning etc all will be affected
by decision two. You are rewriting the "database" from scratch. While
it is generic, it is weakly coherent with your ideas and can be
limiting. Over time,
your speed may suffer and indexing generic fields may never make
sense. (it could be worked but you'd find other issues too like
cascade logic)

The first option is best.

Scott Ambler has a website somewhere that goes over Data Modeling and
Object Modeling and all the options. It's a good read. It pays to
understand Data Modelling, the various types of keys, normalizations
and mathematical prowess of the Codd model and the difference between
that and the flexibility of Object modeling before doing too much. In
your simple case, you could easily get away with a one table to one
class approach until you find some good reasons to vary. You may also
have existing constraints on the object or physical side to consider.
ie: do you have existing database or pre-existing objects or design
with lots of inheritance? Many peole point to Hibernate for a
transparent persistence. Toplink / BEA is another somewhat older one.
JDO is ok too...depends on the feature match up there to your
requirements.

John