（十）学生课程表查询

创建学生课程表（多对多），两张表，一个学生可以上多个课程，一个课程包含多个学生。

使用列限定符来标识课程号以及学生id。

在关系数据库中和在hbase中的表设计

使用filter查询：

            1.根据学号student_id查询学生选课编号course_id和名称title

            2.根据课程号course_id查询选课学生学号student_id和姓名name

            3.根据教员号teacher_id查询该教员所上课程编号course_id和名称title

            4.上课最多的学生

            5.上课最少的学生

1. 创建表

import java.io.IOException;
import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.hbase.HBaseConfiguration;
import org.apache.hadoop.hbase.HColumnDescriptor;
import org.apache.hadoop.hbase.HTableDescriptor;
import org.apache.hadoop.hbase.MasterNotRunningException;
import org.apache.hadoop.hbase.ZooKeeperConnectionException;
import org.apache.hadoop.hbase.client.HBaseAdmin;
import org.apache.hadoop.hbase.client.HTable;
import org.apache.hadoop.hbase.client.Put;
import org.apache.hadoop.hbase.util.Bytes;
public class Student_Course {
    
    static Configuration configuration = HBaseConfiguration.create();  
    public static void main(String[] args) throws IOException {
        
        createTable("student", "info", "course");
        createTable("course", "info", "student");
        int age=(int)( Math.random()*10+10);
        
        for(int i=0;i<10;i++){
            insertData("student", "s"+i, "info", "name", "name"+i);
            insertData("student", "s"+i, "info", "age", "20");
            insertData("student", "s"+i, "info", "sex", ""+age);
            
            for(int j=0;j<=(int)( Math.random()*9+1);j++){
                int x=(int)( Math.random()*10);
                insertData("student", "s"+i, "course", "c"+x, "course"+x);
            }
        }        
        for(int k=0;k<10;k++){
            
            insertData("course", "c"+k, "info", "tittle", "tittle"+k);
            insertData("course", "c"+k, "info", "introduction", "intro"+k);
            insertData("course", "c"+k, "info", "techear", "teacher"+k);
            for(int j=0;j<=(int)( Math.random()*9+1);j++){
                int x=(int)( Math.random()*10);
                insertData("course", "c"+k, "student", "s"+x, "student"+x);
            }
        }
        
    }
    
    @SuppressWarnings({ "deprecation", "resource" })
    public static void createTable(String tableName,String family1,String family2) {  
        System.out.println("start create table ......");  
        try {  
            HBaseAdmin hBaseAdmin = new HBaseAdmin(configuration);
            if (hBaseAdmin.tableExists(tableName)) {// 如果存在要创建的表，那么先删除，再创建  
                hBaseAdmin.disableTable(tableName);  
                hBaseAdmin.deleteTable(tableName);  
                System.out.println(tableName + " is exist,detele....");  
            }  
            HTableDescriptor tableDescriptor = new HTableDescriptor(tableName);  
            tableDescriptor.addFamily(new HColumnDescriptor(family1));  
            tableDescriptor.addFamily(new HColumnDescriptor(family2));  
            hBaseAdmin.createTable(tableDescriptor);  
        } catch (MasterNotRunningException e) {  
            e.printStackTrace();  
        } catch (ZooKeeperConnectionException e) {  
            e.printStackTrace();  
        } catch (IOException e) {  
            e.printStackTrace();  
        }  
        System.out.println("end create table ......");  
    }  
    @SuppressWarnings({ "deprecation", "resource" })
    public static void insertData(String tablename, String row, String columnFamily, String column, String data)
            throws IOException {
        HTable table = new HTable(configuration, tablename);
        Put p1 = new Put(Bytes.toBytes(row));
        p1.add(Bytes.toBytes(columnFamily), Bytes.toBytes(column), Bytes.toBytes(data));
        table.put(p1);
        System.out.println("put'" + row + "','" + columnFamily + ":" + column + "','" + data + "'");
    }
    
    
}

2. 查询

import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.hbase.HBaseConfiguration;
import org.apache.hadoop.hbase.KeyValue;
import org.apache.hadoop.hbase.client.Get;
import org.apache.hadoop.hbase.client.HTable;
import org.apache.hadoop.hbase.client.Result;
import org.apache.hadoop.hbase.client.ResultScanner;
import org.apache.hadoop.hbase.client.Scan;
import org.apache.hadoop.hbase.filter.BinaryComparator;
import org.apache.hadoop.hbase.filter.CompareFilter;
import org.apache.hadoop.hbase.filter.Filter;
import org.apache.hadoop.hbase.filter.RowFilter;
import org.apache.hadoop.hbase.filter.SubstringComparator;
import org.apache.hadoop.hbase.filter.ValueFilter;
import org.apache.hadoop.hbase.util.Bytes;
public class Stu_Cour_Filter {
     static Configuration conf = HBaseConfiguration.create();
     static String stableName="student";
     static String ctableName="course";
     public static void main(String[] args) throws IOException {
          Stu_id_Cour_id_tit("s1");
          Cour_id_Ttu_id_name("c0");
          Tea_id_Cour_tit("teacher4");
          More_Class_Stu();
          Less_Class_stu();
          
     }
     //   根据学号student_id查询学生选课编号course_id和名称title
     public static void Stu_id_Cour_id_tit(String stuid) throws IOException{
          HTable stable=new HTable(conf, stableName);
          HTable ctable=new HTable(conf, ctableName);
          List<String> courseid=new ArrayList<String>();
          Scan scan=new Scan();
          System.out.println("scan student通过id查找..........................................................");
          Filter filter=new RowFilter(CompareFilter.CompareOp.EQUAL,
                    new BinaryComparator(Bytes.toBytes(stuid)));
          scan.setFilter(filter);
          ResultScanner resultScanner=stable.getScanner(scan);
          for (Result result : resultScanner) {
//             System.out.println(result);
               for (KeyValue keyValue : result.raw()) { 
                    
                      String courfamilier=new String(keyValue.getFamily());
//                  System.out.println(courfamilier);
                    if(courfamilier.equals("course")){
                       System.out.println("列：" + new String(keyValue.getFamily())
                                   +"===列限定符"+new String(keyValue.getQualifier())
                                  + "====值:" + new String(keyValue.getValue()));
                     String courQualifier=new String(keyValue.getQualifier());
                     courseid.add(courQualifier);
                    }
               }
          }
          resultScanner.close();
          System.out.println("courseid 该学生选的课程有："+courseid);
          
          System.out.println("scan course 课程名称...........................................................");
          for (int i = 0; i < courseid.size(); i++) {
                    System.out.println("课程号："+courseid.get(i));
                    Filter filter2=new RowFilter(CompareFilter.CompareOp.EQUAL,
                              new BinaryComparator(Bytes.toBytes(courseid.get(i))));
                    scan.setFilter(filter2);
                    ResultScanner resultScanner2=ctable.getScanner(scan);
                    for (Result result : resultScanner2) {
//                       System.out.println(result);
                         for (KeyValue keyValue : result.raw()) { 
                              if(new String(keyValue.getQualifier()).equals("tittle")){
                                 System.out.println("====值:" + new String(keyValue.getValue()));
                              }
                         }
                    }
                    resultScanner.close();
          }
     }
     
     //   根据课程号course_id查询选课学生学号student_id和姓名name
     public static void Cour_id_Ttu_id_name(String courid) throws IOException{
          HTable stable=new HTable(conf, stableName);
          HTable ctable=new HTable(conf, ctableName);
          List<String> studentid=new ArrayList<String>();
          Scan scan=new Scan();
          System.out.println("scan course通过id查找..........................................................");
          Filter filter=new RowFilter(CompareFilter.CompareOp.EQUAL,
                    new BinaryComparator(Bytes.toBytes(courid)));
          scan.setFilter(filter);
          ResultScanner resultScanner=ctable.getScanner(scan);
          for (Result result : resultScanner) {
//             System.out.println(result);
               for (KeyValue keyValue : result.raw()) { 
                    
                      String courfamilier=new String(keyValue.getFamily());
//                  System.out.println(courfamilier);
                    if(courfamilier.equals("student")){
                       System.out.println("列：" + new String(keyValue.getFamily())
                                   +"===列限定符"+new String(keyValue.getQualifier())
                                  + "====值:" + new String(keyValue.getValue()));
                     String courQualifier=new String(keyValue.getQualifier());
                     studentid.add(courQualifier);
                    }
               }
          }
          resultScanner.close();
          System.out.println("courseid 该学生选的课程有："+studentid);
          
          System.out.println("scan course 课程名称...........................................................");
          for (int i = 0; i < studentid.size(); i++) {
                    System.out.println("课程号："+studentid.get(i));
                    Filter filter2=new RowFilter(CompareFilter.CompareOp.EQUAL,
                              new BinaryComparator(Bytes.toBytes(studentid.get(i))));
                    scan.setFilter(filter2);
                    ResultScanner resultScanner2=stable.getScanner(scan);
                    for (Result result : resultScanner2) {
//                       System.out.println(result);
                         for (KeyValue keyValue : result.raw()) { 
                              if(new String(keyValue.getQualifier()).equals("name")){
                                 System.out.println("====值:" + new String(keyValue.getValue()));
                              }
                         }
                    }
                    resultScanner.close();
          }
     }
     
     
     //   根据教员号teacher_id查询该教员所上课程编号course_id和名称title
     @SuppressWarnings("deprecation")
     public static void Tea_id_Cour_tit(String teacherid) throws IOException{
//        HTable stable = new HTable(conf, stableName);
          HTable ctable = new HTable(conf, ctableName);
          List<String> plist = new ArrayList<String>();
          String teacher="";
          Filter filter = new ValueFilter(CompareFilter.CompareOp.EQUAL, new SubstringComparator(teacherid));
          Scan scan = new Scan();
          scan.setFilter(filter);
          ResultScanner resultScanner = ctable.getScanner(scan);
          for (Result result : resultScanner) {
               System.out.println("pro rowkey:" + new String(result.getRow()));
               teacher=new String(result.getRow());
               for (KeyValue keyValue : result.raw()) {
                    System.out.println(
                              "    列:" + new String(keyValue.getQualifier()) + ",值:" + new String(keyValue.getValue()));
               }
          }
          resultScanner.close();
          
          
          Filter filter2=new RowFilter(CompareFilter.CompareOp.EQUAL,
                    new BinaryComparator(Bytes.toBytes(teacher)));
          scan.setFilter(filter2);
          ResultScanner resultScanner2=ctable.getScanner(scan);
          for (Result result : resultScanner2) {
//             System.out.println(result);
               for (KeyValue keyValue : result.raw()) { 
                    if(new String(keyValue.getQualifier()).equals("tittle")){
                       System.out.println("====值:" + new String(keyValue.getValue()));
              
                    }
               }
          }
          resultScanner.close();
     }
     
     //   上课最多的学生
     @SuppressWarnings({ "unchecked", "unused" })
     public static void More_Class_Stu() throws IOException{
          HTable stable=new HTable(conf, stableName);
          HTable ctable=new HTable(conf, ctableName);
          List<String> studentidlist=new ArrayList<String>();
          int num=0;
          String studentid="";
          Scan scan = new Scan();
          ResultScanner resultScanner = stable.getScanner(scan);
          for (Result result : resultScanner) {
//             System.out.println(result);
            studentidlist.add(new String(result.getRow()));
            int tempnum=0;
               for (KeyValue keyValue : result.raw()) { 
                    if(new String(keyValue.getFamily()).equals("course")){
                         tempnum++;
                    }
               }
               if(tempnum>num){
                    num=tempnum;
               }
          }
          
          System.out.println("stuidlist:"+studentidlist);
          System.out.println("morenum:"+num);
          
          System.out.println("scan studernt通过id查找..........................................................");
          Map<String, Integer> coursecount= new HashMap<String,Integer>();
          for (String stuid : studentidlist) {
               Get get=new Get(Bytes.toBytes(stuid));
               get.addFamily(Bytes.toBytes("course"));
               Result result=stable.get(get);
               int count=0;
               for(KeyValue keyValue:result.raw()){
                    count++;
               }
               coursecount.put(stuid, count);
          }
          
          for(Map.Entry<String, Integer> entry:coursecount.entrySet()){
               if(num==entry.getValue()){
                    studentid=entry.getKey();
                    System.out.println("上课最多的学生为："+studentid+",上了"+num+"门课。");
               }
          }
//        Filter filter=new RowFilter(CompareFilter.CompareOp.EQUAL,
//                  new BinaryComparator(Bytes.toBytes(courid)));
//        scan.setFilter(filter);
//        ResultScanner resultScanner=ctable.getScanner(scan);
//        for (Result result : resultScanner) {
//             System.out.println(result);
//             for (KeyValue keyValue : result.raw()) { 
//                  
//                    String courfamilier=new String(keyValue.getFamily());
//                  System.out.println(courfamilier);
//                  if(courfamilier.equals("student")){
//                     System.out.println("列：" + new String(keyValue.getFamily())
//                                 +"===列限定符"+new String(keyValue.getQualifier())
//                                + "====值:" + new String(keyValue.getValue()));
//                   String courQualifier=new String(keyValue.getQualifier());
//                   studentid.add(courQualifier);
//                  }
//             }
//        }
//        resultScanner.close();
//        System.out.println("courseid 该学生选的课程有："+studentid);
     }
     
     
     //   上课最少的学生
     public static void Less_Class_stu() throws IOException{
          HTable stable=new HTable(conf, stableName);
          HTable ctable=new HTable(conf, ctableName);
          List<String> studentidlist=new ArrayList<String>();
          int num=0;
          String studentid="";
          Scan scan = new Scan();
          ResultScanner resultScanner = stable.getScanner(scan);
          for (Result result : resultScanner) {
//             System.out.println(result);
            studentidlist.add(new String(result.getRow()));
            int tempnum=0;
               for (KeyValue keyValue : result.raw()) { 
                    if(new String(keyValue.getFamily()).equals("course")){
                         tempnum++;
                    }
               }
               num=tempnum;
          }    
          
          ResultScanner resultScanner2 = stable.getScanner(scan);
          for (Result result : resultScanner2) {
//             System.out.println(result);
//            studentidlist.add(new String(result.getRow()));
            int tempnum=0;
               for (KeyValue keyValue : result.raw()) { 
                    if(new String(keyValue.getFamily()).equals("course")){
                         tempnum++;
                    }
               }
               if(tempnum<num){
                    num=tempnum;
               }
          }
          System.out.println("stuidlist:"+studentidlist);
          System.out.println("lessnum:"+num);
          
          System.out.println("scan studernt通过id查找..........................................................");
          Map<String, Integer> coursecount= new HashMap<String,Integer>();
          for (String stuid : studentidlist) {
               Get get=new Get(Bytes.toBytes(stuid));
               get.addFamily(Bytes.toBytes("course"));
               Result result=stable.get(get);
               int count=0;
               for(KeyValue keyValue:result.raw()){
                    count++;
               }
               coursecount.put(stuid, count);
          }    
          for(Map.Entry<String, Integer> entry:coursecount.entrySet()){
//             lessnum=entry.getValue();
               if(num==entry.getValue()){
//                  number=entry.getValue();
                    studentid=entry.getKey();
                    System.out.println("上课最少的学生为："+studentid+",上了"+num+"门课。");
               }
          }
     
     }
     
}