Spring connect to Mysql Database simply (Part 1)
Monday, November 30th, 2015
(0 Comment)
In this article, we will develop simple spring project with select, insert, update, delete (crud) mysql database (You need pactice with HelloWorld project part 1 and part 2 before continute). We create basic page as below image:
crud page with mysql
1. Install mysql database
You can click here to install mysql with XAMPP, and then you create database name is “springdb” and create ROOM table (you can not create table, config hibernate will auto create table in config below)
script create manual table in mysql:
|
1 2 3 4 5 6 |
CREATE TABLE IF NOT EXISTS room ( ID int(11) NOT NULL AUTO_INCREMENT, COUNTROOM int(11) DEFAULT NULL, NAME varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (ID) ) |
2. Create spring project by eclipse
You create spring project with struct as follow:
project’s struct
- entity package: mapping object with database’s table
- dao package: execute sql statements (select, insert, update, delete) into database
- service package: to calculate business rule if any before call dao package
- controller package: manage layout, many action in page screen
You need to config maven with porn.xml file
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 |
<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.demo</groupId> <artifactId>springmvc</artifactId> <packaging>war</packaging> <version>0.0.1-SNAPSHOT</version> <name>springmvc Maven Webapp</name> <url>http://maven.apache.org</url> <properties> <spring-framework.version>4.1.4.RELEASE</spring-framework.version> </properties> <dependencies> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-webmvc</artifactId> <version>${spring-framework.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context-support</artifactId> <version>${spring-framework.version}</version> </dependency> <!-- orm mapping --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-orm</artifactId> <version>${spring-framework.version}</version> </dependency> <!-- driver mysql --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.6</version> </dependency> <!-- hibernate --> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-core</artifactId> <version>4.1.8.Final</version> </dependency> <!--log--> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-api</artifactId> <version>1.6.6</version> </dependency> <!-- Servlet --> <dependency> <groupId>javax.servlet</groupId> <artifactId>servlet-api</artifactId> <version>2.5</version> <scope>provided</scope> </dependency> <dependency> <groupId>javax.servlet.jsp</groupId> <artifactId>jsp-api</artifactId> <version>2.1</version> <scope>provided</scope> </dependency> <dependency> <groupId>javax.servlet</groupId> <artifactId>jstl</artifactId> <version>1.2</version> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>3.8.1</version> <scope>test</scope> </dependency> </dependencies> <build> <finalName>springmvc</finalName> <plugins> <plugin> <artifactId>maven-compiler-plugin</artifactId> <version>3.1</version> <configuration> <source>1.7</source> <target>1.7</target> </configuration> </plugin> </plugins> </build> </project> |
Room.java: room object, mapping with room table in database
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
package com.demo.springmvc.entity; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.Table; @Entity @Table(name = "ROOM") public class Room { @Id @Column(name = "ID") @GeneratedValue(strategy = GenerationType.AUTO) private int id; @Column(name = "NAME") private String nameRoom; @Column(name = "COUNTROOM") private int countRoom; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getNameRoom() { return nameRoom; } public void setNameRoom(String nameRoom) { this.nameRoom = nameRoom; } public int getCountRoom() { return countRoom; } public void setCountRoom(int countRoom) { this.countRoom = countRoom; } @Override public String toString() { return "id=" + id + ", name=" + nameRoom + ", count=" + countRoom; } } |
- @Entity: syntax to define object is table
- @Table(name = “ROOM”): mapping with name table is ROOM
- @Id: primary key
- @Column(name = “ID”): mapping with name column is ID
- @GeneratedValue(strategy = GenerationType.AUTO): key auto gen in mysql database
RoomDAO.java:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 |
package com.demo.springmvc.dao; import java.util.List; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.hibernate.Session; import org.hibernate.SessionFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Repository; import com.demo.springmvc.entity.Room; @Repository public class RoomDAO { private static final Logger logger = LoggerFactory.getLogger(RoomDAO.class); @Autowired private SessionFactory sessionFactory; @Autowired public void setSessionFactory(SessionFactory sf) { this.sessionFactory = sf; } public void addRoom(Room p) { Session session = this.sessionFactory.getCurrentSession(); session.persist(p); logger.info("Room saved successfully, Room Details=" + p); } public void updateRoom(Room p) { Session session = this.sessionFactory.getCurrentSession(); session.update(p); logger.info("Room updated successfully, Room Details=" + p); } @SuppressWarnings("unchecked") public List<Room> listRooms() { Session session = this.sessionFactory.getCurrentSession(); List<Room> RoomsList = session .createQuery("from Room order by id desc").list(); return RoomsList; } @SuppressWarnings("unchecked") public List<Room> listRoomsCb() { Session session = this.sessionFactory.getCurrentSession(); List<Room> RoomsListCb = session.createQuery( "from Room order by id asc").list(); return RoomsListCb; } public Room getRoomById(int id) { Session session = this.sessionFactory.getCurrentSession(); Room p = (Room) session.load(Room.class, new Integer(id)); logger.info("Room loaded successfully, Room details=" + p); return p; } public void removeRoom(int id) { Session session = this.sessionFactory.getCurrentSession(); Room p = (Room) session.load(Room.class, new Integer(id)); if (null != p) { session.delete(p); } logger.info("Room deleted successfully, Room details=" + p); } } |
- @Repository: define annotate to use room class with variable @Autowired (exa: @Autowired private RoomDAO roomDAO in RoomService.java file)
- @Autowired: link to spring bean together
RoomService:java:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
package com.demo.springmvc.service; import java.util.List; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import com.demo.springmvc.dao.RoomDAO; import com.demo.springmvc.entity.Room; @Service public class RoomService { @Autowired private RoomDAO roomDAO; @Transactional public void addRoom(Room p) { this.roomDAO.addRoom(p); } @Transactional public void updateRoom(Room p) { this.roomDAO.updateRoom(p); } @Transactional public List<Room> listRooms() { return this.roomDAO.listRooms(); } @Transactional public List<Room> listRoomsCb() { return this.roomDAO.listRoomsCb(); } @Transactional public Room getRoomById(int id) { return this.roomDAO.getRoomById(id); } @Transactional public void removeRoom(int id) { this.roomDAO.removeRoom(id); } } |
- @Service: define annotate to checkpoint Service in business class
- @Transactional: add to config file transaction of spring <tx:annotation-driven/>
RoomController.java:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
package com.demo.springmvc.controller; 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.PathVariable; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import com.demo.springmvc.entity.Room; import com.demo.springmvc.service.RoomService; @Controller // @RequestMapping("/") public class RoomController { @Autowired private RoomService roomService; @RequestMapping(value = "/room", method = RequestMethod.GET) public String listRooms(Model model) { model.addAttribute("room", new Room()); model.addAttribute("listRooms", this.roomService.listRooms()); return "room"; } // For add and update Room both @RequestMapping(value = "/room/add", method = RequestMethod.POST) public String addRoom(@ModelAttribute("room") Room p) { if (p.getId() == 0) { // new Room, add it this.roomService.addRoom(p); } else { // existing Room, call update this.roomService.updateRoom(p); } return "redirect:/room"; } @RequestMapping("/room/remove/{id}") public String removeRoom(@PathVariable("id") int id) { this.roomService.removeRoom(id); return "redirect:/room"; } @RequestMapping("/room/edit/{id}") public String editRoom(@PathVariable("id") int id, Model model) { model.addAttribute("room", this.roomService.getRoomById(id)); model.addAttribute("listRooms", this.roomService.listRooms()); return "room"; } } |
You create room.jsp page to mange insert/update/delete room info
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 |
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%> <%@ taglib uri="http://www.springframework.org/tags" prefix="spring"%> <%@ taglib uri="http://www.springframework.org/tags/form" prefix="form"%> <%@ page session="false"%> <c:set var="url" value="${pageContext.request.contextPath}"></c:set> <html> <head> <title>Room Page</title> </head> <body> <div>Add a Room</div> <c:url var="addAction" value="/room/add"></c:url> <form:form action="${addAction}" commandName="room"> <table> <c:if test="${room.id!=0}"> <tr> <td><form:label path="id"> <spring:message text="ID" /> </form:label></td> <td><form:input path="id" readonly="true" size="8" disabled="true" /> <form:hidden path="id" /></td> </tr> </c:if> <tr> <td><form:label path="nameRoom"> <spring:message text="Name" /> </form:label></td> <td><form:input path="nameRoom" /></td> </tr> <tr> <td><form:label path="countRoom"> <spring:message text="countRoom" /> </form:label></td> <td><form:input path="countRoom" /></td> </tr> <tr> <td colspan="2"><c:if test="${room.id!=0}"> <input type="submit" value="<spring:message text="Edit Room"/>" /> </c:if> <c:if test="${room.id==0}"> <input type="submit" value="<spring:message text="Add Room"/>" /> </c:if></td> </tr> </table> </form:form> <br> <h3>Rooms List</h3> <c:if test="${!empty listRooms}"> <table class="tg"> <tr> <th width="80">Room ID</th> <th width="120">Room Name</th> <th width="120">Room Count</th> <th width="60">Edit</th> <th width="60">Delete</th> </tr> <c:forEach items="${listRooms}" var="room"> <tr> <td>${room.id}</td> <td>${room.nameRoom}</td> <td>${room.countRoom}</td> <td><a href="<c:url value='/room/edit/${room.id}' />">Edit</a></td> <td><a href="<c:url value='/room/remove/${room.id}' />">Delete</a></td> </tr> </c:forEach> </table> </c:if> </body> </html> |
In index.jsp, modify to redirect to room page
|
1 2 3 |
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %> <c:redirect url="/room"/> |
Finally, you modify config files to build and run success project
servlet-config-context.xml:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:context="http://www.springframework.org/schema/context" xmlns:mvc="http://www.springframework.org/schema/mvc" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:tx="http://www.springframework.org/schema/tx" xsi:schemaLocation=" http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd"> <!-- Enables the Spring MVC @Controller programming model --> <!-- <context:annotation-config /> --> <mvc:annotation-driven/> <!-- Resolves views selected for rendering by @Controllers to .jsp resources in the /WEB-INF/views directory --> <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver"> <property name="prefix" value="/WEB-INF/pages/" /> <property name="suffix" value=".jsp" /> </bean> <!-- scan all controller files --> <context:component-scan base-package="com.demo.springmvc.controller" /> </beans> |
servlet-config-jdbc.xml: config connect to mysql database
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
<?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:jee="http://www.springframework.org/schema/jee" xmlns:task="http://www.springframework.org/schema/task" xmlns:mvc="http://www.springframework.org/schema/mvc" xmlns:tx="http://www.springframework.org/schema/tx" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd http://www.springframework.org/schema/task http://www.springframework.org/schema/task/spring-task.xsd http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd"> <!-- Root Context: defines shared resources visible to all other web components --> <context:component-scan base-package="com.demo.springmvc.dao; com.demo.springmvc.service"/> <tx:annotation-driven transaction-manager="transactionManager"/> <bean id="transactionManager" class="org.springframework.orm.hibernate4.HibernateTransactionManager"> <property name="sessionFactory" ref="sessionFactory"/> </bean> <bean id="persistenceExceptionTranslationPostProcessor" class="org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor" /> <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName" value="com.mysql.jdbc.Driver" /> <property name="url" value="jdbc:mysql://localhost:3306/springdb" /> <property name="username" value="root" /> <property name="password" value="" /> </bean> <bean id="sessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean"> <property name="dataSource" ref="dataSource"/> <!--scan all class entity to mapping--> <property name="packagesToScan" value="com.demo.springmvc.entity" /> <property name="hibernateProperties"> <props> <prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop> <prop key="hibernate.show_sql">true</prop> <prop key="hibernate.hbm2ddl.auto">create</prop> <!-- <prop key="hibernate.hbm2ddl.auto">create/none/update</prop> create table--> <prop key="hibernate.default_schema"></prop> <prop key="hibernate.format_sql">true</prop> </props> </property> </bean> </beans> |
- username and password: you need modify your information if any
- hibernate.hbm2ddl.auto have 3 value:
- create: auto drop all table and auto create tables into mysql database from all class of Entity package
- update: update add column in database if any change in class Entity
- none: nothing to do
- the first run project, should set value is create to auto generate tables in database. After that set value is update or none to don’t lost data so be drop table.
web.xml
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
<?xml version="1.0" encoding="UTF-8"?> <web-app version="2.5" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"> <display-name>hello_world</display-name> <context-param> <param-name>contextConfigLocation</param-name> <!-- load config file for mysql --> <param-value>/WEB-INF/servlet-config-jdbc.xml</param-value> </context-param> <listener> <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class> </listener> <servlet> <servlet-name>spring-mvc</servlet-name> <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class> <init-param> <param-name>contextConfigLocation</param-name> <!-- load config file for page content --> <param-value>/WEB-INF/servlet-config-context.xml</param-value> </init-param> <load-on-startup>1</load-on-startup> </servlet> <servlet-mapping> <servlet-name>spring-mvc</servlet-name> <url-pattern>/</url-pattern> </servlet-mapping> </web-app> |
Right click on project name and select Build Project.
Publish to ther server and Run with url http://localhost:8080/springmvc
Tags: mysql, part, spring, spring databaseRelated Posts
 How to create quartz spring dynamic in database
|
Spring mvc mongodb crud users roles
|
Java spring mvc tutorial on lvtutorial.com
|
Angularjs spring mvc crud example
|
Related Posts
|
How to create quartz spring dynamic in database
|
Spring mvc mongodb crud users roles
|
|
Java spring mvc tutorial on lvtutorial.com
|
Angularjs spring mvc crud example
|
Tutorial: how to install Jboss server 7.1 with eclipse