动态sql(二）：注解

表结构：
create table grade(
   gid number primary key,
   gname varchar2(20),
   sex number(1)
);


-- Add comments to the columns 
comment on column STUDENT.sex
  is '0男 1女';
  
create table student(
   sid number primary key,
   sname varchar2(20),
   gid number references grade(gid)
);


实体类：
public class Student {
private Integer sid;
private String sname;
private Integer gid;
private Integer sex;
public Integer getSid() {
return sid;
}
public void setSid(Integer sid) {
this.sid = sid;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public Integer getGid() {
return gid;
}
public void setGid(Integer gid) {
this.gid = gid;
}
public Integer getSex() {
return sex;
}
public void setSex(Integer sex) {
this.sex = sex;
}
}






接口：

public interface StudentMapper {

//静态内部类

static class StudentProvier{

//方法内的参数为Map

/**
* ${}在注解情况 失效  使用#{}
* 对象的属性 取值 别名.属性名
* 提供sql方法的参数 是 Map 键值对
* 提供sql语句方法
* @param map
* @return
*/
public String queryStudentSql( Map map){    //字符串拼接
Student student=(Student)map.get("stu");
String sql=" select * from student where 1=1 ";
if(student.getSname()!=null &&!"".equals(student.getSname())){
student.setSname("%"+student.getSname()+"%");
sql+=" and sname like #{stu.sname}";
}
if(student.getGid()!=null &&!"".equals(student.getGid())){

sql+="  and gid = #{stu.gid} ";
}
return sql;
}


public String queryStudentSql1( Map map){  //实体类
Student student=(Student)map.get("stu");
SQL sql=new SQL();
sql.SELECT("*").FROM("student");
if(student.getSname()!=null &&!"".equals(student.getSname())){
student.setSname("%"+student.getSname()+"%");
sql.WHERE(" sname like #{stu.sname}");
}
if(student.getGid()!=null &&!"".equals(student.getGid())){
sql.AND();
sql.WHERE(" gid = #{stu.gid}");
}
return sql.toString();

}

public String queryStudentSql1(Map<String,Map<String,String>> map){ //map
 Map<String,String> map=map.get("map");

String sname=map.get("sname");

String age=map.get("age");

SQL sql=new SQL();
sql.SELECT("*").FROM("student");
if(sname!=null &&!"".equals(sname)){

map.put("sname","%"+map.get("sname")+"%")

sql.AND();
sql.WHERE("sname like #{map.sname}");
}
if(age!=null &&!"".equals(age)){
sql.AND();
sql.WHERE("gid = #{map.gid}");
}
return sql.toString();

}

public String updateStudentSql(Map map){
Student student=(Student)map.get("stu");
SQL sql=new SQL();
sql.UPDATE("student");
if(student.getSname()!=null &&!"".equals(student.getSname())){
sql.SET("sname=#{stu.sname}");
}
if(student.getGid()!=null &&!"".equals(student.getGid())){
sql.SET("gid=#{stu.gid}");
}
if(student.getSex()!=null &&!"".equals(student.getSex())){
sql.SET("sex=#{stu.sex}");
}
sql.WHERE("sid =#{stu.sid}");
return sql.toString();
}

public String queryStudentbyAnyGradeSql(Map map){
List<Integer> list=(List<Integer>) map.get("gradeList");

String sql="select * from student where gid in(";

StringBuilder builder = new StringBuilder();
for (Integer in : list) {
builder.append(in + ",");
}

String s = builder.substring(0, builder.length()-1);

s=s+")";

return sql+s;
}
}


@SelectProvider(type=StudentProvier.class,method="queryStudentSql1")
public List<Student> queryStudent(@Param("stu") Student student);


//  直接使用<script>sql语句 </script> 这种方式不支持使用
@Select("<script>select * from student where 1=1 " +
"<choose>"+
      "<when test=\"sex!=null\">"+
      "   and sex=#{sex}"+
      "</when>"+
      " <otherwise>"+
      "   and sex=1 "+
      "</otherwise>"+
      "</choose></script>")
public List<Student> queryBySex(@Param("sex")Integer sex);
/**
* 更新学生信息
* @param sid
* @return

*/

--用实体类来接收字段变量

@UpdateProvider(type=StudentProvier.class,method="updateStudentSql")

public void updateStudent(@Param("stu") Student student);

--用map来接收字段变量 --假设传来参数String sname,String age

@UpdateProvider(type=StudentProvier.class,method="updateStudentSql")

public void updateStudent(@Param("map") Map<String,String> map);

/**
* 通过传入的班级查询所有学生
* 0,1,2
* 1,2
* 2,3,4
* 
* @param gradeList
*/
@SelectProvider(type=StudentProvier.class,method="queryStudentbyAnyGradeSql")
public List<Student> queryStudentbyAnyGrade(@Param("gradeList")List<Integer> gradeList);
}


//测试类


public class MyBatisTest {
private  SqlSession getSession() throws IOException {
// mybatis核心配置文件路径
String resource = "cn/et/lesson4/annotion/mybatis.xml";


InputStream inputStream = Resources.getResourceAsStream(resource);
// 工厂类
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder()
.build(inputStream);
// session操作的是指向sql语句的一个唯一标识符
SqlSession openSession = sqlSessionFactory.openSession();
return openSession;
}


@Test
public  void selectStudent() throws IOException {
SqlSession openSession = getSession();

StudentMapper mapper = openSession.getMapper(StudentMapper.class);
Student student=new Student();

student.setGid(2);
student.setSname("飞");
List<Student> stList = mapper.queryStudent(student);

for(Student stu:stList){
System.out.println(stu.getGid()+"---"+stu.getSname());
}
}
@Test
public  void selectStudentBySex() throws IOException {
SqlSession openSession = getSession();
StudentMapper mapper = openSession.getMapper(StudentMapper.class);

List<Student> stList = mapper.queryBySex(1);

for(Student stu:stList){
System.out.println(stu.getSname());
}
}

@Test
public  void updateStudent() throws IOException {
SqlSession openSession = getSession();

StudentMapper mapper = openSession.getMapper(StudentMapper.class);
Student student=new Student();

student.setSname("gou飞");
student.setGid(1);
student.setSex(1);
student.setSid(1);
mapper.updateStudent(student);

openSession.commit();
}


@Test
public  void queryStudentbyAnyGrade() throws IOException {
SqlSession openSession = getSession();

StudentMapper mapper = openSession.getMapper(StudentMapper.class);

List<Integer> list=new ArrayList<>();
list.add(1);
list.add(3);

List<Student> sList = mapper.queryStudentbyAnyGrade(list);

for(Student stu:sList){
System.out.println(stu.getSname());
}
}
}