Introduction

This tutorial shows how to check username availability using Servlet, AJAX and MySQL. Sometimes we need to check username availability instantly before a user presses the submit button after filling a long-sized signup form. In this case we can use AJAX with any technologies like PHP, Codeigniter, Servlet, Struts, JSF, Spring etc. for checking whether the input username is avaialable or already occupied by someone else. So if we give instant result to the user for username availability then sometimes it makes more sensible than while pressing the submit button and goes top of the signup form to rectify the username input field if input username is not available.

So after finishing this example you will be able to apply the same logic to any Servlet based web framework or any server side technologies such as PHP, Codeigniter etc.

If you need the similar example using Codeigniter framework and PHP please read here Username availability check using Codeigniter, AJAX and MySQL Username availability check using PHP, AJAX and MySQL respectively.

This example uses JavaScript event onblur for checking user availability. onblur means when focus is out of a particular object such as inputbox, textarea etc.

Prerequsites

Knowledge of Java, jQuery, Servlet, MySQL Database

Apache Tomcat 7, Eclipse, JDK 1.6

jQuery Libraries

jquery-3.3.1.min.js -> https://jquery.com/download/
jquery-migrate-1.4.1.js -> https://jquery.com/download/
jquery-ui-1.10.3.custom.min.js -> https://osdn.net/projects/sfnet_javanautics/downloads/js/jquery-ui-1.10.3.custom.min.js/

Download assets -> assets

Creating Project

Create a maven based web application with the following details:

Group Id: com.roytuts
Artifact Id: username-availability

Maven Dependency

In order to implement the server side code we need the following dependencies into our application.

We add scope as provided for few dependencies, such as, Servlet and JSP APIs because these jars are available in the server container.

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.roytuts</groupId>
	<artifactId>username-availability</artifactId>
	<packaging>war</packaging>
	<version>0.0.1-SNAPSHOT</version>
	<name>username-availability Maven Webapp</name>
	<url>http://maven.apache.org</url>
	<dependencies>
		<dependency>
			<groupId>javax.servlet</groupId>
			<artifactId>javax.servlet-api</artifactId>
			<version>3.0.1</version>
			<scope>provided</scope>
		</dependency>
		<dependency>
			<groupId>javax.servlet.jsp</groupId>
			<artifactId>jsp-api</artifactId>
			<version>2.2</version>
			<scope>provided</scope>
		</dependency>
		<dependency>
			<groupId>javax.servlet.jsp.jstl</groupId>
			<artifactId>jstl-api</artifactId>
			<version>1.2</version>
		</dependency>
		<dependency>
			<groupId>org.glassfish.web</groupId>
			<artifactId>jstl-impl</artifactId>
			<version>1.2</version>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.6</version>
		</dependency>
	</dependencies>
	<build>
		<finalName>username-availability</finalName>
	</build>
</project>

Creating Table

First thing is to create MySQL table, which will be used to store the user details information.

This table stores information about user’s log in details.

CREATE TABLE `user` (
	`user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
	`login_username` varchar(100) NOT NULL,
	`login_password` varchar(255) NOT NULL,
	`last_login` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (`user_id`),
	UNIQUE KEY `login_email_UNIQUE` (`login_username`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Dumping Data

We need to insert some data in order to complete the testing that checks whether the functionality is really working as expected in the application.

insert into `user`(`user_id`,`login_username`,`login_password`,`last_login`)
values
(1,'user1','$2a$08$S5IfrpOVOvFbbOSOmZpjsO5N9PXgEerTloK','2014-07-19 19:18:30'),
(14,'user2','$2a$08$v1kJflweCK3FOcoAsmYAUCMxFa5Shh7c2','2013-11-17 19:22:46');

Updating Deployment Descriptor

Modify Web.xml file as below. We have just added welcome file that will be displayed upon home page URL access.

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                xmlns="http://java.sun.com/xml/ns/javaee"
                xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
                id="WebApp_ID" version="3.0">
                <display-name>username-availability</display-name>
                <welcome-file-list>
                                <welcome-file>index.jsp</welcome-file>
                </welcome-file-list>
</web-app>

Creating Servlet

Create a servlet – AuthServlet.java. We will use annotation based mapping because we are using servlet 3.

This Servlet will be used to handle user’s request and response. This Servlet also performs some validation and displays appropriate message to the end users.

We also establish database connection to perform database operations.

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
* Servlet implementation class AuthServlet
*/
@WebServlet("/AuthServlet")
public class AuthServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
	/**
	* @see HttpServlet#HttpServlet()
	*/
	public AuthServlet() {
		super();
	}
	/**
	* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
	* response)
	*/
	@Override
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
	}
	/**
	* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
	* response)
	*/
	@Override
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String username = request.getParameter("username");
		PrintWriter out = response.getWriter();
		if (username != null && username.trim().length() > 0) {
			String sql = "SELECT * FROM user WHERE login_username='" + username.trim() + "' LIMIT 1";
			Connection connection = null;
			ResultSet resultSet = null;
			try {
				connection = DBUtils.getDBConnection();
				resultSet = DBUtils.getDBResultSet(connection, sql);
				if (resultSet != null) {
					if (resultSet.next()) {
						out.print("<span style="color:red;">Username unavailable</span>");
					} else {
						out.print("<span style="color:green;">Username available</span>");
					}
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				if (resultSet != null) {
					try {
						DBUtils.closeResultSet(resultSet);
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				if (connection != null) {
					try {
						DBUtils.closeDBConnection(connection);
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
		} else {
			out.print("<span style="color:red;">Username is required field.</span>");
		}
	}
}

Related Posts:

Creating View File

Create a view file – index.jsp. This view file should be put under src/main/webapp folder.

Please make sure to put downloaded js libraries, as specified in Prerequisites section, under webapp/assets/js.

We trigger the blur event of JavaScript or jQuery using AJAX to check in database whether the username is occupied or available.

We use HTTP POST method to call the appropriate Servlet URL for checking the username availability.

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Check username availability using Servlet, jQuery, AJAX, MySQL</title>
	<script
	src="${pageContext.request.contextPath}/assets/js/jquery-3.3.1.min.js"></script>
	<script
	src="${pageContext.request.contextPath}/assets/js/jquery-migrate-1.4.1.min.js"></script>
	<script
	src="${pageContext.request.contextPath}/assets/js/jquery-ui-1.10.3.custom.min.js"></script>
</head>
<body>
	<div style="margin: 10px 0 0 10px; width: 600px">
	<h3>Servlet, AJAX, MySQL username availability check</h3>
	<form id="signupform" style="padding: 10px;">
		<fieldset>
		<legend>Check username</legend>
			<div>
				<label>Username</label><br /> <input type="text" name="username" id="username" />
				<div id="msg"></div>
			</div>
		</fieldset>
	</form>
	</div>
	<!-- below jquery things triggered on onblur event and checks the username availability in the database -->
	<script type="text/javascript">
		$(document).ready(function() {
			$("#username").live("blur", function(e) {
				$('#msg').hide();
				if ($('#username').val() == null || $('#username').val() == "") {
					$('#msg').show();
					$("#msg").html("Username is required field.").css("color", "red");
				} else {
					$.ajax({
						type: "POST",
						url: "http://localhost:8080/username-availability/AuthServlet",
						data: $('#signupform').serialize(),
						dataType: "html",
						cache: false,
						success: function(msg) {
							$('#msg').show();
							$("#msg").html(msg);
						},
						error: function(jqXHR, textStatus, errorThrown) {
							$('#msg').show();
							$("#msg").html(textStatus + " " + errorThrown);
						}
					});
				}
			});
		});
	</script>
</body>
</html>

JDBC Utility Class

We have DBUtils.java for database related queries. We create some common functions or methods to perform database operations through JDBC API.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DBUtils {
	/**
	* to load the database driver
	*
	* @return a database connection
	* @throws SQLException
	* throws an exception if an error occurs
	*/
	public static Connection getDBConnection() throws SQLException {
		Connection conn = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost" + ":" + "3306" + "/" + "ci_post", "root", "");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}
	/**
	* to get a result set of a query
	*
	* @param query
	* custom query
	* @return a result set of custom query
	* @throws SQLException
	* throws an exception if an error occurs
	*/
	public static ResultSet getDBResultSet(Connection conn, String query) throws SQLException {
		ResultSet rs = null;
		if (null != conn) {
			PreparedStatement st = conn.prepareStatement(query);
			rs = st.executeQuery();
		}
		return rs;
	}
	/**
	* to run an update query such as update, delete
	*
	* @param query
	* custom query
	* @throws SQLException
	* throws an exception if an error occurs
	*/
	public static void runQuery(Connection conn, String query) throws SQLException {
		if (null != conn) {
			PreparedStatement st = conn.prepareStatement(query);
			st.executeUpdate();
		} else {
			System.out.println("Query execution failed!");
		}
	}
	/**
	* close an opened PreparedStatement
	*
	* @return a void
	* @throws SQLException
	* throws an exception if an error occurs
	*/
	public static void closePreparedStatement(PreparedStatement ps)	throws SQLException {
		if (null != ps) {
			ps.close();
		}
	}
	/**
	* close an opened ResultSet
	*
	* @return a void
	* @throws SQLException
	* throws an exception if an error occurs
	*/
	public static void closeResultSet(ResultSet rs) throws SQLException {
		if (null != rs) {
			rs.close();
		}
	}
	/**
	* close an opened database connection
	*
	* @return a void
	* @throws SQLException
	* throws an exception if an error occurs
	*/
	public static void closeDBConnection(Connection conn) throws SQLException {
		if (null != conn) {
			conn.close();
		}
	}
}

Testing the Application

Please deploy the application on Tomcat server and hit the URL http://localhost:8080/username-availability/AuthServlet.

Home Screen

username availability servlet ajax mysql

Username Availability

username availability servlet ajax mysql

Username Non-availability

username availability servlet ajax mysql

Thanks for reading.

Tags:

Leave a Reply

Your email address will not be published. Required fields are marked *