Handling errors when running SQL query

[shaf maff]

Hi Guys

I have the following method in my sql bean:

public boolean exUpdate(String Query) {
PreparedStatement pstmt;
try {
pstmt = conn.prepareStatement(Query);
pstmt.executeUpdate(Query);
pstmt.close();
conn.close();
return true;
} catch (Exception e) { System.out.println("Exception : "+ e.getMessage()); 
return false; 
}
}

I am alittle inexperienced when it comes to handling errors. I am using a try/catch which will log errors, I also have a return statement which will be used to inform the user if anything goes wrong. Do I need anything else ? I was told I should have a commit/rollback in there, but how to do implement it?




.
[ December 02, 2008: Message edited by: shaf maff ]

[Jimmy Clark]

Transaction demarcation is a big topic. Handling and programming transactions can be done in many different ways. Basically, you want to identify what is a "transaction" in terms of your application. You would first need to get a good understanding of the definition of a "transaction."

Once you have identified the transaction, you then need to look at the operations of the transaction and which methods implement those operations.

Next, you need to look at how you have designed the business logic and the database logic and look at the business methods and the persistence methods.

Check out the Java Transaction API for more info. Below is an example for study:

public void updateCustomerInfo(CustomerInfo ci) throws UpdateException {
 
     javax.transaction.UserTransaction ut = null;
 
     try {
         javax.naming.InitialContext ic = new InitialContext();
         ut = (UserTransaction)ic.lookup("jta/UserTransaction");
     } catch (Exception e) {
          throw new UpdateException(e.getMessage()); 
     }
 
     try {
 
         ut.begin();
  
         customerDAO.exUpdate(ci);
 
         [B]ut.commit();[/B]
 
     } catch (Exception e) {
          try {
 
              [B]ut.rollback();[/B]          
 
          } catch (Exception ex) {
              System.out.println("Log error");
          }
          throw new UpdateException(e.getMessage());
     }
}

[ December 02, 2008: Message edited by: James Clark ]

[shaf maff]

Thanks for the reply. I had a quick read of the api. How is this different from the way I have coded my one above ? It looks like this should only be used in transactions, I am not doing any so should I just stick to my code ?

[Jimmy Clark]

Do you understand the definition of "transaction" ?
[ December 02, 2008: Message edited by: James Clark ]

[shaf maff]

Well, its usually a transaction between parties. Its usually attributed to money, unless java means it in its more general way? :s

[Jimmy Clark]

I suggest that you execute a Yahoo search on the term "database transaction demarcation." IBM and the Hibernate group have good material that covers transaction management and programming.

Enjoy!

[shaf maff]

I read up on the java website and it makes sense now. I have a question, in the code you posted, I wanted to know for the following line of code:

customerDAO.exUpdate(ci);

the exUpdate is my custom function which runs the sql statement right ?


.
[ December 02, 2008: Message edited by: shaf maff ]

[Amit Ghorpade]

Originally posted by shaf maff:

customerDAO.exUpdate(ci);

the exUpdate is my custom function which runs the sql statement right ?

yes that's what DAO class methods are meant for. They provide an interface to execute queries.