new table in 6.3

[Jose Blanco]

I have a new table in 6.3 - individual_stats, and I'm seeing this error:

org.hibernate.hql.internal.ast.QuerySyntaxException: individual_stats is not mapped [SELECT count(*) FROM individual_stats WHERE email='bla...@umich.edu']

How do I map indiviual_stats table?

Thank you!

-Jose

[Tim Donohue]

Hi Jose,

That error from Hibernate usually means that you are trying to run a straight SQL query when it's expecting you to use HQL (Hibernate Query Language) and a Hibernate Entity object.  So, more than likely you need to do one of the following:

• Either, Create a Hibernate Entity class that maps to this Table. See https://docs.jboss.org/hibernate/annotations/3.5/reference/en/html/entity.html This is how DSpace 6.x (and above) already maps to existing tables...you'll see an Entity object corresponding to every table in the codebase.
• Or, you might be able to tell Hibernate this is just a plain old SQL query by using Hibernate's "createNativeQuery()" method instead of using "createQuery()" (as the latter expects a Hibernate Entity).  More info on that is at https://docs.jboss.org/hibernate/core/4.0/hem/en-US/html/query_native.html

Hopefully that helps.

Tim

---

From: dspac...@googlegroups.com <dspac...@googlegroups.com> on behalf of Jose Blanco <bla...@umich.edu>
Sent: Thursday, February 20, 2020 10:42 AM
To: DSpace Technical Support <dspac...@googlegroups.com>
Subject: [dspace-tech] new table in 6.3

 

--
All messages to this mailing list should adhere to the DuraSpace Code of Conduct: https://duraspace.org/about/policies/code-of-conduct/
---
You received this message because you are subscribed to the Google Groups "DSpace Technical Support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dspace-tech...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/dspace-tech/CAK%3DKc-uyfZ8QofBe2Mo9WKyPDSAA8vvOgh4HfsAM%3DJWGPFsNNw%40mail.gmail.com.

[Jose Blanco]

Looking for an example.  I wonder if someone in the community has done this. I think using createNativeQuery() would be preferable.  I have 4 tables that are not part of the dspace code that I need to manipulate.

Thanks!
-Jose

[Tim Donohue]

Hi Jose,

There is one example of createNativeQuery() in the DSpace codebase itself here:

https://github.com/DSpace/DSpace/blob/master/dspace-api/src/main/java/org/dspace/eperson/dao/impl/GroupDAOImpl.java#L170

I'm sure you could find more examples via Google or StackOverflow as well.

Tim

---

From: Jose Blanco <bla...@umich.edu>
Sent: Thursday, February 20, 2020 2:28 PM
To: Tim Donohue <tim.d...@lyrasis.org>
Cc: DSpace Technical Support <dspac...@googlegroups.com>
Subject: Re: [dspace-tech] new table in 6.3

 

[Jose Blanco]

Kind of stuck trying to create a query without having to map it.  This is what I have:

    @Override

    public List<EPerson> findProxiesForDepositor(Context context, UUID depositor_id) throws SQLException {

        Query query = getHibernateSession(context).createSQLQuery("SELECT * FROM EPerson WHERE uuid in (SELECT proxy_id from proxies where depositor_id =:depositor_id)");

        query.addEntity(EPerson.class);

        query.setParameter("depositor_id", depositor_id);

        List<EPerson> epersons = query.list();

        return epersons;

    }

It's telling me that :

[ERROR] EPersonDAOImpl.java:[187,14] cannot find symbol

[ERROR] symbol:   method addEntity(java.lang.Class<org.dspace.eperson.EPerson>)

[ERROR] location: variable query of type org.hibernate.Query

I found something on the web that suggested I could do it this way.

If someone could correct this query, that would really help me because I have others like it.

Thank you!

-Jose

[Jose Blanco]

I just tried:

       Query q = getHibernateSession(context).createSQLQuery("SELECT * FROM EPerson  WHERE uuid in (SELECT proxy_id from proxies where depositor_id = '" + depositor_id.toString() + "')");

       q.setResultTransformer(Transformers.aliasToBean(EPerson.class));

       List<EPerson> results = q.list();

       return results;

And I'm getting:

org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111

in this line:

       List<EPerson> results = q.list();

[Mark H. Wood]

On Tue, Feb 25, 2020 at 02:22:01PM -0500, Jose Blanco wrote:
> I just tried:
>
> Query q = getHibernateSession(context).createSQLQuery("SELECT * FROM
> EPerson WHERE uuid in (SELECT proxy_id from proxies where depositor_id = '"
> + depositor_id.toString() + "')");
>
> q.setResultTransformer(Transformers.aliasToBean(EPerson.class));
>
> List<EPerson> results = q.list();
>
> return results;
>
>
>
> And I'm getting:
>
>
> org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111
>
>
> in this line:
>
> List<EPerson> results = q.list();

I haven't dealt with this myself, but the first thing I would check is
whether Hibernate understands that proxies.proxy_id is of type UUID.

I would guess that you'll continue to fight issues like this so long
as "proxies" is not associated with an ORM entity type. That would
mean writing an entity class 'Proxies' to tell Hibernate all about
your table. You can find examples in
'dspace-api/src/main/java/org/dspace/content'. To fit in with the
patterns of DSpace's object-relational mapping, you probably should
also write:

ProxiesDAO
ProxiesDAOImpl
ProxiesService
ProxiesServiceImpl

You'll also need to add a <mapping/> element to
dspace/config/hibernate.cfg.xml so that Hibernate knows that it should
expect to find your entity class.

Yes, I know: it looks like more frosting than cake. But, if you use
Hibernate, then Hibernate needs a lot of information about what it is
to manipulate for you. And, it will be easier to use other types as
examples if you follow the complete pattern.

--
Mark H. Wood
Lead Technology Analyst

University Library
Indiana University - Purdue University Indianapolis
755 W. Michigan Street
Indianapolis, IN 46202
317-274-0749
www.ulib.iupui.edu

[Jose Blanco]

Mark, I have one question.  Can you point me to and example of how a row is inserted into a table.

Thanks!  Jose

--
All messages to this mailing list should adhere to the DuraSpace Code of Conduct: https://duraspace.org/about/policies/code-of-conduct/
---
You received this message because you are subscribed to the Google Groups "DSpace Technical Support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dspace-tech...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/dspace-tech/20200226141439.GB8836%40IUPUI.Edu.

[Jose Blanco]

I think I see an example:

    @Override

    public ResourcePolicy createResourcePolicy(Context context, DSpaceObject dso, Group group, EPerson eperson, int type, String rpType) throws SQLException, AuthorizeException {

        if(group == null && eperson == null)

        {

            throw new IllegalArgumentException("We need at least an eperson or a group in order to create a resource policy.");

        }

        ResourcePolicy myPolicy = resourcePolicyService.create(context);

        myPolicy.setdSpaceObject(dso);

        myPolicy.setAction(type);

        myPolicy.setGroup(group);

        myPolicy.setEPerson(eperson);

        myPolicy.setRpType(rpType);

        resourcePolicyService.update(context, myPolicy);

        return myPolicy;

    }

[Mark H. Wood]

On Thu, Mar 12, 2020 at 04:45:31PM -0400, Jose Blanco wrote:
> I think I see an example:
>
> @Override
>
> public ResourcePolicy createResourcePolicy(Context context,
> DSpaceObject dso, Group group, EPerson eperson, int type, String rpType)
> throws SQLException, AuthorizeException {
>
> if(group == null && eperson == null)
>
> {
>
> throw new IllegalArgumentException("We need at least an eperson
> or a group in order to create a resource policy.");
>
> }
>
>
> ResourcePolicy myPolicy = resourcePolicyService.create(context);
>
> myPolicy.setdSpaceObject(dso);
>
> myPolicy.setAction(type);
>
> myPolicy.setGroup(group);
>
> myPolicy.setEPerson(eperson);
>
> myPolicy.setRpType(rpType);
>
> resourcePolicyService.update(context, myPolicy);
>
>
> return myPolicy;
>
> }

I think that you are correct. xxxService.create() instantiates the
entity. You set values for its fields. Then xxxService.update()
saves the instance.

This is all just doing things in Hibernate's cache, which keeps some
entity instances handy and keeps track of whether they have been
altered. Behind the scenes, Hibernate's cache manager fills these
instances from database rows and uses mutated values to update
database rows. But often you can think of it as: .create() INSERTs
an empty row, .find() SELECTs a row, and .update UPDATEs a row.