Main Content

ormupdate

Update database tables using object relational mapping

Since R2023b

collapse all in page

    Syntax

    ormupdate(conn,ormObject)
    ormupdate(conn,ormObject,CascadeChanges=cascade)

    Description

    ormupdate(conn,ormObject) updates a database table with the properties of the mappable objects in ormObject based on a connection object conn. This method identifies which row of the database each object represents, and then updates each one using the current object properties. For more information on mappable objects, see database.orm.mixin.Mappable.

    example

    ormupdate(conn,ormObject,CascadeChanges=cascade) specifies the logical CascadeChanges (since R2024b) to persist mappable properties on a database. For example, ormupdate(conn,ormObject,CascadeChanges=false) prevents the updating of objects in ForeignKey and LinkTable properties.

    Examples

    collapse all

    Open Live Script

    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

    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, 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"

    Use the receiveProduct method of the Product class to increase the inventory of Toy1. 

    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 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"


    clear ans toy
close(conn)

    Input Arguments

    collapse all

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

    Mappable object, specified as a scalar or vector. For more information on mappable objects, see database.orm.mixin.Mappable.

    • true – ormupdate inserts entries corresponding to the top-level class, and also inserts rows to account for the ForeignKey and LinkTable properties.

    • false – ormupdate does not update objects with the ForeignKey and LinkTable properties in the database, but instead updates only the data in the top-level class and its mapping to rows in other tables.

    Example: ormupdate(conn,newOrder,CascadeChanges=false);

    Version History

    Introduced in R2023b

    expand all

    See Also

    | | |