Delphi.Spring4D.Marshmallow

Открыл для себя потрясающий ORM framework Marshmallow, который является частью библиотеки Spring4D и работает с его коллекциями.

Вообще, надо сказать, что такие вещи, как ORM призваны сокращать время разработки и создавать код более высокой абстракции. Если, скажем, понадобится переехать на другой SQL сервер, то сделать это будет явно проще, заменив адаптеры баз данных, а не переписывая весь код.

Я основывал данную статью на официальном wiki проекта. И вот, что интересного получилось.

Создание сущностей в коде из базы через CodeGenerator, выглядит он так, после компиляции

Подключение идет по ODBC, я экспериментировал с MySQL, соответственно скачал MySQL Connector/ ODBC.

Сначала нужно создать ODBC в Windows, причем в Win 10 есть 32 битная версия и 64 битная версия. Сработала 32 битная версия, чтобы аккаунт появился в CodeGenerator.

Далее, в CodeGenerator можно идти 2 путями – выбирать поставщика данных, я выбрал

На след. вкладке выбираем источник данных и заполняем поля базы данных.

В результате у нас формируется строка соединения, у меня она получилась такой

Provider=MSDASQL.1;Password=masterkey;Persist Security Info=True;User ID=root;Data Source=MySQL ODBC;Initial Catalog=customers

1	Provider=MSDASQL.1;Password=masterkey;Persist Security Info=True;User ID=root;Data Source=MySQL ODBC;Initial Catalog=customers

Далее, жмем на Connect и наблюдаем таблицы БД, которые удалось вытащить из БД, также нужно указать каталог, куда будут выгружаться модели БД в виде pas файлов, вот что у нас получилось на выходе

unit customers;

interface

uses
  Spring.Persistence.Mapping.Attributes,
  Spring.Persistence.Core.Graphics,
  Spring;

type
  [Entity]
  [Table('customers', '')]
  TCustomer = class
  private
    Fid: Nullable<Int64>;
    Fname: Nullable<string>;
  public
    [AutoGenerated]
    [Column('id',[cpPrimaryKey,cpDontUpdate],10,0)]
    property id: Nullable<Int64> read Fid write Fid;
    [Column('name',[],45)]
    property name: Nullable<string> read Fname write Fname;
  end;

implementation


end.

unit customers;

interface

uses

Spring.Persistence.Mapping.Attributes,

Spring.Persistence.Core.Graphics,

Spring;

type

[Entity]

[Table('customers', '')]

TCustomer = class

private

Fid: Nullable<Int64>;

Fname: Nullable<string>;

public

[AutoGenerated]

[Column('id',[cpPrimaryKey,cpDontUpdate],10,0)]

property id: Nullable<Int64> read Fid write Fid;

[Column('name',[],45)]

property name: Nullable<string> read Fname write Fname;

end;

implementation

end.

unit testtable;

interface

uses
  Spring.Persistence.Mapping.Attributes,
  Spring.Persistence.Core.Graphics,
  Spring;

type
  [Entity]
  [Table('testtable', '')]
  Ttesttable = class
  private
    Fid: Nullable<Int64>;
    Fname: Nullable<string>;
  public
    [AutoGenerated]
    [Column('id',[cpPrimaryKey,cpDontUpdate],10,0)]
    property id: Nullable<Int64> read Fid write Fid;
    [Column('name',[],45)]
    property name: Nullable<string> read Fname write Fname;
  end;

implementation


end.

unit testtable;

interface

uses

Spring.Persistence.Mapping.Attributes,

Spring.Persistence.Core.Graphics,

Spring;

type

[Entity]

[Table('testtable', '')]

Ttesttable = class

private

Fid: Nullable<Int64>;

Fname: Nullable<string>;

public

[AutoGenerated]

[Column('id',[cpPrimaryKey,cpDontUpdate],10,0)]

property id: Nullable<Int64> read Fid write Fid;

[Column('name',[],45)]

property name: Nullable<string> read Fname write Fname;

end;

implementation

end.

Здесь надо сказать, что было пара лишних запятых, но я их убрал и код скомпилировался. Далее, посмотрим что умеет фрэймворк

Подключение к БД через FireDAC

procedure TMain.ConnectFD;
var
  oParams: TStrings;
begin
  oParams := TStringList.Create;
  try
    oParams.Add('DataBase=odbc_test');
    oParams.Add('Password=masterkey');
    oParams.Add('User_Name=root');
    oParams.Add('Port=3306');
    oParams.Add('Server=localhost');
    FDConnection.Params.Assign(oParams);
    FDConnection.DriverName := 'MySQL';
   // Connect и проверка!
    FDConnection.Connected := true;
    if FDConnection.Connected then
      Caption := 'Connected';
  finally
    oParams.Free();
  end;
end;

procedure TMain.ConnectFD;

var

oParams: TStrings;

begin

oParams := TStringList.Create;

try

oParams.Add('DataBase=odbc_test');

oParams.Add('Password=masterkey');

oParams.Add('User_Name=root');

oParams.Add('Port=3306');

oParams.Add('Server=localhost');

FDConnection.Params.Assign(oParams);

FDConnection.DriverName := 'MySQL';

// Connect и проверка!

FDConnection.Connected := true;

if FDConnection.Connected then

Caption := 'Connected';

finally

oParams.Free();

end;

Подключение фрэймворка к соединению происходит примерно так…

procedure TMain.FormCreate(Sender: TObject);
begin
  ReportMemoryLeaksOnShutdown := true;
  ConnectFD();
  FConnection := TConnectionFactory.GetInstance(dtFireDAC, FDConnection);
  FConnection.AutoFreeConnection := True;
  FConnection.QueryLanguage := qlMySql;
  FSession := TSession.Create(FConnection);
end;

procedure TMain.FormCreate(Sender: TObject);

begin

ReportMemoryLeaksOnShutdown := true;

ConnectFD();

FConnection := TConnectionFactory.GetInstance(dtFireDAC, FDConnection);

FConnection.AutoFreeConnection := True;

FConnection.QueryLanguage := qlMySql;

FSession := TSession.Create(FConnection);

end;

Далее, я набросал небольшой пример, выглядит он так…

Все эти методы реализованы на высоком уровне абстракции, посмотрим как это выглядит

Create через Save

var
  c: TCustomer;
begin
  try
    c := TCustomer.Create();
    c.name := 'newCustomer ' + Random(100).ToString();
    FSession.Save(c);
  finally
    c.Free();
  end;
end;

var

c: TCustomer;

begin

try

c := TCustomer.Create();

c.name := 'newCustomer ' + Random(100).ToString();

FSession.Save(c);

finally

c.Free();

end;

Если заглянуть в Save, то выглядит это так…

procedure TSession.Save(const entity: TObject);
begin
  if IsNew(entity) then
    Insert(entity)
  else
    Update(entity);
end;

procedure TSession.Save(const entity: TObject);

begin

if IsNew(entity) then

Insert(entity)

else

Update(entity);

end;

То есть фрэймворк пытается определить, есть ли такая запись в таблице.

Create через Insert

var
  c: TCustomer;
begin
  try
    c := TCustomer.Create();
    c.name := 'newCustomer ' + Random(100).ToString();
    FSession.Insert(c);
  finally
    c.Free();
  end;
end;

var

c: TCustomer;

begin

try

c := TCustomer.Create();

c.name := 'newCustomer ' + Random(100).ToString();

FSession.Insert(c);

finally

c.Free();

end;

Update

var
  c: TCustomer;
begin
  try
    c := TCustomer.Create();
    c.id := FSession.ExecuteScalar<Integer>('select max(id) from customers', []); // show primary key
    c.name := 'newCustomer ' + Random(100).ToString();
    FSession.Update(c);
  finally
    c.Free();
  end;
end;

var

c: TCustomer;

begin

try

c := TCustomer.Create();

c.id := FSession.ExecuteScalar<Integer>('select max(id) from customers', []); // show primary key

c.name := 'newCustomer ' + Random(100).ToString();

FSession.Update(c);

finally

c.Free();

end;

Delete

var
  c: TCustomer;
begin
  try
    c := TCustomer.Create();
    c.id := FSession.ExecuteScalar<Integer>('select max(id) from customers', []);
    FSession.Delete(c);
  finally
    c.Free();
  end;
end;

var

c: TCustomer;

begin

try

c := TCustomer.Create();

c.id := FSession.ExecuteScalar<Integer>('select max(id) from customers', []);

FSession.Delete(c);

finally

c.Free();

end;

Выполнение SQL запроса, возвращающего результат

var
  r: integer;
begin
  r := FSession.ExecuteScalar<Integer>('select last_insert_id()', []);
  Memo.Lines.Add(r.ToString());
end;

var

r: integer;

begin

r := FSession.ExecuteScalar<Integer>('select last_insert_id()', []);

Memo.Lines.Add(r.ToString());

end;

SingleOrDefault

var
  c: TCustomer;
begin
  try
    c := FSession.SingleOrDefault<TCustomer>('Select*from customers where id=:0', [2]);
    memo.Lines.Add('SingleOrDefault ' + c.name);
  finally
    c.Free();
  end;
end;

var

c: TCustomer;

begin

try

c := FSession.SingleOrDefault<TCustomer>('Select*from customers where id=:0', [2]);

memo.Lines.Add('SingleOrDefault ' + c.name);

finally

c.Free();

end;

FindOne

var
  c: TCustomer;
begin
  try
    c := FSession.FindOne<TCustomer>(2);
    memo.Lines.Add('FindOne ' + c.name);
  finally
    c.Free();
  end;
end;

var

c: TCustomer;

begin

try

c := FSession.FindOne<TCustomer>(2);

memo.Lines.Add('FindOne ' + c.name);

finally

c.Free();

end;

ListOfCustomers

var
  cs: IList<TCustomer>;
  c: TCustomer;
begin
  cs := FSession.GetList<TCustomer>('Select*from customers where id > 2', []);
  Memo.Lines.Add('GetList');
  for c in cs do
    Memo.Lines.Add(c.name);
end;

var

cs: IList<TCustomer>;

c: TCustomer;

begin

cs := FSession.GetList<TCustomer>('Select*from customers where id > 2', []);

Memo.Lines.Add('GetList');

for c in cs do

Memo.Lines.Add(c.name);

end;

Nullable

var
  c: TCustomer;
begin
  c := FSession.FindOne<TCustomer>(2);
  try
    if c.name.HasValue then
      Memo.Lines.Add(c.name);
  finally
    c.Free();
  end;
end;

var

c: TCustomer;

begin

c := FSession.FindOne<TCustomer>(2);

try

if c.name.HasValue then

Memo.Lines.Add(c.name);

finally

c.Free();

end;

FindAll

var
  cs: IEnumerable<TCustomer>;
  c: TCustomer;
begin
  cs := FSession.FindAll<TCustomer>().Where(
    function(const c: TCustomer): boolean
    begin
      Result := (c.id.HasValue) and (c.id.Value > 2);
    end);

  Memo.Lines.Add('FindAll');
  for c in cs do
    Memo.Lines.Add(c.name);
end;

var

cs: IEnumerable<TCustomer>;

c: TCustomer;

begin

cs := FSession.FindAll<TCustomer>().Where(

function(const c: TCustomer): boolean

begin

Result := (c.id.HasValue) and (c.id.Value > 2);

end);

Memo.Lines.Add('FindAll');

for c in cs do

Memo.Lines.Add(c.name);

end;

Транзакции

var
  t: IDBTransaction;
  c: TCustomer;
begin
  t := FSession.BeginTransaction;
  try
    try
      c := TCustomer.Create();
      c.name := 'newCustomer ' + Random(100).ToString();
      FSession.Save(c);
      t.Commit();
    finally
      c.Free();
    end;
  except
    on E: Exception do
      t.Rollback();
  end;
end;

var

t: IDBTransaction;

c: TCustomer;

begin

t := FSession.BeginTransaction;

try

c := TCustomer.Create();

c.name := 'newCustomer ' + Random(100).ToString();

FSession.Save(c);

t.Commit();

finally

c.Free();

end;

except

on E: Exception do

t.Rollback();

end;

Создание критериев запроса

var
  cr: ICriteria<TCustomer>;
  cs: IList<TCustomer>;
  name: IProperty;
  id: IProperty;
  c: TCustomer;
begin
  cr := FSession.CreateCriteria<TCustomer>;
  name := TProperty.Create('name');
  id := TProperty.Create('id');
  cs := cr.Add(name.Eq('newCustomer 21')).Add(id.Eq('2')).ToList();
  for c in cs do
    Memo.Lines.Add(c.name);
end;

var

cr: ICriteria<TCustomer>;

cs: IList<TCustomer>;

name: IProperty;

id: IProperty;

c: TCustomer;

begin

cr := FSession.CreateCriteria<TCustomer>;

name := TProperty.Create('name');

id := TProperty.Create('id');

cs := cr.Add(name.Eq('newCustomer 21')).Add(id.Eq('2')).ToList();

for c in cs do

Memo.Lines.Add(c.name);

end;

Полный код модуля

unit uMain;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics, Vcl.Controls, Vcl.Forms,
  Vcl.Dialogs, Data.DB, FireDAC.Comp.Client, FireDAC.Stan.Error, Spring, Spring.Mocking, Spring.Persistence.Adapters.FireDAC,
  Spring.Persistence.Core.Base, Spring.Persistence.Core.Exceptions, Spring.Persistence.Core.Interfaces, Spring.Persistence.Core.Session,
  Spring.Persistence.Core.ResourceStrings, Spring.Persistence.Mapping.Attributes, Spring.Persistence.SQL.Generators.Ansi,
  Spring.Persistence.SQL.Params, FireDAC.Stan.Intf, FireDAC.Stan.Option, FireDAC.UI.Intf, FireDAC.Phys.Intf, FireDAC.Stan.Def,
  FireDAC.Stan.Pool, FireDAC.Stan.Async, FireDAC.Phys, FireDAC.VCLUI.Wait, FireDAC.Phys.MySQLDef, FireDAC.Phys.MySQL,
  Spring.Persistence.Core.ConnectionFactory, Spring.Collections, Vcl.StdCtrls, Vcl.Buttons, Spring.Persistence.Criteria.Interfaces,
  Spring.Persistence.Criteria.Properties;

type
  TMain = class(TForm)
    FDConnection: TFDConnection;
    FDPhysMySQLDriverLink: TFDPhysMySQLDriverLink;
    bOk: TBitBtn;
    Memo: TMemo;
    bCreateCustomer: TBitBtn;
    bInsert: TBitBtn;
    bUpdate: TBitBtn;
    bDelete: TBitBtn;
    bLastInsertId: TBitBtn;
    bSingleOrDefault: TBitBtn;
    bFindOne: TBitBtn;
    bListOfCustomers: TBitBtn;
    FindAll: TBitBtn;
    bTransaction: TBitBtn;
    bNullable: TBitBtn;
    bCriteria: TBitBtn;
    procedure FormCreate(Sender: TObject);
    procedure bOkClick(Sender: TObject);
    procedure bCreateCustomerClick(Sender: TObject);
    procedure bInsertClick(Sender: TObject);
    procedure bUpdateClick(Sender: TObject);
    procedure bDeleteClick(Sender: TObject);
    procedure bLastInsertIdClick(Sender: TObject);
    procedure bSingleOrDefaultClick(Sender: TObject);
    procedure bFindOneClick(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure bListOfCustomersClick(Sender: TObject);
    procedure FindAllClick(Sender: TObject);
    procedure bTransactionClick(Sender: TObject);
    procedure bNullableClick(Sender: TObject);
    procedure bCriteriaClick(Sender: TObject);
  private
    { Private declarations }
    FConnection: IDBConnection;
    FSession: TSession;
    procedure ConnectFD();
  public
    { Public declarations }
  end;

var
  Main: TMain;

implementation

uses
  Spring.Persistence.SQL.Interfaces, testTable, customers;

{$R *.dfm}

procedure TMain.bCreateCustomerClick(Sender: TObject);
var
  c: TCustomer;
begin
  try
    c := TCustomer.Create();
    c.name := 'newCustomer ' + Random(100).ToString();
    FSession.Save(c);
  finally
    c.Free();
  end;
end;

procedure TMain.bCriteriaClick(Sender: TObject);
var
  cr: ICriteria<TCustomer>;
  cs: IList<TCustomer>;
  name: IProperty;
  id: IProperty;
  c: TCustomer;
begin
  cr := FSession.CreateCriteria<TCustomer>;
  name := TProperty.Create('name');
  id := TProperty.Create('id');
  cs := cr.Add(name.Eq('newCustomer 21')).Add(id.Eq('2')).ToList();
  for c in cs do
    Memo.Lines.Add(c.name);
end;

procedure TMain.bDeleteClick(Sender: TObject);
var
  c: TCustomer;
begin
  try
    c := TCustomer.Create();
    c.id := FSession.ExecuteScalar<Integer>('select max(id) from customers', []);
    FSession.Delete(c);
  finally
    c.Free();
  end;
end;

procedure TMain.bFindOneClick(Sender: TObject);
var
  c: TCustomer;
begin
  try
    c := FSession.FindOne<TCustomer>(2);
    memo.Lines.Add('FindOne ' + c.name);
  finally
    c.Free();
  end;
end;

procedure TMain.bInsertClick(Sender: TObject);
var
  c: TCustomer;
begin
  try
    c := TCustomer.Create();
    c.name := 'newCustomer ' + Random(100).ToString();
    FSession.Insert(c);
  finally
    c.Free();
  end;
end;

procedure TMain.bLastInsertIdClick(Sender: TObject);
var
  r: integer;
begin
  r := FSession.ExecuteScalar<Integer>('select last_insert_id()', []);
  Memo.Lines.Add(r.ToString());
end;

procedure TMain.bListOfCustomersClick(Sender: TObject);
var
  cs: IList<TCustomer>;
  c: TCustomer;
begin
  cs := FSession.GetList<TCustomer>('Select*from customers where id > 2', []);
  Memo.Lines.Add('GetList');
  for c in cs do
    Memo.Lines.Add(c.name);
end;

procedure TMain.bNullableClick(Sender: TObject);
var
  c: TCustomer;
begin
  c := FSession.FindOne<TCustomer>(2);
  try
    if c.name.HasValue then
      Memo.Lines.Add(c.name);
  finally
    c.Free();
  end;
end;

procedure TMain.bOkClick(Sender: TObject);
var
  testTableObject: Ttesttable;
begin
  testTableObject := FSession.FindOne<Ttesttable>(1);
  if testTableObject <> nil then
    Memo.Lines.Add(testTableObject.name);
end;

procedure TMain.bSingleOrDefaultClick(Sender: TObject);
var
  c: TCustomer;
begin
  try
    c := FSession.SingleOrDefault<TCustomer>('Select*from customers where id=:0', [2]);
    memo.Lines.Add('SingleOrDefault ' + c.name);
  finally
    c.Free();
  end;
end;

procedure TMain.bTransactionClick(Sender: TObject);
var
  t: IDBTransaction;
  c: TCustomer;
begin
  t := FSession.BeginTransaction;
  try
    try
      c := TCustomer.Create();
      c.name := 'newCustomer ' + Random(100).ToString();
      FSession.Save(c);
      t.Commit();
    finally
      c.Free();
    end;
  except
    on E: Exception do
      t.Rollback();
  end;
end;

procedure TMain.bUpdateClick(Sender: TObject);
var
  c: TCustomer;
begin
  try
    c := TCustomer.Create();
    c.id := FSession.ExecuteScalar<Integer>('select max(id) from customers', []); // show primary key
    c.name := 'newCustomer ' + Random(100).ToString();
    FSession.Update(c);
  finally
    c.Free();
  end;
end;

procedure TMain.ConnectFD;
var
  oParams: TStrings;
begin
  oParams := TStringList.Create;
  try
    oParams.Add('DataBase=odbc_test');
    oParams.Add('Password=masterkey');
    oParams.Add('User_Name=root');
    oParams.Add('Port=3306');
    oParams.Add('Server=localhost');
    FDConnection.Params.Assign(oParams);
    FDConnection.DriverName := 'MySQL';
   // Connect и проверка!
    FDConnection.Connected := true;
    if FDConnection.Connected then
      Caption := 'Connected';
  finally
    oParams.Free();
  end;
end;

procedure TMain.FindAllClick(Sender: TObject);
var
  cs: IEnumerable<TCustomer>;
  c: TCustomer;
begin
  cs := FSession.FindAll<TCustomer>().Where(
    function(const c: TCustomer): boolean
    begin
      Result := (c.id.HasValue) and (c.id.Value > 2);
    end);

  Memo.Lines.Add('FindAll');
  for c in cs do
    Memo.Lines.Add(c.name);
end;

procedure TMain.FormCreate(Sender: TObject);
begin
  ReportMemoryLeaksOnShutdown := true;
  ConnectFD();
  FConnection := TConnectionFactory.GetInstance(dtFireDAC, FDConnection);
  FConnection.AutoFreeConnection := True;
  FConnection.QueryLanguage := qlMySql;
  FSession := TSession.Create(FConnection);
end;

procedure TMain.FormDestroy(Sender: TObject);
begin
  FSession.Free();
end;

end.

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

unit uMain;

interface

uses

Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics, Vcl.Controls, Vcl.Forms,

Vcl.Dialogs, Data.DB, FireDAC.Comp.Client, FireDAC.Stan.Error, Spring, Spring.Mocking, Spring.Persistence.Adapters.FireDAC,

Spring.Persistence.Core.Base, Spring.Persistence.Core.Exceptions, Spring.Persistence.Core.Interfaces, Spring.Persistence.Core.Session,

Spring.Persistence.Core.ResourceStrings, Spring.Persistence.Mapping.Attributes, Spring.Persistence.SQL.Generators.Ansi,

Spring.Persistence.SQL.Params, FireDAC.Stan.Intf, FireDAC.Stan.Option, FireDAC.UI.Intf, FireDAC.Phys.Intf, FireDAC.Stan.Def,

FireDAC.Stan.Pool, FireDAC.Stan.Async, FireDAC.Phys, FireDAC.VCLUI.Wait, FireDAC.Phys.MySQLDef, FireDAC.Phys.MySQL,

Spring.Persistence.Core.ConnectionFactory, Spring.Collections, Vcl.StdCtrls, Vcl.Buttons, Spring.Persistence.Criteria.Interfaces,

Spring.Persistence.Criteria.Properties;

type

TMain = class(TForm)

FDConnection: TFDConnection;

FDPhysMySQLDriverLink: TFDPhysMySQLDriverLink;

bOk: TBitBtn;

Memo: TMemo;

bCreateCustomer: TBitBtn;

bInsert: TBitBtn;

bUpdate: TBitBtn;

bDelete: TBitBtn;

bLastInsertId: TBitBtn;

bSingleOrDefault: TBitBtn;

bFindOne: TBitBtn;

bListOfCustomers: TBitBtn;

FindAll: TBitBtn;

bTransaction: TBitBtn;

bNullable: TBitBtn;

bCriteria: TBitBtn;

procedure FormCreate(Sender: TObject);

procedure bOkClick(Sender: TObject);

procedure bCreateCustomerClick(Sender: TObject);

procedure bInsertClick(Sender: TObject);

procedure bUpdateClick(Sender: TObject);

procedure bDeleteClick(Sender: TObject);

procedure bLastInsertIdClick(Sender: TObject);

procedure bSingleOrDefaultClick(Sender: TObject);

procedure bFindOneClick(Sender: TObject);

procedure FormDestroy(Sender: TObject);

procedure bListOfCustomersClick(Sender: TObject);

procedure FindAllClick(Sender: TObject);

procedure bTransactionClick(Sender: TObject);

procedure bNullableClick(Sender: TObject);

procedure bCriteriaClick(Sender: TObject);

private

{ Private declarations }

FConnection: IDBConnection;

FSession: TSession;

procedure ConnectFD();

public

{ Public declarations }

end;

var

Main: TMain;

implementation

uses

Spring.Persistence.SQL.Interfaces, testTable, customers;

{$R *.dfm}

procedure TMain.bCreateCustomerClick(Sender: TObject);

var

c: TCustomer;

begin

try

c := TCustomer.Create();

c.name := 'newCustomer ' + Random(100).ToString();

FSession.Save(c);

finally

c.Free();

end;

procedure TMain.bCriteriaClick(Sender: TObject);

var

cr: ICriteria<TCustomer>;

cs: IList<TCustomer>;

name: IProperty;

id: IProperty;

c: TCustomer;

begin

cr := FSession.CreateCriteria<TCustomer>;

name := TProperty.Create('name');

id := TProperty.Create('id');

cs := cr.Add(name.Eq('newCustomer 21')).Add(id.Eq('2')).ToList();

for c in cs do

Memo.Lines.Add(c.name);

end;

procedure TMain.bDeleteClick(Sender: TObject);

var

c: TCustomer;

begin

try

c := TCustomer.Create();

c.id := FSession.ExecuteScalar<Integer>('select max(id) from customers', []);

FSession.Delete(c);

finally

c.Free();

end;

procedure TMain.bFindOneClick(Sender: TObject);

var

c: TCustomer;

begin

try

c := FSession.FindOne<TCustomer>(2);

memo.Lines.Add('FindOne ' + c.name);

finally

c.Free();

end;

procedure TMain.bInsertClick(Sender: TObject);

var

c: TCustomer;

begin

try

c := TCustomer.Create();

c.name := 'newCustomer ' + Random(100).ToString();

FSession.Insert(c);

finally

c.Free();

end;

procedure TMain.bLastInsertIdClick(Sender: TObject);

var

r: integer;

begin

r := FSession.ExecuteScalar<Integer>('select last_insert_id()', []);

Memo.Lines.Add(r.ToString());

end;

procedure TMain.bListOfCustomersClick(Sender: TObject);

var

cs: IList<TCustomer>;

c: TCustomer;

begin

cs := FSession.GetList<TCustomer>('Select*from customers where id > 2', []);

Memo.Lines.Add('GetList');

for c in cs do

Memo.Lines.Add(c.name);

end;

procedure TMain.bNullableClick(Sender: TObject);

var

c: TCustomer;

begin

c := FSession.FindOne<TCustomer>(2);

try

if c.name.HasValue then

Memo.Lines.Add(c.name);

finally

c.Free();

end;

procedure TMain.bOkClick(Sender: TObject);

var

testTableObject: Ttesttable;

begin

testTableObject := FSession.FindOne<Ttesttable>(1);

if testTableObject <> nil then

Memo.Lines.Add(testTableObject.name);

end;

procedure TMain.bSingleOrDefaultClick(Sender: TObject);

var

c: TCustomer;

begin

try

c := FSession.SingleOrDefault<TCustomer>('Select*from customers where id=:0', [2]);

memo.Lines.Add('SingleOrDefault ' + c.name);

finally

c.Free();

end;

procedure TMain.bTransactionClick(Sender: TObject);

var

t: IDBTransaction;

c: TCustomer;

begin

t := FSession.BeginTransaction;

try

c := TCustomer.Create();

c.name := 'newCustomer ' + Random(100).ToString();

FSession.Save(c);

t.Commit();

finally

c.Free();

end;

except

on E: Exception do

t.Rollback();

end;

procedure TMain.bUpdateClick(Sender: TObject);

var

c: TCustomer;

begin

try

c := TCustomer.Create();

c.id := FSession.ExecuteScalar<Integer>('select max(id) from customers', []); // show primary key

c.name := 'newCustomer ' + Random(100).ToString();

FSession.Update(c);

finally

c.Free();

end;

procedure TMain.ConnectFD;

var

oParams: TStrings;

begin

oParams := TStringList.Create;

try

oParams.Add('DataBase=odbc_test');

oParams.Add('Password=masterkey');

oParams.Add('User_Name=root');

oParams.Add('Port=3306');

oParams.Add('Server=localhost');

FDConnection.Params.Assign(oParams);

FDConnection.DriverName := 'MySQL';

// Connect и проверка!

FDConnection.Connected := true;

if FDConnection.Connected then

Caption := 'Connected';

finally

oParams.Free();

end;

procedure TMain.FindAllClick(Sender: TObject);

var

cs: IEnumerable<TCustomer>;

c: TCustomer;

begin

cs := FSession.FindAll<TCustomer>().Where(

function(const c: TCustomer): boolean

begin

Result := (c.id.HasValue) and (c.id.Value > 2);

end);

Memo.Lines.Add('FindAll');

for c in cs do

Memo.Lines.Add(c.name);

end;

procedure TMain.FormCreate(Sender: TObject);

begin

ReportMemoryLeaksOnShutdown := true;

ConnectFD();

FConnection := TConnectionFactory.GetInstance(dtFireDAC, FDConnection);

FConnection.AutoFreeConnection := True;

FConnection.QueryLanguage := qlMySql;

FSession := TSession.Create(FConnection);

end;

procedure TMain.FormDestroy(Sender: TObject);

begin

FSession.Free();

end;

end.

Delphi.Spring4D.Marshmallow

MY PROJECTS

Recent Posts

Categories