How to use Java Servlets

[Wyvern]

I did a test with Ajax, who will play record in Oracle. So at every keystroke on the keyboard -> A select is executed.

I would like your opinion on several points:
- Can I manage many connections/disconnections?
- Is this code correct from the performance point of view?
- What would you change?

I focus on making the code clean and efficient.

package lu.dinow.ajax;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
public class StudentInfo extends javax.servlet.http.HttpServlet  {
private Connection orclCon;
public void doGet(HttpServletRequest req, HttpServletResponse res)
    throws IOException, ServletException {
 System.out.println("doGet" );
 res.setContentType("text/xml" );
 PrintWriter out = res.getWriter();
 String ret = process(req,req.getSession());
 out.print(ret);
 out.close();
}
public void init() throws ServletException {
 super.init();
 try {
  orclCon = connect();
 } catch(ClassNotFoundException e) {
  e.printStackTrace();
 } catch(SQLException e) {
  e.printStackTrace();
 }
}
private String process(HttpServletRequest res, HttpSession session){
 String ret = "";
 try{
  ret = "<root>";
  String typedLetters = res.getParameter("roll" );
  Connection con = (orclCon == null) ? connect() : orclCon;
  Statement stmt = con.createStatement();
  String query = "Select First_name from employees where lower(first_name) like lower('" + typedLetters +"%') order by First_name";
  ResultSet rs = stmt.executeQuery(query);
  int i = 0;
  while(rs.next()){
   ret +="<employee id='"+ (i++) +"'>"+rs.getString("First_name" )+"</employee>";
  }
  rs.close();
  stmt.close();
  ret +="</root>";
 }catch(Exception ex){
  System.out.println(ex.getMessage());
 }
 return  ret;
}
public void destroy() {
 super.destroy();
 if (orclCon != null){
  try {
   System.out.println("Disconnected from Oracle" );
   orclCon.close();
  } catch(SQLException e) {
   e.printStackTrace();
  }
 }
}
private Connection connect() throws ClassNotFoundException, SQLException {
   System.out.println("Attempt to connect to Oracle..." );
   String driverName = "oracle.jdbc.driver.OracleDriver";
   Class.forName(driverName);
   String url = "jdbc:oracle:thin:@EMEALU6CZ5J2J:1521:ORCL";
   return DriverManager.getConnection(url, "HR", "HR" );
}
public void doPost(HttpServletRequest req, HttpServletResponse res)
    throws IOException, ServletException {
 System.out.println("doPost" );
 res.setContentType("text/xml" );
 PrintWriter out = res.getWriter();
 String ret = process(req, req.getSession());
 out.print(ret);
 out.close();
}
}

[Praetor]

You'd better not go through a connection pool? Especially for such cases (auto completion in ajax) if you got 10 connections typing in the field, it immediately made a multitude of requests and then initiating connections in no time ...

[Wyvern]

For the connection pool, how do I practice? I "met" that in the context of tomcat or what? Protection against code injection:

String selectStatement = "Select First_name, Last_name from employees where lower(first_name) like ? order by First_name";
  PreparedStatement prepStmt = conn.prepareStatement(selectStatement);
  prepStmt.setString(1, (typedLetters+"%" ).toLowerCase());
  ResultSet rs = prepStmt.executeQuery();

I found the below statement on the net (source: kgo.de):

With connection pools servlets acquire connections to databases from a pool and returns the connection back to the pool for later reuse. Because the connection is opened only at the first request there is no overhead with opening and closing connection every time. The servlet creates the pool in its 'init' method. This method is executed only when the servlet is loaded. After a timeout period the connection pool closes expired connections.

Connection pools are an efficient way of handling database connections with servlets.

[MindSpace]

Put a timer in your fields for Ajax too (like at least 500ms without modification before making the application) it will avoid eating 200 requests for the tape guy.