Java DB Inserting Data Into Table

Question

I am having trouble trying to insert data into a table in my database in netbeans, below is my code:

public class NewUser extends HttpServlet {

/**
 * Processes requests for both HTTP <code>GET</code> and <code>POST</code>
 * methods.
 *
 * @param request servlet request
 * @param response servlet response
 * @throws ServletException if a servlet-specific error occurs
 * @throws IOException if an I/O error occurs
 */
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    response.setContentType("text/html;charset=UTF-8");

    HttpSession session = request.getSession(false);

    String [] query = new String[5];
    query[0] = (String)request.getParameter("username");
    query[1] = (String)request.getParameter("password");
    query[2] = (String)request.getParameter("confPassword");
    query[3] = (String)request.getParameter("name");
    query[4] = (String)request.getParameter("address");


    Jdbc jdbc = (Jdbc)session.getAttribute("dbbean"); 

    if (jdbc == null)
        request.getRequestDispatcher("/WEB-INF/conErr.jsp").forward(request, response);

    else {
        jdbc.insert(query);
        request.setAttribute("message", query[0]+" has been registered successfully!");
    }

This is in a class "NewUser.java". The insert method is in a class "jdbc" and is as follows:

public void insert(String[] str){
    PreparedStatement ps = null;
    try {
        ps = connection.prepareStatement("INSERT INTO CUSTOMER VALUES (?,?,?,?)",PreparedStatement.RETURN_GENERATED_KEYS);
        ps.setString(1, str[0].trim()); 
        ps.setString(2, str[1]);
        ps.setString(3, str[3]);
        ps.setString(4, str[4]);
        ps.executeUpdate();

        ps.close();
        System.out.println("1 row added.");
    } catch (SQLException ex) {
        Logger.getLogger(Jdbc.class.getName()).log(Level.SEVERE, null, ex);
    }

}

The "CUSTOMER" table looks as follows:

# | Username | Password | Name | Address | ID

('ID' is the primary key which is auto incremented)

The JSP where the user inputs the information is as follows:

<%@page import="model.Jdbc"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>User</title>
    </head>
    <body>
        <h2>User's details:</h2>
        <%! int i = 0;
            String str = "Register";
            String url = "NewUser.do";
        %>
        <%
            if ((String) request.getAttribute("msg") == "del") {
                str = "Delete";
                url = "Delete.do";
            } 
            else 
            {
                str = "Register";
                url = "NewUser.do";
            } 
        %>
        <form method="POST" action="<%=url%>">     
            <table>
                <tr>
                    <th></th>
                    <th>Please provide your following details</th>
                </tr>
                <tr>
                    <td>Username:</td>
                    <td><input type="text" name="username"/></td>
                </tr>
                <tr>
                    <td>Password:</td>
                    <td><input type="password" name="password"/></td>
                </tr>
                <tr>
                    <td>Confirm Password:</td>
                    <td><input type="password" name="confPassword"/></td>
                </tr>
                <tr>
                    <td>Name:</td>
                    <td><input type="text" name="name"/></td>
                </tr>
                <tr>
                    <td>Address:</td>
                    <td><input type="text" name="address"/></td>
                </tr>
                <tr> 
                    <td> <input type="submit" value="<%=str%>"/></td>
                </tr>
            </table>
        </form>   
        <%
            if (i++ > 0 && request.getAttribute("message") != null) {
                out.println(request.getAttribute("message"));
                i--;
            }
        %>
        </br>
        <jsp:include page="foot.jsp"/>
    </body>
</html>

The SQL statement used to create the database:

CREATE TABLE Customer (
  username varchar(20),
  password varchar(20),
  name varchar(20),
  address varchar(60),
  id INT primary key GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1)
);

The code doesn't throw any errors however, when I check the database to see if the data has been added it never gets added and I cannot figure out why (I think it's something to do with the "insert" method?). I have looked around for solutions however, I still get the same outcome and could really use some help.

Cheers,

try using `"INSERT INTO CUSTOMER ('Username', 'Password', 'Name', 'Address') VALUES (?,?,?,?)"` as the sql statement — guleryuz, Nov 18 '18 at 08:43
@guleryuz thanks for the reply, I tried this but it didn't work unfortunately — Seano989, Nov 18 '18 at 08:52
there may be an exception, can you add a line `ex.printStackTrace();` as the first line of catch block in insert method. — guleryuz, Nov 18 '18 at 11:18

score 0 · Answer 1 · answered Nov 18 '18 at 08:55

0

For Oracle you need to specify column for auto generated columns:

 ps = connection.prepareStatement("INSERT INTO CUSTOMER VALUES (?,?,?,?)", new String[]{"ID"})

For ID column

answered Nov 18 '18 at 08:55

Ori Marko

56,308
23
131
233

Hey I tried to change the statement to what you posted however, it is still not adding anything into the table – Seano989 Nov 18 '18 at 09:01
@Seano989 Try adding `connection.commit();` after `ps.executeUpdate();` – Ori Marko Nov 18 '18 at 09:03
Unfortunately this didn't work either, could it possibly be due to the 'CUSTOMER' table itself? Each column is just of type 'VARCHAR' other than 'ID' which is an integer – Seano989 Nov 18 '18 at 09:06
@Seano989 Try adding columns names: `"INSERT INTO CUSTOMER ('Username', 'Password', 'Name', 'Address') VALUES (?,?,?,?)", new String[]{"ID"})` – Ori Marko Nov 18 '18 at 09:08
Unfortunately that didn't work either, I really appreciate the help! The actual data that is being put into the table is inputted in a jsp file, i'm not sure if that'll help but i'll edit my post to include the jsp – Seano989 Nov 18 '18 at 09:14
@Seano989 do the line `1 row added.` is printed? – Ori Marko Nov 18 '18 at 09:22
Nothing gets printed out in the console no, but the message saying the customer has been registered is printed to the web page – Seano989 Nov 18 '18 at 09:32

Himanshu · Answer 2 · 2018-11-18T14:25:02.920

0

You forgot to replace prepareStatement.return.. to Statement.return..ps = connection.prepareStatement("INSERT INTO CUSTOMER VALUES (?,?,?,?)",Statement.RETURN_GENERATED_KEYS,new String[]{"ID"});

edited Nov 18 '18 at 14:25

answered Nov 18 '18 at 10:38

Himanshu

3,830
2
10
29

Hey I tried this but I'm getting an error that says 'incompatible types String[] cannot be converted to Int – Seano989 Nov 18 '18 at 13:46
Are you sure all the columns in the Customer table are varchars or some are int too. – Himanshu Nov 18 '18 at 14:25
The only one that is an int is the primary key "ID", I will edit my post to show the SQL statement I executed to create the table, hopefully that will help! – Seano989 Nov 18 '18 at 14:52

Java DB Inserting Data Into Table

2 Answers2