Q:

Ways to generate database errors for exception handling

I am writing java code to do database operations using JPA. i.e. getTransaction, getResultList, setFirstResult, setMaxResults, begin, commit, flush, clear, ...
My question is, even though I can wrap my codes in try catch blocks, I still need ways to generate exception to test if my code works.
How would you generate database errors to test your exception handling? Some SQL commands to run on the DB outside java at the same time when the program runs? My target DB is SQL server 2008 R2.
Examples on common errors like locking and "StaleObjectStateException:Row was updated or deleted by another..." would be appreciated.
Thank you!

A:

For the StaleObjectStateSession you can write a JUnit test case like
e1 = session1.load(key);
e2 = session2.load(key);
e2.modifyAnything();
e2.update();
e2.commit();
e1.modifyAnything();
e1.update();

Then you get your exception. By the way, the StaleObjectStateSession is a Hibernate exception, NOT a database exception. 
For testing a real database exception you can violate a database constraint, for example write a JUnit case where you insert a row with a primary key which already exists or which has a null value in a column which is declared as NOT NULL on the database.
If you want to test only once and you want to avoid the effort for writing a JUnit test case, then you can do it with the eclipse debugging and SQL manipulations:
You set a breakpoint between the load and the commit of the record in your code. When the breakpoint is reached then in SQL you manually remove this record with DELETE FROM table WHERE primary_key =... and commit this modification before you continue execution. (Alternatively you can UPDATE the row, but then do not forget to update the version column too.) Then in Eclipse you continue the execution of the program.
Another idea is to temporarily make a nullable column to not null with ALTER TABLE table MODIFY(column ... NOT NULL). After your test you have to undo this modification. Sure, such a test only should be done on a dedicated test database and not on the productive system.

