[Randall Svancara]

I am having a problem with hibernate and getting it to recognize a image column in sql server. Currently I am using my eclipse and I can not figure out if I am mapping the columns incorrectly or if this is a jdbc driver problem.

Any ideas, or help would be appreciated.

Thanks,

Hibernate Version: 3
Application Server: Tomcat 5.5.9 using DBCP
Database: Microsoft SQL Server using the JTDS driver version 1.1.x
IDE: MyEclipse Plugin with Eclipse

This is the main hibernate configuration file.

<?xml version=’1.0′ encoding=’UTF-8′?>
<!DOCTYPE hibernate-configuration PUBLIC
“-//Hibernate/Hibernate Configuration DTD 3.0//EN”
“http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd”&gt;

<!– Generated by MyEclipse Hibernate Tools. –>
<hibernate-configuration>

<session-factory>
<property name=”connection.datasource”>java:comp/env/jdbc/webrecruiter</property>
<property name=”dialect”>org.hibernate.dialect.SQLServerDialect</property>
<property name=”show_sql”>true</property>

<property name=”generate_statistics”>true</property><mapping resource=”org/afraid/linuxspazz/webrecruiter/hibernate/TblAccount.hbm.xml”></mapping><mapping resource=”org/afraid/linuxspazz/webrecruiter/hibernate/TblResume.hbm.xml” />

</session-factory>

</hibernate-configuration>

This is the parent account table. It has a child table to store resumes in an image field.

<?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&#8221; >

<!– DO NOT EDIT: This is a generated file that is synchronized –>
<!– by MyEclipse Hibernate tool integration. –>
<!– Created Mon Nov 07 21:54:49 MST 2005 –>
<hibernate-mapping package=”org.afraid.linuxspazz.webrecruiter.hibernate”>

<class name=”TblAccount” table=”TBL_Account”>
<id name=”accountid” column=”accountid” type=”integer”>
<generator class=”identity”/>
</id>

<property name=”username” column=”username” type=”string” />
<property name=”password” column=”password” type=”string” />
<property name=”firstname” column=”firstname” type=”string” />
<property name=”lastname” column=”lastname” type=”string” />
<property name=”middlename” column=”middlename” type=”string” />
<property name=”createdate” column=”createdate” type=”timestamp” />
<property name=”updatedate” column=”updatedate” type=”timestamp” />
<property name=”accessdate” column=”accessdate” type=”timestamp” />
<property name=”address1″ column=”address1″ type=”string” />
<property name=”address2″ column=”address2″ type=”string” />
<property name=”city” column=”city” type=”string” />
<property name=”state” column=”state” type=”string” />
<property name=”zipcode” column=”zipcode” type=”string” />
<property name=”country” column=”country” type=”string” />
<property name=”relocate” column=”relocate” type=”string” />
<property name=”salary” column=”salary” type=”double” />
<property name=”incrementtype” column=”incrementtype” type=”string” />
<property name=”relocatedescription” column=”relocatedescription” type=”string” />
<property name=”accounttype” column=”accounttype” type=”string” />
<property name=”hireddescription” column=”hireddescription” type=”string” />
<property name=”hired” column=”hired” type=”string” />
<property name=”lastvieweddatebyrecruiter” column=”lastvieweddatebyrecruiter” type=”timestamp” />
<property name=”lastviewedrecruiterid” column=”lastviewedrecruiterid” type=”integer” />
<property name=”viewcount” column=”viewcount” type=”integer” />
<property name=”firm” column=”firm” type=”string” />
<property name=”jobposition” column=”jobposition” type=”string” />
<property name=”bonus” column=”bonus” type=”double” />
<property name=”followupdate” column=”followupdate” type=”timestamp” />
<property name=”bonustype” column=”bonustype” type=”string” />
<property name=”reviewdate” column=”reviewdate” type=”timestamp” />
<property name=”accountstatus” column=”accountstatus” type=”string” />
<property name=”createdrecruiterid” column=”createdrecruiterid” type=”integer” />
<property name=”lastupdaterecruiterid” column=”lastupdaterecruiterid” type=”integer” />
<property name=”citizen” column=”citizen” type=”string” />
<property name=”visa” column=”visa” type=”string” />
<property name=”qsent” column=”qsent” type=”string” />
<property name=”qsentdatetime” column=”qsentdatetime” type=”timestamp” />
<property name=”qreceived” column=”qreceived” type=”string” />
<property name=”qreceiveddatetime” column=”qreceiveddatetime” type=”timestamp” />

<!–
<set name=”tblAccountJobrequestSet” inverse=”true”>
<key column=”accountid”/>
<one-to-many class=”TblAccountJobrequest”/>
</set>

<set name=”tblAccountnotesSet” inverse=”true”>
<key column=”accountid”/>
<one-to-many class=”TblAccountnotes”/>
</set>

<set name=”tblEducationSet” inverse=”true”>
<key column=”account_id”/>
<one-to-many class=”TblEducation”/>
</set>

<set name=”tblEmailSet” inverse=”true”>
<key column=”accountid”/>
<one-to-many class=”TblEmail”/>
</set>

<set name=”tblPhoneSet” inverse=”true”>
<key column=”accountid”/>
<one-to-many class=”TblPhone”/>
</set>
–>
<set name=”tblResumeSet” inverse=”true”>
<key column=”accountid”/>
<one-to-many class=”TblResume”/>
</set>
<!–
<set name=”tblSentemailSet” inverse=”true”>
<key column=”accountid”/>
<one-to-many class=”TblSentemail”/>
</set>
–>
</class>

</hibernate-mapping>

Resume table mapping configuration. Notice that resume is a blob type

<?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&#8221; >

<!– DO NOT EDIT: This is a generated file that is synchronized –>
<!– by MyEclipse Hibernate tool integration. –>
<!– Created Tue Nov 08 06:20:59 MST 2005 –>
<hibernate-mapping package=”org.afraid.linuxspazz.webrecruiter.hibernate”>

<class name=”TblResume” table=”TBL_Resume”>
<id name=”resumeId” column=”resume_id” type=”integer”>
<generator class=”identity”/>
</id>

<property name=”resume” column=”resume” type=”blob” />
<property name=”filename” column=”filename” type=”string” />
<property name=”contenttype” column=”contenttype” type=”string” />
<property name=”doctype” column=”doctype” type=”string” />
<property name=”createdate” column=”createdate” type=”timestamp” not-null=”true” />

<many-to-one name=”tblAccount” column=”accountid” class=”TblAccount” />
</class>

</hibernate-mapping>

The POJO i am mapping to:

/*
* WARNING: DO NOT EDIT THIS FILE. This is a generated file that is synchronized
* by MyEclipse Hibernate tool integration.
*
* Created Tue Nov 08 06:20:59 MST 2005 by MyEclipse Hibernate Tool.
*/
package org.afraid.linuxspazz.webrecruiter.hibernate;

import java.io.Serializable;

/**
* A class that represents a row in the TBL_Resume table.
* You can customize the behavior of this class by editing the class, {@link TblResume()}.
* WARNING: DO NOT EDIT THIS FILE. This is a generated file that is synchronized
* by MyEclipse Hibernate tool integration.
*/
public abstract class AbstractTblResume
implements Serializable
{
/** The cached hash code value for this instance. Settting to 0 triggers re-calculation. */
private int hashValue = 0;

/** The composite primary key value. */
private java.lang.Integer resumeId;

/** The value of the tblAccount association. */
private TblAccount tblAccount;

/** The value of the simple resume property. */
private java.lang.String resume;

/** The value of the simple filename property. */
private java.lang.String filename;

/** The value of the simple contenttype property. */
private java.lang.String contenttype;

/** The value of the simple doctype property. */
private java.lang.String doctype;

/** The value of the simple createdate property. */
private java.util.Date createdate;

/**
* Simple constructor of AbstractTblResume instances.
*/
public AbstractTblResume()
{
}

/**
* Constructor of AbstractTblResume instances given a simple primary key.
* @param resumeId
*/
public AbstractTblResume(java.lang.Integer resumeId)
{
this.setResumeId(resumeId);
}

/**
* Return the simple primary key value that identifies this object.
* @return java.lang.Integer
*/
public java.lang.Integer getResumeId()
{
return resumeId;
}

/**
* Set the simple primary key value that identifies this object.
* @param resumeId
*/
public void setResumeId(java.lang.Integer resumeId)
{
this.hashValue = 0;
this.resumeId = resumeId;
}

/**
* Return the value of the resume column.
* @return java.lang.String
*/
public java.lang.String getResume()
{
return this.resume;
}

/**
* Set the value of the resume column.
* @param resume
*/
public void setResume(java.lang.String resume)
{
this.resume = resume;
}

/**
* Return the value of the filename column.
* @return java.lang.String
*/
public java.lang.String getFilename()
{
return this.filename;
}

/**
* Set the value of the filename column.
* @param filename
*/
public void setFilename(java.lang.String filename)
{
this.filename = filename;
}

/**
* Return the value of the contenttype column.
* @return java.lang.String
*/
public java.lang.String getContenttype()
{
return this.contenttype;
}

/**
* Set the value of the contenttype column.
* @param contenttype
*/
public void setContenttype(java.lang.String contenttype)
{
this.contenttype = contenttype;
}

/**
* Return the value of the accountid column.
* @return TblAccount
*/
public TblAccount getTblAccount()
{
return this.tblAccount;
}

/**
* Set the value of the accountid column.
* @param tblAccount
*/
public void setTblAccount(TblAccount tblAccount)
{
this.tblAccount = tblAccount;
}

/**
* Return the value of the doctype column.
* @return java.lang.String
*/
public java.lang.String getDoctype()
{
return this.doctype;
}

/**
* Set the value of the doctype column.
* @param doctype
*/
public void setDoctype(java.lang.String doctype)
{
this.doctype = doctype;
}

/**
* Return the value of the createdate column.
* @return java.util.Date
*/
public java.util.Date getCreatedate()
{
return this.createdate;
}

/**
* Set the value of the createdate column.
* @param createdate
*/
public void setCreatedate(java.util.Date createdate)
{
this.createdate = createdate;
}

/**
* Implementation of the equals comparison on the basis of equality of the primary key values.
* @param rhs
* @return boolean
*/
public boolean equals(Object rhs)
{
if (rhs == null)
return false;
if (! (rhs instanceof TblResume))
return false;
TblResume that = (TblResume) rhs;
if (this.getResumeId() == null || that.getResumeId() == null)
return false;
return (this.getResumeId().equals(that.getResumeId()));
}

/**
* Implementation of the hashCode method conforming to the Bloch pattern with
* the exception of array properties (these are very unlikely primary key types).
* @return int
*/
public int hashCode()
{
if (this.hashValue == 0)
{
int result = 17;
int resumeIdValue = this.getResumeId() == null ? 0 : this.getResumeId().hashCode();
result = result * 37 + resumeIdValue;
this.hashValue = result;
}
return this.hashValue;
}
}

And finally the error I am receiving when hibernate tries to load the child table

[2005-11-09 07:45:41,484] http-8080-2 org.hibernate.util.JDBCExceptionReporter DEBUG – could not initialize a collection: [org.afraid.linuxspazz.webrecruiter.hibernate.TblAccount.tblResumeSet#246] [select tblresumes0_.accountid as accountid1_, tblresumes0_.resume_id as resume1_1_, tblresumes0_.resume_id as resume1_0_, tblresumes0_.filename as filename1_0_, tblresumes0_.contenttype as contentt4_1_0_, tblresumes0_.doctype as doctype1_0_, tblresumes0_.createdate as createdate1_0_, tblresumes0_.accountid as accountid1_0_ from TBL_Resume tblresumes0_ where tblresumes0_.accountid=?]
java.sql.SQLException: Unable to convert between java.lang.Integer and BLOB.
at net.sourceforge.jtds.jdbc.Support.convert(Support.java:558)
at net.sourceforge.jtds.jdbc.JtdsResultSet.getBlob(JtdsResultSet.java:1071)
at net.sourceforge.jtds.jdbc.JtdsResultSet.getBlob(JtdsResultSet.java:1239)
at org.apache.tomcat.dbcp.dbcp.DelegatingResultSet.getBlob(DelegatingResultSet.java:526)
at org.hibernate.type.BlobType.get(BlobType.java:56)
at org.hibernate.type.BlobType.nullSafeGet(BlobType.java:110)
at org.hibernate.type.AbstractType.hydrate(AbstractType.java:80)
at org.hibernate.persister.entity.BasicEntityPersister.hydrate(BasicEntityPersister.java:1690)
at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:991)
at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:942)
at org.hibernate.loader.Loader.getRow(Loader.java:855)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:305)
at org.hibernate.loader.Loader.doQuery(Loader.java:412)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
at org.hibernate.loader.Loader.loadCollection(Loader.java:1434)
at org.hibernate.loader.collection.OneToManyLoader.initialize(OneToManyLoader.java:111)
at org.hibernate.persister.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:488)
at org.hibernate.event.def.DefaultInitializeCollectionEventListener.onInitializeCollection(DefaultInitializeCollectionEventListener.java:60)
at org.hibernate.impl.SessionImpl.initializeCollection(SessionImpl.java:1430)
at org.hibernate.collection.AbstractPersistentCollection.initialize(AbstractPersistentCollection.java:176)
at org.hibernate.collection.AbstractPersistentCollection.read(AbstractPersistentCollection.java:48)
at org.hibernate.collection.PersistentSet.size(PersistentSet.java:110)
at org.afraid.linuxspazz.webrecruiter.security.SecurityService.IsAuthenticated(SecurityService.java:87)
at org.afraid.linuxspazz.webrecruiter.struts.action.CandidateLoginAction.execute(CandidateLoginAction.java:53)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:419)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:224)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1194)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11AprProcessor.process(Http11AprProcessor.java:831)
at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:652)
at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:1203)
at java.lang.Thread.run(Thread.java:595)
[2005-11-09 07:45:41,484] http-8080-2 org.hibernate.util.JDBCExceptionReporter WARN – SQL Error: 0, SQLState: 22005
[2005-11-09 07:45:41,484] http-8080-2 org.hibernate.util.JDBCExceptionReporter ERROR – Unable to convert between java.lang.Integer and BLOB.
[2005-11-09 07:45:41,484] http-8080-2 org.afraid.linuxspazz.webrecruiter.security.SecurityService DEBUG – Exception in IsAuthenticated method: org.hibernate.exception.GenericJDBCException: could not initialize a collection: [org.afraid.linuxspazz.webrecruiter.hibernate.TblAccount.tblResumeSet#246]
[2005-11-09 07:45:41,484] http-8080-2 org.hibernate.impl.SessionImpl DEBUG – closing session
[/code]

[Riyad Kalla]

It seems strange that it’s complaining about the Integer column being converted since the resume is generated to a String… I wonder if the error is talking about the key? Can you post your c-reate table scripts for those two tables for us? PLEASE NOTE, you will need to break the SQL keywords up, we have a security script that will stop you from posting straight SQL.

[Randall Svancara]

C-R-E-A-T-E T-A-B-L-E [TBL_Account] (
    [accountid] [int] IDENTITY (7000, 1) NOT NULL ,
    [username] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [password] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [firstname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [lastname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [middlename] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [createdate] [datetime] NULL ,
    [updatedate] [datetime] NULL CONSTRAINT [DF_TBL_Account_updatedate] DEFAULT (getdate()),
    [accessdate] [datetime] NULL ,
    [address1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [address2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [city] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [state] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [zipcode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [country] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [relocate] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [salary] [decimal](12, 2) NULL ,
    [incrementtype] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [relocatedescription] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [accounttype] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [hireddescription] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [hired] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [lastvieweddatebyrecruiter] [datetime] NULL ,
    [lastviewedrecruiterid] [int] NULL ,
    [viewcount] [int] NULL ,
    [firm] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [jobposition] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [bonus] [decimal](12, 2) NULL ,
    [followupdate] [datetime] NULL ,
    [bonustype] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [reviewdate] [datetime] NULL ,
    [accountstatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [createdrecruiterid] [int] NULL ,
    [lastupdaterecruiterid] [int] NULL ,
    [citizen] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [visa] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [qsent] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [qsentdatetime] [datetime] NULL ,
    [qreceived] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [qreceiveddatetime] [datetime] NULL ,
    CONSTRAINT [PK_TBL_Account] PRIMARY KEY  CLUSTERED 
    (
        [accountid]
    )  ON [PRIMARY] 
) ON [PRIMARY]
GO

C-R-E-A-T-E T-A-B-L-E [TBL_Resume] (
    [resume_id] [int] IDENTITY (1, 1) NOT NULL ,
    [resumex] [image] NULL ,
    [filename] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [contenttype] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [accountid] [int] NULL ,
    [doctype] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [createdate] AS (getdate()) ,
    [test] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    CONSTRAINT [PK_TBL_Resume] PRIMARY KEY  CLUSTERED 
    (
        [resume_id]
    )  ON [PRIMARY] ,
    CONSTRAINT [FK_TBL_Resume_TBL_Account] FOREIGN KEY 
    (
        [accountid]
    ) REFERENCES [TBL_Account] (
        [accountid]
    ) ON DELETE CASCADE  ON UPDATE CASCADE 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

[Riyad Kalla]

Thank you, in the mean time can you manually change the generated file to avoid the exception? We are replacing the mapping generation in our 4.1M2 release in the commings weeks, hopefully it will cover this issue.

[Randall Svancara]

Just so you know what I had to do to make this work!!!

I had to upgrade to jtds driver version 1.2 and in my connection URL for my tomcat dbcp configuration, I had to use

useLOBs=false

Please the JTDS FAQ on this. It explains when to use this feature and when not to in a vague sort of way.

Here is a link to a post that I had on the hibernate forum
http://forum.hibernate.org/viewtopic.php?t=949898&start=0&postdays=0&postorder=asc&highlight=&sid=21d8288ec020e826b186b9a019ca0d72

Of course I willing to try any additional solutions to resolve this problem

[Randall Svancara]

Thanks again. I always look forward to new releases from MyEclipse.

[Author]

Posts