20. Aplikacja internetowa MVC w architekturze trójwarstwowej – przykład 6, SQL Server Express

20.1. Baza danych SQL Server Express

W tej wersji zainstalujemy listę osób w tabeli bazy danych serwera SQL Server Express 2005, dostępnej pod adresem URL [http://msdn.microsoft.com/vstudio/express/sql/]. Poniższe zrzuty ekranu pochodzą z klienta EMS Manager Lite dla serwera SQL Server Express [http://www.sqlmanager.net/fr/products/mssql/manager], bezpłatnego klienta administracyjnego serwera SGBD SQL Server Express.

Baza danych nosi nazwę [dbpersonnes]. Zawiera ona tabelę [PERSONNES]:

Tabela [PERSONNES] będzie zawierała listę osób zarządzanych przez aplikację internetową. Została ona utworzona za pomocą następujących poleceń SQL:

CREATE TABLE [dbo].[PERSONNES] (
  [ID] int IDENTITY(1, 1) NOT NULL,
  [VERSION] int NOT NULL,
  [NOM] varchar(30) COLLATE French_CI_AS NOT NULL,
  [PRENOM] varchar(30) COLLATE French_CI_AS NOT NULL,
  [DATENAISSANCE] datetime NOT NULL,
  [MARIE] tinyint NOT NULL,
  [NBENFANTS] tinyint NOT NULL,
  PRIMARY KEY CLUSTERED ([ID]),
  CONSTRAINT [PERSONNES_ck_NOM] CHECK ([NOM]<>''),
  CONSTRAINT [PERSONNES_ck_PRENOM] CHECK ([PRENOM]<>''),
  CONSTRAINT [PERSONNES_ck_NBENFANTS] CHECK ([NBENFANTS]>=(0))
)
ON [PRIMARY]
GO

wiersz 2: klucz główny [ID] jest typu całkowitego. Atrybut IDENTITY wskazuje, że w przypadku wstawienia wiersza bez wartości w kolumnie ID tabeli, SQL Express samodzielnie wygeneruje liczbę całkowitą dla tej kolumny. W przypadku IDENTITY(1, 1) pierwszy parametr to pierwsza możliwa wartość klucza głównego, a drugi to przyrost stosowany podczas generowania liczb.

Tabela [PERSONNES] mogłaby mieć następującą zawartość:

Wiemy, że podczas wstawiania obiektu [Personne] przez naszą warstwę [dao], pole [id] tego obiektu przed wstawieniem ma wartość -1, a po wstawieniu przyjmuje wartość inną niż -1; wartość ta jest kluczem głównym przypisanym do nowego wiersza wstawionego do tabeli [PERSONNES]. Na przykładzie zobaczmy, w jaki sposób możemy poznać tę wartość.

Zlecenie SQL

SELECT @@IDENTITY

pozwala ustalić ostatnią wartość wprowadzoną do pola ID w tabeli. Należy go wygenerować po wstawieniu danych. Różni się to od zapytań SGBD, [Firebird] i [Postgres], w których przed wstawieniem pobierano wartość klucza głównego dodanej osoby, ale jest to analogiczne do generowania klucza głównego w plikach SGBD i MySQL. Wykorzystamy go w pliku [personnes-sqlexpress.xml], który gromadzi polecenia SQL wysyłane do bazy danych.

20.2. Projekt Eclipse warstw [dao] i [service]

Aby opracować warstwy [dao] i [service] naszej aplikacji z bazą danych [SQL Server Express], wykorzystamy następujący projekt Eclipse [mvc-personnes-06]:

Projekt ten jest prostym projektem Java, a nie projektem internetowym opartym na Tomcat.

Folder [src]

Ten folder zawiera kod źródłowy warstw [dao] i [service]:

Wszystkie pliki, których nazwy zawierają [sqlexpress], mogły ulec zmianom w stosunku do wersji Firebird, Postgres i MySQL lub pozostać bez zmian. Poniżej opisujemy tylko te, które zostały zmodyfikowane.

Folder [database]

Ten folder zawiera skrypt służący do tworzenia bazy danych SQL Express zawierającej dane osób:

-- SQL Manager 2005 Lite dla serwera SQL (2.2.0.1)
-- ---------------------------------------
-- Host      : (local)\SQLEXPRESS
-- Baza danych: dbpersonnes


--
-- Struktura tabeli PERSONNES: 
--

CREATE TABLE [dbo].[PERSONNES] (
  [ID] int IDENTITY(1, 1) NOT NULL,
  [VERSION] int NOT NULL,
  [NOM] varchar(30) COLLATE French_CI_AS NOT NULL,
  [PRENOM] varchar(30) COLLATE French_CI_AS NOT NULL,
  [DATENAISSANCE] datetime NOT NULL,
  [MARIE] tinyint NOT NULL,
  [NBENFANTS] tinyint NOT NULL,
  CONSTRAINT [PERSONNES_ck_NBENFANTS] CHECK ([NBENFANTS]>=(0)),
  CONSTRAINT [PERSONNES_ck_NOM] CHECK ([NOM]<>''),
  CONSTRAINT [PERSONNES_ck_PRENOM] CHECK ([PRENOM]<>'')
)
ON [PRIMARY]
GO

--
-- Dane dla tabeli PERSONNES  (LIMIT 0,500)
--

SET IDENTITY_INSERT [dbo].[PERSONNES] ON
GO

INSERT INTO [dbo].[PERSONNES] ([ID], [VERSION], [NOM], [PRENOM], [DATENAISSANCE], [MARIE], [NBENFANTS])
VALUES 
  (1, 1, 'Major', 'Joachim', '19541113', 1, 2)
GO

INSERT INTO [dbo].[PERSONNES] ([ID], [VERSION], [NOM], [PRENOM], [DATENAISSANCE], [MARIE], [NBENFANTS])
VALUES 
  (2, 1, 'Humbort', 'Mélanie', '19850212', 0, 1)
GO

INSERT INTO [dbo].[PERSONNES] ([ID], [VERSION], [NOM], [PRENOM], [DATENAISSANCE], [MARIE], [NBENFANTS])
VALUES 
  (3, 1, 'Lemarchand', 'Charles', '19860301', 0, 0)
GO

SET IDENTITY_INSERT [dbo].[PERSONNES] OFF
GO

--
-- Definicja indeksów: 
--

ALTER TABLE [dbo].[PERSONNES]
ADD PRIMARY KEY CLUSTERED ([ID])
WITH (
  PAD_INDEX = OFF,
  IGNORE_DUP_KEY = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
GO

Folder [lib]

Ten folder zawiera archiwa niezbędne do działania aplikacji:

Warto zwrócić uwagę na obecność sterownika JDBC [sqljdbc.jar] w pakietach SGBD i [Sql Server Express]. Wszystkie te pliki archiwalne stanowią część pakietu Classpath projektu Eclipse.

20.3. Warstwa [dao]

Warstwa [dao] ma następującą strukturę:

Przedstawiamy jedynie zmiany w stosunku do wersji [Firebird].

Plik mapowania [personne-sqlexpress.xml] ma następującą postać:


<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE sqlMap
    PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"
    "http://www.ibatis.com/dtd/sql-map-2.dtd">

<sqlMap>
    <!-- alias klasy [Personne] -->
    <typeAlias alias="Personne.classe" 
        type="istia.st.mvc.personnes.entites.Personne"/>
    <!-- tabela mapowania [PERSONNES] – obiekt [Personne] -->
    <resultMap id="Personne.map" 
        class="istia.st.mvc.personnes.entites.Personne">
        <result property="id" column="ID" />
        <result property="version" column="VERSION" />
        <result property="nom" column="NOM"/>
        <result property="prenom" column="PRENOM"/>
        <result property="dateNaissance" column="DATENAISSANCE"/>
        <result property="marie" column="MARIE"/>
        <result property="nbEnfants" column="NBENFANTS"/>
    </resultMap>
    <!-- lista wszystkich osób -->
    <select id="Personne.getAll" resultMap="Personne.map" > select ID, VERSION, NOM, 
        PRENOM, DATENAISSANCE, MARIE, NBENFANTS FROM PERSONNES</select>
    <!-- pobranie konkretnej osoby -->
        <select id="Personne.getOne" resultMap="Personne.map" >select ID, VERSION, NOM, 
        PRENOM, DATENAISSANCE, MARIE, NBENFANTS FROM PERSONNES WHERE ID=#wartość#</select>
    <!-- dodaj osobę -->
    <insert id="Personne.insertOne" parameterClass="Personne.classe">
        insert into 
        PERSONNES(VERSION, NOM, PRENOM, DATENAISSANCE, MARIE, NBENFANTS) 
        VALUES(#wersja#, #nazwisko#, #imię#, #dateNaissance#, #mąż#, 
         #nbEnfants#) 
        <selectKey keyProperty="id">
            select @@IDENTITY as value
        </selectKey>         
    </insert>
    <!-- zaktualizuj dane osoby -->
    <update id="Personne.updateOne" parameterClass="Personne.classe"> update 
        PERSONNES set VERSION=#wersja#+1, NOM=#nazwisko#, PRENOM=#imię#, DATENAISSANCE=#dateNaissance#, 
        MARIE=#mąż#, NBENFANTS=#nbEnfants# WHERE ID=#id# oraz 
        VERSION=#wersja#</update>
    <!-- usuń osobę -->
    <delete id="Personne.deleteOne" parameterClass="int"> delete FROM PERSONNES WHERE 
        ID=#wartość# </delete>
    <!-- pobierz wartość klucza głównego [id] ostatniej dodanej osoby -->
    <select id="Personne.getNextId" resultClass="int">select 
        LAST_INSERT_ID()</select>
</sqlMap>

Zawartość jest taka sama jak w pliku [personnes-firebird.xml], z wyjątkiem następujących szczegółów:

kolejność SQL „Personne.insertOne” uległa zmianie w wierszach 29–37:
polecenie wstawiania SQL jest wykonywane przed poleceniem SELECT, które umożliwi pobranie wartości klucza głównego z wstawionego wiersza
polecenie wstawiania SQL nie zawiera wartości dla kolumny ID w tabeli [PERSONNES]

Odzwierciedla to przykład wstawiania, który omówiliśmy w paragrafie 20.1. Należy zauważyć, że pojawia się tu problem jednoczesnego wstawiania przez różne wątki, opisany dla MySQL w paragrafie 19.3.

Klasa implementacyjna [DaoImplCommon] warstwy [dao] jest taka sama jak w trzech poprzednich wersjach.

Konfiguracja warstwy [dao] została dostosowana do SGBD i [SQL Express]. W związku z tym plik konfiguracyjny [spring-config-test-dao-sqlexpress.xml] ma następującą postać:


<?xml version="1.0" encoding="ISO_8859-1"?>
<!DOCTYPE beans SYSTEM "http://www.springframework.org/dtd/spring-beans.dtd">
<beans>
    <!-- źródło danych DBCP -->
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" 
        destroy-method="close">
        <property name="driverClassName">
            <value>com.microsoft.sqlserver.jdbc.SQLServerDriver</value>
        </property>
        <property name="url">
            <value>jdbc:sqlserver://localhost\\SQLEXPRESS:4000;databaseName=dbpersonnes</value>
        </property>
        <property name="username">
            <value>sa</value>
        </property>
        <property name="password">
            <value>msde</value>
        </property>
    </bean>
    <!-- SqlMapCllient -->
    <bean id="sqlMapClient" 
        class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
        <property name="dataSource">
            <ref local="dataSource"/>
        </property>
        <property name="configLocation">
            <value>classpath:sql-map-config-sqlexpress.xml</value>
        </property>
    </bean>
    <!-- klasa dostępu do warstwy [dao] -->
    <bean id="dao" class="istia.st.mvc.personnes.dao.DaoImplCommon">
        <property name="sqlMapClient">
            <ref local="sqlMapClient"/>
        </property>
    </bean>
</beans>

wiersze 5–19: bean [dataSource] odnosi się teraz do bazy [SQL Express] [dbpersonnes], której administratorem jest [sa] z hasłem [msde]. Czytelnik powinien dostosować tę konfigurację do własnego środowiska.
wiersz 31: klasa [DaoImplCommon] jest klasą implementacyjną warstwy [dao]

Warto wyjaśnić wiersz 11:

            <value>jdbc:sqlserver://localhost\\SQLEXPRESS:4000;databaseName=dbpersonnes</value>

//localhost: oznacza, że serwer SQL Express znajduje się na tym samym komputerze co nasza aplikacja Java
\\SQLEXPRESS: to nazwa instancji serwera SQL. Wygląda na to, że jednocześnie może działać kilka instancji. Wydaje się więc logiczne, aby nadać nazwę instancji, z którą się łączymy. Nazwę tę można uzyskać za pomocą programu [SQL Server Configuration Manager], który zazwyczaj jest instalowany razem z SQL Express:

4000: port nasłuchowy serwisu SQL Express. Zależy to od konfiguracji serwera. Domyślnie serwis korzysta z portów dynamicznych, a więc nieznanych z góry. W związku z tym w adresie URL JDBC nie podaje się portu. W tym przypadku korzystaliśmy ze stałego portu, czyli portu 4000. Uzyskuje się to poprzez konfigurację:

Atrybut dataBaseName określa bazę danych, z którą chcemy pracować. Jest to baza utworzona za pomocą klienta EMS:

Po wprowadzeniu tych zmian można przejść do testów.

20.4. Testy warstw [dao] i [service]

Testy warstw [dao] i [service] są takie same jak w przypadku wersji [Firebird]. Uzyskane wyniki są następujące:

Stwierdzono, że testy zakończyły się powodzeniem w przypadku implementacji [DaoImplCommon]. Nie będziemy musieli tworzyć klasy pochodnej, jak to było konieczne w przypadku SGBD i [Firebird].

20.5. Testy aplikacji [web]

Aby przetestować aplikację internetową z wykorzystaniem SGBD i [SQL Server Express], tworzymy projekt Eclipse [mvc-personnes-06B] w sposób analogiczny do tego, jaki zastosowaliśmy przy tworzeniu poprzednich projektów internetowych.

Wdrażamy projekt internetowy [mvc-personnes-05B] w serwerze Tomcat:

Uruchamiany jest serwer Express SGBD SQL. Zawartość tabeli [PERSONNES] wygląda wówczas następująco:

Następnie uruchamiany jest serwer Tomcat. W przeglądarce wpisujemy adres URL [http://localhost:8080/mvc-personnes-06B]:

Dodajemy nową osobę za pomocą linku [Ajout]:

Sprawdzamy, czy wpis został dodany do bazy danych:

Zachęcamy czytelnika do przeprowadzenia dalszych testów z linkiem [modification, suppression].