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

实验要求

只给出这四种数据库的JAVA客户端编程的相关代码

具体代码

导入依赖：

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.33</version>
</dependency>

<dependency>
    <groupId>redis.clients</groupId>
    <artifactId>jedis</artifactId>
    <version>3.7.0</version>
</dependency>

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-mongodb</artifactId>
</dependency>

<dependency>
    <groupId>org.apache.hbase</groupId>
    <artifactId>hbase-shaded-client</artifactId>
    <version>${hbase.version}</version>
</dependency>

 Mysql操作：

StudentJDBCOperation

package com.example.mysql;

import java.sql.*;

public class StudentJDBCOperation {
    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        // 数据库连接信息
        String url = "jdbc:mysql://localhost:3306/student_db";
        String user = "root";
        String password = "123456";

        try {
            // 1. 加载JDBC驱动
            Class.forName("com.mysql.cj.jdbc.Driver");

            // 2. 建立数据库连接
            conn = DriverManager.getConnection(url, user, password);
            System.out.println("数据库连接成功！");

            // 3. 创建Statement对象
            stmt = conn.createStatement();

            // (1) 向Student表中添加新记录
            String insertSQL = "INSERT INTO Student (Name, English, Math, Computer) VALUES ('scofield', 45, 89, 100)";
            int rowsAffected = stmt.executeUpdate(insertSQL);
            System.out.println("插入记录成功，影响行数: " + rowsAffected);

            // (2) 获取scofield的English成绩信息
            String selectSQL = "SELECT Name, English FROM Student WHERE Name = 'scofield'";
            rs = stmt.executeQuery(selectSQL);

            // 处理查询结果
            while (rs.next()) {
                String name = rs.getString("Name");
                int englishScore = rs.getInt("English");
                System.out.println("学生姓名: " + name + ", English成绩: " + englishScore);
            }

        } catch (ClassNotFoundException e) {
            System.out.println("JDBC驱动加载失败！");
            e.printStackTrace();
        } catch (SQLException e) {
            System.out.println("数据库操作失败！");
            e.printStackTrace();
        } finally {
            // 关闭资源
            try {
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
                if (conn != null) conn.close();
                System.out.println("数据库连接已关闭！");
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Hbase操作：

HBaseStudentOperation

package com.example.hbase;

import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.hbase.HBaseConfiguration;
import org.apache.hadoop.hbase.TableName;
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.Get;
import org.apache.hadoop.hbase.client.Table;
import org.apache.hadoop.hbase.client.Result;
import org.apache.hadoop.hbase.util.Bytes;

import java.io.IOException;

public class HBaseStudentOperation {

    // 表名和列族名常量
    private static final String TABLE_NAME = "Student";
    private static final String COLUMN_FAMILY = "score";
    private static final String ENGLISH_COL = "English";
    private static final String MATH_COL = "Math";
    private static final String COMPUTER_COL = "Computer";

    public static void main(String[] args) {
        Connection connection = null;
        Table table = null;

        try {
            // 1. 创建HBase配置
            Configuration config = HBaseConfiguration.create();
            // 设置HBase配置，根据实际情况修改
            config.set("hbase.zookeeper.quorum", "node1,node2,node3");

            // 2. 创建连接
            connection = ConnectionFactory.createConnection(config);

            // 3. 获取表对象
            table = connection.getTable(TableName.valueOf(TABLE_NAME));

            // (1) 添加scofield的数据
            addStudentData(table, "scofield", 45, 89, 100);
            System.out.println("成功添加scofield的数据");

            // (2) 获取scofield的English成绩信息
            getEnglishScore(table, "scofield");

        } catch (IOException e) {
            System.err.println("HBase操作失败: " + e.getMessage());
            e.printStackTrace();
        } finally {
            // 关闭资源
            try {
                if (table != null) table.close();
                if (connection != null) connection.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 添加学生数据
     */
    private static void addStudentData(Table table, String name, int english, int math, int computer)
            throws IOException {

        // 创建Put对象，指定行键
        Put put = new Put(Bytes.toBytes(name));

        // 添加各科成绩
        put.addColumn(
                Bytes.toBytes(COLUMN_FAMILY),
                Bytes.toBytes(ENGLISH_COL),
                Bytes.toBytes(String.valueOf(english))
        );
        put.addColumn(
                Bytes.toBytes(COLUMN_FAMILY),
                Bytes.toBytes(MATH_COL),
                Bytes.toBytes(String.valueOf(math))
        );
        put.addColumn(
                Bytes.toBytes(COLUMN_FAMILY),
                Bytes.toBytes(COMPUTER_COL),
                Bytes.toBytes(String.valueOf(computer))
        );

        // 执行插入操作
        table.put(put);
    }

    /**
     * 获取学生的English成绩
     */
    private static void getEnglishScore(Table table, String name) throws IOException {
        // 创建Get对象，指定行键
        Get get = new Get(Bytes.toBytes(name));

        // 只获取English列，提高查询效率
        get.addColumn(Bytes.toBytes(COLUMN_FAMILY), Bytes.toBytes(ENGLISH_COL));

        // 执行查询
        Result result = table.get(get);

        // 处理结果
        if (!result.isEmpty()) {
            byte[] englishValue = result.getValue(
                    Bytes.toBytes(COLUMN_FAMILY),
                    Bytes.toBytes(ENGLISH_COL)
            );

            if (englishValue != null) {
                String score = Bytes.toString(englishValue);
                System.out.println("学生 " + name + " 的English成绩为: " + score);
            } else {
                System.out.println("未找到学生 " + name + " 的English成绩");
            }
        } else {
            System.out.println("未找到学生: " + name);
        }
    }

}

Redis操作：

JedisStudentOperation

package com.example.redis;

import redis.clients.jedis.Jedis;
import redis.clients.jedis.JedisPool;
import redis.clients.jedis.JedisPoolConfig;

import java.util.HashMap;
import java.util.Map;

public class JedisStudentOperation {

    // Redis连接配置
    private static final String REDIS_HOST = "192.168.88.151";
    private static final int REDIS_PORT = 6379;
    private static final String REDIS_PASSWORD = "123456";

    // 连接池配置
    private static final int MAX_TOTAL = 8;
    private static final int MAX_IDLE = 8;
    private static final int MIN_IDLE = 0;
    private static final long MAX_WAIT_MILLIS = 1000;

    private JedisPool jedisPool;

    public static void main(String[] args) {
        JedisStudentOperation operation = new JedisStudentOperation();
        operation.init();
        operation.executeOperations();
        operation.close();
    }

    /**
     * 初始化Jedis连接池
     */
    public void init() {
        try {
            // 配置连接池
            JedisPoolConfig poolConfig = new JedisPoolConfig();
            poolConfig.setMaxTotal(MAX_TOTAL);
            poolConfig.setMaxIdle(MAX_IDLE);
            poolConfig.setMinIdle(MIN_IDLE);
            poolConfig.setMaxWaitMillis(MAX_WAIT_MILLIS);
            poolConfig.setTestOnBorrow(true);
            poolConfig.setTestOnReturn(true);

            // 创建连接池
            jedisPool = new JedisPool(poolConfig, REDIS_HOST, REDIS_PORT, 1000, REDIS_PASSWORD);
            System.out.println("Jedis连接池初始化成功");
        } catch (Exception e) {
            System.err.println("Jedis连接池初始化失败: " + e.getMessage());
            e.printStackTrace();
        }
    }

    /**
     * 执行Redis操作
     */
    public void executeOperations() {
        Jedis jedis = null;
        try {
            // 从连接池获取连接
            jedis = jedisPool.getResource();

            System.out.println("开始执行Redis学生成绩操作...");

            // (1) 添加scofield的数据
            addStudentData(jedis, "scofield", 45, 89, 100);

            // (2) 获取scofield的English成绩信息
            getEnglishScore(jedis, "scofield");

            System.out.println("Redis学生成绩操作完成！");

        } catch (Exception e) {
            System.err.println("Redis操作失败: " + e.getMessage());
            e.printStackTrace();
        } finally {
            // 释放连接回连接池
            if (jedis != null) {
                jedis.close();
            }
        }
    }

    /**
     * 添加学生数据到Redis
     */
    private void addStudentData(Jedis jedis, String studentName, int english, int math, int computer) {
        try {
            String studentKey = "student:" + studentName;

            // 使用HashMap存储学生成绩
            Map<String, String> studentScores = new HashMap<>();
            studentScores.put("English", String.valueOf(english));
            studentScores.put("Math", String.valueOf(math));
            studentScores.put("Computer", String.valueOf(computer));

            // 使用hset命令添加哈希字段
            jedis.hset(studentKey, studentScores);

            System.out.println("成功添加学生 " + studentName + " 的数据:");
            System.out.println("  English: " + english);
            System.out.println("  Math: " + math);
            System.out.println("  Computer: " + computer);

        } catch (Exception e) {
            System.err.println("添加学生数据失败: " + e.getMessage());
            throw e;
        }
    }

    /**
     * 获取学生的English成绩
     */
    private void getEnglishScore(Jedis jedis, String studentName) {
        try {
            String studentKey = "student:" + studentName;

            // 使用hget命令获取指定字段
            String englishScore = jedis.hget(studentKey, "English");

            if (englishScore != null) {
                System.out.println("学生 " + studentName + " 的 English 成绩为: " + englishScore);
            } else {
                System.out.println("未找到学生 " + studentName + " 的 English 成绩");
            }

        } catch (Exception e) {
            System.err.println("获取English成绩失败: " + e.getMessage());
            throw e;
        }
    }

    /**
     * 关闭连接池
     */
    public void close() {
        if (jedisPool != null) {
            jedisPool.close();
            System.out.println("Jedis连接池已关闭");
        }
    }

}

MongoDB操作：

MongoDBStudentOperation

package com.example.mongodb;

import com.mongodb.client.MongoClient;
import com.mongodb.client.MongoClients;
import com.mongodb.client.MongoCollection;
import com.mongodb.client.MongoDatabase;
import com.mongodb.client.model.Filters;
import com.mongodb.client.model.Projections;
import org.bson.Document;
import org.bson.conversions.Bson;



public class MongoDBStudentOperation {

    // MongoDB连接配置
    private static final String CONNECTION_STRING = "mongodb://localhost:27017";
    private static final String DATABASE_NAME = "test";
    private static final String COLLECTION_NAME = "student";

    public static void main(String[] args) {
        MongoClient mongoClient = null;

        try {
            // 1. 创建MongoDB客户端连接
            mongoClient = MongoClients.create(CONNECTION_STRING);
            System.out.println("成功连接到MongoDB");

            // 2. 获取数据库和集合
            MongoDatabase database = mongoClient.getDatabase(DATABASE_NAME);
            MongoCollection<Document> collection = database.getCollection(COLLECTION_NAME);

            System.out.println("开始执行MongoDB学生成绩操作...");

            // (1) 添加scofield的数据
            addStudentData(collection, "scofield", 45, 89, 100);

            // (2) 获取scofield的所有成绩信息(只显示score列)
            getStudentScores(collection, "scofield");

            System.out.println("MongoDB学生成绩操作完成！");

        } catch (Exception e) {
            System.err.println("MongoDB操作失败: " + e.getMessage());
            e.printStackTrace();
        } finally {
            // 关闭连接
            if (mongoClient != null) {
                mongoClient.close();
                System.out.println("MongoDB连接已关闭");
            }
        }
    }

    /**
     * 添加学生数据到MongoDB
     */
    private static void addStudentData(MongoCollection<Document> collection,
                                       String studentName,
                                       int english,
                                       int math,
                                       int computer) {
        try {
            // 创建score子文档
            Document scoreDocument = new Document()
                    .append("English", english)
                    .append("Math", math)
                    .append("Computer", computer);

            // 创建学生文档
            Document studentDocument = new Document()
                    .append("name", studentName)
                    .append("score", scoreDocument);

            // 插入文档到集合
            collection.insertOne(studentDocument);

            System.out.println("成功添加学生 " + studentName + " 的数据:");
            System.out.println("  English: " + english);
            System.out.println("  Math: " + math);
            System.out.println("  Computer: " + computer);

        } catch (Exception e) {
            System.err.println("添加学生数据失败: " + e.getMessage());
            throw e;
        }
    }

    /**
     * 获取学生的所有成绩信息（只显示score列）
     */
    private static void getStudentScores(MongoCollection<Document> collection, String studentName) {
        try {
            // 创建查询条件
            Bson filter = Filters.eq("name", studentName);

            // 创建投影，只返回score字段，不返回_id字段
            Bson projection = Projections.fields(
                    Projections.include("score"),
                    Projections.excludeId()
            );

            // 执行查询
            Document result = collection.find(filter)
                    .projection(projection)
                    .first();

            if (result != null) {
                Document score = result.get("score", Document.class);
                if (score != null) {
                    System.out.println("学生 " + studentName + " 的所有成绩:");
                    for (String key : score.keySet()) {
                        System.out.println("  " + key + ": " + score.get(key));
                    }
                } else {
                    System.out.println("未找到学生 " + studentName + " 的成绩信息");
                }
            } else {
                System.out.println("未找到学生: " + studentName);
            }

        } catch (Exception e) {
            System.err.println("获取学生成绩失败: " + e.getMessage());
            throw e;
        }
    }


}