Main Content

ormwrite

Insert mappable objects into database

Since R2023b

    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.

    example

    ormwrite(conn,ormObject,CascadeChanges=cascade) specifies the logical CascadeChanges (since R2024b) to persist mappable properties on a database. For example, ormwrite(conn,ormObject,CascadeChanges=false) prevents the insertion of objects in ForeignKey and LinkTable properties. The database automatically generates PrimaryKey property values for objects whose properties are labeled with the AutoIncrement property attribute.

    example

    ormObject = ormwrite(___) returns mappable objects from the database.

    Examples

    collapse all

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

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

    collapse all

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

    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.

    Property persistence, specified as true or false.

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

    • falseormwrite does not insert objects with the ForeignKey and LinkTable properties in the database, but instead inserts only the data in the top-level class and its mapping to rows in other tables.

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

    Version History

    Introduced in R2023b

    expand all