2

I'm trying to have the result of a query returned to me as a JSON string in java.

I have tried using both HQL and setting up a native SQL query, but it seems i'm required to use the UUID type, which i do not need because none of the fields are JSON. I have queried the database with a similar query and obtained results compatible with what i need

// the three lists in input are always the same length (controlled elsewhere)

    public static String directQuery(List<String> vars, List<String> criteri, List<String> param){
        Session session = HibernateUtility.getSessionFactory().openSession();
        session.beginTransaction();
        String query="";
        String json="";
        if(!GenericUtil.isNullOrBlank(vars)&&!GenericUtil.isNullOrBlank(criteri)&&!GenericUtil.isNullOrBlank(param)){
            query="SELECT CAST(json_agg(results) as TEXT) FROM public.results WHERE ";

            for(int i=0;i<vars.size();i++){
                query=query+""+vars.get(i)+" "+criteri.get(i)+" "+param.get(i)+" AND ";
            }
            query=query.substring(0, query.length()-5);
        }
        json= (String) session.createSQLQuery(query).uniqueResult();
        session.getTransaction().commit();
        session.close();
        return json;

    }

I expect a JSONArray with numerous elements, but i have yet to see any output, but recieve this error

org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111

How can this be solved? Thank you all for you time.

Edit: Changed the code to its most updated form

untrainedMan
  • 21
  • 2
  • 1
    For now, i have cast the result as `SELECT CAST(json_agg(results) as TEXT) FROM public.results WHERE`.... However, i would rather know if this was solvable in other ways, in particular, with hql. – untrainedMan Jun 07 '19 at 14:47
  • If you use a mapped entity for the result, you can get it into a JSR-353 `JsonArray` somewhat easily but there is a lot of very wrong and very bad code in the given sample. I could go through it and explain why not to use each of those parts like that, if you want. – coladict Jun 07 '19 at 14:52
  • The code is still very much work in progress, at the moment I'd rather keep it as is. I know about mapped objects, but I'm trying to avoid processing the data in java, as it is to be sent to an AJAX request in javascript. – untrainedMan Jun 07 '19 at 15:18
  • Please read something about [sql injection](https://www.baeldung.com/sql-injection) and do not compose your query like this. Also have a look at this question https://stackoverflow.com/questions/27215216/postgres-how-to-convert-a-json-string-to-text – Tijkijiki Jun 07 '19 at 16:18

0 Answers0