3 Replies Latest reply on Nov 13, 2013 2:02 PM by wdfink

    How to connect to Mysql table in java?

    ravitadi

      I am hosting a spring application on JBoss7.1AS standalone on my dev box and mysql on OpenShift.

      Using port forwarding, i am able to connect to mysql.

       

      The issue is when i am trying to connect to mysql in through a java class.

      I don't want to use any beans! There is not point for me to use it, if my server connects to mysql db fine.

       

      How do i correctly call the mysql connection in my java file, which MYSQL dv connected in the standalone.xml?

       

      Here is everything

       

       

      STANDALONE.XML on my JBoss 7.1 AS

       

       

                   <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</connection-url>

                              <driver>h2</driver>

                              <security>

                                  <user-name>sa</user-name>

                                  <password>sa</password>

                              </security>

                          </datasource>

                          <datasource jndi-name="java:jboss/datasources/MysqlDS" pool-name="MysqlDS" enabled="true" use-java-context="true">

                              <connection-url>jdbc:mysql://127.0.0.1:3307/spring</connection-url>

                              <driver>mysql</driver>

                              <security>

                                  <user-name>admin</user-name>

                                  <password>XXXXXX</password>

                              </security>

                          </datasource>

                          <drivers>

                              <driver name="h2" module="com.h2database.h2">

                                  <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>

                              </driver>

                              <driver name="mysql" module="com.mysql"/>

                          </drivers>

                      </datasources>

       

       

      PERSISTANCE.XML

       

       

          <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="primary">

                <!-- If you are running in a production environment, add a managed

                   data source, this example data source is just for development and testing! -->

                <!-- The datasource is deployed as WEB-INF/spring-quickstart-ds.xml, you

                   can find it in the source at src/main/webapp/WEB-INF/spring-quickstart-ds.xml -->

                <jta-data-source>java:jboss/datasources/MysqlDS</jta-data-source>

                <properties>

                   <property name="jboss.entity.manager.factory.jndi.name" value="java:jboss/spring-quickstart/persistence" />

                   <!-- Properties for Hibernate -->

                   <property name="hibernate.hbm2ddl.auto" value="create-drop" />

                   <property name="hibernate.show_sql" value="false" />

                </properties>

             </persistence-unit>

          </persistence>

       

       

      SPRING-DS.XML (I don't know why i have to put it in here?, is this nessary? my server is already conencted to the mysql)

       

       

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

          <datasources xmlns="http://www.jboss.org/ironjacamar/schema">

            <datasource jndi-name="java:jboss/datasources/MysqlDB" pool-name="MysqlDS">

                <connection-url>jdbc:mysql://localhost:3307/spring</connection-url>

                <driver>mysql</driver>

            </datasource>

          </datasources>

       

       

      Memberclass.java

       

       

          package pagehelpers;

         

          import java.sql.Connection;

          import java.sql.PreparedStatement;

          import java.sql.SQLException;

         

          import javax.annotation.Resource;

          import javax.sql.DataSource;

         

          import pagemodels.MemberModel;

         

          public class MemberSignupHelper {

         

          public interface Members{

              public void insert(MemberModel member);

            }

        

             @Resource(mappedName = "java:jboss/datasources/MysqlDS")

             private DataSource dataSource;

            

             public void set(DataSource dataSource) {

              this.dataSource = dataSource;

             }

            

             public void insert(MemberModel member) {

              String sql = "INSERT INTO CUSTOMER (name, email) VALUES (?,?)";

              Connection conn = null;

            

              try {

            

          conn = dataSource.getConnection();

          PreparedStatement ps = conn.prepareStatement(sql);

          ps.setString(1, member.getName());

          ps.setString(2, member.getEmail());

          ps.executeUpdate();

          ps.close();

          

          } catch (SQLException e) {

          throw new RuntimeException(e);

          

          } finally {

          if (conn != null) {

          try {

          conn.close();

          } catch (SQLException e) {}

          }

          }

             }

          }

       

       

       

       

      ERROR: NullpointerException because it won't get connection to my mysql database.

      What am i doing wrong?

        • 1. Re: How to connect to Mysql table in java?
          wdfink

          At which line the NPE is thrown? Is dataSource not injected? Do you see any ERROR/WARN in the logfile?

          Also I would avoid injection and a setter method.

          • 2. Re: How to connect to Mysql table in java?
            ravitadi

            I used the Jboss admin web console to check if the my database is connect and it says it is.

            So i am just having hard time making a connection. Because i configure my changes in my standalone.xml, i am not properly sure how to call it in my class.

            Can you show me an example without a injection and setting method?

             

            This is the error I get when i tried to connect to it through the class?

             

            [org.apache.catalina.core.ContainerBase.[jboss.web].[default-host].[/spring-eap6-quickstart].[Mitais]] (http-localhost-127.0.0.1-8080-3) Servlet.service() for servlet Mitais threw exception: java.lang.NullPointerException

              at pagehelpers.MemberSignupHelper.insert(MemberSignupHelper.java:31) [classes:]

              at pagecontrollers.HomePageController.getSignupMembers(HomePageController.java:34) [classes:]

              at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_45]

              at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [rt.jar:1.7.0_45]

              at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_45]

              at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_45]

              at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:213) [spring-web-3.1.1.RELEASE.jar:3.1.1.RELEASE]

              at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:126) [spring-web-3.1.1.RELEASE.jar:3.1.1.RELEASE]

              at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:96) [spring-webmvc-3.1.1.RELEASE.jar:3.1.1.RELEASE]

              at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:617) [spring-webmvc-3.1.1.RELEASE.jar:3.1.1.RELEASE]

              at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:578) [spring-webmvc-3.1.1.RELEASE.jar:3.1.1.RELEASE]

              at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80) [spring-webmvc-3.1.1.RELEASE.jar:3.1.1.RELEASE]

              at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:923) [spring-webmvc-3.1.1.RELEASE.jar:3.1.1.RELEASE]

              at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852) [spring-webmvc-3.1.1.RELEASE.jar:3.1.1.RELEASE]

              at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882) [spring-webmvc-3.1.1.RELEASE.jar:3.1.1.RELEASE]

              at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:778) [spring-webmvc-3.1.1.RELEASE.jar:3.1.1.RELEASE]

              at javax.servlet.http.HttpServlet.service(HttpServlet.java:734) [jboss-servlet-api_3.0_spec-1.0.0.Final.jar:1.0.0.Final]

              at javax.servlet.http.HttpServlet.service(HttpServlet.java:847) [jboss-servlet-api_3.0_spec-1.0.0.Final.jar:1.0.0.Final]

              at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:329) [jbossweb-7.0.13.Final.jar:]

              at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:248) [jbossweb-7.0.13.Final.jar:]

              at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:275) [jbossweb-7.0.13.Final.jar:]

              at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:161) [jbossweb-7.0.13.Final.jar:]

              at org.jboss.as.jpa.interceptor.WebNonTxEmCloserValve.invoke(WebNonTxEmCloserValve.java:50) [jboss-as-jpa-7.1.1.Final.jar:7.1.1.Final]

              at org.jboss.as.web.security.SecurityContextAssociationValve.invoke(SecurityContextAssociationValve.java:153) [jboss-as-web-7.1.1.Final.jar:7.1.1.Final]

              at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:155) [jbossweb-7.0.13.Final.jar:]

              at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102) [jbossweb-7.0.13.Final.jar:]

              at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) [jbossweb-7.0.13.Final.jar:]

              at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:368) [jbossweb-7.0.13.Final.jar:]

              at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:877) [jbossweb-7.0.13.Final.jar:]

              at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:671) [jbossweb-7.0.13.Final.jar:]

              at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:930) [jbossweb-7.0.13.Final.jar:]

              at java.lang.Thread.run(Thread.java:744) [rt.jar:1.7.0_45]

            • 3. Re: How to connect to Mysql table in java?
              wdfink

              You might remove the Datasource injection and setter and use

              new InitialContext().lookup("java:jboss/datasources/MysqlDS")

              to retrieve the DS