Mapping MySql views to JPA Entitites, which unique id to use?

Question

So I'v been using views instead of result queries as entities in my project and I know I'm not alone, so, my question:

What do you use to act as and @Id when working with views? Sometime the answer to that question will be trivial, but sometimes when you don't have a unique field that stands out, what do you guys do?

Right now I'm including more fields that I need in a particular view, so I can have a mix of fields that are unique together, and I use the @Id annotation on each of those, and so far it's been working great.

It seems to be so contextual, I'v been asking myself if there is a more standard way of doing it.

Answer 1

I don't think there is a standard way, but here is the approach that seems worths trying .

Idea is to generate unique "id" values (analog of rownum ) on the fly for the view . A bit modified version of function from Create a view with column num_rows - MySQL (modification done in order to reset rownum):

delimiter //

CREATE FUNCTION `func_inc_var_session`( val int) RETURNS int
    NO SQL
    NOT DETERMINISTIC
     begin
        if val = 0 THEN set @var := -1; end if;
      SET @var := IFNULL(@var,0) + 1;
      return @var;
     end
     //

Say we have a view definition (oversimplified for illustration purposes)

CREATE VIEW v_test1 
SELECT a.field1 
FROM test_table a  

Modifying it to

CREATE VIEW v_test1 
SELECT a.field1,  func_inc_var_session(0) as rownum 
FROM test_table a

would do the job; however, running select * from v_test within one session multiple times will give you sequential rownums, e.g. first time it starts with 1, second time with number of records in the view, etc.

To reset rownum I create another view (because of mysql view limitation - it cannot have subquery in FROM ) :

CREATE VIEW v_reset AS SELECT func_inc_var_session(1) ;

Now we can do

CREATE VIEW v_test1 
SELECT a.field1,  func_inc_var_session(0) as rownum 
FROM test_table a, v_reset

(FROM clause processed first, func_inc_var_session(1) will be executed just once during the query, so it will reset rownum) .

I hope it helps.