Hot questions for Using Enterprise JavaBeans in mysql

Question:

I've got a client, a server and a database. The client communicates with the server via a EJB remote interfaces. As the server - I use a Wildfly 8.2.0. As the database - I use a MySQL. The server communicates with the MySQL via a JPA/Hibernate. When I turn off the MySQL server - the Wildfly throws an exception, of course. But when I turn on the MySQL again - the Wildfly still throws the same ERROR. I've to turn off the Wildfly and turn it back that the Wildfly reconnect to the database.

How to set auto reconnect in the Wildfly?

I tried to set auto reconnect in a connection URL: jdbc:mysql://localhost/db?autoReconnect=true&amp;useUnicode=yes&amp;characterEncoding=UTF8 and i tried to add to the standalone-full.xml file which i use, this line: <check-valid-connection-sql>select 1</check-valid-connection-sql>, but both solutions don't work.

standalone-full.xml:

<!-- ... -->
<datasource jta="true" jndi-name="java:jboss/datasources/MySQLDS" pool-name="MySQLDS" enabled="true" use-ccm="true">
    <connection-url>jdbc:mysql://localhost/db?autoReconnect=true&amp;amp;useUnicode=yes&amp;amp;characterEncoding=UTF8</connection-url>
    <driver-class>com.mysql.jdbc.Driver</driver-class>
    <driver>mysqlDriver</driver>
    <security>
        <user-name>user</user-name>
        <password>***</password>
    </security>
    <validation>
        <check-valid-connection-sql>select 1</check-valid-connection-sql>
        <validate-on-match>false</validate-on-match>
        <background-validation>false</background-validation>
    </validation>
    <timeout>
        <set-tx-query-timeout>false</set-tx-query-timeout>
        <blocking-timeout-millis>0</blocking-timeout-millis>
        <idle-timeout-minutes>0</idle-timeout-minutes>
        <query-timeout>0</query-timeout>
        <use-try-lock>0</use-try-lock>
        <allocation-retry>0</allocation-retry>
        <allocation-retry-wait-millis>0</allocation-retry-wait-millis>
    </timeout>
    <statement>
        <share-prepared-statements>false</share-prepared-statements>
    </statement>
</datasource>
<drivers>
    <driver name="mysqlDriver" module="com.mysql">
        <xa-datasource-class>com.mysql.jdbc.Driver</xa-datasource-class>
    </driver>
</drivers>
<!-- ... -->

Answer:

This working on Wildfly 8.1:

         <datasource jta="true" jndi-name="java:jboss/datasources/xxxdb" pool-name="xxxxDB" enabled="true" use-ccm="false">
          <connection-url>jdbc:mysql://localhost:3306/xxxdb?autoReconnect=true&amp;amp;useUnicode=true&amp;amp;characterEncoding=UTF-8</connection-url>
          <driver-class>com.mysql.jdbc.Driver</driver-class>
          <driver>mysql-connector-java-5.1.26-bin.jar</driver>
          <security>
              <user-name>xxxuser</user-name>
              <password>xxxpassword</password>
          </security>
          <validation>
              <check-valid-connection-sql>select 1</check-valid-connection-sql>
              <validate-on-match>false</validate-on-match>
              <background-validation>true</background-validation>
              <background-validation-millis>10000</background-validation-millis>
          </validation>
          <statement>
              <share-prepared-statements>false</share-prepared-statements>
          </statement>
        </datasource>

Question:

i have a query which will return single record with 2 columns from Table i want to get the result to a List each element hold a column value , but i keep getting ClassCastExceptoion this is the code :

public List<String> getStatus(Long requestId) {
    List result = new ArrayList();
    if (requestId != null)

    {
        StringBuilder querySBuilder = new StringBuilder();
        querySBuilder.append(" select R.request_status_id , L.request_status_desc ");
        querySBuilder.append(" from Table1 R join  Table2 L ");
        querySBuilder.append(" on R.request_status_id = L.REQUEST_STATUS_Id ");
        querySBuilder.append(" where R.REQUEST_ID =  " + requestId);
        System.out.print(querySBuilder.toString());
        List resultList =
           em.createNativeQuery(querySBuilder.toString()).getResultList();
        Vector resultVec = (Vector)resultList.get(0);

        int id = ((BigDecimal)resultVec.elementAt(0)).intValue();
        String statusName = ((String)resultVec.elementAt(0));

        System.out.println("id" + id);
        System.out.println("name " + statusName);
        result.add(id);
        result.add(statusName);
        if (resultVec == null || resultVec.isEmpty()) {
            return new ArrayList<String>();
        }
        return result;


    }

    return null;
}

Answer:

The pattern I would use would be to phrase the incoming native result set as a List<Object[]>, where each object array represents a single record:

Query q = em.createNativeQuery(querySBuilder.toString());
List<Object[]> result = q.getResultList();

for (Object[] row : result) {
    int id = (Integer)row[0];
    String statusName = (String)row[1];

    // do something with the id and statusName from above
}

Question:

So the problem. I need some mechanism to block entities in data source. For example if someone open some article in writing mode then others can't open this article in writing mode, but only in reading mode.

I see only two methods:

  1. in table create some column (ex:isBlocked) and marked it as blocked
  2. use EJB singleton and keep in array opened articles id.

What is better? What are the other ways to solve such problem?


Answer:

For first option, downside is that if someone opens the article in edit mode and closes the browser without releasing it, that article will stay locked. You could add another column, like lockTime and calculate some timeout based on it, after which the article will become unlocked again.

Second option has the same downside as first, plus it wouldn't be that easy in a clustered environment (you would have to synchronize singletons across different servers in a cluster).

In JPA you also have optimistic locking (using @Version) and pesimistic locking (using entityManager.lock()), see this or this for examples, so maybe that can help you also.

Question:

Hi I use IDE Eclipse Luna,JavaSE8.40,WildFly8.2,MySQL5.6.23 and Windows7 32bit. I would like with @Stateful bean connect do MySQL server. When I use datasource it´s work correctly(I create datasource throw webconsole). I would like connect to database without datasource.I have a problem with load the driver.

This work correctly

    public class ServerBean03 implements Rozhrani03 {
        private Context ctx;
        private DataSource dataSource;
        private Connection pripoj;
        private Statement dotaz;
        private ResultSet vysledek;
        @PostConstruct
        private void init(){
            System.out.println("Metoda Init()");
            try {
                ctx = new InitialContext();
                dataSource = (DataSource) ctx.lookup("java:jboss/jdbc/MySQLcon");           
                pripoj = dataSource.getConnection();                
                dotaz = pripoj.createStatement();           }                               
            catch (SQLException | NamingException e) {
                System.out.println("Nepodarilo se vyrobit novou databazi");
                e.printStackTrace();    }   }

This work correctly too

public class ServerBean02 implements Rozhrani02 {
    private Connection pripoj;
    private Statement dotaz;
    private ResultSet vysledek;
    @Resource(lookup="java:jboss/jdbc/MySQLcon")  // nebo  @Resource(name="MySQLcon")
    private javax.sql.DataSource dataSource;
    @PostConstruct
    private void init(){
        System.out.println("Metoda Init()");
        try {
            pripoj = dataSource.getConnection();                        
            dotaz = pripoj.createStatement();   }                       
        catch (SQLException e) {
            System.out.println("Nepodarilo pripojit na novou databazi");
            e.printStackTrace();    }   }

This not work

public class ServerBean01 implements Rozhrani01{
    private Connection pripoj;
    private Statement dotaz;
    @PostConstruct
    private void init(){
        System.err.println("Pokusime se nacist driver");
        try {
            Class.forName("com.mysql.jdbc.Driver"); }       //  Here the Bean is crash 
        catch (ClassNotFoundException e) {
            System.err.println("Nepodarilo se nacist Driver");
            e.printStackTrace();    } 
        System.err.println("Pokusime se pripojit na databazi");
        try {
            pripoj = DriverManager.getConnection("jdbc:mysql://localhost/test", "Java2", "asdasdasd");      
            dotaz = pripoj.createStatement();   }                                                           
        catch (SQLException e) {
            System.err.println("Nepodarilo se pripojit k databazi");
            e.printStackTrace();    }   }

WildFly8.2 I install in ProgramFiles. I create few folders in folder module. It´s look like so : C:\Program Files\JavaEE WildFly8\modules\com\mysql\main 1) I copy there MySQL connector - mysql-connector-java-5.1.34-bin.jar - to folder main 2) I create module.xml - in folder main

<?xml version="1.0" encoding="UTF-8"?>
<module xmlns="urn:jboss:module:1.3" name="com.mysql">
    <resources>
        <resource-root path="mysql-connector-java-5.1.34-bin.jar"/>
    </resources>
    <dependencies>
        <module name="javax.api"/>
        <module name="javax.transaction.api"/>
    </dependencies>
</module>

3) I edit the C:\Program Files\JavaEE WildFly8\standalone\configuration\standalone.xml

<subsystem xmlns="urn:jboss:domain:datasources:2.0">
        <datasources>
            <datasource jndi-name="java:jboss/datasources/ExampleDS" pool-name="ExampleDS" enabled="true" use-java-context="true">
                <connection-url>jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE</connection-url>
                <driver>h2</driver>
                <security>
                    <user-name>sa</user-name>
                    <password>sa</password>
                </security>
            </datasource>
            <datasource jta="true" jndi-name="java:jboss/jdbc/MySQLcon" pool-name="MySQLcon" enabled="true" use-ccm="true">
                <connection-url>jdbc:mysql://localhost:3306/test</connection-url>
                <driver-class>com.mysql.jdbc.Driver</driver-class>
                <driver>mysql-connector-java-5.1.34-bin.jar_com.mysql.jdbc.Driver_5_1</driver>
                <security>
                    <user-name>Java2</user-name>
                    <password>asdasdasd</password>
                </security>
                <validation>
                    <validate-on-match>false</validate-on-match>
                    <background-validation>false</background-validation>
                </validation>
                <timeout>
                    <set-tx-query-timeout>false</set-tx-query-timeout>
                    <blocking-timeout-millis>0</blocking-timeout-millis>
                    <idle-timeout-minutes>0</idle-timeout-minutes>
                    <query-timeout>0</query-timeout>
                    <use-try-lock>0</use-try-lock>
                    <allocation-retry>0</allocation-retry>
                    <allocation-retry-wait-millis>0</allocation-retry-wait-millis>
                </timeout>
                <statement>
                    <share-prepared-statements>false</share-prepared-statements>
                </statement>
            </datasource>
            <drivers>
                <driver name="h2" module="com.h2database.h2">
                    <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>
                </driver>
                <driver name="mysqlDriver" module="com.mysql">
                    <xa-datasource-class>com.mysql.jdbc.Driver</xa-datasource-class>
                    <driver-class>com.mysql.jdbc.Driver</driver-class>
                </driver>
            </drivers>
        </datasources>
    </subsystem>

Have I do something bad? Or have I forgot something?


Answer:

You need to add a driver module dependency to your application in a jboss-deployment-structure.xml as described in Class Loading in WildFly

Eg:

<?xml version="1.0" encoding="UTF-8"?>
<jboss-deployment-structure>
    <deployment>
        <dependencies>
            <module name="com.mysql" />
        </dependencies>
    </deployment>
</jboss-deployment-structure>