6 Replies Latest reply on Sep 7, 2009 6:02 PM by maxandersen

    How to create a JDBC connection

    oliverroell

      Hi,

      I am trying to use a JDBC-connection to a oracle-database in a simple web-application (only servlets & JSPs) with Eclipse 3.4.2 and JBoss Tools 3.

      Is there a tutorial on the web how I can create a JDBC-connection with the JBoss Tools?

      A few years ago I have used the chapter "Connecting to a real database" from the book "JBoss - A Developer's Notebook": Creating an oracle-ds.xml, deploying it under server/default/deploy and putting a datasource-tag within the jbosscmp-jdbc.xml file.
      Is this still a valid approach or is this outdated with JBoss Tools 3?

      I think I should use the "Data Source Explorer" and the "Database Connections" instead, but I have no clue how to do this.

      Does exist an easy way to create a simple JDBC-connection for my DAO within a web-app?

        • 1. Re: How to create a JDBC connection
          peterj

          The oracle-ds.xml file is still needed, the one described in Developer's Notebook is probably still accurate (no major changes have gone into *-ds.xml files since 4.0).

          You should look into using JPA (EJB3-style entity beans) for your database access. Not sure of the exact steps to do this using Tools. Do you have an existing database schema, or will you be generating the schema based on your persistent objects?

          • 2. Re: How to create a JDBC connection
            oliverroell

            I know that JPA is the future of Java's database-access, but I want to use JDBC in my servlet.
            There is an existing oracle-database from our SAP-system and I want to read customer-information with a select.

            The "JMX Agent View" from JBoss displays under "jboss.jdbc":

            datasource=DefaultDS,service=metadata
            datasource=OracleDS,service=metadata

            I think the deploying of the oracle-datasource was successful.

            The book "EJB 3 in action" wrotes on page 148:
            Moreover, application servers allow you to explicitly specify a global JNDI name using the mappedName parameter of the @Resource annotation. For example, if you are using the JBoss Application Server and you have a data source with a global JNDI name of java:/DefaultDS, you can specify the resource mapping as follows:

            @Resource(name="jdbc/ActionBazaarDS", mappedName="java:/DefaultDS")
            private javax.jdbc.DataSource myDB;
            


            I do not know why the DataSource from "EJB 3 in action" belongs to the package javax.jdbc
            I am working with the DataSource from the package javax.sql

            In my servlet I have tried the following:

            @Resource(name="jdbc/OracleDS", mappedName="java:/OracleDS")
            private javax.sql.DataSource ds;
            ...
            Connection con = ds.getConnection();
            Statement statement = con.createStatement();
            ResultSet resultSet = statement.executeQuery("select...");
            


            The ds object is always null. JBoss does not recognize the @Resource-annotation.

            Do I miss something?

            Do I still need a jbosscm-jdbc deployment descriptor in JBoss 4.2.3 when I work with annotations?



            • 3. Re: How to create a JDBC connection
              oliverroell

              Now I have tried the old school approach:

              Context context = new InitialContext();
              DataSource ds = (DataSource) context.lookup("java:OracleDS");
              


              It works. After fighting some hours with JBoss it really works.

              But something must be wrong with my first dependency injection attempt.

              The following annotations are all invalid and create null-values:

              @Resource(name="jdbc/OracleDS", mappedName="java:OracleDS")
              private DataSource ds1;
              
              @Resource(name="java:OracleDS")
              private DataSource ds2;
              
              @Resource(name="java/OracleDS")
              private DataSource ds3;
              
              @Resource(name="jdbc:OracleDS")
              private DataSource ds4;
              
              @Resource(name="jdbc/OracleDS")
              private DataSource ds5;
              


              Does anybody know what's wrong with the Resource-annotations?
              Do they work only within EJBs and not in servlets?


              • 4. Re: How to create a JDBC connection
                peterj

                I was going to ask which version of JBoss AS because your original post did not say, so I just assumed you were using the latest (5.1.0). But you finally stated that you are using 4.2.3 - the @Resource annotation does not work in 4.2.3.

                • 5. Re: How to create a JDBC connection
                  oliverroell

                  I have tested it with JBoss 5.1.0 and the @Resource annotation works.

                  One thing is strange: At the time, "EJB 3 in Action" was written there was no JBoss 5 available. But if "EJB 3 in Action" was created on the basis of JBoss 4.2, and this version don't understand the @Resource annotation, the authors should not write to use @Resource with the JBoss AS.

                  I think, I should buy and read the book "JBoss in Action". It seems that one of the authors is very competent and experienced.

                  Thank you for your help.

                  • 6. Re: How to create a JDBC connection
                    maxandersen

                    Oliver, as you probably have realized by now "creating JDBC connection" doesn't really have anything to do with Tools if the only thing you want to do is to create a datasource - that is done by creating a ds.xml file as you already know.