Insert data to database using servlet

[Tom Nguyen]

How can we insert data through java servlet?

I can insert data to table by using JSP Scriptlet easily but I have a hard time with servlet.

public class UsertToDatabase() {
public boolean inserUser(String name, String email, String pwd) throws SQLException {
		PreparedStatement pstmt = null;
		boolean flag = false;
		System.out.println("start");
		openConn();
		System.out.println("start_1");
		if(conn == null) {
			System.err.println("Fail Connect to the Database");
			return false;
		} else {
			String insertQuery = 
					"INSERT INTO users (name, email, password) VALUES (?, ? ,?)";

			// Perform insertion.
			try {		

				conn.setAutoCommit(false);
				pstmt = conn.prepareStatement(insertQuery);

				pstmt.setString(1, name);
				pstmt.setString(2, email);
				pstmt.setString(3, pwd);
				int rowCount = pstmt.executeUpdate();
				
				System.out.println("UsersToDB - insertUser: " + rowCount);
				conn.commit();
				flag = true;
				conn.setAutoCommit(true);
			
				pstmt.close();
				conn.close();
				System.out.println(
					"Insert/create the USER Success - TRANSACTION COMPLETE");
			} catch (SQLException e) {
				e.printStackTrace();

				System.err.println(
					"There Is Something Wrong. I Don't Know Where. SOS...");
				flag = false;
			} finally {
				if(pstmt != null) {
					pstmt.close();
				}
				if(conn != null) {
					conn.close();
				}
			}
		}
		
		return flag;
	}
	
	public static void main(String args[]) {
		Random rand = new Random();
		String username = "user name" + rand.nextInt();
		String email = "user email" + rand.nextInt();
		String password = "user password";
		UsersToDB a = new UsersToDB();
		try {
			a.inserUser(username, email, password);
		} catch(Exception e)
		{	}
	}
}

I am positive that the above code function fine. The problem is whenever I call UserToDB() class inside the servlet, and I got this error: java.lang.ClassNotFoundException: org.postgresql.Driver which mean that my conn (connection) is not connected to the database but I have ensure to do so by calling openConn() before start the transaction.

This is my servlet code:

protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String username = request.getParameter("username");
		String email = request.getParameter("email");
		String password = request.getParameter("password");
		
		List<String> errList = new ArrayList<String>();
		String errMsg;
		// Add some errMsg to errList ...
		
		if(errList.size() > 0) {
			System.out.println("errList");
			request.setAttribute("loginErr", errList);
		} else {             // No error, begin insert transaction
			UsersToDB action = new UsersToDB();
			try {
				action.inserUser(username, email, password);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			request.setAttribute("successLogin", "Hello " + username);
		}
				
		rd = request.getRequestDispatcher("login.jsp");
		rd.forward(request, response);
	}

[Ulf Dittmer]

Welcome to the Ranch.

java.lang.ClassNotFoundException: org.postgresql.Driver which mean that my conn (connection) is not connected to the database

That means that the JDBC driver is not in the classpath of your web app. You should put the jar file into the WEB-INF/lib directory.

[Tom Nguyen]

I did include the postgresql driver in my lib that why I can test my insertion code as well as my JSP scriptlet code. But when I switch from scriptlet to servlet, it gives that error which makes me so confused to debug it.

[Ulf Dittmer]

Post the openConn method.

[Tom Nguyen]

openConn (because it is under UserToDB so I set it private):

private void openConn() throws SQLException {
		db = new MyDatabase();
		conn = db.getConnection();
}

MyDatabase - getConnection():

public Connection getConnection() throws SQLException {
		try{
			Class.forName("org.postgresql.Driver");		// Postgresql driver.
			try {
				String url="jdbc:postgresql://127.0.0.1:5432/" + dbName;	
				String user="postgres";
				String pw="123";
				conn = DriverManager.getConnection(url, user, pw);		// Perform connection.
			} catch(SQLException e) {
				e.printStackTrace();	// Handle JDBC exception.
				System.err.println("Cannot connect to the database...");
			} catch (Exception e) {
				e.printStackTrace();	
				System.err.println(e.getClass().getName() + ": " + e.getMessage());
				System.exit(0);
			}
		}catch(Exception e){
			e.printStackTrace();		// Handler Class.forName.
			System.err.println("Driver error");
		}
		System.out.println("Opened database '" + dbName + "' successfully!");
		return conn;
	}

[Ulf Dittmer]

The code is a bit odd, as it would print "Opened database successfully" even if there are previous errors :-)

OK, so line 3 loads the DB driver, but that fails because the class isn't there somehow. It's odd that it would work within a JSP, but not in a servlet - are both part of the same web app? The one that has the driver jar file in WEB-INF/lib?

[Tom Nguyen]

Oh, that message is odd ; but except that, there is nothing wrong with the code, isnt there? Any way, they are both resided same directory. That why I am so confused now.

[Ivan Krasic]

Tom Nguyen wrote:Oh, that message is odd ; but except that, there is nothing wrong with the code, isnt there? Any way, they are both resided same directory. That why I am so confused now.

where is the dbName value ?

[Tom Nguyen]

It is global variable of MyDatabase