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" <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> <dependencies> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> <scope>test</scope> </dependency><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><dependency> <groupId>org.apache.tomcat</groupId> <artifactId>tomcat-jasper</artifactId> <version>9.0.12</version></dependency><dependency> <groupId>javax.servlet</groupId> <artifactId>servlet-api</artifactId> <version>3.0-alpha-1</version> </dependency><dependency> <groupId>javax.servlet</groupId> <artifactId>jstl</artifactId> <version>1.2</version></dependency> <!-- This is much required to connect to MySQL --><dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.11</version></dependency><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"?> xsi:schemaLocation=" <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 passwordHence 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// Classpublic 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 classesimport com.students.beans.Student;import java.sql.SQLException;import org.springframework.jdbc.core.BeanPropertyRowMapper;import org.springframework.jdbc.core.JdbcTemplate;// Classpublic 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 classesimport 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.
