I am creating a web based application for movie rentals using Hibernate with MySQL. I have finished with the user creation part. I am now working on the rental transaction.
The user and a movie has a many-to-many association. For this, I have updated the schema as below:
Code:
CREATE TABLE `movies` (
`movieID` int(11) NOT NULL AUTO_INCREMENT,
`movieName` varchar(45) NOT NULL,
`releaseYear` varchar(4) DEFAULT NULL,
`genre` varchar(100) NOT NULL,
`rating` tinyint(2) DEFAULT NULL,
`nCopies` int(10) unsigned DEFAULT '50',
`imageUri` varchar(50) NOT NULL,
`plotLine` varchar(1000) DEFAULT NULL,
PRIMARY KEY (`movieID`),
KEY `movieName_idx` (`movieName`)
)
CREATE TABLE `user_details` (
`registrationID` int(11) NOT NULL,
`userID` varchar(15) NOT NULL,
`password` varchar(80) NOT NULL,
`firstName` varchar(15) DEFAULT NULL,
`lastName` varchar(20) DEFAULT NULL,
`email` varchar(45) DEFAULT NULL,
PRIMARY KEY (`registrationID`),
UNIQUE KEY `registrationID_UNIQUE` (`registrationID`),
UNIQUE KEY `userID_UNIQUE` (`userID`),
KEY `idx_userID` (`userID`)
)
CREATE TABLE `user_movies` (
`userRegistrationID` int(11) NOT NULL,
`bookedMovieID` int(11) NOT NULL,
`bookingDate` date DEFAULT NULL,
PRIMARY KEY (`userRegistrationID`,`bookedMovieID`),
KEY `fk_MOVIE_BOOKERS_idx` (`bookedMovieID`),
KEY `fk_USER_MOVIES_idx` (`userRegistrationID`),
CONSTRAINT `fk_MOVIE_BOOKERS` FOREIGN KEY (`bookedMovieID`) REFERENCES `movies` (`movieID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_USER_MOVIES` FOREIGN KEY (`userRegistrationID`) REFERENCES `user_details` (`registrationID`) ON DELETE CASCADE ON UPDATE CASCADE
)
Note that records already exist in the USER_DETAILS and MOVIES tableNow I require an additional property in the association table -
bookingDate. To attain the same
using XML mapping, I have followed the method as described here :
Code:
https://forum.hibernate.org/viewtopic.php?p=2451262
Following are my entity classes:
Code:
public class User implements Serializable
{
private int registrationID;
private String userID;
private transient String password;
private String firstName;
private String lastName;
private String email;
private String encrPass;
private Set<Rental> rentals;
}
public class Movie implements Serializable
{
private int movieID;
private String movieName;
private String releaseYear;
private String genre;
private int rating;
private int nCopies;
private String imageUri;
private String plotLine;
}
public class Rental implements Serializable
{
private Movie movie;
private Date bookingDate;
}
The corresponding mapping files are as below:
Code:
<hibernate-mapping package="com.clixflix.enitities">
<class name="User" table="USER_DETAILS">
<id column="registrationID" name="registrationID" type="integer" unsaved-value="null">
<generator class="com.clixflix.utils.EntityIDGenerator" />
</id>
<property name="userID" column="userID" type="string" not-null="true" length="15" />
<property name="encrPass" column="password" type="string" not-null="true" />
<property name="firstName" column="firstName" type="string" not-null="true" length="15" />
<property name="lastName" column="lastName" type="string" length="20" />
<property name="email" column="email" type="string" length="45" />
<!-- Many-to-many mapping with extra columns -->
<set name="rentals" cascade="all" lazy="true" table="USER_MOVIES">
<key column="userRegistrationID" not-null="true"/>
<composite-element class="Rental">
<property name="bookingDate" column="bookingDate" type="date"/>
<many-to-one name="movie" class="Movie" not-null="true"/>
</composite-element>
</set>
</class>
</hibernate-mapping>
Code:
<hibernate-mapping package="com.clixflix.enitities">
<class name="Movie" table="MOVIES">
<id name="movieID" column="movieID" type="integer">
<generator class="native" />
</id>
<property name="movieName" column="movieName" type="string" not-null="true" length="45" />
<property name="releaseYear" column="releaseYear" type="string" length="4" />
<property name="genre" column="genre" type="string" not-null="true" length="100" />
<property name="rating" column="rating" type="integer" />
<property name="nCopies" column="nCopies" type="integer" />
<property name="imageUri" column="imageUri" type="string" not-null="true" length="50" />
<property name="plotLine" column="plotLine" type="string" length="100" />
</class>
</hibernate-mapping>
Now my standalone class to test the
update functionality is as below:
Code:
package com.test;
import java.util.Date;
import java.util.Set;
import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.hibernate.criterion.Restrictions;
import com.clixflix.enitities.Movie;
import com.clixflix.enitities.Rental;
import com.clixflix.enitities.User;
public class Main
{
private static SessionFactory factory;
@SuppressWarnings("deprecation")
public static void main(String[] args)
{
try {
factory = new Configuration().configure().buildSessionFactory();
}
catch (Throwable ex) {
System.err.println("Failed to create sessionFactory object." + ex);
throw new ExceptionInInitializerError(ex);
}
Session session = factory.getCurrentSession();
Transaction tx = null;
try {
tx = session.beginTransaction();
System.out.println("########## Get the movie #########");
Criteria cr = session.createCriteria(Movie.class);
cr.add(Restrictions.eq("movieName", "300"));
Movie movie = (Movie) cr.uniqueResult();
System.out.println("Movie : " + movie);
System.out.println("########## Make a rental #########");
Rental rental = new Rental();
rental.setMovie(movie);
rental.setBookingDate(new Date());
System.out.println("########## Get user #########");
Criteria cri = session.createCriteria(User.class);
cri.add(Restrictions.eq("registrationID", 1356889778));
User user = (User) cri.uniqueResult();
System.out.println("User : " + user);
Set<Rental> rentals = user.getRentals();
rentals.add(rental);
user.setRentals(rentals);
session.update(user);
tx.commit();
}
catch (Exception e)
{
if (tx != null) {
tx.rollback();
}
e.printStackTrace();
}
finally {
session.close();
}
}
}
However, I get an "unknown column in field list" exception for the movie property in Rental class.
Below is the sysout with the exception:
Code:
########## Get the movie #########
Hibernate:
select
this_.movieID as movieID0_0_,
this_.movieName as movieName0_0_,
this_.releaseYear as releaseY3_0_0_,
this_.genre as genre0_0_,
this_.rating as rating0_0_,
this_.nCopies as nCopies0_0_,
this_.imageUri as imageUri0_0_,
this_.plotLine as plotLine0_0_
from
MOVIES this_
where
this_.movieName=?
Movie : Movie [300]
########## Make a rental #########
########## Get user #########
Hibernate:
select
this_.registrationID as registra1_1_0_,
this_.userID as userID1_0_,
this_.password as password1_0_,
this_.firstName as firstName1_0_,
this_.lastName as lastName1_0_,
this_.email as email1_0_
from
USER_DETAILS this_
where
this_.registrationID=?
User : User [admin]
Hibernate:
select
rentals0_.userRegistrationID as userRegi1_1_0_,
rentals0_.bookingDate as bookingD2_2_0_,
rentals0_.movie as movie2_0_
from
USER_MOVIES rentals0_
where
rentals0_.userRegistrationID=?
Feb 21, 2013 11:29:24 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
WARN: SQL Error: 1054, SQLState: 42S22
Feb 21, 2013 11:29:24 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: Unknown column 'rentals0_.movie' in 'field list'
org.hibernate.exception.SQLGrammarException: Unknown column 'rentals0_.movie' in 'field list'
at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:82)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:129)
at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
at $Proxy6.executeQuery(Unknown Source)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1926)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1727)
at org.hibernate.loader.Loader.doQuery(Loader.java:852)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:293)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:263)
at org.hibernate.loader.Loader.loadCollection(Loader.java:2123)
at org.hibernate.loader.collection.CollectionLoader.initialize(CollectionLoader.java:61)
at org.hibernate.persister.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:678)
at org.hibernate.event.internal.DefaultInitializeCollectionEventListener.onInitializeCollection(DefaultInitializeCollectionEventListener.java:80)
at org.hibernate.internal.SessionImpl.initializeCollection(SessionImpl.java:1804)
at org.hibernate.collection.internal.AbstractPersistentCollection$4.doWork(AbstractPersistentCollection.java:549)
at org.hibernate.collection.internal.AbstractPersistentCollection.withTemporarySessionIfNeeded(AbstractPersistentCollection.java:234)
at org.hibernate.collection.internal.AbstractPersistentCollection.initialize(AbstractPersistentCollection.java:545)
at org.hibernate.collection.internal.PersistentSet.add(PersistentSet.java:206)
at com.test.Main.main(Main.java:56)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'rentals0_.movie' in 'field list'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1053)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4096)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4028)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2734)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2322)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122)
... 17 more
Could someone please assist with the same ???
Login
FAQ
Search