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.

Note

This method will retrieve a Hibernate query where each “$column_name” will be replaced by it’s real column name.

Caution

The return will be a list of DatabaseMetadataValue objects.

• These queries can be executed by the method executeHQL(String query) at LegacyDAO class.

Sample

Register database metadata table definition

Tip

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));