LegacySrv

LegacySrv is a Spring service that provides direct database access methods for executing raw SQL and HQL (Hibernate Query Language) queries. Access it via ContextWrapper.getContext().getBean(LegacySrv.class) or inject it with @Autowired. Package: com.openkm.db.service.

Caution

Direct SQL and HQL execution bypasses the normal OpenKM security and permission checks. Only a single statement per call is supported. These operations should only be performed by users with administrator privileges.

SQL execution

executeSQL (from Reader)

Description:

Method	Return values	Description
executeSQL(Reader rd)	void	Reads and executes an SQL script from the given Reader, one statement per line. Lines starting with -- and blank lines are skipped. Errors per line are logged but do not abort the script. The reader is closed after execution.

executeSQL (query string)

Description:

Method	Return values	Description
executeSQL(String query)	List<List>	Executes a single SQL statement and returns the result as a list of rows, where each row is a list of column values as strings. BLOB columns are returned as the literal string "BLOB". For non-SELECT statements (INSERT, UPDATE, DELETE), returns a single-row result containing the affected row count.

executeSQL (with parameters)

Description:

Method	Return values	Description
executeSQL(String query, Object… params)	List<List>	Executes a parameterized SQL statement using ? placeholders. Supported parameter types: String, Integer, Long, java.sql.Date, java.sql.Time, java.sql.Timestamp, Boolean. Returns results in the same format as the non-parameterized overload.

• Example SQL: SELECT NBS_UUID, NBS_AUTHOR, NBS_NAME, NBS_NOTES FROM OKM_NODE_BASE WHERE NBS_AUTHOR = ? LIMIT 10;

Example:

package com.openkm;

import java.util.List;

import com.openkm.db.service.LegacySrv;
import com.openkm.util.ContextWrapper;

public class Test {

    public static void main(String[] args) {
        try {
            LegacySrv legacySrv = ContextWrapper.getContext().getBean(LegacySrv.class);

            // Without parameters
            List<List<String>> results = legacySrv.executeSQL(
                "SELECT NBS_UUID, NBS_AUTHOR, NBS_NAME FROM OKM_NODE_BASE LIMIT 10;");
            for (List<String> row : results) {
                System.out.println("uuid=" + row.get(0) + " author=" + row.get(1) + " name=" + row.get(2));
            }

            // With parameters
            List<List<String>> filtered = legacySrv.executeSQL(
                "SELECT NBS_UUID, NBS_NAME FROM OKM_NODE_BASE WHERE NBS_AUTHOR = ? LIMIT 5;", "jsmith");
            for (List<String> row : filtered) {
                System.out.println("uuid=" + row.get(0) + " name=" + row.get(1));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

executeSqlAsMap

Description:

Method	Return values	Description
executeSqlAsMap(String query)	List<Map<String, Object>>	Executes a SQL SELECT statement and returns results as a list of maps, where each map key is a column name and the value is a typed Java object. Column types are mapped as follows: BIGINT → Long, INTEGER → Integer, TIMESTAMP → Timestamp, BLOB → "BLOB", all others → String. For non-SELECT statements, returns a single map with key rowCount.
executeSqlAsMap(String query, Object… params)	List<Map<String, Object>>	Parameterized variant of executeSqlAsMap. Accepts the same parameter types as the parameterized executeSQL overload.

Example:

package com.openkm;

import java.util.List;
import java.util.Map;

import com.openkm.db.service.LegacySrv;
import com.openkm.util.ContextWrapper;

public class Test {

    public static void main(String[] args) {
        try {
            LegacySrv legacySrv = ContextWrapper.getContext().getBean(LegacySrv.class);
            List<Map<String, Object>> results = legacySrv.executeSqlAsMap(
                "SELECT NBS_UUID, NBS_AUTHOR, NBS_SIZE FROM OKM_NODE_BASE LIMIT 5;");
            for (Map<String, Object> row : results) {
                System.out.println("uuid=" + row.get("NBS_UUID") + " size=" + row.get("NBS_SIZE"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

HQL execution

executeHQL

Description:

Method	Return values	Description
executeHQL(String hql)	List	Executes a single HQL (Hibernate Query Language) statement. For SELECT and FROM queries, returns the matching entities or projections as a list of objects (each object may be an entity or an Object[] for multi-column projections). For non-SELECT statements (UPDATE, DELETE, INSERT), executes the update and returns a single-element list containing the affected row count as an Integer.

Example:

package com.openkm;

import java.util.List;

import com.openkm.db.service.LegacySrv;
import com.openkm.util.ContextWrapper;

public class Test {

    public static void main(String[] args) {
        try {
            LegacySrv legacySrv = ContextWrapper.getContext().getBean(LegacySrv.class);

            // Multi-column projection — each result is an Object[]
            List<Object> results = legacySrv.executeHQL(
                "SELECT nb.uuid, nb.author FROM NodeBase nb WHERE nb.name = 'okm:root'");
            for (Object obj : results) {
                if (obj instanceof Object[]) {
                    Object[] cols = (Object[]) obj;
                    System.out.println("uuid=" + cols[0] + " author=" + cols[1]);
                } else {
                    System.out.println(obj);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}