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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Software ProgrammerAuthor Commented:
Thanks a lot and nice explanation...Appreciated....
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.