Connecting Angular Apps to MySQL

Introduction

This blog post is for the Introduction to Software Engineering (SWEN-261: Spring 2022) course at the Rochester Institute of Technology. It is to help faculty understand how one group of students integrated a MySQL database into their class project. Continue reading to learn more about MySQL / Angular integration.

Creating the Database

First, a database needs to be created and configured. You can learn how we did this here.

Set up a MySQL Database on a Raspberry Pi Server

Next, we set up a server using a Raspberry Pi. We wanted to edit the database from any device, so we used a server instead of hosting the files locally. Furthermore, we ensured that our database had persistence and that all users connected to it would have up-to-date information. Learn how we built a database server on a Raspberry Pi here.

Integrating MySQL into the Angular Framework

After setting up the server, we connected our Angular framework—a platform for building mobile and desktop web applications—to the server. See how we did this below.

Our Code

We heavily utilized the @JsonProperty Jackson Annotation, which allows non-standard getter and setter methods to work with JSON. For example, @JsonProperty converts the following Java code to and from JSON (Stack Overflow).

	public class Parameter {
	@JsonProperty("Name")
	public String name;
	@JsonProperty("Value")
	public String value;
	}

view raw readingroom_stackoverflow_jsonprop_ex.java hosted with ❤ by GitHub

"Parameter":{
  "Name":"Parameter-Name",
  "Value":"Parameter-Value"
}

🌸👋🏻 Let’s take this to your inbox. You’ll receive occasional emails about whatever’s on my mind—offensive security, open source, academics, boats, software freedom, you get the idea.

Join 4,144 other subscribers

Account Objects

The Account.java class provides a constructor declaration for account objects (aObj). It also provides getters and setters for some aObj attributes. Please view the Account.java file on GitHub to gain some context before reading the rest of this section. If needed, GeeksforGeeks has a great article on Classes and Objects in Java, which provides additional context for how we set up this class.

First, instance variables are defined at the top of the class. These variables are used in Account.java’s methods (i..e, getters, setters, and during aObj creation).

	@JsonProperty("LastName")
	private String lastname;
	@JsonProperty("FirstName")
	private String firstname;
	@JsonProperty("PhoneNumber")
	private String phonenumber;
	@JsonProperty("Username")
	private String username;
	@JsonProperty("Password")
	private String password;

	@JsonProperty("Role")
	private int role; // user: 1, guest: 2, admin: 0

view raw readingroom_property_example.java hosted with ❤ by GitHub

Next, a constructor declaration is created. Constructor declarations allow aObjs to be defined and referenced.

	/**
	* Creates account entity according to provided authentication
	*
	* @param lastname: account user's lastname
	* @param firstname: account user's firstname
	* @param phonenumber: account user's phone number
	* @param username: account username
	* @param password: account password provided as json object
	* @return Account: returns itself as object
	*/
	public Account(@JsonProperty("LastName") String lastname,
	@JsonProperty("FirstName") String firstname,
	@JsonProperty("PhoneNumber") String phonenumber,
	@JsonProperty("Username") String username,
	@JsonProperty("Password") String password) {

	this.lastname = lastname;
	this.firstname = firstname;
	this.phonenumber = phonenumber;
	this.username = username;
	this.password = password;
	this.role = (this.username.equals("admin")) ? 0 : 1;
	}

view raw readingroom_account_obj_example.java hosted with ❤ by GitHub

The constructor declaration includes the contents for the user to enter. For example, the required attributes are LastName, FirstName, Username, and Password. PhoneNumber is optional, but it is listed anyways. These attributes correspond to fields in the MySQL database, specifically the user table.

However, some fields in the MySQL table do not correspond to attributes in Account.java. For example, the UserID field is nonexistent in Account.java. UserID is a required field that uses auto-increment. In other words, the MySQL database, not the user, defines the account’s UserID. As a result, we do not reference UserID in Account.java (nor do we display a UserID textbox in the user interface during account creation).

Other times, attributes are manually assigned. For example, the role attribute in Account.java (RoleNumber in the user table) is manually assigned if it is not the default value. Manually assigning attributes is bad practice, but this was the best we could do given the project’s time constraints.

The setup for the Book.java and Cart.java classes was almost identical.

LoginDatabaseDAO.java

Next, we implemented LoginDatabaseDAO.java. This class imports Account.java, and it contains the methods used for registering an account and logging in.

First, we created a method to connect to the database server. The database server requires a generic login for all users.

	/**
	* Creates an instance of LoginDatabaseDAO
	*
	* Used for specificying the functionality of overriden functions
	* checkCredentials() and register() that gets dependency injected
	* into the RESTController element
	*/
	public LoginDatabaseDAO(){
	try {
	// the URL connects to the database as user=estore and password=password
	this.connect = DriverManager
	.getConnection("jdbc:mysql://smt5541pi.student.rit.edu:3306/book?"
	+ "user=estore&password=password");

	} catch (SQLException e) {
	e.printStackTrace();
	}
	}

view raw readingroom_database_login.java hosted with ❤ by GitHub

One flaw is that we did not implement account permissions. As a result, any account type can modify the database from the command line. For example, if a non-administrator executed the login command in a bash shell, they could alter the database with administrator permissions.

The server is down, and the password is changed, so no one can log in. However, we can start up the server for demonstrations if needed.

Next, we developed a method for account registration. You can see that register() has parameters for each field needed to create an account. Then, it builds a MySQL statement by concatenating a string with the arguments passed into the function. Lastly, the program sends a query to the database, and the server returns a boolean, indicating if the registration was successful.

	/**
	* Attempt to insert a new account into the database and get the response
	* @param acc – Account object from the user that gets added to the database
	* @return – the status of the success of database insertion
	*/
	@Override
	public boolean register(Account acc) {
	//send the username account stuff into the database
	//THE COMMAND, swap the data values with the acc.*getters
	//example command: INSERT INTO user (LastName, FirstName, PhoneNumber, Username, Password) VALUES ("Gallucci", "Olivia", "(123) 456-7890", "oliviagallucci", "password");

	// create the command
	String query = String.format(
	"INSERT INTO user (LastName, FirstName, PhoneNumber, Username, Password)" +
	" VALUES ('%s', '%s', '%s', '%s', '%s')",
	acc.getLastname(), acc.getFirstname(), acc.getPhonenumber(),
	acc.getUsername(), acc.getPassword());
	System.out.println(query);
	try {
	Statement st = this.connect.createStatement();
	int rs = st.executeUpdate(query);
	System.out.println("updateResult");
	System.out.println(rs);
	//if account not created – 0, no affected rows.
	if (rs != 0){
	System.out.println("pass return nothing check");
	// the row count for 1 element is 1 affected row = 1
	if(rs == 1){
	System.out.println("pass row count check");
	return true;
	}
	}
	} catch (SQLException e) {
	e.printStackTrace();
	return false;
	}
	//if ERROR/conflict returned – unsuccessful
	//If correct – successful.
	return false;
	}

view raw readingroom_register.java hosted with ❤ by GitHub

To complete this file, we created a method for users to login to their respective accounts. Each account had a shopping cart, personalized information, etc.

	/**
	* Call the database in here and get the response of log in or not
	* @param acc – Account object from the user where the password is
	* checked according to the database
	* @return – status of credential checking
	*/
	@Override
	public boolean checkCredentials(Account acc) {
	// send a command to get the user credentials from the database
	//THE COMMAND
	String query = String.format("select Password from user WHERE UserName='%s'",acc.getUsername());
	// print the query
	System.out.println(query);

	try {
	Statement st = this.connect.createStatement();
	ResultSet rs = st.executeQuery(query);

	while (rs.next()){
	System.out.println("queryResult");
	// parse the returned result to get the pure password.
	this.queryResult = rs.getString(1);
	System.out.println(this.queryResult);
	}

	} catch (SQLException e) {
	e.printStackTrace();
	return false;
	}
	//if username not found – returns null OR empty string.
	//no account with that username – return false
	if (queryResult != null \|\| !queryResult.equals("") ){
	System.out.println("pass empty check");
	// check the returned password and the acc.getpassword, if same return true, if not false
	if(this.queryResult.equals(acc.getPassword())){
	System.out.println("pass password check");
	return true;
	}

	}
	System.out.println("last false");
	return false;
	}

view raw readingroom_checkCredentials.java hosted with ❤ by GitHub

LoginDatabaseDAO.java is similar to HelperSQL.java (creates book queries for interacting with the database) and CartDatabaseDAO.java (modifies items in users’ shopping carts).

LoginDAO.java

To complete this series of files, we created LoginDAO.java, which defines the interface for the log-in feature and Account object persistence.

Other

Other files that are less relevant to this post but relevant to the project are InventoryEntry.java (find usage level and other stats about a book) and EstoreDatabaseDAO.java (add, update, delete, filter, search, and sort books).

Conclusion

This post details how a group of students integrated a MySQL database into their Angular class project. Feel free to reach out with questions, concerns, etc.