📄 tiofp documentation - the visitor pattern and sql databases.htm
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<!-- saved from url=(0070)http://www.techinsite.com.au/tiOPF/Doc/3_TheVisitorAndSQLDatabases.htm -->
<HTML><HEAD><TITLE>tiOFP Documentation - The Visitor pattern and SQL databases</TITLE>
<META http-equiv=Content-Type content="text/html; charset=gb2312"><!-- InstanceBegin template="/Templates/TechInsite_Template.dwt" codeOutsideHTMLIsLocked="false" --><!-- InstanceBeginEditable name="doctitle" --><!-- InstanceEndEditable --><!-- InstanceBeginEditable name="head" --><!-- InstanceEndEditable --><LINK
href="tiOFP Documentation - The Visitor pattern and SQL databases_files/TechInsite.css"
type=text/css rel=stylesheet>
<META content="MSHTML 6.00.3790.1830" name=GENERATOR></HEAD>
<BODY>
<SCRIPT type=text/javascript>function Go(){return}</SCRIPT>
<SCRIPT
src="tiOFP Documentation - The Visitor pattern and SQL databases_files/MainMenu_TechInsite.js"
type=text/javascript></SCRIPT>
<SCRIPT
src="tiOFP Documentation - The Visitor pattern and SQL databases_files/HVMenu.js"
type=text/javascript></SCRIPT>
<NOSCRIPT>Your browser does not support script</NOSCRIPT>
<DIV id=Container><IMG height=75 alt=""
src="tiOFP Documentation - The Visitor pattern and SQL databases_files/Banner01.jpg"
width=600 border=0>
<TABLE cellPadding=5 valign="middle">
<TBODY>
<TR>
<TD>
<DIV id=pageheader1>TechInsite</DIV></TD>
<TD>
<DIV id=pageheader2>Home of the TechInsite Object Persistence
Framework<BR>Melbourne, Australia</DIV></TD></TR></TBODY></TABLE>
<HR>
<TABLE>
<TBODY>
<TR>
<TD>
<DIV id=HMenu style="POSITION: relative"></DIV></TD></TR></TBODY></TABLE><BR>
<HR>
<!-- InstanceBeginEditable name="Page title" -->
<H1>3. The Visitor Pattern and SQL Databases </H1><!-- InstanceEndEditable --><!-- InstanceBeginEditable name="Page body" -->
<H1>The aims of this chapter</H1>
<P>In the previous chapter we investigated how the Visitor pattern can be used
to perform a family of related tasks on some elements in a list in a generic
way. We used the framework to create a small application that saves names and
email addresses to either a comma separated value (CSV) file or fixed length
text (TXT) file.</P>
<P>This is useful, but most business applications use a relational database to
save their data, so in this chapter we will extend the framework to store our
objects in an Interbase database.</P>
<H2>Prerequisites</H2>
<P>It would be best to have read ‘Chapter #2: Implement the Visitor Framework’
first because the concepts introduced in this chapter are building on the ideas
discussed in Chapter #2</P>
<H2>The business problem we will work with as an example</H2>
<P>We will continue with out simplified version of the contact management system
introduced in the previous chapter. We shall save a simple list of TPeople
objects, with two properties Name and EMailAdrs. The class diagram of our
business objects looks like this:</P>
<P><IMG height=89
src="tiOFP Documentation - The Visitor pattern and SQL databases_files/3_TheVisitorAndSQLDatabases_clip_image001.gif"
width=275> </P>
<H2>Implementation</H2>
<H2>Creating the database</H2>
<P>Before we can begin, we will need a database to work with. The following SQL
script will create an Interbase database with a single table called People. The
table shall have two columns Name and EMailAdrs. This script is called
SQLVisitor_Intebase_DDL.sql and can be found in the directory with the source
code. (Details for downloading the source code are at the end of the chapter.)
Note the Create Database and Connect commands have hard coded database name,
user name and password. If you have unzipped the source into another directory,
or have changed Interbase’s administrator password, you will have to edit the
script.</P><PRE>Create Database "C:\TechInsite\OPFPresentation\Source\3_SQLVisitors\Test.gdb"
user "SYSDBA" password "masterkey"
connect "C:\TechInsite\OPFPresentation\Source\3_SQLVisitors\Test.gdb"
user "SYSDBA" password "masterkey" ;
drop table People ;
create table People
( OID Integer not null,
Name VarChar( 20 ),
EMailAdrs VarChar( 60 ),
Primary Key ( OID ))
create unique index People_uk on People ( name ) ;
insert into People values ( 1, "Peter Hinrichsen", "peter_hinrichsen@techinsite.com.au");
insert into People values ( 2, "Don Macrae", "don@xpro.com.au" ) ;
insert into People values ( 3, "Malcolm Groves", "malcolm@madrigal.com.au" ) ;
commit ;</PRE>
<H2>Creating a database connection</H2>
<P>We are going to perform this demonstration using the Interbase Express (IBX)
components that come with Delphi. Now, I have been using Interbase for a while
now, but have abstracted the database connection code deep into the hierarchy so
very seldom have to create a TIBDatabase or TIBQuery manually in code. There are
some tricks in wiring these components up with a TIBTransaction that I forget so
we will use Delphi’s IDE to help walk us through the process. We will create a
form and add the components in Delphi’s form editor then copy them to the
clipboard and paste the code that Delphi generates into a PAS file.</P>
<P>I have dropped a TIBDatabase, TIBQuery, TIBTransaction (that’s the one I
forget), TDataSource and TDBGrid on a form, wired them up and set the active
property to true. We can connect to the database as expected so I will copy the
data access controls to the clipboard and paste them into the pas file where we
will be creating our SQL visitor. The form to test the data access components is
shown below:</P>
<P><IMG height=151
src="tiOFP Documentation - The Visitor pattern and SQL databases_files/3_TheVisitorAndSQLDatabases_clip_image002.jpg"
width=495> </P>
<P>If we copy the components at design time onto the clipboard, and paste them
into the pas file, we get some text that looks like this:<BR></P><PRE>object IBDatabase1: TIBDatabase
Connected = True
DatabaseName = 'C:\TechInsite\OPFPresentation\Source\3_SQLVisitors\Test.gdb'
Params.Strings = (
'user_name=SYSDBA'
'password=masterkey')
LoginPrompt = False
DefaultTransaction = IBTransaction1
IdleTimer = 0
SQLDialect = 1
TraceFlags = []
Left = 8
Top = 16
end
object IBTransaction1: TIBTransaction
Active = True
DefaultDatabase = IBDatabase1
Left = 8
Top = 48
end
object IBQuery1: TIBQuery
Database = IBDatabase1
Transaction = IBTransaction1
Active = True
CachedUpdates = False
SQL.Strings = (
'select * from people')
Left = 8
Top = 80
end
</PRE>
<P>This is actually the way Delphi stores the components in the form’s DFM file.
This technique of combining Delphi’s IDE with coding components by hand was
shown to me by Mark Miller in a presentation he made at BorCon 1999 – a very
useful technique.</P>
<P>It only takes a couple of minutes work to edit this code from the DFM format
into Pascal source. The finished result looks like this:<BR></P><PRE>var
FDB : TIBDatabase ;
FTransaction : TIBTransaction ;
FQuery : TIBQuery ;
lData : TPerson ;
begin
FDB := TIBDatabase.Create( nil ) ;
FDB.DatabaseName := 'C:\TechInsite\OPFPresentation\Source\3_SQLVisitors\Test.gdb' ;
FDB.Params.Add( 'user_name=SYSDBA' ) ;
FDB.Params.Add( 'password=masterkey' ) ;
FDB.LoginPrompt := False ;
FDB.Connected := True ;
FTransaction := TIBTransaction.Create( nil ) ;
FTransaction.DefaultDatabase := FDB ;
FDB.DefaultTransaction := FTransaction ;
FQuery := TIBQuery.Create( nil ) ;
FQuery.Database := FDB ;
FQuery.SQL.Text := 'select * from people' ;
FQuery.Active := True ;
end;</PRE>
<H2>Our first SQL database visitor</H2>
<P>We can wrap this code up as a TVisitor class by implementing the database
access in the Visitors Execute method. The Interface of our SQL read Visitor
looks like this:<BR><PRE>TVisSQLRead = class( TVisitor )
protected
function AcceptVisitor( pVisited : TVisited ) : boolean ; override ;
public
procedure Execute( pVisited : TVisited ) ; override ;
end ;</PRE>
<P>We can add some code to scan the query result set, and convert each row to an
object and we end up with a big, fat execute method like this:<BR></P><PRE>procedure TVisSQLRead.Execute(pVisited: TVisited);
var
FDB : TIBDatabase ;
FTransaction : TIBTransaction ;
FQuery : TIBQuery ;
lData : TPerson ;
begin
if not AcceptVisitor( pVisited ) then
Exit ; //==>
FDB := TIBDatabase.Create( nil ) ;
FDB.DatabaseName := 'C:\TechInsite\OPFPresentation\Source\3_SQLVisitors\Test.gdb' ;
FDB.Params.Add( 'user_name=SYSDBA' ) ;
FDB.Params.Add( 'password=masterkey' ) ;
FDB.LoginPrompt := False ;
FDB.Connected := True ;
FTransaction := TIBTransaction.Create( nil ) ;
FTransaction.DefaultDatabase := FDB ;
FDB.DefaultTransaction := FTransaction ;
FQuery := TIBQuery.Create( nil ) ;
FQuery.Database := FDB ;
FQuery.SQL.Text := 'select * from people' ;
FQuery.Active := True ;
while not FQuery.EOF do
begin
lData := TPerson.Create ;
lData.Name := FQuery.FieldByName( 'Name' ).AsString ;
lData.EMailAdrs := FQuery.FieldByName( 'EMailAdrs' ).AsString ;
TPeople( pVisited ).Add( lData ) ;
FQuery.Next ;
end ;
FQuery.Free ;
FTransaction.Free ;
FDB.Free ;
end;</PRE>
<P>We can register this Visitor with the Visitor Manager and test it with our
experimental application. The result is shown below: </P>
<P><IMG height=183
src="tiOFP Documentation - The Visitor pattern and SQL databases_files/3_TheVisitorAndSQLDatabases_clip_image001_0000.gif"
width=340> </P>
<P>Now that we have got the SQL read Visitor working, we can refactor the code
to thin it down, and improve reuse and maintainability.</P>
<H2>Move the database connection to an abstract Visitor</H2>
<P>The code that creates and frees the TIBDatabase, TIBTransaction and TIBQuery
is crying out to be moved to an abstract class. While we are about it, we shall
create another pas file to store our abstract SQL visitors and call it
tiPtnVisSQL. The interface of our new abstract Visitor called TVisQryAbs looks
like this:</P><PRE>TVisSQLAbs = class( TVisitor )
protected
FDB : TIBDatabase ;
FTransaction : TIBTransaction ;
FQuery : TIBQuery ;
// Implement AcceptVisitor in the concrete class
// function AcceptVisitor( pVisited : TVisited ) : boolean ; override ;
public
constructor Create ; override ;
destructor Destroy ; override ;
// Implement Execute in the concrete class
// procedure Execute( pVisited : TVisited ) ; override ;
end ; </PRE>
<P>and the constructor and destructors simply contain the code to create and
destroy the TIBDatabase, TIBTransaction and TIBQuery as in the previous example.
</P>
<P>This means our concrete visitor (the one that maps the SQL result set to
objects) has an Execute method that looks like this: </P><PRE>procedure TVisSQLRead.Execute(pVisited: TVisited);
var
lData : TPerson ;
begin
if not AcceptVisitor( pVisited ) then
Exit ; //==>
FQuery.SQL.Text := 'select * from people' ;
FQuery.Active := True ;
while not FQuery.EOF do
begin
lData := TPerson.Create ;
lData.Name := FQuery.FieldByName( 'Name' ).AsString ;
lData.EMailAdrs := FQuery.FieldByName( 'EMailAdrs' ).AsString ;
TPeople( pVisited ).Add( lData ) ;
FQuery.Next ;
end ;
end;</PRE>
<H2>Implementing the Template Method pattern</H2>
<P>This is starting to look more elegant, however if we have several of these
visitors to read different classes from the database, we will be still
duplicating lots of code. In the Execute method above, if we where to be
reading, say Addresses instead of People, the lines in red and bold would be
different, and the lines in blue and italics would be duplicated. </P><PRE>procedure TVisSQLRead.Execute(pVisited: TVisited);
var
lData : TPerson ; // Different
begin
if not AcceptVisitor( pVisited ) then // Duplicated
Exit ; // Duplicated
FQuery.SQL.Text := 'select * from people' ; // Different
FQuery.Active := True ; // Duplicated
while not FQuery.EOF do // Duplicated
begin // Duplicated
lData := TPerson.Create ; // Different
lData.Name := FQuery.FieldByName( 'Name' ).AsString ; // Different
lData.EMailAdrs := FQuery.FieldByName( 'EMailAdrs' ).AsString ; // Different
TPeople( pVisited ).Add( lData ) ; // Different
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -