0

问题

  1. 用户与书是一对多,查询用户的时候,可以查出这个用户下面有多少书,
  2. 书与用户是一对一,查询书的时候,可以查出这本书是属于哪个用户

数据表sql

user

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

book

CREATE TABLE `book` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `price` float DEFAULT NULL,
  `userId` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

dao层

user

public class User {

    private int no;

    private String name;

    private String email;

    public int getNo() {
        return no;
    }

    public void setNo(int no) {
        this.no = no;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }
}

book

public class Book {

    private int id;

    private String name;

    private float price;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public float getPrice() {
        return price;
    }

    public void setPrice(float price) {
        this.price = price;
    }

mapper

user

package com.liangwei.demo.mapper;

import java.util.List;
import com.liangwei.demo.model.User;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

@Mapper
public interface UserMapper {

    @Insert({"insert into user(no, name, email) values(#{no}, #{name}, #{email})"})
    void AddUser(User userInfo);

    @Delete("delete from user where no=#{no}")
    void delUserById(@Param("no") int no);

    @Update("update user set name = #{name}, email = #{email} where no = #{no}")
    void updateUserByNo(@Param("name") String name, @Param("email") String email, @Param("no") int no);

    @Select("select * from user")
    List<User> getUser();
}

book

package com.liangwei.demo.mapper;

import com.liangwei.demo.model.Book;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface BookMapper {

    @Insert({"insert into book(id, name,  price) values(#{id}, #{name}, #{price})"})
    void addBook(Book book);

    @Delete("delete from book where id=#{id}")
    void delBookById(@Param("id") int id);

    @Update("update book set name = #{name}, price = #{price} where id = #{id}")
    void updateBookById(@Param("name") String name, @Param("price") float price, @Param("id") int id);

    @Select("select * from book")
    List<Book> getBook();
}

service层

user

package com.liangwei.demo.service;

import com.liangwei.demo.model.User;
import org.apache.ibatis.annotations.*;
import java.util.List;

public interface UserService {

    void AddUser(User user);

    void delUserById(@Param("no") int no);

    void updateUserByNo(User user);

    List<User> getUser();
}

book

package com.liangwei.demo.service;

import com.liangwei.demo.model.Book;
import org.apache.ibatis.annotations.*;

import java.util.List;

public interface BookService {

    void addBook(Book book);

    void delBookById(int id);

    void updateBookById(Book book);

    List<Book> getBook();
}

impl

user

package com.liangwei.demo.service.impl;

import java.util.Collections;
import java.util.List;
import java.util.stream.Collectors;

import com.liangwei.demo.mapper.UserMapper;
import com.liangwei.demo.model.User;
import com.liangwei.demo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class UserServiceImpl implements UserService{

    @Autowired
    private UserMapper userMapper;

    @Override
    public void AddUser(User user) {
        userMapper.AddUser(user);
    }

    @Override
    public void delUserById(int no) {
        userMapper.delUserById(no);
    }

    @Override
    public void updateUserByNo(User user) {
        userMapper.updateUserByNo(user.getName(),user.getEmail(),user.getNo());
    }

    @Override
    public List<User> getUser() {

        List<User> userList = userMapper.getUser();


        Collections.sort(userList,(user1, user2) -> user1.getNo() - user2.getNo());

        return userList;
    }
}

book

package com.liangwei.demo.service.impl;

import com.liangwei.demo.mapper.BookMapper;
import com.liangwei.demo.model.Book;
import com.liangwei.demo.service.BookService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class BookServiceImpl implements BookService {

    @Autowired
    private BookMapper bookMapper;

    @Override
    public void addBook(Book book) {
        bookMapper.addBook(book);
    }

    @Override
    public void delBookById(int id) {
        bookMapper.delBookById(id);
    }

    @Override
    public void updateBookById(Book book) {
        bookMapper.updateBookById(book.getName(),book.getPrice(),book.getId());
    }

    @Override
    public List<Book> getBook() {
        return bookMapper.getBook();
    }
}

controller层

user

package com.liangwei.demo.controller;

import com.liangwei.demo.model.User;
import com.liangwei.demo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.text.ParseException;
import java.util.Date;
import java.util.List;

@RestController
@RequestMapping("/api")
public class UserController {

    @Autowired
    private UserService userService;

    @RequestMapping(value ="/user", method = RequestMethod.GET)
    public List<User> UserQry() {
        return userService.getUser();
    }

    @RequestMapping(value ="/user", method = RequestMethod.POST)
    public void UserAdd(User user){
         userService.AddUser(user);
    }

    @RequestMapping(value ="/user", method = RequestMethod.PUT)
    public void UserUpdate(User user){
        userService.updateUserByNo(user);
    }

    @RequestMapping(value ="/user", method = RequestMethod.DELETE)
    public void UserDel(@RequestParam(value="no") int no){
        userService.delUserById(no);
    }
}

book

package com.liangwei.demo.controller;

import com.liangwei.demo.model.Book;
import com.liangwei.demo.service.BookService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
@RequestMapping("/api")
@Api("图书api")
public class BookController {

    @Autowired
    private BookService bookService;

    @ApiOperation("查询书本的信息")
    @RequestMapping(value ="/book", method = RequestMethod.GET)
    public List<Book> BookQry() {
        return bookService.getBook();
    }

    @ApiOperation("增加书本信息")
    @RequestMapping(value ="/book", method = RequestMethod.POST)
    public void BookAdd(Book book){
        bookService.addBook(book);
    }

    @ApiOperation("修改书本信息")
    @RequestMapping(value ="/book", method = RequestMethod.PUT)
    public void BookUpdate(Book book){
        bookService.updateBookById(book);
    }

    @ApiOperation("删除书本信息")
    @ApiImplicitParam(name = "id", value = "书本编号", required = true, dataType = "Integer")
    @RequestMapping(value ="/book", method = RequestMethod.DELETE)
    public void BookDel(@RequestParam(value="id") int id){
        bookService.delBookById(id);
    }
}
2019-03-01 提问
2 个回答
0

试下 左连 left join

撰写答案

推广链接