第8章 數(shù)據(jù)庫(kù)系統(tǒng)的應(yīng)用與開(kāi)發(fā)

第8章數(shù)據(jù)庫(kù)系統(tǒng)的應(yīng)用與開(kāi)發(fā)第3版本章學(xué)習(xí)目標(biāo)●

SQLServer集成環(huán)境；●數(shù)據(jù)庫(kù)系統(tǒng)的體系結(jié)構(gòu)；●常用數(shù)據(jù)庫(kù)編程接口；●案例設(shè)計(jì)開(kāi)發(fā)：學(xué)生成績(jī)管理系統(tǒng)；內(nèi)容：重點(diǎn)：●數(shù)據(jù)庫(kù)編程接口●數(shù)據(jù)庫(kù)系統(tǒng)應(yīng)用案例開(kāi)發(fā)**數(shù)據(jù)庫(kù)結(jié)構(gòu)的分析—概念模型->邏輯模型8.1SQLServer集成環(huán)境8.1.1SQLServer發(fā)展歷程1、微軟公司早年與Sybase公司共同研發(fā)數(shù)據(jù)庫(kù)，后來(lái)自己開(kāi)發(fā)數(shù)據(jù)庫(kù)系統(tǒng)MSSQLServer，發(fā)布了SQLServer6.5版，后續(xù)分布了SQLServer7.0；后續(xù)依次出了：2000版、2005版、2008版、2008R2版、2012版、2014版、2016版和2017版，到當(dāng)前的2019版。本教材以2019版為基礎(chǔ)。2、SQLServer2000版本適合應(yīng)用于部門IT和增強(qiáng)型企業(yè)數(shù)據(jù)平臺(tái)的可靠服務(wù)產(chǎn)品。其突出的特點(diǎn)及功能包括：XML支持、數(shù)據(jù)挖掘和數(shù)據(jù)腳本、故障恢復(fù)群集；3、SQLServer2005版本適合應(yīng)用于部門IT和增強(qiáng)型企業(yè)數(shù)據(jù)平臺(tái)的可靠服務(wù)產(chǎn)品。4、SQLServer2008版本適合應(yīng)用于綜合性數(shù)據(jù)平臺(tái)解決方案，在關(guān)鍵業(yè)務(wù)和商業(yè)智能方面具有很大的改進(jìn)。SQLServer2008R2版本通過(guò)強(qiáng)大的分析處理能力，提供綜合性集成數(shù)據(jù)管理和商業(yè)智能。5、SQLServer2012版本通過(guò)云就緒技術(shù)和解決方案，為業(yè)務(wù)關(guān)鍵型數(shù)據(jù)庫(kù)和端到端商業(yè)智能提供新標(biāo)準(zhǔn)。6、SQLServer2014版本包含SQLServer2012版所有功能外，還包括：通過(guò)內(nèi)存優(yōu)化表使交易型系統(tǒng)的性能提高10倍以上；數(shù)據(jù)倉(cāng)庫(kù)列存儲(chǔ)技術(shù)使分析型查詢的性能提高100倍以上；SSD緩存池?cái)U(kuò)展；資源調(diào)控器實(shí)現(xiàn)IO管理，便于數(shù)據(jù)庫(kù)整合；輕松備份至MicrosoftAzure；云備份加密支持。7、SQLServer2016版本提供的主要功能包括：實(shí)時(shí)運(yùn)營(yíng)分析；高可用性和災(zāi)難恢復(fù)；安全性和合規(guī)性；高性能的數(shù)據(jù)倉(cāng)庫(kù)；將復(fù)雜數(shù)據(jù)轉(zhuǎn)化為切實(shí)可行的見(jiàn)解；移動(dòng)商業(yè)智能；簡(jiǎn)化大數(shù)據(jù)；數(shù)據(jù)庫(kù)內(nèi)高級(jí)分析；從本地到云的一致性體驗(yàn)的數(shù)據(jù)平臺(tái)等。8、SQLServer2017版本提供的主要功能有：用于加密數(shù)據(jù)的安全傳輸層（TLS）支持；機(jī)器學(xué)習(xí)服務(wù)增強(qiáng)；SQLServer分析服務(wù)（SSAS）；Linux活動(dòng)目錄下的SQLServer集成；Linux平臺(tái)上的SQLServer集成服務(wù)（SSIS）；WindowsServer上的SSIS等。9、SQLServer2019版本提供的功能主要包括兩大方面：內(nèi)存技術(shù)改進(jìn)與云整合。兩大方面的突出功能體現(xiàn)了，無(wú)論是結(jié)構(gòu)化數(shù)據(jù)還是非結(jié)構(gòu)化數(shù)據(jù)，SQLServer2019都具有行業(yè)領(lǐng)先性能和安全性的數(shù)據(jù)平臺(tái)來(lái)查詢和分析數(shù)據(jù)。8.1.2SQLServer版本概述企業(yè)版。企業(yè)版是功能最齊全的版本，主要針對(duì)企業(yè)用戶，需要收費(fèi)，即需要用戶購(gòu)買后，用獲得的序列號(hào)激活才可使用。標(biāo)準(zhǔn)版。標(biāo)準(zhǔn)版是針對(duì)個(gè)人開(kāi)發(fā)的版本，不是免費(fèi)的，同樣需要用戶購(gòu)買后，用獲得的序列號(hào)激活才可使用。與企業(yè)版的區(qū)別主要體現(xiàn)在性能、允許用戶數(shù)量以及部分功能上。開(kāi)發(fā)者版。開(kāi)發(fā)者版是針對(duì)開(kāi)發(fā)者的版本，是免費(fèi)的版本，包含了企業(yè)版全部的完整功能，但該版本僅能用于開(kāi)發(fā)、測(cè)試和演示，不允許部署到生產(chǎn)環(huán)境中。速成版。速成版是一個(gè)入門級(jí)的SQLServer數(shù)據(jù)庫(kù)版本，和開(kāi)發(fā)者版一樣完全是免費(fèi)的，適用于學(xué)習(xí)和開(kāi)發(fā)測(cè)試，也可以用于部署較小規(guī)模的Web網(wǎng)站和應(yīng)用程序服務(wù)器。Web版。SQLServerWeb版是Web主機(jī)托管服務(wù)提供商和WebVAP的低總體擁有成本選擇，它可針對(duì)從小規(guī)模到大規(guī)模Web資產(chǎn)等內(nèi)容提供可伸縮性、經(jīng)濟(jì)性和可管理性能力。8.1.3SQLServer服務(wù)器安裝以SQLServer2019速成版為例，介紹SQLServer服務(wù)器的安裝與配置。1.安裝SQLServer服務(wù)器/zh-cn/sql-server/sql-server-downloads

下載SQLServer2019Express（速成版），得到SQL2019-SSEI-Expr.exe文件。有3種安裝類型：基本、自定義、下載介質(zhì)。推薦選擇“基本”安裝類型，即可以安裝帶默認(rèn)配置的SQLServer數(shù)據(jù)庫(kù)引擎功能，當(dāng)然也可以根據(jù)需要選擇“自定義”安裝類型，或者選擇“下載介質(zhì)”安裝類型將安裝文件下載到本地硬盤，以便于多次安裝。（1）在線安裝選擇“基本”安裝類型后保持默認(rèn)的“中文簡(jiǎn)體”語(yǔ)言選項(xiàng)，單擊“接受”按鈕接受“MicrosoftSQLServer許可條款”，在“安裝位置”欄中可自己更改也可保持默認(rèn)安裝位置C:\ProgramFiles\MicrosoftSQLServer，單擊“安裝”按鈕開(kāi)始在線安裝。等待一會(huì)兒即可安裝成功，安裝成功后顯示如圖8.2所示界面。SQLServer2019安裝成功后，若想安裝SQLServer管理工具，則接著單擊“安裝SSMS”按鈕即可（2）離線安裝若選擇“下載介質(zhì)”下載安裝文件到本地硬盤，則雙擊打開(kāi)下載得到的安裝包文件SQLEXPR_x64_CHS.exe或SQLEXPRADV_x64_CHS.exe。打開(kāi)如圖8.3所示界面，選擇“全新SQLServer獨(dú)立安裝或向現(xiàn)有安裝添加功能”選項(xiàng)，在下一界面中選中“我接受許可條款和隱私聲明”復(fù)選框，并單擊“下一步”按鈕推薦選擇功能有：數(shù)據(jù)庫(kù)引擎服務(wù)、SQLServer復(fù)制、全文和語(yǔ)義提取搜索、客戶端工具連接、客戶端工具向后兼容性，其他功能則不選擇，即如圖8.6所示的①框里的功能選擇。而②里的默認(rèn)安裝路徑可根據(jù)需要來(lái)修改，建議實(shí)例根目錄改到非C盤目錄，如D:\GreenTools\SQL2019。實(shí)例配置界面&服務(wù)器配置界面數(shù)據(jù)庫(kù)引擎配置界面2.安裝SQLServer管理工具SQLServerManagementStudio（SSMS）是一種集成環(huán)境，用于管理從SQLServer到AzureSQL數(shù)據(jù)庫(kù)的任何SQL基礎(chǔ)結(jié)構(gòu)。SSMS提供用于配置、監(jiān)視、管理SQLServer和數(shù)據(jù)庫(kù)實(shí)例的工具。使用SSMS部署、監(jiān)視和升級(jí)應(yīng)用程序使用的數(shù)據(jù)層組件，以及生成查詢和腳本。無(wú)論你的數(shù)據(jù)庫(kù)和數(shù)據(jù)倉(cāng)庫(kù)是位于本地計(jì)算機(jī)還是云端中，都可以使用SSMS進(jìn)行查詢、設(shè)計(jì)和管理。在圖8.3所示界面上選擇“安裝SQLServer管理工具”選項(xiàng)，會(huì)打開(kāi)下載SQLServerManagementStudio（SSMS）界面，再在界面中單擊“免費(fèi)下載SQLServerManagementStudio(SSMS)”鏈接，下載得到文件SSMS-Setup-CHS.exe。雙擊后打開(kāi)界面，單擊“安裝”按鈕即可。安裝SQLServer管理工具后，安裝的工具有SQLServer管理控制臺(tái)、SQLServerProfiler、數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)、分析服務(wù)部署向?qū)У裙ぞ?，還包含安裝了客戶端和服務(wù)器之間通信的連接組件，以及用于ODBC和OLEDB的網(wǎng)絡(luò)驅(qū)動(dòng)庫(kù)。

SQLServer配置管理器SQLServer網(wǎng)絡(luò)配置選擇“SQLServer網(wǎng)絡(luò)配置”→“SQLEXPRESS協(xié)議”選項(xiàng)，該協(xié)議包括：SharedMemory、NamesPipes、TCP/IP。默認(rèn)情況下只啟用SharedMemory。若當(dāng)前安裝的服務(wù)器通過(guò)局域網(wǎng)提供給其他客戶端使用（如編寫程序連接數(shù)據(jù)庫(kù)），則需要啟用TCP/IP。SQL

Server

EXPRESS安裝完畢后默認(rèn)沒(méi)有監(jiān)聽(tīng)端口，需要設(shè)置TCP/IP的端口（通常端口號(hào)為1433，可修改但需要注意操作系統(tǒng)防火墻）。具體操作為：選擇“SQLServer網(wǎng)絡(luò)配置”→“SQLEXPRESS的協(xié)議”選項(xiàng)，雙擊或右擊TCP/IP，選擇“屬性”選項(xiàng)，在“IP地址”選項(xiàng)卡中，選擇IPALL→“TCP端口”選項(xiàng)，將“TCP端口”設(shè)置成1433即可，如圖8.12所示。然后依次右擊“禁用”和“啟用”選項(xiàng)，使設(shè)置的監(jiān)聽(tīng)端口生效，再選擇“SQLServer服務(wù)”→“SQLServer（SQLEXPRESS）”選項(xiàng)，右擊“重啟”選項(xiàng)。重啟后在“命令提示符”里運(yùn)行網(wǎng)絡(luò)命令“netstat-an”，檢查活動(dòng)連接中應(yīng)用出現(xiàn)有TCP/IP對(duì)本地地址“:1433”的“LISTENING”監(jiān)聽(tīng)狀態(tài)。SQLServer管理控制臺(tái)8.1.4

SQLServer數(shù)據(jù)類型SQLServer主要數(shù)據(jù)類型SQLServer主要數(shù)據(jù)類型（續(xù)）

8.2數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)的體系結(jié)構(gòu)一般有四種基本架構(gòu)模式：?jiǎn)斡脩裟Ｊ街鲝氖蕉嘤脩裟Ｊ娇蛻舳?服務(wù)器（Client/Server，C/S）架構(gòu)模式瀏覽器/服務(wù)器（Browser/Server，B/S）架構(gòu)模式8.3常用數(shù)據(jù)庫(kù)編程接口常用的接口有ODBC、OLEDB、ADO、ADO.NET、JDBC等OLEDB接口ADO接口JDBC接口8.4案例開(kāi)發(fā)——學(xué)生成績(jī)管理系統(tǒng)開(kāi)發(fā)一個(gè)簡(jiǎn)單版的學(xué)生成績(jī)管理系統(tǒng)的主要需求功能：（1）學(xué)生信息管理模塊。學(xué)生信息管理模塊的主要任務(wù)是實(shí)現(xiàn)對(duì)學(xué)生的個(gè)人信息的管理工作，對(duì)學(xué)生信息進(jìn)行維護(hù)更新，包括添加、查詢、修改、刪除學(xué)生信息等。學(xué)生主要信息有學(xué)號(hào)、姓名、性別、出生日期、系別、專業(yè)等。（2）學(xué)生成績(jī)管理模塊。本系統(tǒng)需要對(duì)學(xué)生成績(jī)進(jìn)行統(tǒng)計(jì)與分析，以便教師快速地了解學(xué)生的學(xué)習(xí)狀況。學(xué)生成績(jī)管理模塊包括成績(jī)添加、成績(jī)修改、成績(jī)查詢、成績(jī)統(tǒng)計(jì)等功能?？筛鶕?jù)學(xué)生學(xué)號(hào)、姓名或?qū)I(yè)等進(jìn)行查詢過(guò)濾，再輸入對(duì)應(yīng)學(xué)生的課程成績(jī)，也可以修改或刪除學(xué)生成績(jī)信息。查詢功能是系統(tǒng)的核心功能之一。在系統(tǒng)中既有單條件查詢也有多條件查詢，可以實(shí)現(xiàn)精確查詢和模糊查詢，還可以按照院系或?qū)I(yè)、成績(jī)范圍、課程等條件查詢。開(kāi)發(fā)者可以根據(jù)需要合理設(shè)計(jì)。（3）系統(tǒng)管理模塊。為保證本系統(tǒng)的正常運(yùn)行和其安全性，設(shè)計(jì)此模塊。系統(tǒng)管理模塊的主要任務(wù)是維護(hù)系統(tǒng)的正常運(yùn)行和安全性設(shè)置，包括用戶注冊(cè)、用戶登錄、修改密碼、退出登錄等功能。8.4.2數(shù)據(jù)庫(kù)設(shè)計(jì)根據(jù)需求功能分析，使用E-R圖設(shè)計(jì)出系統(tǒng)的概念模型，并將其轉(zhuǎn)換成邏輯模型。經(jīng)過(guò)優(yōu)化后轉(zhuǎn)換為具體的物理數(shù)據(jù)庫(kù)結(jié)構(gòu)，即物理模型。對(duì)于本案例“學(xué)生成績(jī)管理系統(tǒng)”采用MicrosoftSQLServer2019（Express版）數(shù)據(jù)庫(kù)管理系統(tǒng)。1.數(shù)據(jù)庫(kù)概念結(jié)構(gòu)設(shè)計(jì)本系統(tǒng)只涉及學(xué)生注冊(cè)登錄、教務(wù)部門課程錄入、排課或?qū)W生選課、教師成績(jī)處理、學(xué)生成績(jī)查詢等功能。相關(guān)的實(shí)體有院系部門、專業(yè)、教師、學(xué)生、課程等。經(jīng)過(guò)分析主要有如下聯(lián)系：院系部門與教師之間的1:n“聘用”聯(lián)系；院系部門與學(xué)生之間的1:n“包含”聯(lián)系；院系部門與專業(yè)之間的1:n“開(kāi)設(shè)”聯(lián)系；學(xué)生與課程之間的m:n“選修”聯(lián)系。學(xué)生成績(jī)管理概念模型（E-R圖）本系統(tǒng)核心是學(xué)生成績(jī)管理，因此對(duì)于教師實(shí)體也可以不考慮，因教師僅僅是外部實(shí)體（系統(tǒng)外的使用者），而學(xué)生也是使用者，但學(xué)生又是系統(tǒng)內(nèi)部實(shí)體。按照5.3節(jié)數(shù)據(jù)庫(kù)概念結(jié)構(gòu)設(shè)計(jì)原則，本系統(tǒng)的概念結(jié)構(gòu)模型E-R圖如圖8.21所示。注意，圖8.21省略了實(shí)體的屬性，保留了實(shí)體聯(lián)系的重要屬性。2.數(shù)據(jù)庫(kù)邏輯結(jié)構(gòu)設(shè)計(jì)根據(jù)5.4.1節(jié)E-R模型向關(guān)系模型的轉(zhuǎn)換規(guī)則，將圖8.21轉(zhuǎn)換成關(guān)系模型，并考慮優(yōu)化掉某些外部實(shí)體和弱實(shí)體，以及僅保留重要屬性等，可以得到如下關(guān)系模式：院系部門（院系編號(hào)，院系名稱，院系簡(jiǎn)稱）；學(xué)生（學(xué)號(hào)，姓名，性別，出生日期，院系，專業(yè)），院系是外關(guān)鍵字，專業(yè)也是外關(guān)鍵字；專業(yè)（專業(yè)編號(hào)，專業(yè)名稱，所屬院系），所屬院系是外關(guān)鍵字；課程（課程編號(hào)，課程名稱，先修課程，學(xué)時(shí)，學(xué)分，開(kāi)課學(xué)期），先修課程是以自己為參照的外關(guān)鍵字；選修（學(xué)號(hào)，課程編號(hào)，成績(jī)），學(xué)號(hào)和課程編號(hào)分別是關(guān)系“選修”的兩個(gè)外關(guān)鍵字。3.數(shù)據(jù)庫(kù)表結(jié)構(gòu)設(shè)計(jì)根據(jù)上述設(shè)計(jì)的關(guān)系模型邏輯結(jié)構(gòu)，以及選擇的SQLServer2019數(shù)據(jù)庫(kù)系統(tǒng)。本案例系統(tǒng)的數(shù)據(jù)庫(kù)表結(jié)構(gòu)有院系表Department、學(xué)生信息表Student、專業(yè)表Major、課程表Course、成績(jī)表SC以及用戶表Users8.4.3系統(tǒng)設(shè)計(jì)與實(shí)現(xiàn)本系統(tǒng)采用C/S架構(gòu)模式設(shè)計(jì)、使用Java語(yǔ)言及JDBC數(shù)據(jù)庫(kù)連接接口實(shí)現(xiàn)，根據(jù)系統(tǒng)使用者劃分多個(gè)角色：系統(tǒng)管理員、教務(wù)員、教師、學(xué)生。結(jié)合數(shù)據(jù)庫(kù)結(jié)構(gòu)設(shè)計(jì)，可在用戶表中設(shè)置相應(yīng)用戶賬號(hào)的權(quán)限角色，以便控制不同角色身份使用不同的功能模塊。1.準(zhǔn)備JDBC驅(qū)動(dòng)程序包在微軟官方網(wǎng)站下載SQLServer最新版JDBC驅(qū)動(dòng)程序包，如MicrosoftJDBCDriver11.2forSQLServer，得到壓縮包文件sqljdbc__chs.zip，壓縮包中類似mssql-jdbc-11.2.0.jre8.jar就是所需JDBC驅(qū)動(dòng)包。2.JDBCAPI基本操作（1）DriverManager類 DriverManager類用于加載JDBC驅(qū)動(dòng)并且創(chuàng)建與數(shù)據(jù)庫(kù)的連接。（2）Connection接口 Connection接口代表Java程序和數(shù)據(jù)庫(kù)的連接，只有獲得該連接對(duì)象后才能訪問(wèn)數(shù)據(jù)庫(kù)和操作數(shù)據(jù)表。（3）Statement接口 Statement接口用于執(zhí)行靜態(tài)的SQL語(yǔ)句，并返回一個(gè)結(jié)果集對(duì)象，該接口的對(duì)象通過(guò)Connection實(shí)例的createStatement()方法獲得。利用該對(duì)象把靜態(tài)SQL語(yǔ)句發(fā)送到數(shù)據(jù)庫(kù)編譯執(zhí)行，然后返回?cái)?shù)據(jù)庫(kù)的處理結(jié)果。（4）PreparedStatement接口 PreparedStatement是Statement的子接口，用于執(zhí)行預(yù)編譯的SQL語(yǔ)句。該接口擴(kuò)展為帶參數(shù)SQL語(yǔ)句的執(zhí)行操作，應(yīng)用該接口中的SQL語(yǔ)句可以使用占位符“?”來(lái)代替其參數(shù)，然后通過(guò)對(duì)應(yīng)的setXxx()方法為SQL語(yǔ)句的參數(shù)賦值。（5）ResultSet接口 ResultSet接口用于保存JDBC執(zhí)行查詢時(shí)返回的結(jié)果集，該結(jié)果集封裝在一個(gè)邏輯表格中。在ResultSet接口內(nèi)部有一個(gè)指向表格數(shù)據(jù)行的游標(biāo)(或指針)。當(dāng)ResultSet對(duì)象初始化時(shí)，游標(biāo)在表格的第1行之前，調(diào)用next()方法可將游標(biāo)移動(dòng)到下一行。如果后面沒(méi)有數(shù)據(jù)，則返回false。在應(yīng)用程序中經(jīng)常使用next()方法作為循環(huán)條件來(lái)迭代遍歷ResultSet結(jié)果集。3.建立新工程及數(shù)據(jù)庫(kù)表對(duì)應(yīng)的實(shí)體類將8.4.2節(jié)中設(shè)計(jì)的表結(jié)構(gòu)在Java工程中建立相應(yīng)的實(shí)體類，如Users類、Student類等。4.建立本系統(tǒng)公用的數(shù)據(jù)訪問(wèn)處理工具perties文件driverClass=com.microsoft.sqlserver.jdbc.SQLServerDriverurl=jdbc:sqlserver://:1433;instanceName=SQLEXPRESS;databaseName=StudentDB;integratedSecurity=false;encrypt=falseuser=sapassword=12345678不同數(shù)據(jù)庫(kù)的JDBC連接驅(qū)動(dòng)類driverClass和URL不相同，請(qǐng)根據(jù)對(duì)應(yīng)數(shù)據(jù)庫(kù)使用正確的驅(qū)動(dòng)類和URL連接字符串。另外建議單獨(dú)建立數(shù)據(jù)庫(kù)連接用戶，而禁止使用超級(jí)管理員sa。（1）JdbcDao類publicclassJdbcDao{privatestaticPropertiesproperties=null;static{//靜態(tài)代碼塊，類加載時(shí)自動(dòng)執(zhí)行InputStreamdbProps=JdbcDaoUtils.class.getClassLoader().getResourceAsStream("perties");properties=newProperties();try{properties.load(dbProps);}catch(IOExceptione){thrownewRuntimeException(e);}}

privatestaticStringurl=properties.getProperty("url");privatestaticStringuser=properties.getProperty("user");privatestaticStringpassword=properties.getProperty("password");privatestaticStringdriverClass=properties.getProperty("driverClass");privatestaticConnectionconn=null;privatestaticPreparedStatementpstmt=null;privatestaticResultSetrs=null;

//獲得數(shù)據(jù)庫(kù)連接publicstaticConnectiongetConnection(){try{Class.forName(driverClass);conn=DriverManager.getConnection(url,user,password);}catch(ClassNotFoundException|SQLExceptione){e.printStackTrace();}returnconn;}//關(guān)閉數(shù)據(jù)庫(kù)連接，釋放資源publicstaticvoidclose(ResultSetrs,PreparedStatementpstmt,Connectionconn){try{if(rs!=n