Resource download: https://download.csdn.net/download/weixin_44893902/45601768
Exercise point design: fuzzy query, delete, add
1, Language and environment
- Implementation language: JAVA language.
- Environment requirements: MyEclipse/Eclipse + Tomcat + MySql.
- Use technology: Jsp+Servlet+JavaBean or spring MVC + Spring + mybatis.
2, Realize function
Develop the logistics system - company information management module using SSM (Spring + spring MVC + mybatis) framework, and realize the following functions:
1. The home page displays all company information, as shown in Figure 1.
2. Realize the fuzzy query function by company name: enter the company name to be queried in the input box, and click the "search" button to display the relevant company information.
3. The deletion function is realized. A prompt of "delete confirmation" pops up before deletion, and the latest data is displayed after successful deletion. The effect diagram is shown in Figure 3.
4. Realize the function of adding company information: click "add company information" on the home page to jump to the page of adding company information. The page effect is shown in Figure 4. To complete the function, the company number and company name must be entered.
3, Database design
1. Create a database (logisticsDB).
2. Create a company information (tb_company) table. See Table 1 for the structure.
Table 1 company information structure
4, Recommended implementation steps
-
Establish database and data table, and add test data (add at least 5 test data).
-
Open Eclipse or Idea, create a Web project, and create the corresponding package.
-
Add Spring, Spring MVC, and MyBatis support to the project.
-
Create the entity class and the MyBatis mapping file corresponding to the entity in the project, and correctly configure the SQL mapping file of MySQL.
-
Create Mapper mapper, business class. Write SQL statements in the Mapper or mapping file to query and add operations.
-
Create the Controller class and correctly configure the configuration file of spring MVC.
-
Correctly configure Spring's configuration file and effectively manage SQLSessionFactory.
5, Implementation code
1. mysql code
1. Create a database (logisticsDB).
2. Create a company information (tb_company) table.
/* Date: 25/07/2021 22:02:55 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for tb_company -- ---------------------------- DROP TABLE IF EXISTS `tb_company`; CREATE TABLE `tb_company` ( `Company_id` int(11) NOT NULL AUTO_INCREMENT, `Company_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `Company_city` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `Company_phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `Company_fax` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `Company_adress` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `Company_remark` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`Company_id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1006 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of tb_company -- ---------------------------- INSERT INTO `tb_company` VALUES (1001, 'Changsha Feima Media Co., Ltd', 'Xiangtan, Hunan', '0731-2784651', '0731-2784651', 'Wangcheng', NULL); INSERT INTO `tb_company` VALUES (1002, 'Changsha Changben automobile', 'Zhuzhou, Hunan', '0731-2784555', '0731-2784555', 'Chaling', NULL); INSERT INTO `tb_company` VALUES (1003, 'Hunan Huitong Technology Co., Ltd', 'Changsha, Hunan', '0731-2784777', '0731-2784777', 'Changsha', NULL); INSERT INTO `tb_company` VALUES (1004, 'Changsha Science and technology', 'Changsha, Hunan', '0731-2784651', '0731-2784651', 'Xiangtan', NULL); SET FOREIGN_KEY_CHECKS = 1;
2. Project Java code
directory structure
Company
JAR package:
src
com.controller
CompanyController.java
package com.controller;
import java.util.List;
import javax.annotation.Resource;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import com.entity.TbCompany;
import com.service.CompanyService;
@Controller
public class CompanyController {
@Resource
CompanyService companyService;
// Query and fuzzy query
@RequestMapping("/CompanysList")
public String CompanysList(Model model, String Company_name) {
List<TbCompany> companyList = companyService.selectAll(Company_name);
model.addAttribute("companyList", companyList);
return "/company";
}
// Jump to add page
@RequestMapping("/insertInto")
public String insertInto() {
return "/addCompany";
}
// add to
@RequestMapping("/insertCompany")
public String insertCompany(TbCompany tbCompany) {
int insertCompany = companyService.insertCompany(tbCompany);
return "redirect:/CompanysList.do";
}
// delete
@RequestMapping("/delCompany")
public String delCompany(int companyId) {
int delCompany = companyService.delCompany(companyId);
return "redirect:/CompanysList.do";
}
}
com.dao
TbCompanyMapper.java
package com.dao;
import com.entity.TbCompany;
import java.util.List;
import org.apache.ibatis.annotations.Param;
public interface TbCompanyMapper {
int deleteByPrimaryKey(Integer companyId);
int insert(TbCompany record);
List<TbCompany> selectAll(@Param("Company_name")String Company_name);
}
TbCompanyMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.dao.TbCompanyMapper" >
<resultMap id="BaseResultMap" type="com.entity.TbCompany" >
<id column="Company_id" property="companyId" jdbcType="INTEGER" />
<result column="Company_name" property="companyName" jdbcType="VARCHAR" />
<result column="Company_city" property="companyCity" jdbcType="VARCHAR" />
<result column="Company_phone" property="companyPhone" jdbcType="VARCHAR" />
<result column="Company_fax" property="companyFax" jdbcType="VARCHAR" />
<result column="Company_adress" property="companyAdress" jdbcType="VARCHAR" />
<result column="Company_remark" property="companyRemark" jdbcType="VARCHAR" />
</resultMap>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
delete from tb_company
where Company_id = #{companyId,jdbcType=INTEGER}
</delete>
<insert id="insert" parameterType="com.entity.TbCompany" >
insert into tb_company (Company_id, Company_name, Company_city,
Company_phone, Company_fax, Company_adress,
Company_remark)
values (#{companyId,jdbcType=INTEGER}, #{companyName,jdbcType=VARCHAR}, #{companyCity,jdbcType=VARCHAR},
#{companyPhone,jdbcType=VARCHAR}, #{companyFax,jdbcType=VARCHAR}, #{companyAdress,jdbcType=VARCHAR},
#{companyRemark,jdbcType=VARCHAR})
</insert>
<select id="selectAll" resultMap="BaseResultMap" >
select Company_id, Company_name, Company_city, Company_phone, Company_fax, Company_adress,
Company_remark
from tb_company
<where>
<if test="Company_name!= null">Company_name like "%"#{Company_name}"%" </if>
</where>
</select>
</mapper>
com.entity
TbCompany.java
package com.entity;
public class TbCompany {
private Integer companyId;
private String companyName;
private String companyCity;
private String companyPhone;
private String companyFax;
private String companyAdress;
private String companyRemark;
public Integer getCompanyId() {
return companyId;
}
public void setCompanyId(Integer companyId) {
this.companyId = companyId;
}
public String getCompanyName() {
return companyName;
}
public void setCompanyName(String companyName) {
this.companyName = companyName == null ? null : companyName.trim();
}
public String getCompanyCity() {
return companyCity;
}
public void setCompanyCity(String companyCity) {
this.companyCity = companyCity == null ? null : companyCity.trim();
}
public String getCompanyPhone() {
return companyPhone;
}
public void setCompanyPhone(String companyPhone) {
this.companyPhone = companyPhone == null ? null : companyPhone.trim();
}
public String getCompanyFax() {
return companyFax;
}
public void setCompanyFax(String companyFax) {
this.companyFax = companyFax == null ? null : companyFax.trim();
}
public String getCompanyAdress() {
return companyAdress;
}
public void setCompanyAdress(String companyAdress) {
this.companyAdress = companyAdress == null ? null : companyAdress.trim();
}
public String getCompanyRemark() {
return companyRemark;
}
public void setCompanyRemark(String companyRemark) {
this.companyRemark = companyRemark == null ? null : companyRemark.trim();
}
}
com.service
CompanyService.java
package com.service;
import java.util.List;
import com.entity.TbCompany;
public interface CompanyService {
//query
List<TbCompany> selectAll(String Company_name);
//add to
int insertCompany(TbCompany tbCompany);
//delete
int delCompany(int companyId);
}
com.serviceImpl
CompanyServiceImpl.java
package com.serviceImpl;
import java.util.List;
import javax.annotation.Resource;
import org.springframework.stereotype.Service;
import com.dao.TbCompanyMapper;
import com.entity.TbCompany;
import com.service.CompanyService;
@Service
public class CompanyServiceImpl implements CompanyService {
@Resource
TbCompanyMapper mapper;
@Override
public List<TbCompany> selectAll(String Company_name) {
List<TbCompany> selectAll=mapper.selectAll(Company_name);
return selectAll;
}
@Override
public int insertCompany(TbCompany tbCompany) {
int insertCompany=mapper.insert(tbCompany);
return insertCompany;
}
@Override
public int delCompany(int companyId) {
int delCompany=mapper.deleteByPrimaryKey(companyId);
return delCompany;
}
}
mybatis
sqlMapConfig.xml.java
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <!-- alias --> <typeAliases> <package name="com.entity" /> </typeAliases> </configuration>
spring
applicationContext-dao.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://www.springframework.org/schema/beans"
xmlns:p="http://www.springframework.org/schema/p"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-4.2.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-4.2.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-4.2.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-4.2.xsd ">
<!-- appoint spring Container read db.properties file -->
<context:property-placeholder location="classpath:jdbc.properties"></context:property-placeholder>
<!-- Register connection pool with bean In container -->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
<property name="driverClassName" value="${jdbc.driver}"></property>
<property name="Url" value="${jdbc.url}"></property>
<property name="username" value="${jdbc.username}"></property>
<property name="password" value="${jdbc.password}"></property>
</bean>
<!-- to configure SqlSessionFactory -->
<bean class="org.mybatis.spring.SqlSessionFactoryBean">
<!-- set up MyBatis Core profile -->
<property name="configLocation" value="classpath:mybatis/sqlMapConfig.xml" />
<!-- set up data sources -->
<property name="dataSource" ref="dataSource" />
</bean>
<!-- to configure Mapper scanning -->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<!-- set up Mapper Scan package -->
<property name="basePackage" value="com.dao" />
</bean>
<!-- Configure transaction manager -->
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"></property>
</bean>
<!-- Enable annotation mode management AOP affair -->
<tx:annotation-driven transaction-manager="transactionManager" />
</beans>
applicationContext-service.xml
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.springframework.org/schema/beans" xmlns:context="http://www.springframework.org/schema/context" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:mvc="http://www.springframework.org/schema/mvc" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.2.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.2.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.2.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.2.xsd http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-4.2.xsd "> <!-- to configure Service scanning --> <context:component-scan base-package="com" /> </beans>
spring-mvc.xml
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.springframework.org/schema/beans" xmlns:context="http://www.springframework.org/schema/context" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:mvc="http://www.springframework.org/schema/mvc" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.2.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.2.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.2.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.2.xsd http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-4.2.xsd "> <!-- to configure Controller scanning --> <context:component-scan base-package="com.controller" /> <!-- Configure annotation driven --> <mvc:annotation-driven /> <!-- Configure view parser --> <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver"> <!-- prefix --> <property name="prefix" value="/WEB-INF/jsp/" /> <!-- suffix --> <property name="suffix" value=".jsp" /> </bean> </beans>
jdbc.properties
jdbc.url=jdbc:mysql://localhost:3306/logisticsdb?useUnicode=true&characterEncoding=UTF-8&useSSL=false jdbc.username=root jdbc.password=123456 jdbc.driver=com.mysql.jdbc.Driver
WebContent
web.xml
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.jcp.org/xml/ns/javaee" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd" id="WebApp_ID" version="3.1">
<display-name>Company</display-name>
<welcome-file-list>
<welcome-file>index.html</welcome-file>
<welcome-file>index.htm</welcome-file>
<welcome-file>index.jsp</welcome-file>
<welcome-file>default.html</welcome-file>
<welcome-file>default.htm</welcome-file>
<welcome-file>default.jsp</welcome-file>
</welcome-file-list>
<!--spring container -->
<context-param>
<param-name>contextConfigLocation</param-name>
<param-value>classpath:spring/applicationContext-*.xml</param-value>
</context-param>
<!-- Listener, loading spring to configure -->
<listener>
<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
</listener>
<!-- Front end controller -->
<servlet>
<servlet-name>springmvc</servlet-name>
<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
<init-param>
<param-name>contextConfigLocation</param-name>
<param-value>classpath:spring/spring-mvc.xml</param-value>
</init-param>
<load-on-startup>1</load-on-startup>
</servlet>
<servlet-mapping>
<servlet-name>springmvc</servlet-name>
<url-pattern>*.do</url-pattern>
</servlet-mapping>
<!-- set up post Requested character encoding filter -->
<filter>
<filter-name>CharacterEncodingFilter</filter-name>
<filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
<init-param>
<param-name>encoding</param-name>
<param-value>UTF-8</param-value>
</init-param>
</filter>
<filter-mapping>
<filter-name>CharacterEncodingFilter</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
</web-app>
JSP
index.jsp
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<!DOCTYPE html>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"
+request.getServerPort()+path;
%>
<html>
<head>
<meta charset="utf-8">
<title>Sign in</title>
</head>
<body>
<script type="text/javascript">
window.location.href="<%=basePath%>/CompanysList.do";
</script>
</body>
</html>
addCompany.jsp
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Add company information</title>
<style type="text/css">
.centen1 {
width: 100%;
height: 40px;
color: white;
line-height: 40px;
background-color: #0098CB;
}
table {
margin: auto;
}
span {
color: red;
}
</style>
</head>
<body>
<div class="centen1">
<h3>Add company</h3>
</div>
<div class="centen2">
<form action="insertCompany.do">
<table border="0" cellspacing="10" cellpadding="6">
<tr>
<td>Company number: <input type="text"
name="companyId" required> <span>*</span>
</td>
</tr>
<tr>
<td>corporate name: <input type="text"
name="companyName" required> <span>*</span>
</td>
</tr>
<tr>
<td>City: <input type="text"
name="companyCity">
</td>
</tr>
<tr>
<td>contact number: <input type="text"
name="companyPhone">
</td>
</tr>
<tr>
<td>pass
really: <input
type="text" name="companyFax">
</td>
</tr>
<tr>
<td>land
site: <input
type="text" name="companyAdress">
</td>
</tr>
<tr>
<td>prepare
notes: <input
type="text" name="companyRemark">
</td>
</tr>
<tr>
<td>
<input
type="submit" value="preservation" /> <input type="reset" value="Reset" />
</td>
</tr>
</table>
</form>
</div>
</body>
</html>
company.jsp
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<!DOCTYPE html>
<html>
<head>
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<meta charset="utf-8">
<title>logistics system - Company information management</title>
<style type="text/css">
.head {
height: 40px;
width: 100%;
background-color: #F8F4FF;
}
.head1 {
width: 250px;
height: 100%;
float: left;
}
.head2 {
width: 450px;
height: 100%;
line-height: 36px;
float: right;
}
.centen {
color: white;
font-size: 20px;
width: 100%;
height: 36px;
background-color: #0098CB;
margin-top: 20px;
line-height: 36px;
}
.foot {
margin-top: 20px;
}
table {
text-align: center;
}
</style>
</head>
<body>
<div class="head">
<div class="head1">
<a href="insertInto.do">
<button type="button"
style="height: 36px; font-size: 20px; background-color: #D9F0FF; border: none; "> add company information < / button >
</a>
</div>
<div class="head2" style="font-size: 20px; text-align: center;">
<form action="CompanysList.do" method="post">
corporate name: <input type="text" placeholder="corporate name" name="Company_name"
style="height: 26px; font-size: 15px; margin-bottom: 2px;" /> <input
type="submit" value="lookup" style="height: 30px; font-size: 15px;" />
</form>
</div>
</div>
<div class="centen">
<h4>Company information list</h4>
</div>
<div class="foot">
<table width="100%" border="1px" cellpadding="5" cellspacing="0">
<tr>
<th width="150px">corporate name</th>
<th width="150px">city</th>
<th width="150px">Telephone</th>
<th width="150px">Fax</th>
<th width="150px">address</th>
<th width="150px">operation</th>
</tr>
<c:forEach var="company" items="${companyList }" varStatus="item">
<tr>
<td width="150px">${company.companyName}</td>
<td width="150px">${company.companyCity}</td>
<td width="150px">${company.companyPhone}</td>
<td width="150px">${company.companyFax}</td>
<td width="150px">${company.companyAdress}</td>
<td width="150px"><a
href="javascript:if(confirm('Are you sure you want to delete?'))location='delCompany.do?companyId=${company.companyId}'">delete</a>
</td>
</tr>
</c:forEach>
</table>
</div>
</body>
</html>