Hi all,
When I ran the sql query generated by Hibernate at MySQL Command Line Client, I got 1 row of result.. But not in my app that uses Hibernate..
Then I wrote a simple Java App to connect to the DB directly. If I used PreparedStatement to execute the query, I didn't get any row, but when I used Statement to execute the query, it returned me with 1 row of result.
Here is the code that I wrote to execute the query:
Code:
public static void main(String[] args) throws Exception {
Connection result = null;
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
}
catch (Exception ex){
System.err.println("Check classpath. Cannot load db driver: com.mysql.jdbc.Driver");
}
try {
result = DriverManager.getConnection("jdbc:mysql://127.0.0.1/zzz", "xxx", "yyy");
}
catch (SQLException e){
System.err.println( "Driver loaded, but cannot connect to db: " + "jdbc:mysql://127.0.0.1/zzz");
}
String sql ="select provider0_.ID as ID0_, case when provider0__2_.ID is not null then 1 " +
"when provider0_.ID is not null then 0 end as clazz_0_, provider0__1_.VERSION as "+
"VERSION6_0_, provider0__1_.USERNAME as USERNAME6_0_, provider0__1_.PASSWORD as PASSWORD6_0_, provider0__2_.ADDRESS as ADDRESS8_0_, provider0__2_.EMAIL as EMAIL8_0_, provider0__2_.FULLNAME as FULLNAME8_0_, provider0__2_.PHONE as PHONE8_0_ from PROVIDER provider0_ inner join USER provider0__1_ on provider0_.ID=provider0__1_.ID left outer join PODCAST_PROVIDER provider0__2_ on provider0_.ID=provider0__2_.ID where provider0_.ID=?";
System.out.println("PreparedStatement");
PreparedStatement pstmt = result.prepareStatement(sql);
pstmt.setLong(1,3);
System.out.println("pstmt["+pstmt+"]");
ResultSet rs = pstmt.executeQuery();
while(rs.next()) {
System.out.println("YES");
}
String anotherSql = "select provider0_.ID as ID0_, case when provider0__2_.ID is not null then 1 when provider0_.ID is not null then 0 end as clazz_0_, provider0__1_.VERSION as VERSION6_0_, provider0__1_.USERNAME as USERNAME6_0_, provider0__1_.PASSWORD as PASSWORD6_0_, provider0__2_.ADDRESS as ADDRESS8_0_, provider0__2_.EMAIL as EMAIL8_0_, provider0__2_.FULLNAME as FULLNAME8_0_, provider0__2_.PHONE as PHONE8_0_ from PROVIDER provider0_ inner join USER provider0__1_ on provider0_.ID=provider0__1_.ID left outer join PODCAST_PROVIDER provider0__2_ on provider0_.ID=provider0__2_.ID where provider0_.ID=3";
System.out.println("End pstmt");
System.out.println("Statement");
Statement stmt = result.createStatement();
String newSQL = sql.substring(0,sql.length()-1);
newSQL +=3;
rs = stmt.executeQuery(newSQL);
while(rs.next()) {
System.out.println("YES2");
}
System.out.println("End stmt");
System.out.println("New Statement");
stmt = result.createStatement();
rs = stmt.executeQuery(anotherSql);
while(rs.next()) {
System.out.println("YES3");
}
}
The following is the result of running the above code :
Code:
PreparedStatement
pstmt[com.mysql.jdbc.ServerPreparedStatement[1] - select provider0_.ID as ID0_, case when provider0__2_.ID is not null then 1 when provider0_.ID is not null then 0 end as clazz_0_, provider0__1_.VERSION as VERSION6_0_, provider0__1_.USERNAME as USERNAME6_0_, provider0__1_.PASSWORD as PASSWORD6_0_, provider0__2_.ADDRESS as ADDRESS8_0_, provider0__2_.EMAIL as EMAIL8_0_, provider0__2_.FULLNAME as FULLNAME8_0_, provider0__2_.PHONE as PHONE8_0_ from PROVIDER provider0_ inner join USER provider0__1_ on provider0_.ID=provider0__1_.ID left outer join PODCAST_PROVIDER provider0__2_ on provider0_.ID=provider0__2_.ID where provider0_.ID=3]
End pstmt
Statement
YES2
End stmt
New Statement
YES3
Appreciate any advice you can give on this matter. Thank you in advance
Best Regards,
Antonius Ng
Hibernate version: 2.1.8
Mapping documents:<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping
>
<class
name="com.n2n.user.User"
table="USER"
>
<id
name="id"
column="ID"
type="long"
unsaved-value="null"
>
<generator class="native">
</generator>
</id>
<version
name="version"
column="VERSION"
type="int"
/>
<property
name="username"
type="java.lang.String"
update="true"
insert="true"
column="USERNAME"
length="16"
not-null="true"
unique="true"
/>
<property
name="password"
type="java.lang.String"
update="true"
insert="true"
column="PASSWORD"
length="16"
not-null="true"
/>
<set
name="roles"
table="USER_ROLE"
lazy="true"
cascade="none"
sort="unsorted"
>
<key
column="USER_ID"
>
</key>
<many-to-many
class="com.n2n.user.Role"
column="ROLE_ID"
outer-join="auto"
/>
</set>
<!--
To add non XDoclet property mappings, create a file named
hibernate-properties-User.xml
containing the additional properties and place it in your merge dir.
-->
<joined-subclass
name="com.n2n.cms.user.Provider"
table="PROVIDER"
>
<key
column="ID"
/>
<set
name="contents"
lazy="true"
inverse="true"
cascade="all-delete-orphan"
sort="unsorted"
>
<key
column="PROVIDER_ID"
>
</key>
<one-to-many
class="com.n2n.cms.content.Content"
/>
</set>
<joined-subclass
name="com.n2n.cms.podcast.user.PodcastProvider"
table="PODCAST_PROVIDER"
>
<key
column="ID"
/>
<property
name="address"
type="java.lang.String"
update="true"
insert="true"
column="ADDRESS"
not-null="false"
/>
<property
name="email"
type="java.lang.String"
update="true"
insert="true"
column="EMAIL"
not-null="true"
/>
<property
name="fullname"
type="java.lang.String"
update="true"
insert="true"
column="FULLNAME"
not-null="true"
/>
<property
name="phone"
type="java.lang.String"
update="true"
insert="true"
column="PHONE"
not-null="true"
/>
</joined-subclass>
</joined-subclass>
</class>
</hibernate-mapping>
Code between sessionFactory.openSession() and session.close():Code:
String queryString="from Channel channel where channel.provider.id = :provider";
Map params = new HashMap();
params.put("provider",provider.getId());
try {
Query query = session.createQuery(queryString);
Iterator keys = params.keySet().iterator();
while(keys.hasNext()){
String key = (String) keys.next();
Object param = params.get(key);
if(param instanceof Collection)
query.setParameterList(key, (Collection) param);
else
query.setParameter(key, param);
}
ls = query.list();
Full stack trace of any exception that occurs:
Caused by: net.sf.hibernate.UnresolvableObjectException: No row with the given i
dentifier exists: 3, of class: com.n2n.cms.user.Provider
at net.sf.hibernate.UnresolvableObjectException.throwIfNull(Unresolvable
ObjectException.java:38)
at net.sf.hibernate.impl.SessionImpl.internalLoad(SessionImpl.java:1964)
at net.sf.hibernate.type.ManyToOneType.resolveIdentifier(ManyToOneType.j
ava:69)
at net.sf.hibernate.type.EntityType.resolveIdentifier(EntityType.java:20
8)
at net.sf.hibernate.impl.SessionImpl.initializeEntity(SessionImpl.java:2
222)
at net.sf.hibernate.loader.Loader.initializeEntitiesAndCollections(Loade
r.java:326)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:316)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections
(Loader.java:141)
at net.sf.hibernate.loader.Loader.doList(Loader.java:1097)
at net.sf.hibernate.loader.Loader.list(Loader.java:1088)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:854)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1554)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:49)
at com.n2n.entity.hibernate.HibernateEntityDAO.useQuery(HibernateEntityD
AO.java:315)
... 31 more
Name and version of the database you are using:
MySQL 4.1
The generated SQL (show_sql=true):
select provider0_.ID as ID0_, case when provider0__2_.ID is not null then 1 when
provider0_.ID is not null then 0 end as clazz_0_, provider0__1_.VERSION
as VERSION6_0_, provider0__1_.USERNAME as USERNAME6_0_, provider0__1_.PASSWORD
as PASSWORD6_0_, provider0__2_.ADDRESS as ADDRESS8_0_, provider0__2_.EMAIL as
EMAIL8_0_, provider0__2_.FULLNAME as FULLNAME8_0_, provider0__2_.PHONE as
PHONE8_0_ from PROVIDER provider0_ inner join USER provider0__1_ on
provider0_.ID=provider0__1_.ID left outer join PODCAST_PROVIDER provider0__2_ on
provider0_.ID=provider0__2_.ID where provider0_.ID=?;
Debug level Hibernate log excerpt:
Login
FAQ
Search
