• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Tim Cooke
  • Liutauras Vilda
  • Jeanne Boyarsky
Sheriffs:
  • Paul Clapham
  • Rob Spoor
  • Junilu Lacar
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Piet Souris
  • Carey Brown
Bartenders:

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

 
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Saloon Keeper
Posts: 9821
80
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Emily Green
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Saloon Keeper
Posts: 9821
80
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
That was my issue. Getting the values.
This is my function



Thank you very much for your replies! I just don't know what the best way to do this would be
 
Carey Brown
Saloon Keeper
Posts: 9821
80
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
What is the layout of the existing database lookup table? Is this a fixed layout or can you redesign it?
 
Emily Green
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Saloon Keeper
Posts: 9821
80
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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

 
Emily Green
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Saloon Keeper
Posts: 9821
80
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Saloon Keeper
Posts: 9821
80
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.

 
Emily Green
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Saloon Keeper
Posts: 9821
80
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Your ValueScore class would need to implement the getValue() and getScore() getter methods. Then you can have something like:

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.
 
Saloon Keeper
Posts: 5233
211
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
A TreeMap<Integer, ...> is your friend. Look at the methods 'ceilingEntry' and 'floorEntry'.
 
Carey Brown
Saloon Keeper
Posts: 9821
80
Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic