createDatabaseSchema() not correctly setting "not null"

[JamesSimons]

When creating a database table using the org.springframework.orm.hibernate3.LocalSessionFac toryBean.createDatabaseSchema(), the table is being created with some fields being null capable, despite the use of the not-null=true attribute for all the fields in the associated hibernate xml mapping. To further confuse the issue, some other fields in the same table ARE getting correctly created as null restricted.

I had first suspected the JDBC Driver (com.ibm.as400.access.AS400JDBCDriver) but when I tried to run the build a second time (with the file already existing, the warning message in the log showed me the create table SQL generated by the LocalSessionBeanFactory, and its clear that the "not null" attributes are not consistant there, so it seems to be a Spring/Hibernate issue.

2007-02-15 14:37:42,065 WARN org.springframework.orm.hibernate3.LocalSessionFactoryBean - 
Unsuccessful schema statement: 
create table PFCASBNCOV (RECID bigint generated by default as identity, VERSIONID bigint not null, 
BVCO char(4) not null, BVPOLN char(10) not null, BVCVBR numeric(2) not null, BVCVPB char(1) not null, 
BVEFYY numeric(4), BVEFMM numeric(2), BVEFDD numeric(2), BVPLAN char(6), BVCOD1 char(6), BVVAL1 numeric(8), 
BVCOD2 char(6), BVVAL2 numeric(8), BVCOD3 char(6), BVVAL3 numeric(8), BVCOD4 char(6), BVVAL4 numeric(8), 
BVCOD5 char(6), BVVAL5 numeric(8), BVUSR1 char(10), BVUSR2 char(10), BVUSER char(10), BVLCHY numeric(4), 
BVLCHM numeric(2), BVLCHD numeric(2), primary key (RECID))

As you can see, the “not null” modifier does not appear on a number of fields. This directly corresponds with the fields that are being created as null capable in the database table. Can anyone see a problem in my setup that I'm missing, or had experience with something similar and found a work around ?

The spring version is spring framework 2.0.2

The code to generate the table is as follows:

LocalSessionFactoryBean factory = (LocalSessionFactoryBean) appContext.getBean("&SessionFactory") ;
factory.createDatabaseSchema() ;

The session factory bean

	<bean id="SessionFactory"
		class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
		<property name="dataSource" ref="DataSource" />
		<property name="mappingResources">
			<list>
				<value>
					com\genelco\newlife\entity\cas\base\BenefitCoverageBase.hbn.xml
				</value>
			</list>

		</property>
	</bean>

The hibernate mapping file

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

<hibernate-mapping package="com.genelco.newlife.entity.cas.base"
	default-lazy="false">

	<class name="BenefitCoverageBase" table="PFCASBNCOV">

		<id name="uniqueID" column="RECID" unsaved-value="-1">
			<generator class="native" />
		</id>

		<natural-id mutable="true">
			<property name="companyCode" type="string" index="CASBNCOV"
				not-null="true">
				<column name="BVCO" sql-type="char(4)" />
			</property>

			<property name="policyNumber" type="string" index="CASBNCOV"
				not-null="true">
				<column name="BVPOLN" sql-type="char(10)" />
			</property>

			<property name="coverageBaseRideCode" type="integer"
				index="CASBNCOV" not-null="true">
				<column name="BVCVBR" sql-type="numeric(2)" />
			</property>

			<property name="policyOrBenefitCove" type="string"
				index="CASBNCOV" not-null="true">
				<column name="BVCVPB" sql-type="char(1)" />
			</property>

			<component name="effectiveDate"
				class="com.genelco.newlife.framework.FixedDate">
				<property name="year" access="field" type="integer"
					index="CASBNCOV" not-null="true">
					<column name="BVEFYY" sql-type="numeric(4)" />
				</property>
				<property name="month" access="field" type="integer"
					index="CASBNCOV" not-null="true">
					<column name="BVEFMM" sql-type="numeric(2)" />
				</property>
				<property name="day" access="field" type="integer"
					index="CASBNCOV" not-null="true">
					<column name="BVEFDD" sql-type="numeric(2)" />
				</property>
			</component>

		</natural-id>

		<version name="version" column="VERSIONID" type="long" />

		<property name="planCode" type="string" not-null="true">
			<column name="BVPLAN" sql-type="char(6)" />
		</property>

		<property name="coverageCode1" type="string" not-null="true">
			<column name="BVCOD1" sql-type="char(6)" />
		</property>

		<property name="coverageValue1" type="integer"
			not-null="true">
			<column name="BVVAL1" sql-type="numeric(8)" />
		</property>

		<property name="coverageCode2" type="string" not-null="true">
			<column name="BVCOD2" sql-type="char(6)" />
		</property>

		<property name="coverageValue2" type="integer"
			not-null="true">
			<column name="BVVAL2" sql-type="numeric(8)" />
		</property>

		<property name="coverageCode3" type="string" not-null="true">
			<column name="BVCOD3" sql-type="char(6)" />
		</property>

		<property name="coverageValue3" type="integer"
			not-null="true">
			<column name="BVVAL3" sql-type="numeric(8)" />
		</property>

		<property name="coverageCode4" type="string" not-null="true">
			<column name="BVCOD4" sql-type="char(6)" />
		</property>

		<property name="coverageValue4" type="integer"
			not-null="true">
			<column name="BVVAL4" sql-type="numeric(8)" />
		</property>

		<property name="coverageCode5" type="string" not-null="true">
			<column name="BVCOD5" sql-type="char(6)" />
		</property>

		<property name="coverageValue5" type="integer"
			not-null="true">
			<column name="BVVAL5" sql-type="numeric(8)" />
		</property>

		<property name="userDefinedField10A" type="string"
			not-null="true">
			<column name="BVUSR1" sql-type="char(10)" />
		</property>

		<property name="userDefinedField10A1" type="string"
			not-null="true">
			<column name="BVUSR2" sql-type="char(10)" />
		</property>

		<property name="lastChangeUser" type="string" not-null="true">
			<column name="BVUSER" sql-type="char(10)" />
		</property>

		<component name="lastChangeDate"
			class="com.genelco.newlife.framework.FixedDate">
			<property name="year" access="field" type="integer"
				not-null="true">
				<column name="BVLCHY" sql-type="numeric(4)" />
			</property>
			<property name="month" access="field" type="integer"
				not-null="true">
				<column name="BVLCHM" sql-type="numeric(2)" />
			</property>
			<property name="day" access="field" type="integer"
				not-null="true">
				<column name="BVLCHD" sql-type="numeric(2)" />
			</property>
		</component>


	</class>

</hibernate-mapping>

Thanks

James Simons

[karldmoore]

Hmm this does look like a strange problem. If the table doesn't exist and you run it for the first time, is there any correlation between which columns don't get not null set? Is it the same everytime, always the same columns? I would say this is a Hibernate problem.

[JamesSimons]

Thanks for your reply.

The problem is repeatable (I can delete the table, recreate it, and get the exact same results) and consistent across the database (I have over 100 tables with this problem). Researching some more, it seems that the properties inside the <natural-id> tags are correctly getting the not null attribute, but the remaining properties are not.

I wasn't certain if the "create table" generation was Hibernate, since even when I put "hibernate.show_sql true" in my hibernate.properties, I didn't see any SQL in the log. But it makes sense that it would be. I guess my next step is to trace it through to figure out which Hibernate module gets called, or to try to create the schema with the Hibernate tools and see if I have the same issues.

[karldmoore]

It's good that it's repeatable . I think the schema generation uses hbm2ddl. That might be why you weren't seeing the SQL logs. If you have a look at the reference manual link, the logging section tells you want to need to do. It might be worth having a look at the Hibernate forums and JIRA to see if this issue has been seen in Hibernate before.

http://www.hibernate.org/hib_docs/v3...oolsetguide-s1
http://www.hibernate.org/hib_docs/v3...ration-logging