spring boot在项目中同时使用 jpa 与MyBatis操作数据库

spring boot mvc 基本配置和mybatis配置与操作请参考前面的博客。

小型项目可能jpa使用更便捷，在大型项目中更多会考虑安全和性能要求，在这方面半自动的mybatis则更具有优势。

本教程提供一个简单的spring boot数据库操作案例，包括了jpa和mybatis的整合，但本文主要给了jpa的数据库操作，而在示例代码中提供了jpa和mybatis的共同配置和使用，更详细的内容请参考官方文档（注意，由于spring boot版本之间差别，应该根据项目配置查看相应的帮助文档）：

https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#reference

项目结构如下：

 

1、在pom.xml添加依赖

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

  application.properties文件内容（有jpa配置和mybatis配置） 

spring.
   thymeleaf:
    prefix: "classpath:/templates/"
    suffix: ".html"
    
spring.datasource.url=jdbc:mysql://localhost:3306/test?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
# 
# Hikari will use the above plus the following to setup connection pooling
spring.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.hikari.minimum-idle=10
spring.datasource.hikari.maximum-pool-size=25
spring.datasource.hikari.auto-commit=true
spring.datasource.hikari.idle-timeout=3000
spring.datasource.hikari.pool-name=DatebookHikariCP
spring.datasource.hikari.max-lifetime=200000
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.connection-test-query=SELECT 1

mybatis.typeAliasesPackage=com.example.demo.entity
mybatis.mapper-locations=classpath:/mapper/*Mapper.xml

#jpa configuration
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true

 

2、编写实体类（注意包的位置）

 

package com.example.demo.entity;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="product")
public class Product {
	@Id
    @GeneratedValue
    private Long id;

    @Column(nullable = false)
    private String productName;
    
	@Column(nullable = true)
    private String location;

//    @Column(nullable = false, unique = true)
	@Column(nullable = false)
    private String email;

    public Long getId() {
		return id;
	}
	public void setId(Long id) {
		this.id = id;
	}
	public String getProductName() {
		return productName;
	}
	public void setProductName(String productName) {
		this.productName = productName;
	}
	public String getLocation() {
		return location;
	}
	public void setLocation(String location) {
		this.location = location;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
    
    public Product(String name,String location,String email){
    	this.productName=name;
    	this.location=location;
    	this.email=email;    	
    	
    }
    public Product(){}
}

注解一目了然，不多说

3、编写Dao类

package com.example.demo.dao;

import java.util.List;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import com.example.demo.entity.Product;

public interface ProductRepository extends CrudRepository<Product,Long>{
	List <Product> findByProductName(String name);
	List <Product> findByProductNameContaining(String name);
	//原生态ssql查询
	@Query(value="select * from product", nativeQuery=true)
	List <Product> MyQueryAll();

	//以下是jpa提供的对象方式查询
	@Query("select p from Product p")
	List <Product> MyQueryAll2();
	
	@Query("select p from Product p where p.productName like %:name%")
	List <Product> MyQueryAll3(@Param("name") String name);	
	
	@Query("select p.productName from Product p where p.productName like %:name%")
	List <String> MyQueryAll4(@Param("name") String name);
}

上面代码中提供了原生态的sql查询@Query(value="select * from product", nativeQuery=true) 和若干jpa对象查询，注意他们之间的区别。代码比较简单不展开阐述。注意：@Param一定要引用org.springframework.data.repository.query.Param的，不用引用了mybatis的org.apache.ibatis.annotations.Param，否则要错误

4、编写Controller类

package com.example.demo.controllers;

import java.util.List;
import javax.annotation.Resource;
import javax.servlet.http.HttpSession;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import com.example.demo.dao.ProductRepository;
import com.example.demo.dao.UserDao;
import com.example.demo.dao.UserMapper;
import com.example.demo.entity.Product;
import com.example.demo.entity.User;
import com.example.demo.services.SearchService;

@Controller
public class HelloController {
	@Autowired
	SearchService sv;
	@Autowired
	UserMapper us;
	@Autowired
	UserDao userDao;
	@Autowired
	ProductRepository jpa;
	
    //以下是mybatis访问的数据库
	@RequestMapping("/index")
	public String hello(Model m, HttpSession session) {
		m.addAttribute("person", "张三");
		System.out.println(sv.getinformation("12345"));
		List<User> userlist = us.queryAllUser();
		m.addAttribute("userlist", userlist);
		User sUser = new User();
		sUser.setName("Admin");
		sUser.setAge(30);
		session.setAttribute("sUser", sUser);

		User us = userDao.findById(6);
		System.out.println("通过注解方式获得的User信息如下：");
		System.out.println(us.getName());
		return "index";
	}
    //以下是用jpa访问的数据库
	@RequestMapping("/testjpa")
	// @ResponseBody
	public String hello2(Model m) {
		Product pd = new Product();
		pd.setLocation("厦门");
		pd.setEmail("test@163.com");
		pd.setProductName("显卡");
		jpa.save(pd);

		List<Product> pdlist = jpa.findByProductNameContaining("鼠标");
		m.addAttribute("pdlist", pdlist);

		List<Product> all = jpa.MyQueryAll();
		for (Product p : all)
			System.out.println("产品名称:" + p.getProductName() + "  产地是:" + p.getLocation() + "\n");

		all = jpa.MyQueryAll2();
		System.out.println("second query");
		for (Product p : all)
			System.out.println("产品名称:" + p.getProductName() + "  产地是:" + p.getLocation() + "\n");

		all = jpa.MyQueryAll3("鼠标");
		System.out.println("third query");
		for (Product p : all)
			System.out.println("产品名称:" + p.getProductName() + "  产地是:" + p.getLocation() + "\n");

		List<String> strs = jpa.MyQueryAll4("鼠标");
		System.out.println("fourth query");
		for (String p : strs)
			System.out.println("产品名称:" + p + "\n");

		return "result";

	}

}

5、增加一个Controller对重数据库中的结果，用java的stream进行二次查询操作。

package com.example.demo.controllers;

import java.util.Comparator;
import java.util.List;
import java.util.stream.Collectors;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.example.demo.dao.ProductRepository;
import com.example.demo.entity.Product;
@RestController
public class SteamController {
	@Autowired
	ProductRepository jpa;

	@RequestMapping("/stream")
	public String handler() {
		List<Product> plist = jpa.MyQueryAll();
		// 筛选
		List<Product> list2 = plist.stream().filter(a -> a.getLocation().equals("厦门")).collect(Collectors.toList());
		String str = "";
		for (Product p : list2) {
			str += "产品名称:" + p.getProductName() + "  产地是:" + p.getLocation() + "<br/>";
			System.out.println("产品名称:" + p.getProductName() + "  产地是:" + p.getLocation() + "\n");
		}

		// 排序
		list2 = plist.stream().sorted(Comparator.comparing(Product::getProductName).reversed())
				.collect(Collectors.toList());
		str += "<br/><br/>排序后的输出：<br/><br/>";
		System.out.println("排序后的输出：");
		for (Product p : list2) {
			str += "产品名称:" + p.getProductName() + "  产地是:" + p.getLocation() + "<br/>";
			System.out.println("产品名称:" + p.getProductName() + "  产地是:" + p.getLocation() + "\n");
		}
		// 求和
		Long sum = plist.stream().map(Product::getId).reduce(0L, (a, b) -> a + b);
		System.out.println(sum);

		sum = plist.stream().map(Product::getId).reduce(0L, Long::sum);
		System.out.println(sum);

		return str;
	}
}

 6、页面(thymeleaf模板)

result.html页面 ，用以显示jpa查询结果

<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org"
xmlns="http://www.w3.org/1999/xhtml"
xmlns:layout="http://www.ultraq.net.nz/web/thymeleaf/layout"
>
<head>
<meta charset="UTF-8">
<title></title>
</head>
<body>
<div>
<table border="1"  cellspacing="0">
    <tr>
        <th>产品名称</th>
        <th>产品源地</th>   
    </tr>
    <tr  th:each="pd : ${pdlist}" >
        <td th:text="${pd.productName}"></td>
        <td th:text="${pd.location}"></td>
 
    </tr>
</table>
<br/>
  
</div>
</body>
</html>

 index.html页面 ，用以显示mybatis查询结果

<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org"
xmlns="http://www.w3.org/1999/xhtml"
xmlns:layout="http://www.ultraq.net.nz/web/thymeleaf/layout"
>
<head>
<meta charset="UTF-8">
<title></title>
</head>
<body>
 
<table border="1"  cellspacing="0">
    <tr>
        <th>姓名</th>
        <th>年龄</th>   
    </tr>
    <tr  th:each="user : ${userlist}" >
        <td th:text="${user.name}"></td>
        <td th:text="${user.age}"></td>
 
    </tr>
</table>
<br/>
 
<span>th:text不能够写出 th: text 中间不能用空格</span>
<div th:object="${session.sUser}">
<p>姓名: <span th:text="*{name}" >Sebastian</span>. </p> 
<p>年龄: <span th:text="*{age}" >Pepper</span>. </p>
</div>
<!-- <p>姓名: <span th:text="${session.sUser.name}" ></span>. </p> -->
</div>
</body>
</html>

7、浏览器输出结果 

（1）jpa输出结果

 

（2）mybatis输出结果 

（3）jpa在控制台输出结果 

 （4）在页面stream查询操作输出

示例代码下载：https://pan.baidu.com/s/1xw38WFVRAvzd5kCnR9jBAA 提取码: pjhw 复制这段内容后打开百度网盘手机App，操作更方便哦