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.isValidEmai lAddr(emai lAddress)) {
details.addElement("Invali d Email Format:"+emailAddress+",tr y 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.isValidEmailA ddr(emailA ddress) && userName != null &&
passWord != null && repassWord != null && repassWord.equals(passWord );
}
}
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.isValidEmai
details.addElement("Invali
}
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.isValidEmailA
passWord != null && repassWord != null && repassWord.equals(passWord
}
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
essentially, the method will be called if the data is valid right??
gaz
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.
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.
ASKER
is this the correct type of method?
Gaz
public void insertData() {
Class.forName("[sun.jdbc.a dbc.jdbcOd bcDriver]" );
Connection conn = null;
PreparedStatement ps = null;
try {
conn = DriverManager.getConnectio n( "[jdbc:odbc:BillboardCompa ny]", 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(); }
}
}
Gaz
public void insertData() {
Class.forName("[sun.jdbc.a
Connection conn = null;
PreparedStatement ps = null;
try {
conn = DriverManager.getConnectio
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(); }
}
}
ASKER
>>>
sorry i just spotted this:
ps.setInt( 1, firstName );
it should be
ps.setString(1, firstName);
sorry i just spotted this:
ps.setInt( 1, firstName );
it should be
ps.setString(1, firstName);
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.getConnectio n( "[jdbc:odbc:BillboardCompa ny]", 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
a question; the only errors in the code now relates to this: conn = DriverManager.getConnectio
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.a dbc.jdbcOd bcDriver]" );
I think it should be odbc instead of adbc and you don't need '[]'
Class.forName("sun.jdbc.od bc.jdbcOdb cDriver");
I think it should be odbc instead of adbc and you don't need '[]'
Class.forName("sun.jdbc.od
>> 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 :)
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.getConnectio n( "[jdbc:odbc:BillboardCompa ny]", 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.
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!
>> ps.setString(1, firstName);
good catch!
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\UserRegBea n.java:137 : unreported exception java.lang.ClassNotFoundExc eption; must be caught or declared to be thrown
Class.forName("sun.jdbc.od bc.JdbcOdb cDriver");
^
C:\Documents and Settings\Amanda Blanton\Desktop\UserRegBea n.java:142 : unreported exception java.sql.SQLException; must be caught or declared to be thrown
conn = DriverManager.getConnectio n( "jdbc:odbc:BillboardCompan y");
^
C:\Documents and Settings\Amanda Blanton\Desktop\UserRegBea n.java:146 : unreported exception java.sql.SQLException; must be caught or declared to be thrown
ps = conn.prepareStatement( "INSERT INTO UserRegInformation" +
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\UserRegBea
Class.forName("sun.jdbc.od
^
C:\Documents and Settings\Amanda Blanton\Desktop\UserRegBea
conn = DriverManager.getConnectio
^
C:\Documents and Settings\Amanda Blanton\Desktop\UserRegBea
ps = conn.prepareStatement( "INSERT INTO UserRegInformation" +
you can throw those exception out of this method for now.
public void insertData() throws ClassNotFoundException, SQLException {
public void insertData() throws ClassNotFoundException, SQLException {
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
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.
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.od bc.JdbcOdb cDriver");
Connection conn = null;
PreparedStatement ps = null;
try {
if (isValid()==true){
conn = DriverManager.getConnectio n( "jdbc:odbc:BillboardCompan y");
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(); }
}
}
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.od
Connection conn = null;
PreparedStatement ps = null;
try {
if (isValid()==true){
conn = DriverManager.getConnectio
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 {
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 {
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
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(); %>
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(); %>
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.od bc.JdbcOdb cDriver");
Connection conn = null;
PreparedStatement ps = null;
try {
if (!isValid() ){
throw new SQLException( "invalid data - from UserRegBean.insertData" );
}
else if (isValid()==true) {
conn = DriverManager.getConnectio n( "jdbc:odbc:BillboardCompan y");
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(); }
}
}
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.od
Connection conn = null;
PreparedStatement ps = null;
try {
if (!isValid() ){
throw new SQLException( "invalid data - from UserRegBean.insertData" );
}
else if (isValid()==true) {
conn = DriverManager.getConnectio
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(); }
}
}
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
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
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
ASKER
that makes sense
hey i get this error now
org.apache.jasper.JasperEx ception: [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();
hey i get this error now
org.apache.jasper.JasperEx
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 );
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 );
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
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 :-)
you can also make use of tomcat connection pool for better performance.
BTW, what database software r u using?