In MySQL 8.0
--JSON function
It can also be used as a document database (document store) if you use, so I tried using MySQL Connector / J in a Java (8) environment, although it was late.
【reference】
Install the following on the same machine (Windows PC or Linux virtual machine is OK).
Next, register a test user on the MySQL (Community) Server (OK from the command line or from MySQL Workbench).
--Username is testuser @ localhost
--The password is T35_U53r
--SELECT, INSERT, UPDATE, DELETE privileges
--Authentication can be either caching_sha2_password or mysql_native_password
Finally, create a DB (schema) test_db with CREATE DATABASE etc. and you are ready to go.
↓ is a test code for simple document processing.
Test code
package site.hmatsu47.DocDbTest;
import java.util.List;
import com.mysql.cj.xdevapi.Collection;
import com.mysql.cj.xdevapi.DbDoc;
import com.mysql.cj.xdevapi.DocResult;
import com.mysql.cj.xdevapi.Schema;
import com.mysql.cj.xdevapi.Session;
import com.mysql.cj.xdevapi.SessionFactory;
public class Main {
public static void main(String args[]) {
//Connect to server
Session session = new SessionFactory().getSession("mysqlx://localhost:33060/test_db?user=testuser&password=T35_U53r");
//Connect to DB
Schema db = session.getSchema("test_db");
//collection'test_collection'Create
Collection col = db.createCollection("test_collection", true);
//Add documents to collection
col.add("{\"id\":1, \"name\":\"Sakuraba\", \"Program\":\"Java Champion\"}")
.execute();
col.add("{\"id\":2, \"name\":\"Well\", \"Program\":\"Oracle ACE\"}")
.execute();
col.add("{\"id\":3, \"name\":\"Sero\", \"Program\":[\"Java Champion\",\"Oracle Groundbreaker Ambassador\"]}")
.execute();
col.add("{\"id\":4, \"name\":\"Tomita\", \"Program\":\"Oracle ACE Associate\"}")
.execute();
col.add("{\"id\":5, \"name\":\"Mitani\", \"Program\":\"Oracle ACE\"}")
.execute();
//It has nothing to do with the main subject, but I can't find Jukucho in the DB of Oracle's ACE, Groundbreaker, and Java Champion ...
//Add index to "id" column of collection
col.createIndex("id_index", "{\"fields\": [{\"field\": \"$.id\", \"type\": \"INT\"}]}");
//From the collection "Program LIKE'%Oracle%'Look for and display
searchProgram(col, "Oracle");
System.out.println();
//From the collection "Program LIKE'%Java%'Look for and display
searchProgram(col, "Java");
System.out.println();
//From the collection "id=2 ”and display
searchId(col, 2);
System.out.println();
//From the collection "id=4 ”and display
searchId(col, 4);
//Delete collection
db.dropCollection("test_collection");
}
//Search for "Program" of the target document from the collection and display it.
private static void searchProgram(Collection col, String keyword) {
System.out.println("Search: " + keyword);
DocResult docs = col.find("Program like :program")
.bind("program", "%" + keyword + "%").execute();
//Get and display results
List<DbDoc> docl = docs.fetchAll();
docl.forEach(doc -> System.out.println(doc.toFormattedString()));
}
//Numerically search and display the "id" of the target document from the collection
private static void searchId(Collection col, long value) {
System.out.println("Search: " + value);
DocResult docs = col.find("id = :id")
.bind("id", value).execute();
//Get and display results
System.out.println(docs.fetchOne().toFormattedString());
}
}
Connect to the DB server with new SessionFactory (). getSession () as an argument and connect to the DB with session.getSchema (); as an argument with the schema (DB) name. I have.
As the name suggests, ʻURL is ʻURL, so if you use symbols in your password, please escape them appropriately (I made a mistake and worried for an hour).
Then in order
--Create a new document collection (if the same name already exists, use it as it is)
--Add documents to document collection (5 lines in total)
--Indexing
--Search string LIKE
--Numerical search (intended to use index)
--Delete document collection
I am doing.
As you can see, the string LIKE search shows that the search is correct even if one document has multiple elements with the same name.
Looking at the table design during execution, it looks like this (SHOW CREATE TABLE test_db.test_collection;)
test_collection table design
CREATE TABLE `test_collection` (
`doc` json DEFAULT NULL,
`_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
`$ix_i_C6EF652A87142734264FBA60F41C59108CF4D8CA` int(11) GENERATED ALWAYS AS (json_extract(`doc`,_utf8mb4'$.id')) VIRTUAL,
PRIMARY KEY (`_id`),
KEY `id_index` (`$ix_i_C6EF652A87142734264FBA60F41C59108CF4D8CA`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Also, the execution result will be like this.
Execution result
Search: Oracle
{
"Program" : "Oracle ACE",
"_id" : "00005c8ded80000000000000006b",
"id" : 2,
"name" : "Well"
}
{
"Program" : ["Java Champion", "Oracle Groundbreaker Ambassador"],
"_id" : "00005c8ded80000000000000006c",
"id" : 3,
"name" : "Sero"
}
{
"Program" : "Oracle ACE Associate",
"_id" : "00005c8ded80000000000000006d",
"id" : 4,
"name" : "Tomita"
}
{
"Program" : "Oracle ACE",
"_id" : "00005c8ded80000000000000006e",
"id" : 5,
"name" : "Mitani"
}
Search: Java
{
"Program" : "Java Champion",
"_id" : "00005c8ded80000000000000006a",
"id" : 1,
"name" : "Sakuraba"
}
{
"Program" : ["Java Champion", "Oracle Groundbreaker Ambassador"],
"_id" : "00005c8ded80000000000000006c",
"id" : 3,
"name" : "Sero"
}
Search: 2
{
"Program" : "Oracle ACE",
"_id" : "00005c8ded80000000000000006b",
"id" : 2,
"name" : "Well"
}
Search: 4
{
"Program" : "Oracle ACE Associate",
"_id" : "00005c8ded80000000000000006d",
"id" : 4,
"name" : "Tomita"
}
Other X DevAPI
--Asynchronous processing --CRUD and other operations on relational DB schemas --Transaction processing
There are several functions such as.
-** MySQL 8.0 related article posted on Qiita **
Recommended Posts