Last updated on April 20th, 2022 at 10:00 am
In java we can make use of Servlets to connect to Mysql database and Display results in html.
Name of my database as My_test and display the column with the name Firstname from table named Employees. Modify localhost to the name or ip address of the server running MYSQL service. Tested in TomCat 10.
The script also has the ability to check the mysql driver and it will throw exception java.lang.ClassNotFoundException: com.mysql.jdbc.Driver, To fix this i am using mysql-connector-java-8.0.28.jar and has uploaded it in the WEB-INF/lib directory. Once done compile the file again and restart the service to fix the exception. Download the file from here
Please find the entire script below. Once compiled trigger the servlet by going to http://<website>:8080/app/connect
import java.sql.*;
import jakarta.servlet.ServletException;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import java.io.*;
import jakarta.servlet.annotation.*;
@WebServlet("/connect")
public class JDBCTest extends HttpServlet {
public void doGet(HttpServletRequest request,
HttpServletResponse response)
throws ServletException, IOException {
PrintWriter out = response.getWriter();
InputStream input = null;
try {
System.out.println("-------- MySQL JDBC Connection Program Started ------------");
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
out.println("Mysql Driver Not Found");
e.printStackTrace();
return;
}
System.out.println("MySQL JDBC Driver Found!!!");
Connection connection = null;
try {
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/My_test","root", "anypassword");
if (connection != null) {
Statement stmt = null;
ResultSet rs = null;
stmt = connection.createStatement();
if (stmt.execute("select * from Employees")) {
rs = stmt.getResultSet();
} else {
System.err.println("select failed");
}
while (rs.next()) {
String entry = rs.getString("Firstname");
out.println(entry);
}
} else {
out.println("Failed to make connection!");
}
} catch (SQLException e) {
out.println("Cannot Connect To Mysql Server:- Please check whether the server is up and running");
out.println("<p>Error Details</P>"+e);
System.out.println("Connection Failed! Check output console");
e.printStackTrace();
return;
}
}
finally {
if (input != null) {
try {
input.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
}
Servlet tries to connect to the Database and run the query select * from Employees. If the mysql server is not available it will throw another exception stating that it cannot connect to the server if so please check whether the services is up and running.
admin