java - Is it a good idea to put jdbc connection code in servlet class?

Question

I am new in servlet and database.I have a core.java file in which I have a JDBC connection code.In the same directory I have a coreServlet.java file which take form value from HTML page.My questions are:

1. How can I make interaction between two java classes,for example how can I use variables that I got from HTML form in servlet(coreServlet.java) to my core.java file so that I can store that in my database ?
2. Is it is a better Idea to put my jdbc connection code in coreServlet.java ?

My project structure is like:

  - aarya(project name)   
        |
         - WEB-INF
                 |
        |         -web.xml
        -src
           |
          -pkg
           |
            -CoreServlet.java(servlet to interact HTML form)
            -Main.java
            -Core.java(jdbc code is here to interact database)
        |
        -html
            |
            - core.html
        |
        - css
             |
             -core.css

        |
        -javascript
                  |
                   -core.js
        |
        - lib
        |
        -index.html

Answer 1

The database connection data should be in your JNDI Data Source and it would be better if you use a Connection Pool but never in a class. Since yours is a Web Application, note that the connection pool configuration depends heavily on the web application server.

As examples, this is very well explained in Tomcat 7 Database Connection Pool configuration and JBoss 7 Database Connection Pool configuration (there are other steps to configure the database connection pool on GlassFish and other Web application server, note that this is different on each server).

From both examples, you can see that you will have a XML file where you put the connection attributes: database URL, user, password, min and max pool connection size (how many connections to database will be open)

• Tomcat way:

  <Resource name="jdbc/ProjectX" auth="Container"
      type="javax.sql.DataSource" driverClassName="com.mysql.jdbc.Driver"
      url="jdbc:mysql://localhost:3306/projectx"
      username="user" password="password" maxActive="20" maxIdle="10" maxWait="-1"/>
  

• JBoss way:

  <datasource jndi-name="jdbc/ProjectX" pool-name="MySqlDS">
          <connection-url>jdbc:mysql://localhost:3306/projectx</connection-url>
          <driver>com.mysql</driver>
          <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
          <pool>
              <min-pool-size>10</min-pool-size>
              <max-pool-size>100</max-pool-size>
              <prefill>true</prefill>
          </pool>
          <security>
              <user-name>user</user-name>
              <password>password</password>
          </security>
          <statement>
              <prepared-statement-cache-size>32</prepared-statement-cache-size>
              <share-prepared-statements/>
          </statement>
  </datasource>
  <drivers>
      <driver name="com.mysql" module="com.mysql">
          <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>
      </driver>
  </drivers>
  

In the end, if you have configured your database connection pool and it works, all you have to do in your code to recover the connection is call InitialContext#lookup to recover the resource using its JNDI resource name.

Knowing this, after configuring a JNDI resource to connect to MySQL database with name "jdbc/ProjectX", you can have a class which recovers the Connection like this:

public class DatabaseConnectivity {
    public static Connection getConnection() throws NamingException, SQLException {
        InitialContext cxt = new InitialContext();
        DataSource ds = (DataSource) cxt.lookup("java:/comp/env/jdbc/ProjectX" );
        return ds.getConnection();
    }
}

By the way, I would use different names for packages to know the group of functionality of the classes. For example:

src
- edu.home.controller.servlet
  + CoreServlet.java
- edu.home.controller.filter
  + SessionFilter.java
- edu.home.model.entity
  + AnEntity.java
  + AnotherEntity.java
- edu.home.model.database
  + DatabaseConnectivity.java
- edu.home.model.service
  + AnEntityService.java
  + AnotherEntityService.java
(and on and on...)