Orm映射 java_ORM框架三种映射在Springboot上的使用

ORM(对象/关系映射)是数据库层非常重要的一部分，有三种常用的映射关系

1.多对一

tbl_clazz

clazz{

id

name

description

grade_id

charge_id

}

clazz {

id

name

description

grade:{

id:

name:

...

},

charge:{

id

name

gender

}

}

提供多对一的查询   -------班级表对应年级表和班主任表

bean

extend

ClazzVM.java

dao

extend

ClazzVMMapper.java(接口)

mapper

extend

ClazzVMMapper.xml

ClazzVMMapper主要实现sql语句，是非常重要的一部分，关键代码如下

select * frompoll_clazzwhere id=#{id}

column="grade_id" property="grade"

select="com.briup.apps.poll.dao.GradeMapper.selectByPrimaryKey">

column="charge_id" property="charge"

select="com.briup.apps.poll.dao.UserMapper.selectByPrimaryKey">

2.一对多

一对多 查询  ------一个问题包括多个问题选项

查找所有问题，包括问题选项

bean:

package com.briup.apps.poll.bean.extend;

import java.util.List;

import com.briup.apps.poll.bean.Options;public classQuestionVM {privateLong id;privateString name;privateString questionType;private Listoptions;publicLong getId() {returnid;

}public voidsetId(Long id) {this.id =id;

}publicString getName() {returnname;

}public voidsetName(String name) {this.name =name;

}publicString getQuestionType() {returnquestionType;

}public voidsetQuestionType(String questionType) {this.questionType =questionType;

}public ListgetOptions() {returnoptions;

}public void setOptions(Listoptions) {this.options =options;

}

}

dao:

public interface QuestionVMMapper {

List selectAll() throws Exception;

List selectByQuestionnaireId(long id) throws Exception;

}

QuestionVMMapper.xml(对应实现dao层的selectAll功能)

select * frompoll_question

column="id"property="options"javaType="ArrayList"ofType="com.briup.apps.poll.bean.Options"

select="selectOptionsByQuestionId">

select * from poll_options where question_id =#{id}

service层：

接口

package com.briup.apps.poll.service;

import java.util.List;

import com.briup.apps.poll.bean.Question;

import com.briup.apps.poll.bean.extend.QuestionVM;public interfaceIQuestionService {//查找所有问题，并查找出问题的所有选项

ListfindAll() throws Exception;//更新或者保存问题，并保存更新问题选项

voidsaveOrUpdateQuestionVM(QuestionVM questionVM) throws Exception;//删除问题，级联删除选项

void deleteById(longid) throws Exception;//关键字查询

Listquery(String keywords) throws Exception;//批量删除题目

voiddeleteBach(Long[] id)throws Exception;

}

接口实现层

package com.briup.apps.poll.service.Impl;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Service;

import com.briup.apps.poll.bean.Options;

import com.briup.apps.poll.bean.OptionsExample;

import com.briup.apps.poll.bean.Question;

import com.briup.apps.poll.bean.QuestionExample;

import com.briup.apps.poll.bean.extend.QuestionVM;

import com.briup.apps.poll.dao.OptionsMapper;

import com.briup.apps.poll.dao.QuestionMapper;

import com.briup.apps.poll.dao.extend.QuestionVMMapper;

import com.briup.apps.poll.service.IQuestionService;

@Servicepublic classQuestionServiceImpl implements IQuestionService {

@AutowiredprivateQuestionVMMapper questionVMMapper;

@AutowiredprivateOptionsMapper optionsMapper;

@AutowiredprivateQuestionMapper questionMapper;

@Overridepublic ListfindAll() throws Exception {returnquestionVMMapper.selectAll();

}/** 保存或者修改问题，带选项*/@Overridepublic voidsaveOrUpdateQuestionVM(QuestionVM questionVM) throws Exception {/** 1.得到question和options实体，因为保存是分别保存的

* 2.判断是否是保存还是修改操作

* 3.判断题目类型(简答还是选择)

**/Question question=newQuestion();

question.setId(questionVM.getId());

question.setName(questionVM.getName());

question.setQuestiontype(questionVM.getQuestionType());

List options=questionVM.getOptions();if(questionVM.getId()==null){/*保存操作

* 1.保存题目

* 2.保存选型*/

if(questionVM.getQuestionType().equals("简答题")){//仅仅保存题目

questionMapper.insert(question);

}else{//先保存题目

questionMapper.insert(question);//获取question的id

long id=question.getId();//保存选项

for(Options option : options){//遍历所有选项，进行保存

option.setQuestionId(id);

optionsMapper.insert(option);

}

}

}else{//修改

/** 1.修改题目信息

* 2.删除选项

* 3.重新添加选项*/questionMapper.updateByPrimaryKey(question);

OptionsExample example=newOptionsExample();

example.createCriteria().andQuestionIdEqualTo(question.getId());

optionsMapper.deleteByExample(example);long id=question.getId();for(Options option : options){//遍历所有选项，进行保存

option.setQuestionId(id);

optionsMapper.insert(option);

}

}

}

@Overridepublic void deleteById(longid) throws Exception {

questionMapper.deleteByPrimaryKey(id);

}

@Overridepublic Listquery(String keywords) throws Exception {

QuestionExample example=newQuestionExample();

example.createCriteria().andNameLike(keywords);returnquestionMapper.selectByExample(example);

}

@Overridepublic voiddeleteBach(Long[] ids) throws Exception {for(longid : ids){

questionMapper.deleteByPrimaryKey(id);

}

}

}

Controll层

package com.briup.apps.poll.web.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.web.bind.annotation.GetMapping;

import org.springframework.web.bind.annotation.PostMapping;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RestController;

import com.briup.apps.poll.bean.Question;

import com.briup.apps.poll.bean.extend.QuestionVM;

import com.briup.apps.poll.service.IQuestionService;

import com.briup.apps.poll.util.MsgResponse;

import io.swagger.annotations.Api;

import io.swagger.annotations.ApiOperation;

@Api(description="问题模块接口")

@RestController

@RequestMapping("/question")

public class QuestionController {

@Autowired

private IQuestionService questionService;

@ApiOperation(value="删除问题",notes="同时级联删除options")

@GetMapping("deleteQuestionById")

public MsgResponse deleteQuestionQuestionById(long id){

try{

questionService.deleteById(id);

return MsgResponse.success("删除成功", null);

}catch(Exception e){

return MsgResponse.error(e.getMessage());

}

}

@ApiOperation(value="保存或者更新问题",notes="如果id为空，执行保存操作，如果id不为空，执行更新操作")

@PostMapping("saveOrUpdateQuestion")

public MsgResponse saveOrUpdateQuestion(QuestionVM questionVM){

try {

questionService.saveOrUpdateQuestionVM(questionVM);

return MsgResponse.success("success", null);

} catch (Exception e) {

e.printStackTrace();

return MsgResponse.error(e.getMessage());

}

}

@ApiOperation(value="查找所有问题",notes="并查找出所有问题的选项")

@GetMapping("findAllQuestion")

public MsgResponse findAllQuestion(){

try{

List list=questionService.findAll();

return MsgResponse.success("success", list);

}catch(Exception e){

e.printStackTrace();

return MsgResponse.error(e.getMessage());

}

}

@ApiOperation(value="批量删除问题",notes="输入问题id")

@GetMapping("deleteBatch")

public MsgResponse deleteBatch(Long[] ids){

try{

questionService.deleteBach(ids);

return MsgResponse.success("success", null);

}catch(Exception e){

e.printStackTrace();

return MsgResponse.error(e.getMessage());

}

}

@ApiOperation(value="关键字查询",notes="输入题目的关键字")

@GetMapping("findByQuery")

public MsgResponse findByQuery(String keywords){

try{

List list=questionService.query(keywords);

return MsgResponse.success("success", list);

}catch(Exception e){

e.printStackTrace();

return MsgResponse.error(e.getMessage());

}

}

}

3.多对多

问卷模块

1. 查询所有问卷/通过关键字查询问卷(单表)

2. 预览问卷、课调

通过问卷id查询问卷下所有信息(问卷，问题，选项)

2.1 数据库级别表示多对多

问卷 questionnaire

id name

1 主讲问卷

2 辅讲问卷

问题 question

id name

1 授课质量

2 技术水平

3 亲和力

qq

id questionnaire_id question_id

1 1 1

2 1 2

3 1 3

4 2 1

5 2 3

# 通过问卷id查找问卷信息

select * from poll_questionnaire where id =1;

// id name description

# 通过问卷id查找属于问卷的问题信息

select * from poll_question where id in (

select question_id

from poll_qq

where questionnaire_id = 1;

);

2.2 面向对象级别上多对多

QuestionnaireVM{

private Long id;

private String name;

private List questions;

}

QuestionVM{

private Long id

private String name;

private List questionnaires;

}

bean层：

package com.briup.apps.poll.bean.extend;

import java.util.List;public classQuestionnaireVM {privateLong id;privateString name;privateString description;private ListquestionVMs;publicLong getId() {returnid;

}public voidsetId(Long id) {this.id =id;

}publicString getName() {returnname;

}public voidsetName(String name) {this.name =name;

}publicString getDescription() {returndescription;

}public voidsetDescription(String description) {this.description =description;

}public ListgetQuestionVMs() {returnquestionVMs;

}public void setQuestionVMs(ListquestionVMs) {this.questionVMs =questionVMs;

}

}

dao层：

package com.briup.apps.poll.dao.extend;

import java.util.List;

import com.briup.apps.poll.bean.extend.QuestionnaireVM;public interfaceQuestionnaireVMMapper {

List selectById(longid);

}

QuestionnaireVM.xml

select * from poll_questionnaire where id =#{id}

column="id"property="questionVMs"javaType="ArrayList"ofType="com.briup.apps.poll.bean.extend.QuestionVM"

select="com.briup.apps.poll.dao.extend.QuestionVMMapper.selectByQuestionnaireId">

select * from poll_question where id in(select question_id from poll_qq where questionnaire_id =#{id}

)

根据问卷id查找所有该问卷的问题，在QuestionVMMapper.java中声名了一个根据问卷id查找所有问题的方法

该方法在QuestionVMMapper.xml中对应的代码为：

select * from poll_question where id in(select question_id from poll_qq where questionnaire_id =#{id}

)

service层

接口

package com.briup.apps.poll.service;

import java.util.List;

import com.briup.apps.poll.bean.Questionnaire;

import com.briup.apps.poll.bean.extend.QuestionnaireVM;public interfaceIQuestionnaireVMService {//查询所有问卷

ListfindAll();//查询所有问卷及问卷的问题

List findQuestionById(longid);

}

实现

package com.briup.apps.poll.service.Impl;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Service;

import com.briup.apps.poll.bean.Questionnaire;

import com.briup.apps.poll.bean.QuestionnaireExample;

import com.briup.apps.poll.bean.extend.QuestionnaireVM;

import com.briup.apps.poll.dao.QuestionnaireMapper;

import com.briup.apps.poll.dao.extend.QuestionnaireVMMapper;

import com.briup.apps.poll.service.IQuestionnaireVMService;

@Servicepublic classQuestionnaireVMServiceImpl implements IQuestionnaireVMService{

@AutowiredprivateQuestionnaireMapper questionnaireMapper;

@AutowiredprivateQuestionnaireVMMapper questionnaireVMMapper;

@Overridepublic ListfindAll() {

QuestionnaireExample example=newQuestionnaireExample();returnquestionnaireMapper.selectByExampleWithBLOBs(example);

}

@Overridepublic List findQuestionById(longid) {returnquestionnaireVMMapper.selectById(id);

}

}

controll层

package com.briup.apps.poll.web.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.web.bind.annotation.GetMapping;

import org.springframework.web.bind.annotation.PostMapping;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RestController;

import com.briup.apps.poll.bean.Questionnaire;

import com.briup.apps.poll.bean.extend.QuestionnaireVM;

import com.briup.apps.poll.service.IQuestionnaireVMService;

import com.briup.apps.poll.util.MsgResponse;

import io.swagger.annotations.Api;

import io.swagger.annotations.ApiOperation;

@Api(description="问卷模块接口")

@RestController

@RequestMapping("/questionnaire")public classQuestionnaireVMController {

@AutowiredprivateIQuestionnaireVMService qnvs;

@ApiOperation(value="查找所有问卷",notes="仅仅只查找问卷")

@PostMapping("findAllQuestionnaire")publicMsgResponse findAllQuestionnaire(){try{

List list=qnvs.findAll();return MsgResponse.success("success", list);

}catch(Exception e){

e.printStackTrace();returnMsgResponse.error(e.getMessage());

}

}

@ApiOperation(value="预览问卷信息",notes="预览包括问卷上的问题")

@GetMapping("findAllQuestionnaireById")public MsgResponse findAllQuestionnaireById(longid){try{

List list=qnvs.findQuestionById(id);return MsgResponse.success("success", list);

}catch(Exception e){

e.printStackTrace();returnMsgResponse.error(e.getMessage());

}

}

}