Are there any data structures and/or libraries in Java that will allow for the creation of what would essentially be a table of data with String column/row labels and numeric data? It doesn’t need to be displayed to the user, just something for my program to use in order to efficiently access data.
For example, if I want to access the sales revenue data for account #123 from July 1, 2021. With my knowledge currently the only ways I can think to do this are to have a ridiculously complicated web of maps with string keys that refer to lists of the final values. For example, I could create a map with String keys referencing the account name with another map as the value. That map could contain a string representing a date with the associated value being the revenue for that date.
I would imagine there has to be an easier way to accomplish this. My only other thought was to literally create an excel sheet VIA java (Apache POI ) and send all my numbers there to be referenced which seems like a lot of extra unnecessary work and processing for the program.
I don’t have enough data to warrant the creation of an actual database. I really just need something small scale that is flexible enough to handle different data types so I can have string labels.
Maybe there is a way to do this with 2D arrays, I just can’t think of how since they can’t handle different data types.
My last idea was to try and implement a guava table, though I’m not sure if something like that would work for my use case, currently doing more research on how guava tables work.
Advertisement
Answer
You have pretty much defined “relational database”.
A table in a relational database is a named collection of rows (records) whose values are defined by columns. Those columns are named, and each column is designated by you to hold a certain kind of data (data type).
For example, you said:
For example, I could create a map with String keys referencing the account name with another map as the value. That map could contain a string representing a date with the associated value being the revenue for that date.
That sounds like a account table having a column for account number of type text (or VARCHAR) and a column with customer name. The account number column holds values that identify each account uniquely, so we call that a primary key.
A related table might be named valuation with a column of a date type, and a column of a numeric type for the valuation amount. You would also add a primary key, likely a sequential number auto-generated. And you would have a foreign key which would be a copy of that primary key value from the parent account, the account number.
As a Java developer, you have several choices for a database engine. I would suggest the H2 Database Engine because it is easy to embed in your app as H2 is written in Java. And it is easy to get started with, has good documentation, and is actively developed. But you have other choices too such as Apache Derby, SQLite, and more. Asking for specific recommendations for software products or libraries is explicitly off-topic on Stack Overflow, so I’ll leave it at that.
If you search Stack Overflow, you will find multiple Answers of mine with complete code for examples of working with H2 from JDBC code in Java including instantiating a DataSource, creating a table, populating data, and running queries to retrieve data, with use of try-with-resources syntax.