JDBC / Servlet / MySQL

[martin weber]

Hi everyone, I have problems reading data from a MySQL-Database through a Servlet. The Connection works; it must be a simple step.
I don't know how to do the last step. The ContextListener checks the Connection at startup. Heres my Code:

ContextListener.java

package webapp;
 

import java.sql.Connection;
import java.sql.Statement;
import java.util.logging.Logger;
 
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
import javax.sql.DataSource;
 
public class MyServletContextListener implements ServletContextListener{

public static Logger logger = Logger.getLogger("Demo");
 
public void contextInitialized(ServletContextEvent sce) {
logger.info("Demo wird gestartet."); 
try {
Context initialContext = new InitialContext();
Context context = (Context)initialContext.lookup("java:comp/env");
DataSource datasource = (DataSource) context.lookup("jdbc/DefaultDB");

Connection connection = datasource.getConnection();
logger.info("Datasource ist verfuegbar");
 
Statement statement = connection.createStatement();
logger.info("Datasource erfolgreich getestet");
statement.executeQuery("select * from kunde");
logger.info("Datasource erfolgreich getestet");
 
sce.getServletContext().setAttribute("datasource",datasource);
} catch (Exception e) {
logger.severe("Problem beim Zugriff auf Datasource, start von Demo gescheitert:\n" + e);
throw new RuntimeException(e);
}
// sce.getServletContext().setAttribute("dateformat","dd.MM.yyyy");
// Hier k√∂nnen Sie weiteren Programmcode einf√�gen, der beim Start der
// Web-Application ausgef√�hrt werden soll.
}
public void contextDestroyed(ServletContextEvent arg0) {
logger.info("Demo wird gestoppt.");
// Hier k√∂nnen Sie weiteren Programmcode einf√�gen, der beim Stoppen der
// Web-Application ausgef√�hrt werden soll.
}
}
 

context.xml

<?xml version="1.0" encoding="UTF-8"?>
<Context path="/DBTest" docBase="DBTest"
        debug="5" reloadable="true" crossContext="true">
 
<Resource name="jdbc/DefaultDB" auth="Container"
type="javax.sql.DataSource" username="root" password="pass"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/tebia" maxActive="20" maxIdle="10" 
removeAbandoned="true" removeAbandonedTimeout="300" logAbandoned="true"/>
</Context>
 

Response.java

package webapp;
 
import java.io.*;
import java.rmi.RemoteException;
import java.sql.*;
import java.util.Date;
import javax.servlet.ServletException;
import javax.servlet.http.*;
import javax.sql.DataSource;
 
import org.eclipse.jdt.internal.compiler.ast.ThrowStatement;
 
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.ResultSet;
import com.mysql.jdbc.Statement;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
 

 public class Response extends javax.servlet.http.HttpServlet implements javax.servlet.Servlet {
  
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

 

try {
String strPasswort = request.getParameter("Passwort");
 

DataSource datasource;


//?this.datasource = datasource;
try {

Connection con= (Connection) datasource.getConnection();
Statement state = (Statement) con.createStatement();
ResultSet ergebnis= (ResultSet) 
state.executeQuery("select * from user where benutzername = " + strUser);


response.setContentType("text/html");
PrintWriter out = response.getWriter();


out.print(ergebnis);



} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

[Paul Clapham]

If I am not mistaken, your question is "How can I get something (a Connection) out of the ServletContext in a servlet?" Well, this is hardly a JDBC question, so perhaps I didn't understand your problem.

So, let me suggest that in your servlet you shouldn't be importing any com.mysql classes. Just like in your context listener, you should be importing those classes from the java.sql package instead.

Also, in web applications which are going to have more than one user, it is not a good idea to have only a single Connection available to those users. Typically you configure a connection pool and have the servlet get a connection from the pool. But I see your code is a demo, so perhaps you planned to do that later.

[martin weber]

Thanks for your answer.
You are right: connection pooling is a further feature, first we want to implement the basics.

I will follow your hint with the com.mysql-Classes, I think this was a mistake trough Eclipse; i clicked the wrong classes to import.

So, I'm sorry if I asked my question wrong; here step by step what I think it has to be (the way I coded it)...

*) I start the servlet. The context-listener starts and checks if the connection works. If it works, the connection is established and works for the whole "underlying" webapp. Right?

*) With the start of the servlet I wan't to get data out the SQL. So I can use the connection provided and established by the context-listener. Right? (Uh, this might be wrong...)

But I don't know how to get access to the DataSource-Object (Is it an object?)



Thanks, guys!

[Paul Clapham]

Originally posted by martin weber:
But I don't know how to get access to the DataSource-Object (Is it an object?)

Of course it's an object. It's not a primitive so it must be an object. You put it into the servlet context in your listener class, right? So you take it out of the servlet context in your servlet. Step 1: get the servlet context. Step 2: get the "datasource" attribute from that.