Does the TransactionAttributeType Required has an impact on JDBC-calls?

[Oliver Rensen]

Hi ranchers,

I have a question regarding transactions in Session-Beans.

There is the following method in a Stateless Session-Bean:

    @TransactionAttribute(TransactionAttributeType.REQUIRED)
    public List bookProdOrders(String field1, String field2) {
        try {
            return new DataAccessObject().bookProdOrders(field1, field2);
        } catch (Exception ex) {
            throw new EJBException("Error occurred.\n"
                    + ex.getMessage());
        }
    }

The class DataAccessObject is a POJO.

The method bookProdOrders() uses JDBC for the database-access:

    public List bookProdOrders(
            String field1, String field2) throws Exception {

        Context context = null;
        Connection con = null;
        String s = "", t = "";
        Statement statement = null;
        ResultSet resultSet = null;
        List retval = new ArrayList();

        try {
            context = new InitialContext();
            DataSource ds = (DataSource) context.lookup(JDBC_CONNECTION);
            con = ds.getConnection();
            statement = con.createStatement();
            resultSet = statement.executeQuery(
                    "select aufnr,budat,schicht,team,sum(gutmenge) from table "
                    + "where mandt='100' and werks='"
                    + field1 + "' and palnr='" + field2 + "' and booked=' ' "
                    + "group by aufnr,budat,schicht,team "
                    + "order by aufnr,budat,schicht,team");

            while (resultSet.next()) {
                // Evaluate ResultSet
            }

            resultSet.close();

            // Do some business-logic.

            statement.executeUpdate(
                    "update table set booked='X' "
                    + "where mandt='100' and werks='"
                    + field1 + "' and palnr='" + field2 + "' and booked=' '");

        } catch (Exception ex) {
            throw new RemoteException(
                    "bookProdOrders(): Error occurred.\n"
                    + ex.getMessage());
        } finally {
            try {
                if (statement != null) { statement.close(); }
                if (con != null) { con.close(); }
                if (context != null) { context.close(); }
            } catch (Exception ex) {
                throw new RemoteException(
                        "bookProdOrders(): Error occurred.\n"
                        + ex.getMessage());
            }
        }

        return retval;
    }

In the first SQL-statement I read all records where the booked-field is not set.

Then I do some business-logic.

At the end I make an SQL-update on the same table and set a value into the booked-field, so that this records will not be booked twice.

Is this approach transaction-safe?

What would happen when two or more users call the SLSB-method bookProdOrders() on the same time?
Would the two or more SLSB-method-calls on bookProdOrders() be handled sequentially from JBoss?

Does the TransactionAttributeType Required has an impact on the JDBC-calls?

Kind regards
Oliver