Spring MVC Integration with MySQL

We will be explaining the steps involved in integrating Spring MVC and MySQL. In MySQL, we will be having the necessary data which plays the backbone of the application. Let us take a sample application that holds the data of a few students and their NEET marks. Spring MVC application will interact with MySQL and retrieve the data as required.

Steps Involved in Creation of Database and Table

Step 1: Create the database

1.1:  Create a database test.

-- test is the name of the database here

Step 2: Make the database test an active

use test;

Step 3: Create the table

create table studentsdetails(id int auto_increment primary key,
name varchar(25),caste varchar(25),neetmarks int,gender varchar(10));

Step 4: insert records into it

-- 
insert into studentsdetails(name,caste,neetmarks,gender) values ('Geek1','OBC',600,'Female');
insert into studentsdetails(name,caste,neetmarks,gender) values ('Geek2','General',700,'Female');
insert into studentsdetails(name,caste,neetmarks,gender) values ('Geek3','General',600,'Male');
insert into studentsdetails(name,caste,neetmarks,gender) values ('Geek4','OBC',670,'Male');
insert into studentsdetails(name,caste,neetmarks,gender) values ('Geek5','SC',600,'Female');
insert into studentsdetails(name,caste,neetmarks,gender) values ('Geek6','SC',500,'Male');

Step 5: Select the data from it

select * from studentsdetails;

studentsdetails data

Now let us do the necessary steps in the Spring MVC application, the project structure is as follows: 

File: pom.xml

XML

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.students</groupId>
  <artifactId>SpringMVCMedicalSeatAvailabilityBasedOnNEETMarks</artifactId>
  <packaging>war</packaging>
  <properties>
    <maven.compiler.source>1.8</maven.compiler.source>
    <maven.compiler.target>1.8</maven.compiler.target>
</properties>
  <version>0.0.1-SNAPSHOT</version>
  <name>SpringMVCMedicalSeatAvailabilityBasedOnNEETMarks Maven Webapp</name>
  <url>http://maven.apache.org</url>

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.12</version>
      <scope>test</scope>
    </dependency>
     <!-- https://mvnrepository.com/artifact/org.springframework/spring-webmvc -->
<dependency>
<groupId>org.mockito</groupId>
<artifactId>mockito-all</artifactId>
<version>1.9.5</version>
<scope>test</scope>
</dependency>
<dependency>

    <groupId>org.springframework</groupId>
    <artifactId>spring-webmvc</artifactId>
    <version>5.1.1.RELEASE</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-context</artifactId>
    <version>5.1.1.RELEASE</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-test</artifactId>
    <version>5.1.1.RELEASE</version>
    <scope>test</scope>
</dependency>
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-test</artifactId>
   <version>2.4.1</version>
  <scope>test</scope>
</dependency>


<!-- https://mvnrepository.com/artifact/org.apache.tomcat/tomcat-jasper -->
<dependency>
    <groupId>org.apache.tomcat</groupId>
    <artifactId>tomcat-jasper</artifactId>
    <version>9.0.12</version>
</dependency>
    <!-- https://mvnrepository.com/artifact/javax.servlet/javax.servlet-api -->
<dependency>
    <groupId>javax.servlet</groupId>
    <artifactId>servlet-api</artifactId>
    <version>3.0-alpha-1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/javax.servlet/jstl -->
<dependency>
    <groupId>javax.servlet</groupId>
    <artifactId>jstl</artifactId>
    <version>1.2</version>
</dependency>
    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <!-- This is much required to connect to MySQL -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.11</version>
</dependency>
    <!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>5.1.1.RELEASE</version>
</dependency>

  </dependencies>
  <build>
    <finalName>SpringMVCMedicalSeatAvailabilityBasedOnNEETMarks</finalName>
    <sourceDirectory>src/main/java</sourceDirectory>
    <plugins>
<plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-surefire-plugin</artifactId>
                <version>3.0.0-M3</version>
                <configuration>
                <testFailureIgnore>true</testFailureIgnore>
                <shutdown>kill</shutdown> <!-- Use it if required-->
                </configuration>
            </plugin>
            <plugin>
  <groupId>org.apache.maven.plugins</groupId>
  <artifactId>maven-war-plugin</artifactId>
  <version>3.3.2</version>
</plugin>

      <plugin>
        <groupId>org.codehaus.mojo</groupId>
        <artifactId>tomcat-maven-plugin</artifactId>
        <version>1.0-beta-1</version>
      </plugin>
 </plugins>

  </build>
</project>

File: spring-servlet.xml

This is the much-required file to communicate with MySQL

XML

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:mvc="http://www.springframework.org/schema/mvc"
    xsi:schemaLocation="
        http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context.xsd
        http://www.springframework.org/schema/mvc
        http://www.springframework.org/schema/mvc/spring-mvc.xsd">
<context:component-scan base-package="com.students.controllers"></context:component-scan>

<bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
<property name="prefix" value="/WEB-INF/jsp/"></property>
<property name="suffix" value=".jsp"></property>
</bean>
<!-- According to the username and password that we use, the changes need to be done below -->
 <!-- Generally username will be root and password will be empty or will come with a password
Hence accordingly change the data here -->

<bean id="ds" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
<!-- <property name="url" value="jdbc:mysql://localhost:3306/test"></property> -->
  <!-- Here test is the name of the database -->
<property name="url" value="jdbc:mysql://localhost:3306/test?user=root&password=password&serverTimezone=UTC"></property>

<!-- <property name="username" value="root"></property>
<property name="password" value="password"></property>   -->
</bean>

<bean id="jt" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="ds"></property>
</bean>

<bean id="dao" class="com.students.dao.StudentDao">
<property name="template" ref="jt"></property>
</bean>


</beans>

Let us now roll on over to ‘bean class.’ The fields in this bean class should be equivalent to the MySQL table structure. Then only it will be easier and more effective to communicate.

File: Student.java

Java

// Java Program to Illustrate Student Class

// Class
public class Student {

    // Class data members

    // Map to studentsdetails.id
    private int id;
    // Map to studentsdetails.name
    private String name;
    // Map to studentsdetails.caste
    private String caste;
    // Map to studentsdetails.neetMarks
    private int neetMarks;
    // Map to studentsdetails.gender
    private String gender;

    // Getter and setter methods

    // Getter
    public int getNeetMarks() { return neetMarks; }

    // Setter
    public void setNeetMarks(int neetMarks)
    {
        this.neetMarks = neetMarks;
    }

    // Getter
    public String getGender() { return gender; }

    // Setter
    public void setGender(String gender)
    {
        this.gender = gender;
    }

    // Getter
    public int getId() { return id; }

    // Setter
    public void setId(int id) { this.id = id; }

    // Getter
    public String getName() { return name; }

    // Setter
    public void setName(String name) { this.name = name; }

    // Getter
    public String getCaste() { return caste; }

    // Setter
    public void setCaste(String caste)
    {
        this.caste = caste;
    }
}

Now to do the database operations, we need the DAO java file

File: StudentDao.java

Java

// Java Program to Illustrate StudentDao Class

// Importing required classes
import com.students.beans.Student;
import java.sql.SQLException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

// Class
public class StudentDao {

    JdbcTemplate template;

    public void setTemplate(JdbcTemplate template)
    {
        this.template = template;
    }

    // We can search the data from MySQL by means of
    // studentname

    public Student getStudentsByName(String studentName)
        throws SQLException
    {
        String sql
            = "select * from studentsdetails where name=?";
        return template.queryForObject(
            sql, new Object[] { studentName },
            new BeanPropertyRowMapper<Student>(
                Student.class));
    }

    // We can search the data from MySQL by means of caste
    public Student getStudentsByCaste(String caste)
        throws SQLException
    {
        String sql
            = "select * from studentsdetails where caste=?";
        return template.queryForObject(
            sql, new Object[] { caste },
            new BeanPropertyRowMapper<Student>(
                Student.class));
    }

    // We can search the data from MySQL by means of id
    public Student getStudentsById(int id)
        throws SQLException
    {
        String sql
            = "select * from studentsdetails where id =?";
        return template.queryForObject(
            sql, new Object[] { id },
            new BeanPropertyRowMapper<Student>(
                Student.class));
    }

    // We can search the data from MySQL by means of
    // neetmarks
    public Student getStudentsByNeetMarks(int neetMarks)
        throws SQLException
    {
        String sql
            = "select * from studentsdetails where neetMarks=?";
        return template.queryForObject(
            sql, new Object[] { neetMarks },
            new BeanPropertyRowMapper<Student>(
                Student.class));
    }

    // As much of business logic can be written here
}

Let us see the controller class now

StudentController.java

Java

// Java Program to Illustrate StudentController Class

// Importing required classes
import com.students.beans.Student;
import com.students.dao.StudentDao;
import java.sql.SQLException;
import org.springframework.beans.factory.annotation.Autowired;
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 org.springframework.web.bind.annotation.SessionAttributes;
import org.springframework.web.servlet.ModelAndView;

// Class
@Controller
@SessionAttributes("student")
public class StudentController {

    // @Autowired
    // It will inject dao from xml file
    StudentDao dao;

    @Autowired public StudentController(StudentDao dao)
    {
        // this keyword refers to current instance itself
        this.dao = dao;
    }

    @ModelAttribute("student") public Student getStudent()
    {
        return new Student();
    }

    // For searchform
    @RequestMapping("/studentsearchform")
    public String searchform(Model m)
    {
        m.addAttribute("command", new Student());
        return "studentsearchform";
    }

    // It provides check students and determines
    // for medical seat availability based on NEET marks
    // in model object

    // It is up to one to change the logic here
    // for the availability of medical seat
    // Just to show as an example, below calculations
    // aretaken Exception need to be handled carefully as
    // interacting with database
    @RequestMapping(value = "/checkByNeetMarks",
                    method = RequestMethod.POST)
    public ModelAndView
    checkByNeetMarks(@ModelAttribute("student")
                     Student student)
    {

        ModelAndView mav = null;
        Student student1;

        // Try block to check for exceptions
        try {
            student1
                = dao.getStudentsByName(student.getName());
            mav = new ModelAndView("welcome");

            if (null != student1) {
                System.out.println(
                    student1.getId() + "..."
                    + student1.getName() + ".."
                    + student1.getCaste() + "..neet marks.."
                    + student1.getNeetMarks());
                boolean isAvailable = false;
                if (student1.getCaste().equalsIgnoreCase(
                        "General")
                    && student1.getNeetMarks() >= 600) {
                    isAvailable = true;
                }

                if (student1.getCaste().equalsIgnoreCase(
                        "OBC")
                    && student1.getNeetMarks() >= 500) {
                    isAvailable = true;
                }

                if (student1.getCaste().equalsIgnoreCase(
                        "SC")
                    && student1.getNeetMarks() >= 400) {
                    isAvailable = true;
                }

                mav.addObject("firstname",
                              student1.getName());

                if (isAvailable) {
                    mav.addObject(
                        "availability",
                        "Eligible to get Medical Seat");
                }

                else {
                    mav.addObject(
                        "availability",
                        "Not eligible to get Medical Seat");
                }
                mav.addObject("caste", student1.getCaste());
                mav.addObject("neetmarks",
                              student1.getNeetMarks());
            }
            else {
                mav.addObject("firstname",
                              student.getName());
                mav.addObject(
                    "availability",
                    "Not present in the database");
                // mav.addObject("location",
                // student.getLocation());
            }
        }

        // Catch block to handle SQL Exceptions
        catch (SQLException e) {

            // Displaying exception along with line number
            // using printStackTrace() method
            e.printStackTrace();
        }

        return mav;
    }
}

The above set can be able to be prepared as a war file and it can be deployed under tomcat webapps folder.

At the start of Tomcat, the above application can be invoked by using

http://localhost:8080/SpringMVCMedicalSeatAvailabilityBasedOnNEETMarks/

As this is taken as a sample project, not much beautification is done

We can click on this link and move forward

On click of the link, we will get as below

Check for the url change. All should be aligned with controller 

Usecase:

According to the logic written, we are getting results, here 

• “Geek1” is the name given for the search. It will be checked against the “studentsdetails” table 
• Circled one indicates the name of the request mapping. 

It is a sample application and the necessary steps are given in this which interacts with the MySQL database. Using Spring MVC and MySQL, we can easily carry out the business logic easily.