In kbmmw 5.02.1, the function of ORM is added (this may not be exactly the same as the definition of other languages), we simply think

It is a class and database conversion. Today, I will introduce how to realize the mutual relationship between the class and the database through the ORM function of kbmmw.

Conversion and operation.

 Prerequisites: delphi 10.2.1,  kbmmw 5.02.1,   unidac 7.0.2

Start sqlserver2008 manager and start the database.

Open delphi, create a standard project, place several controls like the figure

Set uniconnection1 to connect to the sql server 2008 database

ok

Add a few necessary units and set up the initialization code.

unit mainp;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs;
  kbmMWORM, Vcl.StdCtrls, Data.DB, DBAccess, Uni, kbmMWCustomConnectionPool;
  kbmMWCustomSQLMetaData, kbmMWMSSQLMetaData, kbmMWUniDAC, UniProvider,
  SQLServerUniProvider, DASQLMonitor, UniSQLMonitor ;

type
  TForm2 = class(TForm)
    kbmMWUNIDACConnectionPool1: TkbmMWUNIDACConnectionPool;
    kbmMWMSSQLMetaData1: TkbmMWMSSQLMetaData;
    UniConnection1: TUniConnection;
    Button1: TButton;
    SQLServerUniProvider1: TSQLServerUniProvider;
    UniSQLMonitor1: TUniSQLMonitor;
    Button2: TButton;
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
    ORM: TkbmMWORM;
  end;

where
  Form2: TForm2;

implementation

{ $R *.dfm } 
procedure TForm2.FormCreate(Sender: TObject);
 begin 
  ORM: =TkbmMWORM. Create (kbmMWUNIDACConnectionPool1); // Create an ORM object
  ORM.QueryMode: = mwoqmMixed;
end ;

procedure TForm2.FormDestroy(Sender: TObject);
begin
  ORM.Free; // release the ORM object
end;

end.

Now create a new unit, define a contact human, and add the corresponding annotation information.

unit uContact;

interface
uses  System.Generics.Collections,kbmMWNullable,kbmMWRTTI,  kbmMWORM,DB;


type

    [kbmMW_Table( ' name:CONTACT ' )] // table name is contact
     TContact = class
     private
        FID:kbmMWNullable<string>;
        FName:kbmMWNullable<string>;
        FAddress:kbmMWNullable<string>;
        FZipCode:kbmMWNullable<string>;
        FCity:kbmMWNullable<string>;
        FComments:kbmMWNullable < string > ;
        public 
        [kbmMW_Field( ' primary:true, generator:shortGuid ' ,ftString, 40 )] //Primary key, and automatically generated as GUID
         property ID:kbmMWNullable< string > read FID write FID;
        [kbmMW_Field('name:NAME',ftString,50)]
        property Name:kbmMWNullable<string> read FName write FName;
        [kbmMW_Field('name:ADDRESS',ftString,80)]
        property Address:kbmMWNullable<string> read FAddress write FAddress;
        [kbmMW_Field('name:ZIPCODE',ftInteger)]
        property ZipCode:kbmMWNullable<string> read FZipCode write FZipCode;
         [kbmMW_Field('name:city',ftString,50)]
        property City:kbmMWNullable<string> read FCity write FCity;
        [kbmMW_Field('name:comments',ftString,200)]
        property Comments:kbmMWNullable<string> read FComments write FComments;
       end;

implementation



initialization
   TkbmMWRTTI.EnableRTTI([TContact]); //Turn on RTTI 
   kbmMWRegisterKnownClasses([TContact]);//Registered object

end.

Ok, let’s go back to the main form, add the corresponding code, we will first create the corresponding table.

procedure TForm2.Button1Click(Sender: TObject);
begin

 ORM.CreateTable([Tcontact]);

end;

Compile and run, click the Create Library button to display the successful construction.

Let’s see what happens behind the scenes? First of all, we first in the sql monitor, look at what the background has done?

 

Through sql monitor, we can see very clearly that kbmmw first queries the secondary database for this table. If there is no such table, it is defined according to Tcontact.

The field is used to generate the corresponding SQL statement, execute this SQL, and generate the corresponding table in the database.

 

The production in the database is completely ok. We will generate some data in the next step to see if it is normal.

Generate data code

procedure TForm2.Button3Click (Sender: TObject);
was
   t1, t2, t3: TContact;
begin

   t1:=TContact.Create;
   t1.Name: = ' red fish ' ;
   t1.Address: = ' Do not tell you ' ;
   t1.ZipCode:='1234567';
   t1.City: = ' Siping ' ;
   t1.Comments: = ' old card program coder' ;

   ORM.Persist (t1);


   t2:=TContact.Create;
   t2.Name: = ' Dry hard ' ;
   t2.Address: = ' Still don't tell you ' ;
   t2.ZipCode:='54565552';
   t2.City: = ' DC' ;
   t2.Comments: = ' Transformers maker ' ;

   ORM.Persist(t2);

   t3:=TContact.Create;
   t3.Name: = ' Jack' ;
   t3.Address: = 'I just don't tell you ' ;
   t3.ZipCode:='252556';
   t3.City: = ' NY ' ;
   t3.Comments: = ' Really handsome! ' ;

   ORM.Persist(t3);

  Showmessage( ' operation successful ' );


end;

Look at the background there are those sql. In fact, this Persist is updated and inserted, if the update fails, it is inserted.

Look at the effect of the generated data in the database.

Completely correct.

Let’s take a look at how to query data through ORM.

There are three ways to query data in kbmmw orm.

// Query mode controls what syntax to use for queries.
// mwoqmMW (default) use kbmMW’s SQL syntax and automatically
//   rewrite the query to match supported databases.
// mwoqmNative provides the query string without alterations to the
//   database.
// mwoqmMixed default use kbmMW’s SQL syntax with automatic rewrite
//   unless the first character in the query statement is #
TkbmMWORMQueryMode = (mwoqmMW,mwoqmNative,mwoqmMixed);

By default, kbmmw’s own SQL syntax is used and automatically converted to the corresponding database syntax.

The second is the syntax of directly using the target database.

The third is a hybrid mode. If the first letter of the query is not #, use kbmmw’s own sql syntax.

We use mixed mode query

procedure TForm2.Button5Click(Sender: TObject);
var
   o:TObjectList<Tcontact>;
begin
     o:=TObjectList<Tcontact>(orm.Query(Tcontact,'#SELECT * FROM contact',true));

     Showmessage( ' shared ' +o.Count.ToString + ' strip record ' );

     o.Free;

end;

or

procedure TForm2.Button5Click(Sender: TObject);
var
   o:TObjectList<Tcontact>;
begin
     o:=orm.QueryList<Tcontact>('#SELECT * FROM contact');
     Showmessage( ' shared ' +o.Count.ToString + ' strip record ' );
     o.Free;
end;

The same is true for background SQL.

Let’s query a single piece of data, there are two ways to query the heads-up data.

One is the SQL method, an ORM method

First introduce the sql way

procedure TForm2.Button6Click (Sender: TObject);
was
   o: Tcontact;
begin
     o:=orm.Query<Tcontact>('#SELECT * FROM contact WHERE NAME=''Jack''');

     If o= nil  then 
        begin 
          showmessage( ' no query to data! ' );
          exit;
        end;

     showmessage(o.Comments);

     o.Free;

end;

Query using kbmw ORM

procedure TForm2.Button7Click (Sender: TObject);
was
   o: Tcontact;
   b:boolean;
Begin 
     o: =orm.Query<Tcontact>([ ' name ' ],[ ' red fisher ' ], mwoqoEQ);
      if o= nil  then 
         begin 
          showmessage( ' no query to data! ' );
          exit;
        end;
      showmessage(o.Comments);
     o.Free;

end;

Modify the database

procedure TForm2.Button8Click (Sender: TObject);
was
   o: Tcontact;

Begin 
     o: =orm.Query<Tcontact>([ ' name ' ],[ ' red fisher ' ], mwoqoEQ);
      if o= nil  then 
         begin 
          showmessage( ' no query to data! ' );
          exit;
        end;

    o.Name: = ' Red Fish II Generation ' ;
    orm.Update(o);

      Showmessage( ' Modified successfully! ' );
     o.Free;

end;

The result is also normal

See what happens in the background?

Is the database saved correctly?

No problem, it’s so cool.

By the way, add the deleted code.

procedure TForm2.Button9Click (Sender: TObject);
was
   o: Tcontact;

Begin 
     o: =orm.Query<Tcontact>([ ' name ' ],[ ' red fisher ' ], mwoqoEQ);
      if o= nil  then 
         begin 
          showmessage( ' no query to data! ' );
          exit;
        end;


    orm.Delete (o);

      Showmessage( ' delete successfully! ' );
     o.Free;

end;

Clear all data

procedure TForm2.Button4Click(Sender: TObject);
begin
      orm.PurgeTable (Tcontact);

end;

Delete built table

procedure TForm2.Button2Click(Sender: TObject);
begin
   ORM.DeleteTable([Tcontact])
end;

I finally finished it.

Everyone has a headache for the kbmmw label above. The first thing to remember is a lot of label names. The second one can’t be a mistake. This is really troublesome.

The good news is that the author has included the automatic production of these annotations in the plan, and expects that the later versions can be directly and automatically produced, which is much more convenient.

Before you have an automatic sound field, please refer to the instructions and handle it by yourself.

// ORM attribute syntax
// ====================
//
// kbmMW_Table - Define a table.
//   Must be used on classes.
//
//   Define a table named person.
//   [kbmMW_Table('name:person')]
//
//   Define 2 ascending indexes i_fieldname, and i_anotherfieldname on the field fieldname and anotherfieldname.
//   [kbmMW_Table('name:person, index:fieldname, index:anotherfieldname...
//
//   Define an ascending index named i1, on the field name
//   [kbmMW_Table('name:person, index:{name:i1,field:name},...
//
//   Define a descending index named i1, on the field name
//   [kbmMW_Table('name:person, index:{name:i1,field:name,descending:true},...
//
//   Define a compound unique index named i2, on the fields name and age. Name field part is descending.
//   [kbmMW_Table('name:person, index:{name:i2,unique:true,fields:[{name:name,descending:true},{name:age}]
//
//
// kbmMW_Field - Define fields in a table.
//   Must be used on properties within a class if they are to be persisted.
//
//   Define a field that will be persisted. Its type will be decided for
//     from the property type. String type fields will have a size of 50.
//     Table field name will be the same as the property name.
//   [kbmMW_Field]
//
//   Define a field that will be persisted. It will accept unicode data of max 50 characters.
//     It will have the same name as the property.
//   [kbmMW_Field(ftWideString,50)]
//
//   Define a field named id, and make it primary key. It will be automatically populated bu the generator shortGuid.
//   [kbmMW_Field('name:id, primary:true, generator:shortGuid',ftString,40)]
//   property ID:kbmMWNullable<string> read FID write FID;
//
//   These generators exists:
//     GUID           - Returns a GUID formatted as a regular GUID {123e4567-e89b-12d3-a456-426655440000}
//     SHORTGUID      - Returns a short GUID where braces and dashes are missing: 123e4567e89b12d3a456426655440000
//     SEQUENCE       - Returns next unique number from a sequence. Provide name of sequencer in sequence property
//                      and optional sequencestart property (not supported by all databases!)
//     DATETIME       - Returns a date time value, formatted according to the dateFormat property.
//
//   Define a field named id, and make it primary key. It will be populated by a sequence generator.
//     Since no sequencer was given, one is automatically generated named s_tablename_fieldname
//   [kbmMW_Field('name:id, primary:true, generator:sequence',ftInteger)]
//   property ID:kbmMWNullable<integer> read FID write FID;
//
//   Define a field named id, and make it primary key. It will be populated by sequence generator SEQ, starting from value 10.
//     (not all databases supports sequencers with a defined start!)
//   [kbmMW_Field('name:id, primary:true, generator:sequence, seqneuce:SEQ1, sequenceStart:10',ftInteger)]
//   property ID:kbmMWNullable<integer> read FID write FID;
//
//   Define a field named id, and make it primary key. It will be populated automatically by the database.
//     (not all databases support auto increment type fields!)
//   [kbmMW_Field('name:id, primary:true',ftAutoInc)]
//   property ID:kbmMWNullable<integer> read FID write FID;
//
//   Define a field named datetime containing date/time values as Delphi local time values.
//   [kbmMW_Field('name:datetime',ftDateTime)]
//   property DateTime:TkbmMWDateTime read FDateTime write FDateTime;
//
//   Define a field named datetime containing date/time values as Delphi UTC values.
//   [kbmMW_Field('name:datetime, dateFormat:UTC',ftDateTime)]
//   property DateTime:TkbmMWDateTime read FDateTime write FDateTime;
//
//   Define a field named datetime containing date/time values as Unix local time millisecs since EPOC.
//   [kbmMW_Field('name:datetime, dateFormat:LOCALSINCEEPOCHMS',ftInt64)]
//   property DateTime:TkbmMWDateTime read FDateTime write FDateTime;
//
//   Define a field named datetime containing date/time values as Unix UTC time millisecs since EPOC.
//   [kbmMW_Field('name:datetime, dateFormat:UTCSINCEEPOCHMS',ftInt64)]
//   property DateTime:TkbmMWDateTime read FDateTime write FDateTime;
//
//   Define a field named datetime containing date/time values as Unix local time secs since EPOC.
//   [kbmMW_Field('name:datetime, dateFormat:LOCALSINCEEPOCH',ftInt64)]
//   property DateTime:TkbmMWDateTime read FDateTime write FDateTime;
//
//   Define a field named datetime containing date/time values as Unix UTC time secs since EPOC.
//   [kbmMW_Field('name:datetime, dateFormat:UTCSINCEEPOCH',ftInt64)]
//   property DateTime:TkbmMWDateTime read FDateTime write FDateTime;
//
//   Define a field named datetime containing date/time values as ISO8601 formatted string.
//   [kbmMW_Field('name:datetime, dateFormat:ISO8601',ftString,50)]
//   property DateTime:TkbmMWDateTime read FDateTime write FDateTime;
//
//   Define a field named datetime containing date/time values as RFC1123 formatted string.
//   [kbmMW_Field('name:datetime, dateFormat:RFC1123',ftString,50)]
//   property DateTime:TkbmMWDateTime read FDateTime write FDateTime;
//
//   Define a field named datetime containing date/time values as NCSA formatted string.
//   [kbmMW_Field('name:datetime, dateFormat:NCSA',ftString,50)]
//   property DateTime:TkbmMWDateTime read FDateTime write FDateTime;
//
// kbmMW_Null - Specify NULL conversion.
//   (This attribute is also used for object marshalling).
//
//   If, for example, a property is of type integer, the property is not directly able to indicate a NULL state since
//   all values of an integer are considered non NULL values.
//   However its possible to define a specific value to be interpreted as NULL.
//   Eg.
//   [kbmMW_Field('name:somefield',ftInteger)]
//   [kbmMW_Null(-1)]
//   property MyProperty:integer read FMyProperty write FMyProperty;
//
//   This will define that the value -1 must be interpreted as NULL when storing and retrieving data
//   from the database.
//
// kbmMW_NotNull - Indicate that the property must never contain the NULL value (either interpreted via the kbmMW_Null attribute or actual).
//   Eg.
//   [kbmMW_Field('name:somefield',ftInteger)]
//   [kbmMW_NotNull]
//   property MyProperty:kbmMWNullable<integer> read FMyProperty write FMyProperty;