mybatis小练习

1.按成绩从高到低排序查询成绩在80——90（闭区间）之间的学员姓名
步骤： 先在选课的dao接口中增加一个方法，用来查询80-90区间内的所有选课信息，然后从选课信息中提取出所有学号，在学生的dao中写一个根据学号（根据单个学号查询即可）查询学生信息的方法，把上面提取出的所有学号，都调用一下这个方法，然后输出每个学生的姓名

2.查询选课学分在3以上的年龄最小的两个学员的姓名
3.查询每门课的平均分
4.查询数学成绩高于王敏的数学成绩的学员信息
5.查询课程学分在3以上，成绩及格的男同学姓名

Course类

package mybatis;

public class Course {
    private int cno;
    private String cname;
    private int cpno;
    private int ccredit;



    public int getCno() {
        return cno;
    }

    public void setCno(int cno) {
        this.cno = cno;
    }

    public String getCname() {
        return cname;
    }

    public void setCname(String cname) {
        this.cname = cname;
    }

    public int getCpno() {
        return cpno;
    }

    public void setCpno(int cpno) {
        this.cpno = cpno;
    }

    public int getCcredit() {
        return ccredit;
    }

    public void setCcredit(int ccredit) {
        this.ccredit = ccredit;
    }
}

CourseDao

package mybatis;

import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface CourseDao {
    @Select("select * from course where ccredit >3")
        List<Course> selectByCredit();
}

Sc类

package mybatis;

public class Sc {
    private int sno;
    private int cno;
    private int grade;



    public int getSno() {
        return sno;
    }

    public void setSno(int sno) {
        this.sno = sno;
    }

    public int getCno() {
        return cno;
    }

    public void setCno(int cno) {
        this.cno = cno;
    }

    public int getGrade() {
        return grade;
    }

    public void setGrade(int grade) {
        this.grade = grade;
    }
}

ScDao 


package mybatis;

import org.apache.ibatis.annotations.Select;

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

public interface ScDao {
    @Select("select * from sc where grade between 80 and 90 order by grade desc")
    List<Sc> selectByGrade();

    @Select("select * from sc where cno = #{cno}")
    List<Sc> selectByCno(int cno);

    @Select("select cno,avg(grade) a from sc group by cno")
    List<Map<String,Integer>> selectAvg();
}

Student类

package mybatis;

public class Student {
    private int sno;
    private String sname;
    private String ssex;
    private int sage;
    private String sdept;



    public int getSno() {
        return sno;
    }

    public void setSno(int sno) {
        this.sno = sno;
    }

    public String getSname() {
        return sname;
    }

    public void setSname(String sname) {
        this.sname = sname;
    }

    public String getSsex() {
        return ssex;
    }

    public void setSsex(String ssex) {
        this.ssex = ssex;
    }

    public int getSage() {
        return sage;
    }

    public void setSage(int sage) {
        this.sage = sage;
    }

    public String getSdept() {
        return sdept;
    }

    public void setSdept(String sdept) {
        this.sdept = sdept;
    }
}

StudentDao

package mybatis;

import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface StudentDao {
    @Select("select * from student where sno=#{sno}")
    Student selectBySno(int sno);


    List<Student> selectAll();

    @Select("select * from sc where grade=#{grade}")
    List<Sc> selectByGrade(int grade);


}

测试一

package mybatis;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.InputStream;
import java.util.List;

public class Test1 {
    public static void main(String[] args) throws Exception{
        String config = "mybatis-config.xml";
        //使用io流读取配置文件
        InputStream in = Resources.getResourceAsStream(config);
        //通过配置文件的内容，构建连接工厂
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        //从工厂中获取一个数据库连接
        SqlSession session = factory.openSession();
        //通过连接执行sql语句
        StudentDao studentDao = session.getMapper(StudentDao.class);
        ScDao scDao = session.getMapper(ScDao.class);
        List<Sc> list = scDao.selectByGrade();
        for (Sc s : list) {
            Student st = studentDao.selectBySno(s.getSno());
            System.out.println(st.getSname());
        }
    }
}

测试二

package mybatis;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.InputStream;
import java.util.*;

public class Test2 {

    public static void main(String[] args) throws Exception {
        String config = "mybatis-config.xml";
        InputStream in = Resources.getResourceAsStream(config);
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        SqlSession session = factory.openSession();

        StudentDao studentDao = session.getMapper(StudentDao.class);
        ScDao scDao = session.getMapper(ScDao.class);
        CourseDao courseDao = session.getMapper(CourseDao.class);

        List<Course> courses = courseDao.selectByCredit();

        Set<Integer> set = new HashSet<Integer>();
        List<Student> list = new ArrayList<Student>();
        for(Course c : courses){
            List<Sc> scList = scDao.selectByCno(c.getCno());
            for(Sc s : scList){
                //如果集合中不存在当前学号，则查询这个学生的信息
                if(!set.contains(s.getSno())) {
                    Student st = studentDao.selectBySno(s.getSno());
                    list.add(st);
                    set.add(s.getSno());
                }
            }
        }

        Comparator<Student> com = new Comparator<Student>() {
            public int compare(Student o1, Student o2) {
                return o1.getSage()-o2.getSage();
            }
        };

        Collections.sort(list, com);

        System.out.println(list.get(0).getSname());
        System.out.println(list.get(1).getSname());

    }
}

测试三

package mybatis;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.InputStream;
import java.util.List;
import java.util.Map;

public class Test3 {
    public static void main(String[] args)  throws Exception{
        String config = "mybatis-config.xml";
        InputStream in = Resources.getResourceAsStream(config);
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        SqlSession session = factory.openSession();

        ScDao scDao = session.getMapper(ScDao.class);
        List<Map<String, Integer>> map = scDao.selectAvg();
        for (Map<String, Integer> m : map) {
            System.out.println(m.get("cno") + "\t " + m.get("a"));
        }
    }
}