Use your own database table from Java
Methods
The class DatabaseMetadataUtils has a set of methods to select, update and delete queries:
Method | return | Description |
---|---|---|
buildQuery(String table, String filter, String order) | String |
Returns a hibernate selected query. |
buildUpdate(String table, String values, String filter) | String | Returns a hibernate updated query. |
buildDelete(String table, String filter) |
String | Returns a hibernate update query. |
This method will retrieve a Hibernate query where each "$column_name" will be replaced by it's real column name.
These queries can be executed by the method executeHQL(String query) at LegacyDAO class.
The return will be a list of DatabaseMetadataValue objects. |
Sample
Register database metadata table definition
Table named "security" with tree columns:
- Column name "uuid_id" of type text.
- Column name "type" of type text.
- column name "name" of type text.
Go to Administration > Tools > Database query and execute the SQL:
INSERT INTO OKM_DB_METADATA_TYPE (DMT_TABLE, DMT_REAL_COLUMN, DMT_TYPE, DMT_VIRTUAL_COLUMN) VALUES ('security', 'col00', 'text', 'uuid_id');
INSERT INTO OKM_DB_METADATA_TYPE (DMT_TABLE, DMT_REAL_COLUMN, DMT_TYPE, DMT_VIRTUAL_COLUMN) VALUES ('security', 'col01', 'text', 'type');
INSERT INTO OKM_DB_METADATA_TYPE (DMT_TABLE, DMT_REAL_COLUMN, DMT_TYPE, DMT_VIRTUAL_COLUMN) VALUES ('security', 'col02', 'text', 'name');
Java classes
File ExtendedSecurity.java:
public class ExtendedSecurity extends DatabaseMetadataCommon implements IsSerializable {
public static final String TYPE_USER = "user";
public static final String TYPE_ROLE = "role";
// Metadata Virtual Name mapping
public static final String MV_TABLE_NAME = "security";
public static final String MV_COLUMN_NAME_UUID = "uuid_id";
public static final String MV_COLUMN_NAME_TYPE = "type";
public static final String MV_COLUMN_NAME_NAME = "name";
private String uuid;
private String type;
private String name;
@Override
public void loadFromMap(Map<String, String> map) {
super.loadFromMap(map);
if (map.containsKey(MV_COLUMN_NAME_UUID)) {
setUuid(map.get(MV_COLUMN_NAME_UUID));
}
if (map.containsKey(MV_COLUMN_NAME_TYPE)) {
setType(map.get(MV_COLUMN_NAME_TYPE));
}
if (map.containsKey(MV_COLUMN_NAME_NAME)) {
setName(map.get(MV_COLUMN_NAME_NAME));
}
}
@Override
public Map<String, String> restoreToMap() {
Map<String,String> map = super.restoreToMap();
if (uuid != null) {
map.put(MV_COLUMN_NAME_UUID, getUuid());
}
if (type != null) {
map.put(MV_COLUMN_NAME_TYPE, getType());
}
if (name != null) {
map.put(MV_COLUMN_NAME_NAME, getName());
}
return map;
}
public String getUuid() {
return uuid;
}
public void setUuid(String uuid) {
this.uuid = uuid;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
File Example.java:
public class Example {
public test() {
// get sequence
Double seq = new Double(DatabaseMetadataDAO.getNextSequenceValue("table_name", "col_name"));
// create value
final ExtendedSecurity security = new ExtendedSecurity();
security.setUuid("some uuid");
security.setName("some name");
security.setType(ExtendedSecurity.TYPE_ROLE);
security.setRealTable(ExtendedSecurity.MV_TABLE_NAME);
Double newValue = new Double(DatabaseMetadataDAO.createValue(DatabaseMetadataUtils.getDatabaseMetadataValueByMap(security.restoreToMap())));
// Execute query
String filter = "$" + ExtendedSecurity.MV_COLUMN_NAME_UUID + "='some uuid'";
DatabaseMetadataDAO.executeValueQuery(DatabaseMetadataUtils.buildQuery(ExtendedSecurity.MV_TABLE_NAME, filter, "")));
// Update value
security.setName("some name changed");
DatabaseMetadataDAO.updateValue(DatabaseMetadataUtils.getDatabaseMetadataValueByMap(security.restoreToMap()));
// Delete value
DatabaseMetadataDAO.deleteValue(DatabaseMetadataUtils.getDatabaseMetadataValueByMap(security.restoreToMap()).getId());
}
}
Advanced
Also joins between tables can be done. The method executeMultiValueQuery should be used for it:
List<String> tables = new ArrayList<String>();
tables.add(GWTCountry.MV_TABLE_NAME);
tables.add(GWTState.MV_TABLE_NAME);
String query = "from DatabaseMetadataValue dmv1, DatabaseMetadataValue dmv2 ";
query += "where dmv1.table='" + GWTCountry.MV_TABLE_NAME + "' ";
query += "and dmv2.table='" + GWTState.MV_TABLE_NAME + "' ";
query += "and dmv1.$" + GWTCountry.MV_COLUMN_COUNTRY_NAME + "='SPAIN' ";
query += "and dmv1.$" + GWTCountry.MV_COLUMN_COUNTRY_NAME + "=" + "dmv2.$" + GWTState.MV_COLUMN_COUNTRY_NAME;
DatabaseMetadataDAO.executeMultiValueQuery(DatabaseMetadataUtils.replaceVirtual(tables, query));