Link to home
Start Free TrialLog in
Avatar of Gar04
Gar04

asked on

jsp/Javabean insert to database question

hey Kennethxu and rrz
its Gaz again
been playing a lot with registration form you helped me with earlier
and i am having trouble writting the data to the database
you c dreamweaver has server behaviours that insert data to a db however
if i do this it completely ignores the validation bean you helped with.
i looked at how to insert the data using a servlet
but am not sure how to incorporate the code into the java bean
the data is already retrieved with the set and get method
do i write another method to achieve a database insert?????
here is the old code:

package com.ora.jsp.beans.userinfo;

import java.io.*;
import java.util.*;
import com.ora.jsp.util.*;

/**
 *  
 * bean used to capture and validate user input for a registration form.
 *
 *
 */
public class UserRegBean implements Serializable {
   

    // Bean Properties
    private String firstName;
    private String lastName;
    private String orgName;
    private String emailAddress;
    private String userName;
    private String passWord;
    private String repassWord;
    private boolean isInitialized;      

    /**
     * Gets the FirstName property value
     */
    public String getFirstName() {
        return firstName;
    }

    /**
     * Sets the FirstName property value
     */
    public void setFirstName(String firstName) {
        isInitialized = true;
        this.firstName = firstName;
    }

    /**
     * Gets the LastName property value
     */
    public String getLastName() {
        return lastName;
    }

    /**
     * Sets the LastName property value
     */
    public void setLastName(String lastName) {
        isInitialized = true;
        this.lastName = lastName;
    }

    /**
     * Gets the Organisation property value
     */
    public String getOrgName() {
        return orgName;
    }

    /**
     * Sets the Organisation property value
     */
    public void setOrgName(String orgName) {
        isInitialized = true;
        this.orgName = orgName;
    }

   
     /**
     * Gets the Email Address property value
     */
    public String getEmailAddress() {
        return emailAddress;
    }

    /**
     * Sets the Email Address property value
     */
    public void setEmailAddress(String emailAddress) {
        isInitialized = true;
        this.emailAddress = emailAddress;
    }

    /**
     * Gets the UserName property value
     */
    public String getUserName() {
        return userName;
    }

    /**
     * Sets the UserName property value
     */
    public void setUserName(String userName) {
        isInitialized = true;
        this.userName = userName;
    }
   
   
     /**
     * Gets the Password property value
     */
    public String getPassWord() {
        return passWord;
    }

    /**
     * Sets the Password property value
     */
    public void setPassWord(String passWord) {
        isInitialized = true;
        this.passWord = passWord;
    }

     /**
     * Gets the Retype Password property value
     */
    public String getRepassWord() {
        return repassWord;
    }

    /**
     * Sets the Retype Password property value
     */
    public void setRepassWord(String repassWord) {
        isInitialized = true;
        this.repassWord = repassWord;
    }
     
     /**
      * Validation Methods for the Registration Data
      */
   public String getPropertyStatusMsg() {
          String msg ="Thank you for registering!";
          if (!isInitialized ()) {
          msg ="Please enter values in all fields";
          }
          else if (!isValid()) {
          msg ="The Following data is missing or invalid: ";
          }
          return msg;
          
          }
          
    public String[] getPropertyStatusDetails() {
    Vector details = new Vector();
    if (isInitialized() && !isValid()) {
          
          if (firstName == null) {
                      details.addElement("A First Name is Missing");
                      }
                
          if (lastName == null) {
                      details.addElement("A Last Name is Missing");
                      }            
            
            if (orgName == null) {
                      details.addElement("An Organisation or Company Name is Missing");
                      }
                      
          if (emailAddress == null) {
                      details.addElement("An Email Address is Missing");
                      }
                else if (!StringFormat.isValidEmailAddr(emailAddress))  {
                                  details.addElement("Invalid Email Format:"+emailAddress+",try this format:'bob@name.com'");
                            }
                            
                            
          if (userName == null) {
                      details.addElement("A User Name is Missing");
                      }      
                      
          if (passWord == null) {
                      details.addElement("A Password is Missing");
                      }
                      
          if (repassWord == null) {
                      details.addElement("A re-typed Password is Missing");
                      }
                      
                else if (repassWord != passWord){
                            details.addElement("The retyped Password does not match the Password you entered!");
                            }
                            
          }
          
          String[] arr = new String[details.size()];
          details.copyInto(arr);
          return arr;
    }      
   
    public boolean isInitialized()  {
          return isInitialized;
          }
          
   
    /**
     * Returns true if all property values have valid values
     * (they are only set if the value is valid).
     */
    public boolean isValid() {
       return firstName != null &&
                  lastName != null && orgName != null && 
            StringFormat.isValidEmailAddr(emailAddress) && userName != null &&
            passWord != null && repassWord != null && repassWord.equals(passWord);
       }

   
 }
                
ASKER CERTIFIED SOLUTION
Avatar of kennethxu
kennethxu

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of kennethxu
kennethxu

you need to import package java.sql.* to try the example, you'll have to make necessary changes depend on the table and database you use.

you can also make use of tomcat connection pool for better performance.

BTW, what database software r u using?
Avatar of Gar04

ASKER

hey
i am using MS Access because it is all that i have access to
right now
so the database connection can go into the method
and i use sql to write to the database
i think that i have an understanding of how it should work
gaz
Avatar of Gar04

ASKER

essentially, the method will be called if the data is valid right??
gaz
>> essentially, the method will be called if the data is valid right??
it doesn't happen automatically, you need to put in your own logic to call the method.
for example, in you jsp page, you can call isValid(), if it returns true, then call the insert method.
Avatar of Gar04

ASKER

is this the correct type of method?
Gaz

public void insertData() {
                
  Class.forName("[sun.jdbc.adbc.jdbcOdbcDriver]");
  Connection conn = null;
  PreparedStatement ps = null;
       try {
  conn = DriverManager.getConnection( "[jdbc:odbc:BillboardCompany]", dbuser, password );
                    
             if (isValid() == true) {
        ps = conn.prepareStatement( "INSERT INTO UserRegInformation" +
        "(FirstName, LastName, OrganisationName, EmailAddress, Username, Password)" + "values( ?, ?, ?, ?, ? )" );
        ps.setInt( 1, firstName );
        ps.setString( 2, lastName );
        ps.setString( 3, orgName );
        ps.setString( 4, userName );
        ps.setString( 5, repassWord );
                    
        ps.executeUpdate();
      }
       finally {
            if( ps != null ){
            try {ps.close();}
            catch( Throwable t ) { t.printStackTrace(); }
            if( conn!=null){
              try {conn.close(); }
                      catch( Throwable t ) { t.printStackTrace(); }
        }
                  
    }      
Avatar of Gar04

ASKER

>>>
sorry i just spotted this:
 ps.setInt( 1, firstName );
it should be
ps.setString(1, firstName);
Avatar of Gar04

ASKER

sorry, i also noticed that some of the curly braces are incorrect and i fixed 'em

a question; the only errors in the code now relates to this: conn = DriverManager.getConnection( "[jdbc:odbc:BillboardCompany]", dbuser, password );
and points specifically to the dbuser,password);

is it necessary to include these, this is a development app and i haven't set dbuser and passwords yet!!??

Gaz
>> Class.forName("[sun.jdbc.adbc.jdbcOdbcDriver]");
I think it should be odbc instead of adbc and you don't need '[]'
Class.forName("sun.jdbc.odbc.jdbcOdbcDriver");
>> and points specifically to the dbuser,password);
you'll need to use real dbuser and password, if your access file doesn't have access control, you don't need them, or just pass "admin", "admin". I guess :)
>> conn = DriverManager.getConnection( "[jdbc:odbc:BillboardCompany]", dbuser, password );

again you don't need the '[]', sorry for the confusion in my example.

and you need to create a system DSN in control panel->odbc, named BillBoardCompany, pointing to your access file.
>> it should be
>> ps.setString(1, firstName);

good catch!
Avatar of Gar04

ASKER

>>>"and you need to create a system DSN in control panel->odbc, named BillBoardCompany, pointing to your access file."
i have created the DSN to BillBoardCompany
so i think that that bit is covered for now

i get more errors now:

C:\Documents and Settings\Amanda Blanton\Desktop\UserRegBean.java:137: unreported exception java.lang.ClassNotFoundException; must be caught or declared to be thrown
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                     ^
C:\Documents and Settings\Amanda Blanton\Desktop\UserRegBean.java:142: unreported exception java.sql.SQLException; must be caught or declared to be thrown
                    conn = DriverManager.getConnection( "jdbc:odbc:BillboardCompany");
                                            ^
C:\Documents and Settings\Amanda Blanton\Desktop\UserRegBean.java:146: unreported exception java.sql.SQLException; must be caught or declared to be thrown
                    ps = conn.prepareStatement( "INSERT INTO UserRegInformation" +
you can throw those exception out of this method for now.

public void insertData() throws ClassNotFoundException, SQLException {
Avatar of Gar04

ASKER

hey it has compiled fine
regarding the if statement in the code
essentially
i don't need it there do i ?
as when call the method in the JSP page i will first call the isValid() boolean method and if it is true then
call the insertData() method

or should i just leave it in there???
what do i do in this situation?
Gaz
hmm... I'm not quite sure of your question. I think you need to call the insertData in the jsp file.
Avatar of Gar04

ASKER

yes, i mean of course to call the method in the jsp
i knwo that you said to call it in the call to the isValid() method in the jsp
but in the code for insertData() i have the if (isValid()==true) condition statement
i was just wondering if this is necessary because the method will not be called in the isValid method in the jsp
unless the data is valid
it just seems that i am using it twice
????

public void insertData() throws ClassNotFoundException, SQLException  {
                
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                  Connection conn = null;
                  PreparedStatement ps = null;
            try {
                  if (isValid()==true){
                    conn = DriverManager.getConnection( "jdbc:odbc:BillboardCompany");
                    ps = conn.prepareStatement( "INSERT INTO UserRegInformation" +
                    "(FirstName, LastName, OrganisationName, EmailAddress, Username, Password)" + "values( ?, ?, ?, ?, ? )" );
                    ps.setString( 1, firstName );
                    ps.setString( 2, lastName );
                    ps.setString( 3, orgName );
                    ps.setString( 4, userName );
                    ps.setString( 5, repassWord );
                    
                    ps.executeUpdate();
              }
                   
                  }
                  finally {
                                  if( ps != null )
                                
                                  try {ps.close(); }
                                
                                  catch( Throwable t ) { t.printStackTrace(); }
                                  
                                
                                  if( conn!=null)
                                
                                  try {conn.close(); }
                                
                                  catch( Throwable t ) { t.printStackTrace(); }
                               
                               }
                  
          }
oh! I'm sorry that I didn't see it. I think it is good that you always check in the insert methods itsefl. that ensures only good data will go into database.

in the jsp page, I would suggtest to called it again because it'll be easier to somebody else might need to understand your code.

I would also also throw an exception if the insert method is called but the data is not valid, because that indicates programming error.

          try {
               if ( ! isValid() ){
                 throw new SQLException( "invalid data - from UserRegBean.insertData" );
               } else {
Avatar of Gar04

ASKER

where do i throw this exception in the bean?
and how exactly do i call the isValid() an then the insertData() methods using jsp
as i can't find good examples of this and am getting errors.
Gaz
by the way thanks for the continued help
>> where do i throw this exception in the bean?
the code I posted above is sto replace:
          try {
               if (isValid()==true){

>> and how exactly do i call the isValid() an then the insertData() methods using jsp
just do:

<% if( Bean1.isValid() ) Bean1.insertData(); %>
Avatar of Gar04

ASKER

i guess that this is overkill:
 if (!isValid() ){
                 throw new SQLException( "invalid data - from UserRegBean.insertData" );
                 }
                 else if (isValid()==true) {

i just need the replacement you gave me
right? thank you for indulging me, i am just trying to learn some stuff
gaz




 public void insertData() throws ClassNotFoundException, SQLException  {
                
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                  Connection conn = null;
                  PreparedStatement ps = null;
                  
            try {
                   if (!isValid() ){
                 throw new SQLException( "invalid data - from UserRegBean.insertData" );
                 }
                 else if (isValid()==true) {
                  conn = DriverManager.getConnection( "jdbc:odbc:BillboardCompany");
                    ps = conn.prepareStatement( "INSERT INTO UserRegInformation" +
                    "(FirstName, LastName, OrganisationName, EmailAddress, Username, Password)" + "values( ?, ?, ?, ?, ? )" );
                    ps.setString( 1, firstName );
                    ps.setString( 2, lastName );
                    ps.setString( 3, orgName );
                    ps.setString( 4, userName );
                    ps.setString( 5, repassWord );
                    
                    ps.executeUpdate();
              }
              
                   
                  }
                  


                  finally {
                                  if( ps != null )
                                
                                  try {ps.close(); }
                                
                                  catch( Throwable t ) { t.printStackTrace(); }
                                  
                                
                                  if( conn!=null)
                                
                                  try {conn.close(); }
                                
                                  catch( Throwable t ) { t.printStackTrace(); }
                                  
                               
                               }
            
    }      
Avatar of Gar04

ASKER

thanks again, i just got your last message
i will give it a test and if i have any bother i will get back to u
else i will be back to award u the points u deserve!
gaz
it looks overkill to throw an exception, but if you think this way, a bean developer might not be the jsp page developer.

the jsp page developer might write code call the insertData without checking the data. and he expect data to be inserted. It will be very confused when he find that the method returned without error, but data is not in database, what's wrong? the exception is a good indication that this is because of data validation.

HTH
Avatar of Gar04

ASKER

that makes sense

hey i get this error now
org.apache.jasper.JasperException: [Microsoft][ODBC Microsoft Access Driver] Number of query values and destination fields are not the same.
  i think i know what is wrong
and will immediately try to fix it
i forgot to enter emailAddress

   ps = conn.prepareStatement( "INSERT INTO UserRegInformation" +
                 "(FirstName, LastName, OrganisationName, EmailAddress, Username, Password)" + "values( ?, ?, ?, ?, ? )" );
                 ps.setString( 1, firstName );
                 ps.setString( 2, lastName );
                 ps.setString( 3, orgName );
                 ps.setString( 4, userName );
                 ps.setString( 5, repassWord );
                 
                 ps.executeUpdate();
you forgot email address.
1. add another ? to the values(...)
2. change to follow:
                 ps.setString( 1, firstName );
                 ps.setString( 2, lastName );
                 ps.setString( 3, orgName );
                 ps.setString( 4, emailAddress );
                 ps.setString( 5, userName );
                 ps.setString( 6, repassWord );
Avatar of Gar04

ASKER

hey
it seems to be writing to the db fine
and it is working well with the validation

if you where here i would buy u a pint, actually many pints

this is cool because i guess that i can reuse the code over and over with some alterations
for more input forms

thanks a lot kennethxu
glad to know it works for you. as always, it's my pleasure to help :-)