32

实验4

NoSQL和关系数据库的操作比较

 

1.实验目的

1)理解四种数据库(MySQL、HBase、Redis和MongoDB)的概念以及不同点

2)熟练使用四种数据库操作常用的Shell命令;

3)熟悉四种数据库操作常用的Java API

2.实验平台

1操作系统:Linux(建议Ubuntu16.04Ubuntu18.04);

2Hadoop版本:3.1.3

3MySQL版本:5.6;

4HBase版本:2.2.2

5Redis版本5.0.5

6MongoDB版本4.0.16

7JDK版本:1.8

8Java IDEEclipse;

3.实验步骤

(一) MySQL数据库操作

学生表如14-7所示。

14-7 学生表Student

Name

English

Math

Computer

zhangsan

69

86

77

lisi

55

100

88

  1. 根据上面给出的Student,在MySQL数据库中完成如下操作:

(1)在MySQL中创建Student表,并录入数据;

创建Student表的SQL语句如下:

 

CREATE TABLE student(

    NAME VARCHAR(30) NOT NULL,

    English TINYINT UNSIGNED NOT NULL,

    Math TINYINT UNSIGNED NOT NULL,

    Computer TINYINT UNSIGNED NOT NULL

);

————————————————

 

  Student表中插入两条记录的SQL语句如下:

insert into student values("zhangsan",69,86,77);

insert into student values("lisi",55,100,88);

                          

(2)SQL语句输出Student表中的所有记录;

输出Student表中的所有记录的SQL语句如下:

select * from student;

上述SQL语句执行后的结果截图如图所示。

 

 

(3)查询zhangsanComputer成绩

查询zhangsanComputer成绩的SQL语句如下:

select name , Computer from student where name = "zhangsan";

上述语句执行后的结果截图如图所示。

 

4修改lisiMath成绩改为95。

        修改lisiMath成绩的SQL语句如下:

update student set Math=95 where name="lisi";

上述SQL语句执行结果截图如图所示。

 

2.根据上面已经设计出的Student,使用MySQLJAVA客户端编程实现以下操作:

1)向Student表中添加如下所示的一条记录:

scofield

45

89

100

package com.xusheng.nosql.mysql;

import java.sql.*;

public class mysql_test {

 

    /**

     * @param xusheng

     */

    //JDBC DRIVER and DB

    static final String  DRIVER="com.mysql.jdbc.Driver";

    static final String DB="jdbc:mysql://localhost/student?useSSL=false";

    //Database auth

    static final String USER="root";

    static final String PASSWD="root";

 

    public static void main(String[] args) {

        // TODO Auto-generated method stub

        Connection conn=null;

        Statement stmt=null;

        try {

            //加载驱动程序

            Class.forName(DRIVER);

            System.out.println("Connecting to a selected database...");

            //打开一个连接

            conn=DriverManager.getConnection(DB, USER, PASSWD);

            //执行一个查询

            stmt=conn.createStatement();

            String sql="insert into student values('scofield',45,89,100)";

            stmt.executeUpdate(sql);

            System.out.println("Inserting records into the table successfully!");

        } catch (ClassNotFoundException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }catch (SQLException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }finally

        {

            if(stmt!=null)

                try {

                    stmt.close();

                } catch (SQLException e) {

                    // TODO Auto-generated catch block

                    e.printStackTrace();

                }

            if(conn!=null)

                try {

                    conn.close();

                } catch (SQLException e) {

                    // TODO Auto-generated catch block

                    e.printStackTrace();

                }

        }

    }

}

 

 

 

2获取scofieldEnglish成绩信息

package com.xusheng.nosql.mysql;

import java.sql.*;

public class mysql_qurty {

 

    /**

     * @param args

     */

    //JDBC DRIVER and DB

    static final String  DRIVER="com.mysql.jdbc.Driver";

    static final String DB="jdbc:mysql://localhost/student?useSSL=false";

    //Database auth

    static final String USER="root";

    static final String PASSWD="root";

 

    public static void main(String[] args) {

        // TODO Auto-generated method stub

        Connection conn=null;

        Statement stmt=null;

        ResultSet rs=null;

        try {

            //加载驱动程序

            Class.forName(DRIVER);

            System.out.println("Connecting to a selected database...");

            //打开一个连接

            conn=DriverManager.getConnection(DB, USER, PASSWD);

            //执行一个查询

            stmt=conn.createStatement();

            String sql="select name,English from student where name='scofield' ";

            //获得结果集

            rs=stmt.executeQuery(sql);

            System.out.println("name"+"\t\t"+"English");

            while(rs.next())

            {

                System.out.print(rs.getString(1)+"\t\t");

                System.out.println(rs.getInt(2));

            }

        } catch (ClassNotFoundException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }catch (SQLException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }finally

        {

            if(rs!=null)

                try {

                    rs.close();

                } catch (SQLException e1) {

                    // TODO Auto-generated catch block

                    e1.printStackTrace();

                }

            if(stmt!=null)

                try {

                    stmt.close();

                } catch (SQLException e) {

                    // TODO Auto-generated catch block

                    e.printStackTrace();

                }

            if(conn!=null)

                try {

                    conn.close();

                } catch (SQLException e) {

                    // TODO Auto-generated catch block

                    e.printStackTrace();

                }

        }

    }

}

 

 

(二)HBase数据库操作

学生表Student如表14-8所示。

14-8 学生表Student

     name

score

English

Math

Computer

zhangsan

69

86

77

lisi

55

100

88

  1. 根据上面给出的学生表Student的信息执行如下操作:

(1)Hbase Shell命令创建学生Student

create 'student','score'

Student表中插入上面表格数据的命令如下:

 

put 'student','zhangsan','score:English','69'

put 'student','zhangsan','score:Math','86'

put 'student','zhangsan','score:Computer','77'

put 'student','lisi','score:English','55'

put 'student','lisi','score:Math','100'

put 'student','lisi','score:Computer','88'

      

                        

(2)用scan命令浏览Student表的相关信息

scan指令浏览Student表相关信息的命令如下:

scan 'student'

 

(3)查询zhangsan的Computer成绩

查询zhangsanComputer成绩的命令如下:

get 'student','zhangsan','score:Computer'

 

4)修改lisi的Math成绩,95。

修改lisiMath成绩的命令如下:

put 'student','lisi','score:Math','95'

 

2.根据上面已经设计出的Student表用HBase API编程实现以下操作:

1)添加数据:English:45  Math:89 Computer:100

scofield

45

89

100

实现添加数据的Java代码如下:

 

package com.xusheng.nosql.hbase;

 

import java.io.IOException;

import org.apache.hadoop.conf.Configuration;

import org.apache.hadoop.hbase.HBaseConfiguration;

import org.apache.hadoop.hbase.TableName;

import org.apache.hadoop.hbase.client.Admin;

import org.apache.hadoop.hbase.client.Connection;

import org.apache.hadoop.hbase.client.ConnectionFactory;

import org.apache.hadoop.hbase.client.Put;

import org.apache.hadoop.hbase.client.Table;

 

public class hbase_insert {

 

    /**

     * @param xusheng

     */

    public static Configuration configuration;

    public static Connection connection;

    public static Admin admin;

    public static void main(String[] args) {

        // TODO Auto-generated method stub

        configuration  = HBaseConfiguration.create();

        //configuration.set("hbase.rootdir","hdfs://localhost:9000/hbase");

        //configuration.set("hbase.rootdir","hdfs://hadoop102:8020/HBase");

        configuration.set("hbase.zookeeper.quorum","hadoop102,hadoop103,hadoop104");

 

        try{

            connection = ConnectionFactory.createConnection(configuration);

            admin = connection.getAdmin();

        }catch (IOException e){

            e.printStackTrace();

        }

        try {

            insertRow("student","scofield","score","English","45");

            insertRow("student","scofield","score","Math","89");

            insertRow("student","scofield","score","Computer","100");

        } catch (IOException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

        close();

    }

    public static void insertRow(String tableName,String rowKey,String colFamily,

                                 String col,String val) throws IOException {

        Table table = connection.getTable(TableName.valueOf(tableName));

        Put put = new Put(rowKey.getBytes());

        put.addColumn(colFamily.getBytes(), col.getBytes(), val.getBytes());

        table.put(put);

        table.close();

    }

    public static void close(){

        try{

            if(admin != null){

                admin.close();

            }

            if(null != connection){

                connection.close();

            }

        }catch (IOException e){

            e.printStackTrace();

        }

    }

}

 

                          

2获取scofieldEnglish成绩信息

Java代码如下:

 

package com.xusheng.nosql.hbase;

 

import java.io.IOException;

import org.apache.hadoop.conf.Configuration;

import org.apache.hadoop.hbase.Cell;

import org.apache.hadoop.hbase.CellUtil;

import org.apache.hadoop.hbase.HBaseConfiguration;

import org.apache.hadoop.hbase.TableName;

import org.apache.hadoop.hbase.client.Admin;

import org.apache.hadoop.hbase.client.Connection;

import org.apache.hadoop.hbase.client.ConnectionFactory;

import org.apache.hadoop.hbase.client.Get;

import org.apache.hadoop.hbase.client.Put;

import org.apache.hadoop.hbase.client.Result;

import org.apache.hadoop.hbase.client.Table;

 

public class hbase_query {

 

    /**

     * @param args

     */

    public static Configuration configuration;

    public static Connection connection;

    public static Admin admin;

    public static void main(String[] args) {

        // TODO Auto-generated method stub

        configuration  = HBaseConfiguration.create();

        //configuration.set("hbase.rootdir","hdfs://localhost:9000/hbase");

        //configuration.set("hbase.rootdir","hdfs://hadoop102:8020/HBase");

        configuration.set("hbase.zookeeper.quorum","hadoop102,hadoop103,hadoop104");

 

        try{

            connection = ConnectionFactory.createConnection(configuration);

            admin = connection.getAdmin();

        }catch (IOException e){

            e.printStackTrace();

        }

        try {

            getData("student","scofield","score","English");

        } catch (IOException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

        close();

    }

    public static void getData(String tableName,String rowKey,String colFamily,

                               String col)throws  IOException{

        Table table = connection.getTable(TableName.valueOf(tableName));

        Get get = new Get(rowKey.getBytes());

        get.addColumn(colFamily.getBytes(),col.getBytes());

        Result result = table.get(get);

        showCell(result);

        table.close();

    }

    public static void showCell(Result result){

        Cell[] cells = result.rawCells();

        for(Cell cell:cells){

            System.out.println("RowName:"+new String(CellUtil.cloneRow(cell))+" ");

            System.out.println("Timetamp:"+cell.getTimestamp()+" ");

            System.out.println("column Family:"+new String(CellUtil.cloneFamily(cell))+" ");

            System.out.println("row Name:"+new String(CellUtil.cloneQualifier(cell))+" ");

            System.out.println("value:"+new String(CellUtil.cloneValue(cell))+" ");

        }

    }

    public static void close(){

        try{

            if(admin != null){

                admin.close();

            }

            if(null != connection){

                connection.close();

            }

        }catch (IOException e){

            e.printStackTrace();

        }

    }

}

 

(三)Redis数据库操作

Student键值对如下:

zhangsan:

English: 69

Math: 86

Computer: 77

lisi:

English: 55

Math: 100

Computer: 88

 

1. 根据上面给出的键值对,完成如下操作:

1Redis的哈希结构设计出学生表Student键值可以用student.zhangsan和student.lisi来表示两个键值属于同一个表);   

插入上述键值对的命令如下:

 

hset student.zhangsan English 69

hset student.zhangsan Math 86

hset student.zhangsan Computer 77

hset student.lisi English 55

hset student.lisi Math 100

hset student.lisi Computer 88

2hgetall命令分别输出zhangsanlisi的成绩信息

查询zhangsan成绩信息的命令如下:

hgetall student.zhangsan

 

查询lisi成绩信息的命令如下:

hgetall student.lisi

 

3hget命令查询zhangsan的Computer成绩

查询zhangsanComputer成绩的命令如下:

hget student.zhangsan Computer

 

4)修改lisi的Math成绩,95

修改lisiMath成绩的命令如下:

hset student.lisi Math 95

 

2.根据上面已经设计出的学生表Student,RedisJAVA客户端编程(jedis),实现如下操作:

1)添加数据:English:45  Math:89 Computer:100

该数据对应的键值对形式如下:

scofield:

English: 45

Math: 89

Computer: 100

该数据对应的键值对形式如下:

scofield:

English: 45

Math: 89

Computer: 100

完成添加数据操作的Java代码如下:

 

package com.xusheng.nosql.redis;

import java.util.Map;

import redis.clients.jedis.Jedis;

 

public class jedis_test {

 

    /**

     * @param args

     */

    public static void main(String[] args) {

        // TODO Auto-generated method stub

        Jedis jedis = new Jedis("localhost");

        jedis.hset("student.scofield", "English","45");

        jedis.hset("student.scofield", "Math","89");

        jedis.hset("student.scofield", "Computer","100");

        Map<String,String>  value = jedis.hgetAll("student.scofield");

        for(Map.Entry<String, String> entry:value.entrySet())

        {

            System.out.println(entry.getKey()+":"+entry.getValue());

        }

    }

}

 

 

2获取scofieldEnglish成绩信息

获取scofieldEnglish成绩信息的Java代码如下:

 

package com.xusheng.nosql.redis;

 

import java.util.Map;

import redis.clients.jedis.Jedis;

 

public class jedis_query {

 

    /**

     * @param xusheng

     */

    public static void main(String[] args) {

        // TODO Auto-generated method stub

        Jedis jedis = new Jedis("localhost");

        String value=jedis.hget("student.scofield", "English");

        System.out.println("scofield's English score is:    "+value);

    }

}

 

(四)MongoDB数据库操作

Student文档如下:

{

“name”: “zhangsan”,

“score”: {

“English”: 69,

“Math”: 86,

“Computer”: 77

}

}

{

“name”: “lisi”,

“score”: {

“English”: 55,

“Math”: 100,

“Computer”: 88

}

}

 

1.根据上面给出的文档,完成如下操作:

1MongoDB Shell设计出student集合

 

(2)find()方法输出两个学生的信息

 

(3)find()方法查询zhangsan所有成绩(只显示score)

 

4)修改lisi的Math成绩,95

 

2.根据上面已经设计出的Student集合,用MongoDBJava客户端编程,实现如下操作:

1添加数据:English:45 Math:89  Computer:100

与上述数据对应的文档形式如下:

{

“name”: “scofield”,

“score”: {

“English”: 45,

“Math”: 89,

“Computer”: 100

}

}

   


MongoClient mongoClient = new MongoClient("127.0.0.1", 27017);

DB db = mongoClient.getDB("test");

DBCollection collection = db.getCollection("student");

DBObject doc = new BasicDBObject();

doc.put("name", "scofield");

Map<String,String> map=new HashMap<String, String>();

map.put("english","45");

map.put("math","89");

map.put("computer","100");

doc.put("score",map);

db.getCollection("student").insert(doc);

 

2)获取scofield所有成绩成绩信息(只显示score)

 

MongoClient mongoClient = new MongoClient("127.0.0.1", 27017);

DB db = mongoClient.getDB("test");

DBCollection collection = db.getCollection("student");

DBObject doc = new BasicDBObject();

doc.put("name","scofield");

DBCursor cursor = collection.find(doc);
try{

     while(cursor.hasNext()){

         System.out.println("student集合所拥有的name--[" + cursor.next().get("name") + "]");

     }

 }finally{

     cursor.close();

 }

 System.out.println("student集合中的记录数为----------->" + cursor.count());

 System.out.println("student集合数据格式化后的JSON串为-->" + JSON.serialize(cursor));

 

 

posted @ 2025-01-06 23:31  恋恋恋白  阅读(21)  评论(0)    收藏  举报