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);
}
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);
}
@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 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);
}
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;
}
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 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<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);
}
@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);
}
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;
}
@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();
}
}
@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);
}