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

Question

We are developing an application in spring boot, where we will be creating and publishing dynamic forms into our application at runtime to post the form-data into the db (MySql) in JSON format. We want to create a generic api to handle CRUD operations for all dynamic forms.

We know about the php code-igniter framework query builder, which is capable to build and execute the queries.

https://www.codeigniter.com/userguide3/database/query_builder.html#inserting-data

For example :

$query = $this->db->get('mytable'); // Produces: SELECT * FROM mytable

        $data = array(
    'title' => 'My title',
    'name' => 'My Name',
    'date' => 'My date'

); $this->db->insert('mytable', $data);

Is there any way (any plugin or maven dependency) to do the same (as php code-igniter) in java spring boot.

        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);

Answer 1

There are few ways to achieve similar functionality:

1. Use Spring Data JPA (mostly used)

   This module generates (or derives) queries from the method based on convention, for example:

   interface UsersRepository extends CrudRepository<User, Long> {
     List<User> findByLastname(String lastname);
   }
   

   calling personRepository.findByLastName("Doe") can be translated into:

   SELECT * FROM users WHERE last_name = 'Doe'
   

   However, for Spring to do that sort of magic it needs to map the result set returned by a "raw" SQL query to an object. This is where the concept of ORM comes in. Because of that, you need to do an additional step: configure entities(that would be your objects) and set up the desired relationship between them. For example:

   @Entity
   @Table(name = "users")
   public class User {
     @Id
     private String id;
     @Column(name = "first_name")
     private String firstName;
     @Column(name = "last_name")
     private String lastName;
     //constructor, getters and setters
   }
   

   Now Spring knows how to map columns to an object and would generate query automatically for you. To be more precise, it's done by hibernate (that the ORM framework) under the hood, but don't let terminology confuse you. I would suggest googling a little bit about Spring Data JPA vs JPA vs Hibernate more since it's out of the scope of this question.

   There is a good beginner tutorial available at Spring Guides. You can also read about it more in the official documentation here.

2. Use Spring JdbcTemplate

   It's probably more similar to what CodeIgniter does, but instead of using "internal API", jdbcTemplate uses "raw" SQL statements:

   jdbcTemplate.query(
       "SELECT id, first_name, last_name FROM customers WHERE first_name = ?", new Object[] { "Josh" },
       (rs, rowNum) -> new Customer(rs.getLong("id"), rs.getString("first_name"), rs.getString("last_name"))
   ).forEach(customer -> log.info(customer.toString()));
   

   However, as you can see, it returns a result set where you need manually map your domain object.

   The code example is available at Spring Guides. More documentation is available here.

The are other alternatives such as jOOQ, MyBatis, and QueryDSL.

Personally, I have not encountered any of the approaches except "Spring Data JPA" in "production" applications I worked on (probably due to the fact that it's closer to the Java code and hides a lot of SQL internals under the hood), although very familiar with the first two. I would recommend going with the first one due to its popularity, but it's ultimately up to you.

Since it's out of the scope of that answer to decide which one you should pick, I would post some additional links:

• JPA vs Spring JdbcTemplate
• Spring Data vs Spring Data JPA vs JdbcTemplate

Update: (addressing the comment)

I still would argue that even your forms are "dynamic", your table schema is "fixed" by nature. You can:

1. Use JPA Entity to map "fixed" fields (such as id, timestamp maybe, etc.). Create dynamicForm field as a part of this Entity - it would contain a json string -> which would be "TEXT" type in DB:

   @Entity
   @Table(name = "forms")
   public class FormEntity {
     @Id
     private String id;
     private LocalDateTime created;
     @Column(name = "dynamic_form")
     private String dynamicForm;
     //constructor, getters and setters
   }
   

2. In Java, create your map (with whatever key-values as in your example) as a separate class

3. Serialize it using Jackson's ObjectMapper to string (which would be a json).

4. Map it to your Entity's "dynamicForm" field:

   public class MyBusinessLogicService {
     public void processForm(Map<String, String> map) {
         FormEntity formEntity = new FormEntity();
         formEntity.setDynamicForm(objectMapper.writeValueAsString(yourMap));
         //.... setting other values
         formEntityRepository.save(formEntity);
     }
   }
   

5. When you read the form you would deserialize dynamicForm field.

The solution is not limited by a map, but you can store any type of object. The challenge on your side would be to know what type of "object" is present in there. If it's a map, you would have no knowledge of the key-values that are inside of the map.