Storing arrays in databases

What is the most efficient way to store large arrays (10000x100) in a database, say, hsqldb? I need to do this for a certain math program that I'm writing in java. Please help. The whole array will be retrieved and stored often (not so much individual elements). Also, some meta-data about the array needs to be stored about the array.

Asked by: Blake572 | Posted: 28-01-2022

Answer 1

Great question.

Unless you want to translate your arrays into a set of normalized tables, which it sounds like you don't, you might want to contemplate serialization.

Serialization is a fancy word for turning objects into some format that you can save to disk or a database. The two major formats for serialization are binary and XML, and I'm betting that Java has some support for it.

Depending on what data types you're using, you should be able to turn your array into XML or binary and then save that to a single field in the database. You could get started with this technique in Java by checking out I know that it's built into .NET.

Hope that this helps. Let me know if I can give you any more direction.

Answered by: Lucas376 | Posted: 01-03-2022

Answer 2

How about storing the data as a BLOB and using Java to decode the BLOB into an actual Java array? It would be much more efficient for storing and retrieving the whole array in one gulp, but would be terrible for twiddling individual elements.

Answered by: Tara753 | Posted: 01-03-2022

Answer 3

Come up with an internal representation -- be it XML, JSON, some binary file you come up with yourself, or any other form of serialization.

Store it in a table using the "blob" datatype. Store any metadata associated with the matrix in additional columns.

I strongly disagree that the way to do it is to create a table with the same number of rows and columns as your matrix -- that is a very high price to pay for functionality you don't use.

Prepare your insert/select statements beforehand, and use bind variables to change what matrix you are working with -- don't make the db reparse every request.

Answered by: Emily305 | Posted: 01-03-2022

Answer 4

If its is only 1 array, why not use a binary file?

Answered by: Emily188 | Posted: 01-03-2022

Answer 5

As allready suggested: Don't use a RDBMS if you don't need the features. Instead of Serialization though you might want to concider a low level API like JDBM that provides some database like features like managing an on-disk index.

Answered by: Alissa408 | Posted: 01-03-2022

Answer 6

If your data is densely packed (the values histogram is close to flat line), your best choice is blob and serialization using Object[Output/Input]Stream.

Otherwise, you might find it more efficient to use sparse arrays and variation of Entity-Attribute-Value schema. Here is an example:

 Name | IndexKey  | Value
 foo  | 'default' | 39        
 foo  | 0:0:0     | 23
 foo  | 0:0:1     | 34
 foo  | 1:5:0     | 12
 bar  | 1:3:8     | 20
 bar  | 1:3:8     | 23
 bar  | 1:1:1     | 24
 bar  | 3:0:6     | 54

This also allows you fast updates to parts of the table and selecting slices using SQL 'like' operator.

If the number of your dimensions is fixed to break down the key column to separate int columns for each dimension in order to improve the index efficiency and have more flexible selection criteria (you can use first index 'null' for metadata like the default value).

In any case, it is a good idea to create a clustered index on Name,IndexKey columns.

Answered by: Rafael832 | Posted: 01-03-2022

Answer 7

Define a table with the data your array holds and insert the array values into a table.

This is very simple data access/storage. Will your array dimensions always be the same?

Answered by: Richard890 | Posted: 01-03-2022

Answer 8

  • Do it in one big explicit transaction. Don't force the database system to create a new implicit transaction for every insert.
  • Use a prepared statement.

Answered by: Audrey323 | Posted: 01-03-2022

Answer 9

PostgreSQL has built-in support for arrays.

Answered by: Sophia200 | Posted: 01-03-2022

Answer 10

Java Serialization to a Byte Array stored as a BLOB will be your best bet. Java will serialized a large array quite efficiently. Use the rest of the rows columns for anything you're interested in querying upon or displaying readily. It can also be a good idea to keep the BLOBs in their own table and have the "regular" rows point to the "BLOB" rows, if you query and report on the non-BLOB data much (though this can vary by database implementation).

Answered by: Julian886 | Posted: 01-03-2022

Answer 11

HSQLDB 2.0 supports one dimensional arrays stored as a column of the table. So each row of the table will correspond to one row of the 2D array.

But if you want to retreive a 2D array as a whole, BLOB is the best solution.

Answered by: Daniel306 | Posted: 01-03-2022

Similar questions

java - Storing long strings (CLOB) in Hsqldb databases?

So here's some code: statement.executeUpdate("CREATE TABLE SomeTable(id INTEGER IDENTITY, " + "text CLOB)"); which throws an exception "Wrong data type: CLOB in statement [...]". Is there a way to store CLOBs in Hsqldb databases? The documentation says it is. Or maybe my knowledge of SQL is so rusty that I forgot how to define them.

java - Are flat file databases any good?

Closed. This question is opinion-based. It is not c...

java - Joining Information Across DB2 and Oracle Databases Best Practices

We are designing a fairly large brownfield application, and run into a bit of a issue. We have a fairly large amount of information in a DB2 database from a legacy application that is still loading data. We also have information in an Oracle database that we control. We have to do a 'JOIN' type of operation on the tables. Right now, I was thinking of pulling the information out of the DB2 table into a Li...

java - Storing long strings (CLOB) in Hsqldb databases?

So here's some code: statement.executeUpdate("CREATE TABLE SomeTable(id INTEGER IDENTITY, " + "text CLOB)"); which throws an exception "Wrong data type: CLOB in statement [...]". Is there a way to store CLOBs in Hsqldb databases? The documentation says it is. Or maybe my knowledge of SQL is so rusty that I forgot how to define them.

java - Using two different databases with identical hibernate mapping files

Basically the idea is to use the same hibernate mapping files for two different underlying databases. In production the underlying database is MySQL5 and for testing purposes I'd like to use Apache Derby - in order to avoid setting up and maintaining various MySQL databases for testing purposes. I hoped that just switching the DataSource's Driver and changing a few params would do the job, but I've already run int...

java - separate read and write databases with hibernate

With hibernate, I want to be able to support multiple databases, one for read and one for writes. Is this possible? Is the only way to create 2 separate session objects?

Databases and Java

I am starting out writing java code and interacting with databases for my "nextbigthing" project. Can someone direct me towards the best way to deal with adding/updating tables/records to databases? Here is my problem. There is too much repitition when it comes to DB code in java. I have to create the tables first (I use mysql). I then create classes in Java for each table. Then I create a AddRow, DeleteRow, UpdateRow and ...

sql - Once I have the name of a SQLServer, how do I list its databases in java?

I am trying to populate a drop down list of SQLServers on a network using the osql -L command through Java. The user will then choose a server and type in a username and password. I also need to populate another list with the databases on that server. Any ideas on how I can implement that using java? If possible, please give java code. Thank you. public class SQL implements ActionLi...

java - How do I connect to multiple databases using JPA?

I have an application using Java servlets/JSP's. There are multiple clients using my app, however each client has a separate database. All the databases have the same schema. I would like to determine which database connection to use at the time when a user logs into the system. For example client A logs in, I determine that client A belongs to database C, grab the connection for database C and continue on my merr...

what databases can be used with java?

I am doing an undergrad final project, and need to justify my choice of MySQL for the database element of my project. Truth is, it's the only one I can really use, and hence I went for it. What other database systems could I have used? Any advantages and disadvantages of these over MySQL?

How to use the distinct on databases DB4O in Java?

Who can tell me how to use Distinct operation on db4o in Java code. I couldn't find any example in Java. Thanks!

Still can't find your answer? Check out these amazing Java communities for help...

Java Reddit Community | Java Help Reddit Community | Java Community | Java Discord | Java Programmers (Facebook) | Java developers (Facebook)