Tuesday, July 16, 2024

Node.js MySQL Select Statement Example

In this post we'll see examples of Select statement using Node.js and MySQL to fetch data from DB. We'll be using the Promise Based API provided by MySQL2 library and a connection pool to connect to database in the examples provided here.

Refer Node.js - MySQL Connection Pool to get more information about using MySQL Connection Pool with Node.js

Table used

Table used for the example is Employee table with columns as- id, name, age, join_date.


CREATE TABLE `node`.`employee` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `join_date` DATE NOT NULL,
  `age` INT NULL,
  PRIMARY KEY (`id`));

Id is auto incremented so we don't need to send that data from our code.

Node.js MySQL Select statement example

In this example all the records are fetched from the Employee table.

We'll keep the DB connection configuration in a separate file that can be used wherever it is needed by importing it.

util\database.js

const mysql = require('mysql2/promise');

const pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: 'admin',
    database: 'node',
    waitForConnections: true, 
    connectionLimit: 10,
  });

module.exports = pool;

As you can see pool object is exported here so that it can be used in other files.

app.js

In this file let's create a function to select all the records from the Employee table.

const pool = require('./util/database');

async function getEmployees(){
  const sql = "SELECT * FROM EMPLOYEE";
  try{
    const conn = await pool.getConnection();
    const [results, fields] = await conn.query(sql);
    console.log(results); // results contains rows returned by server
    console.log(fields);
    conn.release();
  }catch(err){
    console.log(err);
  }
}

getEmployees(30);

Important points to note here-

  1. getEmployees () function is a async function as we are using async/await (Promise based API) rather than callback based API.
  2. We are using await with pool.getConnection() method.
  3. By using array destructuring we get the returned values for results and fields.
  4. results contains rows returned by server.
  5. fields variable contains extra meta data about results, if available.
  6. After the task, connection is released using conn.release() which means connection goes back to the pool.

On running the file-

>node app.js
[
  {
    id: 1,
    name: 'Ishan',
    join_date: 2024-03-23T18:30:00.000Z,
    age: 28
  },
  {
    id: 2,
    name: 'Rajesh',
    join_date: 2023-06-16T18:30:00.000Z,
    age: 34
  }
]
[
  `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(45),
  `join_date` DATE(10) NOT NULL,
  `age` INT
]

Select with where condition example

In this example we'll have a condition using Where clause, only those records are selected that fulfil the condition. For the condition, parameterized query is used.

app.js

In this file let's create a function to select data from the Employee table in MySQL based on the age condition.

const pool = require('./util/database');

async function getEmployeesByAge(age){
  const sql = "SELECT * FROM EMPLOYEE where age >?";
  const values = [age];
  try{
    const conn = await pool.getConnection();
    const [result, fields] = await conn.execute(sql, values);
    console.log(result); // results contains rows returned by server
    console.log(fields); // fields contains extra meta data about results, if available
    conn.release(); // connection sent back to pool
  }catch(err){
    console.log(err);
  }
}

getEmployeesByAge(30);

On running it

>node app.js
[
  {
    id: 2,
    name: 'Rajesh',
    join_date: 2023-06-16T18:30:00.000Z,
    age: 34
  }
]
[
  `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(45),
  `join_date` DATE(10) NOT NULL,
  `age` INT
]

That's all for this topic Node.js MySQL Select Statement Example. If you have any doubt or any suggestions to make please drop a comment. Thanks!


Related Topics

  1. Node.js MySQL Insert Example
  2. Node.js MySQL Update Example
  3. Node.js - Connect to MySQL Promise API

You may also like-

  1. How to Setup a Node.js Project
  2. Node.js Event Driven Architecture
  3. Node.js path.basename() Method With Examples
  4. Writing a File in Node.js
  5. Fail-Fast Vs Fail-Safe Iterator in Java
  6. Java Stream - Convert Stream to List
  7. Switch Expressions in Java 12
  8. Custom Pipe in Angular With Example

Monday, July 15, 2024

Node.js MySQL Insert Example

In this post we'll see examples of inserting records into table using Node.js and MySQL. We'll be using the Promise Based API provided by MySQL2 library and a connection pool to connect to database in the examples provided here.

Refer Node.js - MySQL Connection Pool to get more information about using MySQL Connection Pool with Node.js

Table used

Table used for the example is Employee table with columns as- id, name, age, join_date.


CREATE TABLE `node`.`employee` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `join_date` DATE NOT NULL,
  `age` INT NULL,
  PRIMARY KEY (`id`));

Id is auto incremented so we don't need to send that data from our code.

Node.js insert record MySQL example

We'll keep the DB connection configuration in a separate file that can be used wherever it is needed by importing it.

util\database.js

const mysql = require('mysql2/promise');

const pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: 'admin',
    database: 'node',
    waitForConnections: true, 
    connectionLimit: 10,
  });

module.exports = pool;

As you can see pool object is exported here so that it can be used in other files.

app.js

In this file let's create a function to insert data into the Employee table in MySQL.

const pool = require('./util/database');

async function insertEmployee(){
  const sql = "INSERT INTO EMPLOYEE (name, join_date, age) values ('Ishan', '2023-11-22', 31)";
  try{
    const conn = await pool.getConnection();
    const [result, fields] = await conn.query(sql);
    console.log(result);
    console.log(fields);
    conn.release();
  }catch(err){
    console.log(err);
  }
}

// call the function
insertEmployee();

Important points to note here-

  • insertEmployee() function is a async function as we are using async/await (Promise based API) rather than callback based API.
  • We are using await with pool.getConnection() method.
  • By using array destructuring we get the returned values for result and fields.
  • fields variable contains extra meta data about results, if available.
  • result contains a ResultSetHeader object, which provides details about the operation executed by the server.
  • After the task, connection is released using conn.release() which means connection goes back to the pool.

On running the file-

>node app.js

ResultSetHeader {
  fieldCount: 0,
  affectedRows: 1,
  insertId: 1,
  info: '',
  serverStatus: 2,
  warningStatus: 0,
  changedRows: 0
}
Undefined

If you want to get the number of rows inserted you can get it using result.affectedRows and to get the id of the inserted row you can use result.insertId

Using parameterized Insert query

In the above example problem is that insert query will insert the same record as the data is hardcoded. To make it more generic you can use a parameterized query with the placeholders for the values that are passed later.

const pool = require('./util/database');

async function insertEmployee(empName, joinDate, age){
  const sql = "INSERT INTO EMPLOYEE (name, join_date, age) values (?, ?, ?)";
  const values = [empName, joinDate, age];
  try{
    const conn = await pool.getConnection();
    const [result, fields] = await conn.execute(sql, values);
    console.log(result);
    console.log(fields);
    conn.release();
  }catch(err){
    console.log(err);
  }
}

// call the function
insertEmployee('Ishan', '2024-03-24', 28);
insertEmployee('Rajesh', '2023-06-17', 34);

Important points to note here-

  1. Prepared statement is used now where placeholders are used to pass values later.
  2. Values are passed in an array which is also passed along with the query.
  3. conn.execute() is used here rather than conn.query() as execute helper prepares and queries the statement.

Inserting multiple records - Node.js and MySQL

In the above example parameterized query is used which is definitely a step forward to using hardcoded data. If you have multiple records to be inserted simultaneously then you can use single question mark '?' which represents multiple rows of data coming from an array.

app.js

const pool = require('./util/database');

async function insertEmployee(data){
  const sql = "INSERT INTO EMPLOYEE (name, join_date, age) values ?";
  
  try{
    const conn = await pool.getConnection();
    const [result, fields] = await conn.query(sql, [data]);
    console.log(result);
    console.log(fields);
    conn.release();
  }catch(err){
    console.log(err);
  }
}

const records = [
  ['Ishan', '2024-03-24', 28], 
  ['Rajesh', '2023-06-17', 34]
]
// call the function
insertEmployee(records);

On running it

>node app.js

ResultSetHeader {
  fieldCount: 0,
  affectedRows: 2,
  insertId: 1,
  info: 'Records: 2  Duplicates: 0  Warnings: 0',
  serverStatus: 2,
  warningStatus: 0,
  changedRows: 0
}

Undefined

As you can see affected rows count is 2.

That's all for this topic Node.js MySQL Insert Example. If you have any doubt or any suggestions to make please drop a comment. Thanks!


Related Topics

  1. Node.js MySQL Update Example
  2. Node.js MySQL Delete Example
  3. Node.js - How to Connect to MySQL
  4. Node.js MySQL Select Statement Example

You may also like-

  1. Difference Between __dirname and process.cwd() in Node.js
  2. Node.js path.join() Method
  3. Reading a File in Node.js
  4. Setting Wild Card Route in Angular
  5. Difference Between Comparable and Comparator in Java
  6. Java is a Strongly Typed Language
  7. How to Create Password Protected Zip File in Java

Monday, July 8, 2024

How ArrayList Works Internally in Java

ArrayList arguably would be the most used collection along with the HashMap. Many of us programmers whip up code everyday which contains atleast one of these data structures to hold objects. I have already discussed how HashMap works internally in Java, in this post I'll try to explain how ArrayList internally works in Java.

As most of us would already be knowing that ArrayList is a Resizable-array implementation of the List interface i.e. ArrayList grows dynamically as the elements are added to it. So let's try to get clear idea about the following points-

  • How ArrayList is internally implemented in Java.
  • What is the backing data structure for an ArrayList.
  • How it grows dynamically and ensures that there is always room to add elements.

Because of all these side questions it is also a very important Java Collections interview question.

Note that the code of ArrayList used here for reference is from Java 17


Where does ArrayList internally store elements

Basic data structure used by Java ArrayList to store objects is an array of Object class, which is defined as follows-

transient Object[] elementData;

I am sure many of you would be thinking why transient and how about serializing an ArrayList then?
ArrayList provides its own version of readObject and writeObject methods so no problem in serializing an ArrayList and that is the reason, I think, of making this Object array as transient.

Sunday, July 7, 2024

ArrayList in Java With Examples

Java ArrayList is one of the most used collection and most of its usefulness comes from the fact that it grows dynamically. Contrary to arrays you don't have to anticipate in advance how many elements you are going to store in the ArrayList. As and when elements are added ArrayList keeps growing, if required.

Though internally it is not really some "elastic" array which keeps growing, it is as simple as having an array with an initial capacity (default is array of length 10). When that limit is crossed another array is created which is 1.5 times the original array and the elements from the old array are copied to the new array.

Refer How does ArrayList work internally in Java to know more about how does ArrayList work internally in Java.


Hierarchy of the ArrayList

To know the hierarchy of java.util.ArrayList you need to know about 2 interfaces and 2 abstract classes.

  • Collection Interface- Collection interface is the core of the Collection Framework. It must be implemented by any class that defines a collection.
  • List interface- List interface extends Collection interface. Apart from extending all the methods of the Collection interface, List interface defines some methods of its own.
  • AbstractCollection- Abstract class which implements most of the methods of the Collection interface.
  • AbstractList- Abstract class which extends AbstractCollection and implements most of the List interface.

ArrayList extends AbstractList and implements List interface too. Apart from List interface, ArrayList also implements RandomAccess, Cloneable, java.io.Serializable interfaces.

Saturday, July 6, 2024

Spring MVC - Binding List of Objects Example

In this post we’ll see how to bind a list of objects in Spring MVC so that the objects in that List can be displayed in the view part or to add to an ArrayList by binding object properties in view.

Spring MVC Project structure using Maven


Maven Dependencies

This example uses Spring 6 which uses Jakarta EE 9+ (in the jakarta namespace). JSTL tags are also used in this Spring MVC example for binding list of objects so you need to add Maven dependency for JSTL apart from Spring dependencies. Here only properties and dependencies section is given.

<properties>
  <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  <maven.compiler.source>17</maven.compiler.source>
  <maven.compiler.target>17</maven.compiler.target>
  <spring.version>6.1.10</spring.version>
</properties>

<dependencies>
    <dependency>
     <groupId>org.springframework</groupId>
     <artifactId>spring-core</artifactId>
     <version>${spring.version}</version>
  </dependency>
  <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-context</artifactId>
      <version>${spring.version}</version>
  </dependency>
  <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-webmvc</artifactId>
      <version>${spring.version}</version>
  </dependency>
  <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-web</artifactId>
      <version>${spring.version}</version>
  </dependency>
<!-- https://mvnrepository.com/artifact/jakarta.servlet.jsp.jstl/jakarta.servlet.jsp.jstl-api -->
<dependency>
    <groupId>jakarta.servlet.jsp.jstl</groupId>
    <artifactId>jakarta.servlet.jsp.jstl-api</artifactId>
    <version>3.0.0</version>
</dependency>
<dependency>
    <groupId>org.glassfish.web</groupId>
    <artifactId>jakarta.servlet.jsp.jstl</artifactId>
    <version>3.0.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/jstl/jstl -->
<dependency>
    <groupId>jstl</groupId>
    <artifactId>jstl</artifactId>
    <version>1.2</version>
</dependency>

  <!--<dependency>
     <groupId>taglibs</groupId>
     <artifactId>standard</artifactId>
     <version>1.1.2</version>
</dependency>-->
  <dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.11</version>
    <scope>test</scope>
  </dependency>
  
</dependencies>

Spring MVC binding List example – Required XML Configuration

Since JSTL tags are used in JSP so you need your view to resolve to JstlView, for that you need to add viewClass property in the bean definition for InternalResourceViewResolver in your DispatcherServlet configuration.

<bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
  <property name="viewClass" value="org.springframework.web.servlet.view.JstlView" />
  <property name="prefix" value="/WEB-INF/jsp/" />
  <property name="suffix" value=".jsp" />
</bean>

Spring MVC binding List example – Model classes

List stores objects of the User class which is defined as given below.

public class User {

 private String firstName;
 private String lastName;
 private String email;
 public User() {}
 public User(String firstName, String lastName, String email) {
  this.firstName = firstName;
  this.lastName = lastName;
  this.email = email;
 }
 
 public String getFirstName() {
  return firstName;
 }
 public void setFirstName(String firstName) {
  this.firstName = firstName;
 }
 public String getLastName() {
  return lastName;
 }
 public void setLastName(String lastName) {
  this.lastName = lastName;
 }
 public String getEmail() {
  return email;
 }
 public void setEmail(String email) {
  this.email = email;
 }
}

Following class acts a container for the List of User objects.

public class UserListContainer {
  private List<User> users;

  public List<User> getUsers() {
    return users;
  }

  public void setUsers(List<User> users) {
    this.users = users;
  }
}

Spring MVC binding List example – Controller class

@Controller
public class UserController {
  @RequestMapping(value = "/getUsers", method = RequestMethod.GET)
  public String getUsers(Model model) throws Exception{
    List<User> users = getListOfUsers();
    UserListContainer userList = new UserListContainer();
    userList.setUsers(users);
    model.addAttribute("Users", userList);
    return "showUsers";
  }
    
  // Dummy method for adding List of Users
  private List<User> getListOfUsers() {
    List<User> users = new ArrayList<User>();
    users.add(new User("Jack", "Reacher", "abc@xyz.com"));
    users.add(new User("Remington", "Steele", "rs@cbd.com"));
    users.add(new User("Jonathan", "Raven", "jr@sn.com"));
    return users;
  }    
}

In the controller class there is a handler method getUsers() where a list of users is created and set to the UserListContainer which in turn is added as an attribute to the Model. Logical view name returned from the method is showUsers which resolves to a JSP at the location WEB-INF\jsp\showUsers.jsp.

Spring MVC binding List example – Views

If you just want to iterate the list and show the object fields then you can use the given JSP.

WEB-INF\jsp\showUsers.jsp

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html>
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib prefix="form" uri="http://www.springframework.org/tags/form"%>
<html>
<head>
<meta charset="ISO-8859-1">
<title>Spring MVC List of objects display</title>
</head>
<body>
<table>
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>Email</th>
</tr>
<c:forEach items="${Users.users}" var="user" varStatus="tagStatus">
  <tr>
    <td>${user.firstName}</td>
    <td>${user.lastName}</td>
    <td>${user.email}</td>
  </tr>
</c:forEach>
</table>
</body>
</html>

Here JSTL Core <c:forEach> tag is used to iterate over a collection of objects. Using var attribute you can provide a variable to point to the current item in the collection, which is "user" in the code.

Just showing the object fields

Binding list of objects

If you want to iterate the list, show the object fields and want to bind the List of objects again to modelAttribute then you can use the following JSP. The JSP displays a form with input boxes mapping to each object property. Form gives the functionality to update email of the user.

WEB-INF\jsp\updateUsers.jsp

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html>
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib prefix="form" uri="http://www.springframework.org/tags/form"%>
<html>
<head>
<meta charset="ISO-8859-1">
<title>Spring MVC List of objects display</title>
</head>
<body>
<form:form method="POST" action="saveUsers" modelAttribute="Users">
<table>
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>Email</th>
</tr>
<c:forEach items="${Users.users}" var="user" varStatus="tagStatus">
    <tr>
        <td><form:input path="users[${tagStatus.index}].firstName" value="${user.firstName}" readonly="true" /></td>
        <td><form:input path="users[${tagStatus.index}].lastName" value="${user.lastName}" readonly="true" /></td>
        <td><form:input path="users[${tagStatus.index}].email" value="${user.email}" /></td>
    </tr>
</c:forEach>
</table>
<input type="submit" value="Save" />
</form:form>
</body>
</html>

Important points to note here

  1. In this JSP Spring form tags are used for Spring MVC form fields and for looping the List JSTL tag is used. For these tag libraries following lines are added in the JSP.
    <%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
    <%@ taglib prefix="form" uri="http://www.springframework.org/tags/form"%>
    
  2. Using varStatus attribute you can get the current index (loop status). Index is 0 based.
  3. Path attribute in form tag is the path to property for data binding. For example, in the first iteration of the loop, because of providing this path attribute, input element will translate to.
      
    <tr>
      <td><input id="users0.firstName" name="users[0].firstName" value="Jack" readonly="readonly" type="text" /></td>
      <td><input id="users0.lastName" name="users[0].lastName" value="Reacher" readonly="readonly" type="text" /></td>
      <td><input id="users0.email" name="users[0].email" value="abc@xyz.com" type="text" /></td>
    </tr>
    
    As you can see what is displayed in the input boxes maps to properties of the first User object in the List.

You need to change the UserController class to test this.

import java.util.ArrayList;
import java.util.List;

import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import com.netjstech.springweb.model.User;
import com.netjstech.springweb.model.UserListContainer;

@Controller
public class UserController {
  @RequestMapping(value = "/getUsers", method = RequestMethod.GET)
  public String getUsers(Model model) throws Exception{
    List<User> users = getListOfUsers();
    UserListContainer userList = new UserListContainer();
    userList.setUsers(users);
    model.addAttribute("Users", userList);
    return "updateUsers";
  }
    
  // Dummy method for adding List of Users
  private List<User> getListOfUsers() {
    List<User> users = new ArrayList<User>();
    users.add(new User("Jack", "Reacher", "abc@xyz.com"));
    users.add(new User("Remington", "Steele", "rs@cbd.com"));
    users.add(new User("Jonathan", "Raven", "jr@sn.com"));
    return users;
  } 
  
  @RequestMapping(value = "/saveUsers", method = RequestMethod.POST)
  public String saveUsers(@ModelAttribute("Users") UserListContainer userList) throws Exception{
    List<User> users = userList.getUsers();
    for(User user : users) {
      System.out.println("Email- " + user.getEmail());
    }
    return "updateUsers";
  }
}

Once the application is deployed it can be accessed using the URL - http://localhost:8080/spring-mvc/getUsers which now resolves to updatedUsers.jsp view.

Showing the object fields and binding to Model

Binding list of objects Spring MVC

From the above image you can notice that email for two of the users is updated. On clicking save you can check in the console also to verify that the user object properties are actually updating.

Email- jack@xyz.com
Email- remingtons@cbd.com
Email- jr@sn.com
  

That's all for this topic Spring MVC - Binding List of Objects Example. If you have any doubt or any suggestions to make please drop a comment. Thanks!

>>>Return to Spring Tutorial Page


Related Topics

  1. Spring MVC File Download Example
  2. Spring MVC Exception Handling Example Using @ExceptionHandler And @ControllerAdvice
  3. Difference Between @Controller And @RestController Annotations in Spring
  4. Spring MVC Dot (.) Truncation Problem With @PathVariable Annotation
  5. Spring JdbcTemplate Insert, Update And Delete Example

You may also like-

  1. Spring Batch Processing With List of Objects in batchUpdate() Method
  2. registerShutdownHook() Method in Spring Framework
  3. ApplicationContextAware And BeanNameAware Interfaces in Spring Framework
  4. Autowiring Using Annotations in Spring
  5. Java Stream API Interview Questions And Answers
  6. Difference Between ArrayList And CopyOnWriteArrayList in Java
  7. Multiple Catch Blocks in Java Exception Handling
  8. Uber Mode in Hadoop

Thursday, July 4, 2024

Node.js - MySQL Connection Pool

In the post Node.js - Connect to MySQL Promise API we have seen how to connect to MySQL using Promise based API of mysql2 package. Using mysql2 package you can also create a connection pool.

By using a database connection pool, you can create a pool of connections that can be reused, that helps in reducing the time spent connecting to the MySQL server. When you need to connect to DB you take a connection from the pool and that connection is released again to the pool, rather than closing it, when you are done.

Creating MySQl connection pool

You can create a connection pool by using createPool() method.

import mysql from 'mysql2/promise';
const pool = mysql.createPool({
  host: 'localhost',
  user: 'USER_NAME',
  password: 'PASSWORD',
  database: 'node', 
  port: 3306
});

Replace USER_NAME and PASSWORD with your MySQL configured user name and password. Port number used is the default 3306 (if you are using default port even port number is optional) and it is connecting to DB named node which I have created in MySQL.

There are other settings also for which default is used if no value is provided.

import mysql from 'mysql2/promise';

const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  database: 'test',
  waitForConnections: true,
  connectionLimit: 10,
  maxIdle: 10, // max idle connections, the default value is the same as `connectionLimit`
  idleTimeout: 60000, // idle connections timeout, in milliseconds, the default value 60000
  queueLimit: 0,
  enableKeepAlive: true,
  keepAliveInitialDelay: 0,
});

MYSQL2 Connection Pool with Promise Based API Example

Here is a complete example where connection pool is used to get a connection and execute queries.

Table used for the example-

CREATE TABLE `node`.`employee` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `join_date` DATE NOT NULL,
  `age` INT NULL,
  PRIMARY KEY (`id`));

util\database.js

This is the file where connection pool is created and pool object is exported so that it can be used in other files.

const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'admin',
  database: 'node',
  waitForConnections: true, // this is default anyway
  connectionLimit: 10, // this is default anyway
});

module.exports = pool;

The pool does not create all connections upfront but creates them on demand until the connection limit is reached.

app.js

const pool = require('./util/database');

async function insertEmployee(empName, joinDate, age){
  const sql = "INSERT INTO EMPLOYEE (name, join_date, age) values (?, ?, ?)";
  const values = [empName, joinDate, age];
  try{
    const conn = await pool.getConnection();
    const [result, fields] = await conn.execute(sql, values);
    console.log(result);
    console.log(fields);
    conn.release();
  }catch(err){
    console.log(err);
  }
}

insertEmployee('Rajesh', '2023-06-17', 34);

Important points to note here-

  1. insertEmployee() function is a async function as we are using async/await (Promise based API) rather than callback based API.
  2. We are using await with pool.getConnection() method.
  3. By using array destructuring we get the returned values for result and fields.
  4. fields variable contains extra meta data about results, if available.
  5. result contains a ResultSetHeader object, which provides details about the operation executed by the server.
  6. After the task, connection is released using conn.release() which means connection goes back to the pool.

On running the file-

>node app.js

ResultSetHeader {
  fieldCount: 0,
  affectedRows: 1,
  insertId: 1,
  info: '',
  serverStatus: 2,
  warningStatus: 0,
  changedRows: 0
}

undefined

In the above example connection is acquired manually from the pool and manually returned to the pool.

You can also use pool.query() and pool.execute() methods directly. When using these methods connection is automatically released when query resolves.

Here is one more function getEmployees() where pool.query() is used.

async function getEmployees(){
  const sql = "SELECT * FROM EMPLOYEE";
  try{
    const [result, fields] = await pool.query(sql);
    console.log(result);
  }catch(err){
    console.log(err);
  }
}

getEmployees();

On running the file app.js with getEmployee() method-

>node app.js
[
  {
    id: 1,
    name: 'Rajesh',
    join_date: 2023-06-16T18:30:00.000Z,
    age: 34
  }
]

That's all for this topic Node.js - MySQL Connection Pool. If you have any doubt or any suggestions to make please drop a comment. Thanks!


Related Topics

  1. Node.js - How to Connect to MySQL
  2. Writing a File in Node.js

You may also like-

  1. Node.js path.resolve() Method
  2. NodeJS Blocking Non-blocking Code
  3. Java Record Class With Examples
  4. Exception Propagation in Java Exception Handling
  5. Invoking Getters And Setters Using Reflection in Java
  6. React Virtual DOM
  7. Spring Transaction Management Example - @Transactional Annotation and JDBC

Node.js path.resolve() Method

In the post Node.js path.join() Method we saw how path.join() method can be used to join the path segments to form a final path. In Node.js path module there is also a path.resolve() method that is used to resolve path segments into an absolute path.

The given path segments are processed from right to left, with each subsequent path prepended until an absolute path is constructed. Method stops prepending more path segments as soon as an absolute path is formed.

After processing all the given path segments if an absolute path has not yet been generated, the current working directory is used to construct an absolute path.

If no path segments are passed, path.resolve() will return the absolute path of the current working directory.

The resulting path is normalized and trailing slashes are removed unless the path is resolved to the root directory.

path.resolve() method syntax

path.resolve([...paths])

...paths is a sequence of path segments of type string that are resolved into an absolute path.

Method returns a String representing an absolute path.

A TypeError is thrown if any of the arguments is not a string.

path.resolve() method Node.js examples

Suppose I have a Node.js app created under nodews directory with that context let's try to use path.resolve() method to see how absolute paths are formed. We'll also use path.join() with the same path segments to give an idea how path.resolve() differs from path.join() method.

1. Passing various path segments

const path = require('path');

console.log(__dirname);

const filePath = path.join("app", "views", "mypage.html");
console.log('path.join() output:', filePath);

const resolvePath = path.resolve("app", "views", "mypage.html");
console.log('path.resolve() output:', resolvePath);

Output

D:\NETJS\NodeJS\nodews
path.join() output: app\views\mypage.html
path.resolve() output: D:\NETJS\NodeJS\nodews\app\views\mypage.html

As you can see path.join() just adds the path segment and returns it whereas path.resolve() tries to construct an absolute path using passed path segments. Since it is not able to generate an absolute path after processing all given path segments, the current working directory is used to construct an absolute path.

2. Giving one of the path segments with separator.

const path = require('path');

console.log(__dirname);

const filePath = path.join("/app", "views", "mypage.html");
console.log('path.join() output:', filePath);

const resolvePath = path.resolve("/app", "views", "mypage.html");
console.log('path.resolve() output:', resolvePath);

Output

D:\NETJS\NodeJS\nodews
path.join() output: \app\views\mypage.html
path.resolve() output: D:\app\views\mypage.html

As you can see path.join() just adds the path segment and returns it whereas path.resolve() processes the path segments from right to left until an absolute path is constructed. Method is able to form an absolute path when it comes to '/app' path segments.

3. Having more path segments with separator.

const path = require('path');

console.log(__dirname);

const filePath = path.join("/app", "/views", "mypage.html");
console.log('path.join() output:', filePath);

const resolvePath = path.resolve("/app", "/views", "mypage.html");
console.log('path.resolve() output:', resolvePath);

Output

D:\NETJS\ NodeJS\nodews
path.join() output: \app\views\mypage.html
path.resolve() output: D:\views\mypage.html

4. Having separator in the rightmost path segment.

const path = require('path');

console.log(__dirname);

const filePath = path.join("/app", "/views", "/mypage.html");
console.log('path.join() output:', filePath);

const resolvePath = path.resolve("/app", "/views", "/mypage.html");
console.log('path.resolve() output:', resolvePath);

Output

D:\NETJS\NodeJS\nodews
path.join() output: \app\views\mypage.html
path.resolve() output: D:\mypage.html

5. Using '..' (one level up) as one of the path segments.

const path = require('path');

console.log(__dirname);

const filePath = path.join("/app", "/views", "..", "mypage.html");
console.log('path.join() output:', filePath);

const resolvePath = path.resolve("/app", "/views", "..", "mypage.html");
console.log('path.resolve() output:', resolvePath);

Output

D:\NETJS\NodeJS\nodews
path.join() output: \app\mypage.html
path.resolve() output: D:\mypage.html

Here path.resolve() will initially construct the absolute path as D:\views\..\mypage.html which is then normalized to D:\mypage.html

That's all for this topic Node.js path.resolve() Method. If you have any doubt or any suggestions to make please drop a comment. Thanks!

>>>Return to Angular Tutorial Page


Related Topics

  1. Node.js path.basename() Method With Examples
  2. __dirname and __filename in Node.js
  3. NodeJS Event Loop
  4. Node.js - Connect to MySQL Promise API

You may also like-

  1. Difference Between __dirname and process.cwd() in Node.js
  2. Reading a File in Node.js
  3. Node.js Event Driven Architecture
  4. Java ThreadLocal Class With Examples
  5. Why Class Name And File Name Should be Same in Java
  6. Running Dos/Windows Commands From Java Program
  7. Angular Pipes With Examples
  8. Spring Boot Event Driven Microservice With Kafka

Monday, July 1, 2024

Node.js path.join() Method

The path.join() method in Node.js Path module is used to join all given path segments together to form a single well formed path. While forming the path path.join() method takes care of the following.

  1. Uses the platform-specific separator as a delimiter. For example / on POSIX and either \ or / on Windows.
  2. Normalizes the resulting path. While normalizing following tasks are done.
    • Resolves '..' (parent directory or one level up) and '.' (current directory) segments.
    • When multiple, sequential path segment separation characters are found (e.g. / on POSIX and either \ or / on Windows), they are replaced by a single instance of the platform-specific path segment separator (/ on POSIX and \ on Windows).
    • If the path is a zero-length string, '.' is returned, representing the current working directory.

path.join() method syntax

path.join([...paths])

...paths is a sequence of path segments of type string which are joined together to form a path.

Method returns a String representing the final path.

Throws TypeError if any of the path segments is not a string.

path.join() method Node.js examples

1. Joining various path segments

const path = require('path');

const filePath = path.join("app", "views", "mypage.html");
console.log(filePath);

Output

app/views/mypage.html

2. Normalizing path by removing any extra path separators.

const path = require('path');

const filePath = path.join("app", "/views", "//mypage.html");
console.log(filePath);

Output

app/views/mypage.html

3. Using with __dirname to get the path starting from the directory of currently executing file.

const path = require('path');

const filePath = path.join(__dirname, "views", "mypage.html");
console.log(filePath);

Output

D:\NETJS\NodeJS\nodews\views\mypage.html

Same code in Linux system

const path = require('path');

const filePath = path.join(__dirname, "views", "mypage.html");
console.log(filePath);

Output

/home/netjs/nodeapp/views/mypage.html

4. Using '..' path segment to go one level up. For example, if file is executing in directory D:\NETJS\NodeJS\nodews\views\mypage.html then the following code

const path = require('path');

const filePath = path.join(__dirname, "..", "views", "mypage.html");
console.log(filePath);
gives the output as
D:\NETJS\NodeJS\views\mypage.html

It is because the file where this code resides is in D:\NETJS\NodeJS\nodews and '..' path segment means going one level up meaning D:\NETJS\NodeJS

That's all for this topic Node.js path.join() Method. If you have any doubt or any suggestions to make please drop a comment. Thanks!

>>>Return to Angular Tutorial Page


Related Topics

  1. Node.js path.basename() Method With Examples
  2. __dirname and __filename in Node.js
  3. NodeJS Blocking Non-blocking Code
  4. Node.js - How to Connect to MySQL

You may also like-

  1. Appending a File in Node.js
  2. Node.js REPL
  3. Creating HTTP server in Node.js
  4. Difference Between yield And sleep in Java Multi-Threading
  5. Compact Strings in Java
  6. Convert float to String in Java
  7. Directives in Angular
  8. Transaction Management in Spring