How to use the ARRAY Type?

[con...@googlemail.com]

Hi everybody,

I saw there is ARRAY data type, so I try to use it. But I have 2 problems.

I created a table at first: create table userdata(userid int, friends ARRAY)

1) Then, I want to insert to records:
     
        String query = "insert into userdata values(?, ?)";
        PreparedStatement prep = conn.prepareStatement(query);
        prep.setInt(1, 2);
        prep.setObject(2, new int[] { 4, 5, 6 });
        prep.addBatch();
        prep.setInt(1, 3);
        prep.setObject(2, new int[] { 3, 3, 3 });
        prep.addBatch();
        prep.execute();

But, I can just insert the last record. The first record is missing. I don't know why...

2) The second problem is I can't read the array.

        Statement stat = conn.createStatement();
        String query = "select * from userdata";
        ResultSet rs;
        rs = stat.executeQuery(query);
        rs.first();
        int[] ar = (int[])rs.getObject(2);

Then I got the error Message: [Ljava.lang.Object; cannot be cast to [I

Can someone help me?

Thank you very much!

Conny





 

[Rami Ojares]

Hi Conny,

I have never used ARRAY type (and never will :)
But few things come to my mind immediately.
First how does H2 know that you have an array of integers?
In the table definition you only say ARRAY.
Maybe it is an ARRAY of bytes or cars or .. whatever
Most likely it is an array of objects.

And hey that's exactly what the error message says!

Why don't you just model your data like this:
create table userdata(userid int primary key);
create table friends(friendid int primary key, userid int references
userdata(userid));

Now you can set constraints better to values and know your types better.
This foolery with arrays in the relational database is just asking for
trouble.
(Yes I know, I am being provocative).

- rami

> --
> You received this message because you are subscribed to the Google
> Groups "H2 Database" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/h2-database/-/qokF3f6ZtxcJ.
> To post to this group, send email to h2-da...@googlegroups.com.
> To unsubscribe from this group, send email to
> h2-database...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/h2-database?hl=en.

[Lukas Eder]

> Now you can set constraints better to values and know your types better.
> This foolery with arrays in the relational database is just asking for
> trouble.
> (Yes I know, I am being provocative).

Arrays can be very useful with stored procedures... In normalised
schemata, of course, they probably don't make much sense

[con...@googlemail.com]

Hi Rami,

Thank you very much for the answer, but I still don't understand much about the two tables, which you recommended.

For example:
My original database:
-----------------------------------
userId | Friends
------------------------------------
user1 | user3
user2 | user3
user3 | user1, user2
-------------------------------------

The new tables are:

Userdata:
----------
userId
-----------
user1
user2
user3
--------

Friends:
----------------------
friendId | userId
------------------------
user1 | user3
user2 | user3
user3 | user1
user3 | user2
---------------------------

1)In this Example the user1 and user2 has the same friend user3, so the "Friends" Table needs more space then the first ARRAY Type. Isn't it?

2) Why do I need the table "Userdata" ? I can easily use "select * from Friends where userId = ???"  and get all the information. Will the Table userdata speed up the selection?

May be my questions are very stupid... I am really new in database. :(

[con...@googlemail.com]

Hi Lukas,

Can you tell me, how can I use the ARRAY Type im my example rightly?

[Lukas Eder]

> Can you tell me, how can I use the ARRAY Type im my example rightly?

This is not an authoritative answer, but I think you should just use
Object[] instead of int[] (which is what Rami said, also). H2 only
supports Object[] arrays so far.

[Rami Ojares]

I think my suggestion was a bit hasty and you know data modeling can
only be done when you know the meaning and purpose of the concepts.
I think you want to represent an entity user.
For that entity create a table USER with a unique nonchanging id and
whatever attributes you want to store for a user (any user)

Then you want to store the information of user's friendships with each
other, right?

That is a many-to-many relationship between user and and another user.
Further the relationship is symmetric (well at least one would hope for
mutual feelings).

So I would come up with a new entity/concept/substantive FRIENDSHIP.

It would have columns
- FRIENDSHIP_ID (primary key, every table needs an identity)
- USER1 references USER table
- USER2 references USER table

Now you can for example define that a user can not be friend of himself.
CHECK(USER1 <> USER2)

You can define that do not store one friendship more than once
UNIQUE(USER1, USER2)

I don't think it is possible to disallow USER2 -> USER1 if you already
have USER1 -> USER2 ... bummer
Anyway you can be assured that all friendships stored are between
EXISTING users (no nullpointer exceptions).

- rami

> --
> You received this message because you are subscribed to the Google
> Groups "H2 Database" group.
> To view this discussion on the web visit

> https://groups.google.com/d/msg/h2-database/-/kNJ1EmbkyW0J.

[hilhe...@gmail.com]

Forvegive my poor English

You Shoud use the static methods in the Calss Array in java.lang.reflect package to access the object that contains the array

And the use the ObjectInputStream to complete the Convert;

for example :

Object objs = rs.getObject("arraycoloum");

    byte[] objBytes = Array.get(objs,i);

    Object  Obj = new (ObjectInputStream(new ByteArrayInputStream(objBytes))).readObject();

    (Any class defined or or orgfin) classobj = (Any Class defined or origin)Obj

 

在 2011年6月17日星期五UTC+8上午6时13分31秒，con...@googlemail.com写道：