How to consume java Map to generate insert and update sql queries in JOOQ (without code generation)?

Question

We are developing an application where we will generate forms at run time. Now we need to perform CRUD operations on the data of the dynamic form.

we want to save the record into the table by writing

Map<String, String> map = new TreeMap<>();
    map.put("name","User Name");
    map.put("dob", "30-3-1995");
    map.put("address", "Address Line");
    map.put("city", "Jaipur");
    /* insert into user table */
    db.insert("user",map); // SAME AS JPA

Does java spring boot provides query builder like php CodeIgniter query builder

I am trying to use JOOQ to achieve the same.

    @Override
    public void testingJooq(){
        Field<String> username = field("username", String.class, "harigyan2");
        // How bindings bind the value to the Field?
        Field<String> password = field("password", String.class, "abcd");
        Field<String> email = field("email", String.class, "hg2@server.com");
        Field<Boolean> enabled = field("enabled", Boolean.TYPE, true);
        // list of columns to insert
        List<? extends Field<? extends Serializable>> columns = Arrays.asList(username, password, email, enabled);
        // list of corresponding values as columns 
        List<? extends Field<? extends Serializable>> values = Arrays.asList(field("harigyan2", String.class), field("pwdhere", String.class), field("harigyan@server.com", String.class), field("1", Boolean.TYPE));
        this.create.insertInto(table("user"), columns)
                .values(values).getSQL();
    }

The problems with the above code is that :

1. the two lists columns and values should be in the same sequence and
2. data types in the values should be matched with the table column data type in the code

Note : We can't use JOOQ with code generation because all the forms will be dynamic and will not have any corresponding Java Class (Entity class).

Map<String, Object> map = new TreeMap<>();
    map.put("name","User Name");
    map.put("dob", "30-3-1995");
    map.put("address", "Address Line");
    map.put("city", "Jaipur");
    /* insert into user table */
   this.create.insertInto(table('user')).values(map).execute();

It will be useful if above JOOQ insert statement find the fields names (map key name as column name) and values from the map<key, value> and create a insert sql statement.

We used php CodeIgniter in a project to build sql queries Php CodeIgniter Query Builder so we are trying to find something similar in java also.

Answer 1

Your usage of plain SQL templates for column names

Field<String> username = field("username", String.class, "harigyan2");

This doesn't do what you think it does. You're using a plain SQL template. But there's no bind parameter marker (?), so your bind value "harigyan2" is superfluous. Remove the value to do this, instead:

Field<String> username = field("username", String.class);

Or, avoid using plain SQL templates if you don't need them, e.g. by writing:

Field<String> username = field(name("username"), String.class);

See also: https://blog.jooq.org/2020/04/03/whats-a-string-in-the-jooq-api

Your usage of plain SQL templates for bind values

Don't do this!

Arrays.asList(
  field("harigyan2", String.class), 
  field("pwdhere", String.class), 
  field("harigyan@server.com", String.class), 
  field("1", Boolean.TYPE)
);

You're again using plain SQL templates, meaning your strings will be included in your SQL statements verbatim. This leads to syntax errors and SQL injection. Use bind values by wrapping the values using DSL.val()

Arrays.asList(
  val("harigyan2", String.class), 
  val("pwdhere", String.class), 
  val("harigyan@server.com", String.class), 
  val("1", Boolean.TYPE)
);

Alternatively, re-use the type information from your previously defined field references:

Arrays.asList(
  val("harigyan2", username), 
  val("pwdhere", password), 
  val("harigyan@server.com", email), 
  val("1", enabled)
);

Using a map directly

You can! If you have a Map<String, Object>, Map<Name, Object> or Map<Field<?>, Object>, just pass it to the insert statement:

create.insertInto(table("user")).set(map).execute();