Function that looks up a table for a value and returns the corresponding value

[Emily Green]

I'm sorry if this question may be fairly easy or too broad but I'm a beginner and this is what I'm trying to do:
In the database I have a table for Users, and one table that is used as lookup. it holds scores and values.
i.e

10 5
15 6
20 7

After form submission, on the Users table a value like 12 is persisted.
What I need is, a function to get this value 12..Look it up on the table.. It falls in the range 10-14 which means it has a score of 5.
I need to take this score and perform some calculations.
How do I do this ?
I am using Spring-boot , I can use getters and setters for the values

[Carey Brown]

Using your example, you have blocks of 5 possible values that would result in the same lookup value being returned. So, if you do this as a formula instead of a lookup you'd have

lookupValue = enteredValue / 5 + 3;

[Emily Green]

The values given were just an example so that I keep the question simple. Because the actual values in the database are like
0   1
68  2
79  3
85  4
...
Not every range is of equal size, But thank you. i was looking for a more general way to actually look them up, because there's more than one table with different values. I want to be able to use the same way in all of them

[Carey Brown]

If you are already using a database then why not store the table in the database? If the range isn't to huge then I'd suggest filling in the gaps in your lookup with actual entries. Again, using your example:
10 - 5
11 - 5
12 - 5
13 - 5
14 - 5
15 - 6
16 - 6
Or, have a lookup with a min/max range
10, 14 - 5
15, 19 - 6
Then an SQL query could easily look up the value.

[Emily Green]

That was my issue. Getting the values.
This is my function

public void Calculate(long id ) {
 private UserRepository user;
 User userInfo=user.findOne(id);
// now I have the user object

integer number=userInfo.getNumber(); // I get the number 12

//this is the part that I'm missing. 
Where do I load the table that's in the database as a lookup, so that I can iterate through it and check where 12 falls ?
What data structures are best to use ? Dictionaries, maybe TreeMaps ? I'm not familiar with that part. So I dont know how exactly to do it 

}

Thank you very much for your replies! I just don't know what the best way to do this would be

[Carey Brown]

What is the layout of the existing database lookup table? Is this a fixed layout or can you redesign it?

[Emily Green]

The table looks like this :
Value  Score
0         1
68       2
79       3
85       4
90       5
98       6
110      7
120     8

The important part is that when I get a value that falls between a certain range, I get the correct score as a result.
The table is not a must to be on the database. It can be on a class too.
But there's 20 tables similar to this one. I was suggested to store them to the database as tables and look the values accordingly.

[Carey Brown]

If you create a ValueScore class that has members 'value' and 'score' and keep these in either an array or an ArrayList in sorted order you could iterate through them looking for

for( int i=0 ; i < ary.length-1 ; i++ ) {
    if( input >= ary[i].value && input < ary[i+1].value ) {
        return ary[i].score;
    }
}

[Emily Green]

That's a very good solution. Thank you so much, but could I create one class to hold all lookup tables ?
And instead of arrays to use Maps so that I don't iterate all the time ? These are just things I've learnt theoretically, my problem is implementing it as I'm fairly new

[Carey Brown]

Emily Green wrote:That's a very good solution. Thank you so much, but could I create one class to hold all lookup tables ?
And instead of arrays to use Maps so that I don't iterate all the time ? These are just things I've learnt theoretically, my problem is implementing it as I'm fairly new

I was originally thinking of a Map but that wouldn't work if your data has gaps in it unless your set was small enough that you could load all the intermediate values into the Map when you load a lookup table. For a smallish set of value/scores I don't see the performance of iterating through the values as presenting any issue.

[Emily Green]

Well I have 20 lookup Table, with 10 rows each.
But there are some big gaps. As for example there is one table that has values like :
Value score
0       1
200   2
and so on.

And There's calculations that I need to perform with those scores.
So say You fill out the form with values. They're persisted to the database.
And theN I call the function that takes the values you've entered. And looks up the score for each value in the lookup Tables ( which can be either stored in the DB as tables or in a class..I think class might be better )
And then add those scores and save the score.

[Carey Brown]

Emily Green wrote:That's a very good solution. Thank you so much, but could I create one class to hold all lookup tables ?

You could create a LookupTable class and for each of your 10 or so lookup tables create an instance of this class.
This is based on the code snippet I gave you previously.

// LookupTable for a range of values that may be huge
// Uses a List. Should be efficient enough for small to large sets
// Huge sets may present a performance issue but they will at
// least fit into memory.
class LookupTable
{
	private List<ValueScore>	list;
	private int min;
	private int max;
	
	// constructor
	// could pass in a value/score list read in from a DB, hard-coded, or some other source
	// values in list are expected to have gaps
	public LookupTable( List<ValueScore> list )
	{
		Collections.sort( list ); // ValueScore implements Comparable
		this.list = list;
		min = list.get( 0 ).getValue();
		max = list.get( list.size()-1 ).getValue();
	}
	
	public int getScore( int value )
	{
		if( value < min || value >= max )
			return -1;
		for( int i=0 ; i < list.size()-1 ; i++ )
		{
		    if( value >= list.get(i).getValue() && value < list.get(i+1).getValue() )
		    {
		        return list.get(i).getScore();
		    }
		}
		return -1; // not found - ERROR?
	}
}

[Emily Green]

This is great! Thank you so much! But how would the List look like so that I can access getValue and getScore separately ?
Should it be like a Map that holds keys and values ?

[Carey Brown]

Your ValueScore class would need to implement the getValue() and getScore() getter methods. Then you can have something like:

list.get(0).getValue();
list.get(0).getScore();

I don't think a Map helps in your case. To access a Map you need a unique key and if you lookup in your Map a key of 10 you'd find it. If, on the other hand, you used a key of 14 then you wouldn't. This is because you have gaps in your keys. This assumes that the data you are using to populate the Map with has gaps.

[Piet Souris]

A TreeMap<Integer, ...> is your friend. Look at the methods 'ceilingEntry' and 'floorEntry'.

[Carey Brown]

Piet Souris wrote:A TreeMap<Integer, ...> is your friend. Look at the methods 'ceilingEntry' and 'floorEntry'.

Cool! Thanks Piet, I wasn't aware of that one.