How to define Ignite Caches for objects Joined from Multiple Tables

Question

I am working with Postgres set up as an external persistent store for Ignite and want to know what ways there are to define caches for objects who’s data is spread over multiple tables.

E.G. for to work with this Person and Car class and their tables below, I have provided my own Implementation of the CacheStore. This approach seems to be very verbose however as I need to manually assign the field values myself. Are there any other methods I could be using to do this?

Person Class:

public class PersonMO{
        private int id;
        private String name;
        private String address;
        private Set<Car> cars

        public PersonMO() {};
        public PersonMO(int id, String name, String address) {
                this.id = id;
                this.name = name;
                this.address = address;
        }
       
        public String getName() {
                return name;
        }
       
        public void setName(String name) {
                this.name = name;
        }

        public int getId() {
                return id;
        }
       
        public void setId(int id) {
                this.id = id;
        }
       
        public String getAddress() {
                return address;
        }
       
        public void setAddress(String address) {
                this.address = address;
        }
       
        public String toString() {
                return "ID: "+id +", Name: "+name+" AD: " +address;
        }

        public void setCars(Set<Car> cars) {
                this.cars = cars;
        }
       
        public Set<Car> getCars() {
                return cars;
        }
}

Car Class

public class Car {
        int id;
        private String name;
       
        public Car(int id, String name) {
                this.id = id;
                this.name = name;
        }
       
        public int getId() {
                return id;
        }
       
        public void setId(int id) {
                this.id = id;
        }
       
        public String getName() {
                return name;
        }
       
        public void setName(String name) {
                this.name = name;
        }
       
}

CacheStore implmentation

public class PersonMOCacheStore implements CacheStore<Integer, PersonMO>{

        @SpringResource(resourceName = "pgDataSource")
        private DriverManagerDataSource pgDataSource;
       
        @LoggerResource
    private IgniteLogger log;

        //public void loadCache(IgniteBiInClosure<Integer, PersonMO> clo, @Nullable Object... args)
        @Override
        public void loadCache(IgniteBiInClosure<Integer, PersonMO> clo, Object... args)
                        throws CacheLoaderException {
                log.info(">> Loading cache from store...");
               
                try(Connection conn = pgDataSource.getConnection()){
                        try(PreparedStatement st = conn.prepareStatement("select * from PERSON")){
                                try(ResultSet rs = st.executeQuery()){
                                        while(rs.next()) {
                                                PersonMO person = new PersonMO(rs.getInt(1),rs.getString(2), rs.getString(3));
                                                person.setCars(getCarSet(conn, person.getId() ) );
                                                clo.apply(person.getId(), person);
                                        }
                                        log.info(">> Finished Loading cache from store...");
                                }
                        }
                }catch(SQLException e) {
                         throw new CacheLoaderException("Failed to load values from cache store.",e);
                }
               
        }
       
        //implementation for IgniteCache.get
        @Override
        public PersonMO load(Integer key) throws CacheLoaderException {
                log.info(">> Loading person from store...");
               
                try (Connection conn = pgDataSource.getConnection()) {
                        try(PreparedStatement st = conn.prepareStatement("select * from PERSON where id = ?")){
                                st.setString(1, key.toString());
                                ResultSet rs = st.executeQuery();
                                if(rs.next()) {
                                        PersonMO p= new PersonMO(rs.getInt(1),rs.getString(2), rs.getString(3));
                                        //p.setCars( getCarSet(conn, p.getId() ) );
                                        return p;
                                }else {
                                        return null;
                                }
                               
                               
                        }
                }catch(SQLException e) {
                         throw new CacheLoaderException("Failed to load values from cache store.",e);
                }
        }


        private Set<Car> getCarSet(Connection conn, int personId) throws SQLException{
                Set<Car> carSet = new HashSet<Car>();
                PreparedStatement st = conn.prepareStatement("select * from CAR where id = "+ personId);
                ResultSet rs = st.executeQuery();
               
                while(rs.next()) {
                        carSet.add(new Car(rs.getInt(1),rs.getString(2) ));
                }
                return carSet;
        }
       //other methods needed left out for sake of simplicity 
}

Answer 1

You can use CacheJdbcPojoStore to populate caches. Then the data may be accessed via SQL or key/value APIs.

However, it's not much less verbose :)

https://www.gridgain.com/docs/latest/developers-guide/persistence/external-storage#cachejdbcpojostore