2

I'm doing a project using JSF 2 and I have a particularly complicated query that does movie suggestions. I tried to put it as a named query and the compiler told me there was an error and I honestly have no idea where to look

SELECT M.*
FROM Account A,
     Movie M,
     Orders O,
     Rental R,
  (SELECT Acc.Id
   FROM Account Acc,
     (SELECT COUNT(*) AS c,
             Acc.Id
      FROM Account Acc,
        (SELECT M.*
         FROM Account A,
              Rental R,
              Orders O,
              Movie M
         WHERE A.Id=R.AccountId
           AND R.MovieId=M.Id
           AND R.OrderId=O.Id
           AND A.Id=:id) AS a
      INNER JOIN
        (SELECT M.*,
                A.Id AS AccountId
         FROM Account A,
              Rental R,
              Orders O,
              Movie M
         WHERE A.Id=R.AccountId
           AND R.MovieId=M.Id
           AND R.OrderId=O.Id
           AND A.Id<>:id) AS b USING (Id,Name,TYPE,Rating,DistrFee,NumCopies)
      WHERE Acc.Id = b.AccountId ) AS COUNT
   WHERE COUNT.c>0
     AND COUNT.Id = Acc.Id) AS SimilarAccount
WHERE A.Id=R.AccountId
  AND R.MovieId=M.Id
  AND R.OrderId=O.Id
  AND A.Id=:id
  AND M.Id NOT IN
    (SELECT M.Id
     FROM Account A,
          Rental R,
          Orders O,
          Movie M
     WHERE A.Id=R.AccountId
       AND R.MovieId=M.Id
       AND R.OrderId=O.Id
       AND A.Id=SimilarAccount.Id)

Performance issues aside (the professor wanted movie suggestions to be done via MySQL), I am not sure the procedure for storing this query for repeated using. I would store the query as a view, but it has a parameter (it requires a parameter id for the user)

How would you suggest storing this query?

Justin
  • 9,634
  • 6
  • 35
  • 47
Phillip Huff
  • 555
  • 1
  • 6
  • 19
  • 2
    Maybe as `View` ? No... There are params. Stored procedure, otherwise. – BlitZ Apr 15 '13 at 05:53
  • "AND A.Id=:id" <== this looks weird, maybe the ":" is just too much? – DrCopyPaste Apr 16 '13 at 12:31
  • :id is a parameter. Those are normal syntax for NamedQueries in JPA. I made it work by converting it to a Stored Procedure and calling the procedure via a NativeQuery. I'm leaving the question open though in case someone has a better idea. – Phillip Huff Apr 16 '13 at 17:06

1 Answers1

0

I would suggest creating a Java Properties file with key-value pairs and make a private field of type Properties in your DAO class and inject it using Spring configuration which will read the values from the file or u can consider using stored procedures.. If i understand u right....

Vijay Jana
  • 11
  • 4