CODEWITHSUNDEEP
javahibernate
Jose Hdez
Jose Hdez

Reputation: 2347

Persists an object with Hibernate

I am trying to store an object into a database using Hibernate but I get an Exception and I am completely lost about the reason.

I have developed two ways: First of all, using the save/persist/merge methods as follows:

sessionCARTIF.beginTransaction();
sessionCARTIF.save((Schedulers)object);
sessionCARTIF.getTransaction().commit();

In this case, the exception I obtain is thrown when committing results. It says the transaction was nos successfully started.

Secondly, I have try by common SQL query as follows:

String sSQL = "INSERT INTO SCHEDULERS(SCHEDULER_ID, SCHEDULER_NAME, START_DATE,"
                + "FINISH_DATE, TIMER, EVENT_ID, BUILDING_ID, EVENT_PROPERTIES)"
                + " VALUES ("+object.getSchedulerId()+", '"+object.getSchedulerName()+"', "
                + "to_timestamp('"+object.getStartDate()+"','YYYY-MM-DD HH24:MI:SS.FF'), "
                + "to_timestamp('"+object.getFinishDate()+"','YYYY-MM-DD HH24:MI:SS.FF'), "
                + " '"+object.getTimer()+"', "+object.getEvents().getEventId()+", "
                + "'"+object.getBuildingId()+"', '"+object.getEventProps()+"');";
sessionCARTIF.createSQLQuery(sSQL).executeUpdate();

In this second case, the exception says org.hibernate.exception.SQLGrammarException. However, the query has been tested with the database graphical interface and it works.

Any idea??

Thanks in advance

Upvotes: 1

Views: 116

Answers (1)

MikkolidisMedius
MikkolidisMedius

Reputation: 4844

There are several reasons why you could be getting the error about the transaction not being started successfully. The first and most common one is because you are trying to do more than one transaction with the same session (Hibernate sessions are usually transaction bound, when you commit the session is closed unless you change the auto_close_session option to false).

The usual pattern is to create a SessionFactory object that creates Sessions as needed:

public class HibernateUtil {
  private static final SessionFactory sessionFactory;
  static {
    try {
      // Create the SessionFactory from hibernate.cfg.xml
      sessionFactory = new Configuration().configure().buildSessionFactory();
    } catch (Throwable ex) {
      // Make sure you log the exception, as it might be swallowed
      System.err.println("Initial SessionFactory creation failed." + ex);
      throw new ExceptionInInitializerError(ex);  
    }
  }

  public static SessionFactory getSessionFactory() {  
    return sessionFactory;  
  } 
}

Then use the factory to create a session for every transaction, like this:

Session session = HibernateUtil.getSessionFactory().openSession();
Transaction tx = null;
try {
   tx = session.beginTransaction();
   // work with the db
   tx.commit();
}
catch (Exception e) {
  if (tx != null) {
    tx.rollback();
  }
  // Handle the exception, print message, etc.
} finally {
   session.close();
}

Another reason to get this error is because you're having a non transactional datasource or connection definition, or you have auto-commit enabled at database level or transactions are disabled (some DBs need specific drivers or libraries to support transactions, especially distributed transactions).

The reason why the SQL string generates an error could be caused by the conversion to string of one or more of the parameters you're concatenating into your SQL string. When you concatenate, Java calls the .toString() methods of each of the classes and the output could not be what you're expecting (especially with dates and non-string values). To verify that this is the problem, print the sSQL string and check the SQL that is actually being generated and eventually post that.

Note that concatenating values into an SQL instruction is considered a bad practice and can lead to SQL injection! It is better to use PreparedStatements and set parameters separately. This also has the side effect of handling type conversion for you, especially for dates, for example:

PreparedStatement stmt = conn.prepareStatement("INSERT INTO SCHEDULERS(SCHEDULER_ID, SCHEDULER_NAME, START_DATE,"
    + "FINISH_DATE, TIMER, EVENT_ID, BUILDING_ID, EVENT_PROPERTIES)"
    + " VALUES (?, ?, ?, ?, ?, ?, ?, ?)");

stmt.setString(1, object.getSchedulerId());
stmt.setString(2, object.getSchedulerName());
stmt.setDate(3, object.getStartDate());
stmt.setDate(4, object.getFinishDate());
stmt.setString(5, object.getTimer());
stmt.setString(6, object.getEvents().getEventId());
stmt.setString(7, object.getBuildingId());
stmt.setString(8, object.getEventProps());

stmt.executeUpdate();

Note that in this case you don't have to take care of putting quotes around strings and don't need to handle date formats as well, and SQL injection is guaranteed to not be possible in this case.

Upvotes: 1

Related Questions