A database query using tomcat & servlets & JNDI

controller: PersonRead.java

package web;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

import org.apache.log4j.Level;
import org.apache.log4j.Logger;

public class PersonRead extends HttpServlet {
 private static final long serialVersionUID = 1L;
 final Logger logger = Logger.getLogger(PersonRead.class.getName());
 ArrayList<String> colList = null;
 private DataSource dataSource;
 private Connection connection;
 
 public void init( ) throws ServletException {
  try {
   Context initContext = new InitialContext();
   Context envContext = (Context) initContext.lookup("java:/comp/env");
   dataSource = (DataSource) envContext.lookup("jdbc/mysql");
  } catch (NamingException e) {
   e.printStackTrace();
  }
 }

 void connect() {
  String url = "jdbc:mysql://localhost:3306/person";
  String user = "root";
  String pass = "root";
  try {
   Class.forName("com.mysql.jdbc.Driver");
   connection = (Connection) DriverManager.getConnection(url, user, pass);
   logger.log(Level.INFO, "Connection successful");
  } catch (SQLException e) {
   e.printStackTrace();
  } catch (ClassNotFoundException e) {
   e.printStackTrace();
  } catch (SecurityException e) {
   e.printStackTrace();
  } 
 }

 ArrayList<Person> getPerson() {
  ArrayList<Person> pList = new ArrayList<Person>();
  PreparedStatement preparedStatement = null;
  ResultSet resultSet = null;
  try {
   connection = dataSource.getConnection();
   preparedStatement = connection
     .prepareStatement("select * from person order by id");
   resultSet = preparedStatement.executeQuery();
   java.sql.ResultSetMetaData rsm = resultSet.getMetaData( );
   int colCount =  rsm.getColumnCount( );
   colList = new ArrayList<String>();
   for (int i = 1; i <= colCount; i++) {
    colList.add(rsm.getColumnName(i));
   }

   while (resultSet.next()) {
    Person p = new Person();
    p.setName(resultSet.getString("name"));
    p.setSurname(resultSet.getString("surname"));
    p.setIdNumber(resultSet.getInt("id"));
    pList.add(p);
   }
  } catch (SQLException e) {
   e.printStackTrace();
  } finally {
   if (preparedStatement != null)
    try {
     preparedStatement.close();
    } catch (SQLException e) {
     e.printStackTrace();
    }
   if (resultSet != null)
    try {
     resultSet.close();
    } catch (SQLException e) {
     e.printStackTrace();
    }
   if (connection != null)
    try {
     connection.close();
    } catch (SQLException e) {
     e.printStackTrace();
    }
  }
  return pList;
 }

 public void doGet(HttpServletRequest request, HttpServletResponse response)
 throws IOException, ServletException {
  doPost(request, response);
 }
 
 public void doPost(HttpServletRequest request, HttpServletResponse response)
   throws IOException, ServletException {
  ArrayList<Person> personList = getPerson();
  request.setAttribute("data", personList);
  request.setAttribute("columns", colList);
  RequestDispatcher view = request.getRequestDispatcher("resultDB.jsp");
  view.forward(request, response);
  
  
 }
}

---

syntax highlighted by Code2HTML, v. 0.9.1
context: context.xml

    

    

        type="javax.sql.DataSource" username="root" password="root"

        driverClassName="com.mysql.jdbc.Driver"

        url="jdbc:mysql://localhost:3306/person"

        maxActive="10" maxIdle="4" />