Search code examples
javamysqljpainner-joinnativequery

MySQL inner join error : During the execution of the query was detected to be null. Primary keys must not contain null

I want to correlate the specific columns on two tables and get back matching values based on timestamps i.e. events that occurred at the same time but written on two separate tables. I am using MYSQL INNER JOINS as below:

So far, my query looks like this:

   String threatQueryJoin = "SELECT awsevents.ALERT.resourceName, awsevents.RECORD.Record_Id,awsevents.RECORD.resourceName, "
            + "awsevents.ALERT.alert_id FROM awsevents.ALERT inner join awsevents.RECORD on awsevents.ALERT.resourceName "
            + "= awsevents.RECORD.resourceName where (awsevents.ALERT.timeStamp >= '2019-05-10' AND awsevents.RECORD.timeStamp >= '2019-05-10') "
            + "AND (awsevents.ALERT.resourceName='company-checker-log-963f5e78-0dea-46f1-9a48-5a116bdae350' "
            + "AND awsevents.RECORD.resourceName='company-checker-log-963f5e78-0dea-46f1-9a48-5a116bdae350') order by awsevents.RECORD.timeStamp";
    Query q = AWSEventsDAO.em.createNativeQuery(threatQueryJoin);

List ll = q.getResultList();

The response of the query in MySQL workbench looks good:

table inner join resuktset

However, I want to write the results into another table. Since I am not proficient in MySQL, I will be glad to know if there is a better way to handle such queries. Using inner join, I have the following error:

Exception Description: The primary key read from the row [ArrayRecord(
 => company-checker-log-963f5e78-0dea-46f1-9a48-5a116bdae350
 => 45167
 => company-checker-log-963f5e78-0dea-46f1-9a48-5a116bdae350
 => 8108)] during the execution of the query was detected to be null.  Primary keys must not contain null.
Solution

  • I have solved this issue by removing the mapped class i.e. Record.class and using 

    Object[] alert = threatList.get(i);

    as shown in these tutorials , on jpa and native queries. It turns out that the EntityManager returns a List of Object[] which need to be handled afterwards.