public List<Incidence> getIncidencesByDepartament(Integer departamentId) {
JPAQuery query = new JPAQuery(entityManager);
JPAQuery subquery = new JPAQuery(entityManager);
// SELECT * FROM CPR_INCIDENCES WHERE AREA_ID in (
// SELECT dar.area_id FROM CPR_VW_DEPARTAMENTOS_AREAS dar JOIN CPR_VW_DEPARTAMENTOS dep ON
// dar.DEP_ID=dep.UBICACION_ID WHERE dep.per_id=60224);
QIncidence qIncidence = QIncidence.incidence;
QDepartmentsAreas qDepartmentsAreas = QDepartmentsAreas.departmentsAreas;
QDepartments qDepartments = QDepartments.departments;
query
.from(qIncidence)
.where(
qIncidence.areaId.in(
subquery
.from(qDepartmentsAreas)
.join(qDepartmentsAreas.departments, qDepartments)
.where(qDepartments.locationId.eq(departamentId))
.list(qDepartmentsAreas.areaId)))
.list(qIncidence);
return query.list(qIncidence);
}
public List<Event> getIncidencesByDegree(Long connectedUserId) {
JPAQuery query = new JPAQuery(entityManager);
JPAQuery subquery = new JPAQuery(entityManager);
// SELECT ev.* FROM CPR_VW_EVENTS_HISTORICO ev JOIN CPR_INCIDENCES inc ON
// ev.ID=inc.EVENT_ID WHERE
// ev.CODE in (SELECT ast.ID_ASIGNATURA FROM CPR_EXT_ASI_TITULACION ast
// JOIN CPR_EXT_DIR_TITULACION dt ON dt.ID_TITULACION=ast.ID_TITULACION WHERE
// dt.ID_PERSONA=54341);
QEvent qEvent = QEvent.event;
QIncidence qIncidence = QIncidence.incidence;
QSubDegree qSubDegree = QSubDegree.subDegree;
QDirDegree qDirDegree = QDirDegree.dirDegree;
query
.from(qEvent)
.join(qEvent.incidences, qIncidence)
.where(
qEvent.code.in(
subquery
.from(qSubDegree)
.join(qSubDegree.dirDegree, qDirDegree)
.where(qDirDegree.userId.eq(connectedUserId))
.list(qSubDegree.subjectId)));
return query.list(qEvent);
}
@Override
public TipoMinusvalia getTipoMinusvaliaByOrden(String codigoMinusvalia) {
JPAQuery query = new JPAQuery(entityManager);
QTipoMinusvalia tipoMinusvalia = QTipoMinusvalia.tipoMinusvalia;
query.from(tipoMinusvalia).where(tipoMinusvalia.orden.eq(Long.parseLong(codigoMinusvalia)));
return query.uniqueResult(tipoMinusvalia);
}
@Override
public SessionDto register(String username, String password, String name, String email) {
log.debug("registering new user: "******"Could not register user!", e);
throw new RuntimeException("Could not register user!");
}
}
@Override
public List<DbAnime> findAll() {
QDbAnime dbAnime = QDbAnime.dbAnime;
JPAQuery query = new JPAQuery(em).from(dbAnime).where(dbAnime.deleted.isFalse());
return maxResultCount == null ? query.list(dbAnime) : query.limit(maxResultCount).list(dbAnime);
}
private void predicate(
JPAQuery query,
QSpecial special,
Integer[] siteId,
Integer[] categoryId,
Date beginDate,
Date endDate,
Boolean isWithImage,
Boolean isRecommend) {
query.from(special);
BooleanBuilder exp = new BooleanBuilder();
if (ArrayUtils.isNotEmpty(siteId)) {
exp = exp.and(special.site.id.in(siteId));
}
if (ArrayUtils.isNotEmpty(categoryId)) {
exp = exp.and(special.category.id.in(categoryId));
}
if (beginDate != null) {
exp = exp.and(special.creationDate.goe(beginDate));
}
if (endDate != null) {
exp = exp.and(special.creationDate.goe(endDate));
}
if (isWithImage != null) {
exp = exp.and(special.withImage.eq(isWithImage));
}
if (isRecommend != null) {
exp = exp.and(special.recommend.eq(isRecommend));
}
query.where(exp);
}
/**
* Clone the state of this query to a new JPAQuery instance with the given EntityManager
*
* @param entityManager
* @return
*/
public JPAQuery clone(EntityManager entityManager) {
JPAQuery q =
new JPAQuery(entityManager, JPAProvider.getTemplates(entityManager), getMetadata().clone());
q.projection = projection;
q.flushMode = flushMode;
q.hints.putAll(hints);
q.lockMode = lockMode;
return q;
}
public List<Model> findList(Integer siteId, Integer type) {
JPAQuery query = new JPAQuery(this.em);
QModel model = QModel.model;
query.from(model);
BooleanBuilder exp = new BooleanBuilder();
exp = exp.and(model.site.id.eq(siteId));
if (type != null) {
exp = exp.and(model.type.eq(type));
}
query.where(exp);
query.orderBy(model.seq.asc());
return query.list(model);
}
public Page<Special> findPage(
Integer[] siteId,
Integer[] categoryId,
Date beginDate,
Date endDate,
Boolean isWithImage,
Boolean isRecommend,
Pageable pageable) {
JPAQuery query = new JPAQuery(this.em);
query.setHint(QueryHints.HINT_CACHEABLE, true);
QSpecial special = QSpecial.special;
predicate(query, special, siteId, categoryId, beginDate, endDate, isWithImage, isRecommend);
return QuerydslUtils.page(query, special, pageable);
}
public List<Article> listArticles() {
JPAQuery jpaQuery = new JPAQuery(em);
QArticle qArticle = QArticle.article;
List<Article> articleList =
jpaQuery
.from(qArticle)
.where(
qArticle
.owner
.eq(plAccess.getUser().getLoginName())
.and(qArticle.deleted.eq(false)))
.list(qArticle);
return articleList;
}
@Override
public List<Competencia> getCompetenciasByAsignatura(Long asignaturaId) {
JPAQuery query = new JPAQuery(entityManager);
QCompetencia asiCompetencias = QCompetencia.competencia;
QAsignatura asignaturas = new QAsignatura("aa");
query
.from(asiCompetencias)
.join(asiCompetencias.gdoAsignatura, asignaturas)
.fetch()
.join(asignaturas.gdoExtAsiToda)
.fetch()
.where(asignaturas.id.eq(asignaturaId));
return query.list(asiCompetencias);
}
@Override
public List<VerificaCompetencia> getVerificaCompetenciaByAsignatura(
String codigoAsignatura, Long cursoAca) {
JPAQuery query = new JPAQuery(entityManager);
QVerificaCompetencia verificaCompetencia = QVerificaCompetencia.verificaCompetencia;
query
.from(verificaCompetencia)
.where(
verificaCompetencia
.codAsignatura
.eq(codigoAsignatura)
.and(verificaCompetencia.cursoAcaIni.loe(cursoAca)));
return query.list(verificaCompetencia);
}
public List<BookmarkOrFolder> getAll(Folder parent) {
JPAQuery query = new JPAQuery(getEntityManager());
QBookmark bookmark = QBookmark.bookmark;
QFolder folder = QFolder.folder;
JPAQuery from = query.from(bookmark, folder);
List<Tuple> list = from.list();
// JPAQuery query = new JPAQuery(em);
// List<T> resultList = getEntityManager().createQuery(getAllForFolderQuery()).getResultList();
// List<T> filteredResults = new ArrayList<T>();
// for (T details : resultList) {
// if (filterMatches(details)) {
// filteredResults.add(details);
// }
// }
return null; // filteredResults;
}
@Test
public void test() throws IOException, ClassNotFoundException {
// create query
JPAQuery query = new JPAQuery(entityManager);
query.from(cat).where(cat.name.eq("Kate")).list(cat);
// get metadata
QueryMetadata metadata = query.getMetadata();
assertFalse(metadata.getJoins().isEmpty());
assertTrue(metadata.getWhere() != null);
assertTrue(metadata.getProjection().isEmpty());
// serialize metadata
ByteArrayOutputStream baos = new ByteArrayOutputStream();
ObjectOutputStream out = new ObjectOutputStream(baos);
out.writeObject(metadata);
out.close();
// deserialize metadata
ByteArrayInputStream bain = new ByteArrayInputStream(baos.toByteArray());
ObjectInputStream in = new ObjectInputStream(bain);
QueryMetadata metadata2 = (QueryMetadata) in.readObject();
in.close();
// validate it
assertEquals(metadata.getJoins(), metadata2.getJoins());
assertEquals(metadata.getWhere(), metadata2.getWhere());
assertEquals(metadata.getProjection(), metadata2.getProjection());
// create new query
JPAQuery query2 = new JPAQuery(entityManager, metadata2);
assertEquals("from Cat cat\nwhere cat.name = :a1", query2.toString());
query2.list(cat);
}
@Override
public Competencia getCompetencia(Long competenciaId) {
JPAQuery query = new JPAQuery(entityManager);
QCompetencia asiCompetencias = QCompetencia.competencia;
QAsignatura asignaturas = new QAsignatura("aa");
query
.from(asiCompetencias)
.join(asiCompetencias.gdoAsignatura, asignaturas)
.fetch()
.join(asignaturas.gdoExtAsiToda)
.fetch()
.where(asiCompetencias.id.eq(competenciaId));
List<Competencia> list = query.list(asiCompetencias);
if (list != null && list.size() > 0) {
return list.get(0);
} else {
return new Competencia();
}
}
public List<Incidence> getIncidencesPendingByUser(Long connectedUserId) {
JPAQuery query = new JPAQuery(entityManager);
JPAQuery subquery = new JPAQuery(entityManager);
JPAQuery subquery2 = new JPAQuery(entityManager);
QIncidence qIncidence = QIncidence.incidence;
QDepartmentsAreas qDepartmentsAreas = QDepartmentsAreas.departmentsAreas;
QDepartments qDepartments = QDepartments.departments;
QEvent qEvent = QEvent.event;
QSubDegree qSubDegree = QSubDegree.subDegree;
QDirDegree qDirDegree = QDirDegree.dirDegree;
QCheck qCheck = QCheck.check;
// WHERE uev.PERSONA_ID=64931 AND EVENT_ID not in (
// SELECT IDEVENT FROM (
// SELECT IDEVENT, DIRECTION FROM CPR_TERMINAL_CHECKS WHERE iduser=64931 and
// DIRECTION is not null GROUP BY IDEVENT, DIRECTION
// ) GROUP BY IDEVENT HAVING count(idevent)=2
// ) AND event_id not in(SELECT EVENT_ID FROM CPR_INCIDENCES WHERE TYPE!=3)
// ORDER BY ev.START_EVENT DESC;
return query.list(qIncidence);
}
public Model findDefault(Integer siteId, Integer type) {
JPAQuery query = new JPAQuery(this.em);
QModel model = QModel.model;
query.from(model);
BooleanBuilder exp = new BooleanBuilder();
exp = exp.and(model.site.id.eq(siteId));
exp = exp.and(model.type.eq(type));
query.where(exp);
query.orderBy(model.seq.asc());
query.limit(1);
List<Model> list = query.list(model);
return !list.isEmpty() ? list.get(0) : null;
}
protected List<Identifiable> getObjectsFor(RegistryObject ro) throws XDSException {
List<Identifiable> objects = new ArrayList<Identifiable>();
if (ro != null) {
objects.add(ro);
JPAQuery query = new JPAQuery(getSession().getEntityManager()).from(QAssociation.association);
StoredQueryParam paramConfidentialityCode =
getQueryParam(XDSConstants.QRY_DOCUMENT_ENTRY_CONFIDENTIALITY_CODE);
StoredQueryParam paramFormatCode = getQueryParam(XDSConstants.QRY_DOCUMENT_ENTRY_FORMAT_CODE);
if (paramConfidentialityCode != null || paramFormatCode != null) {
BooleanBuilder docBuilder = new BooleanBuilder();
docBuilder.and(
QXDSDocumentEntry.xDSDocumentEntry.eq(QAssociation.association.targetObject));
addXdsCodeMatch(
docBuilder,
getQueryParam(XDSConstants.QRY_DOCUMENT_ENTRY_CONFIDENTIALITY_CODE),
XDSConstants.UUID_XDSDocumentEntry_confidentialityCode,
QXDSDocumentEntry.xDSDocumentEntry.xdsCodes);
addXdsCodeMatch(
docBuilder,
getQueryParam(XDSConstants.QRY_DOCUMENT_ENTRY_FORMAT_CODE),
XDSConstants.UUID_XDSDocumentEntry_formatCode,
QXDSDocumentEntry.xDSDocumentEntry.xdsCodes);
BooleanBuilder orBuilder = new BooleanBuilder();
orBuilder
.orNot(QAssociation.association.targetObject.instanceOf(XDSDocumentEntry.class))
.or(
new JPASubQuery()
.from(QXDSDocumentEntry.xDSDocumentEntry)
.where(docBuilder)
.exists());
query.where(
QAssociation.association.sourceObject.eq(ro),
QAssociation.association.assocType.id.eq(XDSConstants.HAS_MEMBER),
orBuilder);
} else {
query.where(
QAssociation.association.sourceObject.eq(ro),
QAssociation.association.assocType.id.eq(XDSConstants.HAS_MEMBER));
}
List<Association> associations = query.list(QAssociation.association);
log.debug("#### Found Associations: {}", associations);
objects.addAll(associations);
RegistryObject obj;
List<Association[]> childAssocs = new ArrayList<Association[]>();
for (Association assoc : associations) {
obj = assoc.getTargetObject();
if (obj instanceof Association) {
childAssocs.add(new Association[] {(Association) obj, assoc});
} else {
objects.add(obj);
}
}
for (Association[] assoc : childAssocs) {
if (objects.contains(assoc[0].getTargetObject())
&& // Add only Associations with source AND target object in result!
objects.contains(assoc[0].getSourceObject())) {
objects.add(assoc[0]);
} else {
objects.remove(
assoc[1]); // Remove association because target (Association) is not in result
}
}
}
return objects;
}
@Override
public TipoMinusvalia getTipoMinusvaliaById(Long tipoMinusvaliaId) {
JPAQuery query = new JPAQuery(entityManager);
query.from(tipoMinusvalia).where(tipoMinusvalia.id.eq(tipoMinusvaliaId));
return query.uniqueResult(tipoMinusvalia);
}
@Override
public List<TipoMinusvalia> getTiposMinusvalias() {
JPAQuery query = new JPAQuery(entityManager);
query.from(tipoMinusvalia).orderBy(tipoMinusvalia.orden.asc());
return query.list(tipoMinusvalia);
}
@Override
public List<Book> find(BookSearchCriteria bookSearchCriteria) {
JPAQuery query = new JPAQuery(entityManager);
QBook book = QBook.book;
return query.from(book).where(applySearchCriteriaToQuery(bookSearchCriteria, book)).list(book);
}
public List<DbAnime> findAllWithDeleted() {
QDbAnime dbAnime = QDbAnime.dbAnime;
JPAQuery query = new JPAQuery(em).from(dbAnime);
return maxResultCount == null ? query.list(dbAnime) : query.limit(maxResultCount).list(dbAnime);
}
