Tuesday, October 15, 2024

Spring MVC Checkbox And Checkboxes Form Tag Example

In this post we’ll see how to use checkbox and checkboxes provided by the form tag in the Spring MVC framework.

Technologies used

Following is the list of tools used for the Spring MVC checkbox and checkboxes form tag example.

  1. Spring 6.1.x Release (Spring core, spring web, spring webmvc).
  2. Java 21
  3. JSTL tag library
  4. Tomcat server V 10.x

Spring MVC Project structure using Maven

Maven Dependencies

Apart from Spring dependencies following dependency is also needed in the pom.xml for JSTL tags.

<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>

<form:checkbox> and <form:checkboxes> tags in Spring MVC

  • <form:checkbox>- This tag renders an HTML 'input' tag with type 'checkbox'. With this tag the value for the checkbox is hardcoded with in the JSP page.
  • <form:checkboxes>- This tag renders multiple HTML 'input' tags with type 'checkbox'. If you don't want to list the value for the checkbox with in the JSP but want to provide a list of available options at runtime and pass that in to the tag then you can use checkboxes tag. You pass in an Array, a List or a Map containing the available options in the "items" property.

Spring MVC checkbox example

For the Spring MVC form checkbox example let’s assume there is a class UserPreferences which is used to list out preferences as check boxes in the JSP page.

Spring MVC checkbox example – Model Class

public class UserPreferences {
 private boolean receiveNewsletter;
 private String[] cardioExercises;
 private String favouriteFood;
 public boolean isReceiveNewsletter() {
  return receiveNewsletter;
 }
 public void setReceiveNewsletter(boolean receiveNewsletter) {
  this.receiveNewsletter = receiveNewsletter;
 }
 public String[] getCardioExercises() {
  return cardioExercises;
 }
 public void setCardioExercises(String[] cardioExercises) {
  this.cardioExercises = cardioExercises;
 }
 public String getFavouriteFood() {
  return favouriteFood;
 }
 public void setFavouriteFood(String favouriteFood) {
  this.favouriteFood = favouriteFood;
 }
}

Spring MVC checkbox example – View

Following JSP (preferences.jsp) shows all the approaches to the checkbox tag in Spring MVC.

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
  <meta charset="UTF-8">
  <%@ taglib prefix="form" uri="http://www.springframework.org/tags/form"%>
  <title>Spring MVC checkbox example in form tag</title>
</head>
<body>
  <form:form method="POST" action="showPreferences" modelAttribute="preferences">
  <table>
    <tr>
      <td>Subscribe to newsletter?:</td>
      <!--  Property is of type java.lang.Boolean-->
      <td><form:checkbox path="receiveNewsletter"/></td>
    </tr>
    <tr>
      <td>Favorite cardio exercises:</td>
      <!--  Property is of an array or of type java.util.Collection -->
      <td>Running: <form:checkbox path="cardioExercises" value="Running"/>
      <td>Skipping: <form:checkbox path="cardioExercises" value="Skipping"/>
      <td>Cycling: <form:checkbox path="cardioExercises" value="Cycling"/>
      <td>Burpee: <form:checkbox path="cardioExercises" value="Burpee"/>
    </tr>
    <tr>
      <td>Favourite Food:</td>
      <%-- Property is of type java.lang.Object --%>
      <td>Raw Vegetables: <form:checkbox path="favouriteFood" value="Raw Vegetables"/></td>
      <td>Steamed Vegetables: <form:checkbox path="favouriteFood" value="Steamed Vegetables"/></td>
    </tr>
    <tr>
      <td><input type="submit" value="Submit"></td>
    </tr>
  </table>
  </form:form>
</body>
</html>

The check boxes with in the JSP are checked or left unchecked based on the following-

  • When the bound value is of type java.lang.Boolean, the input(checkbox) is marked as 'checked' if the bound value is true.
  • When the bound value is of type array or java.util.Collection, the input(checkbox) is marked as 'checked' if the configured value is present in the bound Collection.
  • For any other bound value type, the input(checkbox) is marked as 'checked' if the configured setValue(Object) is equal to the bound value.

The values for the properties are taken from an object of type UserPreferences bean which is bound using the attribute “modelAttribute” with in the form tag. The object is set with in the handler method of the Controller class.

There is another JSP that is used to display the values selected by checking the check boxes.

showPreferences.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<title>User Preferences</title>
</head>
<body>

  <div>
    <span>Subscribe to newsletter?:</span>
    <span>${preferences.receiveNewsletter}</span>
  </div>
  <div>
    <span>Favorite cardio exercises:</span>
    <c:forEach items="${preferences.cardioExercises}" var="exercise" varStatus="counter">
      <span>${exercise}
        <c:if test="${not counter.last}">
          <c:out value="," ></c:out> 
        </c:if>
      </span>        
    </c:forEach>
  </div>
  <div>
    <span>Favourite Food:</span>
    <span>${preferences.favouriteFood}</span>
  </div>
</table>
</body>
</html>

Spring MVC checkbox example – Configuration changes

Since JSTL tags are also used so you need to configure InternalResourceViewResolver to resolve a JstlView for that following configuration has to be added in the configuration file.

<bean id="JSPViewResolver" 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 checkbox example – Controller class

import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
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.UserPreferences;

@Controller
public class PreferenceController {
	@GetMapping("/preferences")
	public String showUserPreferences(Model model) throws Exception{
		UserPreferences pref = new UserPreferences(); 
		pref.setReceiveNewsletter(true);
		pref.setCardioExercises(new String[]{"Running", "Burpee"});
		pref.setFavouriteFood("Steamed Vegetables");
		model.addAttribute("preferences", pref);
		return "preferences";
	}
	 
	@RequestMapping(value = "/showPreferences", method = RequestMethod.POST)
	public String showPreferences(@ModelAttribute("preferences") UserPreferences preferences, Model model) throws Exception{
		//System.out.println("In ShowPreferences");
		model.addAttribute("preferences", preferences);
		return "showPreferences";
	}
}

In the Controller class, showUserPreferences() method is used to handle the /preferences request path. Method returns the view name as "preferences" which resolves to /WEB-INF/jsp/preferences.jsp JSP.

In the handler method, object of UserPreferences class is set to the Model which is used in the JSP to mark the check box as checked or leave it unchecked. If you want some of the check boxes to be checked in the JSP by default then you can set the values for the properties in the UserPreferences object.

Another handler method showPreferences() handles the request when submit button is clicked in the preferences.jsp.

Deploying and testing the application

Once the application is deployed to Tomcat server it can be accessed using the URL- http://localhost:8080/springwebproj/preferences (This is as per my project name- springwebproj)

Spring MVC checkbox example

In the above page it shows those check boxes as checked for which values are set in the handler method of the controller class. In the following page some more boxes are checked before clicking on submit button.

Spring MVC form checkbox tag

Page which shows all the values that are checked.

Spring MVC checkboxes tag example

If you want to provide the list of options for the checkbox at runtime rather than hardcoding them then you can add checkboxes tag in Spring MVC application. You pass in an Array, a List or a Map containing the available options in the "items" property.

Spring MVC checkboxes example – Model Class

public class UserPreferences {
  private boolean receiveNewsletter;
  private String[] cardioExercises;
  private List<String> favouriteFood;
  public boolean isReceiveNewsletter() {
    return receiveNewsletter;
  }
  public void setReceiveNewsletter(boolean receiveNewsletter) {
    this.receiveNewsletter = receiveNewsletter;
  }
  public String[] getCardioExercises() {
    return cardioExercises;
  }
  public void setCardioExercises(String[] cardioExercises) {
    this.cardioExercises = cardioExercises;
  }
  public List<String> getFavouriteFood() {
    return favouriteFood;
  }
  public void setFavouriteFood(List<String> favouriteFood) {
    this.favouriteFood = favouriteFood;
  }
}

Spring MVC checkboxes example – Views

preferences.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
  <meta charset="UTF-8">
  <%@ taglib prefix="form" uri="http://www.springframework.org/tags/form"%>
  <title>Spring MVC checkbox example in form tag</title>
</head>
<body>
  <form:form method="POST" action="showPreferences" modelAttribute="preferences">
    <table>
      <tr>
        <td>Subscribe to newsletter?:</td>
        <!--  Property is of type java.lang.Boolean-->
        <td><form:checkbox path="receiveNewsletter"/></td>
      </tr>
      <tr>
        <td>Favorite cardio exercises:</td>
        <td><form:checkboxes path="cardioExercises" items="${prefMap}"/></td>
      </tr>
      <tr>
        <td>Favourite Food:</td>
        <td><form:checkboxes path="favouriteFood" items="${foodList}"/></td>
      </tr>
      <tr>
        <td><input type="submit" value="Submit"></td>
      </tr>
    </table>
  </form:form>
</body>
</html>

As you can see now <form:checkboxes> tag is used with the items property. The values used with the items property in the JSP prefMap and foodList should be available as a model attribute containing String of values to be selected from. If a Map is used, the map entry key will be used as the value and the map entry’s value will be used as the label to be displayed.

There is another JSP that is used to display the values selected by checking the check boxes.

showPreferences.jsp

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="ISO-8859-1">
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<title>User Preferences</title>
</head>
<body>
  <div>
    <span>Subscribe to newsletter?:</span>
    <span>${preferences.receiveNewsletter}</span>
  </div>
  <div>
    <span>Favorite cardio exercises:</span>
    <c:forEach items="${preferences.cardioExercises}" var="exercise" varStatus="counter">
        <span>${exercise}
          <c:if test="${not counter.last}">
               <c:out value="," ></c:out> 
          </c:if>
        </span>   
    </c:forEach>
  </div>
  <div>
    <span>Favourite Food:</span>
    <c:forEach items="${preferences.favouriteFood}" var="food" varStatus="foodCounter">
        <span>${food}
        <c:if test="${not foodCounter.last}">
          <c:out value="," ></c:out> 
        </c:if>
        </span>           
    </c:forEach>
  </div>
</body>
</html>

Spring MVC checkboxes example – Controller class

import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
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.UserPreferences;

@Controller
public class PreferenceController {
  @GetMapping("/preferences")
  public String showUserPreferences(Model model) throws Exception{
    UserPreferences pref = new UserPreferences();    
    //default check values
    pref.setReceiveNewsletter(true);
    pref.setCardioExercises(new String[]{"Running", "Burpee"});
    pref.setFavouriteFood(Arrays.asList("Boiled legumes", "Steamed Vegetables"));
    // Preparing values for "Favorite cardio exercises" check box
    Map<String, String> prefMap = new HashMap<>();
    prefMap.put("Running", "Running");
    prefMap.put("Burpee", "Burpee");
    prefMap.put("Skipping", "Skipping");
    prefMap.put("Cycling", "Cycling");    
    model.addAttribute("prefMap", prefMap);
    model.addAttribute("preferences", pref);        
    return "preferences";
  }
  
  // List for "Favourite Food" check box
  @ModelAttribute("foodList")
  public List<String> getFoodList(){
    List<String> foodList = new ArrayList<>();
    foodList.add("Steamed Vegetables");
    foodList.add("Boiled legumes");
    foodList.add("Pizza");
    foodList.add("Burger");
    return foodList;
  }
    
  @RequestMapping(value = "/showPreferences", method = RequestMethod.POST)
  public String showPreferences(@ModelAttribute("preferences") UserPreferences preferences, Model model) throws Exception{
    model.addAttribute("preferences", preferences);
    return "showPreferences";
  }
}

As you can see prefMap and foodList which are used in the JSP to show options for checkboxes in the JSP are set here as model attribute. If you want some of the check boxes to be checked in the JSP then you can set the values for the properties in the UserPreferences object.

Deploying and testing the application

Once the application is deployed to Tomcat server it can be accessed using the URL- http://localhost:8080/springwebproj/preferences

Spring MVC checkboxes example

This is the page with the check boxes marked as checked for the properties set in the handler method of the controller class.

Page which shows all the values that are checked.

Spring MVC form checkboxes tag

Reference- https://docs.spring.io/spring/docs/current/spring-framework-reference/web.html#mvc-view-jsp-formtaglib

That's all for this topic Spring MVC Checkbox And Checkboxes Form Tag 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 Web MVC Tutorial
  2. Spring MVC Radiobutton And Radiobuttons Form Tag Example
  3. Spring MVC PDF Generation Example
  4. Spring MVC Generate Response as JSON Example
  5. Spring MVC Form Example With Bean Validation

You may also like-

  1. Connection Pooling With Apache DBCP Spring Example
  2. BeanFactoryAware Interface in Spring Framework
  3. Autodiscovery of Bean Using componenent-scan in Spring
  4. How to Inject Prototype Scoped Bean in Singleton Bean
  5. Volatile in Java
  6. Type Erasure in Java Generics
  7. Searching Within a String Using indexOf(), lastIndexOf() And contains() Methods
  8. How to Sort an ArrayList in Descending Order in Java

Monday, October 14, 2024

Statement Interface in Java-JDBC

In the post Java JDBC Steps to Connect to DB we have already seen a complete example using the interfaces Driver, Connection, Statement and ResultSet provided by the JDBC API. In this post we’ll see Java Statement interface in detail.

Statement interface in JDBC

java.sql.Statement interface in JDBC API is used to execute a static SQL statement and returning the result of the executed query.

Statement interface has following two sub-interfaces

  1. PreparedStatement
  2. CallableStatement

PreparedStatement– PreparedStatement object stores the SQL statement in its pre-compiled state. That way it can efficiently execute the same SQL statement multiple times with different parameters.

CallableStatement- This interface is used to execute SQL stored procedures.

You can get a Statement object by calling the Connection.createStatement() method on the Connection object.

Frequently used methods of the Statement interface

Mostly you will use the execute methods of the Java Statement interface to execute queries.

  1. boolean execute(String sql)- Executes the given SQL statement (it can be any kind of SQL query), which may return multiple results.
    Returns a boolean which is true if the first result is a ResultSet object; false if it is an update count or there are no results.
  2. ResultSet executeQuery(String sql)- Executes the given SQL statement, which returns a single ResultSet object. If you want to execute a Select SQL query which returns results you should use this method.
  3. int executeUpdate(String sql)- Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement.
    Returns an int denoting either the row count for the rows that are inserted, deleted, updated or returns 0 if nothing is returned.
    Note:This method cannot be called on a PreparedStatement or CallableStatement.
  4. int[] executeBatch()- Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts.

Java Statement example

Let’s see an example where SQL statements are executed using execute(), executeUpdate and executeQuery methods. In the example-

Using execute() method a SQL statement is executed and then the boolean value is checked.

Using executeUpdate() method insert, update and delete statements are executed and row count of the affected rows is displayed.

Using executeQuery() method select statement is executed and the returned ResultSet is processed.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCStmt {
  public static void main(String[] args) {
    Connection connection = null;
    try {
      // Loading driver
      Class.forName("com.mysql.jdbc.Driver");
   
      // Creating connection
      connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/netjs", 
                   "root", "admin");
  
      // creating Statement
      Statement stmt = connection.createStatement();  
            
      /** execute method **/
      boolean flag = stmt.execute("Update Employee set age = 40 where id in (5, 6)");
      if(flag == false){
        System.out.println("Updated rows " + stmt.getUpdateCount() );
      }
            
      /** executeUpdate method **/
      // Insert
      int count = stmt.executeUpdate("Insert into employee(name, age) values('Kim', 23)");
      System.out.println("Rows Inserted " + count);
            
      // update
      count = stmt.executeUpdate("Update Employee set age = 35 where id = 17");
      System.out.println("Rows Updated " + count);
            
      //delete
      count = stmt.executeUpdate("Delete from Employee where id = 5");
      System.out.println("Rows Deleted " + count);
            
      /** executeQuery method **/
      // Executing Query
      ResultSet rs = stmt.executeQuery("Select * from Employee");

      // Processing Resultset
      while(rs.next()){
        System.out.println("id : " + rs.getInt("id") + " Name : " 
          + rs.getString("name") + " Age : " + rs.getInt("age")); 
      }    
    } catch (ClassNotFoundException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }finally{
      if(connection != null){
        //closing connection 
        try {
          connection.close();
        } catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
        }
      } // if condition
    }// finally
  }
}

Reference: https://docs.oracle.com/en/java/javase/21/docs/api/java.sql/java/sql/Statement.html

That's all for this topic Statement Interface in Java-JDBC. If you have any doubt or any suggestions to make please drop a comment. Thanks!

>>>Return to Java Advanced Tutorial Page


Related Topics

  1. Connection Interface in Java-JDBC
  2. Types of JDBC Drivers
  3. CallableStatement Interface in Java-JDBC
  4. Batch Processing in Java JDBC - Insert, Update Queries as a Batch
  5. Connection Pooling Using C3P0 in Java

You may also like-

  1. BigInteger in Java With Examples
  2. Nested class and Inner class in Java
  3. Serialization Proxy Pattern in Java
  4. How ArrayList Works Internally in Java
  5. CopyOnWriteArrayList in Java With Examples
  6. Volatile Keyword in Java With Examples
  7. Garbage Collection in Java
  8. Spring NamedParameterJdbcTemplate Insert, Update And Delete Example

Sunday, October 13, 2024

Types of JDBC Drivers

JDBC API standardizes the way any Java application connects to DB. JDBC API is a collection of interfaces and JDBC drivers implement these interfaces in the JDBC API enabling a Java application to interact with a database.

The JDBC driver gives out the connection to the database and implements the protocol for transferring the query and result between client and database.

JDBC Driver Types

JDBC drivers can be categorized into four types.

Monday, September 30, 2024

Angular Access Control CanActivate Route Guard Example

In your Angular application you may need to control access to different parts of your app. To control that kind of authorization and authentication you can use route guards in Angular.

Using route guards, you can perform some action before navigating to another route and you can restrict access to routes based on the outcome of that action. In this post we’ll see example of using CanActivate route guard to restrict access to route based on whether user is authenticated or not.


Why route guards in Angular

Use of route guards to control access in your Angular app may be considered for following scenarios-

  • User is not authorized to navigate to the component rendered by the route user want to navigate to.
  • User is not authenticated (not logged in).
  • You need to fetch some data before you display the target component.
  • You want to save pending changes before leaving a component.
  • You want to ask the user if it's OK to discard pending changes rather than save them.

Sunday, September 29, 2024

How to Read File From The Last Line in Java

There are applications where you have to read huge files line by line may be using some tool like Pentaho, Camel. Let's say these files are in the format header, records and footer and you need to check something in the footer (last line of the file) and if that condition is not met you need to reject the file. Now, reading line by line in such scenario, will be wasteful as you'll anyway reject the file in the end.

So for such scenarios it is better to read the last line (or may be last N lines) of the file, to have better throughput. In this post you will see how to read a file from the last line in Java.


In Java reading file from the end can be done using RandomAccessFile which has a seek method to set the file-pointer offset, measured from the beginning of the file.

Apache Commons IO also has a ReversedLinesFileReader class which reads lines in a file reversely.

File used

Let's say you have a file aa.txt which has the following content-

This is the first line.
This is the second line.
This is the third line.
This is the fourth line.

And you want to read last line of this file using Java.

Monday, August 26, 2024

Node.js MySQL Delete Example

In the post Node.js MySQL Update Example we saw examples of updating records in a table using Node.js and MySQL. In this post we'll see examples of deleting records from 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.

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`));

Node.js delete 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 delete Employee record from employee table in MySQL DB.

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

async function deleteEmployee(id){
  // TODO Verify if emp with the same id exists or not.. 
  // For that select query to get by id can be used

  const sql = "DELETE FROM EMPLOYEE where id = ?";
  const values = [id];
    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);
    }
}

deleteEmployee(1);

Important points to note here-

  • deleteEmployee() function is an async function as we are using async/await (Promise based API) rather than callback-based API.
  • We are using await with pool.getConnection() method.
  • deleteEmployee() function takes one argument where it expects an id for the employee to be deleted.
  • Delete query is created as a parameterized query, where id is passed later. By having this kind of prepared statement makes our code more generic to be used with any id and also gives better performance.
  • 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: 0,
  info: '',
  serverStatus: 2,
  warningStatus: 0,
  changedRows: 0
}

undefined

That's all for this topic Node.js MySQL Delete 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 Select Statement Example
  3. Node.js - MySQL Connection Pool

You may also like-

  1. Node.js path.resolve() Method
  2. NodeJS Event Loop
  3. Node.js Event Driven Architecture
  4. How to Setup a Node.js Project
  5. Custom Async Validator in Angular Reactive Form
  6. Pre-defined Functional Interfaces in Java
  7. Java Program to Convert a File to Byte Array
  8. JavaScript let and const With Examples

Saturday, August 24, 2024

Node.js MySQL Update Example

In the post Node.js MySQL Insert Example we have seen how to do an insert in MySQL DB from Node.js application. In this post we'll see examples of updating records in a 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`));

Node.js update 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 update Employee record in MySQL.

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

async function updateEmployee(emp){
  // Verify if emp with the same id exists or not
  // For that select query to get by id can be used

  const sql = "UPDATE EMPLOYEE set name = ?, join_date = ?, age = ? where id = ?";
  const values = [emp.name, emp.joinDate, emp.age, emp.id];
    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);
    }
}

//calling function with employee object
updateEmployee({name:'Ishan', joinDate:'2024-04-23', age:28, id:1})

Important points to note here-

  1. updateEmployee() function is an 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. updateEmployee() function takes one argument where it expects an employee object to be passed.
  4. Using that object, appropriate values are passed to the prepared statement to update a record.
  5. By using array destructuring we get the returned values for result and fields.
  6. fields variable contains extra meta data about results, if available.
  7. result contains a ResultSetHeader object, which provides details about the operation executed by the server.
  8. 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: 0,
  info: 'Rows matched: 1  Changed: 1  Warnings: 0',
  serverStatus: 2,
  warningStatus: 0,
  changedRows: 1
}

Undefined

If you want to get the number of rows which are updated you can get it using result.affectedRows from the result object.

You can check the DB to see the updated values.

Node.js MySQL Update

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


Related Topics

  1. Node.js MySQL Select Statement Example
  2. Node.js MySQL Delete Example
  3. Node.js - Connect to MySQL Promise API

You may also like-

  1. Node.js path.basename() Method With Examples
  2. __dirname and __filename in Node.js
  3. Appending a File in Node.js
  4. Creating HTTP server in Node.js
  5. Service in Angular With Examples
  6. Java Stream - count() With Examples
  7. Why main Method static in Java
  8. Named Tuple in Python