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,操作更方便哦