[jboss-user] [JBoss Seam] - Re: problems with ms sql
brett.schuchert
do-not-reply at jboss.com
Mon Oct 9 23:15:31 EDT 2006
I had the same problem. Here's partial code that does the trick. Note that I'm using an EntityManager in a JSE environment, not a JEE environment.
Also, since I'm using hibernate, I did one hacky thing... I down casted the EntityManager to EntityManagerImpl.
Here's what I wrote to set identity on and off (this is a first pass, I'll properly handle exceptions soon):
private void executeSql(final EntityManager em, final String sql) {
| try {
| getDestinationEm().getTransaction().begin();
|
| EntityManagerImpl emi = (EntityManagerImpl)em; //THE HACK
| Connection con = emi.getSession().connection();
| Statement s = con.createStatement();
| s.execute(sql);
| s.close();
| con.close();
|
| getDestinationEm().getTransaction().commit();
| } catch (Exception e) {
| e.printStackTrace();
| }
| }
|
| private void setInsertModeOn() {
| executeSql(getDestinationEm(), "SET IDENTITY_INSERT Application ON");
| }
|
| private void setInsertModeOff() {
| executeSql(getDestinationEm(), "SET IDENTITY_INSERT Application OFF");
| }
Then here's the code to read the from one database (something called TagApplication), create an instance of application (with the id set from the TagApplication row's value) and persist it:
// Note that this just performs a query returning all applications after
| // a particular date
| private List<TagApplication> retrieveAllTagApplications() {
| final Query query = getSourceEm()
| .createQuery("SELECT ta from TagApplication ta where ta.applicationDate >= ?1");
| query.setParameter(1, getIgnoreDate(), TemporalType.DATE);
| return query.getResultList();
| }
|
| public void translate() {
| // I've already been given my EntityManagerFactories, so create my EntityManager
| if (getSourceEm() == null) {
| setSourceEm(sourceEmf.createEntityManager());
| }
| if (getDestinationEm() == null) {
| setDestinationEm(destinationEmf.createEntityManager());
| }
|
| try {
| // Set IDENTITY_INSERT on Application in HealthTrackDB and begin a transaction
| setInsertModeOn();
| getDestinationEm().getTransaction().begin();
|
| // retrieve all TagApplication rows
| final List<TagApplication> tagApplications = retrieveAllTagApplications();
|
| // For each TagApplication row, create an Application based on the
| // TagApplication (field-by-field hand copy) and (batch) insert it.
| for (TagApplication ta : tagApplications) {
| Application a = convert(ta);
| getDestinationEm().persist(a);
| }
|
| // Finish off the (implicit) batch insert & reset the IDENTITY_INSERT to off
| getDestinationEm().getTransaction().commit();
| setInsertModeOff();
| } finally {
| getSourceEm().close();
| getDestinationEm().close();
| }
| }
Brett
View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=3977115#3977115
Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=3977115
More information about the jboss-user
mailing list