JSP with JDBC connection to a MySQL database


Before we start with this example for connecting and reading data from a MySQL database, we assume that you already have a JSP/Servlet container like Apache Tomcat, Jetty etc. and a MySQL database installed on your computer.

An easy to understand article about installing Apache Tomcat can be found in previous tutorials here on Librescript.

For simplicity, we will not use Maven, Spring MVC or Spring Boot in this tutorial, but these are powerful frameworks and you should consider using them in larger JSP/Servlets projects.

After we have set up MySQL, we want to create a table and fill it with some data. Open the MySQL Monitor or any MySQL GUI and type:

Tip:

You can check if the MySQL server is responding on a command prompt or terminal by typing:

Windows/Linux:         telnet localhost 3306

macOS:                       nc -vz localhost 3306

Since we want to understand how JSP/Servlets work under the hood, we will not use IDEs like Eclipse, IntelliJ, etc. and will set up the whole project manually.

Navigate to the Apache Tomcat installation folder and open the webapps folder.

Create a new folder called jdbc-example, a subfolder called WEB-INF and a subfolder called lib. Also create an index.jsp file in the jdbc-example folder

Download mysql-connector-java-8.0.19.jar from https://dev.mysql.com/downloads/connector/j/, unzip the file and place it in the WEB-INF/lib folder

└── jdbc-example

    └── WEB-INF

        └── lib

            └── mysql-connector-java-8.0.19.jar

    └── index.jsp

Add the following code to the JSP file (index.jsp)

Launch Apache Tomcat. Navigate to the Tomcat folder and type:

Windows/Linux:         bin\catalina.bat start

macOS:                       bin/catalina.sh start

And finally, open a browser and call up:

http://localhost:8080

Common SQL exceptions thrown during the execution of JSPs/Servlets and how to resolve them:

Communications link failure. The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

Server is down. Start MySQL Server

SQLException caught: The server time zone value ‘CEST’ is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the ‘serverTimezone’ configuration property) to use a more specifc time zone value if you want to utilize time zone support.

Append the parameter serverTimezone to the connection string.

String connectionURL = “jdbc:mysql://localhost:3306/demo_database?serverTimezone=UTC”;

Final note:

Establishing a JDBC connection in a JSP is not good practice, we have done it here to keep the tutorial as simple as possible. Try to avoid writing much Java code in JSPs, because JSPs are views and Java code belongs in servlets.

JSP with JDBC connection to a MySQL database

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">