MyBatis中遇到的MySql语句

学生类:

package com.xiaomuedu.entity;


public class Student{
	private String StudentId;
	
	private String studentCode;
	
	private String studentName;
	
    //专业
	private String major;

    //用于selectKey中的count,并需要seter/getter方法,无实际意义,
    // 如果parameterType不是实体类,是map,则不需要
    private Integer count;

	

	public String getStudentId() {
		return StudentId;
	}

	public void setId(String StudentId) {
		this.StudentId= StudentId;
	}

	public String getStudentCode() {
		return studentCode;
	}

	public void setStudentCode(String studentCode) {
		this.studentCode = studentCode;
	}

	public String getStudentName() {
		return studentName;
	}

	public void setStudentName(String studentName) {
		this.studentName = studentName;
	}

	public String getMajor() {
		return major;
	}

	public void setMajor(String major) {
		this.major = major;
	}
	
}

 

学生成绩类:

package com.xiaomuedu.entity;

public class Score {
	private String id;
	
	private String studentId;
	
	private String subject;
	
	private Integer score;

	
	
	public String getId() {
		return id;
	}

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

	public String getStudentId() {
		return studentId;
	}

	public void setStudentId(String studentId) {
		this.studentId = studentId;
	}

	public String getSubject() {
		return subject;
	}

	public void setSubject(String subject) {
		this.subject = subject;
	}

	public Integer getScore() {
		return score;
	}

	public void setScore(Integer score) {
		this.score = score;
	}
	
}

 

学生级联查询(大Model):

package com.xiaomuedu.entity;

import java.util.List;

//使用于级联查询(一个学生有多个成绩)
public class StudentModel extends Student{
	//学生成绩集合
	private List<Score> scoreList;

	
	public List<Score> getScoreList() {
		return scoreList;
	}

	public void setScoreList(List<Score> scoreList) {
		this.scoreList = scoreList;
	}
	
	
}

 

学生dao类:

package com.xiaomuedu.dao;

import java.util.List;
import java.util.Map;

import com.xiaomuedu.entity.Student;
import com.xiaomuedu.mybatis.MyBatisRepositoryVelocity;

/**
 * 人员表dao
 * @author Administrator
 * @time 2016-12-23 上午10:55:26
 */
@MyBatisRepositoryVelocity
public interface StudentDao {
	Integet insertOrUpdate(Student student);
	
    //级联查询(一堆多)
	List<StudentModel> queryStudentList();
		
}

成绩dao类(ScoreDao):

package com.xiaomuedu.dao;

import java.util.List;
import java.util.Map;

import com.xiaomuedu.entity.Score;
import com.xiaomuedu.mybatis.MyBatisRepositoryVelocity;

/**
 * 人员表dao
 * @author Administrator
 * @time 2016-12-23 上午10:55:26
 */
@MyBatisRepositoryVelocity
public interface ScoreDao {
    //用于级联查询的
	List<Score> getStudentScoreList(String StudentId);
		
}

 

根据id查询学生表来确定是进行插入还是更新操作(studentMapper):

<!--如果parameterType不是实体类(Student),是map,则不需要-->
<insert id="insertOrUpdateStudent" parameterType="Student">
		<selectKey resultType="count" keyProperty="int" order="BEFORE">
			select count(*) from student where StudentId = #{StudentId}
		</selectKey>
		<if test="count == 0">
			insert into student (StudentId, 
					studentName,
					studentCode,
					major
			 ) values (
			 	#{StudentId},
			 	#{studentName},
			 	#{studentCode},
			 	#{major}
			 )
		</if>
		<if test="count == 1">
			update student set 
					studentName = #{studentName}, 
					studentCode = #{studentCode}, 
					major = #{major}
			where StudentId = #{StudentId}
		</if>
	</insert>

 

MyBatis实现级联操作(一对多的情况)-studentMapper:

<!--如果column是多个参数,需要写成column="{studentId = studentId, xxId = xxId}"-->
<!--ofType是子查询的返回结果类型，可以是实体类,也可以是集合,但必须与StudentModel中定义的List<Score>的Score一致-->
<resultMap type="StudentModel" id="stuList">
	<collection property="scoreList" select="com.xiaomuedu.dao.ScoreDao.getStudentScoreList" column="studentId = studentId" ofType="Score" autoMapping="true"></collection>
</resultMap>
	
<select id="queryStudentList" resultMap="stuList">
	select * from student
</select>

级联操作的子集查询语句(ScoreMapper):

<select id="getStudentScoreList" resultType="Score">
   select * from studentId = #{StudentId}
</select

 

 

转载于:https://my.oschina.net/chengwei426/blog/1488458