Mysql、Hbase、Redis、MongoDB四种API操作

一、Mysql

pom.xml

<dependencies>
        <dependency>
            <groupId>org.codehaus.jettison</groupId>
            <artifactId>jettison</artifactId>
            <version>1.4.1</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.38</version>
        </dependency>
    </dependencies>

java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import org.codehaus.jettison.json.JSONArray;
import org.codehaus.jettison.json.JSONObject;
public class Mysql {
    public static void main(String[] args) {
        String driver = "com.mysql.jdbc.Driver";

//这里我的数据库名字是Test，改成你自己的数据库名
        String url = "jdbc:mysql://localhost:3306/db?uerUnicode=true&characterEncoding=UTF-8&useSSL=false";
        String user = "******";
        String pwd = "****";
        try {
            Class.forName(driver);
            Connection con = DriverManager.getConnection(url,user,pwd);
            Statement stet = con.createStatement();

             //我的数据库Test中的表tstudent，改成你自己的表
//            String sql = "insert into student values ('scofield',45,89,100)";
//
//            int count= stet.executeUpdate(sql);
//            if(count>0){
//                System.out.println("插入成功");
//            }
            String sql = "select english from student where name = 'scofield'";
            ResultSet resultSet = stet.executeQuery(sql);
            if (resultSet.next()){
               String english = resultSet.getString("english");
               System.out.println(english);

            }
            con.close();

        } catch (Exception e) {

            e.printStackTrace();// TODO: handle exception
        }
    }
}

二、HBase

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>example-hbase</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>15</maven.compiler.source>
        <maven.compiler.target>15</maven.compiler.target>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.apache.hbase</groupId>
            <artifactId>hbase-client</artifactId>
            <version>2.3.6</version>
        </dependency>
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.6</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.testng</groupId>
            <artifactId>testng</artifactId>
            <version>6.14.3</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.testng</groupId>
            <artifactId>testng</artifactId>
            <version>6.14.3</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-common</artifactId>
            <version>2.10.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-hdfs</artifactId>
            <version>2.10.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-mapreduce-client-core</artifactId>
            <version>2.10.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-client</artifactId>
            <version>2.10.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.zookeeper</groupId>
            <artifactId>zookeeper</artifactId>
            <version>3.7.0</version>
            <type>jar</type>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.1</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

java

import org.apache.hadoop.hbase.client.*;
import org.apache.hadoop.hbase.util.Bytes;
import org.testng.annotations.*;
import org.apache.hadoop.conf.*;
import org.apache.hadoop.hbase.*;

import javax.ws.rs.GET;
import java.io.IOException;
import java.nio.charset.StandardCharsets;

public class TableAdminTest {
    private static Connection connection;
    private static Admin admin;
    @BeforeTest
    public void beforeTest() throws IOException {
        Configuration configuration=HBaseConfiguration.create();
        configuration.set("hbase.zookeeper.quorum","master:2181");
        connection = ConnectionFactory.createConnection(configuration);
        admin=connection.getAdmin();
    }
    @Test
    public void creatTableTest() throws IOException {
        try {//插入的信息
//            insertRow("bigdata:Student", "scofield", "name", "name", "scofield");
//            insertRow("bigdata:Student", "scofield", "score", "English", "45");
//            insertRow("bigdata:Student", "scofield", "score", "Math", "89");
//            insertRow("bigdata:Student", "scofield", "score", "Computer", "100");
              getRow("bigdata:Student", "scofield", "score", "English");
        } catch (IOException e) {//异常处理
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    @AfterTest
    public void AfterTest() throws IOException {
        admin.close();
        connection.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 getRow(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 rs = table.get(get);
        System.out.println(new String(rs.getValue(colFamily.getBytes(),col==null?null:col.getBytes())));
        table.close();
    }
}

另外还要再在resource中添加hadoop的core-site.xml、hbase-site.xml文件、log4j.properties文件

log4j.properties文件

log4j.rootLogger=WARN, stdout, R
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
# Pattern to output the caller's file name and line number.
#log4j.appender.stdout.layout.ConversionPattern=%5p [%t] (%F:%L) - %m%n
# Print the date in ISO 8601 format
log4j.appender.stdout.layout.ConversionPattern=%d [%t] %-5p %c - %m%n
log4j.appender.R=org.apache.log4j.RollingFileAppender
log4j.appender.R.File=example.log
log4j.appender.R.MaxFileSize=100KB
# Keep one backup file
log4j.appender.R.MaxBackupIndex=1
log4j.appender.R.layout=org.apache.log4j.PatternLayout
log4j.appender.R.layout.ConversionPattern=%p %t %c - %m%n
# Print only messages of level WARN or above in the package com.foo.
log4j.logger.com.foo=WARN

三、redis

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>redisLink</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>15</maven.compiler.source>
        <maven.compiler.target>15</maven.compiler.target>
    </properties>
    <dependencies>
                <!--用于单元测试的包-->
                 <dependency>
                     <groupId>junit</groupId>
                     <artifactId>junit</artifactId>
                     <version>4.12</version>
                 </dependency>
                 <!--mysql数据库的jdbc驱动包：用于连接mysql数据库-->
                 <dependency>
                     <groupId>mysql</groupId>
                     <artifactId>mysql-connector-java</artifactId>
                     <version>8.0.18</version>
                 </dependency>
                 <!--redis数据库的连接驱动包-->
                 <dependency>
                     <groupId>redis.clients</groupId>
                     <artifactId>jedis</artifactId>
                     <version>2.9.0</version>
                 </dependency>
                 <!--数据库工具包：用于操作数据库-->
                 <dependency>
                     <groupId>commons-dbutils</groupId>
                     <artifactId>commons-dbutils</artifactId>
                     <version>1.5</version>
                 </dependency>
                 <!--对象池驱动包：用于存放我们需要池化的对象，如创建redis数据库连接池-->
                 <dependency>
                     <groupId>org.apache.commons</groupId>
                     <artifactId>commons-pool2</artifactId>
                     <version>2.5.0</version>
                 </dependency>
                 <!--c3p0数据库连接池包-->
                 <dependency>
                     <groupId>c3p0</groupId>
                     <artifactId>c3p0</artifactId>
                     <version>0.9.1.2</version>
                 </dependency>
         </dependencies>

</project>

java

import redis.clients.jedis.Jedis;

import java.util.Map;

public class RedisLinkTest {
    public static void main(String[] args) {
        //连接本地的 Redis 服务
        Jedis jedis = new Jedis("          ");
        // 如果 Redis 服务设置了密码，需要下面这行，没有就不需要
        // jedis.auth("123456");
        System.out.println("连接成功");
        //查看服务是否运行
        System.out.println("服务正在运行: "+jedis.ping());
        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())
        {
            if (entry.getKey().equals("Computer")){
                System.out.println("scofield的计算机成绩为："+entry.getValue());
            }
        }
    }
}

四、mongo

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>Mongodb</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>15</maven.compiler.source>
        <maven.compiler.target>15</maven.compiler.target>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.mongodb</groupId>
            <artifactId>mongo-java-driver</artifactId>
            <version>3.0.3</version>
        </dependency>
    </dependencies>

</project>

java

import java.util.ArrayList;
import java.util.List;
import com.mongodb.MongoClient;
import com.mongodb.MongoCredential;
import com.mongodb.ServerAddress;
import com.mongodb.client.MongoCollection;
import com.mongodb.client.MongoCursor;
import com.mongodb.client.MongoDatabase;
import org.bson.Document;

public class MongoTest {
    public static void main(String[] args){
        try {
            //连接到MongoDB服务 如果是远程连接可以替换“localhost”为服务器所在IP地址
            //ServerAddress()两个参数分别为 服务器地址 和 端口
            ServerAddress serverAddress = new ServerAddress("       ",27017);
            List<ServerAddress> addrs = new ArrayList<ServerAddress>();
            addrs.add(serverAddress);

            //MongoCredential.createScramSha1Credential()三个参数分别为 用户名 数据库名称 密码
            MongoCredential credential = MongoCredential.createScramSha1Credential("admin", "admin", "123456".toCharArray());
            List<MongoCredential> credentials = new ArrayList<MongoCredential>();
            credentials.add(credential);

            //通过连接认证获取MongoDB连接
            MongoClient mongoClient = new MongoClient(addrs,credentials);

            //连接到数据库
            MongoDatabase mongoDatabase = mongoClient.getDatabase("admin");
            System.out.println("Connect to admin successfully");
//            创建集合
//            mongoDatabase.createCollection("test");
            MongoCollection<Document> collection = mongoDatabase.getCollection("database");
            System.out.println("集合 database 选择成功");
            //插入文档
//            Document document = new Document("name","scofield").
//                    append("score",new Document("English","45").
//                            append("Math","89").
//                            append("Computer","100"));
//            List<Document> documents = new ArrayList<Document>();
//            documents.add(document);
//            collection.insertMany(documents);
//            System.out.println("文档插入成功");
            //从文档中查询
            MongoCursor<Document> cursor = collection.find(new Document("name","scofield"))
                    .projection(new Document("score",1).append("_id",0)).iterator();
            while (cursor.hasNext()){
                System.out.println(cursor.next().toJson());
            }
        } catch (Exception e) {
            System.err.println( e.getClass().getName() + ": " + e.getMessage() );
        }

    }
}