How to set user defined variables in MySQL through a Java project using JDBC

Question

Summing up I want to avoid what is beins talk about in posts like this:

https://stackoverflow.com/a/9673179/6028947

I have a pretty complex query that a co-worker has helped me write:

set @profile_rank=0, @current_profile=0; 

select filtered.id_profile, profile.name_profile, count(*) as matching from 
    (
    select * 
    from (select id_profile, id_skill, name_skill as name, ranking_skill, @profile_rank := if(@current_profile = id_profile, @profile_rank + 1, 1) as profile_rank,
    @current_profile := id_profile from 
        (
        select distinct id_profile, skill.id_skill, name_skill, ranking_skill 
        from offer_skill_profile 
        join skill 
        on offer_skill_profile.id_skill = skill.id_skill
        ) sp 
        order by id_profile, ranking_skill desc, name_skill) ranked where id_skill in ('109250886','1224864731') and profile_rank <= 10
    )
    filtered inner join profile on profile.id_profile = filtered.id_profile group by id_profile order by matching desc;

Now it does exactly what I need it to do, and the only problem is executing it from Java. From what I have tested and read about, I believe it is not working because the variable set loose their value, basically, stuff like this is not getting past 0 value : @profile_rank + 1

Now I have read it might be because prepared statement cannot handle multiple sql statements, and the first line with set @profile_rank=0, @current_profile=0; is being count as a statement.

So while this posts are not about JDBC which is what I use, there are several connectors that cannot do this operation, and I suspect it might be the same with JDBC but cannot find anything too specific. I am trying to learn how can this be done from Java, using JDBC and this is the version I use in the POM

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.46</version>
</dependency>

To clarify, the query does not fail, it simply gives wrong results, which are what I used to get in previous, unrefined versions of the query, when I did not take into account that each profile has a different top 10 (if you see at the end of the query there is a filter to profile_rank only accepting values below 10).

Additional info:

Mysql version 5.7, java 8.

CREATE TABLE profile (
        id_profile VARCHAR(200) NOT NULL,
        name_profile VARCHAR(200),
        type_profile VARCHAR(200),
        PRIMARY KEY (id_profile)
    );


CREATE TABLE offer_skill_profile (
    id_oferta VARCHAR(200) NOT NULL,
    id_skill VARCHAR(200) NOT NULL,
    id_profile VARCHAR(200) NOT NULL,
    ranking_skill DOUBLE NOT NULL,
    PRIMARY KEY (id_oferta, id_skill, id_profile),
    FOREIGN KEY (id_oferta) REFERENCES oferta(id_oferta),
    FOREIGN KEY (id_skill) REFERENCES skill(id_skill),
    FOREIGN KEY (id_profile) REFERENCES profile(id_profile)
);

Results of doing a

select * from ternay limit 10;

+------------+------------+-----------+----------------------+
| id_oferta  | id_skill   | id_perfil | ranking_skill        |
+------------+------------+-----------+----------------------+
| 1004 | 107              | 679681082 |                    0 |
| 1004 | 115              | 679681082 |  0.10846866454897801 |
| 1004 | 117              | 679681082 | 0.038003619695992294 |
| 1004 | 129              | 679681082 |  0.04987975085098989 |
| 1004 | 147              | 679681082 |  0.02771097269499438 |
| 1004 | 299              | 679681082 |   0.0522549770819894 |
| 1004 | 321              | 679681082 |  0.11955305362697576 |
| 1004 | 417              | 679681082 |  0.11321911701097703 |
| 1004 | 964              | 679681082 | 0.015043099462996949 |
| 1004 | 967              | 679681082 |  0.05304671915898924 |
+------------+------------+-----------+----------------------+

Sample of code I use:

String complexQuery = "set @profile_rank=0, @current_profile=0;
select filtered.id_profile, profile.name_profile, count(*) as matching from 
    (
    select * 
    from (select id_profile, id_skill, name_skill as name, ranking_skill, @profile_rank := if(@current_profile = id_profile, @profile_rank + 1, 1) as profile_rank,
    @current_profile := id_profile from 
        (
        select distinct id_profile, skill.id_skill, name_skill, ranking_skill 
        from offer_skill_profile 
        join skill 
        on offer_skill_profile.id_skill = skill.id_skill
        ) sp 
        order by id_profile, ranking_skill desc, name_skill) ranked where id_skill in ('109250886','1224864731') and profile_rank <= 10
    )
    filtered inner join profile on profile.id_profile = filtered.id_profile group by id_profile order by matching desc;";       

    preparedstatement = con.prepareStatement(complexQuery);
    preparedstatement.setString(1, queryParameters.get("key").get(0));

Later on in another part of the code I do:

if (preparedStatement != null) {
rsExecuted = preparedStatement.executeQuery();
                    }