[大数据]Oracle driver and url

1.oracle driver

oracle.jdbc.OracleDriver

• Orcacle url

  • 第一种方式：

    • 通过SID的方式获取：

      查询实例的sql语句：
      select value from v$parameter where name='instance_name';
      

    • url的格式：

      jdbc:oracle:thin:@localhost:1521:orcl.a
      

  • 第二种方式：

    • 通过Server_name的方式

      查询服务的sql语句
      select value from v$parameter where name='service_names';
      

    • url格式：

      jdbc:oracle:thin:@//host:port/service_name
      

  • 第三种方式：

    • 通过RAC方式（太长不建议）

  • 以上第一种与第二种方式的区别在于：

    （1）官方推荐使用第二种，因为对于集群来说，每个节点的SID 是不一样的，但是SERVICE_NAME 确可以包含所有节点。
    （2）两个url的书写方式也不同，SID没有「@」后面没有「//」,「port」后面是「/」
    		[SID]       jdbc:oracle:thin:@localhost:1521:orcl.a
    		[ServerName]jdbc:oracle:thin:@//host:port/service_name
    

2.mysql driver

com.mysql.jdbc.Driver

• Mysql url

jdbc:mysql://127.0.0.1:3306/juggle?useSSL=false&characterEncoding=utf-8

3.Oracle driver :常用sql语句：

• 查询目前所在的数据库：

  select name from v$database;
  HELOWIN
  

• 查看数据库结构字段

  desc v$databases;
  

• 怎样查看哪些用户拥有SYSDBA、SYSOPER权限:

  select * from V$PWFILE_USERS
  

• 查看当前数据库连接用户

  Show user;
  

• 进入test数据库

  database test;
  

• 查看当前库的所有数据表：

  select TABLE_NAME from all_tables;
  

• 查询表字段

  desc all_tables;
  

• 更新数据库用户的密码

  alter user sys indentified by test;
  alter user system indentified by test;
  

• 增加数据库用户

  create user test11 identified by test default tablespace users Temporary TABLESPACE Temp;
  

• 用户授权

  grant connect,resource,dba to test11;
  grant sysdba to test11;
  

• 建表语句

  create table Person(id int,name varchar(16));
  

• 插入语句

  insert into Person values(1,'one');
  insert into Person values(2,'er');
  insert into Person values(3,'san');
  insert into Person values(4,'si');
  insert into Person values(5,'wu');
  insert into Person values(6,'liu');
  insert into Person values(7,'qi');
  insert into Person values(8,'ba');
  insert into Person values(9,'jiu');
  insert into Person values(10,'shi');
  insert into Person values(11,'shiyi');
  insert into Person values(12,'shier');
  insert into Person values(13,'shisan');
  insert into Person values(14,'shisi');
  insert into Person values(15,'shiwu');
  

3.状态存储到minio 中，相关API的使用：

 public static void isExist() throws Exception {
        boolean exists = minioClient.bucketExists(BucketExistsArgs.builder().bucket(bucket).build());
        if (!exists) {
            minioClient.makeBucket(MakeBucketArgs.builder().bucket(bucket).build());
            System.out.println("create bucket");
        } else {
            System.out.println("bucket is exist");
        }
    }

    /**
     * 将文件上传
     *
     * @throws Exception
     */
    public static void putObject(String addressPath) throws Exception {
        InputStream inputStream = new FileInputStream(addressPath);
        PutObjectArgs build = PutObjectArgs.builder()
                .bucket(bucket)
                .stream(inputStream, 266, -1)
                .object(addressPath).build();
        minioClient.putObject(build);
    }

    /**
     * 举例出所有的文件列表
     */
    public static void listObject() throws Exception {
        ListObjectsArgs build = ListObjectsArgs.builder().bucket(bucket).recursive(true).build();
        Iterator<Result<Item>> iterator = minioClient.listObjects(build).iterator();
        while (iterator.hasNext()) {
            System.out.println(iterator.next().get().objectName());
        }
    }

    /**
     * 从minio上读取文件
     */
    public static void getObjectContent() throws Exception {
        GetObjectArgs build = GetObjectArgs.builder()
                .bucket(bucket)
                .object(addressPath)
                .build();
        String line;
        StringBuilder sb = new StringBuilder();
        InputStream inputStream = minioClient.getObject(build);
        BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(inputStream));
        while ((line = bufferedReader.readLine()) != null) {
            sb.append(line).append("\n");
        }
        System.out.println(sb.toString());

    }

    /**
     * 从文件查询
     * 支持存储为json格式 或者csv格式
     * 注意： 查询语句中必须用单引号
     */
    public static void selectObjectContent() throws Exception {
        String sqlExpression = "select name from S3Object where name ='jack' limit 5";
        InputSerialization is = new InputSerialization(CompressionType.NONE, true,
                null, ',', FileHeaderInfo.USE, null, '\\', '\n');
        OutputSerialization os = new OutputSerialization(',', null, null, QuoteFields.ASNEEDED, '\n');
        HashMap<String, String> map = new HashMap<>();
        map.put("name", "jack");
        SelectResponseStream stream =
                minioClient.selectObjectContent(
                        SelectObjectContentArgs.builder()
                                .bucket(bucket)
                                .object(addressPath)
                                .sqlExpression(sqlExpression)
                                .extraQueryParams(map)
                                .inputSerialization(is)
                                .outputSerialization(os)
                                .requestProgress(true)
                                .build());

        byte[] buf = new byte[512];
        int bytesRead = stream.read(buf, 0, buf.length);
        System.out.println(new String(buf, 0, bytesRead, StandardCharsets.UTF_8));

        Stats stats = stream.stats();
        System.out.println("bytes scanned: " + stats.bytesScanned());
        System.out.println("bytes processed: " + stats.bytesProcessed());
        System.out.println("bytes returned: " + stats.bytesReturned());
        stream.close();
    }

    public static void deleteObject(String addressPath) throws Exception {
        minioClient.removeObject(
                RemoveObjectArgs.builder().bucket(bucket).object(addressPath).build());
    }

    /**
     * source zyj//Users/yingjiezhang/test1.csv: size 266 must be greater than 5242880
     * allowed minimum part size is 5MiB in multipart upload.
     * 文件必须要大于5m，代码进行了检测
     * @throws Exception
     */
    public static void composeObject() throws Exception {
        List<ComposeSource> sourceObjectList = new ArrayList<ComposeSource>();
        sourceObjectList.add(
                ComposeSource.builder().bucket(bucket).object(addressPath1).build());
        sourceObjectList.add(
                ComposeSource.builder().bucket(bucket).object(addressPath2).build());

        ObjectWriteResponse objectWriteResponse = minioClient.composeObject(
                ComposeObjectArgs.builder()
                        .bucket(bucket)
                        .object(addressPathCount)
                        .sources(sourceObjectList)
                        .build());

        System.out.println(objectWriteResponse.etag());
    }

    /**
     * filename 本地文件地址
     * object  minio中的地址
     * 将本地文件上传到minio ,和putObject之间的区别： putObject比较适合
     * @param addressPath
     * @throws Exception
     */
    public static void uploadObject(String addressPath) throws Exception {
        minioClient.uploadObject(UploadObjectArgs.builder().bucket(bucket).object(addressPath)
                .filename("/Users/yingjiezhang/test1.csv").build());
    }

    /**
     * Sets object-lock configuration in a bucket.
     * retentionDurationDays:在配置的保留规则持续时间过后，MinIO 会自动解除锁定。
     * lock model
     * COMPLIANCE: 防止任何会改变或修改对象或其锁定设置的操作。没有 MinIO 用户可以修改对象或其设置，包括 MinIO root 用户。
     *             COMPLIANCE 锁定对象强制锁定对象完全不变。在配置的保留期限过去之前，您无法更改或删除锁定。
     * GOVERNANCE: 防止非特权用户进行任何会改变或修改对象或其锁定设置的操作。对存储桶或对象拥有 s3:BypassGovernanceRetention 权限的用户可以修改对象或其锁定设置。
     *             GOVERNANCE锁定对象强制锁定对象的托管不变性，其中具有 s3:BypassGovernanceRetention 操作的用户可以修改锁定对象、更改保留持续时间或完全解除锁定。
     *
     * 
     * 注意： 锁用完不会自动释放
     */
    public static void setLockObject() throws Exception{
        ObjectLockConfiguration config = new ObjectLockConfiguration(RetentionMode.COMPLIANCE, new RetentionDurationDays(100));
        minioClient.setObjectLockConfiguration(
                SetObjectLockConfigurationArgs.builder()
                .bucket(bucket)
                .config(config)
                .build());
    }