Database Choice with hibernate

[Scott Stephens]

I have been spending the last few months trying to learn JSF. Because applications always use data, the choice for an appropriate database was the next issue I was confronted with.
I develop with eclipse, Tomcat 6, myFaces and tried my first project using HSQLDB. Right now I am starting to finally starting to understand some of the concepts and have been programming a practice project with an Apache Derby Database. I really like use an identity ID so that I don't have to worry about incrementing the ID field. But Hibernate doesn't suppport identity fields in Derby(?). My purpose is to learn JSF and the dependent technologies. It's not about a huge RDBMS or something highly performant.

My question is: for general web applications with hibernate, is there a standard or a reccomended database that has a high acceptance within the developer community? i.e. PostgreSQL, MYSQL ...

I realize that it depends on the requirements of a project, but maybe someone has a good reccomendation that would also be a well-accepted DB in the Java "World" Any tips or information would be appreciated.

[Bill Gorder]

t Hibernate doesn't suppport identity fields in Derby(?).

Sure it does. It supports all the generation strategies except sequence. I use Derby all the time for local development(client) and testing(embedded). It has served me exceptionally well for this purpose especially when my test/stage/prod environments run DB2 since they are almost syntactically equal in many respects. I know people have used it in production applications but I am not one of them so I can not comment to that.

PostgreSQL and MYSQL are both good choices and either one would serve your purpose as well.

You can look at this page for some comparisons, it is wikipedia so it might not be 100% accurate, but for something like this it probably is fairly accurate.
http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems

[Scott Stephens]

Here is what I get when I try to

	public static void saveStammdaten(Stammdaten sd) {
		// TODO Auto-generated method stub
		Session session = HibernateUtil.getSessionFactory().openSession();
		Transaction tx = session.beginTransaction();
		session.save(sd);
		tx.commit();
		session.close();
	}

java.sql.SQLSyntaxErrorException: Attempt to modify an identity column 'PERSON_ID'.

My mapping file looks like this:

<hibernate-mapping package="model">
	<class lazy="false" name="Stammdaten" table="PERSONAL">
		<id name="id"  type="int">
		<column name="PERSON_ID" />
		<generator class="assigned"></generator>
		</id>
		<property generated="never" lazy="false" name="name"
			type="java.lang.String">
			<column name="NAME" />
		</property>
		<property generated="never" lazy="false" name="vorname"
			type="java.lang.String">
			<column name="VORNAME" />
		</property>
		<property generated="never" lazy="false" name="gebDat"
			type="java.util.Date">
			<column name="GEBDAT" />
		</property>
		<property generated="never" lazy="false" name="strasse"
			type="java.lang.String">
			<column name="STRASSE" />
		</property>
		<property generated="never" lazy="false" name="plz"
			type="java.lang.String">
			<column name="PLZ" />
		</property>
		<property generated="never" lazy="false" name="telefon"
			type="java.lang.String">
			<column name="TELEFON" />
		</property>
	</class>
</hibernate-mapping>

I tried <generator class="identity"> but then my other function for saving a record that already exists didn't work:

session.saveOrUpdate(s);

[James Boswell]

Using Identity as generation type is the way to go. What error do you get when using the saveOrUpdate method?

[Scott Stephens]

I instansiated my object (with id) and now using "identity" strategy I get a null pointer exception when I do a session.save
why does hibernate do a getId() when the identity generator class is used? I would think it wouldn't need the attribute because it gets generated new.

java.lang.NullPointerException
model.Stammdaten.getId(Stammdaten.java:61:

From the Tomcat console :
Initial SessionFactory creation failed.org.hibernate.PropertyAccessException: Exception occurred inside getter of model.Stammdaten.id


Any ideas?

[Bill Gorder]

What was the error you got when using the identity strategy? When using the identity strategy you are not setting the id correct? That should be done by the database. Also your DDL should define that. Using Derby as an example since it was mentioned.

CREATE TABLE PERSONAL
(
PERSON_ID INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),
NAME VARCHAR(100) ,
--More columns etc defined here
) ;

This will allow you to set the identity if you do not provide it it will use the generation strategy. If instead you use GENERATED ALWAYS you must never try to insert an ID.

[Scott Stephens]

The above mentioned is the error that comes when I use identity.
I don't set the id field at all. The data is in a managed bean. The variable sd is instansiated at class loading. id is set to null.
The DAOStammdaten.save() function is called from JSF per EL

 <h:commandButton id="sendSD" value="Speichern" action="#{sdHandler.saveStammdaten}

sdHandler.saveStammdaten just calls the DAO function keeping the business logic in it's layer.

	public String saveStammdaten(){
		
		DAOStammdaten.saveStammdaten(this.sd);
		return "SAVED";
	}

[Scott Stephens]

here the error msg that is displayed in the browser:
org.hibernate.PropertyAccessException: Exception occurred inside getter of model.Stammdaten.id
org.hibernate.property.BasicPropertyAccessor$BasicGetter.get(BasicPropertyAccessor.java:172)
org.hibernate.engine.UnsavedValueFactory.getUnsavedIdentifierValue(UnsavedValueFactory.java:67)
org.hibernate.tuple.PropertyFactory.buildIdentifierProperty(PropertyFactory.java:67)
org.hibernate.tuple.entity.EntityMetamodel.<init>(EntityMetamodel.java:147)
org.hibernate.persister.entity.AbstractEntityPersister.<init>(AbstractEntityPersister.java:457)
org.hibernate.persister.entity.SingleTableEntityPersister.<init>(SingleTableEntityPersister.java:131)
org.hibernate.persister.PersisterFactory.createClassPersister(PersisterFactory.java:84)
org.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:261)
org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1327)
org.hibernate.cfg.AnnotationConfiguration.buildSessionFactory(AnnotationConfiguration.java:867)
dao.HibernateUtil.<clinit>(HibernateUtil.java:12)
dao.DAOStammdaten.saveStammdaten(DAOStammdaten.java:74)

[James Boswell]

Can you post your code for the Stammdaten class?

[Scott Stephens]

I now have both session.save and session saveOrUpdate working!! However with a table that is not identity. For now a good step. I have in my mapping file class="assigned" and assign the id after getting a list and finding the size +1
I am still working on many of the concepts, so it takes a while to sort through some of the details- when class="assigned", the id has to be set before you can use an EL-Expression in a JSF Page. Otherwise, it says Null pointer.

package model;

import java.util.Date;

public class Stammdaten {
private Integer id;
private String name;
private String vorname;
private Date gebDat;
private String strasse;
private String plz;
private String ort;
private String telefon;
private String passwort;


public String getPasswort() {
	return passwort;
}

public void setPasswort(String passwort) {
	this.passwort = passwort;
}

public String getStrasse() {
	return strasse;
}

public void setStrasse(String strasse) {
	this.strasse = strasse;
}

public String getPlz() {
	return plz;
}

public void setPlz(String plz) {
	this.plz = plz;
}

public String getOrt() {
	return ort;
}

public void setOrt(String ort) {
	this.ort = ort;
}

public String getTelefon() {
	return telefon;
}

public void setTelefon(String telefon) {
	this.telefon = telefon;
}

public int getId() {
	return id;
}

public void setId(int id) {
	this.id = id;
}
public String getName() {
	return name;
}
public void setId(Integer id) {
	this.id = id;
}

public void setName(String name) {
	this.name = name;
}
public String getVorname() {
	return vorname;
}
public void setVorname(String vorname) {
	this.vorname = vorname;
}
public Date getGebDat() {
	return gebDat;
}
public void setGebDat(Date gebDat) {
	this.gebDat = gebDat;
}


}

[Scott Stephens]

I got the Derby database on another table to use identity and it works fine for a session.save.