Hot questions for Using Enterprise JavaBeans in jdbc

Question:

I'm new to JTA and currently dive into its specification. I've also created some example projects to dive into this subject faster. I use IBM WebSphere 9 as a runtime.

I've created a simple project consists of EJB and MDB. The idea is that I sending some JMS to the queue, MDB then get this message, processing it and invoke EJB using local interface (both MDB and EJB are located at the same EAR). EJB in its turn will handle incoming object and write it to the Oracle database using JDBC via XA datasource.

MDB onMessage() method has a TransactionAttributeType.NOT_SUPPORTED defined, and as JTA says it should run outside the transaction context.

The process() method of EJB, which is invoked from MDB hasn't any TransactionAttributes defined, and as it comes from JTA it should have a default value which is TransactionAttributeType.REQUIRES_NEW. Thus, if I'm not wrong, it starts a new global TX when being invoked, or am I wrong?

I've also created a simple DAO class, which gets a JDBC connection and runs statement to store the data received from EJB. It is located in a plain Java class at the package next to EJB.

The problem occurs when I try to run the project, to be more specific it occurs when I'm trying to get a connection from the datasource. Since I use the XA datasource, XAER_PROTO exception occurs:

[7/17/18 16:32:52:771 GMT+01:00] 000001b4 WSRdbXaResour E DSRA0304E: XAException occurred. XAException contents and details are: The XA Error is: -6 The XA Error message is: Routine was invoked in an improper context. The Oracle > Error code is: 24776 The Oracle Error message is: Internal XA Error

After some time spent on investigating this issue I've figured out, that this problem is possibly related to this statements from JTA specification:

3.4.7 Local and Global Transactions

...

When using the same connection to perform both local and global transactions, the following rules apply:

• The local transaction must be committed (or rolled back) before starting a global transaction in the connection.

• The global transaction must be disassociated from the connection before any local transaction is started.

So my questions are:

  1. Is an EJB method, annotated with a TransactionAttributeType.REQUIRES_NEW starts a global TX in terms of JTA?

  2. Am I right in my assumptions that retrieving of new JDBC connection from datasource initiates new local transaction in terms of JTA?

  3. If all above is right than is it actually possible to use plain JDBC in EJB under a global TX? Or I should invoke JDBC-related methods only from non-transactional EJBs?

  4. Should I consider the described above approach as a faulty one?

  5. Should I use more abstract JTA interfaces to work with database instead of use of "plain" JDBC methods? If so then which way would be preferable?


Answer:

To answer your questions:

1. Is an EJB method, annotated with a TransactionAttributeType.REQUIRES_NEW starts a global TX in terms of JTA?

Yes, REQUIRES_NEW causes the container to start a new global transaction.

2. Am I right in my assumptions that retrieving of new JDBC connection from datasource initiates new local transaction in terms of JTA?

You are almost right on this one. Retrieving a JDBC connection doesn't actually initiate a transaction. However, doing meaningful work on a JDBC connection (such as createStatement, execute, and so forth) initiates a local transaction, in the absence of a global transaction.

3. If all above is right than is it actually possible to use plain JDBC in EJB under a global TX? Or I should invoke JDBC-related methods only from non-transactional EJBs?

Plain JDBC is perfectly valid from an EJB, whether under a global transaction or not.

4. Should I consider the described above approach as a faulty one?

The scenario as you described ought to work fine. Most likely there is some additional detail that causing the trouble, possibly related to ordering or not committing.

5. Should I use more abstract JTA interfaces to work with database instead of use of "plain" JDBC methods? If so then which way would be preferable?

I would recommend debugging the cause of the problem you are seeing.

If I understand the scenario you described, you invoke getConnection twice, first within a global transaction due to EJB marked REQUIRES_NEW, and subsequently, after that method returns and the transaction commits, the second time you invoke getConnection and use it is for a new local transaction. You should clarify which getConnection attempt is failing, whether there are others, if possible posting snippets from your source code. You should also post the full stack of the error. Lacking these, it means the answers you get are more likely to be guesses than answers. For example, given the Oracle error, I could guess that you might have additional prior usage of the connection in a local transaction that is never committed before attempting to use the connection in a global transaction. It should also be noted that in an application server, connections are pooled, and so prior usage could be from a different thread that did not commit a connection in a local transaction. The application server does its best to detect this sort of thing and clean up for you, but it can't always. So you will also want to review your other usage of connections for any that might not be committing/rolling back under some condition.

Question:

I'm creating a JSF web app on TomEE1.7.2 (over tomcat7, javaEE6). I have JDBC to mysql5.6.23 connection setting which look like below, and it is working just good, for only some hours.

My data access super class:

public class BaseDao {
    @javax.ejb.EJB
    MyEnvironmentService env;

    @javax.persistence.PersistenceContext(unitName = "persistence-unit-stg")
    protected javax.persistence.EntityManager em_stg;

    @javax.persistence.PersistenceContext(unitName = "persistence-unit-prd")
    protected javax.persistence.EntityManager em_prd;

    protected javax.persistence.EntityManager em;

    @javax.annotation.PostConstruct
    private void init(){
        switch (env.getName()){
            case "stg":
                em = em_stg;
                break;
            case "prd":
                em = em_prd;
                break;
            default:
                throw new RuntimeException("Oh no!");
        }
    }
}

My data access class:

@javax.ejb.Stateless
public class MyDao extends BaseDao{
    public MyEntity find(Object id) {
        return em.find(MyEntity.class, id);
    }
}

My META-INF/persistence.xml:

<?xml version="1.0" encoding="UTF-8" ?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
    <persistence-unit name="persistence-unit-stg" transaction-type="JTA">
        <jta-data-source>mysql-jdbc-jta-resource-stg</jta-data-source>
    </persistence-unit>
    <!-- almost same persistence-unit for prd -->
</persistence>

My WEB-INF/resource.xml:

<?xml version='1.0' encoding='UTF-8'?>
<resources>
    <Resource id="mysql-jdbc-jta-resource-stg" type="javax.sql.DataSource">
        JdbcDriver com.mysql.jdbc.Driver
        JdbcUrl jdbc:mysql://db-stg.bar.someRegion.rds.amazonaws.com/someDBname
        UserName username
        password password
        jtaManaged true
    </Resource>
    <!-- almost same Resource for prd -->
</resources>

I have 2 problems that I want to solve:

1. When I start my web app on staging environment, leave it, and mysql "wait_timeout" comes, my web application gets

javax.transaction.RollbackException: Transaction is marked for rollback

and CANNOT reconnect to mysql again forever. I've already tried some JDBC's autoreconnect=true or autoreconnectForPool=true things, did NOT work either, and I feel like it's not the best thing to do, in the first place (feature already deprecated?) .

2. When I start my web app, I see exactly 3 connections connected to mysql, and everytime, ONLY 1 of the connection is seem to be used and extends its timeout, but other 2 are never used and killed just at the "wait_timeout". I tried "initialSize 10", and it comes with 15 connections, and again, ONLY 1 of the connection is used.

I guess there is a really simple solution for them, because it says:

Internally, from TomEE 1.5.0, JDBC pools are managed via Tomcat-pool.

in this page http://tomee.apache.org/common-datasource-configurations.html

Well yes, I DO want to use this Tomcat JDBC pool ( or something alternative is fine.), it doesn't seem to be enabled. May be I'm missing some xml settings or jar or whatever, but I have no idea. please help me out.


Answer:

I'm successfully using a resources.xml in my WEB-INF folder similar to this one in of my production projects:

<?xml version="1.0" encoding="utf-8"?>
<resources>
<Resource id="mysql-jdbc-jta-resource-stg" type="javax.sql.DataSource">
    JtaManaged = true
    DataSourceCreator = tomcat

    validationQuery = SELECT 1
    initialSize = 2
    removeAbandoned = true
    removeAbandonedTimeout = 120

    driverClassName = com.mysql.jdbc.Driver
    url = jdbc:mysql://db-stg.bar.someRegion.rds.amazonaws.com/someDBname
    username = your-username
    password = your-pw
</Resource>
</resources>

One important difference is to use DataSourceCreator = tomcat. This ensures that TomEE creates a pool which takes care for connection validation ("SELECT 1") and removes stalled/outdated connections, thus freeing up resources in the background.

By contrast, the DataSourceCreator = dbpc (which is default when not set: "Default provided pools are DBCP...") behaved as you described/experienced it (timeouts,...). Check the related documentation for further differences.

Also note, that = is used in my working configuration. I discovered that in previous TomEE releases it seemed to work without =. However, I would recommend to configure with = consistently when your target application server is TomEE 1.7.2 or higher.

Further details on this can also be found in the DataSource Configuration page.

Hope it helps.

Question:

@PersistenceContext(unitName = "myPU")
private EntityManager em;

    public void run1()
    {
       // uses em
    }
    public void run2()
    {
       // uses em
    }

As per my understanding each of the methods represent different transactions. I'm having hard times figuring out what the equivalent code using plain JDBC could be,especially when it comes to JDBC connection usage.

Is commit issued at the end of each method ? Is a new connection created and closed for each method ? What does it mean that the implemenation could use a connection pool ?


Answer:

As in most cases: It depends.

In your case, if the methods are being invoked in different transactions, you'd get an EntityManager per bean.

Usually the acquisition of an SQL connection is lazy. So when an EntityManager requires a connection the first time, it will fetch one from the pool you configured. The container then makes sure that the connection is unavailable to other transactions. When the transaction finishes, transactional operations such as commit or rollback are managed by the container, too.

Is commit issued at the end of each method ?

No, at the end of each transaction. If your methods starts new transactions, then yes.

Is a new connection created and closed for each method ?

The same here. If a transaction is in progress and a connection from the same pool has been used before, then it will be reused. Otherwise you wouldn't see your own changes in some other operations. If your methods start new transactions, then yes, you'd get new connections (usually just new wrapper instances around pooled connections to reduce connect/disconnect overheads) and they'd be released at the end of the transaction no matter the outcome.

What does it mean that the implemenation could use a connection pool ?

I think I don't completely understand your question.

Depending on your container, you usually deal with connection pools anyway. So does the EntityManager in your case.

Connection pooling is a way of reusing the same physical connections over and over again to, as mentioned earlier, reduce connect/disconnect overheads.

Please let me know, if something is still unclear.

Question:

I thought this would be really simple but so far I have not managed this. I have a tomcat cluster and I want to assign a master.

I was thinking a race between nodes to lock a table. Whoever locks it is master. The master must now keep that lock. The other nodes should try to acquire the lock...

I don't mind pure JDBC but the application is using JPA and EJB (TomEE)

How to lock? How to hold lock? how to wait to acquire lock indefinitely if already locked?

OK I managed to get it to "wait forever" but now I am scared because when reading the docs I get the impression it should not wait at all so I don't feel like I am in control...

This is my deadlock using Eclipselink:

    Map<String, Object> timeout = new HashMap<>();
    timeout.put("javax.persistence.lock.timeout", 1L);
    master = entityManager.find(Master.class, "MASTER", LockModeType.PESSIMISTIC_READ, timeout);

the timeout flag is also specified in persistence.xml...


Answer:

To get the lock, use javax.persistence.LockModeType.PESSIMISTIC_WRITE and EntityMode's lock() method (or any of the methods that take a lock mode). You can hold the lock by not letting the transaction complete (either via commit or rollback).

To keep retrying the lock, I would loop forever with a try-catch to handle the exceptions as they occur and try again to get the lock. Note that depending on your isolation level, you will probably have to clean up your old transaction and start a new one each time you try to get the lock. Set your lock wait timeout to some reasonable duration (say a minute) or Thread.sleep() at least briefly between attempts to get the lock so you don't waste a lot of resources trying and failing to get the lock on all of the non-master servers.

You will want to make sure that you don't have any timeouts that will kill the connection or transaction prematurely (both for the "master" and all of the non-masters that are trying to become the master).

You will also want to make sure that you properly handle the "master" changing (it will happen eventually) in an automated way, so when another server manages to get the lock it takes over all the "master" business logic.

Question:

I am developing a web application, where, among other things, I need to upload a file to a BLOB column in a mysql table. From what I can see this can be done with JDBC calls (PrepareStatement() etc), but I would like to be able to do this in an EJB class - what I have cobbled together looks like this:

@Stateless
public class ItemsSession {
    @PersistenceContext(unitName ="officePU")
    private EntityManager em;
    private List<Items> itl;
    private static final Logger logger=
            Logger.getLogger(ItemsSession.class.getName());
...
    public String updateDocument(Integer id,InputStream is) throws SQLException{
        String msg="";
        try{
            java.sql.Connection conn = em.unwrap(java.sql.Connection.class);
            PreparedStatement pstmt=conn.prepareStatement("UPDATE Documents SET doc = ? WHERE id = ?");
            pstmt.setBinaryStream(1, is);
            pstmt.setLong(2, id);
            pstmt.executeUpdate();
            pstmt.close();
        }
        catch (PersistenceException e){
            msg=e.getMessage();
        }

        return msg;
    }
...
}

I have two questions, though:

  • I would like not to use JDBC directly - is there a way to do this that is 'pure JPA' (edit: not EJB)?

  • If I have to do it this way, is the PreparedStatement included in the container managed transaction?


Another edit: the code above does the job - I have now tested it. But it isn't pretty, I think.


Answer:

The first thing you have to do to persist BLOB values the JPA way is you define an entity. The following an example pseodo code:

@Entity
public class Documents {
    @Id
    private Long id;

    @Lob
    private byte[] doc;

    // .... getters + setters 
}

Then you modify your EJB as follows:

@Stateless
public class ItemsSession {
    @PersistenceContext(unitName ="officePU")
    private EntityManager em;
    // ... the rest of your code

    public String updateDocument(Integer id,InputStream is) throws SQLException{
        String msg = "";
        Documents docs = em.find(Documents.class, id); // fetch record from DB
        // Assuming your InputStream is a ByteArrayInputStream
        byte[] doc = new byte[is.available()]; // create target array
        is.read(doc, 0, doc.length);  // read bytes into byte array
        docs.setDoc(doc); // 

        return msg; // returning exception message from method call?????
    }
    ...
}

If you don't change the defaults EJB methods are invoked in a transaction by default. So when your method exits, the update should be synchronized with the database.

This answer kann only help you if you read and understand the basics of the JPA. And here is an official tutorial to JPA persistence among other lots of tutorials on the web.

Update

I would like not to use JDBC directly - is there a way to do this that is 'pure JPA'

No.

If I have to do it this way, is the PreparedStatement included in the container managed transaction?

No. But you can use bean managed transaction. If you want to use BMT, the following pseudocode might help you:

@Stateless
@TransactionManagement(TransactionManagementType.BEAN)
public class ItemsSession {

    @Resource UserTransaction ut;
    @Resource DataSource datasource;  // you should define datasource on your application server

    ...
    public String updateDocument(Integer id,InputStream is) throws SQLException{
        // ...
        try (java.sql.Connection conn = datasource.getConnection();
            PreparedStatement pstmt=conn.prepareStatement("UPDATE Documents SET doc = ? WHERE id = ?")) {

            pstmt.setBinaryStream(1, is);
            pstmt.setLong(2, id);

            ut.begin();
            pstmt.executeUpdate();
            ut.commit();

        } catch (PersistenceException e){
            // ... error handling
        }

        return ...;
    }
...
}

Question:

I'm using Spring JdbcTemplate on one of my projects and now, when there are really very much requests with it - I started to face this exception:

org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; 
uncategorized SQLException for SQL [{? = call API.get_data_for_nb(?, ?)}]; 
SQL state [99999]; error code [17009]; Closed Statement; 
nested exception is java.sql.SQLException: Closed Statement

So the Closed Statement exception is received when you try to execute statement that is already closed, but in my case I don't close it by myself - I use JdbcTemplate exactly for that. So, firstly, what could be the reason for that?

The JdbcTemplate object itself is contained in @Stateless EJB in this way:

@Stateless(name = "NbEdwServiceEJB")
public class NbEdwServiceBean implements NbEdwServiceLocal, NbEdwServiceRemote {

    @Resource(mappedName = JNDI)    
    private DataSource dataSource;

    private static volatile JdbcTemplate jdbcTemplate;

    @PostConstruct
    protected void construct() {
        synchronized (NbEdwServiceBean.class) {
            if (jdbcTemplate == null) {
                jdbcTemplate = new JdbcTemplate(dataSource);
                jdbcTemplate.setResultsMapCaseInsensitive(true);
            }
        }
    }

    private String getDataFromDB(final String request, final int isDigitalSignVerified) {
        String response = null;
        try {
            response = jdbcTemplate.execute(SQL_GET_DATA, new CallableStatementCallback<String>() {
                public String doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
                    cs.registerOutParameter(1, Types.VARCHAR);
                    cs.setInt(2, isDigitalSignVerified);
                    cs.setString(3, request);
                    cs.executeUpdate();
                    return cs.getString(1);
                }
            });
        } catch (DataAccessException ex) {
            LOGGER.error("getDataFromDB()", ex);
        }

        return response;
    }
}

I know that this is maybe not the strictly right way to do it, I could just create instance of JdbcTemplate for every stateless bean - so I might do just that. So, secondly, why is this ever happening? My suppose was that JdbcTemplate's execute method isn't thread safe, but can someone give the full explanation on what is going on?

I have JEE version 5 running on WebLogic 10.3.5 if it's matter.


Answer:

@Tolegen Izbassar I'm sorry that you're stuck with EE5.

Concerning the Singleton and EE5 there were some alternatives out there. One is vendor specific extensions, for example JBoss 5.x had service beans providing Singleton+JMX. A second solution is to use a earlier version of Jboss Seam compatible with EE5. A third alternative is to use the ServerContext from the Servlet API.

What you're trying to do in @PostConstuct is definitely not good. Non final statics in SLSB is a no go.

I suggest to have a look, at section 29.3 from Spring framework reference which describes EJB - Spring integration, an example from that section:

@Stateless
@Interceptors(SpringBeanAutowiringInterceptor.class)
public class MyFacadeEJB implements MyFacadeLocal {
// automatically injected with a matching Spring bean
@Autowired
private MyComponent myComp;
// for business method, delegate to POJO service impl.
public String myFacadeMethod(...) {
return myComp.myMethod(...);
}

Question:

I have Enterprise Appilcation Solution in Neon Eclipse, which envelope EJB and Servlet projects and deployed to Wildfly 10 server. Servlet calls EJB for access DB. If I put JDBC driver in WEB-INF folder of Servlet project, all is going well, but if I move the code to EJB and link the driver as external JAR (see screenshot), I get error:

 java.lang.ClassNotFoundException: com.mysql.jdbc.Driver from [Module "deployment.NeoflexBank.ear.Neoflex.jar:main" from Service Module Loader]

Here is the code I use for access the DB:

    public String test() {
        String output = "";

        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        } catch (Exception e) {
            e.printStackTrace();
        }

        String url="jdbc:mysql://localhost:3306/neoflex";
        String username="root";
        String password="";
        String query="select * from clients";
        Connection conn;
        try {
            conn = (Connection) DriverManager.getConnection(url, username, password);
            Statement stmt = (Statement) conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);

            while(rs.next())
            {
                output += rs.getInt("id");
                output += rs.getString("username");
                output += rs.getString("birth_date");
                output += rs.getString("name");
                output += rs.getString("surename");
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            return e.toString();
        }

        return output;
    }


Answer:

The way to do this in a Java EE server is to define a DataSource and then use that for getting JDBC connections:

  1. move the mysql-connector-java-5.1.40-bin.jar into the standalone\deployments directory of your WildFly installation.

  2. start up your WildFly server and open the admin console at http://localhost:9990. You may have to follow some instructions to set up a bit of security here.

  3. click on the Configuration tab, and then click on:

    a. Subsystems

    b. Datasources

    c. Non-XA

  4. Click the blue Add button and follow the prompts.

Now, the code in your EJB will look a bit like:

@Stateless
public class SomeEJB {

   @Resource(name="java:/MySqlDS")
   private DataSource ds;

   public String test() {
        String output = "";

        String query="select * from clients";

        try {
            try (Connection conn = ds.getConnection();
                 Statement stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery(query)) {

                while(rs.next())
                {
                    output += rs.getInt("id");
                    output += rs.getString("username");
                    output += rs.getString("birth_date");
                    output += rs.getString("name");
                    output += rs.getString("surename");
                }
            } 
        } catch (SQLException e) {
            return e.toString();
        }

        return output;
    }
}

This decouples your application from the database configuration. The DataSource configuration can also be done from a command line script.