JDBC with JSP fails to insert

Question

I am having some issues right now with JDBC in JSP. I am trying to insert username/pass ext into my MySQL DB. I am not getting any error or exception, however nothing is being inserted into my DB either. Below is my code, any help would be greatly appreciated.

<%
String uname=request.getParameter("userName");
String pword=request.getParameter("passWord");
String fname=request.getParameter("firstName");
String lname=request.getParameter("lastName");
String email=request.getParameter("emailAddress");
%>


<%
try{
    String dbURL = "jdbc:mysql:localhost:3306/assi1";
    String user = "root";
    String pwd = "password";
    String driver = "com.mysql.jdbc.Driver";

    String query = "USE Users"+"INSERT INTO User (UserName, UserPass, FirstName, LastName, EmailAddress) " +
                   "VALUES ('"+uname+"','"+pword+"','"+fname+"','"+lname+"','"+email+"')";
    Class.forName(driver);
    Connection conn = DriverManager.getConnection(dbURL, user, pwd);
    Statement statement = conn.createStatement();
    statement.executeUpdate(query);

    out.println("Data is successfully inserted!");
    }
    catch(SQLException e){
    for (Throwable t : e)
        t.printStackTrace();
    }
       %>

DB script here:

CREATE DATABASE Users;

use Users;

CREATE TABLE User (
UserID INT NOT NULL AUTO_INCREMENT,
UserName VARCHAR(20),
UserPass VARCHAR(20),
FirstName VARCHAR(30),
LastName VARCHAR(35),
EmailAddress VARCHAR(50),
PRIMARY KEY (UserID)
);

While there are lot of improvements that can be made, I think the issue is "USE Users"+"INSERT INTO....". which will eventually translate to "USE UsersINSERT INTO..." which is not valid SQL...and I think your exception stacktrace is going to the system out — Aravind Yarram, Aug 17 '12 at 02:22
@Pangea: He is not even capable of connect, since his connection URL is wrong. StryleR, when you ask something, try to expose all your problems (the exceptions that are being thrown, etc.) — davidbuzatto, Aug 17 '12 at 02:34
Always make use of `PreparedStatement` to protect against SQL injection and always [avoid](http://stackoverflow.com/a/3180202/1037210) using scriptlet. Move the scriptlet code to Java classes instead to reduce the complexity of the JSP by following the best practices. — Lion, Aug 17 '12 at 02:49
@Lion: I think he is starting to learn how to develop web application in Java. I don't like to teach the incorrect way of doing things and after correcting it, but some teachers like this approach. I hope his teacher will teach these better practices soon. — davidbuzatto, Aug 17 '12 at 02:52

davidbuzatto · Accepted Answer · 2012-08-17T03:12:44.867

First of all, you don't need to use the USE statement in your SQL, since the database was already selected in the connection URL. Even if you use the USE statement, it should not be glued to the INSERT statement. So, in your dbURL, you need to set the apropriate db:

String dbURL = "jdbc:mysql://localhost:3306/Users";

Note that your URL was wrong too. You need two slashed after the second colon char. And you need to change your query variable to:

String query = "INSERT INTO User (UserName, UserPass, FirstName, LastName, EmailAddress) " + 
               "VALUES ('"+uname+"','"+pword+"','"+fname+"','"+lname+"','"+email+"')";

I hope your code is in this state because you are starting to learn Java Web development (I saw the homework tag). In the real world, this is not the way to develop Java web applications, since scriptlets (code between <% and %> and other variations) are not used anymore and a lot of other things needs to be improved, as the way you are dealing with JDBC.

Edit: Here is your fixed code:

<%
    Connection conn = null;

    try {

        String uname=request.getParameter("userName");
        String pword=request.getParameter("passWord");
        String fname=request.getParameter("firstName");
        String lname=request.getParameter("lastName");
        String email=request.getParameter("emailAddress");

        String dbURL = "jdbc:mysql://localhost:3306/Users";
        String user = "root";
        String pwd = "password";
        String driver = "com.mysql.jdbc.Driver";

        String query = "INSERT INTO User (UserName, UserPass, FirstName, LastName, EmailAddress) " +
                       "VALUES ('"+uname+"','"+pword+"','"+fname+"','"+lname+"','"+email+"')";
        Class.forName(driver);
        conn = DriverManager.getConnection(dbURL, user, pwd);
        Statement statement = conn.createStatement();
        statement.executeUpdate(query);
        statement.close();

        out.println("Data is successfully inserted!");

    } catch( ClassNotFoundException e ){
        System.err.println( "Database Driver class not Found!" );
        e.printStackTrace();
    } catch( SQLException e ){
        e.printStackTrace();
    } finnaly {
        try {
            if ( conn != null ) {
                conn.close();
            }
        } catch ( SQLException e ) {
            System.err.println( "Problems when closing connection" );
            e.printStackTrace();
        }
    }
%>

I think that this will work. As Lion said, consider using better solutions in your database code. I Think your teacher will teach these other things soon.

Thanks for all the advice guys. I made the changes you mentioned David, however it still will not work. Also I did not know scriplets are outdated, that is what they are teaching us. — StrykeR, Aug 17 '12 at 02:40
@StrykeR: As I already asked, what is the error? I saw some sintax errors in your code too... I'm rewriting it. — davidbuzatto, Aug 17 '12 at 02:41
I will give that code a shot, thanks. Now I may be way off here, but from some of the stuff in the book ive looked ahead on. Could I take the html I have, input into a jsp, pass the info into a servlet which inserts into the DB? and would this not be a better way to go then using the jsp to connect? — StrykeR, Aug 17 '12 at 03:05
Yes, you can and it is a better approach. JSPs should be used to display something, not to process. They are the "view" of the application, not the model, or the controller or even should contain the business logic. — davidbuzatto, Aug 17 '12 at 03:10
You are welcome! Take a look in the modifications that I did to see what was wrong and try to understand the error messages (that you didn't post) ;) — davidbuzatto, Aug 17 '12 at 03:12
I will be doing some more homework on the MVC then :). So comparing code I assume I should keep everything in one scriplet (if I ever use them again), make sure I have my DB name correct (dbURL), close any statements, and add a try/catch on the close connection (incase it fails). — StrykeR, Aug 17 '12 at 03:19

JDBC with JSP fails to insert

1 Answers1