Setting up our Swift web-server to connect to MySQL

This series is going to show you how to write a basic backend using Swift.  This is for those who feel more comfortable using Swift throughout their application rather than switching between languages (ex: Java/Spring Boot backend which can also be started out here).  If this is something you’re interested in, I’ll be breaking this down into small parts so it doesn’t become too frightening!

Last time we focused on setting up our Swift web-service project which can be found here.  This time we’re going to be doing some configuration within this project to set up communication with MySQL.

Connecting to MySQL

The first thing we’re going to do is configure our project to connect with MySQL.  Before we add in the MySQL Perfect dependency, we have to do a little bit of setting up first on our machine.  Open up Terminal and type the following…

brew update

And once that has finished updating type

brew install mysql

---

Potential Errors (Follow these steps if you receive the following error)

To fix this, type

xcode-select --install

And then go ahead and attempt to perform the previous step by typing

brew install mysql

---

Now we can update our Package.swift file to include the Perfect MySQL dependency.  After doing so, your file will look as follows…

	import PackageDescription
	let package = Package(
	name: "swift-server",
	dependencies: [
	.Package(url: "https://github.com/PerfectlySoft/Perfect-HTTPServer.git", majorVersion: 2),
	.Package(url:"https://github.com/PerfectlySoft/Perfect-MySQL.git", majorVersion: 3)
	]
	)

view raw Package.swift hosted with ❤ by GitHub

Let’s update our project to include this dependency.  Open up Terminal and type…

swift package update

And once that has finished updating, type in the following command

swift package generate-xcodeproj

Awesome.  So we have the MySQL dependency added, now we can write some code to configure our project to communicate with our MySQL database that we set up in a previous tutorial here (you’ll need to do this first, unless you have a MySQL database already setup and running on your machine).

I’m going to create a new Swift file called Database.swift which is where we’ll put all of our database related functionality.  To do this…

1. Right click on our project directory, swift-server
2. Select New File… and in the popup window select Swift File
3. Give your file a name – I’ve called mine Database and then go ahead and click save.

After creating this new Swift file, my project directory looks as follows…

Now, there are a few other things we’re going to do before we make the call to connect to our database.  Perform the following…

1. Right click on your new Database.swift file > Show File Inspector

On the right hand side of Xcode, you’ll notice a panel appear.  Make sure in the Target Membership that you have the following selected…

We’re ready to start coding.  I’m going to create a new function in the Database.swift that will connect to our database.  It will look like this…

	public class DB {
	// You'll need to update these values based on how you've set up MySQL.
	let host = "127.0.0.1"
	let user = "root"
	let password = "admin"
	let database = "nintendo"
	func databaseConnect(host: String, user: String, password: String, db: String) -> MySQL {
	let mysql = MySQL() // Create an instance of MySQL to work with
	let connected = mysql.connect(host: host, user: user, password: password, db: db)
	guard connected else {
	// verify that we have connected successfully
	print(mysql.errorMessage())
	return mysql
	}
	return mysql
	}
	}

view raw main.swift hosted with ❤ by GitHub

Keep in mind that you’ll have to change the values for user, password, and database depending on how you’ve set up MySQL on your computer.

---

As promised, I will break down these tutorials to keep them short and easy to follow.  I will end this one right here as I know it might take a bit of time to get this setup – please leave me a comment below if you run into any errors while trying to get your project to this point.

If you’re interested in this series, next time I’ll be showing you how to query our MySQL database that we connected in this tutorial from our Swift web-server project!

Creating Some Service Layer Functionality

Last tutorial we looked at setting up our GameRepository interface so that we could run queries against our database by using the @Query annotation.  You can import the updated source code here and continue following along if you haven’t gone through any of the previous tutorials.

This time we’re going to take a look at creating our service layer which is responsible for the business logic of our application whereas a repository is used to accomplish operations related to our database tables.  For large projects, adhering to this practice allows for easier maintainability of code especially when there are changing requirements.

Setting up the Service Class

Similar to what we’ve done in the past, we’re going to create another package and name it service which will hold all of our service classes.

• Right click tutorial.springsetup
• Select New > Other and type “package” to filter
• Name this package service and hit Finish

We’re also going to create another class within this new package.  I’m going to call mine GameService which will be responsible for handling anything related to our Game entity.  Once you have created your service package and made a new class within this package, your project directory should look something like…

Cool, so we’ve created our first service class but before we create any methods we need to annotate this class with @Service (similar to @Entity in our Game class) which will allow for this class to be auto-detected via component scanning when we run this project (but don’t worry about this too much for now).

Remember our findGamesByTitle(String title) method we created last time inside of our GameRepository class?  I want to add a little more logic to this by formatting the input title parameter before executing this method so I’m going to make a new method inside of our GameService class to do so.  Here’s what my GameService class looks like now…

package tutorial.springsetup.service;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import tutorial.springsetup.entity.Game;
import tutorial.springsetup.repository.GameRepository;

@Service
public class GameService {

    @Autowired
    private GameRepository gameRepo; // Autowire in our GameRepository to allow access to all public methods

    public List<Game> searchGamesByTitle(String title){
        String formattedTitle = "%" + title + "%";  // wrap '%' around the input title parameter
        List<Game> results = gameRepo.findGamesByTitle(formattedTitle); // now perform our repository query
        return results; // return results
    }
}

As you can see we’ve created a new method inside of our GameService class called searchGamesByTitle which will take the value of title within the input parameter, wrap it with ‘%’ so that our query will find results that have a game title containing the value of title, and then we can call our GameRepository method to execute findGamesByTitle using our formattedTitle value.  We could add some handling for the case where title is null but for simplicity I’ll leave it as is for now.

Super, we’ve added our first service method.  Now we’re going to make another which will CREATE a new Game object and INSERT it into our database.  This time we will take advantage of the operations provided by JpaRepository which we had extended in our GameRepository class.

Here’s what my insert method looks like (I’ve added it just below the previous one we had created)

public Game insertGame(Game game) throws Exception {
    if(game == null){    // Check that our game is not null
        throw new Exception("Invalid input!");
    }

    Game insertedGame = gameRepo.save(game);  // Save the game into the database and store the returned value
    return insertedGame; // return the inserted game
}

The insertGame method above will take a Game object as a parameter and attempt to insert it into the database.  If game is null I do not want to insert it so instead I will throw an Exception.  Otherwise, gameRepo.save(game) will execute where save is a method provided by JpaRepository which will insert our new object into the database.

Testing our Service Methods

Now that we have created a couple of methods inside of our GameService class, let’s see if they are working as expected.  Open up the GameServiceTests class we created in the previous tutorial and we’ll add in the following test cases…

@Autowired
private GameService gameService; // Autowire in our GameService to access public methods

@Test
public void testSearchGamesByTitle(){
    List<Game> results = gameService.searchGamesByTitle("mario");
    Assert.assertEquals(2,  results.size());
}

@Test
public void testInsertGame() throws Exception{
    // Create new game object that we want to insert.
    Game game = new Game();
    game.setTitle("The Legend of Zelda: A Link to the Past");
    game.setDescription("Action-adventure video game developed and published by Nintendo.");
    Game created = gameService.insertGame(game);
    Assert.assertNotNull(created.getId());
}

Now that we have a couple of new test cases set up…

• Right click GameServiceTests in the project directory
• Run As > JUnit Test

And if your tests pass, you should see the following

Cool!  We now know that our methods are working as we expected.

And if we’re really curious, we can open up Terminal and use the MySQL command line tool to execute a quick SELECT statement to double check that our game object is being saved as we expect it to.

And we can see our new game that has been inserted as the last row in the table.

What we have completed so far

So far in this tutorial we have developed a basic understanding of what a service layer will contain.  We’ve gone through adding a little more logic to our GameRepository method we created in the last tutorial and we’ve taken advantage of the save method extended from JpaRepository so that we can INSERT new rows into the database.

Next tutorial we’re going to be creating our GameController which will act as an additional layer to our project and we’ll go through creating our first API.  This is just so exciting.

If you were having a little trouble following along or if you prefer to import the project, I’ve put the updated source code on github which can be found here.

Have any questions, comments, suggestions?  Having issues getting your project to this stage?  Leave a comment and I’ll be happy to get back to you. 

 

Querying a Database From Your Spring Boot Application

Prefer to watch a video? 

Otherwise keep on scrolling!

---

Last tutorial we managed to configure our Hibernate and database properties within our application to allow for us to create tables using Java objects annotated with @Entity and @Table.  In this tutorial we will be learning how to create simple queries within our application that we can run against our database.

Setting up the Game Repository

In our last tutorial we created a table using the Game.java class.  In order to run queries against our game table, we need to create a repository class.  For every entity class in Java, you will need to create a repository class which will allow for you to perform simple queries.

Remember in the last tutorial how we created a package and named it entity?  We’re going to do something similar here, but instead we’re going to call it repository.  This package will be responsible for holding all of your repository classes.

• Right click the tutorial.springsetup package
• Select New > Other and type “package” to filter
• Name your package “repository” and hit Finish

Your project directory will now look something like this…

With your new package selected, once again right click it and create a new interface called GameRepository.  Once this interface has been created (and it is inside of your repository package), add in the following code…

import org.springframework.data.jpa.repository.JpaRepository;
import tutorial.springsetup.entity.Game;

public interface GameRepository extends JpaRepository<Game, Long>{

}

We’re going to extend JpaRepository so that we can make use of the generic CRUD operations that are provided through this interface.  Might as well take advantage of existing operations and if you’re interested, all of them can be found here.

So we have our repository class set up, now we can start writing some queries.  The first query that I want to write will perform a search for game by it’s title.  To do so, I will use the following code…

@Query("SELECT g FROM Game g WHERE lower(g.title) LIKE lower(?1)")
public List<Game> findGamesByTitle(String title);

What is going on here?  The first line which is annotated with @Query is responsible for the query that is going to be executed when this method is called.  This might look a little different from your typical SQL as we are using our Java Game object within the statement.  Basically, we are going to select every game where the lowercase title of the game is LIKE the String title parameter we are passing through in the second line of code.

I guess if we break it down we can kind of think of it like this

• findGamesByTitle(“mario”);
• @Query(“SELECT g FROM Game g WHERE lower(g.title) LIKE lower(“mario”))
• return a List of Game objects that were found when performing the query above

Testing our First Query

Awesome, so we have written our first query that will attempt to find games by their title.  How do we know this is working?  Let’s write a quick unit test to check that we have it set up correctly and so that you can get a better idea of how to call these methods.

• Right click on the src/test/java package
• Select New > Other and create a new class.  I’ve named mine GameServiceTests

This class will be used to test all of your Game related functionality (sure it will).

Now your project directory might look as follows…

Open the GameServiceTests class you have just created and type in the following…

import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import tutorial.springsetup.App;
import tutorial.springsetup.entity.Game;
import tutorial.springsetup.repository.GameRepository;

@RunWith(SpringRunner.class)
@SpringBootTest(classes = App.class) // App.class is the name of my main application class, if yours is different you might have to change this.
public class GameServiceTests {

    @Autowired
    private GameRepository gameRepo; // By autowiring in our game repository, we will have access to all of it's public methods

    @Test
    public void testFindGameByTitle(){
        List<Game> results = gameRepo.findGamesByTitle("%mario%"); // We want to find all games with a title like "mario"
        Assert.assertEquals(2, results.size());
    }
}

This is our first unit test which aims to test the query we had created in the previous step.  We want to see if we can find all games with a title like “mario” and check that we are getting the expected results (in this case, we are expecting to find two games).  If we run this test now, it’s going to fail because we have not yet put any data into our database.  Because we have yet to create any methods that will INSERT rows into our game table, for demonstration purposes we’re going to quickly add a few rows into our table from our Terminal window.

• Open up a Terminal window
• mysql -u root -p and enter your password.  This should allow you to use the MySQL command line tool
• USE nintendo; so that all of our SQL statements will run against the nintendo database

Now we can start inserting a few rows for testing purposes.  Copy and paste the following statements below into your terminal window so that we have some data to test with.

INSERT INTO game(id, title, description) VALUES(1, 'Super Mario Bros', 'Platform Video Game');
INSERT INTO game(id, title, description) VALUES(2, 'Super Mario Bros 2', 'Platform Video Game');
INSERT INTO game(id, title, description) VALUES(3, 'Donkey Kong Country', 'Platform Video Game');

Your window might look something like…

So now we have three rows of data in our game table, let’s run our unit test we wrote earlier to see if our query is working as expected.

• Right click on GameServiceTests.java
• Select Run As > JUnit Test

This will run your application and execute the unit test which will appear in the left panel.  If it works as expected, you should see the following

Green is good.  This tell us that we have found two games in our table that have titles LIKE “mario” which is exactly the output we expect.

What we have covered so far

Cool, so if you have followed from the beginning we have managed to set up our database, create our Spring Boot and Hibernate environment, configure our project to communicate with our database, write queries within our project that will run against our database and test that they are working as expected.  I hope you’ve been able to follow along without too many issues but let me know in the comments below if you’re totally lost.

In the next tutorial I will show you how to create our Service layer where we will be able to create new game entries (INSERT into) from our application as well as update/modify existing data.  This is where we can take advantage of the JpaRepository we had extended in our GameRepository class.

Oh yeah, and if you’d prefer to just import the source code you can find the updated files here.

Have any questions, comments, suggestions?  Having issues getting your project to this stage?  Leave a comment and I’ll be happy to get back to you. 

Setting up your MySQL Database

Before we get started with some interesting things, first you’re going to want to set up your database.  For this tutorial we will be working with MySQL, but feel free to use whatever you’re most comfortable with.  If you already have your database set up, and you are only interested in Spring + Hibernate you can just skip this step completely.  My examples will be run on a mac, but if you have any questions on setting up in a different environment feel free to ask.

Installing MySQL

Head over to https://dev.mysql.com/downloads/mysql/ for the community version, you’ll probably see a few listed but pick the .dmg file.

Once that has finished downloading, you’ll be prompted through a general setup in which you’ll probably just want to click next until you see the “install” button.

Alright, so you’ve hit “install” and now you get a popup which provides you with your temporary root credentials.

You might not have any idea what that means, but that’s okay just remember to keep this information saved or your next google search might be something like “how do i change my mysql password?? grrr” because if you’re anything like me, you sure as hell won’t be going to the MySQL reference manual.

Creating Your First Database

Great, so you have now successfully installed MySQL and have saved your temporary credentials.  Open up terminal and we’ll get started on creating our first database.

Type mysql -u root -p and when it prompts you to “Enter Password” type in the temporary one you were given earlier.  Once you’ve entered this password, you’ll likely be asked to change it.  Upon doing so, you should now be able to make use of the mysql command line tool where we can start entering in our SQL statements.  Your screen might look something like this…

---

Potential Errors – Skip this chunk if you are able to successfully access the mysql command line tool.

'mysql' is not recognized as an internal or external command, operable program or batch file

If you are receiving the above error after attempting to access mysql via Terminal, there is an issue with your PATH variable.  To fix this, try running export PATH=${PATH}:/usr/local/mysql/bin in Terminal and then repeating the previous step.

---

Let’s create our first database.  I want to name my database “nintendo” for the purpose of this tutorial, so I’m going to perform CREATE DATABASE nintendo; 

You can name your database whatever you want.  After doing so, your terminal window might kind of look like this…

This is good.  If we want to start using this database and adding tables to it through terminal, we can execute USE nintendo; which will mean that we have switched to use our newly created database and all of our SQL statements we would like to execute in this session will run against this database.  If we want to see the tables within our new database, we can type SHOW tables; which (at this point) will show an empty result as we have yet to create any.

If you’ve made it this far, I hope you have been able to successfully install MySQL and create your first database.  For the purpose of this tutorial, we will not be creating any tables through terminal.  Rather, we will be setting up a Spring + Hibernate environment where I will show you how you can take an object-oriented domain written in Java and map it to a relational database.  Are you wondering what the hell this even means?  If so, I’ll walk you through it in the upcoming tutorials.

Have any questions, comments, suggestions?  Or maybe you’re just stumbling across a few issues trying to get your database set up (I know, it’s frustrating). Leave a comment and I’ll be happy to get back to you.