Reputation: 5667

java.sql.SQLException: This function is not supported using HSQL and Spring

Can someone please tell me why am I geting java.sql.SQLException: This function is not supported using HSQL and Spring? I am trying to insert a new row into my database..

Below is my DAO and I get the error on the mySession.save(message) line:

@Transactional
@Repository
public class MessageDaoImpl implements MessageDao
{


    private Log log = null;
    @Autowired
    private SessionFactory sessionFactory;

    public MessageDaoImpl()
    {
        super();
        log = LogFactory.getLog(MessageDaoImpl.class);

    }

    @SuppressWarnings("unchecked")
    @Transactional(readOnly = true, propagation = Propagation.REQUIRED)
    public List<Message> listMessages()
    {
        try
        {
            return (List<Message>) sessionFactory.getCurrentSession()
                    .createCriteria(Message.class).list();

        } catch (Exception e)
        {
            log.fatal(e.getMessage());
            return null;
        }
    }


    @SuppressWarnings("unchecked")
    @Transactional(readOnly = false, propagation = Propagation.REQUIRED)
    public void SaveOrUpdateMessage(Message message)
    {
        try
        {
            Session mySession = sessionFactory.getCurrentSession();
            mySession.save(message);
            mySession.flush();
        } catch (Exception e)
        {
            log.fatal(e.getMessage());
        }
    }


}

Here is my main class:

public static void main(String[] args)
    {
        ApplicationContext context = new AnnotationConfigApplicationContext(HelloWorldConfig.class);


        MessageService mService  = context.getBean(MessageService.class);

        HelloWorld helloWorld = context.getBean(HelloWorld.class);

        /**
         * Date:   4/26/13 / 9:26 AM
         * Comments:
         *
         *    I added Log4J to the example.
         */

        LOGGER.debug("Message from HelloWorld Bean: " + helloWorld.getMessage());

        Message message = new Message();
        message.setMessage(helloWorld.getMessage());
        //
        mService.SaveMessage(message);



        helloWorld.setMessage("I am in Staten Island, New York");


        LOGGER.debug("Message from HelloWorld Bean: " + helloWorld.getMessage());
    }
}

Here is my DatabaseConfig:

public class DatabaseConfig
{

    private static final Logger LOGGER = getLogger(DatabaseConfig.class);


    @Autowired
    Environment env;

    @Bean
    public DataSource dataSource() {

        EmbeddedDatabaseBuilder builder = new EmbeddedDatabaseBuilder();
        EmbeddedDatabase db = builder.setType(EmbeddedDatabaseType.HSQL).
        addScript("schema.sql").build();

        return db;
    }


    @Bean
    public DataSource hsqlDataSource()  {

        BasicDataSource ds = new BasicDataSource();


        try {
            ds.setDriverClassName("org.hsqldb.jdbcDriver");
            ds.setUsername("sa");
            ds.setPassword("");
            ds.setUrl("jdbc:hsqldb:mem:mydb");
        }
        catch (Exception e)
        {
            LOGGER.error(e.getMessage());
        }
        return ds;



    }

    @Bean
    public SessionFactory sessionFactory()
    {

        LocalSessionFactoryBean factoryBean = new LocalSessionFactoryBean();
        factoryBean.setDataSource(hsqlDataSource());
        factoryBean.setHibernateProperties(getHibernateProperties());
        factoryBean.setPackagesToScan(new String[]{"com.xxxxx.HelloSpringJavaBasedJavaConfig.model"});

        try
        {
            factoryBean.afterPropertiesSet();
        } catch (IOException e)
        {
            LOGGER.error(e.getMessage());
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }


        return factoryBean.getObject();
    }

    @Bean
    public Properties getHibernateProperties()
    {
        Properties hibernateProperties = new Properties();

        hibernateProperties.setProperty("hibernate.dialect", env.getProperty("hibernate.dialect"));
        hibernateProperties.setProperty("hibernate.show_sql", env.getProperty("hibernate.show_sql"));
        hibernateProperties.setProperty("hibernate.use_sql_comments", env.getProperty("hibernate.use_sql_comments"));
        hibernateProperties.setProperty("hibernate.format_sql", env.getProperty("hibernate.format_sql"));
        hibernateProperties.setProperty("hibernate.hbm2ddl.auto", env.getProperty("hibernate.hbm2ddl.auto"));

        hibernateProperties.setProperty("hibernate.generate_statistics", env.getProperty("hibernate.generate_statistics"));

        hibernateProperties.setProperty("javax.persistence.validation.mode", env.getProperty("javax.persistence.validation.mode"));

        //Audit History flags
        hibernateProperties.setProperty("org.hibernate.envers.store_data_at_delete", env.getProperty("org.hibernate.envers.store_data_at_delete"));
        hibernateProperties.setProperty("org.hibernate.envers.global_with_modified_flag", env.getProperty("org.hibernate.envers.global_with_modified_flag"));

        return hibernateProperties;
    }

    @Bean
    public HibernateTransactionManager hibernateTransactionManager()
    {
        HibernateTransactionManager htm = new HibernateTransactionManager();
        htm.setSessionFactory(sessionFactory());
        htm.afterPropertiesSet();
        return htm;
    }


}

and here is what I am getting to the console:

Exception in thread "main" org.hibernate.AssertionFailure: null id in com.xxx.HelloSpringJavaBasedJavaConfig.model.Message entry (don't flush the Session after an exception occurs)

Here is my message model bean:

@Entity
@Table(name = "messages")
public class Message
{

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.AUTO)
    private String id;

    @Column(name = "message")
    private String message;

    public String getId()
    {
        return id;
    }

    public void setId(String id)
    {
        this.id = id;
    }

    public String getMessage()
    {
        return message;
    }

    public void setMessage(String message)
    {
        this.message = message;
    }

    @Override
    public String toString()
    {
        return "Message{" +
                "id='" + id + '\'' +
                ", message='" + message + '\'' +
                '}';
    }
}

Upvotes: 1

Answers (5)

Iuliana Cosmina

Reputation: 134

I had the same issue after I upgraded hibernate to version 4.2.8 .Looking in the logs, I noticed that the sql query generated by hibernate tried to insert a record with a null primary key. The field was annotated just with: @Id @GeneratedValue

Upgrading hsqldb to version 2.2.9 made this disappear just like Denis said and I am very thankful to him for the reply.

Upvotes: 1

Denis

Reputation: 209

This relates to the version of hsql being used probably the version causing issue was 1.8 with hibernate 4. Need to use 2.2.9 instead

Upvotes: 10

SJS

Reputation: 5667

it was a version issues. I updated the versions and now everything works

Upvotes: 1

Matt Rick

Reputation: 146

It seems very likely that this issue is related to attempting to use a Session which has already signaled an error. As Sotirios mentioned, it is a bad idea to catch exceptions in your DAO, and if you do, it is critical that you re-throw them.

Normally, once you catch a Hibernate exception, you must roll back your transaction and close the session as the session state may no longer be valid (Hibernate core documentation).

If the Session throws an exception, including any SQLException, immediately rollback the database transaction, call Session.close() and discard the Session instance. Certain methods of Session will not leave the session in a consistent state. No exception thrown by Hibernate can be treated as recoverable. Ensure that the Session will be closed by calling close() in a finally block.

Since you're using Spring, most of that doesn't apply to you, but the exception message you are seeing indicates that the actual cause of your problem probably was related to catching an exception and then continuing to use the same session.

Upvotes: 0

Sotirios Delimanolis

Reputation: 279970

You can't use a String with @GenerateValue with the Strategy GenerationType.AUTO since it uses sequence generator and those can't be used with non-numerical values. You have to set it yourself. Use an Integer or Long if you want it to be generated for you.

Hibernate docs

Or use an id generator that uses string values

@Id 
@GeneratedValue(generator="system-uuid")
@GenericGenerator(name="system-uuid", strategy = "uuid")

Upvotes: 1

java.sql.SQLException: This function is not supported using HSQL and Spring

Answers (5)

Related Questions