Forum:

JDBC and Relational Databases

Updating/inserting data through servlet using JDBC

Greenhorn

Posts: 18

posted 11 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

I am trying to make a Social network site where a user can log or register. Once logged in with correct credentials, the user could search in a friends list and invite him/her to be friend.

So far I have made 2 jsp files-
[1] index.jsp [2] UserRegistration.jsp

and HttpServlets-UserLogIn and RegistrationServlet

The Dynamic project gets connected through Tomcat using JDBC 5.1.23 to mySQL database.

schema is SocialNetwork with 2 tables:

My index.jsp in webContent folder is:

my UserRegistration JSP is

userLogIn servlet is:

package Web;

import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebInitParam;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@WebServlet(urlPatterns = { "/userLogin" }, initParams = { @WebInitParam(name = "userLogin", value = "userLogin") })
public class userLogin extends HttpServlet {
	private static final long serialVersionUID = 1L;

public userLogin() {
		super();

}

protected void doGet(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {

}

protected void doPost(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {
		// get attributes from the jsp form

String user = req.getParameter("userName");
		String pw = req.getParameter("userPW");

// print those attribute to the console

System.out.println("user Name-" + user);
		System.out.println("user Name-" + pw);

// send response as HTML to browser
		resp.setContentType("text/html");

PrintWriter out = resp.getWriter();

out.println("<html>");
		out.println("<head><title> Social Network </title></head>");
		out.println("<body>");
		out.println("Hello " + user);
		out.println(" ");
		out.println("you are registared as " + user);
		out.println(" ");
		out.println("Your Password is " + pw);
		out.println("</body></html>");

}
}

RegistrationServlet is :-

package Web;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@WebServlet("/RegistrationServlet")
public class RegistrationServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;

public RegistrationServlet() {
		super();

}

protected void doGet(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {

}

protected void doPost(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {

String nUser = req.getParameter("newUser");
		String nPW = req.getParameter("newUserPW");

String age = req.getParameter("age");
		// print those attribute to the console

System.out.println("user Name-" + nUser);
		System.out.println("user Name-" + nPW);

// send response as HTML to browser
		resp.setContentType("text/html");

PrintWriter out = resp.getWriter();

out.println("<html>");
		out.println("<head><title> Social Network </title></head>");
		out.println("<body>");
		out.println("Hello " + nUser);
		out.println(" you typed pw as " + nPW);
		out.println(" your age is " + age);
		// ********************************************************************
		// JDBC driver name and database URL
		final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
		final String DB_URL = "jdbc:mysql://localhost:3306/SocialNetwork";
		// ***********************************************************************
		// Database credentials
		final String USER = "root";
		final String PASS = "root";
		Connection conn = null;
		Statement stmt = null;
		try {
			// Register JDBC driver
			Class.forName(JDBC_DRIVER);

// connection to mySQL server and  Open a connection
			System.out.println("Connecting to a selected database...");
			conn = DriverManager.getConnection(DB_URL, USER, PASS);
			System.out.println("Connected database successfully...");

// create a Statement object ()
			System.out.println("Inserting records into the table...");
			stmt = conn.createStatement();

//  Execute a query
			String sql = "INSERT INTO SocialNetwork.userInfo"
					+ "(userName, userPW)" + "VALUES('" + nUser + "','" + nPW
					+ "')";
			int i = stmt.executeUpdate(sql);
			out.println("Connection successfull.." + i);

//Update the Db
			stmt.executeUpdate(sql);

// executeQuery () method and save it on ResultSet

sql = "SELECT * FROM SocialNetwork.userInfo";

ResultSet rs = stmt.executeQuery(sql);

// check inserted rows in the Db table
			while (rs.next()) {
				String Db_Name = rs.getString("userName");
				String passwordFromDb = rs.getString("userPW");
				// rs.getString(newPassW)
				out.println(" DbName **" + Db_Name);
				out.println("->DbPass **" + passwordFromDb);

}

} catch (Exception e) {
			e.printStackTrace();
		}

I am not clear about the inserting data in to the DataBase and connection.

How could I connect the UserLogin servlet to the UserInfo table so that if the user’s name and password are found and matched in the DB , he could see his/her friends’ list.

If that username and password is not found in the UserInfo, a message should display that the user has to Register first.

Once the user is logged-in, he should see a list of all the Usernames and Age.
Later, the user could select a user from that list and invite that user to be his/her friend.

Right now I am getting attributes added to userInfo table in username and userPW columns:[ serial# is th PK] when the user Register on RegistrationServlet:

Ashwini Kashyap

Ranch Hand

Posts: 90

posted 11 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

For connecting to other servlet from first, you can make use of RequestDispatcher here.

Also, for inserting or updating the records in database, make use of PreparedStatement class of JDBC.

Thanks,
Ashwini Kashyap | www.infocepts.com

induz sat

Greenhorn

Posts: 18

posted 11 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Thanks for suggestions.

if I use PreparedStatement I dont know why I have to add cast to PrepareStatement.

Also my first column is serial# and its PK with AIncrement. I get "No value specified for parameter 1" on tomcat console. Line 13.

How to go about it?

Ashwini Kashyap

Ranch Hand

Posts: 90

posted 11 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

You don't need to type cast it. Its returning PreparedStatement object only.

Also, there is no need to set primary key as its auto increment, JDBC internally will provide it for you.

Thanks,
Ashwini

induz sat

Greenhorn

Posts: 18

posted 11 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

You don't need to type cast it. Its returning PreparedStatement object only.

I get red underline in Eclipse with

For Auto increment I corrected it:

now my data is being saved in the userInfo Db and serial# is being auto incremented.

Now Using my first index jsp, how can I check userName and password against the UserInfo data for authenticated credentials?

Thanks

Ashwini Kashyap

Ranch Hand

Posts: 90

posted 11 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

For that you may make use of select query using Statement class and check entry against UserInfo.