I think this link can be useful: http://mikedesjardins.net/2008/09/22/hibernate-criteria-subqueries-exists/ It contains the following example about how create n exists criteria: "What you’re really trying to do is to obtain all Pizza Orders where an associated small pizza exists. In other words, the SQL query that you’re trying to emulate is SELECT...
CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<Object[]> cq = cb.createQuery(Object[].class); Root<Org> orgRoot = cq.from(Org.class); Join<Org, Dept> ord = orgRoot.join("dept"); cq.multiselect(orgRoot.get("orgname"), ord.get("deptname"), cb.sum(ord.<Integer> get("deptamt"))).groupBy( orgRoot.get("orgname")); Query qry = em.createQuery(cq); List<Object[]> orgList = qry.getResultList(); ...
sql,ms-access,access-vba,criteria
I don't have MSAccess to hand but surely the modulo operation would give you what you need? In your SQL query use (angle MOD 360). For example, (-45 MOD 360) = 315, (405 MOD 360) = 45. Edit, adding sample condition expressions Assuming an arc of 90 degrees either side...
excel,range,criteria,formula,countif
You could use SUMPRODUCT instead, where you can use ranges: =SUMPRODUCT(--(A:A<B:B-120)) If A:A is smaller than B:B after removing 120 days, then (A:A<B:B-120) will return true for each row where this applies, and false otherwise. -- at the start will convert those to 1s and 0s respectively and SUMPRODUCT add...
javascript,html,html5,criteria
Why not simply put the code within an if statement? //create current Date var curDate = new Date(); //This date represents the upper limit of the interval var upperDate = new Date('June 20, 2014 05:00 PM'); //This date represents the lower limit of the interval var lowerDate = new Date('June...
Simply use criteria.add(Restrictions.eq()) to your criteria and I think if the id is unique you need to use .uniqueResult() to get the wanted result from your criteria, your code should be like this: CriteriaBuilder cb = this.entityManager.getCriteriaBuilder(); Criteria cr = cb.createCriteria(Origem.class); // add the restriction here cr.add(Restrictions.eq("id", loginBean.origem.getId)); Origem root...
r,list,criteria,subset,indices
Your question isn't totally clear. It sounds like you're looking for the following: > df$ID %in% df$ID[which(df$ACTION == "Work")] [1] FALSE FALSE FALSE TRUE TRUE TRUE TRUE TRUE TRUE TRUE [11] TRUE FALSE FALSE FALSE FALSE FALSE Step by step: ## Which rows have "Work" in the "ACTION" column? >...
Given your criteria, the first two points of your sorting algorithm degenerate into one much simpler algorithm. That is: No matter which column is full of 0's, you are always sorting by first column, then by second column, then by third column ascending values. This will leave your target item...
grails,criteria,grails-domain-class
You have to rewrite the criteria to look like this: Test.createCriteria().list{ createAlias('longList', 'l') 'in' ('l.elements', [1L]) } This boils down to the property name under which Hibernate stores collection (elements)....
You shouldn't pass params into the service, the HTTP request params should only be used in the web layer. Try this instead Controller class PostController { def postService; def index() {} def insert () { render 'it works' def post = postService.insertPost(params.post, session.user) } def showPost () { def user...
It looks like for every row, you could like to calculate the average number of sent items for the REP in the row. That actually can be better accomplished with the ave() function. Here's a sample data set data <- data.frame( Was.Sent=c("Sent","No")[c(1,1,2,1,2,1,2,1)], REP=c(1,2,3,1,2,3,1,2) ) And here's the code that will...
mongodb,nosql,criteria,morphia
You need to use the Field class' equal method instead of the equals method of the Object class. query.field("tokenHash").equal("RotU72A4E9Oh8qC+cCX4Y+MdIShuuaDX03jQQXZVG34="); equals is a method in the Object class used for comparing the value in two Objects. It returns just true or false. Hence the below statement query.field("tokenHash").equals("RotU72A4E9Oh8qC+cCX4Y+MdIShuuaDX03jQQXZVG34="); Would return false, without...
Try: WHERE [Holiday Name] in ('Thanksgiving', 'Christmas', 'New Years'); ...
java,jpa,design,repository,criteria
Sounds like you may be looking for the Specification pattern which would allow you write a method like: public Order findOrderBySpecification(Specification specification) { } and then call this using a combination of one or more specifications e.g. by account number, by account number and name etc. There is an example...
java,spring,hibernate,criteria,hibernate-criteria
I've experienced this kind of requirement. I tried to get nested objects as nested objects using Transformers.aliasToBean, which will not work. By default, Transformers.aliasToBean don't have the capability to select nested object as nested object. You can take a look at my question Using Projecions to fetch a particular column...
Try the following: CriteriaBuilder cb = entityManager.getCriteriaBuilder(); CriteriaQuery<Product> query = cb.createQuery(Product.class); // "FROM Product p" Root<Product> product = query.from(Product.class); // "RIGHT JOIN store_has_product shp", "ON p.id_product=shp.id_product" is handled inside Product.class Join<Product,StoreHasProduct> storeHasProduct = product.join(Product_.storeHasProductCollection); // Create an alias to the shp.id_store column to use in the where condition Path<Integer> id...
java,hibernate,criteria,hibernate-criteria
I write in the following the solution I found for this problem. First, I have to retrieve a view of my State table containing the rows of the most recent states grouped by team, then I have to add restrictions for the type. The trick here is to generate a...
java,spring,hibernate,criteria
for(Skill skill:job.getSkills()){ DetachedCriteria subquery = DetachedCriteria.forClass(Skill.class,"skill"); subquery.add(Restrictions.eq("id",skill.getId())); subquery.setProjection(Projections.property("id")); subquery.createAlias("jobs", "job"); subquery.add(Restrictions.eqProperty("job.id", "Job.id")); criteria.add(Subqueries.exists(subquery)); } I managed to solve it.now it works....
I think while using Projections.rowCount() hibernate ignores criteria.setFetchMode(field, FetchMode.JOIN); You can use createAlias criteria.createAlias("field","field", JoinType.LEFT_OUTER_JOIN); Check query generated by hibernate in both the cases by using hibernate.show_sql in Hibernate configuration file https://docs.jboss.org/hibernate/orm/3.3/reference/en/html/session-configuration.html#configuration-optional...
jpa,inheritance,entity,criteria
Okay I updated EasyCriteria 3.0.0 to UaiCriteria 4.0.0 (name changed for legal reasons) and that fixed the problem. Remember to make sure that you don't have the old dependency remaining in Maven dependencies even after removing it from pom.xml and adding the new dependency there. I'm using Eclipse m2 plugin....
java,sql,sql-server,hibernate,criteria
Try this: How to query on a month for a date with Hibernate criteria, was useful for me! Maybe you need http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html to understand how to use the equivalents of MSSQL DATEPART in MySQL...
Try this one. CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<Shop > cq = cb.createQuery(Shop.class); Root<Shop > root = cq.from(Shop.class); cq.select(root.get("id"), cb.avg(root.get("ratings"))); cq.where( cb.le(cb.avg(root.get("ratings"), 5); cq.groupBy(c.get("id")); ...
java,hibernate,criteria,projection
You can do that with sqlProjection .setProjection(Projections.sqlProjection("sum(A + B) as sumAB", new String[] {"sumAB"} , new Type[] {Hibernate.DOUBLE})); ...
You forgot to include the class '\Propel\Runtime\Query\Criteria'
@Override public Account getAccountByNickName(String nickname, String accountsCollection) { Criteria criteria = Criteria.where("personalSettings.nickName").is(nickname); DBCollection coll = mongoOperation.getCollection(accountsCollection); DBCursor dbCursor = coll.find(Query.query(criteria).getQueryObject()); DBObject dbobj = dbCursor.next(); Account account = (new Gson()).fromJson(dbobj.toString(), Account.class); return account; } For now - only one way :) Because MongoDB doesn't support searching in map by elemMatch and...
this works for me: def criteriaUsers = UserRole.createCriteria() def users = criteriaUsers.listDistinct{ eq("role", role_admin) }.user Side note: from the nomenclature of your classes it looks like you are using spring-security-core plugin. In my humble opinion the hasMany = [roles: UserRole] is redundant as the association User - Role is already...
hibernate,grails,join,criteria
The problem is related about how Grails is interpreting all the mappings that uses the joinTable. We have deleted all the joinTables references inside User and Competition domain classes and linking them through UserCompetition instead of each other and the queries started to work. class User { (...) static hasMany...
grails,hql,criteria,createcriteria
In this formula property can help you because formula can participate in queries and it is transient by default. Steps- Create a formula property and concatenate your strings there. use this formula property in your criteria query. Use this post for writing formula property. Hope this help...
java,jpa,criteria,openjpa,criteria-api
An example approach may look like this: CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<Object[]> qDef = cb.createQuery(Object[].class); Root<EmployeeEntity> e = qDef.from(EmployeeEntity.class); qDef.multiselect( e.get(EmployeeEntity_.employeeId), e.get(EmployeeEntity_.employeeContactInfo).get(ResourceNodeEntity_.phone)); qDef.where( cb.equal( e.get(EmployeeEntity_.employeeContactInfo).get(ResourceNodeEntity_.zipCode), cb.literal("123456"))); List<Object[]> objects =...
I've fixed the problem. I removed the line: Row rowSelected = vo.next(); And I've added: Row rowSelected = vo.first();...
java,hibernate,orm,hql,criteria
The EAGER fetch associations are applied for: retrieval via get() or load() retrieval that happens implicitly when an association is navigated Criteria queries HQL queries if subselect fetching is used I assume you have one EAGER @OneToMany collection in your Preference entity. The HQL overides the default fetch plan therefore...
sql,hibernate,spring-mvc,criteria,hibernate-criteria
i have worked out using criteriaquery to fullfill my needs, here is the code. it works @Transactional public List<Item> listItems(String alpahbet, String number, String shelf) { CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder(); CriteriaQuery<Item> criteriaQuery = criteriaBuilder.createQuery(Item.class); Root<Item> itemRoot = criteriaQuery.from(Item.class); criteriaQuery.select(itemRoot).where(criteriaBuilder.equal(itemRoot.get("alpahbetField"), alpahbet), criteriaBuilder.equal(itemRoot.get("numberField"),...
java,jpa,criteria,criteria-api
No. It is not possible. According to the JPA (2) spec, a CriteriaQuery must have at least one root. EDIT: I was hoping to find a definitive reference in §6.5.2 (Query Roots) of the JPA 2 spec, but it is inconclusive on that point. However I recall a question on...
java,hibernate,jpa-2.0,criteria
To fetch data from association, you use left join fetch clauses: select distinct b from Bill b left join fetch b.period left join fetch b.entry where b... or select distinct e from Entry e left join fetch e.bill b left join fetch b.period where e... Regarding Criteria, its fetch() method...
No it isn't (always) safe to re-use DetachedCriteria. For example: get a list and a rowcount, reusing the DetachedCriteria: DetachedCriteria dc = DetachedCriteria.forClass(A.class); Criteria c1 = dc.getExecutableCriteria(session); c1.setProjection(Projections.rowCount()); long count = ((Number) c1.uniqueResult()).longValue(); System.out.println(count + " result(s) found:"); Criteria c2 = dc.getExecutableCriteria(session); System.out.println(c2.list()); This prints: Hibernate: select count(*) as y0_...
try this: public function actionPayment() { $criteria = new CDbCriteria(); $criteria->condition = "account_type = :account_type"; $criteria->params=(array(':account_type'=>'credit')); $dataProvider=new CActiveDataProvider('Payments',array( 'criteria'=>$criteria, )); $this->render('index',array( 'dataProvider'=>$dataProvider, )); } ...
Due to https://hibernate.atlassian.net/browse/HHH-869 there is no way to query a collection of value types with Hibernate (which GORM uses) You must use HQL instead....
hibernate,jpa,criteria,embeddable
You need to chain the get. Predicate predicate2 = cb.equal(ul.get("userLike").get("user").get("idUser"), userId); ...
Firstly, there is a ';' in the first column name. That might be the reason it is not getting results. Secondly, all projections should be added to the same projection list like below; public List<abc> Extractingioapublishfor() { Criteria criteria = session.createCriteria(abc.class); criteria.setProjection( Projections.projectionList().add(Projections.property("rId")) .add(Projections.property("tId")).add(Projections.property("ld"))); return criteria.list(); } ...
java,groovy,solr,spring-boot,criteria
One possible solution is to make the field in solr to be multi-valued.
nhibernate,criteria,criteria-api,queryover,nhibernate-queryover
We cannot override mapping in this case. We can do it opposite way - have lazy in place - and use eager fetching for querying. The decision process (of reference loading) is done outside of the query, ex post. So it could be pre-loaded, but cannot be avoided. Solution here...
php,arrays,multidimensional-array,criteria
You can build an more complex sort function where you check the columns in priority. usort($teams, function($a, $b) { if ($a["Points"] < $b["Points"]) return -1; if ($a["Points"] > $b["Points"]) return 1; if ($a['GD'] < $b['GD']) return -1; if ($a['GD'] > $b['GD']) return 1; return 0; }); ...
java,hibernate,criteria,hibernate-criteria
In the documentation provided type of the property is Long. In the example provided by you Byte is used. I assume there can be problems with this. Try to change it to Long.
nhibernate,lazy-loading,criteria
Well, the way how to fix this scenario, would be to change the: IEnumerable<Pet> GetByToy(Toy toy). This is new implementation: public IEnumerable<Pet> GetByToy(Toy toy) { // I. we have to firstly create a subquery var petsWithToySubquery = DetachedCriteria.For<Pet>("pet") // join toys .CreateAlias("toys", "toys", NHibernate.SqlCommand.JoinType.LeftOuterJoin) // add restriction .Add(Restrictions.Eq("toys.Id", toy.Id)) //...
mysql,jpa,eclipselink,criteria,jpa-2.1
This is due to EclipseLink bug https://bugs.eclipse.org/bugs/show_bug.cgi?id=340089 which causes EclipseLink to be unable to distinguish the value returned from the SQL query from the construct it uses internally to indicate there were no results. Selecting another value is the only workaround, but it seems simple enough that not many people...
Instead of calling the property translatedData.property on your entity you can simply call your method directly in twig: {{ mainEntity.getTranslatedData('your_language') }} It will allow you to pass a parameter....
Criteria does not work like EL or Java methods or attributes, you cannot refer to inner objects with a dot .. You have to create a restriction in Ticket, right? What does Ticket has? An User. Then... you have to create a new User, set the username to this User...
java,spring,jpa,expression,criteria
You should use CriteriaBuilder#like() instead of CriteriaBuilder#equal(), to perform like matching. Predicate likesTags = criteriaBuilder.like(fromTagsSynonyms.get(TagSynonym_.title), "%"+piece+"%"); For the rest your code is basically correct....
An example criteria based query may look like this: CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<C> cq = cb.createQuery(C.class); Root<C> c = cq.from(C.class); Join<C, B> b = c.join("b"); cq.select(c) cq.where(b.get("a").get("x").in(cb.parameter(List.class, "param"))); List<C> = em.createQuery(cq) .setParameter("param", Arrays.asList("foo", "bar", "baz")) .getResultList(); ...
mockito,criteria,private-methods
With Mockito, you cannot mock private method calls. Try PowerMockito with which you can mock any kinds of methods like static methods, private methods, local method instantiations and so on.
hibernate,many-to-many,criteria,hibernate-mapping,hibernate-criteria
I found a solution on my own: @Override public List<Article> getByTags(Collection<Long> tagIds) { return (List<Article>)createCriteria() .createAlias("tags", "t") .add(Restrictions.in("t.id", tagIds)) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) .list(); } .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY) was missing....
If you add Criterions to your criteria, they are ANDed in SQL. To be able to handle the situation you can add an if clause: if (device.getSerialNumber() != null && device.getSerialNumber().trim().length > 0 && device.getIpAddress() != null && device.getIpAddress().trim().length > 0 ) { criteria.add(Restrictions.or( Restrictions.like("serialNumber", device.getSerialNumber()), Restrictions.like("ipAdress", device.getIpAdress())); } else...
vba,match,condition,criteria,vlookup
This should work (I decided not to use worksheetfunction.vlookup): Sub MyVlookup2() Dim myrange As Range Dim i As Long, j As Long Dim lastrow As Long Dim lastrow2 As Long Dim diff As Double Const tolerance As Long = 100 Set myrange = Range("D:G") lastrow = Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row lastrow2...
You might consider using a formula for the property. You can fill this with any SQL query data. Consider for example an order - order line relation where you want to fill a property of the order with the number of order lines, like: public class Order { private String...
hibernate,criteria,nhibernate-criteria
Use ProjectionList . It will return Object array containing fields quantity , title , id . Criteria c = getSession().createCriteria(OrderItem.class, "oi"); c.createAlias("oi.book", "book"); c.createAlias("oi.orders", "or"); c.createAlias("or.user", "usr"); ProjectionList proList = Projections.projectionList(); proList.add(Projections.property("oi.quantity")); proList.add(Projections.property("book.title")); proList.add(Projections.property("or.id")); c.setProjection(proList); c.add(Restrictions.eq("usr.id", "1")); return c.list(); ...
jpa,criteria,java-ee-7,metamodel
You should not use CriteriaUpdate for a simple update by id when you have the whole record. Simply use getEntiyManager().merge(t); ...
I'd do a select from Emp_Reporting table :) SELECT EMPP.empid, [ommited for formatting ...] FROM Emp_Reporting EMPR inner join EMPR.details EMPP inner join EMPP.jobDetails EMPJ WHERE EMPP.firstName LIKE :name OR EMPP.id= :id ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.property("EMPP.id")); projectionList.add(Projections.property("EMPP.firstName")); projectionList.add(Projections.property("...")); Criteria criteria = createCriteria(Emp_Reporting.class,"EMPR") .createAlias("details","EMPP")...
You Hibernate query is translated to SQL. If the underlying column is a varchar column I'm not sure that Hibernate can do anything about it. As the issue is in the database tier then one suggestion would be to create a virtual column in the database (if your database supports...
symfony2,collections,doctrine,many-to-many,criteria
Criteria with a manytomany relationship wasn't supported in doctrine a while ago, but I see this pull request: https://github.com/doctrine/doctrine2/pull/885/commits So it is presumably supported in more recent versions of doctrine....
Assuming you actually mapped your collection correctly, the main part you seem to be missing is the Join: CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<Dates> query = cb.createQuery(Dates.class); Root<Dates> root = query.from(Dates.class); Join<Dates, DateInfo> infos = root.join("dates", JoinType.LEFT); query.distinct(true); em.createQuery(query.where(cb.between(infos.<Integer>get("id"), 1, 10))).getResultList(); Of course you can substitute metamodel fields where I used...
java,jpa,criteria,criteria-api
Assuming that MyEntity refers to the table you want to drop/truncate you can proceed as follows: // Criteria API (JPA 2.1 and above) CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaDelete<MyEntity> query = builder.createCriteriaDelete(MyEntity.class); query.from(MyEntity.class); em.createQuery(query).executeUpdate(); or with a generalized approach: public <T> int deleteAllEntities(Class<T> entityType) { CriteriaBuilder builder = em.getCriteriaBuilder(); CriteriaDelete<T> query...
hibernate,criteria,hibernate-criteria
Your code looks like you have some confusion between SQL and ORM approaches. With Criteria API you don't want to specify foreign keys, but entity properties. Given a Student class like this public class Student { private Long id; private Set<Survey> surveys; private Set<Test> tests; ... accessor methods etc }...
java,unit-testing,nullpointerexception,mockito,criteria
You have to mock add method of Criteria. Here is what happens here. In your first version you have mocked instance of criteria. When you invoke method add() it does nothing and returns null but it does not bother you because you do not use the return value. However you...
php,mysql,activerecord,yii,criteria
So, this was the answer... I had to make the $zonas array which I was already doing as a sql statement that would complete the query, like this... $zonas = 'SELECT z.parroquias_id FROM m_zona_mercadeo z, m_tipo_zona_mercadeo t, m_rrhh r WHERE r.usuario_id = '.$user->id.' AND r.tipo_zona_id = t.id AND t.id =...
java,hibernate,criteria,many-to-one
Try adding alias : criteria.createAlias("documentMovement.docMaster", "docMaster") And later call criteria.add(Restrictions.ne("docMaster.status",FMSConstants.CLOSED_STATUS)); ...
c#,nhibernate,hql,criteria,restrictions
What we would need is Sub-SELECT. This could be achieved with subquery. 15.8. Detached queries and subqueries We can define subquery with DetachedCriteria: var subquery = DetachedCriteria.For<OrderItem>() .Add(Restrictions.Eq("FinalDeliveryIndicator", true)) .SetProjection(Projections.Property("OrderId")); This would later end up as this SQL snippet: (SELECT OrderId FROM OrderItems WHERE FinalDeliveryIndicator = 1 ) And this...
java,hibernate,java-ee,criteria
you almost had it correct. Take out the else in your else if public List<IDevice> findByObject(Device device) { Criteria criteria = getSessionFactory().getCurrentSession().createCriteria(Device.class); if (device.getIpAdress() != null && device.getIpAdress().trim().length() > 0 ) { criteria.add(Restrictions.like("ipAdress", device.getIpAdress())); } if (device.getSerialNumber() != null && device.getSerialNumber().trim().length() > 0 ) { criteria.add(Restrictions.like("serialNumber", device.getSerialNumber())); } if...
hibernate,caching,jpa,eclipselink,criteria
The DML operations are directly executed against the database, therefore bypassing the first level cache dirty checking emchanism. Neither Interceptors nor Event Listeners nor JPA PostUpdate hooks are going to be triggered, since updates are not generated by the Hibernate action queue mechanism. The AUTO flush is only triggered for...
So, yeah, I went with using HQL in the end. Nothing special. List<B> bList = session.createQuery( "select b from A as a join a.children as b where a.name like 'Test%'" ).list(); ...
java,sql,hibernate,group-by,criteria
I think here is the correct version of your code: Session session = getCurrentSession(); ProjectionList projectionList = Projections.projectionList(); projectionList.add(Projections.groupProperty("totalCode")) .add(Projections.groupProperty("activityCode")) .add(Projections.sum("amount")) .add(Projections.rowCount()); Criteria criteria = session.createCriteria(Payment.class); criteria.setProjection(projectionList); List<Object[]> payments = criteria.list(); for (Object[] payment : payments) { System.out.println("totalCode: " +...
mysql,symfony-1.4,criteria,propel,mysql-real-escape-string
Halleluja, I think I found the solution: Propel::getConnection()->quote($userInput) This does the magic. Quoting and escaping in one step! So the subQuery would be: $subQuery = "(SELECT SUM(id) FROM my_other_table WHERE customer_id = 123) > ".Propel::getConnection()->quote($userInput); Can someone verifiy, that this is the best way to secure the input?...
excel,indexing,match,criteria,inequality
Try this one in B2: =SMALL(IF(C2:F2>A2,$C$1:$F$1),1) This is an array formula, so type the formula then press CTRL+SHIFT+ENTER. Curly brackets will automatically appear at the start and end of the formula. Then drag formula down. If there is no value greater than A2, SMALL returns #NUM!...
java,sql,hibernate,jpa,criteria
To add where clause (condition), I have to use joins as specified below as joinOptions. And to retrieve data I have to fetch those data as fetch Relation. CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder(); CriteriaQuery<ArticleType> criteria = criteriaBuilder.createQuery(ArticleType.class); Root<ArticleType> root = criteria.from(ArticleType.class); criteria.select(root).distinct(true); Join<ArticleType, ArticleTypeVarianteOption> joinOptions = root.join("articleTypeVarianteOptionCollection"); if...