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.

This tutorial shows how to check username availability using Servlet, AJAX and MySQL. 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
Knowledge of jQuery
Knowledge of Servlet
Knowledge of MySQL Database

Softwares

Apache Tomcat v7
Eclipse
JDK 1.6
mysql-connector-java-5.1.23-bin.jar

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

Maven dependency

<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.jeejava</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>

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

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=15 DEFAULT CHARSET=utf8;

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

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

Step 3. Create Dynamic Web Project called username-availability. Creates resources/js directory under Webcontent for putting the js files. Put mysql-connector-java-5.1.23-bin.jar under WEB-INF/lib directory.

Step 4. Modify Web.xml file as below

<?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>

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

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>");
		}
	}
}

Step 6. Create a view file – index.jsp. Please make sure to put downloaded js libraries under webapp/assets/js.

<%@ 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>

Step 7. We have DBUtils.java for database related queries.

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

servlet mysql ajax jquery

Username availability

servlet mysql ajax jquery

Username non-availability

servlet mysql ajax jquery

Thanks for reading.

Tags:

I am a professional Web developer, Enterprise Application developer, Software Engineer and Blogger. Connect me on Roy Tutorials | TwitterFacebook Google PlusLinkedin | Reddit

Leave a Reply

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