1. Home
  2. OpenKM 6.3 - CE
  3. Development
  4. Creating your own database tables
  5. Use your own database table from Java
  1.  

Use your own database table from Java

Methods

The class DatabaseMetadataUtils has a set of methods to select, update and delete queries:

MethodreturnDescription
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.

List<Object> results = LegacyDAO.executeHQL(query);

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