Read and Write Objects to Relational Database Using ORM Workflow
This example shows the basic operations for reading and writing objects to a relational database using Object Relational Mapping (ORM). This example depends on the Product class that maps to a database table. This class contains several properties that map to the database, as well as some methods that alter those properties.
classdef (TableName = "products") Product < database.orm.mixin.Mappable properties(PrimaryKey,ColumnName = "ProductNumber") ID int32 end properties Name string Description string Quantity int32 end properties(ColumnName = "UnitCost") CostPerItem double end properties(ColumnName = "Manufacturer") Supplier string end properties(ColumnType = "date") InventoryDate datetime end methods function obj = Product(id,name,description,supplier,cost,quantity,inventoryDate) if nargin ~= 0 inputElements = numel(id); if numel(name) ~= inputElements || ... numel(description) ~= inputElements || ... numel(supplier) ~= inputElements || ... numel(cost) ~= inputElements || ... numel(quantity) ~= inputElements || ... numel(inventoryDate) ~= inputElements error('All inputs must have the same number of elements') end % Preallocate by creating the last object first obj(inputElements).ID = id(inputElements); obj(inputElements).Name = name(inputElements); obj(inputElements).Description = description(inputElements); obj(inputElements).Supplier = supplier(inputElements); obj(inputElements).CostPerItem = cost(inputElements); obj(inputElements).Quantity = quantity(inputElements); obj(inputElements).InventoryDate = inventoryDate(inputElements); for n = 1:inputElements-1 % Fill in the rest of the objects obj(n).ID = id(n); obj(n).Name = name(n); obj(n).Description = description(n); obj(n).Supplier = supplier(n); obj(n).CostPerItem = cost(n); obj(n).Quantity = quantity(n); obj(n).InventoryDate = inventoryDate(n); end end end function obj = adjustPrice(obj,amount) arguments obj (1,1) Product amount (1,1) {mustBeNumeric} end obj.CostPerItem = obj.CostPerItem + amount; end function obj = shipProduct(obj,amount) arguments obj (1,1) Product amount (1,1) {mustBePositive,mustBeInteger} end obj.Quantity = obj.Quantity - amount; end function obj = receiveProduct(obj,amount) arguments obj (1,1) Product amount (1,1) {mustBePositive,mustBeInteger} end obj.Quantity = obj.Quantity + amount; obj.InventoryDate = datetime('today'); end end end
Create a Database Connection
To read and write to a database using ORM, create a sqlite database file that does not require a connection to a live database.
filename = "orm_demo.db"; if exist(filename,"file") conn = sqlite(filename); else conn = sqlite(filename,"create"); end % Remove it to maintain consistency execute(conn,"DROP TABLE IF EXISTS products");
Populate Database Table with Objects
The orm2sql function shows how a mapped MATLAB® class is represented as a database table. Display the database column information based on the class defined in Product.m.
orm2sql(conn,"Product")ans =
"CREATE TABLE products
(ProductNumber double,
Name text,
Description text,
Quantity double,
UnitCost double,
Manufacturer text,
InventoryDate date,
PRIMARY KEY (ProductNumber))"
Use the sqlfind function to verify that the products table does not exist.
sqlfind(conn,"products")ans =
0×5 empty table
Catalog Schema Table Columns Type
_______ ______ _____ _______ ____
Insert a Scalar Object
Create a Product object and use it to create and populate a table.
toy = Product(1,"Toy1","Descr1","CompanyA",24.99,0,datetime(2023,1,1))
toy =
Product with properties:
ID: 1
Name: "Toy1"
Description: "Descr1"
Quantity: 0
CostPerItem: 24.9900
Supplier: "CompanyA"
InventoryDate: 01-Jan-2023
Use the ormwrite function to populate the database with the data from toy, and use the sqlread function to read the table and verify the results.
ormwrite(conn,toy);
sqlread(conn,"products")ans=1×7 table
ProductNumber Name Description Quantity UnitCost Manufacturer InventoryDate
_____________ ______ ___________ ________ ________ ____________ ____________________________
1 "Toy1" "Descr1" 0 24.99 "CompanyA" "2023-01-01 00:00:00.000000"
Insert an Array of Objects
Instantiate a Product class with an array of objects.
productArray = Product(2:10,... ["Toy2","Toy3","Toy4","Toy5","Toy6","Toy7","Toy8",... "Toy9","Toy10"],... ["Descr2","Descr3","Descr4","Descr5","Descr6","Descr7",... "Descr8","Descr9","Descr10"],... ["CompanyB","CompanyA","CompanyC","CompanyB","CompanyA","CompanyD","CompanyE","CompanyF","CompanyG"],... [5.99,4.99,14.99,12.99,17.99,4.99,149.99,10.99,5.99],... [1000,350,225,25,600,300,50,100,1250],... repmat(datetime(2023,1,1),1,9))
productArray=1×9 Product array with properties:
ID
Name
Description
Quantity
CostPerItem
Supplier
InventoryDate
% View the last object
productArray(end)ans =
Product with properties:
ID: 10
Name: "Toy10"
Description: "Descr10"
Quantity: 1250
CostPerItem: 5.9900
Supplier: "CompanyG"
InventoryDate: 01-Jan-2023
Use ormwrite to insert multiple objects at the same time.
% Insert the object array into the database and view the results ormwrite(conn,productArray); sqlread(conn,"products")
ans=10×7 table
ProductNumber Name Description Quantity UnitCost Manufacturer InventoryDate
_____________ _______ ___________ ________ ________ ____________ ____________________________
1 "Toy1" "Descr1" 0 24.99 "CompanyA" "2023-01-01 00:00:00.000000"
2 "Toy2" "Descr2" 1000 5.99 "CompanyB" "2023-01-01 00:00:00.000000"
3 "Toy3" "Descr3" 350 4.99 "CompanyA" "2023-01-01 00:00:00.000000"
4 "Toy4" "Descr4" 225 14.99 "CompanyC" "2023-01-01 00:00:00.000000"
5 "Toy5" "Descr5" 25 12.99 "CompanyB" "2023-01-01 00:00:00.000000"
6 "Toy6" "Descr6" 600 17.99 "CompanyA" "2023-01-01 00:00:00.000000"
7 "Toy7" "Descr7" 300 4.99 "CompanyD" "2023-01-01 00:00:00.000000"
8 "Toy8" "Descr8" 50 149.99 "CompanyE" "2023-01-01 00:00:00.000000"
9 "Toy9" "Descr9" 100 10.99 "CompanyF" "2023-01-01 00:00:00.000000"
10 "Toy10" "Descr10" 1250 5.99 "CompanyG" "2023-01-01 00:00:00.000000"
Read Objects from a Database
Once a class has been mapped to an existing database table, objects of that class can be constructed by reading data from the database.
Use the ormread method to read data from the database. This method uses the mapping to determine which tables to read, and also determines how the column values correspond to the properties.
% Clear all Product objects from the workspace clear toy productArray % Recreate the objects by reading from the database and view the first and % last allProducts = ormread(conn,"Product")
allProducts=10×1 Product array with properties:
ID
Name
Description
Quantity
CostPerItem
Supplier
InventoryDate
allProducts(1)
ans =
Product with properties:
ID: 1
Name: "Toy1"
Description: "Descr1"
Quantity: 0
CostPerItem: 24.9900
Supplier: "CompanyA"
InventoryDate: 01-Jan-2023
allProducts(end)
ans =
Product with properties:
ID: 10
Name: "Toy10"
Description: "Descr10"
Quantity: 1250
CostPerItem: 5.9900
Supplier: "CompanyG"
InventoryDate: 01-Jan-2023
Read in a Subset of Objects
Use the ormread method with the RowFilter name-value argument to import a subset of the objects in the database.
Filter the items where CostPerItem is less than $10.
rf = rowfilter("CostPerItem"); rf = rf.CostPerItem < 10; inexpensiveItems = ormread(conn,"Product",RowFilter=rf)
inexpensiveItems=4×1 Product array with properties:
ID
Name
Description
Quantity
CostPerItem
Supplier
InventoryDate
% Verify by checking the properties of one of the objects
inexpensiveItems(1)ans =
Product with properties:
ID: 2
Name: "Toy2"
Description: "Descr2"
Quantity: 1000
CostPerItem: 5.9900
Supplier: "CompanyB"
InventoryDate: 01-Jan-2023
Update the Database with Objects
The ormupdate method updates existing rows in a database table based on changes to one or more mapped MATLAB® objects.
Use the receiveProduct method of the Product class to increase the inventory of Toy1.
% Find the Toy1 product on the database rf = rowfilter("Name"); rf = rf.Name == "Toy1"; toy = ormread(conn,"Product",RowFilter=rf)
toy =
Product with properties:
ID: 1
Name: "Toy1"
Description: "Descr1"
Quantity: 0
CostPerItem: 24.9900
Supplier: "CompanyA"
InventoryDate: 01-Jan-2023
% Use the receiveProduct method of Product to increase the amount of % products in the inventory toy = receiveProduct(toy,500)
toy =
Product with properties:
ID: 1
Name: "Toy1"
Description: "Descr1"
Quantity: 500
CostPerItem: 24.9900
Supplier: "CompanyA"
InventoryDate: 27-Jun-2024
Use the fetch function to see that these changes are not reflected in the database.
fetch(conn,"SELECT * FROM products WHERE Name = 'Toy1'")ans=1×7 table
ProductNumber Name Description Quantity UnitCost Manufacturer InventoryDate
_____________ ______ ___________ ________ ________ ____________ ____________________________
1 "Toy1" "Descr1" 0 24.99 "CompanyA" "2023-01-01 00:00:00.000000"
Use the ormupdate method to push the changes made in MATLAB® to the database. Then, use the fetch function to verify that Quantity and InventoryDate are updated in the database.
ormupdate(conn,toy);
fetch(conn,"SELECT * FROM products WHERE Name = 'Toy1'")ans=1×7 table
ProductNumber Name Description Quantity UnitCost Manufacturer InventoryDate
_____________ ______ ___________ ________ ________ ____________ ____________________________
1 "Toy1" "Descr1" 500 24.99 "CompanyA" "2024-06-27 00:00:00.000000"
Refresh Objects to Match the Database
You can refresh an object in MATLAB® to reflect the current state of the database. First, change the quantity of Toy1 to 1000 and view the result using the fetch function.
execute(conn,"UPDATE products SET Quantity = 1000 WHERE Name = 'Toy1'"); fetch(conn,"SELECT * FROM products WHERE Name = 'Toy1'")
ans=1×7 table
ProductNumber Name Description Quantity UnitCost Manufacturer InventoryDate
_____________ ______ ___________ ________ ________ ____________ ____________________________
1 "Toy1" "Descr1" 1000 24.99 "CompanyA" "2024-06-27 00:00:00.000000"
Use the ormread method to refresh the properties of the object.
toy = ormread(conn,toy)
toy =
Product with properties:
ID: 1
Name: "Toy1"
Description: "Descr1"
Quantity: 1000
CostPerItem: 24.9900
Supplier: "CompanyA"
InventoryDate: 27-Jun-2024
clear allProducts inexpensiveItems toy close(conn)
