hibernate - IN clause with a composite primary key in JPA criteria -
i have table named group_table in mysql 2 columns user_group_id , group_id (both of them of type varchar). both of these columns form composite primary key.
i need execute statement using sub-select in() select rows based on list of values passed query.
@override @suppresswarnings("unchecked") public list<grouptable> getlist(list<grouptable> list) { criteriabuilder criteriabuilder=entitymanager.getcriteriabuilder(); criteriaquery<grouptable> criteriaquery=criteriabuilder.createquery(grouptable.class); root<grouptable> root = criteriaquery.from(entitymanager.getmetamodel().entity(grouptable.class)); criteriaquery.where(root.in(list)); return entitymanager.createquery(criteriaquery).getresultlist(); } the implementation produces following query.
select group_id, user_group_id projectdb.group_table ((?, ?) in ((?, ?), (?, ?))) /*binding parameters.*/ bind => [null, null, role_aaa, aaa, role_bbb, aaa] please notice first 2 parameters composite key null. should user_group_id , group_id respectively.
why not substituted in parameter list?
while i'm not interested in forming composite primary key in table, (likely) mandatory jaas i'm using authentication.
in scenario, query returns same list supplied database needless in reality. need query deletion of multiple rows.
this missing feature in eclipse link. have devlopped patch
/** ***************************************************************************** * copyright (c) 1998, 2013 oracle and/or affiliates. rights reserved. * program , accompanying materials made available under * terms of eclipse public license v1.0 , eclipse distribution license v. 1.0 * accompanies distribution. * eclipse public license available @ http://www.eclipse.org/legal/epl-v10.html * , eclipse distribution license available @ * http://www.eclipse.org/org/documents/edl-v10.php. * * contributors: * oracle - initial api , implementation oracle toplink * nicolas marcotte <nicolas.marcotte@usherbrooke.ca> - patch in on composite keys comming expression builder ***************************************************************************** */ package org.eclipse.persistence.internal.expressions; import java.io.*; import java.util.*; import org.apache.logging.log4j.logmanager; import org.apache.logging.log4j.logger; import org.eclipse.persistence.internal.helper.*; import org.eclipse.persistence.internal.sessions.abstractsession; import org.eclipse.persistence.queries.*; import org.eclipse.persistence.exceptions.*; import org.eclipse.persistence.expressions.*; import org.eclipse.persistence.internal.databaseaccess.*; import org.eclipse.persistence.internal.sessions.abstractrecord; /** * <p> * <b>purpose</b>: expression sql printer. * <p> * <b>responsibilities</b>:<ul> * <li> print expression in sql format. * <li> replaces field types field names descriptor. * <li> replaces parameter types row or object values. * <li> calls accessor print primitive types. * </ul> * <p> * @author dorin sandu * @since toplink/java 1.0 */ public class expressionsqlprinter { /** * stores current session. session accessor * used print primitive types. */ protected abstractsession session; /** * stores current platform access platform specific functions. */ protected databaseplatform platform; /** * stores call being created. */ protected sqlcall call; /** * stores row. used print parameter nodes. */ protected abstractrecord translationrow; /** * indicates whether qualified field names * (owner + table) should used or not. */ protected boolean shouldprintqualifiednames; // write on protected writer writer; /** used distincts in functions. */ protected boolean requiresdistinct; // used in figuring out when print comma in select line protected boolean isfirstelementprinted; private final expressionbuilder builder; public expressionsqlprinter(abstractsession session, abstractrecord translationrow, sqlcall call, boolean printqualifiednames, expressionbuilder builder) { this.session = session; this.translationrow = translationrow; this.call = call; this.shouldprintqualifiednames = printqualifiednames; // reference session's platform directly if builder or builder's descriptor null if (builder == null || builder.getdescriptor() == null) { this.platform = getsession().getplatform(); } else { this.platform = (databaseplatform) getsession().getplatform(builder.getdescriptor().getjavaclass()); } this.requiresdistinct = false; this.builder = builder; isfirstelementprinted = false; } /** * return call. */ public sqlcall getcall() { return call; } /** * internal: * return database platform specific information. */ public databaseplatform getplatform() { return this.platform; } protected abstractsession getsession() { return session; } /** * internal: * return row translation */ protected abstractrecord gettranslationrow() { return translationrow; } public writer getwriter() { return writer; } /** * internal: * used in figuring out when print comma in select clause */ public boolean isfirstelementprinted() { return isfirstelementprinted; } public void printexpression(expression expression) { translateexpression(expression); } public void printfield(databasefield field) { if (field == null) { return; } //start of patch 1 //resolve alias if not done if (builder.gettablealiases() != null) { databasetable keyatvalue = builder.gettablealiases().keyatvalue(field.gettable()); if (keyatvalue != null) { field.settablename(keyatvalue.getname()); } } //end of patch 1 try { // print field using either short or long notation i.e. owner + table name. if (shouldprintqualifiednames()) { getwriter().write(field.getqualifiednamedelimited(platform)); } else { getwriter().write(field.getnamedelimited(platform)); } } catch (ioexception exception) { throw validationexception.fileerror(exception); } } public void printparameter(parameterexpression expression) { try { final logger logger = logmanager.getlogger(); getcall().appendtranslationparameter(getwriter(), expression, getplatform(), gettranslationrow()); } catch (ioexception exception) { throw validationexception.fileerror(exception); } } public void printparameter(databasefield field) { getcall().appendtranslation(getwriter(), field); } public void printprimitive(object value) { if (value instanceof collection) { printvaluelist((collection) value); return; } session.getplatform().appendliteraltocall(getcall(), getwriter(), value); } public void printnull(constantexpression nullvalueexpression) { if (session.getplatform().shouldbindliterals()) { databasefield field = null; expression localbase = nullvalueexpression.getlocalbase(); if (localbase.isfieldexpression()) { field = ((fieldexpression) localbase).getfield(); } else if (localbase.isquerykeyexpression()) { field = ((querykeyexpression) localbase).getfield(); } session.getplatform().appendliteraltocall(getcall(), getwriter(), field); } else { session.getplatform().appendliteraltocall(getcall(), getwriter(), null); } } public void printstring(string value) { try { getwriter().write(value); } catch (ioexception exception) { throw validationexception.fileerror(exception); } } public void printvaluelist(collection values) { try { getwriter().write("("); iterator valuesenum = values.iterator(); while (valuesenum.hasnext()) { object value = valuesenum.next(); // support nested arrays in. if (value instanceof collection) { printvaluelist((collection) value); } else if (value instanceof expression) { ((expression) value).printsql(this); //start of patch 2 } else if (value instanceof databasefield) { printexpression(builder.getfield((databasefield) value)); //end of patch 2 } else { session.getplatform().appendliteraltocall(getcall(), getwriter(), value); } if (valuesenum.hasnext()) { getwriter().write(", "); } } getwriter().write(")"); } catch (ioexception exception) { throw validationexception.fileerror(exception); } } /* * same printvaluelist, allows collections containing expressions recursively */ public void printlist(collection values) { try { getwriter().write("("); iterator valuesenum = values.iterator(); while (valuesenum.hasnext()) { object value = valuesenum.next(); if (value instanceof expression) { ((expression) value).printsql(this); } else { session.getplatform().appendliteraltocall(getcall(), getwriter(), value); } if (valuesenum.hasnext()) { getwriter().write(", "); } } getwriter().write(")"); } catch (ioexception exception) { throw validationexception.fileerror(exception); } } /** * if distinct has been set distinct clause printed. * required batch reading. */ public boolean requiresdistinct() { return requiresdistinct; } protected void setcall(sqlcall call) { this.call = call; } /** * internal: * used in figuring out when print comma in select clause */ public void setisfirstelementprinted(boolean isfirstelementprinted) { this.isfirstelementprinted = isfirstelementprinted; } /** * if distinct has been set distinct clause printed. * required batch reading. */ public void setrequiresdistinct(boolean requiresdistinct) { this.requiresdistinct = requiresdistinct; } protected void setsession(abstractsession thesession) { session = thesession; } protected void setshouldprintqualifiednames(boolean shouldprintqualifiednames) { this.shouldprintqualifiednames = shouldprintqualifiednames; } /** * internal: * set row translation */ protected void settranslationrow(abstractrecord therow) { translationrow = therow; } public void setwriter(writer writer) { this.writer = writer; } public boolean shouldprintparametervalues() { return gettranslationrow() != null; } protected boolean shouldprintqualifiednames() { return shouldprintqualifiednames; } /** * translate expression i.e. call appropriate * translation method expression based on * type. translation method responsible * translating subexpressions. */ protected void translateexpression(expression theexpression) { theexpression.printsql(this); } } the patch delimited //start of patch n , //end of patch n try sumbmit upstream might takes times
Comments
Post a Comment