HP-41
Posts: 4
Joined: Tue Jun 06, 2017 4:00 pm

Access mySQL via Servlet and Tomcat

Tue Jun 06, 2017 4:23 pm

Dear All,

I need some help getting my first servlet up and running. It simply should query a table in a mySQL database and display the result in a web page but I'm receiving an error message and the web page stays blank. As far as I'm interpreting the error message it seems that the connection to the mySQL database cannot be established. I'm using:

- Raspberry Pi 3 Jessie with Pixel“ (Version Nov. 2016, Kernel-Version 4.4)
- Eclipse 3.8
- Tomcat 7.0
- mySQL

On the console of Eclipse the following messages are displayed:

Code: Select all

Jun 06, 2017 4:06:19 PM org.apache.catalina.core.AprLifecycleListener init
INFO: The APR based Apache Tomcat Native library which allows optimal performance in production environments was not found on the java.library.path: /usr/java/packages/lib/arm:/usr/lib/arm-linux-gnueabihf/jni:/lib/arm-linux-gnueabihf:/usr/lib/arm-linux-gnueabihf:/usr/lib/jni:/lib:/usr/lib
Jun 06, 2017 4:06:21 PM org.apache.tomcat.util.digester.SetPropertiesRule begin
WARNING: [SetPropertiesRule]{Server/Service/Engine/Host/Context} Setting property 'source' to 'org.eclipse.jst.jee.server:FirstServletProject' did not find a matching property.
Jun 06, 2017 4:06:22 PM org.apache.coyote.AbstractProtocolHandler init
INFO: Initializing ProtocolHandler ["http-bio-8080"]
Jun 06, 2017 4:06:22 PM org.apache.coyote.AbstractProtocolHandler init
INFO: Initializing ProtocolHandler ["ajp-bio-8009"]
Jun 06, 2017 4:06:22 PM org.apache.catalina.startup.Catalina load
INFO: Initialization processed in 6374 ms
Jun 06, 2017 4:06:22 PM org.apache.catalina.core.StandardService startInternal
INFO: Starting service Catalina
Jun 06, 2017 4:06:22 PM org.apache.catalina.core.StandardEngine startInternal
INFO: Starting Servlet Engine: Apache Tomcat/7.0.12
Jun 06, 2017 4:06:32 PM org.apache.coyote.AbstractProtocolHandler start
INFO: Starting ProtocolHandler ["http-bio-8080"]
Jun 06, 2017 4:06:32 PM org.apache.coyote.AbstractProtocolHandler start
INFO: Starting ProtocolHandler ["ajp-bio-8009"]
Jun 06, 2017 4:06:32 PM org.apache.catalina.startup.Catalina start
INFO: Server startup in 10175 ms
java.sql.SQLException: Before start of result set
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:897)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:886)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
	at com.mysql.jdbc.ResultSetImpl.checkRowPos(ResultSetImpl.java:790)
	at com.mysql.jdbc.ResultSetImpl.getStringInternal(ResultSetImpl.java:5228)
	at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5151)
	at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5190)
	at com.journaldev.first.FirstServlet.doGet(FirstServlet.java:47)
	at javax.servlet.http.HttpServlet.doHead(HttpServlet.java:244)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:638)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:304)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:240)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:164)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:462)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:164)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
	at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:562)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:395)
	at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:250)
	at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:188)
	at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:166)
	at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:302)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
	at java.lang.Thread.run(Thread.java:745)
java.sql.SQLException: Before start of result set
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:897)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:886)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
	at com.mysql.jdbc.ResultSetImpl.checkRowPos(ResultSetImpl.java:790)
	at com.mysql.jdbc.ResultSetImpl.getStringInternal(ResultSetImpl.java:5228)
	at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5151)
	at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5190)
	at com.journaldev.first.FirstServlet.doGet(FirstServlet.java:47)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:304)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:240)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:164)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:462)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:164)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
	at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:562)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:395)
	at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:250)
	at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:188)
	at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:166)
	at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:302)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
	at java.lang.Thread.run(Thread.java:745)
And here is the source I'm using:

Code: Select all

package com.journaldev.first;
import java.sql.*;

import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.PrintWriter;
import java.util.Date;

/**
 * Servlet implementation class FirstServlet
 */
@WebServlet(description = "My First Servlet", urlPatterns = { "/FirstServlet" , "/FirstServlet.do"}) //, initParams = {@WebInitParam(name="id",value="1"),@WebInitParam(name="name",value="pankaj")})

public class FirstServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
	public static final String HTML_START="<html><body>";
	public static final String HTML_END="</body></html>";
	static Connection conn = null;
	static PreparedStatement stmt = null;
	static String url = "jdbc:mysql://localhost/nachhilfe?user=julia&password=pinguin";
	static String selectSQL = "SELECT * FROM person";
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public FirstServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		PrintWriter out = response.getWriter();
		Date date = new Date();
		try{
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection(url);
			stmt = conn.prepareStatement(selectSQL);
			
			ResultSet rs = stmt.executeQuery();
			String vorname = rs.getString("vorname");
			out.println(HTML_START + "<h2>Hi There!</h2><br> from " + vorname + "<br/><br><h3>Date="+date +"</h3>"+HTML_END);	
			
		}catch (Exception ex) {
			ex.printStackTrace();
		}finally{
			try{
				stmt.close();
				conn.close();
			}catch (Exception exc) {
				exc.printStackTrace();
			}
			
			}
    
		
    
    }
	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
	}

}
Any help would be very much appreciated - thank you very much in advance.

User avatar
DougieLawson
Posts: 29760
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website

Re: Access mySQL via Servlet and Tomcat

Thu Jun 08, 2017 5:49 am

Is

Code: Select all

jdbc:mysql://localhost/nachhilfe?user=julia&password=pinguin
correct? Are you able to connect using those credentials from a command line or LXTerminal with a mysql -u julia -p nachhilfe command?
Microprocessor, Raspberry Pi & Arduino Hacker
Mainframe database troubleshooter
MQTT Evangelist
Twitter: @DougieLawson

Since 2012: 1B*5, 2B*2, B+, A+, Zero*2, 3B*3

Please post ALL technical questions on the forum. Do not send private messages.

HP-41
Posts: 4
Joined: Tue Jun 06, 2017 4:00 pm

Re: Access mySQL via Servlet and Tomcat

Thu Jun 08, 2017 6:27 am

Thank you for your reply. Yes, connecting to the database from the commandline is no problem. I guess that either in Eclipse and/or in Tomcat the link to the JDBC Driver is missing but I couldn't figure out where to look for it. A complete checklist with all the necessary entries in the numerous configuration files and checkbox settings etc. would be helpful.

Thanks again and best regards

mattlewis
Posts: 104
Joined: Sat Jan 12, 2013 3:05 pm
Location: UK
Contact: Website

Re: Access mySQL via Servlet and Tomcat

Thu Jun 08, 2017 10:13 pm

Have you enabled remote access to the MySQL database? Java MySQL JDBC connects via a socket, that requires different configuration to local console access.
http://www.diozero.com/

HP-41
Posts: 4
Joined: Tue Jun 06, 2017 4:00 pm

Re: Access mySQL via Servlet and Tomcat

Sat Jun 10, 2017 4:24 pm

Thank you very much for your reply. I checked /etc/my.cnf, it does not contain the entry "skip-networking". In addition I changed the line "bind-address=127.0.0.1" to "bind-address=192.168.0.51" to make sure that "localhost" doesn't cause any problems. Alas, the result is the same as before. The data from the mySQL table is not retrieved and not displayed in the web page.

HP-41
Posts: 4
Joined: Tue Jun 06, 2017 4:00 pm

Re: Access mySQL via Servlet and Tomcat

Sat Jun 10, 2017 4:46 pm

In the meantime I could solve the problem by tracking the error message "MySQL java.sql.sqlexception:before start of resultset" in Google. I forgot to call rs.next and checking it to return true before accessing the data in the result set. After inserting this statement the sample web page was displayed correctly and contained the data from the selected table.

Thank you to all who replied.

Return to “Java”

Who is online

Users browsing this forum: No registered users and 4 guests