'조작' 태그의 글 목록

MariaDB JSON 형식 데이터 사용 : MariaDB 10.2 부터 가능

참고 사이트

https://mariadb.com/kb/en/library/json-functions/
https://mariadb.com/resources/blog/json-with-mariadb-10-2/
https://bstar36.tistory.com/359

1. 버전 확인
    MariaDB [(test)]> select @@version;
    +-----------------+
    | @@version       |
    +-----------------+
    | 10.3.17-MariaDB |
    +-----------------+
    1 row in set (0.000 sec)

2. JSON 데이터 타입 지원(내부적으로 LongTEXT로 저장)
    MariaDB [test]> create table json_test (id int, data json);
    Query OK, 0 rows affected (0.015 sec)

    MariaDB [test]> show create table json_test;
    +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Table     | Create Table                                                                                                                                                                                  |
    +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | json_test | CREATE TABLE `json_test` (
      `id` int(11) DEFAULT NULL,
      `data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
    +-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.000 sec)

3. JSON 데이터 조작
   A. json_object 함수를 이용하여 key,value 형식으로 Insert
    MariaDB [test]> insert into json_test values (1 , json_object('Name' , 'Kil-Dong, Hong' , 'Sex' , 'M' , 'Phone' , '010-1234-5678')) ;
    Query OK, 1 row affected (0.003 sec)

    MariaDB [test]> commit;
    Query OK, 0 rows affected (0.000 sec)

    MariaDB [test]> select * from json_test ;
    +------+------------------------------------------------------------------+
    | id   | data                                                             |
    +------+------------------------------------------------------------------+
    |    1 | {"Name": "Kil-Dong, Hong", "Sex": "M", "Phone": "010-1234-5678"} |
    +------+------------------------------------------------------------------+
    1 row in set (0.000 sec)

   B. 특정 key 값만 조회 하고자 할때 json_value 함수를 사용

    MariaDB [test]> select id , json_value(data,'$.Name') As Name , json_value(data,'$.Phone') as Phone from json_test ;
    +------+----------------+---------------+
    | id   | Name           | Phone         |
    +------+----------------+---------------+
    |    1 | Kil-Dong, Hong | 010-1234-5678 |
    +------+----------------+---------------+
    1 row in set (0.001 sec)

    C. 특정 하나 Key 값을 update 하고자 할때,  json_replace 함수를 사용

    ※ 단, 이름은 중복날 수 있으니 Unique 값으로 비교하는 것을 추천 (사용방법 안내)
    MariaDB [test]> update json_test set data = json_replace(data,'$.Phone','010-2345-6789') where json_value(data,'$.Name') = 'Kil-Dong, Hong';
    Query OK, 1 row affected (0.002 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

    MariaDB [test]> select id , json_value(data,'$.Name') As Name , json_value(data,'$.Phone') as Phone from json_test ;
    +------+----------------+---------------+
    | id   | Name           | Phone         |
    +------+----------------+---------------+
    |    1 | Kil-Dong, Hong | 010-2345-6789 |
    +------+----------------+---------------+
    1 row in set (0.000 sec)


    Object 값 자체로 추출("" 형태)
    MariaDB [test]> SELECT JSON_EXTRACT(data, '$.Name') from json_test;
    +------------------------------+
    | JSON_EXTRACT(data, '$.Name') |
    +------------------------------+
    | "Su, Peng"                   |
    | "Dori Go"                    |
    | "DaHan, Wi "                 |
    | NULL                         |
    | NULL                         |
    +------------------------------+
    5 rows in set (0.000 sec)


    MariaDB [test]> SELECT JSON_UNQUOTE(JSON_EXTRACT(data, '$.Name')) from json_test;
    +--------------------------------------------+
    | JSON_UNQUOTE(JSON_EXTRACT(data, '$.Name')) |
    +--------------------------------------------+
    | Su, Peng                                   |
    | Dori Go                                    |
    | DaHan, Wi                                  |
    | NULL                                       |
    | NULL                                       |
    +--------------------------------------------+
    5 rows in set (0.000 sec)


    D. 하나 이상의 Key 값을 변경할 때는 json_set 함수 사용

    MariaDB [test]> update json_test set data = json_set(data,'$.Phone','010-3456-7890', '$.Name','Su, Peng') where id = 1 ;
    Query OK, 1 row affected (0.001 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

    MariaDB [test]> select id , json_value(data,'$.Name') As Name , json_value(data,'$.Phone') as Phone from json_test ;
    +------+----------+---------------+
    | id   | Name     | Phone         |
    +------+----------+---------------+
    |    1 | Su, Peng | 010-3456-7890 |
    +------+----------+---------------+
    1 row in set (0.000 sec)

    E. Json 형태가 추가되어도 입력이 가능
    insert into json_test values (2 , json_object('Name' , 'Dori Go' , 'Sex' , 'F' , 'Phone' , '02-123-4567' , 'Birth', '2000-01-01')) ;

    MariaDB [test]> insert into json_test values (2 , json_object('Name' , 'Dori Go' , 'Sex' , 'F' , 'Phone' , '02-123-4567' , 'Birth', '2000-01-01')) ;
    Query OK, 1 row affected (0.001 sec)

    MariaDB [test]> commit;
    Query OK, 0 rows affected (0.000 sec)

    MariaDB [test]> select * from json_test;
    +------+--------------------------------------------------------------------------------+
    | id   | data                                                                           |
    +------+--------------------------------------------------------------------------------+
    |    1 | {"Name": "Su, Peng", "Sex": "M", "Phone": "010-3456-7890"}                     |
    |    2 | {"Name": "Dori Go", "Sex": "F", "Phone": "02-123-4567", "Birth": "2000-01-01"} |
    +------+--------------------------------------------------------------------------------+
    2 rows in set (0.000 sec)

    F. json 데이터 값의 Like 검색

    MariaDB [test]> select count(*) from json_test where json_value(data,'$.Phone') like '02%' ;
    +----------+
    | count(*) |
    +----------+
    |        1 |
    +----------+
    1 row in set (0.000 sec)

    G. json 데이터 타입의 key를 가상 컬럼을 생성 후 Index 작업 가능

    - 테이블 변경(가상 컬럼 추가)
    MariaDB [test]> alter table json_test add phone varchar(20) as (json_value(data,'$.phone')) ;
    Query OK, 0 rows affected (0.341 sec)
    Records: 0  Duplicates: 0  Warnings: 0

    MariaDB [test]> select * from json_test limit 1 ;
    +------+------------------------------------------------------------+-------+
    | id   | data                                                       | phone |
    +------+------------------------------------------------------------+-------+
    |    1 | {"Name": "Su, Peng", "Sex": "M", "Phone": "010-3456-7890"} | NULL  |
    +------+------------------------------------------------------------+-------+
    1 row in set (0.000 sec)

    현재 화면에서 데이터가 안나오는 이유는 Key의 Phone은 P가 대문자 생성시에는 소문자로 만들었음
    삭제 후 다시 추가

    MariaDB [test]> alter table json_test drop phone;
    Query OK, 0 rows affected (0.009 sec)
    Records: 0  Duplicates: 0  Warnings: 0

    MariaDB [test]>  select * from json_test limit 1 ;
    +------+------------------------------------------------------------+
    | id   | data                                                       |
    +------+------------------------------------------------------------+
    |    1 | {"Name": "Su, Peng", "Sex": "M", "Phone": "010-3456-7890"} |
    +------+------------------------------------------------------------+
    1 row in set (0.000 sec)

    MariaDB [test]> alter table json_test add phone varchar(20) as (json_value(data,'$.Phone')) ;
    Query OK, 0 rows affected (0.012 sec)
    Records: 0  Duplicates: 0  Warnings: 0

    MariaDB [test]>  select * from json_test;
    +------+--------------------------------------------------------------------------------+---------------+
    | id   | data                                                                           | phone         |
    +------+--------------------------------------------------------------------------------+---------------+
    |    1 | {"Name": "Su, Peng", "Sex": "M", "Phone": "010-3456-7890"}                     | 010-3456-7890 |
    |    2 | {"Name": "Dori Go", "Sex": "F", "Phone": "02-123-4567", "Birth": "2000-01-01"} | 02-123-4567   |
    +------+--------------------------------------------------------------------------------+---------------+
    2 rows in set (0.000 sec)


    - 인덱스 생성
    key 값을 기준으로 가상 생성된 컬럼에 index를(ix_json_test_01)  추가
    MariaDB [test]> create index ix_json_test_01 on json_test(phone) ;
    Query OK, 0 rows affected (0.021 sec)
    Records: 0  Duplicates: 0  Warnings: 0

    explain 을 통해 plan을 조회합니다.
    MariaDB [test]> explain select count(*) from json_test where Phone like '02%' ;
    +------+-------------+-----------+-------+-----------------+-----------------+---------+------+------+--------------------------+
    | id   | select_type | table     | type  | possible_keys   | key             | key_len | ref  | rows | Extra                    |
    +------+-------------+-----------+-------+-----------------+-----------------+---------+------+------+--------------------------+
    |    1 | SIMPLE      | json_test | index | ix_json_test_01 | ix_json_test_01 | 63      | NULL |    2 | Using where; Using index |
    +------+-------------+-----------+-------+-----------------+-----------------+---------+------+------+--------------------------+
    1 row in set (0.000 sec)

    H. Json Object를 사용하지 않은 Insert
    MariaDB [test]> insert into json_test(id, data) values (3, '{"Name": "DaHan, Wi ", "Sex": "M", "Phone": "010-9876-5432", "Birth": "1999-12-31"}');
    Query OK, 1 row affected (0.003 sec)

    MariaDB [test]> select * from json_test;
    +------+-------------------------------------------------------------------------------------+---------------+
    | id   | data                                                                                | phone         |
    +------+-------------------------------------------------------------------------------------+---------------+
    |    1 | {"Name": "Su, Peng", "Sex": "M", "Phone": "010-3456-7890"}                          | 010-3456-7890 |
    |    2 | {"Name": "Dori Go", "Sex": "F", "Phone": "02-123-4567", "Birth": "2000-01-01"}      | 02-123-4567   |
    |    3 | {"Name": "DaHan, Wi ", "Sex": "M", "Phone": "010-9876-5432", "Birth": "1999-12-31"} | 010-9876-5432 |
    +------+-------------------------------------------------------------------------------------+---------------+
    3 rows in set (0.000 sec)

4. Json 관련 함수들
   관련 링크 :  https://mariadb.com/kb/en/library/json-functions/

   - JSON_Query와 JSON_VALUE 차이
     아래와 같이 임시로 json 변수를 선언하고 json 데이터를 저장합니다.

    MariaDB [test]> SET @json='{ "x": [0,1], "y": "[0,1]", "z": "Monty" }';
    Query OK, 0 rows affected (0.001 sec)

    MariaDB [test]> SELECT JSON_QUERY(@json,'$'), JSON_VALUE(@json,'$');
    +--------------------------------------------+-----------------------+
    | JSON_QUERY(@json,'$')                      | JSON_VALUE(@json,'$') |
    +--------------------------------------------+-----------------------+
    | { "x": [0,1], "y": "[0,1]", "z": "Monty" } | NULL                  |
    +--------------------------------------------+-----------------------+
    1 row in set (0.000 sec)

    MariaDB [test]> SELECT JSON_QUERY(@json,'$.x'), JSON_VALUE(@json,'$.x');
    +-------------------------+-------------------------+
    | JSON_QUERY(@json,'$.x') | JSON_VALUE(@json,'$.x') |
    +-------------------------+-------------------------+
    | [0,1]                   | NULL                    |
    +-------------------------+-------------------------+
    1 row in set (0.000 sec)

    MariaDB [test]> SELECT JSON_QUERY(@json,'$.y'), JSON_VALUE(@json,'$.y');
    +-------------------------+-------------------------+
    | JSON_QUERY(@json,'$.y') | JSON_VALUE(@json,'$.y') |
    +-------------------------+-------------------------+
    | NULL                    | [0,1]                   |
    +-------------------------+-------------------------+
    1 row in set (0.000 sec)

    MariaDB [test]> SELECT JSON_QUERY(@json,'$.z'), JSON_VALUE(@json,'$.z');
    +-------------------------+-------------------------+
    | JSON_QUERY(@json,'$.z') | JSON_VALUE(@json,'$.z') |
    +-------------------------+-------------------------+
    | NULL                    | Monty                   |
    +-------------------------+-------------------------+
    1 row in set (0.000 sec)

    MariaDB [test]> SELECT JSON_QUERY(@json,'$.x[0]'), JSON_VALUE(@json,'$.x[0]');
    +----------------------------+----------------------------+
    | JSON_QUERY(@json,'$.x[0]') | JSON_VALUE(@json,'$.x[0]') |
    +----------------------------+----------------------------+
    | NULL                       | 0                          |
    +----------------------------+----------------------------+
    1 row in set (0.000 sec)

    - JSON_ARRAY 함수
      10.2.3 버전부터 추가됨
      MariaDB [test]> SELECT Json_Array(56, 3.1416, 'My name is "Foo"', NULL);
    +--------------------------------------------------+
    | Json_Array(56, 3.1416, 'My name is "Foo"', NULL) |
    +--------------------------------------------------+
    | [56, 3.1416, "My name is \"Foo\"", null]         |
    +--------------------------------------------------+
    1 row in set (0.000 sec)

    MariaDB [test]> insert into json_test(id, data) values (4, JSON_ARRAY("Name","Ra, Ro ", "Sex", "F", "Phone", "010-1122-3344", "Birth", "1990-06-01"));
    Query OK, 1 row affected (0.003 sec)


    MariaDB [test]> select * from json_test;
    +------+-------------------------------------------------------------------------------------+---------------+
    | id   | data                                                                                | phone         |
    +------+-------------------------------------------------------------------------------------+---------------+
    |    1 | {"Name": "Su, Peng", "Sex": "M", "Phone": "010-3456-7890"}                          | 010-3456-7890 |
    |    2 | {"Name": "Dori Go", "Sex": "F", "Phone": "02-123-4567", "Birth": "2000-01-01"}      | 02-123-4567   |
    |    3 | {"Name": "DaHan, Wi ", "Sex": "M", "Phone": "010-9876-5432", "Birth": "1999-12-31"} | 010-9876-5432 |
    |    4 | ["Name", "Ra, Ro ", "Sex", "F", "Phone", "010-1122-3344", "Birth", "1990-06-01"]    | NULL          |
    +------+-------------------------------------------------------------------------------------+---------------+
    4 rows in set (0.000 sec)

    위의 id 4번 값은 아래에 보듯이 Type이 맞지 않기 때문에 phone값이 정상 인식 되지 않았음.

    MariaDB [test]> select json_type(data) from json_test;
    +-----------------+
    | json_type(data) |
    +-----------------+
    | OBJECT          |
    | OBJECT          |
    | OBJECT          |
    | ARRAY           |
    +-----------------+
    4 rows in set (0.001 sec)

    - 함수들 간략한 설명
    2
    JSON_QUERY와 JSON_VALUE의 차이점
        JSON_QUERY와 JSON_VALUE의 예제와 비교합니다.
    JSON_ARRAY
        나열된 값이 포함 된 JSON 배열을 반환합니다.
    JSON_ARRAY_APPEND
        JSON 문서 내에서 주어진 배열의 끝에 값을 추가합니다.
    JSON_ARRAY_INSERT
        JSON 문서에 값을 삽입합니다.
    JSON_COMPACT
        불필요한 모든 공간을 제거하여 json 문서가 가능한 한 짧습니다.
    JSON_CONTAINS
        지정된 JSON 문서 또는 문서 내의 지정된 경로에서 값을 찾을 수 있는지 여부
    JSON_CONTAINS_PATH
        지정된 JSON 문서에 지정된 경로의 데이터가 있는지 여부를 나타냅니다.
    JSON_DEPTH
        JSON 문서의 최대 깊이.
    JSON_DETAILED
        중첩 구조를 강조하는 가장 이해하기 쉬운 방식으로 JSON을 나타냅니다.
    JSON_EXISTS
        지정된 데이터에 지정된 JSON 값이 있는지 확인합니다.
    JSON_EXTRACT
        JSON 문서에서 데이터를 추출합니다.
    JSON_INSERT
        JSON 문서에 데이터를 삽입합니다.
    JSON_KEYS
        JSON 객체의 최상위 값에서 키를 반환하거나 경로에서 최상위 키를 반환합니다.
        MariaDB [test]> select id, json_keys(data) from json_test;
        +------+-----------------------------------+
        | id   | json_keys(data)                   |
        +------+-----------------------------------+
        |    1 | ["Name", "Sex", "Phone"]          |
        |    2 | ["Name", "Sex", "Phone", "Birth"] |
        |    3 | ["Name", "Sex", "Phone", "Birth"] |
        |    4 | NULL                              |
        |    5 | NULL                              |
        +------+-----------------------------------+
        5 rows in set (0.000 sec)

    JSON_LENGTH
        JSON 문서의 길이 또는 문서 내 값의 길이를 반환합니다.
    JSON_LOOSE
        더 읽기 쉽게 보이도록 JSON 문서에 공백을 추가합니다.
    JSON_MERGE
        주어진 JSON 문서를 병합합니다.
    JSON_MERGE_PATCH
        주어진 JSON 문서의 RFC 7396 호환 병합
    JSON_MERGE_PRESERVE
        JSON_MERGE의 동의어
    JSON_OBJECT
        주어진 키 / 값 쌍을 포함하는 JSON 객체를 반환합니다.
    JSON_QUERY
        JSON 문서가 주어지면 경로로 지정된 객체 또는 배열을 반환합니다.
    JSON_QUOTE
        문자열을 JSON 값으로 인용합니다.
    JSON_REMOVE
        JSON 문서에서 데이터를 제거합니다.
    JSON_REPLACE
        JSON 문서에서 기존 값을 바꿉니다.
    JSON_SEARCH
        JSON 문서 내에서 지정된 문자열의 경로를 반환합니다.
    JSON_SET
        JSON 문서에 데이터를 업데이트하거나 삽입합니다.
    JSON_TYPE
        JSON 값의 유형을 반환합니다.
    JSON_UNQUOTE
        JSON 값을 인용 해제하여 문자열을 반환합니다.
    JSON_VALID
        값이 유효한 JSON 문서인지 여부
    JSON_VALUE
        JSON 문서가 주어지면 지정된 스칼라를 반환합니다.

'Database > MYSQL' 카테고리의 다른 글

MariaDB connect 설치 (0)	2019.11.07
MariaDB 테이블 백업 및 복구 (0)	2019.11.01
MariaDB 우편번호 Import 하기 (0)	2019.11.01
MariaDB SHA2 512 방식으로 암호화 예제 (0)	2019.10.25
Mysql & MariaDB 튜닝 쉘 (0)	2019.08.14

옴니아2 조작하는 프로그램

smarttunesetup(pc).exe

관련 링크 : http://blog.naver.com/leeda235/130078296160

저작자표시

'Mobile Phone' 카테고리의 다른 글

안쓰는 폰 실시간 cctv 만들기 (0)	2013.08.01
갤럭시 S2 이메일 비밀번호 변경 하기 (0)	2012.02.13
모바일 .NET Compact Framework 3.5 재배포 가능 패키지 (0)	2010.03.12
옴니아2 고스톱 맞고 브루마블 2종 세트 (0)	2010.01.12
옴니아 2 배경 화면 ~ (0)	2009.12.10

출처(Reference) : http://www.javafaq.nu/java-example-code-141.html

// -----------------------------------------------------------------------------
// CLOBFileExample.java
// -----------------------------------------------------------------------------

/*
* =============================================================================
* Copyright (c) 1998-2005 Jeffrey M. Hunter. All rights reserved.
*
* All source code and material located at the Internet address of
* http://www.idevelopment.info is the copyright of Jeffrey M. Hunter, 2005 and
* is protected under copyright laws of the United States. This source code may
* not be hosted on any other site without my express, prior, written
* permission. Application to host any of the material elsewhere can be made by
* contacting me at jhunter@idevelopment.info.
*
* I have made every effort and taken great care in making sure that the source
* code and other content included on my web site is technically accurate, but I
* disclaim any and all responsibility for any loss, damage or destruction of
* data or any other property which may arise from relying on it. I will in no
* case be liable for any monetary damages arising from such loss, damage or
* destruction.
*
* As with any code, ensure to test this code in a development environment
* before attempting to run it in production.
* =============================================================================
*/

import java.sql.*;
import java.io.*;
import java.util.*;

// Needed since we will be using Oracle's CLOB, part of Oracle's JDBC extended
// classes. Keep in mind that we could have included Java's JDBC interfaces
// java.sql.Clob which Oracle does implement. The oracle.sql.CLOB class
// provided by Oracle does offer better performance and functionality.
import oracle.sql.*;

// Needed for Oracle JDBC Extended Classes
import oracle.jdbc.*;

/**
* -----------------------------------------------------------------------------
* Used to test the functionality of how to load and unload text data from an
* Oracle CLOB.
*
* This example uses an Oracle table with the following definition:
*
* CREATE TABLE test_clob (
* id NUMBER(15)
* , do*****ent_name VARCHAR2(1000)
* , xml_do*****ent CLOB
* , timestamp DATE
* );
* -----------------------------------------------------------------------------
* @version 1.0
* @author Jeffrey M. Hunter (jhunter@idevelopment.info)
* @author http://www.idevelopment.info
* -----------------------------------------------------------------------------
*/

public class CLOBFileExample {

private String inputTextFileName = null;
private File inputTextFile = null;

private String outputTextFileName1 = null;
private File outputTextFile1 = null;

private String outputTextFileName2 = null;
private File outputTextFile2 = null;

private String dbUser = "SCOTT";
private String dbPassword = "TIGER";
private Connection conn = null;

/**
* Default constructor used to create this object. Responsible for setting
* this object's creation date, as well as incrementing the number instances
* of this object.
* @param args Array of string arguments passed in from the command-line.
* @throws java.io.IOException
*/
public CLOBFileExample(String[] args) throws IOException {

inputTextFileName = args[0];
inputTextFile = new File(inputTextFileName);

if (!inputTextFile.exists()) {
throw new IOException("File not found. " + inputTextFileName);
}

outputTextFileName1 = inputTextFileName + ".getChars.out";
outputTextFileName2 = inputTextFileName + ".Streams.out";

}

/**
* Obtain a connection to the Oracle database.
* @throws java.sql.SQLException
*/
public void openOracleConnection()
throws SQLException
, IllegalAccessException
, InstantiationException
, ClassNotFoundException {

String driver_class = "oracle.jdbc.driver.OracleDriver";
String connectionURL = null;

try {
Class.forName (driver_class).newInstance();
connectionURL = "jdbc:oracle:thin:@melody:1521:JEFFDB";
conn = DriverManager.getConnection(connectionURL, dbUser, dbPassword);
conn.setAutoCommit(false);
System.out.println("Connected.\n");
} catch (IllegalAccessException e) {
System.out.println("Illegal Access Exception: (Open Connection).");
e.printStackTrace();
throw e;
} catch (InstantiationException e) {
System.out.println("Instantiation Exception: (Open Connection).");
e.printStackTrace();
throw e;
} catch (ClassNotFoundException e) {
System.out.println("Class Not Found Exception: (Open Connection).");
e.printStackTrace();
throw e;
} catch (SQLException e) {
System.out.println("Caught SQL Exception: (Open Connection).");
e.printStackTrace();
throw e;
}

}

/**
* Close Oracle database connection.
* @throws java.sql.SQLException
*/
public void closeOracleConnection() throws SQLException {

try {
conn.close();
System.out.println("Disconnected.\n");
} catch (SQLException e) {
System.out.println("Caught SQL Exception: (Closing Connection).");
e.printStackTrace();
if (conn != null) {
try {
conn.rollback();
} catch (SQLException e2) {
System.out.println("Caught SQL (Rollback Failed) Exception.");
e2.printStackTrace();
}
}
throw e;
}

}

/**
* Method used to print program usage to the console.
*/
static public void usage() {
System.out.println("\nUsage: java CLOBFileExample \"Text File Name\"\n");
}

/**
* Validate command-line arguments to this program.
* @param args Array of string arguments passed in from the command-line.
* @return Boolean - value of true if correct arguments, false otherwise.
*/
static public boolean checkArguments(String[] args) {

if (args.length == 1) {
return true;
} else {
return false;
}

}

/**
* Override the Object toString method. Used to print a version of this
* object to the console.
* @return String - String to be returned by this object.
*/
public String toString() {

String retValue;

retValue = "Input File : " + inputTextFileName + "\n" +
"Output File (1) : " + outputTextFileName1 + "\n" +
"Output File (2) : " + outputTextFileName2 + "\n" +
"Database User : " + dbUser;
return retValue;

}

/**
* Method used to write text data contained in a file to an Oracle CLOB
* column. The method used to write the data to the CLOB uses the putChars()
* method. This is one of two types of methods used to write text data to
* a CLOB column. The other method uses Streams.
*
* @throws java.io.IOException
* @throws java.sql.SQLException
*/
public void writeCLOBPut()
throws IOException, SQLException {

FileInputStream inputFileInputStream = null;
InputStreamReader inputInputStreamReader = null;
BufferedReader inputBufferedReader = null;
String sqlText = null;
Statement stmt = null;
ResultSet rset = null;
CLOB xmlDo*****ent = null;
int chunkSize;
char[] textBuffer;
long position;
int charsRead = 0;
int charsWritten = 0;
int totCharsRead = 0;
int totCharsWritten = 0;

try {

stmt = conn.createStatement();

inputTextFile = new File(inputTextFileName);
inputFileInputStream = new FileInputStream(inputTextFile);
inputInputStreamReader = new InputStreamReader(inputFileInputStream);
inputBufferedReader = new BufferedReader(inputInputStreamReader);

sqlText =
"INSERT INTO test_clob (id, do*****ent_name, xml_do*****ent, timestamp) " +
" VALUES(1, '" + inputTextFile.getName() + "', EMPTY_CLOB(), SYSDATE)";
stmt.executeUpdate(sqlText);

sqlText =
"SELECT xml_do*****ent " +
"FROM test_clob " +
"WHERE id = 1 " +
"FOR UPDATE";
rset = stmt.executeQuery(sqlText);
rset.next();
xmlDo*****ent = ((OracleResultSet) rset).getCLOB("xml_do*****ent");

chunkSize = xmlDo*****ent.getChunkSize();
textBuffer = new char[chunkSize];

position = 1;
while ((charsRead = inputBufferedReader.read(textBuffer)) != -1) {
charsWritten = xmlDo*****ent.putChars(position, textBuffer, charsRead);
position += charsRead;
totCharsRead += charsRead;
totCharsWritten += charsWritten;
}

inputBufferedReader.close();
inputInputStreamReader.close();
inputFileInputStream.close();

conn.commit();
rset.close();
stmt.close();

System.out.println(
"==========================================================\n" +
" PUT METHOD\n" +
"==========================================================\n" +
"Wrote file " + inputTextFile.getName() + " to CLOB column.\n" +
totCharsRead + " characters read.\n" +
totCharsWritten + " characters written.\n"
);

} catch (IOException e) {
System.out.println("Caught I/O Exception: (Write CLOB value - Put Method).");
e.printStackTrace();
throw e;
} catch (SQLException e) {
System.out.println("Caught SQL Exception: (Write CLOB value - Put Method).");
System.out.println("SQL:\n" + sqlText);
e.printStackTrace();
throw e;
}

}

/**
* Method used to write the contents (data) from an Oracle CLOB column to
* an O/S file. This method uses one of two ways to get data from the CLOB
* column - namely the getChars() method. The other way to read data from an
* Oracle CLOB column is to use Streams.
*
* @throws java.io.IOException
* @throws java.sql.SQLException
*/
public void readCLOBToFileGet()
throws IOException, SQLException {

FileOutputStream outputFileOutputStream = null;
OutputStreamWriter outputOutputStreamWriter = null;
BufferedWriter outputBufferedWriter = null;
String sqlText = null;
Statement stmt = null;
ResultSet rset = null;
CLOB xmlDo*****ent = null;
long clobLength;
long position;
int chunkSize;
char[] textBuffer;
int charsRead = 0;
int charsWritten = 0;
int totCharsRead = 0;
int totCharsWritten = 0;

try {

stmt = conn.createStatement();

outputTextFile1 = new File(outputTextFileName1);
outputFileOutputStream = new FileOutputStream(outputTextFile1);
outputOutputStreamWriter = new OutputStreamWriter(outputFileOutputStream);
outputBufferedWriter = new BufferedWriter(outputOutputStreamWriter);

sqlText =
"SELECT xml_do*****ent " +
"FROM test_clob " +
"WHERE id = 1 " +
"FOR UPDATE";
rset = stmt.executeQuery(sqlText);
rset.next();
xmlDo*****ent = ((OracleResultSet) rset).getCLOB("xml_do*****ent");

clobLength = xmlDo*****ent.length();
chunkSize = xmlDo*****ent.getChunkSize();
textBuffer = new char[chunkSize];

for (position = 1; position <= clobLength; position += chunkSize) {

// Loop through while reading a chunk of data from the CLOB
// column using the getChars() method. This data will be stored
// in a temporary buffer that will be written to disk.
charsRead = xmlDo*****ent.getChars(position, chunkSize, textBuffer);

// Now write the buffer to disk.
outputBufferedWriter.write(textBuffer, 0, charsRead);

totCharsRead += charsRead;
totCharsWritten += charsRead;

}

outputBufferedWriter.close();
outputOutputStreamWriter.close();
outputFileOutputStream.close();

conn.commit();
rset.close();
stmt.close();

System.out.println(
"==========================================================\n" +
" GET METHOD\n" +
"==========================================================\n" +
"Wrote CLOB column data to file " + outputTextFile1.getName() + ".\n" +
totCharsRead + " characters read.\n" +
totCharsWritten + " characters written.\n"
);

} catch (IOException e) {
System.out.println("Caught I/O Exception: (Write CLOB value to file - Get Method).");
e.printStackTrace();
throw e;
} catch (SQLException e) {
System.out.println("Caught SQL Exception: (Write CLOB value to file - Get Method).");
System.out.println("SQL:\n" + sqlText);
e.printStackTrace();
throw e;
}

}

/**
* Method used to write text data contained in a file to an Oracle CLOB
* column. The method used to write the data to the CLOB uses Streams.
* This is one of two types of methods used to write text data to
* a CLOB column. The other method uses the putChars() method.
*
* @throws java.io.IOException
* @throws java.sql.SQLException
*/
public void writeCLOBStream()
throws IOException, SQLException {

FileInputStream inputFileInputStream = null;
OutputStream clobOutputStream = null;
String sqlText = null;
Statement stmt = null;
ResultSet rset = null;
CLOB xmlDo*****ent = null;
int bufferSize;
byte[] byteBuffer;
int bytesRead = 0;
int bytesWritten = 0;
int totBytesRead = 0;
int totBytesWritten = 0;

try {

stmt = conn.createStatement();

inputTextFile = new File(inputTextFileName);
inputFileInputStream = new FileInputStream(inputTextFile);

sqlText =
"INSERT INTO test_clob (id, do*****ent_name, xml_do*****ent, timestamp) " +
" VALUES(2, '" + inputTextFile.getName() + "', EMPTY_CLOB(), SYSDATE)";
stmt.executeUpdate(sqlText);

sqlText =
"SELECT xml_do*****ent " +
"FROM test_clob " +
"WHERE id = 2 " +
"FOR UPDATE";
rset = stmt.executeQuery(sqlText);
rset.next();
xmlDo*****ent = ((OracleResultSet) rset).getCLOB("xml_do*****ent");

bufferSize = xmlDo*****ent.getBufferSize();

// Notice that we are using an array of bytes as opposed to an array
// of characters. This is required since we will be streaming the
// content (to either a CLOB or BLOB) as a stream of bytes using
// using an OutputStream Object. This requires that a byte array to
// be used to temporarily store the contents that will be sent to
// the LOB. Note that they use of the byte array can be used even
// when reading contents from an ASCII text file that will be sent
// to a CLOB.
byteBuffer = new byte[bufferSize];

clobOutputStream = xmlDo*****ent.getAsciiOutputStream();

while ((bytesRead = inputFileInputStream.read(byteBuffer)) != -1) {

// After reading a buffer from the text file, write the contents
// of the buffer to the output stream using the write()
// method.
clobOutputStream.write(byteBuffer, 0, bytesRead);

totBytesRead += bytesRead;
totBytesWritten += bytesRead;

}

// Keep in mind that we still have the stream open. Once the stream
// gets open, you cannot perform any other database operations
// until that stream has been closed. This even includes a COMMIT
// statement. It is possible to loose data from the stream if this
// rule is not followed. If you were to attempt to put the COMMIT in
// place before closing the stream, Oracle will raise an
// "ORA-22990: LOB locators cannot span transactions" error.

inputFileInputStream.close();
clobOutputStream.close();

conn.commit();
rset.close();
stmt.close();

System.out.println(
"==========================================================\n" +
" OUTPUT STREAMS METHOD\n" +
"==========================================================\n" +
"Wrote file " + inputTextFile.getName() + " to CLOB column.\n" +
totBytesRead + " bytes read.\n" +
totBytesWritten + " bytes written.\n"
);

} catch (IOException e) {
System.out.println("Caught I/O Exception: (Write CLOB value - Stream Method).");
e.printStackTrace();
throw e;
} catch (SQLException e) {
System.out.println("Caught SQL Exception: (Write CLOB value - Stream Method).");
System.out.println("SQL:\n" + sqlText);
e.printStackTrace();
throw e;
}

}

/**
* Method used to write the contents (data) from an Oracle CLOB column to
* an O/S file. This method uses one of two ways to get data from the CLOB
* column - namely using Streams. The other way to read data from an
* Oracle CLOB column is to use getChars() method.
*
* @throws java.io.IOException
* @throws java.sql.SQLException
*/
public void readCLOBToFileStream()
throws IOException, SQLException {

FileOutputStream outputFileOutputStream = null;
InputStream clobInputStream = null;
String sqlText = null;
Statement stmt = null;
ResultSet rset = null;
CLOB xmlDo*****ent = null;
int chunkSize;
byte[] textBuffer;
int bytesRead = 0;
int bytesWritten = 0;
int totBytesRead = 0;
int totBytesWritten = 0;

try {

stmt = conn.createStatement();

outputTextFile2 = new File(outputTextFileName2);
outputFileOutputStream = new FileOutputStream(outputTextFile2);

sqlText =
"SELECT xml_do*****ent " +
"FROM test_clob " +
"WHERE id = 2 " +
"FOR UPDATE";
rset = stmt.executeQuery(sqlText);
rset.next();
xmlDo*****ent = ((OracleResultSet) rset).getCLOB("xml_do*****ent");

// Will use a Java InputStream object to read data from a CLOB (can
// also be used for a BLOB) object. In this example, we will use an
// InputStream to read ASCII characters from a CLOB.
clobInputStream = xmlDo*****ent.getAsciiStream();

chunkSize = xmlDo*****ent.getChunkSize();
textBuffer = new byte[chunkSize];

while ((bytesRead = clobInputStream.read(textBuffer)) != -1) {

// Loop through while reading a chunk of data from the CLOB
// column using an InputStream. This data will be stored
// in a temporary buffer that will be written to disk.
outputFileOutputStream.write(textBuffer, 0, bytesRead);

totBytesRead += bytesRead;
totBytesWritten += bytesRead;

}

outputFileOutputStream.close();
clobInputStream.close();

conn.commit();
rset.close();
stmt.close();

System.out.println(
"==========================================================\n" +
" INPUT STREAMS METHOD\n" +
"==========================================================\n" +
"Wrote CLOB column data to file " + outputTextFile2.getName() + ".\n" +
totBytesRead + " characters read.\n" +
totBytesWritten + " characters written.\n"
);

} catch (IOException e) {
System.out.println("Caught I/O Exception: (Write CLOB value to file - Streams Method).");
e.printStackTrace();
throw e;
} catch (SQLException e) {
System.out.println("Caught SQL Exception: (Write CLOB value to file - Streams Method).");
System.out.println("SQL:\n" + sqlText);
e.printStackTrace();
throw e;
}

}

/**
* Sole entry point to the class and application.
* @param args Array of string arguments passed in from the command-line.
*/
public static void main(String[] args) {

CLOBFileExample cLOBFileExample = null;

if (checkArguments(args)) {

try {

cLOBFileExample = new CLOBFileExample(args);

System.out.println("\n" + cLOBFileExample + "\n");

cLOBFileExample.openOracleConnection();

cLOBFileExample.writeCLOBPut();
cLOBFileExample.readCLOBToFileGet();

cLOBFileExample.writeCLOBStream();
cLOBFileExample.readCLOBToFileStream();

cLOBFileExample.closeOracleConnection();

} catch (IllegalAccessException e) {
System.out.println("Caught Illegal Accecss Exception. Exiting.");
e.printStackTrace();
System.exit(1);
} catch (InstantiationException e) {
System.out.println("Instantiation Exception. Exiting.");
e.printStackTrace();
System.exit(1);
} catch (ClassNotFoundException e) {
System.out.println("Class Not Found Exception. Exiting.");
e.printStackTrace();
System.exit(1);
} catch (SQLException e) {
System.out.println("Caught SQL Exception. Exiting.");
e.printStackTrace();
System.exit(1);
} catch (IOException e) {
System.out.println("Caught I/O Exception. Exiting.");
e.printStackTrace();
System.exit(1);
}

} else {
System.out.println("\nERROR: Invalid arguments.");
usage();
System.exit(1);
}

System.exit(0);
}

저작자표시

'Language > JAVA' 카테고리의 다른 글

System.out.println()은 잊어라 log4sql이 온다. (0)	2011.03.15
Log for SQL (PL/SQL 개발환경에서 로그 정보 확인하기) (0)	2011.03.15
FILE을 CLOB에 INSERT하고, 반대로 컬럼을 읽어 FILE로 WRITE하는 JDBC PROGRAM 예제 (JDBC 8.1 이상 (0)	2011.01.07
Heap Dump 분석을 통한 Perm Area Memory Leak 원인 진단 (0)	2010.12.14
Java ARchives (JAR) (0)	2010.11.17

하하하....

외국사이트에서는

본컴 + VMWARE를 사용한게 나왔네요...

아이템 교환할때 유용할듯...

또... 프로그램 고딴거 사용하면

동시에 3-4계정 조작도 가능하지요...

물리적으로는 ... 무선 키보드...

수신기로...

되는 것도잇찌요...

저작자표시

'Game > 던전앤파이터' 카테고리의 다른 글

던파 불법 프로그램 목록 (0)	2011.01.22
던파 빠른 렙업 사냥터 추천 (0)	2011.01.05
마도학자 스킬트리 (0)	2010.12.09
트레저 헌터의 팔찌 - 격투가 (0)	2010.11.01
트레저 헌터의 목걸이 - 남거너 (0)	2010.11.01

Memory-mapped files

Memory-mapped files allow you to create and modify files that are too big to bring into memory. With a memory-mapped file, you can pretend that the entire file is in memory and that you can access it by simply treating it as a very large array. This approach greatly simplifies the code you write in order to modify the file. Here’s a small example:

//: c12:LargeMappedFiles.java
// Creating a very large file using mapping.
// {RunByHand}
// {Clean: test.dat}
import java.io.*;
import java.nio.*;
import java.nio.channels.*;

public class LargeMappedFiles {
  static int length = 0x8FFFFFF; // 128 Mb
  public static void main(String[] args) throws Exception {
    MappedByteBuffer out = 
      new RandomAccessFile("test.dat", "rw").getChannel()
      .map(FileChannel.MapMode.READ_WRITE, 0, length);
    for(int i = 0; i < length; i++)
      out.put((byte)'x');
    System.out.println("Finished writing");
    for(int i = length/2; i < length/2 + 6; i++)
      System.out.print((char)out.get(i));
  }
} ///:~

To do both writing and reading, we start with a RandomAccessFile, get a channel for that file, and then call map( ) to produce a MappedByteBuffer, which is a particular kind of direct buffer. Note that you must specify the starting point and the length of the region that you want to map in the file; this means that you have the option to map smaller regions of a large file.

MappedByteBuffer is inherited from ByteBuffer, so it has all of ByteBuffer’s methods. Only the very simple uses of put( ) and get( ) are shown here, but you can also use things like asCharBuffer( ), etc.

The file created with the preceding program is 128 MB long, which is probably larger than the space your OS will allow. The file appears to be accessible all at once because only portions of it are brought into memory, and other parts are swapped out. This way a very large file (up to 2 GB) can easily be modified. Note that the file-mapping facilities of the underlying operating system are used to maximize performance.

Performance

Although the performance of “old” stream I/O has been improved by implementing it with nio, mapped file access tends to be dramatically faster. This program does a simple performance comparison:

//: c12:MappedIO.java
// {Clean: temp.tmp}
import java.io.*;
import java.nio.*;
import java.nio.channels.*;

public class MappedIO {
  private static int numOfInts = 4000000;
  private static int numOfUbuffInts = 200000;
  private abstract static class Tester {
    private String name;
    public Tester(String name) { this.name = name; }
    public long runTest() {
      System.out.print(name + ": ");
      try {
        long startTime = System.currentTimeMillis();
        test();
        long endTime = System.currentTimeMillis();
        return (endTime - startTime);
      } catch (IOException e) {
        throw new RuntimeException(e);
      }
    }
    public abstract void test() throws IOException;
  }
  private static Tester[] tests = { 
    new Tester("Stream Write") {
      public void test() throws IOException {
        DataOutputStream dos = new DataOutputStream(
          new BufferedOutputStream(
            new FileOutputStream(new File("temp.tmp"))));
        for(int i = 0; i < numOfInts; i++)
          dos.writeInt(i);
        dos.close();
      }
    }, 
    new Tester("Mapped Write") {
      public void test() throws IOException {
        FileChannel fc = 
          new RandomAccessFile("temp.tmp", "rw")
          .getChannel();
        IntBuffer ib = fc.map(
          FileChannel.MapMode.READ_WRITE, 0, fc.size())
          .asIntBuffer();
        for(int i = 0; i < numOfInts; i++)
          ib.put(i);
        fc.close();
      }
    }, 
    new Tester("Stream Read") {
      public void test() throws IOException {
        DataInputStream dis = new DataInputStream(
          new BufferedInputStream(
            new FileInputStream("temp.tmp")));
        for(int i = 0; i < numOfInts; i++)
          dis.readInt();
        dis.close();
      }
    }, 
    new Tester("Mapped Read") {
      public void test() throws IOException {
        FileChannel fc = new FileInputStream(
          new File("temp.tmp")).getChannel();
        IntBuffer ib = fc.map(
          FileChannel.MapMode.READ_ONLY, 0, fc.size())
          .asIntBuffer();
        while(ib.hasRemaining())
          ib.get();
        fc.close();
      }
    }, 
    new Tester("Stream Read/Write") {
      public void test() throws IOException {
        RandomAccessFile raf = new RandomAccessFile(
          new File("temp.tmp"), "rw");
        raf.writeInt(1);
        for(int i = 0; i < numOfUbuffInts; i++) {
          raf.seek(raf.length() - 4);
          raf.writeInt(raf.readInt());
        }
        raf.close();
      }
    }, 
    new Tester("Mapped Read/Write") {
      public void test() throws IOException {
        FileChannel fc = new RandomAccessFile(
          new File("temp.tmp"), "rw").getChannel();
        IntBuffer ib = fc.map(
          FileChannel.MapMode.READ_WRITE, 0, fc.size())
          .asIntBuffer();
        ib.put(0);
        for(int i = 1; i < numOfUbuffInts; i++)
          ib.put(ib.get(i - 1));
        fc.close();
      }
    }
  };
  public static void main(String[] args) {
    for(int i = 0; i < tests.length; i++)
      System.out.println(tests[i].runTest());
  }
} ///:~

As seen in earlier examples in this book, runTest( ) is the Template Method that provides the testing framework for various implementations of test( ) defined in anonymous inner subclasses. Each of these subclasses perform one kind of test, so the test( ) methods also give you a prototype for performing the various I/O activities.

Although a mapped write would seem to use a FileOutputStream, all output in file mapping must use a RandomAccessFile, just as read/write does in the preceding code.

Here’s the output from one run:

Stream Write: 1719
Mapped Write: 359
Stream Read: 750
Mapped Read: 125
Stream Read/Write: 5188
Mapped Read/Write: 16

Note that the test( ) methods include the time for initialization of the various I/O objects, so even though the setup for mapped files can be expensive, the overall gain compared to stream I/O is significant.

출처 : http://www.linuxtopia.org/

저작자표시

'Language > JAVA' 카테고리의 다른 글

자바 파일 압축 (0)	2010.11.17
JAVA File Locking(자바 파일 잠금) (0)	2010.11.17
다수의 JAR파일들 중, 특정 Class 찾기 (0)	2010.01.11
Java를 이용한 CLOB, BLOB 조작법J (0)	2009.12.23
[Tip] 오라클 CLOB 데이터 입출력 (0)	2009.12.23

폼 데이터 핸들링

ADOBE ACROBAT 에서

"애크로뱃 8의 폼 배포와 트래킹"에서 설명했던 것처럼 데이터셋 파일은 배포 프로세스의 한 부분으로서 자동 생성된다. 파일은 PDF 패키지다.

데이터셋 파일에 파일들을 추가하려면 아래 순서를 따른다.

애크로뱃 8 프로페셔널에서 리턴 폼을 열기 위하여 더블 클릭한다. 데이터셋에 완료 폼 추가(Add Completed Form to Dataset) 대화상자가 열리고, 배포의 한 부분으로서 생성된 데이터셋 PDF 파일이 나열된다.
OK를 클릭해 대화상자를 닫는다. PDF 패키지 파일이 열리고 폼은 데이터 리스트에 포함된다. PDF 패키지의 구조는 데이터 구체화 명령(Figure 2)을 수반한다.

(+) 확대보기
Figure 1 : 패키지의 데이터셋 파일에 폼 파일들이 추가된다.
파일 도착 즉시 데이터셋에 더 많은 파일을 추가하기 위하여, PDF 패키지의 툴바에서 임포트 데이터(Import Data)를 클릭하고 리턴 폼 추가(Add Returned Forms) 대화상자를 연다.
파일 추가(Add File)를 클릭하고 리턴 폼 추가(Add Returned Forms) 대화상자에 있는 완료 폼 파일들을 찾아 선택한다. OK를 클릭하고 대화상자를 닫는다. Figure 1에서 보이는 것과 같이 PDF 패키지에 파일을 로드한다.
스프레드시트에 데이터를 익스포트하기 위하여, PDF 패키지의 툴바에 있는 익스포트 데이터(Export Data)를 클릭한다. 저장파일 대화상자를 열기 위하여 폴더를 선택하고, 디폴트로 데이터셋 파일 이름을 사용하여 CSV(comma-separated values: 쉼표에 의해 분리되는 항목 값) 포맷으로 저장한다. Save를 클릭한다.
스프레드시트 프로그램을 열고 CSV 파일을 찾아 선택한다. 스프레드시트는 리턴 폼을 파일 이름에 따라 정렬하고 두 질문에 대한 각각의 답도 함께 나타낸다 (Figure 2).

Figure 2 : 익스포트 폼 데이터는 스프레드 시트 셀 안의 각각의 질문에 대한 답변과 함께 시트에 나타난다.

얼마나 자주 사용하세요?

PDF 패키지의 파일 어샘블 선택 빈도에 따라 업무 강도나 일정을 알 수 있다. 개인에 따라 종합 파일이 매일 필요할 수도 있고 분기말에나 한번 필요할 수도 있다.

원문출처 : http://www.adobe.com/designcenter/acrobat/articles/acr8at_handleform.html

출처 : http://www.acrobatpdf.com/tip/detail.asp?id=80&gotopage=10&code=

저작자표시

'UTILITY > Acrobat' 카테고리의 다른 글

[보안] 내가 만든 PDF 보고서에 서명하기 (1)	2010.03.09
[배포와수집] 양식의 배포와 트랙킹 (0)	2010.03.09
[배포와수집]양식 데이터 필드 설정 (0)	2010.03.09
전문가가 추천하는 ‘애크로뱃9′ 9대 기능 (0)	2010.03.09
[애크로뱃 9] 팀장은 수정만, 팀원은 열람만! 이제 맞춤형 보안시대 (0)	2010.03.09

웹 개발과 관련된 모든 것들

조작

MariaDB JSON 컬럼 및 함수들

'Database > MYSQL' 카테고리의 다른 글

스마트튠 PC 용

'Mobile Phone' 카테고리의 다른 글

CLOB 파일 다루기 예제

'Language > JAVA' 카테고리의 다른 글

던전앤 파이터 투 계정 돌리기

'Game > 던전앤파이터' 카테고리의 다른 글

자바 메모리를 사용한 큰 파일 조작하기

Memory-mapped files

Performance

'Language > JAVA' 카테고리의 다른 글

[배포와수집]양식 데이터 핸들링

'UTILITY > Acrobat' 카테고리의 다른 글

+ Recent posts

티스토리툴바