AngularJS with SQL Example

Hello readers, in this basic example, developers will learn what AngularJS is and how to make a simple angular application to interact with the database.

1. Introduction

1.1 What is AngularJS?

AngularJS is a JavaScript MVC or Model-View-Controller framework developed by Google that lets developers build well structured, easily testable, and maintainable front-end applications. Before we start with creating an actual application using AngularJS, let us see what the actual parts of an AngularJS application are.

Want to be an AngularJS Ninja?

Subscribe to our newsletter and download the AngularJS Programming Cookbook right now!

In order to get you prepared for your AngularJS development needs, we have compiled numerous recipes to help you kick-start your projects. Besides reading them online you may download the eBook in PDF format!

Download Now

Thank you!

We will contact you soon.

1.1.1 Templates

In AngularJS, a template is an HTML with additional markups. AngularJS library compiles the templates and renders the resultant HTML page.

1.1.2 Directives

Directives are the markers (i.e. attributes) on a DOM element that tell AngularJS to attach a specific behavior to that DOM element or even transform the DOM element and its children. Most of the directives in AngularJS library starts with the ng. The directives consist of the following three parts:

• ng-app: The ng-app directive is a starting point. If the AngularJS framework finds the ng-app directive anywhere in the HTML document, it bootstraps (i.e. initializes) itself and compiles the HTML template
• ng-model: The ng-model directive binds an HTML element to a property on the $scope object. It also binds the values of AngularJS application data to the HTML input controls.
• ng-bind: The ng-bind directive binds the AngularJS application data to the HTML tags
• ng-controller: The ng-controller directive is used to specify a controller in the HTML element. This controller will add behavior or maintain the data in that HTML element and its child elements

1.1.3 Expressions

An expression is like a JavaScript code which is usually wrapped inside the double curly braces such as {{ expression }}. AngularJS library evaluates the expression and produces a result.

The following table lists all the important concepts in AngularJS library.

1.2 Why should we use AngularJS?

Using the Model-View-Controller architecture, the framework separates a web application into a simple and yet manageable structure, which comprises of “views”, “models” and “controllers”. The AngularJS library provides the in-build directives (or attributes) to extend the HTML inside a web page. When developers attach these directives to the HTML elements and attributes, it creates a dynamic web-page with very little coding.

These new APIs make a developer life easier, really! But it would be difficult for a beginner to understand this without an example. Therefore, let’s see how to integrate AngularJS with SQL in Java web applications.

2. AngularJS with SQL Example

Here is a step-by-step guide for implementing the AngularJS framework in Java.

2.1 Tools Used

We are using Eclipse Kepler SR2, JDK 8, MySQL database, and Maven. Having said that, we have tested the code against JDK 1.7 and it works well.

2.2 Project Structure

Firstly, let’s review the final project structure, in case you are confused about where you should create the corresponding files or folder later!

2.3 Project Creation

This section will demonstrate on how to create a Java-based Maven project with Eclipse. In Eclipse Ide, go to File -> New -> Maven Project.

In the New Maven Project window, it will ask you to select project location. By default, ‘Use default workspace location’ will be selected. Just click on next button to proceed.

Select the ‘Maven Web App’ Archetype from the list of options and click next.

It will ask you to ‘Enter the group and the artifact id for the project’. We will input the details as shown in the below image. The version number will be by default: 0.0.1-SNAPSHOT.

Click on Finish and the creation of a maven project is completed. If you observe, it has downloaded the maven dependencies and a pom.xml file will be created. It will have the following code:

pom.xml

<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>AngularJsSqlEx</groupId>
	<artifactId>AngularJsSqlEx</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>war</packaging>
</project>

Let’s start building the application!

3. Application Building

Below are the steps involved in developing this application.

3.1 Database & Table Creation

The following MySQL script is used to create a database called servletDb with table: employeetbl. Open MySQL Workbench and execute the SQLscript to create a sample database table for this tutorial.

CREATE DATABASE servletDb;

CREATE TABLE employeetbl (
        e_id INT NOT NULL AUTO_INCREMENT,
        e_name VARCHAR(100),
        e_email VARCHAR(200),
        e_gender VARCHAR(100),
        PRIMARY KEY (e_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO employeetbl (e_id, e_name, e_email, e_gender) VALUES (100, 'Severus', 'severus.snape@hogwarts.com', 'F');
INSERT INTO employeetbl (e_id, e_name, e_email, e_gender) VALUES (101, 'Harry', 'harry.potter@hogwarts.com', 'M');
INSERT INTO employeetbl (e_id, e_name, e_email, e_gender) VALUES (102, 'April', 'april.o.neil@gmail.com', 'F');
INSERT INTO employeetbl (e_id, e_name, e_email, e_gender) VALUES (103, 'Daniel', 'daniel.atlas@eye.com', 'M');
INSERT INTO employeetbl (e_id, e_name, e_email, e_gender) VALUES (104, 'Albus', 'albus.dumbledore@hogwarts.com', 'M');
INSERT INTO employeetbl (e_id, e_name, e_email, e_gender) VALUES (105, 'Hermione', 'hermione.granger@hogwarts.com', 'F');

SELECT * FROM employeetbl;

3.2 Maven Dependencies

Here, we specify the dependencies for the Servlet, MySQL, and JSON API. The rest dependencies will be automatically resolved by the Maven framework and the updated file will have the following code:

pom.xml

<?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/maven-v4_0_0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>AngularJsSqlEx</groupId>
    <artifactId>AngularJsSqlEx</artifactId>
    <packaging>war</packaging>
    <version>0.0.1-SNAPSHOT</version>
    <name>AngularJsSqlEx Maven Webapp</name>
    <url>http://maven.apache.org</url>
    <dependencies>
        <!-- Servlet Api Dependency -->
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>javax.servlet-api</artifactId>
            <version>3.0.1</version>
        </dependency>
        <dependency>
            <groupId>javax.servlet.jsp</groupId>
            <artifactId>jsp-api</artifactId>
            <version>2.1</version>
        </dependency>
        <!-- AngularJs Json Dependency -->
        <dependency>
            <groupId>org.json</groupId>
            <artifactId>json</artifactId>
            <version>20160810</version>
        </dependency>
        <dependency>
            <groupId>com.google.code.gson</groupId>
            <artifactId>gson</artifactId>
            <version>2.8.0</version>
        </dependency>
        <!-- MySql Connector Dependency -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.41</version>
        </dependency>
        <!-- Java Jstl Dependency -->
        <dependency>
            <groupId>jstl</groupId>
            <artifactId>jstl</artifactId>
            <version>1.2</version>
        </dependency>
    </dependencies>
    <build>
        <finalName>${project.artifactId}</finalName>
    </build>
</project>

3.3 Java Class Creation

Let’s create the required Java files. Right-click on src/main/java folder, New -> Package.

A new pop window will open where we will enter the package name as: com.jcg.java.

Once the package is created in the application, we will need to create the servlet controller, model, and the database interaction classes. Right-click on the newly created package: New -> Class.

A new pop window will open and enter the file name as: Employee. The model (i.e. POJO) class will be created inside the package: com.jcg.java.

Repeat the step (i.e. Fig. 7) and enter the filename as: EmployeeServlet. The servlet controller class will be created inside the package: com.jcg.java.

Again repeat the step listed in Fig. 7 and enter the file name as: MyDb. The database interaction class will be created inside the package: com.jcg.java

3.3.1 Implementation of Model Class

This class is used to map the model attributes with the SQL column names. Let’s see the simple code snippet that follows this implementation.

Employee.java

package com.jcg.java;

public class Employee {

	public int emp_id;
	public String emp_name, emp_email, emp_gender;

	public Employee() {}

	public Employee(int id, String name, String email, String gender) {
		this.emp_id = id;
		this.emp_name = name;
		this.emp_gender = gender;
		this.emp_email = email;
	}

	public int getEmp_id() {
		return emp_id;
	}

	public void setEmp_id(int emp_id) {
		this.emp_id = emp_id;
	}

	public String getEmp_name() {
		return emp_name;
	}

	public void setEmp_name(String emp_name) {
		this.emp_name = emp_name;
	}

	public String getEmp_email() {
		return emp_email;
	}

	public void setEmp_email(String emp_email) {
		this.emp_email = emp_email;
	}

	public String getEmp_gender() {
		return emp_gender;
	}

	public void setEmp_gender(String emp_gender) {
		this.emp_gender = emp_gender;
	}
}

3.3.2 Implementation of Servlet Controller

This is a normal Java servlet except the part that it reads the employee list from the database and display it on the angular view. Let’s see the simple code snippet that follows this implementation.

EmployeeServlet.java

package com.jcg.java;

import java.io.IOException;
import java.util.List;

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

import org.json.JSONArray;

import com.google.gson.Gson;

@WebServlet("/employeeServlet")
public class EmployeeServlet extends HttpServlet {

	private static final long serialVersionUID = 1L;

	/***** This Method Is Called By The Servlet Container To Process A 'GET' Request. *****/
	public void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException {
		handleRequest(req, resp);
	}

	public void handleRequest(HttpServletRequest req, HttpServletResponse resp) throws ServletException {

		JSONArray arrayObj = null;
		List empList = null;
		try {

			/***** Fetching Employee Records From The Database *****/
			empList = MyDb.getEmployeeListFromDb();
			if(empList != null && empList.size() > 0) {				
				System.out.println("Total Employee Records Fetch From Db Are?= " + empList.size());
			} else {
				System.out.println("No Employee Records Are Present In Db");
			}

			arrayObj = new JSONArray(empList);
			String jObj = new Gson().toJson(arrayObj);

			/***** Preparing The Output Response *****/
			resp.setContentType("text/html");
			resp.setCharacterEncoding("UTF-8");
			resp.getWriter().write(jObj);
		} catch (IOException ex) {
			ex.printStackTrace();
		}
	}
}

3.3.3 Implementation of Db Utility Controller

This class helps in interacting with the database to perform the SELECT operation (i.e. to fetch the records to the database). Let’s see the simple code snippet that follows this implementation.

MyDb.java

package com.jcg.java;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class MyDb {

	static ResultSet rsObj = null;
	static Statement stmtObj = null;
	static Connection connObj = null;

	/***** Method #1 :: This Method Is Used To Create A Connection With The Database *****/
	private static Connection connectDb() {
		String connUrl = "jdbc:mysql://localhost:3306/servletDb",
				connName = "<!-- Db Username -->", connPwd = "<!-- Db Password -->";
		try {
			Class.forName("com.mysql.jdbc.Driver");
			connObj = DriverManager.getConnection(connUrl, connName, connPwd);			
		} catch (Exception exObj) {
			exObj.printStackTrace();
		}
		return connObj;
	}

	/***** Method #2 :: This Method Is Used To Retrieve The Records From The Database *****/
	public static List<Employee> getEmployeeListFromDb() {		
		Employee emp = null;
		List<Employee> eList = new ArrayList<Employee>();
		try {
			stmtObj = connectDb().createStatement();

			String sql = "SELECT * FROM servletDb.EmployeeTbl";
			rsObj = stmtObj.executeQuery(sql);
			while(rsObj.next()) {
				emp = new Employee(rsObj.getInt("e_id"), rsObj.getString("e_name"), rsObj.getString("e_email"), rsObj.getString("e_gender"));
				eList.add(emp);
			}
		} catch (SQLException sqlExObj) {
			sqlExObj.printStackTrace();
		} finally {
			disconnectDb();
		}
		return eList;
	}

	/***** Method #3 :: This Method Is Used To Close The Connection With The Database *****/
	public static void disconnectDb() {
		try {
			rsObj.close();
			stmtObj.close();
			connObj.close();
		} catch (SQLException sqlExObj) {
			sqlExObj.printStackTrace();
		}		
	}
}

Do remember, to change the database username and password as per the application’s database configuration.

3.3 Creating JSP View

This file is responsible for the AJAX call made to the servlet and for updating the response back in the JSP. Let’s see the simple code snippet to create a simple view using the angular framework.

index.jsp

<!DOCTYPE html>
<html>
   <head>
      <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
      <title>AngularJS</title>
      
      <!-- Javascript Files -->      
      <script type="text/javascript" src="resource/js/angular_v1.6.0.js"></script>	    	   
      <script type="text/javascript" src="resource/js/form.js"></script>    
           
      <!-- Bootstrap Css -->
      <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
      
      <style type="text/css">
      	.marginTop14 {
      		margin-top: 14px;
      	}
      </style>
   </head>
   <body>
      <h2 align="center" class="text-primary">AngularJS Sql Example</h2>
      <div ng-app="myApp">
         <form ng-controller="EmployeeController" ng-submit="getData()">
            <p>
               <button id="formBtn" type="submit" class="btn btn-primary center-block marginTop14">Get Data from Db</button>
            </p>
           	<div id="empTblContainer" class="container" ng-show="empList.length">
           		<table id="empTbl" class="table table-bordered marginTop14">
              		<thead>
                 		<tr>
                    		<th>Id</th><th>Name</th><th>Email</th><th>Gender</th>
                 		</tr>
              		</thead>
              		<tbody>
                 		<tr ng-repeat="detail in empList">
                    		<td><span id="emp-id">{{detail.map.emp_id}}</span></td><td><span id="emp-name">{{detail.map.emp_name}}</span></td><td><span id="emp-email">{{detail.map.emp_email}}</span></td><td><span id="emp-gender">{{detail.map.emp_gender}}</span></td>
                 		</tr>
              		</tbody>
           		</table>
           	</div>    
           	<div id="errorTxt" ng-show="noEmpRecord.length" class="text-center text-danger marginTop14"><h5>No record found in the database!</h5></div>
         </form>
      </div>
   </body>
</html>

3.3.1 Creating AngularJS Controller

The Javascript file i.e. form.js includes the function (i.e. EmployeeController) as the “controller” in the Model-View-Controller. It submits the form to the server and reads back the response message to the $scope.empList.

form.js

/* 
 * Description - AngularJS Script for Retrieving the Employee Details from the Database.
 * Created By - Yatin Batra  
 * */

var ajaxApp = angular.module("myApp", []);

ajaxApp.controller("EmployeeController", [ '$scope', '$http', function($scope, $http) {
	
	$http.defaults.headers.post["Content-Type"] = "application/x-www-form-urlencoded; charset=utf-8";
	$scope.getData = function() {
		$http({
			url : 'employeeServlet',
			method : "GET",			
		}).then(function(response) {
			if(response.data.myArrayList.length == 0) {
				$scope.noEmpRecord = "Yes";
			} else {
				$scope.empList = response.data.myArrayList;
			}
		}, function(response) {
			console.log("Failure -> " + response.data);
			$scope.empList = response.data.myArrayList;
		});
	};
} ]);

4. Run the Application

As we are ready for all the changes, let us compile the project and deploy the application on the Tomcat7 server. To deploy the application on Tomat7, right-click on the project and navigate to Run as -> Run on Server.

Tomcat will deploy the application in its web-apps folder and shall start its execution to deploy the project so that we can go ahead and test it in the browser.

5. Project Demo

Open your favorite browser and hit the following URL. The output page will be displayed.

http://localhost:8085/AngularJsSqlEx/

Server name (localhost) and port (8085) may vary as per your Tomcat configuration. Developers can debug the example and see what happens after every step. Enjoy!

The user clicks the Get Data from Db button. The request will pass a HTTP GET request using the AngularJS controller and the servlet will return the response as below.

In case, no employee list is returned, the servlet will return the response as below.

That’s all for this post. Happy Learning!!

6. Conclusion

In this section, developers learned how to create a simple AngularJS enabled Java web application. Developers can download the sample application as an Eclipse project in the Downloads section. I hope this article served you with whatever developers were looking for.

7. Download the Eclipse Project

This was an example of AngularJS with SQL implementation.

Do you want to know how to develop your skillset to become a Web Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

1. Building web apps with Node.js

2. HTML5 Programming Cookbook

3. CSS Programming Cookbook

4. AngularJS Programming Cookbook

5. jQuery Programming Cookbook

6. Bootstrap Programming Cookbook

and many more ....

I agree to the Terms and Privacy Policy

Sign up

Thank you!

We will contact you soon.