Solved

jsp/Javabean insert to database question

Posted on 2004-03-22
28
730 Views
Last Modified: 2008-01-16
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);
       }

   
 }
                
0
Comment
Question by:Gar04
  • 15
  • 13
28 Comments
 
LVL 14

Accepted Solution

by:
kennethxu earned 500 total points
ID: 10654967
yes, you need to another method to do the insert, you'll need jdbc programming, it is not difficult if you already know sql.

firstly, you need to import package java.sql.*

here is an basically example of insert
Class.forName( "[the real jdbc driver name here]" );
Connection conn = null;
PreparedStatement ps = null;
try {
  conn = DriverManager.getConnection( "[connection string to your database]", dbuser, password );
  ps = conn.prepareStatement( "insert into tablename( col1, col2 ) values( ?, ? )" );
  ps.setInt( 1, id );
  ps.setString( 2, name );
  ...
  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(); }
}
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 10654975
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?
0
 

Author Comment

by:Gar04
ID: 10655263
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
0
 

Author Comment

by:Gar04
ID: 10655268
essentially, the method will be called if the data is valid right??
gaz
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 10657746
>> 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.
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 10657756
for example, in you jsp page, you can call isValid(), if it returns true, then call the insert method.
0
 

Author Comment

by:Gar04
ID: 10659250
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(); }
        }
                  
    }      
0
 

Author Comment

by:Gar04
ID: 10659343
>>>
sorry i just spotted this:
 ps.setInt( 1, firstName );
it should be
ps.setString(1, firstName);
0
 

Author Comment

by:Gar04
ID: 10659423
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
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 10659599
>> 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");
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 10659615
>> 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 :)
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 10659628
>> 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.
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 10659644
>> it should be
>> ps.setString(1, firstName);

good catch!
0
 

Author Comment

by:Gar04
ID: 10659852
>>>"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" +
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 14

Expert Comment

by:kennethxu
ID: 10659929
you can throw those exception out of this method for now.

public void insertData() throws ClassNotFoundException, SQLException {
0
 

Author Comment

by:Gar04
ID: 10659995
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
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 10660059
hmm... I'm not quite sure of your question. I think you need to call the insertData in the jsp file.
0
 

Author Comment

by:Gar04
ID: 10660122
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(); }
                               
                               }
                  
          }
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 10660338
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 {
0
 

Author Comment

by:Gar04
ID: 10661292
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
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 10661475
>> 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(); %>
0
 

Author Comment

by:Gar04
ID: 10661504
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(); }
                                  
                               
                               }
            
    }      
0
 

Author Comment

by:Gar04
ID: 10661540
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
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 10661764
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
0
 

Author Comment

by:Gar04
ID: 10661860
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();
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 10662061
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 );
0
 

Author Comment

by:Gar04
ID: 10662347
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
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 10662368
glad to know it works for you. as always, it's my pleasure to help :-)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Use of TCL script on Cisco devices:  - create file and merge it with running configuration to apply configuration changes
For cloud, the “train has left the station” and in the Microsoft ERP & CRM world, that means the next generation of enterprise software from Microsoft is here: Dynamics 365 is Microsoft’s new integrated business solution that unifies CRM and ERP fun…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now