20. Веб-додаток MVC у трирівневій архітектурі — Приклад 6, SQL Server Express

20.1. База даних SQL Server Express

У цій версії ми встановимо список осіб у таблицю бази даних SQL Server Express 2005, доступну за посиланням [http://msdn.microsoft.com/vstudio/express/sql/]. Наведені нижче знімки екрана взяті з клієнтської програми EMS Manager Lite для SQL Server Express [http://www.sqlmanager.net/fr/products/mssql/manager], безкоштовної програми для адміністрування SGBD SQL Server Express.

База даних має назву [dbpersonnes]. Вона містить таблицю [PERSONNES]:

Таблиця [PERSONNES] міститиме список осіб, якими керує веб-додаток. Вона була створена за допомогою таких команд 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

рядок 2: первинний ключ [ID] має тип цілого числа. Атрибут IDENTITY вказує, що якщо вставити рядок без значення для стовпця ID таблиці, SQL Express самостійно згенерує ціле число для цього стовпця. У IDENTITY(1, 1) перший параметр — це перше можливе значення первинного ключа, другий — крок, що використовується при генерації чисел.

Таблиця [PERSONNES] може мати такий вміст:

Ми знаємо, що під час вставки об’єкта [Personne] нашим шаром [dao] поле [id] цього об’єкта до вставки дорівнює -1, а після вставки набуває значення, відмінного від -1; це значення є первинним ключем, присвоєним новому рядку, вставленому в таблицю [PERSONNES]. Розглянемо на прикладі, як ми зможемо дізнатися це значення.

Замовлення SQL

SELECT @@IDENTITY

дозволяє дізнатися останнє значення, вставлене в поле ID таблиці. Його слід відправити після вставки. Це відрізняється від запитів SGBD, [Firebird] та [Postgres], де запитувалося значення первинного ключа доданої особи перед вставкою, але це аналогічно генерації первинного ключа у файлах SGBD та MySQL. Ми будемо використовувати його у файлі [personnes-sqlexpress.xml], який об’єднує команди SQL, відправлені до бази даних.

20.2. Проєкт Eclipse для шарів [dao] та [service]

Для розробки шарів [dao] та [service] нашого додатка з базою даних [SQL Server Express] ми будемо використовувати такий проект Eclipse [mvc-personnes-06]:

Цей проєкт є простим Java-проєктом, а не веб-проєктом Tomcat.

Папка [src]

Ця папка містить вихідні коди шарів [dao] та [service]:

Усі файли, у назві яких міститься [sqlexpress], могли зазнати змін або залишитися без змін порівняно з версіями для Firebird, Postgres та MySQL. Далі ми описуємо лише ті, що зазнали змін.

Папка [database]

Ця папка містить скрипт для створення бази даних SQL Express з даними про осіб:

-- SQL Manager 2005 Lite для сервера SQL (2.2.0.1)
-- ---------------------------------------
-- Хост      : (local)\SQLEXPRESS
-- База даних: dbpersonnes


--
-- Структура таблиці 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

--
-- Дані для таблиці PERSONNE S (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

--
-- Визначення індексів: 
--

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

Папка [lib]

Ця папка містить архіви, необхідні для роботи програми:

Слід звернути увагу на наявність драйвера JDBC [sqljdbc.jar] у SGBD та [Sql Server Express]. Усі ці архіви входять до складу Classpath проекту Eclipse.

20.3. Рівень [dao]

Рівень [dao] має такий вигляд:

Ми наводимо лише зміни порівняно з версією [Firebird].

Файл маппінгу [personne-sqlexpress.xml] має такий вигляд:


<?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>
    <!-- псевдонім класу [Personne] -->
    <typeAlias alias="Personne.classe" 
        type="istia.st.mvc.personnes.entites.Personne"/>
    <!-- таблиця відповідності [PERSONNES] — об’єкт [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>
    <!-- список усіх осіб -->
    <select id="Personne.getAll" resultMap="Personne.map" > select ID, VERSION, NOM, 
        PRENOM, DATENAISSANCE, MARIE, NBENFANTS FROM PERSONNES</select>
    <!-- отримати конкретну особу -->
        <select id="Personne.getOne" resultMap="Personne.map" >select ID, VERSION, NOM, 
        PRENOM, DATENAISSANCE, MARIE, NBENFANTS FROM PERSONNES WHERE ID=#значення#</select>
    <!-- додати особу -->
    <insert id="Personne.insertOne" parameterClass="Personne.classe">
        insert into 
        PERSONNES(VERSION, NOM, PRENOM, DATENAISSANCE, MARIE, NBENFANTS) 
        VALUES(#версія#, #прізвище#, #ім'я#, #dateNaissance#, #заміжня#, 
         #nbEnfants#) 
        <selectKey keyProperty="id">
            select @@IDENTITY as value
        </selectKey>         
    </insert>
    <!-- оновити дані особи -->
    <update id="Personne.updateOne" parameterClass="Personne.classe"> update 
        PERSONNES set VERSION=#версія#+1, NOM=#прізвище#, PRENOM=#ім'я#, DATENAISSANCE=#dateNaissance#, 
        MARIE=#marie#, NBENFANTS=#nbEnfants# WHERE ID=#id# та 
        VERSION=#версія#</update>
    <!-- видалити особу -->
    <delete id="Personne.deleteOne" parameterClass="int"> delete FROM PERSONNES WHERE 
        ID=#значення# </delete>
    <!-- отримати значення первинного ключа [id] останньої доданої особи -->
    <select id="Personne.getNextId" resultClass="int">select 
        LAST_INSERT_ID()</select>
</sqlMap>

Його вміст збігається з [personnes-firebird.xml], за винятком таких деталей:

послідовність SQL «Personne.insertOne» змінилася у рядках 29–37:
команда вставки SQL виконується перед командою SELECT, що дозволить отримати значення первинного ключа вставленого рядка
команда вставки SQL не містить значення для стовпця ID таблиці [PERSONNES]

Це відображає приклад вставки, який ми розглядали в параграфі 20.1. Слід зазначити, що тут знову виникає проблема одночасних вставок різними потоками, описана для MySQL у параграфі 19.3.

Клас реалізації [DaoImplCommon] шару [dao] є таким самим, як і у трьох попередніх версіях.

Конфігурація шару [dao] була адаптована до SGBD та [SQL Express]. Таким чином, файл конфігурації [spring-config-test-dao-sqlexpress.xml] має такий вигляд:


<?xml version="1.0" encoding="ISO_8859-1"?>
<!DOCTYPE beans SYSTEM "http://www.springframework.org/dtd/spring-beans.dtd">
<beans>
    <!-- джерело даних 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>
    <!-- клас доступу до рівня [dao] -->
    <bean id="dao" class="istia.st.mvc.personnes.dao.DaoImplCommon">
        <property name="sqlMapClient">
            <ref local="sqlMapClient"/>
        </property>
    </bean>
</beans>

рядки 5–19: біан [dataSource] тепер вказує на базу [SQL Express] [dbpersonnes], адміністратором якої є [sa] з паролем [msde]. Читач повинен змінити цю конфігурацію відповідно до свого середовища.
рядок 31: клас [DaoImplCommon] є класом реалізації шару [dao]

Рядок 11 потребує пояснень:

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

//localhost: вказує, що сервер SQL Express знаходиться на тій самій машині, що й наша Java-програма
\\SQLEXPRESS: це ім’я екземпляра сервера SQL. Схоже, що одночасно можуть працювати кілька екземплярів. Тому логічно вказати ім’я екземпляра, до якого ми звертаємося. Це ім’я можна отримати за допомогою [SQL Server Configuration Manager], який зазвичай встановлюється разом із SQL Express:

4000: порт, на якому працює SQL Express. Це залежить від конфігурації сервера. За замовчуванням програма працює з динамічними портами, тобто тими, які заздалегідь невідомі. Тому в URL-адресі JDBC порт не вказується. У цьому випадку ми працювали з фіксованим портом — портом 4000. Це досягається за допомогою налаштувань:

атрибут dataBaseName визначає базу даних, з якою ми хочемо працювати. Це та база, яка була створена за допомогою клієнта EMS:

Після внесення цих змін можна переходити до тестування.

20.4. Тестування шарів [dao] та [service]

Тестування шарів [dao] та [service] проводиться так само, як і для версії [Firebird]. Отримано такі результати:

Можна констатувати, що тестування було успішно пройдено з реалізацією [DaoImplCommon]. Нам не доведеться створювати похідні класи від цього класу, як це було необхідно зробити для SGBD та [Firebird].

20.5. Тестування додатка [web]

Щоб протестувати веб-додаток із SGBD та [SQL Server Express], ми створюємо проект Eclipse [mvc-personnes-06B] аналогічно до того, як створювали попередні веб-проекти.

Ми розгортаємо веб-проект [mvc-personnes-05B] у Tomcat:

Сервер Express SGBD SQL запускається. Вміст таблиці [PERSONNES] тоді виглядає так:

Потім запускається Tomcat. У браузері ми вводимо URL-адресу [http://localhost:8080/mvc-personnes-06B]:

Ми додаємо нову особу за посиланням [Ajout]:

Перевіряємо, чи додано запис до бази даних:

Читачеві пропонується провести інші тести [modification, suppression].