Getting DB Connection from within a dspace process script

[Brian Keese]

Hello!

I am writing a new "process" script (accessible the same as filter-media, for example, by running `dspace filter-media`). This process will generate a report of numbers of types of bitstreams in the system. I have written other such scripts that use DAOs to access the data, but this one would be much simpler to run a native sql query against the database. 

I have been digging through the codebase all day, trying various things, but I can't seem to get a connection to the database from that context. I feel like I've done this before, but I cannot find the code.

Any help?

Thanks,

Brian Keese

[Michael Koch]

In my installation (8.1) the following code works to access the database directly in a process:

            import org.dspace.utils.DSpace;

            import org.dspace.core.DBConnection;

            import org.hibernate.Session;

            DBConnection dbConnection = new DSpace().getServiceManager()

                                       .getServiceByName(null, DBConnection.class);

            session = (Session) dbConnection.getSession();

            session.createNativeQuery("DROP TABLE IF EXISTS citations_item_source").executeUpdate();

[Brian Keese]

This is perfect. Thank you!

-Brian

[Brian Keese]

For anyone who might stumble across this, this is what I did to do a select query:

import java.lang.StringBuilder;

import javax.persistence.Query;

import javax.persistence.Tuple;

import org.dspace.core.DBConnection;

import org.dspace.utils.DSpace;
import org.hibernate.Session;

DBConnection dbConnection = new DSpace().getServiceManager().getServiceByName(null, DBConnection.class);

Session session = (Session) dbConnection.getSession();
Query qry = session.createNativeQuery("select count(uuid) as count, short_description from ... where ... group by...", Tuple.class);

List<Tuple> results = qry.getResultList();

StringBuilder stringBuilder = new StringBuilder();

stringBuilder.append("count,short_description" + System.lineSeparator());
for (Tuple res : results) {
   stringBuilder.append(res.get("count") + "," + res.get("short_description") + System.lineSeparator());
}