Is it possible to fetch data from database MVC style using procedures?

Question

Sorry for a badly framed title question.

But basically if I have two classes , Employee and Address. Employee has reference to an instance of Address.

Class Employee{
  @Id
  Integer id;

  @Column(name="Name")
  private String name;

  @Column(name="Address")
  private Id addressId;

  @ManyToOne(name="Address", referencedColumnName="ID", insertable=false, updatable=false)
  private Address address;
}

now using spring-hibernate, when I execute something like "Select * from Employee" , it will fetch me not only each employee record but also the corresponding contained Address record.
Thats how hibernate does it . I don't only get the address Id but a whole data of address in each object of employee. So I can do something like employee.getAddress().getName()

but the thing is due to some weird client requirement, we're forced to only use procedures in our code to access data and procedures in turn will call queries . So no direct query in our code. but with procedures we won't be able to take advantage of this feature of spring-hibernate.

Is it possible that a procedure can do the same (return whole data of each employee in each employee object)??

Orest · Accepted Answer · 2016-02-16 13:11:47Z

1

Basically you can use RowMapper for that. Your procedure should return joined data employee and address. Simple example with JdbcTemplate:

public List<Employee> getEmployees(){
    final String sql = "CALL employees_procedure";
    final List<Employee> employees = (Employee) jdbcTemplate.queryForList("CALL employees_procedure", new EmployeeRowMapper());

    return employees ;
}

public class EmployeeRowMapper implements RowMapper {
    public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
        Employee employee= new Employee ();
        employee.set(rs.getString("e.id"));
        ...
        return employee;
    }
}

edited Feb 16, 2016 at 13:11

answered Feb 16, 2016 at 7:47

Orest

6,77812 gold badges57 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Sarabjeet Over a year ago

what if two columns in a table have the same column , say ID ?? how then we would distinguish ? also my classes tend to be fairly nested sometimes. In our project, we have say Employee has instance of Address has instance of State which has an country. So I think we will still have a lot of filtering to do in our RowMapper instance Is there any shorter way, though the one you suggested is the next best thing IMHO ! :)

Sarabjeet Over a year ago

Also what if one employee is mapped to two addresses. how to address filtering and mapping in that case ?

Orest Over a year ago

you should set table aliases like Employee e JOIN Address a, and then getInt("e.id") as I wrote. anyway when you query with hibernate it doing the same nested joins, so they would be in your procedure.

Orest Over a year ago

Ah yes missed that, then probably you should use ResultSetExtractor for example stackoverflow.com/a/6522544/2055854

Sarabjeet Over a year ago

sorry the link u gave had no example of ResultSetExtractor

|

lakhan_Ideavate · Accepted Answer · 2016-02-16 07:47:35Z

-1

Please look at How to call procedures

answered Feb 16, 2016 at 7:47

lakhan_Ideavate

3744 silver badges15 bronze badges

Collectives™ on Stack Overflow

Is it possible to fetch data from database MVC style using procedures?

2 Answers 2

11 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related