Username Availability Check Using Servlet, AJAX And MySQL

Check Username Live

This tutorial shows how to check username availability using Servlet, AJAX and MySQL. Sometimes you may need to check username availability instantly before a user presses the submit button after filling a long-sized signup form. In this case you can use AJAX with any technologies like PHP, Codeigniter, Servlet, Struts, JSF, Spring etc. for checking whether the input username is available or already occupied by someone else. So if you give instant result to the user for username availability then it makes more sense 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.

Related Posts:

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

Java 1.6/1.8+, jQuery 1.8/1.9+, Servlet 3.x/4.x, MySQL Database 5.x/8.x, Tomcat 7.x/10.x

Download assets -> assets

Project Setup

Create a maven based web application with the following details. In order to implement the server side code you would need the following dependencies into your application.

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

<?xml version="1.0" encoding="UTF-8"?>

<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/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>com.roytuts</groupId>
	<artifactId>servlet-ajax-username-availability</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>war</packaging>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<maven.compiler.source>11</maven.compiler.source>
		<maven.compiler.target>11</maven.compiler.target>
	</properties>

	<dependencies>
		<dependency>
			<groupId>javax.servlet</groupId>
			<artifactId>javax.servlet-api</artifactId>
			<version>3.0.1/4.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/8.0.31</version>
		</dependency>
	</dependencies>

	<build>
		<finalName>servlet-ajax-username-availability</finalName>
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.8.1</version>
			</plugin>
		</plugins>
	</build>
</project>

MySQL 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.

For MySQL 5.1.6, use the following table creation script:

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;

For MySQL 8.0.26, use the following table creation script:

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

Dumping Data

I 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');

Deployment Descriptor

Modify src/main/webapp/WEB-INF/web.xml file as below. I 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">-->
<web-app metadata-complete="false"
	xmlns="http://xmlns.jcp.org/xml/ns/javaee"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd"
	version="4.0">

	<display-name>Servlet AJAX Username Availability</display-name>

	<welcome-file-list>
		<welcome-file>index.jsp</welcome-file>
	</welcome-file-list>
</web-app>

Servlet

Create a servlet – AuthServlet.java. I will use annotation based mapping because I am using servlet version 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.

I also establish database connection to perform database operations.

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

	public AuthServlet() {
		super();
	}

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		// TODO Auto-generated method stub
		response.getWriter().append("Served at: ").append(request.getContextPath());
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	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>");
		}
	}

}

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 if you are not using the CDN (Content Delivery Network) link to use the required libraries.

I trigger the blur (up to jquery 1.8 version)/input (jquery 1.9+) event of JavaScript or jQuery using AJAX to check in database whether the username is occupied or available.

I 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>
<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>-->
	
	<script src="https://code.jquery.com/jquery-3.6.1.min.js" integrity="sha256-o88AwQnZB+VDvE9tvIXrMQaPlFFSUTR+nldQm1LuPXQ=" crossorigin="anonymous"></script>
	<script src="https://code.jquery.com/ui/1.13.2/jquery-ui.min.js" integrity="sha256-lSjKY0/srUM9BE3dPm+c4fBo1dky2v27Gdjm2uoZaL0=" crossorigin="anonymous"></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(
									.on(
											//"blur",
											"input",
											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/servlet-ajax-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

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

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"); //MySQL 5.x
			Class.forName("com.mysql.cj.jdbc.Driver"); //MySQL 8.x
			conn = DriverManager.getConnection("jdbc:mysql://localhost" + ":" + "3306" + "/" + "roytuts", "root", "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 Check Username Availability

Deploy the application on Tomcat server and hit the URL http://localhost:8080/servlet-ajax-username-availability/.

Home Screen

The home page will look like the following image: