[JAVA] Interacting with a MySQL database using Apache Cayenne's ORM (Object-Relational Mapping) feature
In this article, you'll use the Object-Relational Mapping (ORM) feature of ** Apache Cayenne ** to work with a ** MySQL ** database from a small ** Java ** project built with Maven.
- This blog is a translation from the English version. You can check the original from here. We use some machine translation. We would appreciate it if you could point out any translation errors. *
Project settings
Maven is one of the most popular tools for building Java applications. In this section you will set the project dependencies (this assumes your system is empty).
As a first step, let's add the following dependencies to add the Apache Cayenne and the MYSQL connector (specifically the JDBC driver).
<dependency>
<groupId>org.apache.cayenne</groupId>
<artifactId>cayenne-server</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.14</version>
<scope>runtime</scope>
</dependency>
The modeler plugin must also be defined in pom.xml. This is the maven command used to launch the Apache Cayenne Modeler plugin from the command line when you open the mapping file for your current project.
<plugin>
<groupId>org.apache.cayenne.plugins</groupId>
<artifactId>cayenne-modeler-maven-plugin</artifactId>
<version>4.0.1</version>
<configuration>
<modelFile>${project.basedir}/src/main/resources/cayenne-blog.xml</modelFile>
</configuration>
</plugin>
Modelers are generally the recommended way to design and configure the mapping between a database and a real Java model class.
You can download it from this page. You will need to download a version made for your particular OS, or you can use the cross-platform version (JAR) included as a Maven plugin instead. The latest stable version at the time of writing this article is version [4.1](https://github.com/apache/cayenne/blob/STABLE-4.1/RELEASE-NOTES.txt?spm=a2c65.11461447.0.0.640 c764cCNwoDP & file = RELEASE-NOTES.txt). This version requires Java 1.8 or later.
As the next step, let's build the project with the mvn clean install command, start the modeler GUI with the mvn cayenne-modeler: run command, and output this screen.
If you're using something other than MySQL, you'll also need to change the JDBC driver, so the configuration is database-dependent. Here is a complete list with the corresponding drivers.
Mapping and database design
For example, suppose you have an existing database called cayenne_blog that shows the one-to-many relationship between two tables, in which the following parameters are defined.
--author: id (PK) and name --article: id (PK), title, content, author_id (FK) Now consider a SQL command that references the DB in this example.
CREATE TABLE `author` (
`id` int(11) NOT NULL,
`name` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Indexes for table `article`
--
ALTER TABLE `article`
ADD PRIMARY KEY (`id`),
ADD KEY `author_id` (`author_id`);
--
-- Indexes for table `author`
--
ALTER TABLE `author`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for table `article`
--
ALTER TABLE `article`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `author`
--
ALTER TABLE `author`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
--
-- Constraints for table `article`
--
ALTER TABLE `article`
ADD CONSTRAINT `article_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `author` (`id`);
db.sql](https://github.com/dassiorleando/apache-cayenne/blob/master/db.sql) Import the file into phpMyAdmin or run the following command from your terminal's MYSQL server Masu: mysql <db.sql.
Now, let's add a plugin called cayenne-maven-plugin, which is a modeler plugin setting, to pom.xml.
<plugin>
<groupId>org.apache.cayenne.plugins</groupId>
<artifactId>cayenne-maven-plugin</artifactId>
<version>4.0.1</version>
<configuration>
<map>${project.basedir}/src/main/resources/blog.map.xml</map>
<dataSource>
<driver>com.mysql.jdbc.Driver</driver>
<url>jdbc:mysql://localhost:3306/cayenne_blog</url>
<username>root</username>
<password>root</password>
</dataSource>
<dbImport>
<defaultPackage>com.dassiorleando.apachecayenne.models</defaultPackage>
</dbImport>
</configuration>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.44</version>
<scope>runtime</scope>
</dependency>
</dependencies>
</plugin>
Here you specify where the ORM saves the data (<data source>) and where the mapping file is saved (<map>). From this setting, the database name is cayenne_blog, the user database credentials are root: root (update to match that of the MYSQL server), and the default package is that of the project structure (create a model class). You can see that you are using the package).
Finally, use the cbimport command from the project command line: mvn cayenne: cdbimport. cdbimport synchronizes the XML map file with an existing database, so you should get a log similar to the following:
INFO] +++ Connecting: SUCCESS.
[INFO] Detected and installed adapter: org.apache.cayenne.dba.mysql.MySQLAdapter
[INFO] Table: cayenne_blog.AUTO_PK_SUPPORT
[INFO] Table: cayenne_blog.article
[INFO] Table: cayenne_blog.author
[INFO] Db Relationship : toOne (article.author_id, author.id)
[INFO] Db Relationship : toMany (author.id, article.author_id)
[INFO]
[INFO] Map file does not exist. Loaded db model will be saved into '/Users/dassiorleando/projects/opensource/apache-cayenne/src/main/resources/blog.map.xml'
[INFO]
[INFO] Detected changes:
[INFO] Create Table article
[INFO] Create Table author
[INFO] Create Table AUTO_PK_SUPPORT
[INFO]
[WARNING] Can't find ObjEntity for author
[WARNING] Db Relationship (Db Relationship : toOne (article.author_id, author.id)) will have GUESSED Obj Relationship reflection.
[INFO] Migration Complete Successfully.
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 4.165 s
[INFO] Finished at: 2019-07-22T17:40:36+01:00
[INFO] Final Memory: 10M/164M
[INFO] ------------------------------------------------------------------------
Let's generate a Java class: mvn cayenne: cgen
[INFO] Scanning for projects...
[INFO]
[INFO] ------------------------------------------------------------------------
[INFO] Building apache-cayenne 0.0.1-SNAPSHOT
[INFO] ------------------------------------------------------------------------
[INFO]
[INFO] --- cayenne-maven-plugin:4.0.1:cgen (default-cli) @ apache-cayenne ---
[INFO] Generating superclass file: /Users/dassiorleando/projects/opensource/apache-cayenne/src/main/java/com/dassiorleando/apachecayenne/model/auto/_Article.java
[INFO] Generating class file: /Users/dassiorleando/projects/opensource/apache-cayenne/src/main/java/com/dassiorleando/apachecayenne/model/Article.java
[INFO] Generating superclass file: /Users/dassiorleando/projects/opensource/apache-cayenne/src/main/java/com/dassiorleando/apachecayenne/model/auto/_Author.java
[INFO] Generating class file: /Users/dassiorleando/projects/opensource/apache-cayenne/src/main/java/com/dassiorleando/apachecayenne/model/Author.java
[INFO] Generating superclass file: /Users/dassiorleando/projects/opensource/apache-cayenne/src/main/java/com/dassiorleando/apachecayenne/model/auto/_AutoPkSupport.java
[INFO] Generating class file: /Users/dassiorleando/projects/opensource/apache-cayenne/src/main/java/com/dassiorleando/apachecayenne/model/AutoPkSupport.java
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
Now you should immediately see that the structure of your project has changed. We are talking about _Article.java and _Author.java (both are extensions of CayenneDataObject). You can see that the same settings (in XML format) are in the resources / blog.map.xml file.
Then enter the command mvn cayenne-modeler: run to start the modeler. Click New Project and specify the Data Domain Nam (blog) for the mapping file on the next page. Here, save it in the same folder as the created map file.
Then click File> Import Datamap to access the UI that allows you to link to the datamap.
Once the cayenne-blog.xml and blog.map.xml are linked, the modeler can update the model to reflect it in the class. It will be as follows.
Apache Cayenne supports three major key strategies.
--Cayenne-Generated: Manage PK generation. --Database generation: PK is managed by the database engine. --Custom Sequence: You need to implement your custom logic here. In the screenshot below, the author table is populated with id, an auto-incremented integer maintained in the database.
Note: Create a DataNode that exactly matches our database configuration, as shown in the following figure.
In the Maven project's resources directory, create a special XML file called cayenne-blog with the following content:
<?xml version="1.0" encoding="utf-8"?>
<domain project-version="9">
<map name="blog"/>
<node name="datanode"
factory="org.apache.cayenne.configuration.server.XMLPoolingDataSourceFactory"
schema-update-strategy="org.apache.cayenne.access.dbsync.SkipSchemaUpdateStrategy">
<map-ref name="blog"/>
<data-source>
<driver value="com.mysql.jdbc.Driver"/>
<url value="jdbc:mysql://localhost:3306/cayenne_blog"/>
<connectionPool min="1" max="1"/>
<login userName="root" password="root"/>
</data-source>
</node>
</domain>
The XMLPoolingDataSourceFactory is responsible for loading JDBC connection information from the XML resource associated with the DataNodeDescriptor.
Mapping structure
Apache Cayenne has its own syntax for writing models.
--DataNode (<node>): Database model. It contains all the information needed to connect to the database, including the database name, drivers, and database user credentials.
--DataMap (<data-map>): A container of persistent entities with which you have a relationship.
--DbAttribute (<db-attribute>): Represents a column in a database table.
--DbEntity (<db-entity>): A single database table or view model. You can have a relationship with DbAttributes.
--ʻObjEntity (: A single persistent Java class model consisting of ObjAttributes, which corresponds to a property of an entity class, and ObjRelationships, a property that has a different entity type. .. --ʻEmbeddable (<embeddable>) : A Java class model that acts as a property of ObjEntity, but supports multiple columns in the database.
--procedure (<procedure>): Register the stored procedure in the database. Used when mapping.
For details, refer to the following Guide.
Applying CRUD to your model
In this section, we will apply some basic operations to the models (ʻArticle and ʻAuthor). ʻThe ObjectSelectclass has some static methods that are useful for querying the database, but for inserts and updates, it uses theserver's context (ObjectContext)` that is used to commit changes. is needed.
Here's how to get the context of the server associated with our project.
ServerRuntime cayenneRuntime = ServerRuntime.builder()
.addConfig("cayenne-blog.xml")
.build();
ObjectContext context = cayenneRuntime.newContext();
Note: The cayenne-blog.xml file is located in your project's resource folder.
Creating an object
You can create an object with the following query.
/**
* Save an author
* @param name
*/
public void save(String name) {
// Save a single author
Author author = this.context.newObject(Author.class);
author.setName(name);
context.commitChanges();
}
Reading an object
You can load the object with the following query.
/**
* Find an author by its ID
* @param id the author's ID
* @return the matched author or null if not existing
*/
public Author findById(int id) {
Author author = Cayenne.objectForPK(context, Author.class, id);
return author;
}
/**
* Looking for an author by name
* @param name the name to look up with
* @return the first matched author or null if not existing
*/
public Author findByName(String name) {
Author foundAuthor = ObjectSelect.query(Author.class)
.where(Author.NAME.eq(name))
.selectOne(this.context);
return foundAuthor;
}
/**
* Find authors by name starting with(like%)
* @param partName expected name part
* @return list of authors
*/
public List<Author> findByNameLike(String partName) {
// Let's apply a case-insensitive LIKE on the Author's name column
// We get all the authors with their name starting with "partName"
List<Author> authorsLike = ObjectSelect.query(Author.class)
.where(Author.NAME.likeIgnoreCase(partName + "%"))
.select(context);
return authorsLike;
}
/**
* Find authors by name ending with
* @param partName expected name part
* @return list of authors
*/
public List<Author> findByNameEndWith(String partName) {
// All authors with names ending with "partName"
List<Author> authorsEnd = ObjectSelect.query(Author.class)
.where(Author.NAME.endsWith(partName))
.select(context);
return authorsEnd;
}
Find all records in a class
You can query all previously saved authors using the following query:
public List<Author> findAll() {
// Looking for all authors
List<Author> authors = ObjectSelect
.query(Author.class)
.select(this.context);
return authors;
}
Object update
You can update the object with the following query.
/**
* Update an author
* @param id the author's ID
* @param newName the new name to set
* @return true for a successful operation and false unknown author
*/
public boolean update(int id, String newName) {
if (StringUtils.isEmpty(newName)) return false;
// Get the author to update
Author author = this.findById(id);
if (author == null) return false;
// Set its name
author.setName(newName);
context.commitChanges();
return true;
}
Relationship to objects
Here's how to link an article written by an author with the author.
/**
* Attach a fake article to the author
* @param id the author's ID
* @return true for a successful operation and false unknown author
*/
public boolean attachArticle(int id) {
// Get the author to link with
Author author = this.findById(id);
if (author == null) return false;
// Create a fake article and link it to the current author
Article article = context.newObject(Article.class);
article.setTitle("My post title");
article.setContent("The content");
article.setAuthor(author);
context.commitChanges();
// Get author's linked data (articles)
List<Article> articles = author.getArticles();
return true;
}
Delete object
You can delete the object with the following query.
/**
* Delete an author
* @param id author's ID
* @return true for a successful operation and false unknown author
*/
public boolean delete(int id) {
// Get the author to delete
Author author = this.findById(id);
if (author != null) {
context.deleteObjects(author);
context.commitChanges();
return true;
} else {
return false;
}
}
Delete all records in the class
The Apache Cayenne API allows you to delete all records in a table using SQLTemplate, but here we just provide a basic SQL delete query along with the target class.
// SQL delete queries for Author and Article classes
SQLTemplate deleteArticles = new SQLTemplate(Article.class, "delete from article");
SQLTemplate deleteAuthors = new SQLTemplate(Author.class, "delete from author");
// Applying the deletion queries
context.performGenericQuery(deleteArticles);
context.performGenericQuery(deleteAuthors);
Expression and Expression Factory
There are many possibilities for building advanced queries using Apache Cayenne. However, in most cases Expression and ExpressionFactory I'm using the class, but here's an example I will introduce some of them.
--likeExp: Used to build a LIKE expression.
--likeIgnoreCaseExp: Used to build a LIKE_IGNORE_CASE expression.
--containsExp: An expression used for a LIKE query that has a pattern that matches anywhere in the string.
--containsIgnoreCaseExp: Similar to containsExp, but uses a case-insensitive approach.
--startsWithExp: The pattern matches the beginning of the string.
--startsWithIgnoreCaseExp: Similar to startsWithExp, but uses a case-insensitive approach.
--ʻEndsWithExp: An expression that matches the end of a string. --ʻEndsWithIgnoreCaseExp: An expression that matches the end of a string using a case-insensitive approach.
--ʻExpTrue: Used for True expressions. --ʻExpFalse: Used for Boolean False expressions.
--ʻAndExp: Used to concatenate two expressions using the ʻand operator.
--ʻOrExp: Used to concatenate two expressions using the ʻor operator.
Conclusion
In this tutorial, you learned how to configure Apache Cayenne's object-relational mapping (ORM) functionality for a MySQL database and basic CRUD operations as an example of a query with a one-to-many relationship. The full source code for this article can be found on Github.
- Alibaba Cloud is the No. 1 (2019 Gartner) cloud infrastructure operator in the Asia Pacific region with two data centers in Japan and more than 60 availability zones in the world. Click here for more information on Alibaba Cloud. Alibaba Cloud Japan Official Page *