超简单的Spring Cloud+Mybatis+jsp集成

刺骨的言语ヽ痛彻心扉 2022-04-23 07:02 191阅读 0赞

Spring Cloud项目默认是不支持jsp文件的，我们要想在项目中用到jsp，就需要增加相关依赖。本文中将进述通过两种形式来操作数据库(一种是通过注解，二是通过配置文件)来实现简单的增删改, 本项目使用的是JDK 1.8    spring boot版本是2.2.1。

**一、通过注解操作数据库：**

1、首先一个创建spring boot web工程(选择war) 如图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xvb3Bsb29rMjE_size_16_color_FFFFFF_t_70][]

增加如下依赖：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xvb3Bsb29rMjE_size_16_color_FFFFFF_t_70 1][]

搭建完成后再增加jsp的相关依赖 如下:

<dependencies>
    		
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-web</artifactId>
    		</dependency>
    
    		
    		<dependency>
    			<groupId>org.mybatis.spring.boot</groupId>
    			<artifactId>mybatis-spring-boot-starter</artifactId>
    			<version>2.0.0</version>
    		</dependency>
    
    		<dependency>
    			<groupId>mysql</groupId>
    			<artifactId>mysql-connector-java</artifactId>
    			<scope>runtime</scope>
    		</dependency>
    		
    
    		
    		<dependency>
    			<groupId>org.apache.tomcat.embed</groupId>
    			<artifactId>tomcat-embed-jasper</artifactId>
    		</dependency>
    
    		<dependency>
    			<groupId>javax.servlet</groupId>
    			<artifactId>javax.servlet-api</artifactId>
    		</dependency>
    		
    		<dependency>
    			<groupId>javax.servlet</groupId>
    			<artifactId>jstl</artifactId>
    		</dependency>
    		
    		<dependency>      
    			<groupId>org.springframework.boot</groupId>      
    			<artifactId>spring-boot-devtools</artifactId>   
    		</dependency>
    		
    
    		
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-tomcat</artifactId>
    			<scope>provided</scope>
    		</dependency>
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-test</artifactId>
    			<scope>test</scope>
    		</dependency>
    		
    	</dependencies>

以上工程的全部依赖都配置好了。

2、配置mysqldatasources在application.properties文件中:

server.port=8080
    
    
    #jsp前后缀
    spring.mvc.view.prefix=/WEB-INF/jsp/
    spring.mvc.view.suffix=.jsp
    
    #datascoure配置
    spring.datasource.driverClassName =com.mysql.cj.jdbc.Driver
    spring.datasource.url =jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
    spring.datasource.username =root
    spring.datasource.password =root
    
    #mapeer文件位置
    mybatis.mapper-locations=classpath:/mapper/UserDao.xml

mvc:配置的就是视图的前缀和后缀

spring.datasource.driverClassName使用了最新的驱动名称:com.mysql.cj.jdbc.Driver。

url后面需要加上serverTimezone=UTC，mysql会自动提供给我们数据空间。

3、接一来创建pojo，mapper等对象，创建如下表结构:

CREATE TABLE \`users\` (  
  \`id\` int(11) NOT NULL AUTO\_INCREMENT,  
  \`name\` varchar(10) NOT NULL,  
  \`age\` int(11) NOT NULL,  
  PRIMARY KEY (\`id\`)  
) ENGINE=InnoDB AUTO\_INCREMENT=10 DEFAULT CHARSET=utf8;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xvb3Bsb29rMjE_size_16_color_FFFFFF_t_70 2][]

创建工程目录如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xvb3Bsb29rMjE_size_16_color_FFFFFF_t_70 3][]

4、pojo对象:

public class User implements Serializable{
    
    	private Integer id;
    	private String  name;
    	private Integer  age;
    	
    	public User(Integer id, String name, Integer age) {
    		super();
    		this.id = id;
    		this.name = name;
    		this.age = age;
    	}
    	
    	public User(String name, Integer age) {
    		super();
    		this.name = name;
    		this.age = age;
    	}
    
    
    
    	public Integer getId() {
    		return id;
    	}
    	public void setId(Integer id) {
    		this.id = id;
    	}
    	public String getName() {
    		return name;
    	}
    	public void setName(String name) {
    		this.name = name;
    	}
    	public Integer getAge() {
    		return age;
    	}
    	public void setAge(Integer age) {
    		this.age = age;
    	}
    
    	@Override
    	public String toString() {
    		return "User [id=" + id + ", name=" + name + ", age=" + age + "]";
    	}
    }

5、Mapeer对象:

@Mapper
    public interface UserMapper {
    
    	@Select("select * from users")
        @Results({
            @Result(property = "id",  column = "id", jdbcType=JdbcType.INTEGER),
            @Result(property = "name",  column = "name", jdbcType=JdbcType.VARCHAR),
            @Result(property = "age",  column = "age", jdbcType=JdbcType.INTEGER)
        })
    	List<User> getAll();
    	
    	
    	@Select("select * from users where id=#{id}")
        @Results({
            @Result(property = "id",  column = "id", jdbcType=JdbcType.INTEGER),
            @Result(property = "name",  column = "name", jdbcType=JdbcType.VARCHAR),
            @Result(property = "age",  column = "age", jdbcType=JdbcType.INTEGER)
        })
    	User getOne(Integer id);
    	
    	
    	//@Insert("INSERT INTO users(id,name,age) VALUES(#{id},#{name}, #{age})")
    	@Insert("INSERT INTO users(name,age) VALUES(#{name}, #{age})")
    	void insert(User user);
    	
    	@Update("update users set name=#{name},age=#{age} where id=#{id}")
    	void update(User user);
    	
    	@Delete("delete from users where id=#{id}")
    	void delete(Integer id);
    }

6、增加control控制器:

//通过注解操作数据库
    @Controller
    public class UserControl {
    	
    	private List<User> list;
    	
    	@Autowired
    	UserMapper userMapper;
    
    	@RequestMapping("/add")
    	public String welcome() {
    		/*userMapper.insert(new User(1,"aa", 12));
    		userMapper.insert(new User(2,"bb", 13));
    		userMapper.insert(new User(3,"cc", 34));*/
    		
    		userMapper.insert(new User("aa", 12));
    		userMapper.insert(new User("bb", 13));
    		userMapper.insert(new User("cc", 34));
    		
    		return "index";
    	}
    	
    	@RequestMapping("/getusers")
    	public String getusers(Model model) {
    		list=userMapper.getAll();
    		System.out.println(list.toString());
    		model.addAttribute("userlist", list);
    		
    		return "index";
    	}
    	
    	@RequestMapping("/del/{id}")
    	public String del(@PathVariable Integer id) {
    		userMapper.delete(id);
    		
    		return "index";
    	}
    	
    	@RequestMapping("/del/{id}/{name}")
    	public String update(@PathVariable Integer id,@PathVariable String name) {
    		User user=userMapper.getOne(id);
    		user.setName(name);
    		userMapper.update(user);
    		
    		return "index";
    	}
    }

7、将结果再通过jsp展示出来，以上将数据放到了model对象，将对象模型存入到key中jsp再取出:

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
    <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
    <!DOCTYPE html>
    <html>
    <head>
    <meta charset="ISO-8859-1">
    <title>Insert title here</title>
    </head>
    <body style="text-align: center;">
    
    <table border="1" style="width: 300px;height: 100px;margin: auto;">
    	<tr>
    		<th>学号</th>
    		<th>姓名</th>
    		<th>年龄</th>
    	</tr>
    	<c:forEach items="${userlist}" var="list">
    	<tr style="text-align: center;">
    		<td>${list.id}</td>
    		<td>${list.name}</td>
    		<td>${list.age}</td>
    	</tr>
    	</c:forEach>
    </table>
    </body>
    </html>

项目运行再后面内容。

**二、通过Xml文件操作数据库：**

1、因为使用注解不需要services和dao层，但是xml这种需要，为了满足MVC模型，增加services包下增加:

@Service
    public class UserServices {
    	
    	@Autowired
    	UserDao dao;
    	
    	public List<User> getAll(){
    		return dao.getAll();
    	}
    	
    	public User getOne(Integer id) {
    		return dao.getOne(id);
    	}
    	
    	public void insert(User user) {
    		dao.insert(user);
    	}
    	
    	public void delete(Integer id) {
    		dao.delete(id);
    	}
    	
    	public void update(User user) {
    		dao.update(user);
    	}
    
    }

2、在dao层增加如下:

@Mapper
    public interface UserDao {
    
        List<User> getAll();
        
        User getOne(Integer id);
        
        void insert(User user);
        
        void update(User user);
        
        void delete(Integer id);
    }

接下就是创建UserDao.xml文件 放在src/main/resources中，放其它路径也可以，但需要配置下路径，可以参考其它文档。在resources创建文件需要在build path创建之后再更换名称就可以了，如图:

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xvb3Bsb29rMjE_size_16_color_FFFFFF_t_70 4][]

4、增加userDao.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.example.demo.dao.UserDao" >
        <resultMap id="BaseResultMap" type="com.example.demo.pojo.User" >
            <id column="id" property="id" jdbcType="INTEGER" />
            <result column="name" property="name" jdbcType="VARCHAR" />
            <result column="age" property="age" jdbcType="INTEGER" />
        </resultMap>
    
        <sql id="Base_Column_List" > 
            id, name, age
        </sql>
    
        <select id="getAll" resultMap="BaseResultMap"  >
           SELECT 
           <include refid="Base_Column_List" />
           FROM users
        </select>
    
        <select id="getOne" parameterType="java.lang.Integer" resultMap="BaseResultMap" >
            SELECT 
           <include refid="Base_Column_List" />
           FROM users
           WHERE id = #{id}
        </select>
    
        <insert id="insert" parameterType="com.example.demo.pojo.User" >
           INSERT INTO 
                users
                (id,name,age) 
            VALUES
                (#{id}, #{name}, #{age})
        </insert>
    
        <update id="update" parameterType="com.example.demo.pojo.User" >
           UPDATE 
                users 
           SET 
            <if test="name != null">name = #{name},</if>
            <if test="age != null">age = #{age},</if>
           WHERE 
                id = #{id}
        </update>
    
        <delete id="delete" parameterType="java.lang.Integer" >
           DELETE FROM
                 users 
           WHERE 
                 id =#{id}
        </delete>
    </mapper>

前面在application.properties中指定了xml的位置，所以可以扫描到文件 如下:

\#mapeer文件位置  
mybatis.mapper-locations=classpath:/mapper/UserDao.xml

5、在control中增加一个控制类UserXmlControl:

//通过xml文件配置操作数据库
    @Controller
    public class UserXmlControl {
    	
    	private List<User> list;
    	
    	@Autowired
    	UserServices services;
    
    	@RequestMapping("/addXml")
    	public String welcome() {
    		/*services.insert(new User(1,"aa", 12));
    		services.insert(new User(2,"bb", 13));
    		services.insert(new User(3,"cc", 34));*/
    		
    		services.insert(new User("aa", 12));
    		services.insert(new User("bb", 13));
    		services.insert(new User("cc", 34));
    		
    		return "index";
    	}
    	
    	@RequestMapping("/getusersXml")
    	public String getusers(Model model) {
    		list=services.getAll();
    		System.out.println(list.toString());
    		model.addAttribute("userlist", list);
    		
    		return "index";
    	}
    	
    	@RequestMapping("/delXml/{id}")
    	public String del(@PathVariable Integer id) {
    		services.delete(id);
    		
    		return "index";
    	}
    	
    	@RequestMapping("/delXml/{id}/{name}")
    	public String update(@PathVariable Integer id,@PathVariable String name) {
    		User user=services.getOne(id);
    		user.setName(name);
    		services.update(user);
    		
    		return "index";
    	}
    }

jsp已经写过了，运行项目 ->将入项目放到tomcat当中   因为是war工程需要在url增加项目名：

首先运行:[http://localhost:8080/spring-cloud-mybatis-jsp/add][http_localhost_8080_spring-cloud-mybatis-jsp_add]  做增加数据操作，

再运行查看:[http://localhost:8080/spring-cloud-mybatis-jsp/getusers][http_localhost_8080_spring-cloud-mybatis-jsp_getusers]![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xvb3Bsb29rMjE_size_16_color_FFFFFF_t_70 5][]

这种是通过注解来操作数据库。

接下来通过xml来操作;

访问:[http://localhost:8080/spring-cloud-mybatis-jsp/addXml][http_localhost_8080_spring-cloud-mybatis-jsp_addXml] 来增加数据

读取数据:[http://localhost:8080/spring-cloud-mybatis-jsp/getusersXml][http_localhost_8080_spring-cloud-mybatis-jsp_getusersXml]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xvb3Bsb29rMjE_size_16_color_FFFFFF_t_70 6][]

数据成功增加以上就是通过注解和XML两种形式来操作数据库。

**项目思考:**

1、可以看到工程为什么都是基于com.example.demo包下，如果需要自定义包就需要进行加载，可以参考

[微服务架构Spring Boot运行原理][Spring Boot] 这篇文章。

2、UserDao中的方法是与UserDao.xml中的id 一一对应的，其中insert，update，delete都是不需要返回所以都是void类型，@Mapper注解会将UserDao转换为一个xml的数据对象。

@Mapper  
public interface UserDao \{

List<User> getAll();  
      
    User getOne(Integer id);  
      
    void insert(User user);  
      
    void update(User user);  
      
    void delete(Integer id);  
\}

3、将项目放入tomcat中如果提示版本过高需要在spring-cloud-mybatis-jsp工程目录下\\.settings\\org.eclipse.wst.common.project.facet.core.xml修改,将<installed facet="jst.web" version="3.0"/> 修改成向下一级<installed facet="jst.web" version="2.0"/> 就可以了

<?xml version="1.0" encoding="UTF-8"?>  
<faceted-project>  
  <fixed facet="wst.jsdt.web"/>  
  <installed facet="java" version="1.8"/>  
  <installed facet="jst.web" version="3.0"/>  
  <installed facet="wst.jsdt.web" version="1.0"/>  
</faceted-project>

4、项目中如果提示这样的一些错误：

org.apache.ibatis.binding.BindingException: Invalid bound statement (not found): com.example.demo.dao.UserDao.getAll

可能就是你的文件或者类没有扫描，可以用我的项目代码参考。

代码我放在git上面地址为: [https://github.com/lookloop21/Spring-Boot-Rersource][https_github.com_lookloop21_Spring-Boot-Rersource]  名为：spring-cloud-mybatis-jsp

项目过程中有什么问题可以在下方留言。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xvb3Bsb29rMjE_size_16_color_FFFFFF_t_70]: /images/20220323/88daabc09eb64d9ba5bad32ae28aa4d2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xvb3Bsb29rMjE_size_16_color_FFFFFF_t_70 1]: /images/20220323/e4b4a9590db2442d8d384ecc390c3176.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xvb3Bsb29rMjE_size_16_color_FFFFFF_t_70 2]: /images/20220323/28878b1d7cd242f2bc413edd8a20c54e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xvb3Bsb29rMjE_size_16_color_FFFFFF_t_70 3]: /images/20220323/0734feae34e54857968de9eb1c32a6e8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xvb3Bsb29rMjE_size_16_color_FFFFFF_t_70 4]: /images/20220323/ebb45a56ebe24e92a1076bb519b35252.png
[http_localhost_8080_spring-cloud-mybatis-jsp_add]: http://localhost:8080/spring-cloud-mybatis-jsp/add
[http_localhost_8080_spring-cloud-mybatis-jsp_getusers]: http://localhost:8080/spring-cloud-mybatis-jsp/getusers
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xvb3Bsb29rMjE_size_16_color_FFFFFF_t_70 5]: /images/20220323/63b48d12cbae479f8db3868d62db5fd2.png
[http_localhost_8080_spring-cloud-mybatis-jsp_addXml]: http://localhost:8080/spring-cloud-mybatis-jsp/addXml
[http_localhost_8080_spring-cloud-mybatis-jsp_getusersXml]: http://localhost:8080/spring-cloud-mybatis-jsp/getusersXml
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xvb3Bsb29rMjE_size_16_color_FFFFFF_t_70 6]: /images/20220323/ae05ab6e91594f9188d9749cc2034275.png
[Spring Boot]: https://mp.csdn.net/postedit/86158772
[https_github.com_lookloop21_Spring-Boot-Rersource]: https://github.com/lookloop21/Spring-Boot-Rersource