Database connection not working through Servlet

Question

I'm just trying to get my connection set up on my servlet and output the query.

I got this working when run on a separate project using the same code but ran as a Java Application, not as a servlet. The driver is also in the correct place.

Below is my code on the servlet:

package myproject;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Servlet implementation class jdbc
 */
@WebServlet("/jdbc")
public class jdbc extends HttpServlet {

    private static final long serialVersionUID = 1L;

    /**
     * @see HttpServlet#HttpServlet()
     */
    public jdbc() {
        super();
        // TODO Auto-generated constructor stub
    }

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
     * response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

    }

    public static Connection con;

    protected static void main(String[] argv) {
        try {
            connectionQuery();

            PreparedStatement statement = con.prepareStatement("SELECT * from Music_Categories");/*write query inside of prepared statement*/
            ResultSet result = statement.executeQuery();
            System.out.println("DataBase table accessed");

            while (result.next()) {
                String retrievedid = result.getString("name");
                System.out.println(retrievedid);
            }

            con.close();
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println(e.getMessage().toString());
        }
    }

    protected static void connectionQuery() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/newschemam?useSSL=false", "root", "root");
            System.out.println("Remote DB connection established");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println("Remote server could not be connected");
        } catch (NullPointerException e) {
            e.printStackTrace();
            System.out.println("Remote server could not be connected");
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("Remote db connection establishment error");
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("False query");
        }
    }
}

Thanks in advance for any guidance!

Answer 1

You need to call your main method from doGet method.

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { main(null); }

I would recommend to rename main method to a protected void getDataFromDb() {..} No need to name it as a main method since you are going to use it as servlet.

Answer 2

Consider this modified example implementing a two-way solution runnable as both an application and a servlet.

Check out

• reuse of query method with different target streams
• use try-with-resource to close used resources
• don't reuse connection between multiple threads

Here goes the code:

@WebServlet("/jdbc")
public class jdbc extends HttpServlet {

    protected static void main(String[] argv) {
        //If called as application: Output goes to System.out
        queryTo(System.out);
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        //Set Content-Type
        response.setContentType("text/plain; charset=UTF-8");
        //If called as Servlet: Output into servlet response
        queryTo(new PrintStream(response.getOutputStream()));
    }

    private static void queryTo(PrintStream out) {
        // Use try-with-resource t o autoclose Connection, PreparedStatement and
        // ResultSet
        try (Connection con = connectionQuery();
                PreparedStatement statement = con.prepareStatement("SELECT * from Music_Categories");
                ResultSet result = statement.executeQuery()) {

            // Log to out
            out.println("DataBase table accessed");
            while (result.next()) {
                String retrievedid = result.getString("name");
                out.println(retrievedid);
            }
        } catch (Exception e) {
            //Exception to output stream as well
            e.printStackTrace(out);
        }
    }

    //Don't keep instances of that around but create fresh.
    //A connection pool might be a good idea
    protected static Connection connectionQuery() throws Exception {
        // Don't need to do this since JDBC 4.0
        // Class.forName("com.mysql.jdbc.Driver");
        System.out.println("Remote DB connection established");
        return DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/newschemam?useSSL=false", "root", "root");
    }
}