java - Using map to cache the data from the sql ( with <= timestamp col in it ) -

java - Using map to cache the data from the sql ( with <= timestamp col in it ) -

i set info next sql in map( because sql called many times , instead of going db everytime ), wondering how implement <= timestamp

edit:

i using oracle, updated tags, however, using preparedstatement in java caches queries, without beingness recompiled, our programme doesn't have cache solution cache info table. going db , getting info taking 2 ms roundtrip, getting info hashmap take nano second. query beingness executed around 20,000 times , load info , set within hashmap.

end of edit.

select ar table1 fk_col1 = ? , timestamp_col <= ? order date desc

the way did follows: not sure, timestamp_col in equals , hashcode right. suggest modifications?

public class table1key { private string fk_col1; private java.sql.timestamp timestamp_col; //setters , getters here. //implementing hashcode , equals. @override public int hashcode() { final int prime = 31; int result = 1; result = prime * result + ((fk_col1 == null) ? 0 : fk_col1.hashcode()); result = prime * result + ((timestamp_col == null) ? 0 : timestamp_col.hashcode()); homecoming result; } @override public boolean equals(object obj) { if (this == obj) homecoming true; if (obj == null) homecoming false; if (getclass() != obj.getclass()) homecoming false; table1key other = (table1key) obj; if (fk_col1 == null) { if (other.fk_col1 != null) homecoming false; } else if (!fk_col1.equals(other.fk_col1)) homecoming false; if (timestamp_col == null) { if (other.timestamp_col != null) homecoming false; } else if (!timestamp_col.equals(other.timestamp_col)) homecoming false; homecoming true; } }

...

private map<table1key, string> map = functions.gethashmapinstance(); public class functions { ... public static <k,v> hashmap<k,v> gethashmapinstance() { homecoming new hashmap<k,v>(); } }

so, populate map following:

private void populatemap() throws sqlexception { seek { ps = conn.preparestatement(table1sql); ps.setfetchsize(20000); resultset rs = ps.executequery(); while(rs.next()) { table1key rdk = new table1key(); string ar = rs.getstring(1); rdk.setfk_col1(rs.getstring(2)); rdk.settimestampcol(rs.gettimestamp(3)); if(actualratemap.get(rdk) == null) { actualratemap.put(rdk, ar); } } } grab (sqlexception e) { e.printstacktrace(); throw e; } { ps.close(); } }

//set key here.

table1key tk = new table1key(); tk.setfk_col1(col1); tk.settimestampcol(timestamp); string ar = actualratemap.get(tk);

//my main concern here .. work if sql has timestamp_col = ?, if timestamp_col < nowadays in map?

if(actualrate != null) { logger.info("actual rate:"+actualrate); }

hashmap doesn't job case, rather treemap can help.

a): rangemap solution

guava rangemap designed handle such case:

//initial rangemap final rangemap<java.sql.timestamp, string> cache = treerangemap.create(); ... string value = cache.get(thistimestamp); if(value == null){ string queryfromdb = ...;//query db cache.put(range.atmost(thistimestamp), queryfromdb); value = queryfromdb; }

of course, 'fk_coll' problem. define map<string/*fk_coll*/, rangemap<java.sql.timestamp, string>> handle case.

b): treemap solution

final treemap<java.sql.timestamp, string> cache = new treemap<>(); ... //get to the lowest degree key greater or equal 'thistimestamp' map.entry<java.sql.timestamp, string> entry = cache.ceilingentry(thistimestamp); if(entry == null){ string queryfromdb = ...;//query db cache.put(thistimestamp, queryfromdb); value = queryfromdb; } else { value = entry.getvalue(); }

and

hashmap<string/*fk_coll*/, treemap<java.sql.timestamp, string>>

handles 'fk_coll'.

plus: evict problem in cache case.

java sql caching collections oracle10g