Unable to insert values into DB using dao class

Question

On form submit, the request is passed to httpservlet request wherein I am trying to insert values into user model (with setter methods) like this:

Servlet:

protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

    System.out.println("Request Parameters from Registraion Servlet: ");
    System.out.println("=========================================== ");
    System.out.println(request.getParameter("uname")); //Doesn't print anything in console
    System.out.println(request.getParameter("lname")); //Doesn't print anything in console
    System.out.println(request.getParameter("email")); //Doesn't print anything in console
    System.out.println(request.getParameter("pwd"));   //Doesn't print anything in console

    try {
        System.out.println("Registration Servlet INSIDE try block:"); //Doesn't print anything in console
        User user = new User();
        user.setFirstName(request.getParameter("uname"));
        user.setLastName(request.getParameter("lname"));
        user.setEmail(request.getParameter("email"));
        user.setPassword(request.getParameter("pwd"));
        RegistrationDAO rsDao = new RegistrationDAO();
        rsDao.insert(user);
    } catch (Exception e) {
        System.out.println("DB related Error");
    }

    System.out.println("Registration Servlet OUTSIDE try block:"); //Doesn't print anything in console
    RequestDispatcher rd = request.getRequestDispatcher("EmailConfirmation.jsp"); //Successfully Redirects
    rd.forward(request, response);
}

It successfully redirect to the JSP page I mentioned but doesn't print any values from request.getparameters(xxx). I am wondering when the servlet is successfully called from action method of form, why are the values not printed?

Scratching my head but not able to figure out what went wrong?

Here is my HTML page:

<form name="myForm" action="RegistrationServlet.do" method="POST" novalidate>

    <input type="text" class="form-control input-sm" name="uname" ng-model="uname" ng-pattern="/^[a-zA-Z]{3,20}/"  unique-username="" required placeholder="First Name" /><br>

    <input type="text" class="form-control input-sm" name="lname" ng-model="lname" ng-pattern="/^[a-zA-Z]{3,20}/" required placeholder="Last Name"/><br>

    <input type="email" class="form-control input-sm" name="email" ng-model="email" ng-pattern="/^[a-zA-Z]{3,20}/" required placeholder="Email"/><br>

    <input type="password" class="form-control input-sm glyphicon glyphicon-ok" name="pwd" ng-model="pwd" required placeholder="Password"/><br>

    <input type="password" class="form-control input-sm glyphicon glyphicon-ok" name="pwd2" ng-model="pwd2" value-matches="pwd" required placeholder="Confirm password"/><br>

    <input type="checkbox" class="" name="agreement" value=""><small> I agree to RTH agreement</small>

    <!--<button class="form-control btn btn-success" type="submit" ng-click="submit($event)">Connect now</button>-->

    <input type="submit" class="form-control btn btn-success" value="Connect now" />
</form>

DAO class:

public class RegistrationDAO {

    public void insert(User user) {

        try {
            System.out.println("Printing USER Values");
            System.out.println("====================");
            System.out.println(user.getFirstName());
            System.out.println(user.getLastName());
            System.out.println(user.getEmail());
            System.out.println(user.getPassword());

            Connection con = DBConnection.getConnection();
            String query = "insert into TBL_USER(USR_FIRST_NAME,USR_LST_NAME,USR_PRIMARY_EMAIL,USR_PASSWORD) values(?,?,?,?)";
            PreparedStatement pst = con.prepareStatement(query);
            pst.setString(1, user.getFirstName());
            pst.setString(2, user.getLastName());
            pst.setString(3, user.getEmail());
            pst.setString(4, user.getPassword());
            pst.executeUpdate();
        } catch (Exception e) {
            System.out.println("@@@@Record insertion error in Registration DAO@@@@");
            System.out.println(e);
        }
    }
}

Model class:

public class User implements Serializable{

    private String firstName;
    private String lastName;
    private String password;
    private String email; 
    //setters and getters here
}

SQL table format:

CREATE TABLE "RTH"."TBL_USER" 
(
    "USR_ID" VARCHAR2(1 BYTE) NOT NULL ENABLE, 
    "USR_IS_ANONYMOUS" RAW(1), 
    "USR_FIRST_NAME" CLOB, 
    "USR_MID_NAME" CLOB, 
    "USR_LST_NAME" CLOB, 
    "USR_PRIMARY_EMAIL" CLOB, 
    "USR_ALT_EMAIL" CLOB, 
    "USR_PRIMARY_CNCT_NMBR" CLOB, 
    "USR_SECONDARY_CNCT_NMBR" CLOB, 
    "USR_TYPE" CLOB, 
    "USR_CATEGORY" CLOB, 
    "USR_DOB" TIMESTAMP (0), 
    "USR_CREATE_DT" TIMESTAMP (0), 
    "CUPN_ID" VARCHAR2(1 BYTE), 
    "USR_MARITIAL_STUTUS" VARCHAR2(20 BYTE), 
    "USR_GENDER" VARCHAR2(1 BYTE), 
    "USR_IMAGE" RAW(1), 
    "USR_PASSWORD" VARCHAR2(20 BYTE), 
     CONSTRAINT "XPKTBL_USER" PRIMARY KEY ("USR_ID")
)

Update:

The problem was I didn't call the insert(User user) method of dao class. Now Its working but I have problem with the insert statement String query = "insert into TBL_USER(USR_FIRST_NAME,USR_LST_NAME,USR_PRIMARY_EMAIL,USR_PASSWORD) values(?,?,?,?)";

I am getting error: java.sql.SQLIntegrityConstraintViolationException: ORA-01400: cannot insert NULL into ("RTH"."TBL_USER"."USR_ID")

Update2:

With little research I changed the sql query to the following:

`String query = "update TBL_USER set USR_FIRST_NAME = ?, USR_LST_NAME = ?, USR_PRIMARY_EMAIL=?, USR_PASSWORD = ?";`

Now I get no error at all but there is no insertion in the table

It's likely that"I get no error at all" means you don't know where your errors are getting logged. — Nathan Hughes, Dec 22 '15 at 04:59
@NathanHughes I have fixed the issue. Please look at my answer — kittu, Dec 22 '15 at 05:14

score 1 · Answer 1 · answered Dec 20 '15 at 14:14

1

Are you sure you are replacing the ?s with the values that need to be inserted? You're not showing us how you do that and I suspect that is where it goes wrong.

answered Dec 20 '15 at 14:14

BRNTZN

564
1
7
21

Your column user id is set to not null so you can't put a new row in to the table without a user id. – BRNTZN Dec 20 '15 at 14:30
Is there a way for oracle db to auto increment it? or do I have to do in java? – kittu Dec 20 '15 at 14:33
http://stackoverflow.com/questions/11296361/how-to-create-id-with-auto-increment-on-oracle – BRNTZN Dec 20 '15 at 14:34
aha already looking at that link from google! thanks anyway for pointing out about the ID column – kittu Dec 20 '15 at 14:36

score 0 · Answer 2 · answered Dec 20 '15 at 14:14

0

can you post the user entity class as well?

The first exception you got implies that you are passing a null value on a column of the table

answered Dec 20 '15 at 14:14

George Moralis

518
2
12

your sql query in dao is wrong. When you use update in sql you update an existing entry , not writing a new one – George Moralis Dec 20 '15 at 14:19
But when I try with `String query = "insert into TBL_USER(USR_FIRST_NAME,USR_LST_NAME,USR_PRIMARY_EMAIL,USR_PASSWORD) values(?,?,?,?)";` I get error: `java.sql.SQLIntegrityConstraintViolationException: ORA-01400: cannot insert NULL into ("RTH"."TBL_USER"."USR_ID")` – kittu Dec 20 '15 at 14:20
add your user class as well to your question – George Moralis Dec 20 '15 at 14:21
ok although i don't have your sql table format , i can understand that you also have a user id column? . Is it set to auto_increasement? if not then your query should have that as well – George Moralis Dec 20 '15 at 14:24
in a insert query you should include all fields even if you don't update them.your USR_ID should get manually a value as well . You are getting that NULL because if you don't include all fields in query the preparedstatement put NULL which isn't permitted by your data table – George Moralis Dec 20 '15 at 14:29
hmm I don't think so because I am trying to insert into only specific column – kittu Dec 20 '15 at 14:30

kittu · Answer 3 · 2015-12-22T04:54:51.390

Creating a sequence and trigger to auto update the ID column fixed the issue. Here is the solution:

CREATE TABLE "RTH"."TBL_USER" 
(   
    "USR_ID" VARCHAR2(1 BYTE) NOT NULL ENABLE, 
    "USR_IS_ANONYMOUS" RAW(1), 
    "USR_FIRST_NAME" CLOB, 
    "USR_MID_NAME" CLOB, 
    "USR_LST_NAME" CLOB, 
    "USR_PRIMARY_EMAIL" CLOB, 
    "USR_ALT_EMAIL" CLOB, 
    "USR_PRIMARY_CNCT_NMBR" CLOB, 
    "USR_SECONDARY_CNCT_NMBR" CLOB, 
    "USR_TYPE" CLOB, 
    "USR_CATEGORY" CLOB, 
    "USR_DOB" TIMESTAMP (0), 
    "USR_CREATE_DT" TIMESTAMP (0), 
    "CUPN_ID" VARCHAR2(1 BYTE), 
    "USR_MARITIAL_STUTUS" VARCHAR2(20 BYTE), 
    "USR_GENDER" VARCHAR2(1 BYTE), 
    "USR_IMAGE" RAW(1), 
    "USR_PASSWORD" VARCHAR2(20 BYTE), 
     CONSTRAINT "XPKTBL_USER" PRIMARY KEY ("USR_ID")
);

Sequence and trigger for auto increment ID value:

CREATE SEQUENCE TBL_USER_SEQ
  START WITH 1
  INCREMENT BY 1
  CACHE 100;

 CREATE OR REPLACE TRIGGER "RTH"."TBL_USER_TRIGGER"
  BEFORE INSERT ON TBL_USER
  FOR EACH ROW
BEGIN
  SELECT TBL_USER_SEQ.nextval
    INTO :new.USR_ID
    FROM dual;
END;

Unable to insert values into DB using dao class

3 Answers3