Solved: insert data + Java | Experts Exchange

2/27/2007

ASKER

I have been looking for examples online on how to get the data from my form to the servlet and the bean. I am using something like this in my servlet, but I continue to get a NullPointerException on the my servlet here: con = cb.getConnection();

DSRBean db = (DSRBean) request.getAttribute("dsrBean");
HttpSession session = request.getSession();
Beans.ConnectionBean cb = (Beans.ConnectionBean) session.getAttribute("DBConnection");
con = cb.getConnection();

String page;
if(db.insertCreateDSR(con)) {

I am guessing it is because I am not setting up my jsp form up correctly.

Any thoughts?

mrcoffee365

2/27/2007

I think we need to see the code where you instantiate the ConnectionBean. getConnection is supposed to return a Connection object, but if you're getting a NullPointerException, then probably you aren't setting it when the ConnectionBean is being created.

OliviaRedhorse

2/27/2007

ASKER

Here is how the connection bean is created.

public class ConnectionBean {
protected Connection connection = null;

public ConnectionBean() {
if(connection == null) {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

String filename = "D:\\databaselocation.mdb";
String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";
database+=filename.trim() + ";DriverID=22;READONLY=true}";

connection = DriverManager.getConnection(database,"","");
System.out.println("Connection to database created");
}catch(ClassNotFoundException cnfe) {
System.out.println("Cound not load jdbc-odbc driver");
cnfe.printStackTrace();
}catch(SQLException se) {
System.out.println("Could not open connection to database");
se.printStackTrace();
}
}
}

public Connection getConnection() {
return connection;
}
public void setConnection(Connection connection) {
this.connection = connection;
}
public boolean closeConnection() {
if(connection != null) {
try {
connection.close();
return true;
}catch(SQLException se) {
se.printStackTrace();
return false;
}
}
return true;
}
}

Right now, I have do not have anything in the JSP form that connects to the database. It is just a simple form that allows the user to create a record. I am confused to how store my data to the bean and then evaluate through the servlet. I tried several things but no luck.

mrcoffee365

2/27/2007

Great. Now where is the code where you create the ConnectionBean and put it into your session? It needs to be called before you call the JSP code that uses the connection.

OliviaRedhorse

2/27/2007

ASKER

Something like this correct?

<%!
private HttpSession session;
private Connection con;
%>
<%
session=request.getSession();
Boolean b = (Boolean) session.getAttribute("SomebodyIn");
if(b==null || !b.booleanValue()) {
%>
<html>
<head>
<script language="javascript">
window.open("index.jsp", "_self");
</script>
</head>
</html>
<%
return;
}
Beans.ConnectionBean cb = (Beans.ConnectionBean) session.getAttribute("DBConnection");
con = cb.getConnection();
%>

OliviaRedhorse

2/27/2007

ASKER

Does this need to be at the beginning of every page that refers to a session?

mrcoffee365

2/27/2007

But I don't see where you create the ConnectionBean which goes into the session before you called session.getAttribute("DBConnection"). Where did you do that?

If you don't have code for it, then that's why there's no connection when you try to use it.

You have to do something like
Beans.ConnectionBean cbstart = new Beans.ConnectionBean();
session.setAttribute("DBConnection",cbstart);

Then later you can do a session.getAttribute on the ConnectionBean.

>>Does this need to be at the beginning of every page that refers to a session?
I'm not sure what you're asking here. If you are going to do a database connection in your jsp file, then it needs to refer to the ConnectionBean, which means that you'll have to make sure that there is a ConnectionBean in the session, and if not, create it.

OliviaRedhorse

2/27/2007

ASKER

Sorry, but I am alittle confused.
I am trying to create a servlet which will get the data from the jsp form, how should the servlet look like? I don't think I created this servlet correctly or may be it is in the form. I currently have nothing in my jsp form except a post when the submit button is clicked. Is this correct?

Here is what my servlet looks like now.
Connection con = null;
response.setContentType("text/html;charset=UTF-8");
PrintWriter out = response.getWriter();
DSRBean db = (DSRBean) request.getAttribute("dsrBean");
HttpSession session = request.getSession();
Beans.ConnectionBean cb = (Beans.ConnectionBean) session.getAttribute("DBConnection");
con = cb.getConnection();

String page;
if(db.insertCreateDSR(con)) {
page = "//CreateDSR.jsp?";
page +="msgStatus=DSRadded&";
page += "txtFirstName=" + db.getFirstName() + "&";
page += "txtLastName=" + db.getLastName() + "&";
page += "txtEmail=" + db.getEmail() + "&";
}
Thank you for your time.

mrcoffee365

2/27/2007

Your servlet might be fine. What you have to do is create the ConnectionBean object, otherwise it doesn't exist and has no connection to get when you do cb.getConnection().

The way you have your ConnectionBean constructor, I think you could call the constructor every time. So every time you use the bean, you could do this:

Beans.ConnectionBean cb = (Beans.ConnectionBean) session.getAttribute("DBConnection");
if( cb == null ) {
Beans.ConnectionBean cb = new Beans.ConnectionBean();
session.setAttribute("DBConnection",cb);
}
con = cb.getConnection();

It's a little repetitive to call it with every use of the DBConnection attribute, because usually you would want to call the constructor for your ConnectionBean once per session (I'm assuming), but this is fine.

To call and set the session once, you normally would create DBConnection at user login, for example. However, for anonymous access, it's trickier to make sure that you have already created the dbconnection for this anonymous user session (the session is created for you by Tomcat), so you might have to check that you have the ConnectionBean every time you use it.

OliviaRedhorse

2/28/2007

ASKER

Ah, ....I see. Thank you for your patience.
I implemented the code above and I get an error for duplicates cb variables already declared. I tried to create another variable but I get the same error. Just so I know I implemented it correctly, I am testing it in the servlet. How do I resolve this?

mrcoffee365

2/28/2007

Right -- sorry -- there's a typo. The error told you exactly what it was -- cb is declared twice. So declare it once in the first line, then just set it in the second:

Beans.ConnectionBean cb = (Beans.ConnectionBean) session.getAttribute("DBConnection");
if( cb == null ) {
cb = new Beans.ConnectionBean();
session.setAttribute("DBConnection",cb);
}

Sometimes the compiler is right!

OliviaRedhorse

2/28/2007

ASKER

One more question, ...Does the following code need to be in the jsp form file?

<jsp:useBean id="DBConnection" scope="request" class="Beans.ConnectionBean" />
<jsp:setProperty name="DBConnection" property="*" />

OliviaRedhorse

2/28/2007

ASKER

Now, I have a null pointer exception here:

if(db.insertCreateDSR(con)) { //at this statement
page = "//CreateDSR.jsp?";
page +="msgStatus=DSRadded&";
page += "txtFirstName=" + db.getFirstName() + "&";
page += "txtLastName=" + db.getLastName() + "&";
page += "txtEmail=" + db.getEmail() + "&";
}

Here is what insertCreateDSR(con) looks like;

public boolean insertCreateDSR(Connection con){
if(con==null) return false;
try {
Statement stmt = con.createStatement();
java.util.Date dt = new java.util.Date();
DateFormat df = DateFormat.getDateTimeInstance(DateFormat.LONG,DateFormat.SHORT);
String date = df.format(dt);
dateCreated = date;

String sql = "insert into ";
sql += TableName.EDSR + "(";
sql += "DATECREAT, REQ_FIRSTNAME, REQ_LASTNAME, REQ_EMAIL" + ")";
sql += "values(";
sql += FieldName.DATECREAT + ",";
sql += FieldName.REQ_FIRSTNAME + ",";
sql += FieldName.REQ_LASTNAME + ",";
sql += FieldName.REQ_EMAIL;
sql += ");";

System.out.println(sql);
stmt.executeUpdate(sql);
stmt.close();
return true;
}catch(SQLException se) {
se.printStackTrace();
return false;
}

}
What I am doing wrong here?

mrcoffee365

2/28/2007

Have you tried it? The code we've been discussing explicitly creates and uses the ConnectionBean object. If you declare it as a Bean, then the JSP code should probably change to use the get and set methods for the bean rather than the straight Java we've been writing.

This page has a simple tutorial on using a database connection bean in a JSP page:
http://www.roseindia.net/jsp/usingbeansinjsp.shtml

mrcoffee365

2/28/2007

Sorry -- I didn't see your last post. So my previous post was for your previous question.

>>Now, I have a null pointer exception here:

>>if(db.insertCreateDSR(con)) { //at this statement

Your db object is null. So where is the db object created?

OliviaRedhorse

2/28/2007

ASKER

The db is created before the cb connection.
In servlet....
Connection con = null;
response.setContentType("text/html;charset=UTF-8");
PrintWriter out = response.getWriter();
DSRBean db = (DSRBean) request.getAttribute("dsrBean");
HttpSession session = request.getSession();
Beans.ConnectionBean cb = (Beans.ConnectionBean) session.getAttribute("DBConnection");
if( cb == null ) {
cb = new Beans.ConnectionBean();
session.setAttribute("DBConnection",cb);
}

String page;
if(db.insertCreateDSR(con)) {

mrcoffee365

2/28/2007

And how is it that db is passed into the program that is using it?

OliviaRedhorse

3/1/2007

ASKER

Okay, I have been searching on the internet for examples of how to insert data into a database with using JSP, servlets and beans. I've had no luck. I did look through the link you provided. How do I resolve this problem. If you have any information please share.
I believe my problem is getting the data from the jsp form to the bean and then accessing the bean from the servlet. Just as you address in your previous message.

Thank you so much for your patience. :)

mrcoffee365

3/1/2007

It might be good to read some tutorials or books on JSP/beans/servlets. Sun has great online tutorials. This one is for JSP:
http://java.sun.com/j2ee/tutorial/1_3-fcs/doc/JSPIntro.html

This one is for JSP/Beans/Struts:
http://java.sun.com/j2ee/tutorial/1_3-fcs/doc/JSPTags3.html

And a great book I've used is
Head First Servlets & JSP from O'Reilly (by Basham, Sierra, and Bates)
Excellent explanations, at both overview and deeply technical levels.

That said, you seem to be close. If you've lost the will to debug, I understand. But for all of this, you usually have to keep plugging away.

The most recent problem is that you have a null "db" object. Also, your "con" object is null, because although you create one in the ConnectionBean, you never assign it to the local con object declared in the code above.

You need to get the database connection you created when you instantiated your ConnectionBean, and use that.

So something like:
con = cb.getConnection();

Also, if you want to use the DSRBean, you have to instantiate it. Which I don't see in the code fragment you posted above. Just as with ConnectionBean, you need to do something like

DSRBean db = (DSRBean) session.getAttribute("dsrBean");
if( db == null ) {
db = new DSRBean ();
session.setAttribute("dsrBean",db);
}

then it won't be null.

Back on the tutorials -- the Sun Java online tutorial is also great:
http://java.sun.com/docs/books/tutorial/index.html

OliviaRedhorse

3/1/2007

ASKER

Thank you so much for your help. :0)

Well, I am still working on it. I feel like I am so close to getting the answer. Here is what I have, thats if you are interested. I have been reading and looking at examples and I have made some changes.

In my jsp form, I have no "<jsp:useBean...>" bean tags. I simply use the following statement.

<form action="ServletName" method="post" enctype="multipart/form-data" name="formName" onsubmit="return validate();">

In my servlet, I have this.

public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
Connection con = null;

response.setContentType("text/html;charset=UTF-8");
PrintWriter out = response.getWriter();

//Create a new instance of the bean
DSRBean db = new Beans.DSRBean();
db.setFirstName(request.getParameter("FirstName"));
db.setLastName(request.getParameter("LastName"));
db.setEmail(request.getParameter("Email"));

HttpSession session = request.getSession();
db = (DSRBean) session.getAttribute("dsrBean");
session.setAttribute("dsrBean", db);

String fname = db.getFirstName(); //This is line 56
String lname = db.getLastName();
String email = db.getEmail();

//tried to test if this would work, nada
fname = request.getParameter("FirstName");

//check I get anything here, all I get is "null" for all
System.out.print(fname);
System.out.print(lname);
System.out.print(email);
System.out.print(phone);
System.out.print(mailCode);
System.out.print(dateRequired);
System.out.print(chargeCode);
System.out.print(briefDescription);
System.out.print(detailedDescription);

Beans.ConnectionBean cb = (Beans.ConnectionBean) session.getAttribute("DBConnection");

if( cb == null ) {
cb = new Beans.ConnectionBean();
session.setAttribute("DBConnection",cb);
}
con = cb.getConnection();

String page = "//ResultForm.jsp?";
if(db.insertCreateDSR(con)) {
page = "//ResultForm.jsp?";
page +="msgStatus=DSRadded&";
page += "FirstName=" + db.getFirstName() + "&";
page += "LastName=" + db.getLastName() + "&";
page += "Email=" + db.getEmail();
}
getServletConfig().getServletContext().getRequestDispatcher(page).forward(request, response);
out.close;
}

public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}

Then in the insertCreateDSR(con) method.

public boolean insertCreateDSR(Connection con){
if(con==null) return false;
try {
Statement stmt = con.createStatement();
java.util.Date dt = new java.util.Date();
DateFormat df = new SimpleDateFormat("MM/dd/yyyy");
String date = df.format(dt);
dateCreated = date;
int dsr = 506;

String sql = "insert into ";
sql += TableName.EDSR + "(";
sql += FieldName.DSR + ", ";
sql += FieldName.DATECREAT + ", ";
sql += FieldName.REQ_FIRSTNAME + ", ";
sql += FieldName.REQ_LASTNAME + ", ";
sql += FieldName.REQ_EMAIL + ")";
sql += " values(";
sql += dsr + ", ";
sql += dateCreated + ", ";
sql += getFirstName() + ", ";
sql += getLastName() + ", ";
sql += getEmail() ;
sql += ")";

System.out.println(sql);
stmt.executeUpdate(sql);
stmt.close();
return true;
}catch(SQLException se) {
se.printStackTrace();
return false;
}

}

The connection bean is still the same.

Now, when I run this, this get;
Mar 1, 2007 11:35:19 AM org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet ControlServlet threw exception
java.lang.NullPointerException
at Controller.ControlServlet.doGet(ControlServlet.java:56)
at Controller.ControlServlet.doPost(ControlServlet.java:193)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:368)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Thread.java:595)

I have working on it so long, I don't which is right or wrong anymore. Do you see anything that I am doing wrong here?
One thing that just gets to me is, how do I retrieve the data from the jsp form to my servlet with using a bean?

Thanks,

mrcoffee365

3/1/2007

This one's pretty easy. You're still using a null db object -- differently this time.

//Create a new instance of the bean
DSRBean db = new Beans.DSRBean();
db.setFirstName(request.getParameter("FirstName"));
db.setLastName(request.getParameter("LastName"));
db.setEmail(request.getParameter("Email"));

HttpSession session = request.getSession();
db = (DSRBean) session.getAttribute("dsrBean");
session.setAttribute("dsrBean", db);

See how you create a db object, but don't put it into your session? Then you set db to the session attribute which is null, because you never did a setAttribute.

So add
session.setAttribute("dsrBean",db);
so that you can do a get successfully later.

OliviaRedhorse

3/1/2007

ASKER

Sorry, little confused because I do have setAttribute in there.
HttpSession session = request.getSession();
db = (DSRBean) session.getAttribute("dsrBean");
session.setAttribute("dsrBean", db);

I guess I am not understanding what you mean by
"See how you create a db object, but don't put it into your session? Then you set db to the session attribute which is null, because you never did a setAttribute.

So add
session.setAttribute("dsrBean",db);
so that you can do a get successfully later."

Sorry, but could you clarify further?
Thanks.

OliviaRedhorse

3/1/2007

ASKER

Thank you I understand now. Sorry, I have working on this just too long. The errors have went away. I do have another question but I will ask under a different topic because it deals with insert statements.

But, if you think you know the answer to this do tell. Otherwise thank you so much for your help and patience.

Connection to EDSR database created ///This is from the connection bean
insert into edsr_tbl(DSR, DATECREAT, REQ_FIRSTNAME, REQ_LASTNAME, REQ_EMAIL, REQ_PHONE, REQ_MAIL, REQ_DATREQ, REQ_CHCODE, REQ_BRIEF, REQ_DETAILED) values(512, 03/01/2007, null, null, null, null, null, null, null, null, null) //this is from the bean itself

mrcoffee365

3/1/2007

Congrats on getting it working.

I'm not sure what your last question is, but I'll look for it when you put the next one in.

Are you wondering why the fields are null? It probably means that you didn't set them.

Given the confusion around the db and cb objects, and whether they are in the session or not, I'd look through the code very carefully for places where you setAttribute and getAttribute.

If you're still using NetBeans, you can walk through this and see why the parameters aren't being set in the dsrBean, right?

OliviaRedhorse

3/1/2007

ASKER

Thanks.
I will be looking closely at the set and get attributes.

mrcoffee365

3/5/2007

In looking at your thread on "inserted null data + MS Access" I saw that you are doing file upload. The process for getting form data is different for a multipart-encoded form. That's why your input parms seemed to be null.

If you haven't already solved it, and need help, feel free to start another question, and I'll be able to help with that one.