Hi Slava,
I have found a way around for this. Not sure about the performance
impediments though What I am doing is I am creating a
CachedStoreAdapter which takes in a Map and returns a
List>. So the implementation is something like this I
pass in a sql statement via map and also the necessary query params into the
cache load method by calling cache.get(Map).
If the query params and result is there in the cache it does not look into
the db . But if the queryparams is not there it goes to the load method
fires the sql statement extracting it from the map that is passed to the
load method. Then returns the map containing the resultset object values. I
am not using BinaryBuilder here . Because for sql query to work on binary
object we need to load the cache first hand and read through is not
available . Hence as we are working on key value pair and the sql cant be
fired on the cache so am returning a map.
The Implementation is as below.
CacheExample class
CacheConfiguration,List>>
joinconfig = new CacheConfiguration();
joinconfig.setName("JOINSCHEMA");
joinConfig.setCacheStoreSessionListnerFactories(new
Factory(){
@Override
public CacheStoreSessionListener create(){
CacheJdbcStoreSessionListner lsnr = new CacheJdbcStoreSessionListner();
MySqlDataSource mysqlDS= new MySqlDataSource();
mysqlDS.setUrl("jdbc:mysql://localhost:3306/dbname");
mysqlDS.setUser("username");
mysqlDS.setPassword("password");
lsnr.setDataSource(mysqlDS);
return lsnr;
}
});
joinConfig.setCacheStoreFactory(FactoryBuilder.factoryOf(CacheJdbcResultStore.class));
joinConfig.setReadThrough(true);
Ignite ignite = Ignition.start();
Map queryParams = new HashMap();
String sqlstmnt ="select c.id, p.name,c.city,p.salary FROM PERSON AS P ,
CITY AS C WHERE P.city_id = c.id and c.id=?";
Map queryParamsVal = new HashMap();
queryparams.put("sql",sqlstmnt );
Map queryParamsVal = new HashMap();
queryParamsVal.put("id",1);
queryParams.put("paramval",queryParamsVal);
IgniteCache, List>> joinCache =
ignite.getOrCreateCache(joinConfig).withkeepBinary();
///first get call to cache will go inside load method as data is not there
System.out.println("First Call to cach will go through to load
"+Arrays.asList(joinCache.get(queryParams)));
System.out.println("Second call to cache will not go to load as data is
already there"+Arrays.asList(joinCache.get(queryParams)));
queryParamsVal.put("id",2);
queryParams.put("paramval",queryParamsVal);
System.out.println("Now data is changed in query params so will again go to
load method in cache");
System.out.println("First Call to cach will go through to load
"+Arrays.asList(joinCache.get(queryParams)));
System.out.println("Second call to cache will not go to load as data is
already there"+Arrays.asList(joinCache.get(queryParams)));
CachedJDBcresultStore.java
public class CacheJdbcResultStore extends
CacheStoreAdapter,List>>{
public List> load(Map args) throws
CacheLoaderException{
try (Connection con = connection()){
try(PreparedStatement st =
con.prepareStatement(args.get("sql").toString({
Map paramval =
(HashMap)args.get("paramval");
st.setInt(1,(Integer)paramVal.get("id"));
ResultSet rs = st.executeQuery();
List ls = new ArrayList<>();
while(rs.next()){
Map valueMap = new HashMap<>();
valueMap.put("city",rs.getString("city"));
valueMap.put("name",rs.getString("name"));
valueMap.put("id",rs.getInt("id"));
valueMap.put("salary",rs.getDouble("salary"));
ls.add(valueMap);
}
return ls
}
}
}
}
}
// add all other unimplemented method
private Connection connection(){
class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con = DriverManager.getConnection("jdbcurl");
conn.setAutoCommit(true);
return conn
}
}
Kindly let me know what can be the performance or limitations of using this
approach. However proper readthorugh is achived indirectly via sql by this
approach.
Thanks in advance
Debashis Sinha
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
