Querying and updating 2 separate tables in my sql one servlet

Question

Currently I'm making an application that is a car rental application. The application can rent out a car, return a car ect. For returning a car I'm pulling in the user name of the person returning the car, attempting to query the db and pull out which car they are renting, then updating the user to no longer have a car rented out and updating the car details to become available. However I notice when I am querying the DB I'm getting a null return. I see my table has this user, so maybe I'm wrong in my syntax please help.

protected void doPost(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {
    String carid = "";
    String uname = req.getParameter("User_Name");
    String defaulted = "User has no car";

    int rs;
    Connection conn = null;
    java.sql.PreparedStatement st = null;
    String nativeSQL = "";
    ResultSet result  =null;

    try {
        Context ctx = new InitialContext();
        Context env = (Context) ctx.lookup("java:comp/env");
        DataSource ds = (DataSource) env.lookup("jdbc/carRentalSystem");
        conn = ds.getConnection();

        conn.setAutoCommit(false);
        result = st.executeQuery("SELECT * FROM userdetails where User_Name='"+ uname+"'");
        if(result.next()) {
            carid= result.getString("Car_Rental");
        }

        st = conn.prepareStatement("update userdetails SET Car_Rental ='" + defaulted+ "' where User_Name='" + uname+ "' ");
        st.clearParameters();
        rs = st.executeUpdate();

        st = conn.prepareStatement("update cardetails SET Availability = 'Available' where id='" + carid+ "'");
        st.clearParameters();
        rs = st.executeUpdate();
        if (rs != 0) {
            res.sendRedirect("carRental.jsp");
            return;
        } else {

        } 
        conn.commit();


    } catch (Exception e) {
        try{conn.rollback();}catch(Exception e1){}
        e.printStackTrace();
    } finally {
        try {
            if (st != null)
                st.close();
        } catch (java.sql.SQLException e) {
        }
        try {
            if (conn != null)
                conn.close();
        } catch (java.sql.SQLException e) {
        }

    }
}

}

My error reads as follows

INFO: Server startup in 7620 ms

java.lang.NullPointerException at com.rental.servlet.AdminReturnCarServlet.doPost(AdminReturnCarServlet.java:63) at javax.servlet.http.HttpServlet.service(HttpServlet.java:648) at javax.servlet.http.HttpServlet.service(HttpServlet.java:729) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207) at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:94) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:504) at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:620) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79) at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:620) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:502) at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1132) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:684) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1539) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1495) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.lang.Thread.run(Thread.java:748)

I did notice while I ran it through debug mode it I'm getting an error I passed if you could not log in, saying invalid password however I am not attempting to log in. — user8565055
What's the value of uname? Does that exactly match the the User_Name column in the database? What happens when you run the query in some other database client? Also, see SQL Injection attack. — Andrew S
The Value of uname is the username given, by the client, it matches the User_Name column username exactly. and thank you for the reading! — user8565055
Are you getting a null on the first query, or is it a subsequent one? (also - is id in cardetails a string or integer/long field?) — Chris J
@MarshallMunoz your preparedStatement is not initialized that's why the NPE. See my response below. — P3trur0

P3trur0 P3trur0 · Accepted Answer · 2017-10-09T13:27:54

Your error is because the java.sql.PreparedStatement st = null; is not initialized before its execution at line:

st.executeQuery("SELECT * FROM userdetails where User_Name='"+ uname+"'");

Hope it helps.

Querying and updating 2 separate tables in my sql one servlet

1 Answers