Results 1 to 1 of 1
  1. #1
    kattoha is offline Member
    Join Date
    Apr 2014
    Posts
    13
    Rep Power
    0

    Default Convert SQL to HQL

    Hello? Community. Please, can any help me? Is it real to convert this query
    Java Code:
    SELECT *, IFNULL(sum(positive) - (count(*) - sum(positive)), 0) rating
    FROM photo_votes pv
    RIGHT OUTER JOIN photos p ON pv.photo_id = p.id WHERE p.published=1
    GROUP BY id ORDER BY rating DESC;
    to HQL.
    The goal of this operation to make possible join fetch eager all photo votes to photo instance.
    Thank you.
    =======Tables:======

    Java Code:
    CREATE TABLE `photo_votes` (
      `positive` bit(1) DEFAULT NULL,
      `user_id` int(11) NOT NULL,
      `photo_id` bigint(20) NOT NULL,
      PRIMARY KEY (`photo_id`,`user_id`),
      KEY `FK864l2ibsesxwtwk5gq06b9uny` (`user_id`),
      CONSTRAINT `FK864l2ibsesxwtwk5gq06b9uny` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
      CONSTRAINT `FK9hjxlo6pa9mxntkkveh55o7fa` FOREIGN KEY (`photo_id`) REFERENCES `photos` (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    CREATE TABLE `photos` (
      `id` bigint(20) NOT NULL,
      `creation_date` datetime DEFAULT NULL,
      `event` bit(1) NOT NULL DEFAULT b'0',
      `link` varchar(255) NOT NULL,
      `published` bit(1) NOT NULL DEFAULT b'0',
      `title` varchar(255) DEFAULT NULL,
      `owner_id` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `UK_3y24du587kor03s3sw07rcp72` (`link`),
      KEY `FKfh8uqh4o1470d8jgtr2xedsip` (`owner_id`),
      CONSTRAINT `FKfh8uqh4o1470d8jgtr2xedsip` FOREIGN KEY (`owner_id`) REFERENCES `users` (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    I did it.
    This shit seems to be works
    Java Code:
    Query query = entityManager.createQuery(
    			"SELECT p, NULLIF(sum(r.positive) - (count(*) - sum(r.positive)), 0) as rating FROM Photo p JOIN FETCH p.votes r WHERE p.published = 1 GROUP BY p ORDER BY rating DESC "
    					);
    	List list = query.getResultList();
    	List<Photo> photoList = new ArrayList<>();
    	for (Object ob : list)
    	{
    	    if (ob.getClass().isArray())
    	    {
    		for (Object ar : (Object[])ob)
    		{
    		    if (ar instanceof Photo)
    			photoList.add((Photo) ar);
    		}
    	    }
    	}
    Last edited by kattoha; 02-27-2016 at 01:25 PM.

Similar Threads

  1. Why do I need to convert RGB to HSB
    By nat in forum New To Java
    Replies: 9
    Last Post: 12-13-2013, 09:15 AM
  2. Convert jar to dll
    By pennaverde in forum New To Java
    Replies: 6
    Last Post: 01-13-2012, 02:47 AM
  3. cannot convert from int to int[][]
    By besweeet in forum New To Java
    Replies: 9
    Last Post: 04-18-2010, 03:36 AM
  4. convert XML using XSL
    By rajjan4u in forum XML
    Replies: 3
    Last Post: 11-20-2009, 04:37 PM
  5. Convert to Int
    By 6kyAngel in forum New To Java
    Replies: 0
    Last Post: 03-06-2009, 09:18 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •