目录
3.在pojo包中添加personIdcard和Idcard实体类
1.Dao层:新建IdcardMapper.java和IdcardMapper.xml
2.Dao层:在PersonIdcardMapper.java和PersonIdcardMapper.xml中分别添加
(1)在IdcardMapper.java和IdcardMapper.xml中分别添加
(2)在PersonIdcardMapper.java和PersonIdcardMapper.xml中分别添加
3.修改前端代码 personidcardlist.html thymeleaf
(1)在IdcardMapper.java和IdcardMapper.xml中分别添加
(2)在PersonIdcardMapper.java和PersonIdcardMapper.xml中分别添加
(1)在IdcardMapper.java和IdcardMapper.xml中分别添加
(2)在PersonIdcardMapper.java和PersonIdcardMapper.xml中分别添加
一、一对一查询
最终展现效果:将数据库中class表中的id、age、name和idcard中的code以表格的形式展现。
基于springboot学习日记(六)
1.在数据库中创建一个新的表idcard,并设置其字段
id要设置为主键,要自动递增(逐渐一般都要设置自动递增)
code设置为varchar类型,因为这是用来存身份证号码,身份证号码比较长,用int容易溢出
点击保存:
添加数据
2.在旧表class中添加字段idcardid并填入数据
不设外键 增加一个字段来让两个表对应 降低耦合度
这里的class表中的idcardid和idcard中的id一一对应,删除其中任意一个表都不会影响到另外一个
数据库部分完成
总框架:
3.在pojo包中添加personIdcard和Idcard实体类
4.设置Mapper
(1)dao.PersonIdcardMapper
package com.lbcjxx.springboot.dao;
import com.lbcjxx.springboot.pojo.PersonIdcard;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
//mybatis的实现写到配置文件当中
//多表查询 1-1 1-多 多-多 增删改(两张表) 查
//1-1 查询 person表 idcard(身份证)
//耦合度降低 不设外键 增加一个字段
//查出来的的字段是组合起来的 需要解析
@Mapper
public interface PersonIdcardMapper {
//查询 所有的person表数据库
public List<PersonIdcard> findAllPerson();
}
(2)mapper.PersonIdcardMapper
<?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.lbcjxx.springboot.dao.PersonIdcardMapper">
<!-- public List<Person> findAllPerson();-->
<!-- id 是方法名字 resultType返回值的类型-->
<!-- //查出来的的字段是组合起来的 需要解析-->
<select id="findAllPerson" resultMap="personResult" >
select class.*,idcard.code
from class,idcard
where class.idcardid=idcard.id
</select>
<!--配置映射关系-->
<resultMap id="personResult" type="com.lbcjxx.springboot.pojo.PersonIdcard">
<!-- 主键用id来配-->
<id property="id" column="id"></id>
<result property="age" column="age"></result>
<result property="name" column="name"></result>
<association property="idcard" javaType="com.lbcjxx.springboot.pojo.Idcard">
<result property="id" column="idcardid"></result>
<result property="code" column="code"></result>
</association>
</resultMap>
</mapper>
5.设置service层
package com.lbcjxx.springboot.service;
import com.lbcjxx.springboot.pojo.PersonIdcard;
import java.util.List;
public interface PersonIdcardService {
//方法名字和mapper层保持一致
public List<PersonIdcard> findAllPerson();
}
package com.lbcjxx.springboot.service.impl;
import com.lbcjxx.springboot.dao.PersonIdcardMapper;
import com.lbcjxx.springboot.pojo.PersonIdcard;
import com.lbcjxx.springboot.service.PersonIdcardService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class PersonIdcardServiceImpl implements PersonIdcardService {
@Autowired
PersonIdcardMapper personIdcardMapper;
@Override
public List<PersonIdcard> findAllPerson() {
return personIdcardMapper.findAllPerson();
}
}
6.设置html展示层
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body>
<h1>personidcard列表页面</h1>
<table border="1">
<tr>
<td>用户id</td>
<td>用户年龄</td>
<td>用户姓名</td>
<td>身份照号码</td>
</tr>
<tr th:each="user:${personlist}">
<td th:text="${user.id}"></td>
<td th:text="${user.age}"></td>
<td th:text="${user.name}"></td>
<td th:text="${user.idcard.code}"></td>
</tr>
</table>
</body>
</html>
7.设置controller层
PersonIdcardController
package com.lbcjxx.springboot.controller;
import com.lbcjxx.springboot.service.PersonIdcardService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
@Controller
public class PersonIdcardController {
//@Autowired
// PersonMapper personMapper;
@Autowired
PersonIdcardService personIdcardService;
//todo --> 网页端
//springboot基于spring和springMVC
@RequestMapping("personidcardlist")
public String personidcardlist(Model model){
model.addAttribute("personlist",personIdcardService.findAllPerson());
return "personidcardlist.html";
}
}
效果展示:
二、一对一查询方法2 -- 级联查询
1.Dao层:新建IdcardMapper.java和IdcardMapper.xml
代码:
package com.lbcjxx.springboot.dao;
import com.lbcjxx.springboot.pojo.Idcard;
import org.apache.ibatis.annotations.Mapper;
//mybatis的实现写到配置文件当中
//多表查询 1-1 1-多 多-多 增删改(两张表) 查
//1-1 查询 person表 idcard(身份证)
//耦合度降低 不设外键 增加一个字段
//查出来的的字段是组合起来的 需要解析
@Mapper
public interface IdcardMapper {
//查询 所有的person表数据库
public Idcard findIdcardById(int id);
}
<?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.lbcjxx.springboot.dao.IdcardMapper">
<!-- public List<Person> findAllPerson();-->
<!-- id 是方法名字 resultType返回值的类型-->
<!-- 方法2: 第一步:person表中查询1次 第二步: 将person idcardid 传到idcard表进行查询-->
<select id="findIdcardById"
parameterType="integer"
resultType="com.lbcjxx.springboot.pojo.Idcard">
select * from idcard where id=#{id}
</select>
</mapper>
2.Dao层:在PersonIdcardMapper.java和PersonIdcardMapper.xml中分别添加
public List<PersonIdcard> findAllPerson2();
<!--方法2-->
<select id="findAllPerson2" resultMap="personResult" >
select class.*,idcard.code
from class,idcard
where class.idcardid=idcard.id
</select>
<!-- 方法2(级联查询): 第一步:person表中查询1次 第二步: 将person idcardid 传到idcard表进行查询-->
<!--配置映射关系-->
<resultMap id="personResult2" type="com.lbcjxx.springboot.pojo.PersonIdcard">
<!-- 主键用id来配-->
<id property="id" column="id"></id>
<result property="age" column="age"></result>
<result property="name" column="name"></result>
<association property="idcard"
column="idcardid"
select="com.wangyang.springboot.dao.IdcardMapper.findIdcardById"
javaType="com.lbcjxx.springboot.pojo.Idcard">
</association>
</resultMap>
3.service层
只需要把findAllPerson改为findAllPerson2
如果要用第一种查询方法把2删掉就是
运行效果和方法一一样
三、一对一删除
1.Dao层
(1)在IdcardMapper.java和IdcardMapper.xml中分别添加
public int deleteIdcardById(int id);
<!-- 删除用户-->
<delete id="deleteIdcardById" parameterType="Integer">
delete from idcard where id=#{id}
</delete>
(2)在PersonIdcardMapper.java和PersonIdcardMapper.xml中分别添加
public int deletePersonById(int id);
<delete id="deletePersonById" parameterType="integer">
delete from class where id=#{id}
</delete>
2.service层
在PersonIdcardService.java和PersonIdcardServiceImpl.java中添加:
public int deletePersonAndIdcardById(int personId,int idcardid);
@Autowired
IdcardMapper idcardMapper;
@Override
public int deletePersonAndIdcardById(int personId,int idcardid) {
//删除idcard表 删除person表
int result=personIdcardMapper.deletePersonById(personId);
if(result>0){
idcardMapper.deleteIdcardById(idcardid);
}
return result;
}
3.修改前端代码 personidcardlist.html thymeleaf
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body>
<h1>personidcard列表页面</h1>
<table border="1">
<tr>
<td>用户id</td>
<td>用户年龄</td>
<td>用户姓名</td>
<td>身份照号码</td>
<td>删除用户</td>
</tr>
<tr th:each="user:${personlist}">
<td th:text="${user.id}"></td>
<td th:text="${user.age}"></td>
<td th:text="${user.name}"></td>
<div th:if="${user.idcard}!=null">
<td th:text="${user.idcard.code}"></td>
</div>
<div th:if="${user.idcard}!=null">
<td>
<a th:href="@{/deletePersonAndIdcard(id=${user.id},idcardid=${user.idcard.id})}">删除用户</a>
</td>
</div>
</tr>
</table>
</body>
</html>
4.Controller层
添加代码:
@RequestMapping("deletePersonAndIdcard")
public String deletePersonAndIdcard(Model model,int id,int idcardid){
int result=personIdcardService.deletePersonAndIdcardById(id,idcardid);
if(result==0){
return "fail.html";
}
return "redirect:/personidcardlist";
}
四、一对一增加
1.Dao层
(1)在IdcardMapper.java和IdcardMapper.xml中分别添加
public int addIdcard(Idcard idcard);
<!-- useGeneratedKeys:将自增长的主键返回-->
<!-- 拿到最新增长主键-->
<insert id="addIdcard"
useGeneratedKeys="true"
keyProperty="id"
parameterType="com.lbcjxx.springboot.pojo.Idcard">
INSERT INTO idcard(code) values (#{code})
</insert>
(2)在PersonIdcardMapper.java和PersonIdcardMapper.xml中分别添加
public int addPerson(PersonIdcard personIdcard);
<insert id="addPerson"
useGeneratedKeys="true"
parameterType="com.lbcjxx.springboot.pojo.PersonIdcard">
INSERT INTO class(age,name,idcardid) values (#{age},#{name},#{idcard.id})
</insert>
2.service层
在PersonIdcardService.java和PersonIdcardServiceImpl.java中添加:
public int addPersonIdcard(PersonIdcard personIdcard);
//事务 增删改(多张表写入) Transactional 在数据操作保证原子性 每个操作要么执行成功,要么同时执行失败
@Transactional
@Override
public int addPersonIdcard(PersonIdcard personIdcard) {
//先往idcard表中增加 再增加person表
int result=idcardMapper.addIdcard(personIdcard.getIdcard());
if(result>0){
personIdcardMapper.addPerson(personIdcard);
}
return result;
}
3.前端代码 thymeleaf
(1)添加页面addpersonidcard.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body>
<h1>增加person页面</h1>
<!--html替代jsp thymeleaf 表达式 可以在html里绑定表达式-->
<form th:action="@{/addpersonidcardcommit}" method="post">
<div>
年龄:<input name="age" />
</div>
<div>
姓名:<input name="name"/>
</div>
<div>
身份证:<input name="code"/>
</div>
<div>
<input type="submit" value="提交"/>
</div>
</form>
<!--<table border="1">-->
<!-- <tr>-->
<!-- <td>用户id</td>-->
<!-- <td>用户年龄</td>-->
<!-- <td>用户姓名</td>-->
<!-- </tr>-->
<!-- <tr th:each="user:${userlist}">-->
<!-- <td th:text="${user.id}"></td>-->
<!-- <td th:text="${user.age}"></td>-->
<!-- <td th:text="${user.name}"></td>-->
<!-- </tr>-->
<!--</table>-->
</body>
</html>
(2)在personidcardlist.html添加代码
<a th:href="@{addpersonidcard}">增加person</a>
4.Controller层
添加代码:
@RequestMapping("addpersonidcardcommit")
public String addpersonidcardcommit(Model model,int age,String name,String code){
PersonIdcard personIdcard=new PersonIdcard();
personIdcard.setAge(age);
personIdcard.setName(name);
Idcard idcard=new Idcard();
idcard.setCode(code);
personIdcard.setIdcard(idcard);
int result=personIdcardService.addPersonIdcard(personIdcard);
if(result==0){
return "fail.html";
}
return "redirect:/personidcardlist";
}
5.运行:
五、一对一修改
1.Dao层
(1)在IdcardMapper.java和IdcardMapper.xml中分别添加
public int updateIdcard(Idcard idcard);
<update id="updateIdcard" parameterType="com.lbcjxx.springboot.pojo.Idcard">
update idcard
set code=#{code}
where id=#{id}
</update>
(2)在PersonIdcardMapper.java和PersonIdcardMapper.xml中分别添加
public int updatePerson(PersonIdcard personIdcard);
<update id="updatePerson" parameterType="com.lbcjxx.springboot.pojo.Person">
update class
set name=#{name},age=#{age}
where id=#{id}
</update>
2.service层
在PersonIdcardService.java和PersonIdcardServiceImpl.java中添加:
public int addPersonIdcard(PersonIdcard personIdcard);
//事务 增删改(多张表写入) Transactional 在数据操作保证原子性 每个操作要么执行成功,要么同时执行失败
@Transactional
@Override
public int addPersonIdcard(PersonIdcard personIdcard) {
//先往idcard表中增加 再增加person表
int result=idcardMapper.addIdcard(personIdcard.getIdcard());
if(result>0){
personIdcardMapper.addPerson(personIdcard);
}
return result;
}
3.前端代码 thymeleaf
(1)添加页面addpersonidcard.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body>
<h1>增加person页面</h1>
<!--html替代jsp thymeleaf 表达式 可以在html里绑定表达式-->
<form th:action="@{/addpersonidcardcommit}" method="post">
<div>
年龄:<input name="age" />
</div>
<div>
姓名:<input name="name"/>
</div>
<div>
身份证:<input name="code"/>
</div>
<div>
<input type="submit" value="提交"/>
</div>
</form>
<!--<table border="1">-->
<!-- <tr>-->
<!-- <td>用户id</td>-->
<!-- <td>用户年龄</td>-->
<!-- <td>用户姓名</td>-->
<!-- </tr>-->
<!-- <tr th:each="user:${userlist}">-->
<!-- <td th:text="${user.id}"></td>-->
<!-- <td th:text="${user.age}"></td>-->
<!-- <td th:text="${user.name}"></td>-->
<!-- </tr>-->
<!--</table>-->
</body>
</html>
(2)在personidcardlist.html添加代码
<a th:href="@{addpersonidcard}">增加person</a>
4.Controller层
添加代码:
@RequestMapping("addpersonidcardcommit")
public String addpersonidcardcommit(Model model,int age,String name,String code){
PersonIdcard personIdcard=new PersonIdcard();
personIdcard.setAge(age);
personIdcard.setName(name);
Idcard idcard=new Idcard();
idcard.setCode(code);
personIdcard.setIdcard(idcard);
int result=personIdcardService.addPersonIdcard(personIdcard);
if(result==0){
return "fail.html";
}
return "redirect:/personidcardlist";
}