Home » JavaScript » Angular.js » AngularJS with SQL Example

About Yatin

Yatin Batra
The author is graduated in Electronics & Telecommunication. During his studies, he has been involved with a significant number of projects ranging from programming and software engineering to telecommunications analysis. He works as a technical lead in the information technology sector where he is primarily involved with projects based on Java/J2EE technologies platform and novel UI technologies.

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.

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.

ConceptDescription
TemplateAn HTML with additional markups.
DirectivesExtend the HTML with the custom attributes (or markers) and elements.
ModelIt is the data shown to the user in the view with which the user interacts.
ScopeA Scope is a context where the model is stored so that controllers, directives, and expressions can access it.
ExpressionsAn expression executes the JavaScript code inside the double curly braces such as {{ expression }}.
CompilerThe Compiler parses the template and instantiates the directives and expressions.
FilterA Filter formats the value of an expression for display to the user.
Data BindingThis syncs the data between the model and the view.
ControllerThe controller in AngularJS maintains the application data, business logic, and behavior using the $scope object. Developers can attach properties and methods to the $scope object inside a controller function, which in turn will add or update the data and attach behaviors to the HTML elements.
ModuleA module is the container for different parts of an application including the controllers, services, filters, and directives which configures the Injector.
ServiceA Service is reusable business logic which is independent of the views.

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!

Fig. 1: Application Project Structure

Fig. 1: Application Project Structure

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.

Fig. 2: Create Maven Project

Fig. 2: Create 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.

Fig. 3: Project Details

Fig. 3: Project Details

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

Fig. 4: Archetype Selection

Fig. 4: Archetype Selection

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.

Fig. 5: Archetype Parameters

Fig. 5: Archetype Parameters

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.

Fig. 6: Java Package Creation

Fig. 6: Java Package Creation

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

Fig. 7: Java Package Name (com.jcg.java)

Fig. 7: Java Package Name (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.

Fig. 8: Java Class Creation

Fig. 8: Java Class Creation

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.

Fig. 9: Java Class (Employee.java)

Fig. 9: Java Class (Employee.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.

Fig. 10: Java Class (EmployeeServlet.java)

Fig. 10: Java Class (EmployeeServlet.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

Fig. 11: Java Class (MyDb.java)

Fig. 11: Java Class (MyDb.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.

Fig. 12: How to Deploy Application on Tomcat

Fig. 12: How to Deploy Application on Tomcat

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!

Fig. 13: Welcome Page

Fig. 13: Welcome Page

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.

Fig. 14: Employee List

Fig. 14: Employee List

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

Fig. 15: Error Message

Fig. 15: Error Message

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.

Download
You can download the full source code of this example here: AngularJsSqlEx
(0 rating, 0 votes)
You need to be a registered member to rate this.
5 Comments Views Tweet it!
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
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

5 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
michl
michl
3 years ago

Nice example, but it does not work, because if I press the button, nothing happens. The servlet ist not called.

michl
michl
3 years ago
Reply to  Yatin

Thanks for the reply. My form.js was empty ;-(
Now it works perfectly.
Thx again.

Farrukh
1 year ago

I’m trying to run it in tomcat 9 (Docker) and getting following failuire, whenever I click on “Get Data from Db”: Tue Dec 31 22:06:32 UTC 2019 WARN: Establishing SSL connection without server’s identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn’t set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to ‘false’. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification. java.sql.SQLException: Unknown system variable ‘query_cache_size’ at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)… Read more »