Using MySQL Document Store with Java

MySQL Document Store offers developers the best features of NoSQL and relational, SQL-based databases. In this session, we will discuss how we can use Java to access a MySQL Document Store, create/remove collections, create/update/remove documents, retrieve, filter, & sort document lists, and leverage raw SQL to retrieve aggregate data for reporting purposes.

Transcript

1. Using MySQL Document Store with Java Scott Stroz MySQL Developer Advocate
2. • MySQL Developer Advocate • Full-stack developer for longer than the term "full-stack developer" has existed. • The only constant in my stack has been MySQL • Die-hard NY Giants fan • Avid golfer • I have the best office mate Obligatory "I Love Me" Slide 3/13/24 Copyright © 2021, Oracle and/or its affiliates 2
3. • "NoSQL" vs relational databases • What is MySQL Document Store? • The anatomy MySQL Document Store data • How to access MySQL Document Store • Connecting to MySQL Document Store with Java • Using the CRUD API • Using raw SQL to query our documents What will we cover? 3/13/24 Copyright © 2021, Oracle and/or its affiliates 3 Photo by Alexander Grey on Unsplash
4. • Slower development time • As more related data is added, CRUD operations can get more complex Copyright © 2021, Oracle and/or its affiliates 4 NoSQL "NoSQL" vs Relational Data • Data can be structured and organized • Easier to run queries for reporting Relational Data • Faster development time • Easier to modify the 'schema' • Simple CRUD API • Data is unstructured • Difficult to run queries for reporting 3/13/24
5. • JSON document storage solution built on top of MySQL • Documents are stored in a MySQL Table • InnoDB storage engine • ACID compliant • Uses JSON datatype • Easy to use CRUD API What is MySQL Document Store? 3/13/24 Copyright © 2021, Oracle and/or its affiliates 5
6. • Schema • Schema - database • Collection • Table • Document • Row in table • Table Columns* • _id • varbinary(32) • doc • JSON • _json_schema • JSON * Others if you add indexes. Anatomy of MySQL Document Store Copyright © 2021, Oracle and/or its affiliates 6 Photo by Kira auf der Heide on Unsplash 2/15/2023
7. • X-Plugin • Installed by default since 8.0.1 • Uses the X Protocol through MySQL Connectors (SDKs) • Connectors available for How do we access MySQL Document Store? 3/13/24 Copyright © 2021, Oracle and/or its affiliates 7 • Node.js • C++ • Python • PHP • .Net • MySQL Shell • Java Photo by Museums Victoria on Unsplash
8. • Demo of using a REST API • Built using Micronaut with Java • Uses 2 different database connections (more on that later) • Most of the demo uses the X-Dev API • The schema looks like à About the Demo Application 3/13/24 Copyright © 2021, Oracle and/or its affiliates 8
9. 3/13/24 Copyright © 2021, Oracle and/or its affiliates 9 NOT PRODUCTION READY!! This Photo by Unknown Author is licensed under CC BY-NC
10. Gradle Dependencies 3/13/24 Copyright © 2021, Oracle and/or its affiliates 10
11. ScoreService 3/13/24 Copyright © 2021, Oracle and/or its affiliates 11
12. ScoreService 3/13/24 Copyright © 2021, Oracle and/or its affiliates 12
13. ScoreService 3/13/24 Copyright © 2021, Oracle and/or its affiliates 13
14. find() CRUD API 3/13/24 Copyright © 2021, Oracle and/or its affiliates 14
15. find() CRUD API 3/13/24 Copyright © 2021, Oracle and/or its affiliates 15
16. find() CRUD API 3/13/24 Copyright © 2021, Oracle and/or its affiliates 16
17. cleanResults() 3/13/24 Copyright © 2021, Oracle and/or its affiliates 17
18. cleanResults() 3/13/24 Copyright © 2021, Oracle and/or its affiliates 18
19. limit() & offset() CRUD API 3/13/24 Copyright © 2021, Oracle and/or its affiliates 19
20. limit() & offset() CRUD API 3/13/24 Copyright © 2021, Oracle and/or its affiliates 20
21. fields() & sort() CRUD API 3/13/24 Copyright © 2021, Oracle and/or its affiliates 21
22. fields() & sort() CRUD API 3/13/24 Copyright © 2021, Oracle and/or its affiliates 22
23. find()with a condition CRUD API 3/13/24 Copyright © 2021, Oracle and/or its affiliates 23
24. find()with a condition CRUD API 3/13/24 Copyright © 2021, Oracle and/or its affiliates 24
25. bind() CRUD API 3/13/24 Copyright © 2021, Oracle and/or its affiliates 25
26. bind() CRUD API 3/13/24 Copyright © 2021, Oracle and/or its affiliates 26
27. bind() with LIKE CRUD API 3/13/24 Copyright © 2021, Oracle and/or its affiliates 27
28. bind() with LIKE CRUD API 3/13/24 Copyright © 2021, Oracle and/or its affiliates 28
29. groupBy() CRUD API 3/13/24 Copyright © 2021, Oracle and/or its affiliates 29
30. groupBy() CRUD API 3/13/24 Copyright © 2021, Oracle and/or its affiliates 30
31. Using Raw SQL – with X-Dev API & Micronaut Data 3/13/24 Copyright © 2021, Oracle and/or its affiliates 31
32. Using Raw SQL – with X-Dev API & Micronaut Data 3/13/24 Copyright © 2021, Oracle and/or its affiliates 32
33. Using Raw SQL – with X-Dev API & Micronaut Data 3/13/24 Copyright © 2021, Oracle and/or its affiliates 33
34. Using Raw SQL – with X-Dev API & Micronaut Data 3/13/24 Copyright © 2021, Oracle and/or its affiliates 34
35. Using Raw SQL – with X-Dev API & Micronaut Data 3/13/24 Copyright © 2021, Oracle and/or its affiliates 35
36. Using Raw SQL – with X-Dev API & Micronaut Data 3/13/24 Copyright © 2021, Oracle and/or its affiliates 36
37. add() CRUD API 3/13/24 Copyright © 2021, Oracle and/or its affiliates 37
38. add() CRUD API 3/13/24 Copyright © 2021, Oracle and/or its affiliates 38
39. modify() CRUD API 3/13/24 Copyright © 2021, Oracle and/or its affiliates 39
40. modify() CRUD API 3/13/24 Copyright © 2021, Oracle and/or its affiliates 40
41. remove() CRUD API 3/13/24 Copyright © 2021, Oracle and/or its affiliates 41
42. remove() CRUD API 3/13/24 Copyright © 2021, Oracle and/or its affiliates 42
43. • "NoSQL" vs Relational Databases • Overview of MySQL Document Store • Anatomy of MySQL Document Store • X-Plugin and X-Protocol for connectivity • Adding Java connector and configuring the connection • Using the CRUD API • Leveraging the power of raw SQL for ad-hoc queries Recap 3/13/24 Copyright © 2021, Oracle and/or its affiliates 43
44. GitHub Resources 3/13/24 Copyright © 2021, Oracle and/or its affiliates 44
45. Document Store Documentation Resources 3/13/24 Copyright © 2021, Oracle and/or its affiliates 45
46. Java Connector Documentation Resources 3/13/24 Copyright © 2021, Oracle and/or its affiliates 46
47. Contact Me 3/13/24 Copyright © 2021, Oracle and/or its affiliates 47 Email scott.stroz@oracle.com LinkedIn https://www.linkedin.com/in/scott-stroz/ Twitter https://twitter.com/boyzoid
48. 3/13/24 Copyright © 2021, Oracle and/or its affiliates 48 Would you like credit for a FREE MySQL Certification Exam or Training? See me after the session!
49. 3/13/24 Copyright © 2021, Oracle and/or its affiliates 49 Q&A Thank You!