ormwrite

Insert mappable objects into database

Since R2023b

Syntax

ormwrite(conn,ormObject)

ormObject = ormwrite(conn,ormObject)

Description

ormwrite(conn,ormObject) inserts one or more mappable objects into rows of a database table, where conn is a database connection object and ormObject contains the objects to be inserted. For more information on mappable objects, see database.orm.mixin.Mappable.

ormObject = ormwrite(conn,ormObject) returns mappable objects from the database. The database automatically generates PrimaryKey property values for objects whose properties are labeled with the AutoIncrement property attribute.

Examples

Insert Database Row into New Table

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 = recieveProduct(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

First, create an 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");

Use the orm2sql function to 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))"

Create a Product object 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, then 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"

clear toy
close(conn)

Insert Database Rows Using Autoincrementing Primary Keys

This example depends on the ProductAutoInc 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. The ID property attribute is the primary key and it is set to AutoIncrement.

classdef ProductAutoInc < database.orm.mixin.Mappable

    properties(PrimaryKey, ColumnName = "ProductNumber", AutoIncrement)
        ID int32
    end
    
    properties
        Name string
        Description string
        Quantity int32
    end

    properties(ColumnName = "UnitCost")
        CostPerItem int32
    end

    properties(ColumnName = "Manufacturer")
        Supplier string
    end

    properties(ColumnType = "date")
        InventoryDate datetime
    end

    methods
        function obj = ProductAutoInc(name,description,supplier,cost,quantity,inventoryDate)
            if nargin ~= 0
                inputElements = numel(name);
                if 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).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).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 = recieveProduct(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

First, create an sqlite database file that does not require a connection to a live database.

filename = "autoIncrementDemo.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 ProductAutoInc");

Create a ProductAutoInc object and store it on the database using the ormwrite function. You can return the object to the workspace by using the optional output argument.

obj = ProductAutoInc("Toy1","Descr1","CompanyA",18.99,100,datetime(2023,7,5))

obj = 
  ProductAutoInc with properties:

               ID: []
             Name: "Toy1"
      Description: "Descr1"
         Quantity: 100
      CostPerItem: 19
         Supplier: "CompanyA"
    InventoryDate: 05-Jul-2023

obj = ormwrite(conn,obj)

obj = 
  ProductAutoInc with properties:

               ID: 1
             Name: "Toy1"
      Description: "Descr1"
         Quantity: 100
      CostPerItem: 19
         Supplier: "CompanyA"
    InventoryDate: 05-Jul-2023

In this example, the ID property of the output object has a value of 1. Use the sqlread function to read the table and verify that the database automatically filled in the primary key value.

sqlread(conn,"ProductAutoInc")

ans=1×7 table
    ProductNumber     Name     Description    Quantity    UnitCost    Manufacturer           InventoryDate        
    _____________    ______    ___________    ________    ________    ____________    ____________________________

          1          "Toy1"     "Descr1"        100          19        "CompanyA"     "2023-07-05 00:00:00.000000"

Instantiate a ProductAutoInc class with an array of objects with the ID property attribute chosen as the primary key and set it to AutoIncrement. The ID property is initially empty for all products.

products = ProductAutoInc(["Toy2";"Toy3";"Toy4"],["Descr2";"Descr3";"Descr4"], ...
    ["CompanyB";"CompanyC";"CompanyD"],[15.99;24.99;249.99],[500;250;150],datetime(2013,8,12:14))

products=1×3 ProductAutoInc array with properties:
    ID
    Name
    Description
    Quantity
    CostPerItem
    Supplier
    InventoryDate

IDS = [products.ID]

IDS =

  0x0 empty int32 matrix

Store the array of objects on the database using the ormwrite function and verify that the database automatically fills in the IDs.

products = ormwrite(conn,products)

products=1×3 ProductAutoInc array with properties:
    ID
    Name
    Description
    Quantity
    CostPerItem
    Supplier
    InventoryDate

IDS = [products.ID]

IDS = 1x3 int32 row vector

   2   3   4

Input Arguments

conn — Database connection

connection object

Database connection, specified as a connection object created from any of the following:

• database

• mysql

• postgresql

• sqlite

ormObject — Mappable object

scalar | vector

Mappable object to be inserted in the database table, specified as a scalar or vector. For more information on mappable objects, see database.orm.mixin.Mappable.

Version History

Introduced in R2023b