CODEWITHSUNDEEP
javamysqljspjdbc
jshariar
jshariar

Reputation: 338

Why am I getting a connection failed error in JSP wit JDBC and MySQL

I am trying to connect to a mysql db and execute a query

But the db connection fails on load.

Pass and userID are all correct.

I dont have any errors on netbeans.

Here is my code

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>

<%@ page import="java.sql.*" %>

<% Class.forName("com.mysql.jdbc.Driver"); %>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>
        <h1>Real Time Stock Price Quote</h1>

        <form action="" method="get">

            <p>Search:</p> 
            <input type="text" name="q" placeholder='search here'><br>
            <input type="submit" value="Submit" ><br>

        </form>

        <%
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

            try{
                Class.forName("com.mysql.jdbc.Driver");
                conn = (Connection)DriverManager.getConnection("jdbc:mysql://localhost:3306/stocklist","root","");
                st = conn.createStatement();

                String qry = request.getParameter("q");

                String data;


                qry= "SELECT * FROM `stocks` WHERE `name` LIKE '%qry%'";


                rs = st.executeQuery(qry);


                 if(!conn.isClosed()){
                     %>

                     <P>connected DB  </P>
                     <%= qry %> 

                     <%
                 }

                %>

                <p> <% rs.getString("symbol"); %> </p>

                <%
            }catch(Exception e){

                    %>
                    <h2>Error! Database Connection Failed!!</h2>

                    <%

                    }

                    %>
    </body>
</html>

Here is the output that I got:

Real Time Stock Price Quote Search:

search here

connected DB

SELECT * FROM stocks WHERE name LIKE '%qry%' Error! Database Connection Failed!!

Why would it connect and then fail?

Upvotes: 0

Views: 70

Answers (2)

Joop Eggen
Joop Eggen

Reputation: 109613

Use try-with-resource to automatically close Connection, (Prepared)Statement and ResultSet. Its syntax is a bit weird: try(DECLARATIONS) {....}. Its advantage it also closes on exceptions and return/break and such.

Then the q parameter must be filled in.

Always use a PreparedStatement: SQL injection.

Let the exception show in all its glory.

`<%= 1+2 %> is to insert an expression in the HTML.

Your error was probably not having something LIKE "qry" in the table and not calling rs.next() to read the first/next record.

    <%
        String q = request.getParameter("q");
        String sql = "SELECT * FROM `stocks` WHERE `name` LIKE ?";

        try (Connection conn = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/stocklist", "root", "");
                PreparedStatement st = conn.prepareStatement(sql)) {
            st.setString(1, "%" + q + "%");

            try (ResultSet rs = st.executeQuery()) {
                while (rs.next()) {
                    %>
                    <p> <%= rs.getString("symbol") %> </p>
                    <%
                }
            }
        }

     %>

Upvotes: 0

rzwitserloot
rzwitserloot

Reputation: 103813

Exceptions have 4 useful bits of information in it: Stack trace, message, type, and cause.

You're throwing away all 4 things and replace it with 'failed!'.

Classic case of 'don't do that'.

Just let the exception happen; your logs will then contain all this information for you to look at. From there you will probably be able to figure out the problem.

Without that information, nobody can help here without a complete clone of your dev machine.

Upvotes: 2

Related Questions