Node.js and PostgreSQL

In this post we will create a REST service using node.js and PostgreSQL database. First thing to do is to install PostgreSQL module using npm (Node Package Manager) if we don't have it yet as shown below.

Then we have to create an Eclipse project as shown in this article. Create two new folder controllers and routes. Inside controllers folder create a js file CustomerController.js, and CustomerRoutes.js inside routes folder. The project structure is shown below.

Now we need to modify server.js as shown below. Here we import express and body-parser package. We also import our own CustomerRoutes.js. Line 8-11 means we parse body part of a request as json and register our route CustomerRoutes.js. Line 13-15 means we return a string 'url not found' if a url doesn't exist. We will see an example later.

Now we will see the CustomerRoutes.js as shown below. Here we import our controller CustomerController.js. Here we have two url /customer and /customer/:id. The first url is bound to http get and post. The second url is bound to http get by id, put, and delete. We will see the logic in CustomerController.js.

We will now see the controller. Here we import pg module and create a connection pool as shown below in CustomerController.js

Next we will see the method to get all data from database and return them as json. Here we connect to database using connection pool. If it failed we release it by calling done() method and throw the error. If succeed we make a query to get all customer data. We release the connection soon after query is run. If the query failed we just print the error to console. If succeed we return the result as json to the response object.

The next method we will see is the method to get customer by its id. Here we connect to database using connection pool. If it failed we release it by calling done() method and throw the error. If succeed we make a query to get a customer data by specifying its id from request parameter. We release the connection soon after the query is run. If the query failed we just print the error to console. If succeed we return the result as json to the response object.

The following method is a method to insert data to PostgreSQL. First we get customer's first and last name from request body. We then make a database connection using connection pool. If it failed we release it by calling done() method and throw the error. If succeed we make a BEGIN statement, INSERT statement, and lastly COMMIT statement. Actually, if we only have single query we don't need to do BEGIN and COMMIT statement. Those statement are the way we do transaction in node.js using PostgreSQL. We surely can create a ROLLBACK statement if the query failed which we don't need now since we only have a single insert statement. We also have to release the connection whenever either a statement failed or a COMMIT statement is executed. If the query failed we just print the error to console. If succeed we return the result as json to the response object.

The method to update a customer data is shown below. First we get customer id from request parameter and customer data from request body. We then make a database connection using connection pool. If it failed we release it by calling done() method and throw the error. If succeed we make a BEGIN statement, UPDATE statement, and lastly COMMIT statement. Same logic applies, if we only have single query we don't need to do BEGIN and COMMIT statement. Those statement are the way we do transaction in node.js using PostgreSQL. We surely can create a ROLLBACK statement if the query failed which we don't need now since we only have a single update statement. We also have to release the connection whenever either a statement failed or a COMMIT statement is executed. If the query failed we just print the error to console. If succeed we return the result as json to the response object.

And lastly we will see a method to delete a customer data. First we get the customer id from request parameter. We then make a database connection using connection pool. If it failed we release it by calling done() method and throw the error. If succeed we make a BEGIN statement, DELETE statement, and lastly COMMIT statement. As mentioned, if we only have single query we don't need to do BEGIN and COMMIT statement. Those statement are the way we do transaction in node.js using PostgreSQL. We surely can create a ROLLBACK statement if the query failed which we don't need now since we only have a single delete statement. We also have to release the connection whenever either a statement failed or a COMMIT statement is executed. If the query failed we just print the error to console. If succeed we return the result as json to the response object.

Let's now run the project and see the result in Postman.

A request to get all customers is shown below.

Next we will create a new customer and then update it.

Now we will get the new customer and delete it.

Lastly, what if we request a wrong url? Below is the result.

Spring Boot and Spring Security

Let's now continue our Spring Boot journey by adding Spring Security. First we need to modify our pom.xml as shown below.

If we run our project this time, instead of displaying our page, Spring will redirect us to login page. At this point of time we haven't even created the login page.

So now we will create a login page as shown below. Here we use primefaces as our front end. We will learn it later. For now what we need to do is to make sure that the username is named "username" and password is named "password" as it is required by Spring Security.

Next we have to create a configuration class that extends WebSecurityConfigurerAdapter. We also have to annotate it with @Configuration and @EnableWebSecurity. These two annotations are used together to configure Spring Security. We then need to override the configure() method. Inside configure() method, we define login and logout url, resources that are permitted to access without any authorization, etc.

So now how do we compare username and password with the ones in database? That is the job of a password encoder. As shown below, there are two methods we have to implement, encode() and matches(). Method encode() is used to encode raw password while matches() is used to compare raw password and encoded one. Raw password is supplied by user input while encoded password is the one we fetched from database. Note that we use MD5 as our encoding algorithm.

Next question is how do we get username and password from database? And who gives that password to our password encoder above? That is the job of user detail service. This class implements UserDetailsService and annotated with @Service to make it a Spring service. Here we have to implement method loadUserByUsername(). This method makes a query to database to get a user if any, using supplied username. It then gets the user's roles which we can use later when we deal with authority in an html page. It then returns a Spring Security User object which has username, password, and roles. Next, Spring calls our password encoder class to compare the password.

Now we need to put them all together as shown below. In our WebSecurityConfigurerAdapter class, we have to create a bean of password encoder and object of type user detail. Method configureGlobal() bind them all together. So our WebSecurityConfigurerAdapter looks like this:

Now run the project and our login page will be displayed.

If authentication failed, an error message is displayed.

If succeed, an error is also displayed. This is because we haven't defined our default page.

To define a default page, we have to create a configuration class that extends WebMvcConfigurer. Method addViewControllers() needs to be implemented. In our case we make index.xhtml as the default page.

Now if we start the project again and successfully logging in, the index.xhtml will be displayed.

Node.js

There are few steps we need to do to conveniently working on Node.js.

Install Node.js from its official website. This is the result in my Windows.

Since I'm a big fan of Eclipse, I want to be able to write Node.js code on Eclipse.To do that we installed an Eclipse package nodeclipse.

Next we install express framework using npm (Node Package Manager). Express framework is used to create a Node.js server. Actually we can create a Node.js server without express framework, but since everybody is doing it I think we will just do it :D

Now we will install nodemon using npm. Nodemon is a tool that restarts Node.js server when a file changes. By default, a changes will not be updated if the server is running. Without nodemon we have to restart it everytime we make changes.

Now it's time to create an Eclipse project. Choose to create a simple empty project. Two important files are created there, package.json and server.js. Package.json contains project information while server.js contains the Node.js server code.

Modify the package.json. Make sure the package.json contains "script"."start" "nodemon server.js" which means when we start Node.js server we use nodemon instead of just starting the server. This way when a file changes we don't need to restart the server. "dependencies"."express" and "devDependencies"."nodemon" must also exist.

Modify server.js as shown below.

Start it as shown below. If we modify server.js the server is restarted automatically.

Spring Boot and MyBatis

Integrating Spring Boot and Mybatis is quite easy. We will use our previous post as our base project. First, we need to add the following dependencies to our maven pom.xml as shown below. Note that we use PostgreSQL as our database.

We will use the following POJO without any annotation in it as shown below.

Next, create a package where we will put all of our Data Access Object files. Inside the package, create a Data Access Object interface and xml files as shown below.

The ICustomerDao.java contains methods required to interact with Customer data. In this interface we have an insert, an update, and a delete Customer method. Also, we have a method to get count of total Customer. We also have a method to get a Customer by its id. Note that we have the id annotated with @Param("id"). The id inside param refers to id that is used in xml file later. We also have a method to get a limited number of Customer (get Customer start from row offset for next limit number of Customer). Note that we also have them annotated with @Param. The interface itself is annotated with @Mapper, meaning it is a MyBatis mapper class.

Next we will see ICustomerDao.xml. Tag <mapper> defines the interface we created above.

Tag <resultmap> defines what object and its alias we want to return from a query. Here we have a Customer object inside package id.co.rhs.models and we give it an alias Customer. For each property in the class we map it to its database column. And since column ADDRESS_ID has relation one to one with class Address, we use <association> tag. The attribute select defines which statement in IAddressDao to get the address object. Other attributes are self explanatory.

Insert, update, and delete statements are very straightforward. The parameters are put inside #{}. Note that in the interface we pass object Customer and in the xml we just need to get its properties inside curly brackets.

Methods to get Customer are shown below. Here we set the resultMap as Customer even though the query returns more than one resultset. If we want to return a result other than our resultMap, we can use resultType. Here we return resultType integer for getAllCustomersCount method.

What about primary key? We can create a native PostgreSQL function to generate random string as shown below. Then in the customer table we set the primary key default value to be the generated string value.

Next we have to make a database configuration in application.properties as shown below. Here we have to define the driver class name, database url, database username and password. This application.properties file is saved in src/main/resources folder. Note that here we provide plain password. Next we will see how to encrypt this password.

To encrypt the password we have to create a method to encrypt and decrypt it. Below are our methods to do that.

Next we have to create a configuration class as shown below. Here we annotate the class with @Configuration and @EnableAutoConfiguration. @Configuration allows us to define a bean, in our case datasource bean for database connection. @EnableAutoConfiguration makes spring auto configuration enabled. We also have four variables annotated with @Value to get its value from application.properties file. A method named datasource() is annotated with @Bean and return a datasource object. We do this since we want to decrypt our encrypted password in application.properties file.

For testing, we will modify our App.java. Here we add @MapperScan annotation to make our dao package be scanned. We also have a variable ICustomerDao which we annotate with @Autowired. This makes Spring injection happens in that variable. We also make the App class implements Spring Boot's CommandLineRunner and implements its run() method. This method will be executed when Spring application is run. In our case it will get a customer by its id and print it on screen.

Our project structure now looks like this.

Below is the result running our code above.

Spring Boot and JSF

Integrating Spring Boot and JSF is quite simple. For this purpose, we will use joinfaces. First, create maven pom.xml and set the parent and the dependencies as shown below.

Next, configure spring boot application. Create a new class and give @SpringBootApplication class annotation. Also create a main method where we will call spring boot application as
shown below.

Next, create a jsf html page and put it inside src/main/resources/META-INF/resources/ source folder. The folder structure and file content is shown below.

Run the App.java and make http request on browser. The following output will be displayed.