• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JDBC and arrays: does it only handle arrays of Objects?

 
Avor Nadal
Ranch Hand
Posts: 151
Java Netbeans IDE Postgres Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello:

I'm curious as how JDBC handle arrays of primitive types. I know that it can directly work with arrays of bytes (byte[]). But I'm referring to the other primitive types: int[], long[], etc. I've been checking the java.sql.Array interface and it seems that it only can work with arrays of Objects that you must cast explicitly: Integer[], Long[]... Am I right?

Thank you.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
And for what purpose do you want to use this array? Does the database column has the SQL type ARRAY? I have never seen this type in any of the databases I worked on.
 
Avor Nadal
Ranch Hand
Posts: 151
Java Netbeans IDE Postgres Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello Roel:

Thanks for your answer. Really it's not a question directly related to one of my current projects. I simply was curious about the fact that you can't get an array more efficiently, with primitives instead of Objects.

Besides that, Is this type of column so rare? By your words, it sounds like that. It's true that I have only used it once, and because I didn't design the BBDD myself. But I thought that people used this type more frequently, to reduce the quantity of registries in a table and improve the performance. For example, when I had to re-design the BBDD that I've mentioned, I decided to replace that column of type ARRAY with actual registries (arranged "vertically" instead of "horizontally"). I made this change because, in my opinion, the elements of the array broke the design and homogeneity of the rest of tables, which always expanded "vertically". But after the switch I went from 1,214,300 registries, to... 27,420,200! That's a big difference. I had to modify and create some indexes to fix some SQL queries that had turned very slow. So I've always wondered if it was the right decision, and whether people use the ARRAY type for these specific scenarios.
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Creating indexes, and getting the correct indexes on a table are how you handle large data sets.

By "registries" do you mean "rows"?

If so, then 27 million, especially if most of them are in tables that only have primitives (eg numbers) that were previously in your ARRAY columns, is not a great deal.
Those tables will be quick look ups, with the correct indexing.
 
Avor Nadal
Ranch Hand
Posts: 151
Java Netbeans IDE Postgres Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:Creating indexes, and getting the correct indexes on a table are how you handle large data sets.

By "registries" do you mean "rows"?

If so, then 27 million, especially if most of them are in tables that only have primitives (eg numbers) that were previously in your ARRAY columns, is not a great deal.
Those tables will be quick look ups, with the correct indexing.


Yes, I meant "rows". I usually use the term "registry" in my Java EE applications to be as much generic as possible about the storage back-end that I use. Even though I always use a database. But yes, in terms of relational databases, the right term is "row". Sorry ;) .

I agree about the importance of using the appropriate indexes. But back in that time, when I made the switch from an ARRAY column to actual rows, I had not much idea about indexes and the query planner. So it was horrible to see such a decrease of performance :S . Nowadays I always create the indexes that I need. But I admit that, from time to time, I wonder whether people use ARRAY columns, and whether my decision was the right one. But by your words, it seems that that type of column isn't so common .

I also take the opportunity to say that sometimes, at least in PostgreSQL, indexes are not enough. Because the query planner also takes into account some configuration parameters, and it may decide not to use your index if it considers that it will be more expensive that a sequence scan. Indeed, yesterday I experienced one of these situations. I almost become crazy, he he he. It turned out to be because of the value of the "random_page_cost" parameter, which was set to 4. The expected time cost calculated by the planner was not accurate at all. I did some tests using the EXPLAIN command and forcing the use of index scans, and the planner happened to be completely wrong. By a width margin. So I decreased that parameter to 2 and the the situation improved a lot. Obviously, all these matters depend on the hardware and OS of each machine. It's an art... From which I still have to learn a lot.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic