Using Hibernate+SQLite

matero · **Joined:** Mon Sep 10, 2007 12:03 pm **Posts:** 2

Hi greetings from argentina

I'm trying to use SQLite+Hibernate+Spring, but i can't find info about using hibernate+sqlite, only tips about nhibernate+sqlite can be found at hibernate.org :(.

is somebody trying to integrate them?

must be a dialect defined?

or am i such a looser to get info ?? :P

if a Dialect must be defined, were can i get some tips to do it ?

thanks for your time
juanjo

matero · **Joined:** Mon Sep 10, 2007 12:03 pm **Posts:** 2

while i'm waiting for some tips ( :S ) i'd defined a simple Dialect for SQLite using the NHibernate's one as base:

here what i'd get (and is working for simple mappings / queries :D):

Code:

/**
 * 
 */
package org.mate.hibernate.dialect;

import org.hibernate.dialect.Dialect;

public class SQLiteDialect extends Dialect{

   public SQLiteDialect()
   {
      registerColumnType(java.sql.Types.BINARY, "BLOB");
      registerColumnType(java.sql.Types.TINYINT, "INTEGER(1)");
      registerColumnType(java.sql.Types.SMALLINT, "INTEGER(2)");
      registerColumnType(java.sql.Types.INTEGER, "INTEGER(4)");
      registerColumnType(java.sql.Types.BIGINT, "INTEGER(8)");
      registerColumnType(java.sql.Types.BIT, "INTEGER(1)");
      registerColumnType(java.sql.Types.BOOLEAN, "INTEGER(1)");
      registerColumnType(java.sql.Types.DECIMAL, "REAL");
      registerColumnType(java.sql.Types.DOUBLE, "REAL");
      registerColumnType(java.sql.Types.FLOAT, "REAL");
      registerColumnType(java.sql.Types.CHAR, "TEXT(1)");
      registerColumnType(java.sql.Types.VARCHAR, "TEXT($l)");
      registerColumnType(java.sql.Types.CLOB, "TEXT");
      registerColumnType(java.sql.Types.BLOB, "BLOB");
      registerColumnType(java.sql.Types.DATE, "DATETIME");
      registerColumnType(java.sql.Types.TIME, "DATETIME");
      registerColumnType(java.sql.Types.NULL, "NULL");
   }

   @Override
   public String getIdentitySelectString()
   {
      return "SELECT last_insert_rowid()";
   }

   @Override
   public String appendIdentitySelectToInsert(String insertSql)
   {
      return insertSql + "; " + getIdentitySelectString();
   }

   @Override
   public  boolean hasAlterTable()
   {
       return false; 
   }

   @Override
   public boolean dropConstraints()
   {
      return false;
   }

   @Override
   public String getForUpdateString()
   {
      return "";
   }

   @Override
   public boolean supportsSubselectAsInPredicateLHS()
   {
   // TODO: try it, at nhibernate's original code says: 
        // SQLite actually does support subselects, but gives syntax errors
   // in tests. Need to investigate this.
      return false; 
   }
}

anyone has some tips defining Dialects ?

haron · **Posted:** Mon Nov 03, 2008 5:37 am

Hallo matero,

Thank you very much for contribution of this Dialect. I have as
well tried this, and it seems to work (at least partly, since I
can't run the whole app yet), but my problem is database
sequences. I have managed to create my own function, which can be
registered into SQLite Database, which I can call like this:

@Override
public String getSequenceNextValString(String sequenceName) {
return "select sqlitePseudoSequenceNextVal('" + sequenceName + "');";
}

And it's works well if I use pure JDBC connection, but if I do it
with Hibernate + SPRING, it seems to be that I get every time new
JDBC connection, so that my registered function is lost... But
that is another question, which I probably will ask in separate
thread.

--
Anton

mbaranski · **Posted:** Thu Feb 12, 2009 9:55 am

In Spring, can't you define a method to call at startup and at shutdown for your object in the <beans> element of applicationContext.xml:

<bean id="hibernateSpringConnector"
class="com.mycompany.HibernateSpringConnector"
init-method="init"
destroy-method="destroy" />

Then, in your init() method, register the function for each new instance of your connector?