JSP database connectivity

[ceyesuma]

Hello.
I was studying the database connectivity tutorial .
I set it up with the database in MySQL.

However I tried to set it up using Netbeans6.7.1. just to see if I could get it to run. it's not working out.

Can this be setup in netbeans? I have attached the program.
at:
http://cid-b5cb457363230793.skydrive.live.com/self.aspx/.Public/schoolTutorial.zip
I also plan to try to recreate it step by step with netbeans if possible.

any feed back on what is wrong with the example setup with netbeans would be appreciated.
-ceyesumma

[kvprajapati]

@ceyesuma : Can this be setup in netbeans?
-- YES or NO - It may be.

But I have a suggestion for you. Read & act this tutorial throughly - JDBC(TM) Database Access

[peter_budo]

@ceyesuma: Your question is confusing, if you have problems to link MySQL with NetBeans have look at this tutorial else please provide better explanation

[ceyesuma]

Thanks
Those links will be helpful.
I have not started the tutorial supplied on the JSP page.

I have the database connection in netbeans and I need to learn how to access it.

I was just starting the tutorial and I set up the whole project in netbeans and the package was a little different . It all seemed to compile but I get the error that it will not deploy.

I was trying to quickly get the example to run in the netbeans.

I plan on tearing the tutorial apart to understand how it is using the MVC Model 2

I guess I will start by creating a java web app using JSF1.2. Will this be a good start?

[peter_budo]

Leave JSF till you get good understanding of JSP and servlets

When you getting errors please post them toether with any code they reference to...

[ceyesuma]

As I am re-writing the jsp database connectivity tutorial I noticed there is a lot of good stuff in it.

it is setting up nice in netbeans so far.

I'll have to decide which code to use for the LoginServlet.
Thanks

[ceyesuma]

Topic
The topic of this question is: db_Connect does not connect.
Hello and Thank you in advance for any assistance.

System info:netbeans project,database attached or provided at:
http://cid-b5cb457363230793.skydrive.live.com/self.aspx/.Public/school.zip

The purpose of this post is: Try to get a sql statement to work

The functionality of this code is:

·  	"SELECT u.uid, firstName, lastName, address1, address2, city, postCode, email, phone, ug.groupName as userGroup "
·  	+"FROM user u, usergroup ug WHERE uid='"+userName+"' AND password='"+password+"' AND groupName IN"
·  	+" (SELECT groupName FROM usergroup WHERE groupid =(SELECT groupid FROM usergroup_mapping WHERE uid=u.uid))";

My question concerning this code is: What is wrong with the sql statement provided in tutorial. Or a description of its function .

The errors related to this code are:

Initializing Sun's JavaServer Faces implementation (1.2_04-b22-p05) for context '/school'
Initialize <------------>jdbcDriver<------------>
12345678<------------>12345678
SQLException:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'user u,usergroup ug WHERE uid='12345678' AND password='12345678' AND groupName I' at line 1
Error on DB return
java.lang.NullPointerException
        at servlets.LoginServlet.doPost(LoginServlet.java:82)

Code description:

Thanks again.
-ceyesuma

Note:

________________________________________________________________________

Code description:

[Thirusha]

I think you are missing brackets in the sql statement, when using the in clause, somthing like this :

where groupId in (1,2,3)

[ceyesuma]

I was told to add table qualifiers to the WHERE statement.

The whole thing is confusing. I'm not sure what
"add table qualifiers to the where statement"

means exactly.

Is it possible for someone to breakdown what it says(laymens terms) so I can start understanding how to change it?
-ceyesumma

[ceyesuma]

Topic
The topic of this question is:
Hello and Thank you in advance for any assistance.

System info:

Noted in previous post.

The purpose of this post is:

Display supplied stmt from above mentioned tutorial. Stmt throws an error as noted in previous post.

Display my new stmt.

Code description:supplied stmt

"SELECT u.uid, firstName, lastName, address1, address2, city, postCode, email, phone, ug.groupName as userGroup "
+"FROM user u, usergroup ug WHERE uid='"+userName+"' AND password='"+password+"' AND groupName IN"
+" (SELECT groupName FROM usergroup WHERE groupid =(SELECT groupid FROM usergroup_mapping WHERE uid=u.uid)

Code description: my new statement

String strQuery = "SELECT u.uid, u.password,ug.groupName FROM user AS u,usergroup AS ug,usergroup_mapping AS ugm WHERE u.uid='userName' AND u.password='password' AND u.uid=ugm.uid AND ugm.groupid=ug.groupid";

My question concerning this code is:

If my new stmt produces the required field when I run it on the databases. Why doesn’t the login except username=10101010 password =StealthProject which is in the data base?

The errors related to this code are:

Initializing Sun's JavaServer Faces implementation (1.2_04-b22-p05) for context '/school'
Initialize <------------>jdbcDriver<------------>
10101010<------------>StealthProject
Error on DB return
java.lang.NullPointerException
        at servlets.LoginServlet.doPost(LoginServlet.java:82)

Thanks again.
-ceyesuma

[Thirusha]

cause u would need quotation marks. when comparing values from a table where the column is not of type numeric, u have to use quotation marks like this:

username=10101010 and password = 'StealthProject'

if username is an alphanumeric column type then that too will need quotation marks as well.

I suggest googling some basic select statements to see how to write sql statements.

[ceyesuma]

Topic
The topic of this question is: jsp database connectivity ServletWrapper
Hello and Thank you in advance for any assistance.

System info:

Netbeans6.7.1,jsf1.2,MySQL(Web app).Model view Controller(MVC )Model 2.

The purpose of this post is:

As it turns out the supplied stmt is good after all but something is wrong with how the servlet is being handled I think . So I need to understand the error produced after logging in to database and all param’s are good(See note to see System.out.println code life cycle.

The functionality of this code is:

tutorial description found at:
http://www.daniweb.com/forums/thread141776.html
My netbeans project school can be found at:
http://cid-b5cb457363230793.skydrive.live.com/self.aspx/.Public/school.zip

My question concerning this code is:

1.What is the purpose of the “+” in the sql stmt. Is this basic SQL? Or is it for the Java?
2.Could someone describe what I should be looking at concerning fixing the error?

The errors related to this code are:


Error description: http browser page:Error

HTTP Status 500 -

type Exception report

message

descriptionThe server encountered an internal error () that prevented it from fulfilling this request.

exception

java.lang.UnsupportedOperationException: Not yet implemented

note The full stack traces of the exception and its root causes are available in the Sun GlassFish Enterprise Server v2.1 logs.
Sun GlassFish Enterprise Server v2.1

Note: Please find code below of program life cycle and where it blew out.

Error description: System.out.println code life cycle. And where it died!

Initializing Sun's JavaServer Faces implementation (1.2_04-b22-p05) for context '/school'
LoginServlet Init()<------------>Class.forName(config.getInitParameter('jdbcDriver')).toString<------------>Initialized the connector String
LoginServlet Init()<------------>created a new UserBean object<------------>
UserBean()<------------>in userbean constructor<------------>constructor
%in getUserData()-------------->10101010<--------------userName%
%in getUserData()-------------->StealthProject<--------------passwored %
UserBean()<------------>in userbean constructor<------------>constructor
connected to db--------->%com.mysql.jdbc.JDBC4Connection@1138a28%<----------------connection
UserBean getUserData()-------------->%SELECT u.uid, firstName, lastName, address1, address2, city, postCode, email,phone, ug.groupName as userGroup FROM user u, usergroup ug WHERE uid='10101010' AND password='StealthProject' AND groupName IN (SELECT groupName FROM usergroup WHERE groupid =(SELECT groupid FROM usergroup_mapping WHERE uid=u.uid))%<--------------my new strQuery (I created
%-------------->com.mysql.jdbc.StatementImpl@1d36fa0<--------------stmt%
%-------------->com.mysql.jdbc.JDBC4ResultSet@1506a1e<--------------rs%
UserBean()<------------>10101010<------------>uid from setter
UserBean()<------------>Patrick<------------>FirstName from setter
UserBean()<------------>Naughton<------------> Last Name from setter
UserBean()<------------>Cromwell Road<------------>Address1 from setter
UserBean()<------------>null<------------>address2 from setter
UserBean()<------------>London<------------>City from setter
UserBean()<------------>C19 90J<------------>post Code from setter
UserBean()<------------>[e-mail]<------------>Email from setter
UserBean()<------------>19770814<------------>Phone from setter
UserBean()<------------>admin<------------> UserGroup from setter
dataManager.getUserData()-------------->%10101010%<--------------getParameter(userName)
dataManager.getUserData()-------------->%StealthProject%<--------------getParameter(passsword)
doPost()-------------->%true%<--------------dbOk
StandardWrapperValve[LoginServlet]: PWC1406: Servlet.service() for servlet LoginServlet threw exception
java.lang.UnsupportedOperationException: Not yet implemented
        at model.DataManager.getUsersList(DataManager.java:123)
        at servlets.LoginServlet.doPost(LoginServlet.java:129)

Thanks again.
-ceyesuma

[peter_budo]

The plus in query uid='"+userName+"' is part of Java as you are building SQL query from simple string. The value provided between single and double quotes and plus is a value that you either retrieved from user or received by previous computation. Consider it as small plus point on the list why to use PreparedStatement instead of basic query

As for second problem I have no idea as you neither provided log file as suggested to look into by error message, neither you provided relevant code with pointing which section is working and where you getting error

[ceyesuma]

Topic
The topic of this question is: reply_servlet_wrapper
Hello and Thank you in advance for any assistance.

The purpose of this post is:

Thanks peter_budo for the reply and the tutorial I might add.

I an not farmiliar with the log you mentioned. I did trace program output where it shows the program makes it to the doPost() of the LoginServlet.java.
Program at school.zip:
http://cid-b5cb457363230793.skydrive.live.com/self.aspx/.Public/school.zip

The errors related to this code are:

Error description:trace program progress and error (Note: doPost() dbOK=true)

Initializing Sun's JavaServer Faces implementation (1.2_04-b22-p05) for context '/school'
LoginServlet Init()<------------>Class.forName(config.getInitParameter('jdbcDriver')).toString<------------>Initialized the connector String
LoginServlet Init()<------------>created a new UserBean object<------------>
UserBean()<------------>in userbean constructor<------------>constructor
%in getUserData()-------------->10101010<--------------userName%
%in getUserData()-------------->StealthProject<--------------passwored %
UserBean()<------------>in userbean constructor<------------>constructor
connected to db--------->%com.mysql.jdbc.JDBC4Connection@1401ab6%<----------------connection
UserBean getUserData()-------------->%SELECT u.uid, firstName, lastName, address1, address2, city, postCode, email,phone, ug.groupName as userGroup FROM user u, usergroup ug WHERE uid='10101010' AND password='StealthProject' AND groupName IN (SELECT groupName FROM usergroup WHERE groupid =(SELECT groupid FROM usergroup_mapping WHERE uid=u.uid))%<--------------my new strQuery (I created
%-------------->com.mysql.jdbc.StatementImpl@199c538<--------------stmt%
%-------------->com.mysql.jdbc.JDBC4ResultSet@14b1500<--------------rs%
UserBean()<------------>10101010<------------>uid from setter
UserBean()<------------>Patrick<------------>FirstName from setter
UserBean()<------------>Naughton<------------> Last Name from setter
UserBean()<------------>Cromwell Road<------------>Address1 from setter
UserBean()<------------>null<------------>address2 from setter
UserBean()<------------>London<------------>City from setter
UserBean()<------------>C19 90J<------------>post Code from setter
UserBean()<------------>[e-mail]<------------>Email from setter
UserBean()<------------>19770814<------------>Phone from setter
UserBean()<------------>admin<------------> UserGroup from setter
dataManager.getUserData()-------------->%10101010%<--------------getParameter(userName)
dataManager.getUserData()-------------->%StealthProject%<--------------getParameter(passsword)
doPost()-------------->%true%<--------------dbOk
StandardWrapperValve[LoginServlet]: PWC1406: Servlet.service() for servlet LoginServlet threw exception
java.lang.UnsupportedOperationException: Not yet implemented
        at model.DataManager.getUsersList(DataManager.java:123)
        at servlets.LoginServlet.doPost(LoginServlet.java:129)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:754)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:847)
        at org.apache.catalina.core.ApplicationFilterChain.servletService(ApplicationFilterChain.java:427)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:315)
        at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:287)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:218)
        at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:648)
        at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:593)
        at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:94)
        at com.sun.enterprise.web.PESessionLockingStandardPipeline.invoke(PESessionLockingStandardPipeline.java:98)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:222)
        at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:648)
        at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:593)
        at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:587)
        at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:1096)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:166)
        at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:648)
        at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:593)
        at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:587)
        at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:1096)
        at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:288)
        at com.sun.enterprise.web.connector.grizzly.DefaultProcessorTask.invokeAdapter(DefaultProcessorTask.java:647)
        at com.sun.enterprise.web.connector.grizzly.DefaultProcessorTask.doProcess(DefaultProcessorTask.java:579)
        at com.sun.enterprise.web.connector.grizzly.DefaultProcessorTask.process(DefaultProcessorTask.java:831)
        at com.sun.enterprise.web.connector.grizzly.DefaultReadTask.executeProcessorTask(DefaultReadTask.java:341)
        at com.sun.enterprise.web.connector.grizzly.DefaultReadTask.doTask(DefaultReadTask.java:263)
        at com.sun.enterprise.web.connector.grizzly.DefaultReadTask.doTask(DefaultReadTask.java:214)
        at com.sun.enterprise.web.portunif.PortUnificationPipeline$PUTask.doTask(PortUnificationPipeline.java:380)
        at com.sun.enterprise.web.connector.grizzly.TaskBase.run(TaskBase.java:265)
        at com.sun.enterprise.web.connector.grizzly.ssl.SSLWorkerThread.run(SSLWorkerThread.java:106)

Code description:LoginServlet.java (Note doPost() dbOk=true)

package servlets;
import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import javax.servlet.http.HttpServlet;

import beans.UserBean;
import model.DataManager;
import model.Validation;


public class LoginServlet extends HttpServlet
{

	private String dbResult = null;
	private String myException = null;
	private String strError = null;
	private boolean dbOK = false;

	// Connection string data
	private String dbURL;
	private String dbusername;
	private String dbpassword;

	//Instance of beans
	private UserBean userBean;
	private ArrayList<UserBean> students;
	private ArrayList<UserBean> teachers;
	private DataManager dataManager;
	private Validation val = new Validation();

        public void LoginServlet(){
            System.out.println("in loginServlet");
        }
	public void init(ServletConfig config) throws ServletException
	{
		super.init(config);
                
		dataManager = new DataManager();
	    dataManager.setDbURL(config.getInitParameter("dbURL"));
	    dataManager.setDbUserName(config.getInitParameter("dbUserName"));
	    dataManager.setDbPassword(config.getInitParameter("dbPassword"));
		try
		{
			Class.forName(config.getInitParameter("jdbcDriver"));
                        System.out.println("LoginServlet Init()<------------>Class.forName(config.getInitParameter('jdbcDriver')).toString<------------>Initialized the connector String");
		}
		catch (Exception ex)
		{
			System.out.println("Initialize connector string");
			ex.printStackTrace();
		}
	}

	public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
	{
		/*
		 * Get user data from submited form
		 */
		String userName = request.getParameter("userName");
		String password = request.getParameter("password");
                  
		if(val.validate(userName, password))
		{
                    System.out.println("LoginServlet Init()<------------>created a new UserBean object<------------>");
			userBean = new UserBean();
            try {
                userBean = dataManager.getUserData(userName, password);
                System.out.println("dataManager.getUserData()-------------->%"+userName+"%<--------------getParameter(userName)");
                    System.out.println("dataManager.getUserData()-------------->%"+password+"%<--------------getParameter(passsword)");
            } catch (SQLException ex) {
                Logger.getLogger(LoginServlet.class.getName()).log(Level.SEVERE, null, ex);
            }

			try
			{
				if(!userBean.getUid().equals(null))
					dbOK = true;
                                System.out.println("doPost()-------------->%"+dbOK+"%<--------------dbOk");
                    
			}
			catch(NullPointerException npe)
			{
                            System.out.println("doPost()-------------->%%<--------------blows up here");
                    
				System.out.println("Error on DB return");
				npe.printStackTrace();
				strError = "Invalid username or password.";
				dbOK = false;
			}

		}
		else
		{
			strError = "Invalid username or password.";
			dbOK = false;
		}

		HttpSession session = request.getSession(true);

		if(dbOK)
		{
			//redirect to student/teacher/admin pages
			if(userBean.getUserGroup().equals("student"))
			{
				session.setAttribute( "userBean", userBean);
				RequestDispatcher dispatcher = request.getRequestDispatcher("/student.jsp");
				dispatcher.forward( request, response);
			}
			else if(userBean.getUserGroup().equals("teacher"))
			{
				session.setAttribute( "userBean", userBean);
				students = new ArrayList<UserBean>(dataManager.getUsersList("student"));
				session.setAttribute("students", students);
				RequestDispatcher dispatcher = request.getRequestDispatcher("/teacher.jsp");
				dispatcher.forward( request, response);
			}
			else if(userBean.getUserGroup().equals("admin"))
			{
				session.setAttribute( "userBean", userBean);
				students = new ArrayList<UserBean>(dataManager.getUsersList("student"));
				session.setAttribute("students", students);
				teachers = new ArrayList<UserBean>(dataManager.getUsersList("teacher"));
				session.setAttribute("teachers", teachers);
				RequestDispatcher dispatcher = request.getRequestDispatcher("/admin.jsp");
				dispatcher.forward( request, response);
			}
		}
		else
		{
			//Error after DB login checkout, redirect back to index.jsp
			session.setAttribute( "error", strError);
			RequestDispatcher dispatcher = request.getRequestDispatcher("/index.jsp");
			dispatcher.forward( request, response);
		}
	}
}

Thanks again.
-ceyesuma


Note:

I plan on re-write in order to try prepared stmt and tag library.
so this error may or may not repeat itself.

[ceyesuma]

Hello again.
I have re-written all the previous posted code using the preparedStatement().
good stuff.

Now, When I started I chose to use JSF1.2. I think my error at described above is because there are no navigation rules (go figure lol you think that is the cause for the error?)

So, my questionsare:
1. can I leave the web.xml along now? (that is where my connecting param's are)
2. can I create the navigation rules in the face-config.xml?
3.finally, Is it possible to rewrite the pages supplied using JSF JSP jsp pages?
Thanks again
ceyesuma

[peter_budo]

You will always need web.xml for few things. If face-config.xml support navigation rules you may try to rewrite it, but unfortunately I cannot be of any assistance because I did not use JSF as yet.
If you succeed please post it on forum for others to see it.

[ceyesuma]

Hello.
I worked all night on a couple other projects but I believe the Netbeans project is still at:
http://cid-b5cb457363230793.skydrive.live.com/self.aspx/.Public/school.zip
I think that one is still not using preparedStatements though.

I will work on it soon. However if some one where to open it in netbeans I believe it would just be a matter of adding a dataManagerController.java that the jsf jsp page talks to and the controler talks to the model DataManager.java dataManager.isStudent()which has the method that would return the string "student","admin" etc to the
faces-conf.xml which has the case and navigation for the pages.

a good simple calulator example is at
http://www.ibm.com/developerworks/library/j-jsf1/#N100D3

I cant upload a worksheet.xls (excel) I have a summary of that tutorial named simpljsf.zip at:
http://cid-b5cb457363230793.skydrive.live.com/self.aspx/.Public/simplejsf.zip

thanks
-ceyesumma