inserting String[] into single column

fabio.fiol · **Joined:** Fri Dec 29, 2006 2:28 pm **Posts:** 11

Hibernate version: 3.1.3

What I'm trying to do:

I have to map a String[] containing about 70.000 strings into a single column of a Postgres database. It is part of the spectrum of a star. The other params are simple int or String.

Mapping documents:

Code:

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 3.0//EN" 
    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping
>
    <class
        name="popolamentodb.Spettro"
        table="SPECTRA"
    >

        <id
            name="id"
            column="id"
            type="java.lang.String"
        >
            <generator class="uuid.hex">
              <!--  
                  To add non XDoclet generator parameters, create a file named 
                  hibernate-generator-params-Spettro.xml 
                  containing the additional parameters and place it in your merge dir. 
              --> 
            </generator>
        </id>

        <property
            name="teff"
            type="java.lang.String"
            update="true"
            insert="true"
        >
            <column
                name="Teff" 
                not-null="true"
                sql-type="CHAR(5)"
            />
        </property>

        <property
            name="logg"
            type="int"
            update="true"
            insert="true"
        >
            <column
                name="Logg" 
                not-null="true"
                sql-type="SMALLINT"
            />
        </property>

        <property
            name="feh"
            type="java.lang.String"
            update="true"
            insert="true"
        >
            <column
                name="Fe_h" 
                not-null="true"
                sql-type="CHAR(4)"
            />
        </property>

        <property
            name="vrot"
            type="int"
            update="true"
            insert="true"
        >
            <column
                name="Vrot" 
                not-null="true"
                sql-type="SMALLINT"
            />
        </property>

        <property
            name="k"
            type="int"
            update="true"
            insert="true"
        >
            <column
                name="K" 
                not-null="true"
                sql-type="SMALLINT"
            />
        </property>

        <property
            name="alpha"
            type="java.lang.String"
            update="true"
            insert="true"
        >
            <column
                name="Alpha" 
                not-null="true"
                sql-type="CHAR(1)"
            />
        </property>

        <property
            name="odf"
            type="java.lang.String"
            update="true"
            insert="true"
        >
            <column
                name="Odf" 
                not-null="true"
                sql-type="CHAR(2)"
            />
        </property>

        <property
            name="res"
            type="java.lang.String"
            update="true"
            insert="true"
        >
            <column
                name="Res" 
                not-null="true"
                sql-type="CHAR(3)"
            />
        </property>

        <property
            name="tipo"
            type="java.lang.String"
            update="true"
            insert="true"
        >
            <column
                name="Type" 
                not-null="true"
                sql-type="CHAR(1)"
            />
        </property>

        <property
            name="data"
            type="popolamentodb.ListAsSQLArrayUserType$STRING"
            update="true"
            insert="true"
        >
            <column
                name="Data" 
                not-null="false"
                sql-type="text[]"
            />
        </property>

    </class>

</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

Code:

        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        
        session.beginTransaction();
        
        Spettro nuovo = new Spettro();
        nuovo.setTeff(Teff);
        nuovo.setLogg(Integer.parseInt(Logg.trim()));
        nuovo.setFeh(Fe_h);
        nuovo.setVrot(Integer.parseInt(VRot.trim()));
        nuovo.setK(Integer.parseInt(K.trim()));
        nuovo.setAlpha(Alpha);
        nuovo.setOdf(ODF);
        nuovo.setRes(Res);
        nuovo.setTipo(Tipo);
        nuovo.setData(dati);// HERE IS THE PROBLEM!!!
        
        session.save(nuovo);
        
        session.getTransaction().commit();

This icode uses HibernateUtil.java:

Code:

package popolamentodb;

import org.hibernate.*;
import org.hibernate.cfg.*;
import org.apache.log4j.Logger;

public class HibernateUtil {
    
    private static final SessionFactory sessionFactory;
    
    static {
        try {
            org.apache.log4j.BasicConfigurator.configure();
            Logger loggerRoot = Logger.getRootLogger();
            Logger loggerConnect = Logger.getLogger("Connect");

            loggerRoot.debug("In HibernateUtil try-clause");
            loggerRoot.error("In HibernateUtil try-clause");
            loggerConnect.debug("In HibernateUtil try-clause via loggerConnect DEBUG*****");

            // Create the SessionFactory from hibernate.cfg.xml
            sessionFactory = new Configuration().configure().buildSessionFactory();
        } catch (Throwable ex) {
            // Make sure you log the exception, as it might be swallowed
            System.err.println("Initial SessionFactory creation failed." + ex);
            throw new ExceptionInInitializerError(ex);
        }
    }
    
    public static SessionFactory getSessionFactory() {
        return sessionFactory;
    }
    
}

Part of the Spettro class:

Code:

    private ListAsSQLArrayUserType.STRING data;
    /**
     *   @hibernate.property
     *     column="Data"
     *     type="popolamentodb.ListAsSQLArrayUserType$STRING"
     *   @hibernate.column
     *     name="Data"
     *     sql-type="text[]"
     *     not-null="true"
     */
    public ListAsSQLArrayUserType.STRING getData() {
        return data;
    }
    public void setData(ListAsSQLArrayUserType.STRING data) {
        this.data = data;
    }

Name and version of the database you are using:

PostgreSQL 8.2

What I'm using:

ListAsSQLArrayUserType.java and SqlArray.java
from http://forum.hibernate.org/viewtopic.php?t=946973

Code:

batmat · **Posted:** Fri Dec 29, 2006 5:05 pm

Well, I've taken a very quick look at the code in the other thread. I'm a bit tired tonight, so I didn't have the courage to read it all. But imo, the code looks way too long for the purpose...

What is the exception you're having and why didn't try to store this field in a "bytea"? I just tried to store a String[] in such a field without specifying anything special, and relying on hibernate to create the schema, it worked without any problem.

I just put:

Code:

while spectra is in fact a String[].

Maybe bytea is too small for your needs? Though I can't find a max size here: http://www.postgresql.org/docs/8.0/stat ... inary.html

If you want so, I can post the small code I wrote to test your case.

fabio.fiol · **Joined:** Fri Dec 29, 2006 2:28 pm **Posts:** 11

Ciao Baptiste!

My very first code was:

Code:

    private String[] data;
    /**
     *   @hibernate.property
     *     column="Data"
     *     type="java.lang.String[]"
     *   @hibernate.column
     *     name="Data"
     *     sql-type="text[]"
     *     not-null="true"
     */
    public String[] getData() {
        return data;
    }
    public void setData(String[] data) {
        this.data = data;
    }

it compiles, but at runtime i get:

Code:

Initial SessionFactory creation failed.org.hibernate.MappingException: Could not determine type for: java.lang.String[], for columns: [org.hibernate.mapping.Column(Data)]
Exception in thread "main" java.lang.ExceptionInInitializerError
        at popolamentodb.HibernateUtil.<clinit>(HibernateUtil.java:40)
        at popolamentodb.Main.creaInserisciSpettro(Main.java:118)
        at popolamentodb.Main.sottoCartelle(Main.java:43)
        at popolamentodb.Main.sottoCartelle(Main.java:54)
        at popolamentodb.Main.sottoCartelle(Main.java:54)
        at popolamentodb.Main.sottoCartelle(Main.java:54)
        at popolamentodb.Main.main(Main.java:32)
Caused by: org.hibernate.MappingException: Could not determine type for: java.lang.String[], for columns: [org.hibernate.mapping.Column(Data)]
        at org.hibernate.mapping.SimpleValue.getType(SimpleValue.java:266)
        at org.hibernate.mapping.SimpleValue.isValid(SimpleValue.java:253)
        at org.hibernate.mapping.Property.isValid(Property.java:185)
        at org.hibernate.mapping.PersistentClass.validate(PersistentClass.java:395)
        at org.hibernate.mapping.RootClass.validate(RootClass.java:192)
        at org.hibernate.cfg.Configuration.validate(Configuration.java:984)
        at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1169)
        at popolamentodb.HibernateUtil.<clinit>(HibernateUtil.java:36)

I thought Hibernate couldn't natively map String[] into text[], so I searched and found the other topic i quoted above. I tried to use that UserType class, but I'm not able. I don't understand...
Please post your code :-)

batmat · **Posted:** Sat Dec 30, 2006 10:02 am

So now you use annotations for mapping. In your first post, you seemed to be using XML metatada. My test is using XML, I don't know what is your requirements precisely, but I tested with 100000. I assumed a length of 50 characters (I used the commons-lang library and its RandomStringUtils class to do my test quicklier, 'cause I'm lazy and commons-lang was already configured in my classpath :-p).

Here's what I have, you'll see it's quite err complicated :-D:
package fr.mipih.hibernatetests.customtypes;

Code:

public class Star {

    Integer id;
    String name;
    String[] spectra;

//getters, setters. 
}

Star.hbm.xml:

Code:

<class name="Star" >
   <id name="id" column="ID">
      <generator class="native"/>
   </id>
   
   <property name="name" />
   <property name="spectra">
      <column name="spectra" />
   </property>
</class>

I'm using HSQLDB. The following table was generated:

Code:

    create table Star (
        ID integer generated by default as identity (start with 1),
        name varchar(255),
        spectra varbinary(255),
        primary key (ID)
    )

On PostgreSQL, it would have created the following sql:

Code:

    create table Star (
        ID int4 not null,
        name varchar(255),
        spectra bytea,
        primary key (ID)
    )

You can use the following code to generate in the console all your SQL:

Code:

import org.hibernate.cfg.Configuration;
import org.hibernate.tool.hbm2ddl.SchemaExport;

public class CreateSchema {
    public static void main(String[] args) {
   Configuration cfg = new Configuration().configure();
   SchemaExport schemaExport = new SchemaExport(cfg);
   schemaExport.setFormat(true).create(true, false);
    }
}

I just changed the dialect to the Postgres one to give the above SQL.

And here's the testcase I wrote. It's not a correct one, since I assume the first declared test is ran before the second one, but hey I said that was quick and dirty :):

Code:

public class TestStar extends TestCase {

    public static void main(String[] args) {
   System.out.println(RandomStringUtils.random(10, "ABC"));
    }

    public static String[] generateSpectra(int number, int maxlength) {
   String[] res = new String[number];
   for (int i = 0; i < number; ++i) {
       res[i] = RandomStringUtils.random(maxlength, "abcABC");
   }
   return res;
    }

    public void testStore() {
   Star s = new Star();
   s.setName("my star");
   String[] spectra = generateSpectra(100000, 50);
   s.setSpectra(spectra);

   Session session = HibernateUtil.currentSession();
   session.beginTransaction();
   session.createSQLQuery("delete from Star").executeUpdate();
   session.flush();
   session.persist(s);
   session.getTransaction().commit();
    }

    public void testGet() {
   Session session = HibernateUtil.currentSession();
   session.beginTransaction();
   List<Star> stars = session.createCriteria(Star.class).list();
   assertTrue(stars.size() == 1);

   Star star = stars.get(0);

    System.out.println("Spectra:");
    for (String current : star.getSpectra()) {
    System.out.println(current);
    }
    }
}

Hope it will help you. Don't hesitate to ask for precisions or anything else.

batmat · **Posted:** Sat Dec 30, 2006 10:08 am

By the way, here I'm using the built-in SerializableType as Steve said in the thread you pointed out: http://forum.hibernate.org/viewtopic.ph ... 96#2258796

Quote:

Another option is to simply serialize the int array into the column which is supported by the built-in SerializableType

fabio.fiol · **Joined:** Fri Dec 29, 2006 2:28 pm **Posts:** 11

I beg your pardon, I'm a stupid guy...
I use neither Annotations nor XML, but NetBeans. When I created a project with the code you provided, I got the same error I got with mine:

Code:

C:\Documents and Settings\Fabio\Documenti\Projects\baptiste\extension\build.xml:27: Schema text failed: Could not determine type for: java.lang.String[], for columns: [org.hibernate.mapping.Column(spectra)]

batmat · **Posted:** Sun Dec 31, 2006 5:27 am

You are using annotations or XML metadata, no way you'd make work Hibernate without that. I guess you should read some documentation, read the tutorial in the reference documentation: http://www.hibernate.org/hib_docs/v3/re ... l-firstapp

Your answer show that you're missing some important things on Hibernate. I don't know what netbeans lets you do "automatically" with Hibernate, but I guess you're using a plugin that should generate the code... That's the big problem with plugins: when you it doesn't work, you're lost. IMO, Plugin should be used with parcimony and what it provides you should be studied carefully.

From what you say about the code I gave, I guess it's not being used at all. If I were you I'd start my app without that ant build file which is certainly not correct.

Use the things I provide, modify the hibernate.cfg.xml file, create a junit testcase a put the code I give. It works for me, it'll work for you. Obviously verify that the SQL I gave is ran once to create the right schema for the code.

fabio.fiol · **Joined:** Fri Dec 29, 2006 2:28 pm **Posts:** 11

Thanx Baptiste!

One last thing: can you mail me your files?

Thank you so much!

fabio.fiol (at) tin.it

batmat · **Posted:** Sun Jan 07, 2007 2:20 pm

done :-)

fabio.fiol · **Joined:** Fri Dec 29, 2006 2:28 pm **Posts:** 11

;-( it ain't arrived yet... try again please!
try even fabio.fiol (at) gmail.com
thanx

batmat · **Posted:** Sat Jan 13, 2007 6:33 pm

re-done :-)

bogdanmata · **Joined:** Tue Aug 18, 2009 5:29 am **Posts:** 1

Could you please help me with this code.

Code:

@Entity
@Table(name="media_tag", schema="test")
public class MediaWithTags {
   private Long id;
   private String name;
   private String[] tags;
//   private List<String> tags;
   
   @Id
   @Column(name="id")
   @SequenceGenerator(name="seq_media_tag", sequenceName="test.seq_media_tag")
   @GeneratedValue(generator="seq_media_tag", strategy=GenerationType.SEQUENCE)
   public Long getId() {
      return id;
   }
   public void setId(Long id) {
      this.id = id;
   }
   
   @Column(name="name")
   public String getName() {
      return name;
   }
   public void setName(String name) {
      this.name = name;
   }
   
   @Column(name="tags")
   @Target(value=String[].class)
   public String[] getTags() {
      return tags;
   }
   public void setTags(String[] tags) {
      this.tags = tags;
   }
//   public List<String> getTags() {
//      return tags;
//   }
//   public void setTags(List<String> tags) {
//      this.tags = tags;
//   }
}

The sql is

Code:

CREATE TABLE test.media_tag
(
  id bigint NOT NULL,
  "name" character varying(50),
  tags character varying(50)[],
  CONSTRAINT pk_media_tag PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

When I'm trying to do the insert I obtain the next stacktrace:

Exception in thread "main" org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:266)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:167)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:50)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1027)
at com.test.dao.impl.AbstractDaoImpl.commitTransaction(AbstractDaoImpl.java:23)
at com.test.main.Main.main(Main.java:65)
Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into test.media_tag (name, tags, id) values (m_iii, <stream of 220 bytes>, 4550) was aborted. Call getNextException to see the cause.
at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2531)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1344)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:343)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2668)
at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)
... 7 more