How to convert fields into rows through SQL in MS Access 2007 or MS SQL Server 2005

Question

I am trying to create a simple project with Java EE. However, I get a huge stacktrace.

org.apache.openejb.config.ValidationFailedException: Module failed validation. AppModule(name=BarSystem_war_exploded)
at org.apache.openejb.config.ReportValidationResults.deploy(ReportValidationResults.java:88)
at org.apache.openejb.config.AppInfoBuilder.build(AppInfoBuilder.java:312)
at org.apache.openejb.config.ConfigurationFactory.configureApplication(ConfigurationFactory.java:974)
at org.apache.tomee.catalina.TomcatWebAppBuilder.startInternal(TomcatWebAppBuilder.java:1227)
at org.apache.tomee.catalina.TomcatWebAppBuilder.configureStart(TomcatWebAppBuilder.java:1100)
at org.apache.tomee.catalina.GlobalListenerSupport.lifecycleEvent(GlobalListenerSupport.java:130)
at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:117)
at org.apache.catalina.util.LifecycleBase.fireLifecycleEvent(LifecycleBase.java:90)
at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5416)
at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:901)
at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:877)
at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:652)
at org.apache.tomee.catalina.TomcatWebAppBuilder.deployWar(TomcatWebAppBuilder.java:663)
at org.apache.tomee.catalina.TomcatWebAppBuilder.deployWebApps(TomcatWebAppBuilder.java:622)
at org.apache.tomee.catalina.deployment.TomcatWebappDeployer.deploy(TomcatWebappDeployer.java:43)
at org.apache.openejb.assembler.DeployerEjb.deploy(DeployerEjb.java:176)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at org.apache.openejb.core.interceptor.ReflectionInvocationContext$Invocation.invoke(ReflectionInvocationContext.java:192)
at org.apache.openejb.core.interceptor.ReflectionInvocationContext.proceed(ReflectionInvocationContext.java:173)
at org.apache.openejb.security.internal.InternalSecurityInterceptor.invoke(InternalSecurityInterceptor.java:35)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at org.apache.openejb.core.interceptor.ReflectionInvocationContext$Invocation.invoke(ReflectionInvocationContext.java:192)
at org.apache.openejb.core.interceptor.ReflectionInvocationContext.proceed(ReflectionInvocationContext.java:173)
at org.apache.openejb.monitoring.StatsInterceptor.record(StatsInterceptor.java:181)
at org.apache.openejb.monitoring.StatsInterceptor.invoke(StatsInterceptor.java:100)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at org.apache.openejb.core.interceptor.ReflectionInvocationContext$Invocation.invoke(ReflectionInvocationContext.java:192)
at org.apache.openejb.core.interceptor.ReflectionInvocationContext.proceed(ReflectionInvocationContext.java:173)
at org.apache.openejb.core.interceptor.InterceptorStack.invoke(InterceptorStack.java:85)
at org.apache.openejb.core.stateless.StatelessContainer._invoke(StatelessContainer.java:227)
at org.apache.openejb.core.stateless.StatelessContainer.invoke(StatelessContainer.java:194)
at org.apache.openejb.server.ejbd.EjbRequestHandler.doEjbObject_BUSINESS_METHOD(EjbRequestHandler.java:370)
at org.apache.openejb.server.ejbd.EjbRequestHandler.processRequest(EjbRequestHandler.java:181)

So I have a UserDAO like this:

@Stateless
public class UserDAO {

@PersistenceContext
private EntityManager em;

public void createUser(String name) {
//        User user = new User();
//        user.setName(name);
//        em.persist(user);
//        System.out.println("Maliiii");
    }
}

when I comment the EntityManager and @PersistenceContext, the TomEE runs without exceptions. However only by removing comments and deploying, I get a huge stacktrace. Before I switch to TomEE, I tried with glassfish and this problem did not occure. However, there was a problem setting up the jdbc connection pool in the application manager, so that's why i switched to tomee.

persistence.xml:

<?xml version="1.0" encoding="UTF-8"?>

<persistence-unit name="NewPersistenceUnit">
    <provider>org.hibernate.ejb.HibernatePersistence</provider>
    <properties>
        <property name="hibernate.connection.url" value="jdbc:mysql://localhost:3306/bar"/>
        <property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver"/>
        <property name="hibernate.connection.username" value="root"/>
        <property name="hibernate.connection.password" value="root"/>
        <!--<property name="hibernate.archive.autodetection" value="class"/>-->
        <property name="hibernate.show_sql" value="true"/>
        <property name="hibernate.format_sql" value="true"/>
        <property name="hbm2ddl.auto" value="create"/>
    </properties>
</persistence-unit>

any ideas how to solve it?

Solution 1

Using PIVOT and UNPIVOT.

UNPIVOT performs almost the reverse operation of PIVOT, by rotating columns into rows. Suppose the table produced in the previous example is stored in the database as pvt, and you want to rotate the column identifiers Emp1, Emp2, Emp3, Emp4, and Emp5 into row values that correspond to a particular vendor. This means that you must identify two additional columns. The column that will contain the column values that you are rotating (Emp1, Emp2,...) will be called Employee, and the column that will hold the values that currently reside under the columns being rotated will be called Orders. These columns correspond to the pivot_column and value_column, respectively, in the Transact-SQL definition. Here is the query.

--Create the table and insert values as portrayed in the previous example.
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int)
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4)
INSERT INTO pvt VALUES (2,4,1,5,5,5)
INSERT INTO pvt VALUES (3,4,3,5,4,4)
INSERT INTO pvt VALUES (4,4,2,5,5,4)
INSERT INTO pvt VALUES (5,5,1,5,5,5)
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM 
   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
   FROM pvt) p
UNPIVOT
   (Orders FOR Employee IN 
      (Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt
GO

Here is a partial result set.

VendorID   Employee   Orders
1      Emp1         4
1      Emp2         3
1      Emp3         5
1      Emp4         4
1      Emp5         4
2      Emp1         4
2      Emp2         1
2      Emp3         5
2      Emp4         5
2      Emp5         5
...

Solution 2

As mentioned above, UNPIVOT operator, if available, will do this... If this is not available, then std SQL approach is:

Union multiple select statments (One for each week) that alias the specific week's column with the same column name alias

  Select 1 as week, Week1Val as value from Table
    UNION
  Select 2 as week, Week2Val as value from Table
    UNION
  Select 3 as week, Week3Val as value from Table
    UNION
 ... 
    UNION
  Select 52 as week, Week52Val as value from Table

Solution 3

No need to export to SQL Server. In Access, try the /View/PivotTable View submenu. (It's in my Access 2003, at any rate.) I like it better than the one in Excel.

Comments

[wypieprz]

Omitting unitName attribute is vendor-specific, try injecting this way @PersistenceContext(name = "NewPersistenceUnit").

[vasil todorov]

I tried, also added the name in persistence.xml, but did not work :(

[Jaqen H'ghar]

Is that the whole stacktrace? Nothing more in the log?

[vasil todorov]

stackoverflow did not let me post it all, I had to cut :(

[vasil todorov]

Hello, I tried using maven. In the pom fle i have: hibernate-jpa-2.1-api, hibernate-entitymanager, hibernate-core.

[OndroMih]

Then it seems that my answer does not apply. Do you have MySQL jdbc driver installed in TomEE? Otherwise I'm out of ideas - try to search TomEE documentation on how to use hibernate instead of built-in OpenJPA, may there is something that needs to be done. Could you post the rest of stackrace? I wonder if there are other nested exceptions, because the top exception is coming from EJB, not from JPA, and has no helpful message.