How to map columns and values when JDBC Template and Native SQL queries are used

We are using Spring JDBC Template and native SQL queries to query the database and not using hibernate.

We define database column names in constants file and values will be coming from POJO class. Currently everywhere we are doing
.addValue(columnname, value)
.addValue(columnname, value)
.addValue(columnname, value)

Each table column names would be different.

Is it possible to generalize the method as a common api so that any entity save would it to save/update the columns?

Basically trying to configure SQL columns and values as generic parameters and save in appropriate table - example product, sales, inventory etc.,

Thanks.
Software ProgrammerAsked:
Who is Participating?
 
mccarlIT Business Systems Analyst / Software DeveloperCommented:
For a snippet of what I suggested above, see below...

// First make sure that you configure this slightly different version of JdbcTemplate in your Spring config,
//   otherwise you can create one in the class that uses it like this...
NamedParameterJdbcTemplate npJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);

Open in new window


And then a method that uses this NamedParameterJdbcTemplate and BeanPropertySqlParameterSource could look like this...
    public void insertEmployee(Employee employee) {
        npJdbcTemplate.update("insert into employees (emp_name, emp_address, emp_first_name) values (:name, :address, :firstName)", new BeanPropertySqlParameterSource(employee));
    }

Open in new window


Where the Employee class looks like this...
public class Employee {
    private String name;
    private String address;
    private String firstName;

    public Employee(String name, String address, String firstName) {
        this.name = name;
        this.address = address;
        this.firstName = firstName;
    }

    public String getName() {
        return name;
    }

    public String getAddress() {
        return address;
    }

    public String getFirstName() {
        return firstName;
    }
}

Open in new window



The main thing to note here, is that on the line that actually does the insert, the first set of names (i.e. emp_name, emp_address, emp_first_name) has to match your database column names, and the last part (i.e. :name, :address, :firstName) has to match the names in your POJO bean, specifically the names of the getter methods but with the "get" removed and the next letter of the method name converted back to lower case.
0
 
mccarlIT Business Systems Analyst / Software DeveloperCommented:
Check out these two classes...

For reading query results into a bean object, https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/jdbc/core/BeanPropertyRowMapper.html

For getting values from beans into queries (where clauses and inserts, etc), https://docs.spring.io/spring/docs/current/javadoc-api/org/springframework/jdbc/core/namedparam/BeanPropertySqlParameterSource.html

Note, as the javadoc says, you wouldn't probably use these in a performance critical situation. They are present more for convenience.
0
 
Software ProgrammerAuthor Commented:
mccarl..i am aware of the both the links but not sure how that is going to resolve my issue. Could you please suggest an idea or example code snippet to understand it?
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
girionisCommented:
Not sure if I understand your question correctly, but it seems that a Map might be a good match for what you're trying to do. Pass a Map as parameter (with the column name as key and the column's value as value), then iterate over the map key/values and add them one by one.
0
 
Software ProgrammerAuthor Commented:
Need a sample code which accepts column names and POJO and binds the corresponding values to it
0
 
girionisCommented:
Can you post a small example of the code that is using the .addValue(columnname, value)?
0
 
Software ProgrammerAuthor Commented:
.addValue("emp_name", value1);
.addValue("emp_address", value2);
.addValue("emp_first_name", value3);

Will Dozer or Model Mapper will resolve this issue or any generic method to handle it??
0
 
girionisCommented:
		Map<String, Object> values = new HashMap<>();
		values.put("emp_name", "Bob");
		values.put("emp_address", "Address xyz");
		values.put("emp_first_name", "Alice");

		for (Map.Entry<String, Object> entry : values.entrySet()) {
			.addValue(entry.getKey(), entry.getValue());
		}

Open in new window

0
 
Software ProgrammerAuthor Commented:
But values will be dynamic and not static
0
 
Software ProgrammerAuthor Commented:
Thanks a lot and nice explanation...Appreciated....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.