SQL Server Database Programming with Java: Concepts, Designs and Implementations

Preface
Copyrights and Trademarks
Acknowledgment
Contents
About the Author
Chapter 1: Introduction
1.1 Outstanding Features About This Book
1.2 Who This Book Is For
1.3 What This Book Covers
1.4 How This Book Is Organized and How to Use This Book
1.5 How to Use the Source Codes and the Sample Database
1.6 Instructors Materials and Customers Supports
Chapter 2: Introduction to Databases
2.1 What Are Databases and Database Programs?
2.1.1 File Processing System
2.1.2 Integrated Databases
2.2 Develop a Database
2.3 Sample Database
2.3.1 Relational Data Model
2.3.2 Entity-Relationship Model (ER)
2.4 Identifying Keys
2.5 Define Relationships
2.6 ER Notation
2.7 Data Normalization
2.7.1 First Normal Form (1NF)
2.7.2 Second Normal Form (2NF)
2.7.3 Third Normal Form (3NF)
2.8 Database Components in Some Popular Databases
2.8.1 Microsoft Access Databases
2.8.2 SQL Server Databases
2.8.3 Oracle Databases
2.9 Create Microsoft SQL Server 2019 Express Sample Database
2.9.1 Create the LogIn Table
2.9.2 Create the Faculty Table
2.9.3 Create Other Tables
2.9.4 Create Relationships Among Tables
2.9.4.1 Create Relationship Between the LogIn and the Faculty Tables
2.9.4.2 Create Relationship Between the LogIn and the Student Tables
2.9.4.3 Create Relationship Between the Faculty and the Course Tables
2.9.4.4 Create Relationship Between the Student and the StudentCourse Tables
2.9.4.5 Create Relationship Between the Course and the StudentCourse Tables
2.9.5 Store Images to the SQL Server 2019 Express Database
2.10 A Short-Cut: How to Use the Sample Database without Building It
2.11 Chapter Summary
Chapter 3: JDBC API and JDBC Drivers
3.1 What Are JDBC and JDBC API?
3.2 JDBC Components and Architecture
3.3 How Does JDBC Work?
3.3.1 Establish a Connection
3.3.1.1 Using DriverManager to Establish a Connection
3.3.1.2 Using DataSource Object to Establish a Connection
3.3.2 Build and Execute SQL Statements
3.3.3 Process Results
3.3.3.1 Using ResultSet Object
3.3.3.2 Using RowSet Object
3.4 JDBC Driver and Driver Types
3.4.1 Type I: JDBC-ODBC Bridge Driver
3.4.2 Type II: Native-API-Partly-Java Driver
3.4.3 Type III: JDBC-Net-All-Java Driver
3.4.4 Type IV: Native-Protocol-All-Java Driver
3.5 JDBC Standard Extension API
3.5.1 JDBC DataSource
3.5.1.1 Java Naming and Directory Interface
3.5.1.2 Deploy and Use a Basic Implementation of DataSource
3.5.2 JDBC Driver-Based Connection Pooling
3.5.3 Distributed Transactions
3.5.3.1 Distributed Transaction Components and Scenarios
3.5.3.2 The Distributed Transaction Process
3.5.4 JDBC RowSet
3.5.4.1 Introduction to Java RowSet Object
3.5.4.2 Implementation Process of a RowSet Object
3.6 Chapter Summary
Chapter 4: JDBC Application and Design Considerations
4.1 JDBC Application Models
4.1.1 Two-Tier Client-Server Model
4.1.2 Three-Tier Client-Server Model
4.2 JDBC Applications Fundamentals
4.2.1 Loading and Registering Drivers
4.2.2 Getting Connected
4.2.2.1 The DriverManager and Driver Classes
4.2.2.2 Using the DriverManager.getConnection() Method
4.2.2.3 Using the Driver.connect() Method
4.2.2.4 The JDBC Connection URL
4.2.2.5 Establish a Database Connection
4.2.3 Executing Statements
4.2.3.1 Overview of Statement Objects and Their Execution Methods
4.2.3.2 Using the Statement Object
4.2.3.2.1 Creating the Statement Object
4.2.3.2.2 Executing the Statement Object
4.2.3.3 Using the PreparedStatement Object
4.2.3.3.1 Creating the PreparedStatement Object
4.2.3.3.2 Setting the Input Parameters
4.2.3.3.3 Executing the PreparedStatement Object
4.2.3.4 Using the CallableStatement Object
4.2.3.4.1 Building a CallableStatement Query String
4.2.3.4.2 Creating the CallableStatement Object
4.2.3.4.3 Setting the Input Parameters
4.2.3.4.4 Registering the Output Parameters
4.2.3.4.5 Executing the CallableStatement Object
4.2.3.5 More About the Execution Methods
4.2.3.6 Creating and Executing SQL Statements
4.2.3.6.1 Creating and Executing the DDL Statements
4.2.3.6.2 Creating and Executing the DML Statements
4.2.3.6.3 JDBC Escape Syntax
4.2.4 Retrieving Results
4.2.4.1 The ResultSet Interface
4.2.4.2 Getting and Processing the ResultSet Object
4.2.4.2.1 Fetching by Row
4.2.4.2.2 Fetching by Column
4.2.5 Using JDBC MetaData Interfaces
4.2.5.1 Using the ResultSetMetaData Interface
4.2.5.2 Using the DatabaseMetaData Interface
4.2.5.3 Using the ParameterMetaData Interface
4.2.6 Closing the Connection and Statements
4.3 Chapter Summary
Chapter 5: Introduction to Apache NetBeans IDE
5.1 Overview of the Apache NetBeans 12
5.1.1 The Apache NetBeans Platform
5.1.2 The Apache NetBeans Open-Source IDE
5.2 Installing and Confirming the Apache NetBeans IDE
5.3 Exploring Apache NetBeans IDE 12
5.3.1 An Overview of Apache NetBeans IDE 12 GUI
5.3.2 Build a New Java with Ant Project
5.3.2.1 Build a Java Application Project
5.3.2.1.1 Add a Graphical User Interface
5.3.2.1.2 Add Other GUI-Related Components
5.3.2.1.3 Develop the Codes for Three Buttons
5.3.2.1.4 Run the Project
5.3.2.2 Build a Java Class Library
5.3.2.2.1 Create a Java Class Library Project
5.3.2.2.2 Create a Java Application Project
5.3.2.2.3 Configure the Compilation Classpath
5.3.2.2.4 Add Codes to the Main.java Tab in the Java Application Project
5.3.2.2.5 Run the Application Project to Call the Java Library
5.3.2.2.6 Build and Deploy the Application
5.3.2.2.7 Distribute the Application to Other Users
5.3.2.3 Build a Java Project with Existing Sources
5.3.2.4 Build a Java Free-Form Project
5.3.3 Build a Java Web Application Project
5.4 Chapter Summary
Part I: Building Two-Tier Client-Server Applications
Chapter 6: Query Data from Databases
6.1 Setup Connection Between Microsoft SQL Server Database and Java Classes
6.1.1 Download and Install Microsoft SQL Server JDBC Driver
6.1.2 Configure TCP/IP Protocol and Setup for SQL Server Express
6.1.3 Configure Authentication Mode for SQL Server 2019 Express
6.1.4 Use the New Database Connection in Apache NetBeans to Setup a Connection
6.2 Introduction to Runtime Object Method
6.3 Create a Java Application Project to Access the SQL Server Database
6.3.1 Create Graphic User Interfaces
6.3.2 Use a JDialog as a MessageBox
6.3.3 Perform the Data Query for the LogIn Table
6.3.3.1 Load and Register Database Drivers
6.3.3.2 Add Microsoft SQL Server JDBC Driver to the Project
6.3.3.3 Load and Register Microsoft SQL Server JDBC Driver
6.3.3.3.1 The JDBC Uniform Resource Locators (URLs)
6.3.3.4 Connect to Databases and Drivers
6.3.3.5 Create and Manage Statement Object
6.3.3.6 Use PreparedStatement Object to Perform Dynamic Query
6.3.3.7 Use ResultSet Object
6.3.4 Develop the Codes for the SelectionFrame Form
6.3.4.1 Modify Codes to Transfer Between SelectionFrame and LogInFrame
6.3.5 Perform the Data Query for the Faculty Table
6.3.5.1 Add Java Package and Coding for the Constructor
6.3.5.2 Query Data using JDBC MetaData Interface
6.3.5.2.1 The DatabaseMetaData Interface
6.3.5.2.2 The ResultSetMetaData Interface
6.3.5.2.3 The ParameterMetaData Interface
6.3.5.2.4 Use DatabaseMetaData Interface to Query Database Related Information
6.3.5.2.5 Use ResultSetMetaData Interface to Query Table-Related Information
6.3.5.3 Display an Image for the Selected Faculty in Canvas
6.3.5.3.1 Operational Sequence to Display an Image in Java
6.3.5.3.2 Create a User-Defined Method to Select and Display Desired Faculty Image
6.3.5.3.3 Develop Additional Codes to Coordinate This Image Displaying
6.3.5.4 Develop the Codes for the Back Button Click Event Handler
6.3.5.5 Query Data Using the execute() Method to Perform a Query-Related Action
6.3.5.6 Query Data Using the CallableStatement Method
6.3.6 Perform the Data Query for the Course Table
6.3.6.1 Import Java Packages and Coding for the CourseFrame Constructor
6.3.6.2 Query Data From Course Table Using CallableStatements
6.3.6.2.1 Build and Formulate the CallableStatement Query String
6.3.6.2.2 Create a CallableStatement Object
6.3.6.2.3 Set the Input Parameters
6.3.6.2.4 Register the Output Parameters
6.3.6.2.5 Execute CallableStatement
6.3.6.2.6 Retrieve the Running Result
6.3.6.3 Coding for the Select Button Click Event Handler to Perform CallableStatement Query
6.3.6.4 Build the SQL Stored Procedure dbo.FacultyCourse
6.3.6.4.1 Structure and Syntax of a SQL Stored Procedure
6.3.6.4.2 Return Multiple Rows from a SQL Stored Procedure to the Java CallableStatement
6.3.6.5 Coding for the CourseList Box to Display Detailed Information for the Selected Course
6.3.6.6 Coding for the Back Button Click Event Handler
6.3.7 Query Data from the Student Table Using the Java RowSet Object
6.3.7.1 Introduction to Java RowSet Object
6.3.7.2 The Operational Procedure of Using the JDBC RowSet Object
6.3.7.3 Build a Graphical User Interface StudentFrame Form
6.3.7.4 Coding for the Constructor of the StudentFrame Class
6.3.7.5 Coding for the Select Button Event Handler to Query Data Using the CachedRowSet
6.3.7.6 Display a Student Picture for the Selected Student
6.4 Chapter Summary
Chapter 7: Insert, Update, and Delete Data from Databases
7.1 Perform Data Manipulations to SQL Server Database Using Java Runtime Object
7.2 Perform Data Insertion to SQL Server Database Using Java Runtime Object
7.2.1 Develop the Codes for the Insert Button Event Handler
7.2.2 Develop a Method for Data Checking Prior to Data Insertion
7.2.3 Develop a Method for Selecting a Valid Faculty Image
7.2.4 Find a Way to Enable the Insert Button to Begin a New Data Insertion
7.2.5 Develop a Method for Clearing Original Faculty Information
7.2.6 Develop the Codes for the Validation of the Data Insertion
7.2.7 Build and Run the Project to Test the Data Insertion
7.3 Perform Data Updating to SQL Server Database Using Java Runtime Object
7.3.1 Modify the Codes Inside the FacultyFrame Constructor
7.3.2 Develop the Codes for the Update Button Event Handler
7.3.3 Build and Run the Project to Test the Data Updating
7.4 Perform Data Deleting to SQL Server Database Using Java Runtime Object
7.4.1 Develop the Codes for the Delete Button Event Handler
7.4.2 Build and Run the Project to Test the Data Deleting
7.5 Perform Data Manipulations Using Updatable ResultSet
7.5.1 Introduction to ResultSet Enhanced Functionalities and Categories
7.5.2 Perform Data Manipulations Using Updatable ResultSet Object
7.5.2.1 Insert a New Row Using the Updatable ResultSet
7.5.2.2 Update a Data Row Using the Updatable ResultSet
7.5.2.3 Delete a Data Row Using the Updatable ResultSet
7.6 Perform Data Manipulations Using Callable Statements
7.6.1 Insert Data to the Course Table Using Callable Statements
7.6.1.1 Develop the Stored Procedure dbo.InsertNewCourse
7.6.1.2 Develop the Codes for the Insert Button Click Event Handler
7.6.2 Update Data to the Course Table Using Callable Statements
7.6.2.1 Develop the Stored Procedure dbo.UpdateCourse
7.6.2.2 Develop the Codes for the Update Button Click Event Handler
7.6.3 Delete Data from the Course Table Using Callable Statements
7.6.3.1 Develop the Stored Procedure dbo.DeleteCourse
7.6.3.2 Develop the Codes for the Delete Button Click Event Handler
7.7 Chapter Summary
Part II: Building Three-Tier Client-Server Applications
Chapter 8: Developing Java Web Applications to Access Databases
8.1 A Historical Review About Java Web Application Development
8.1.1 Using Servlet and HTML Web Pages for Java Web Applications
8.1.2 Using JavaServer Pages Technology for Java Web Applications
8.1.3 Using Java Help Class Files for Java Web Applications
8.1.4 Using the JSP Implicit Object Session for Java Web Applications
8.1.4.1 Modify the FacultyPage JSP File to Use the Session Object
8.1.4.2 Build the Transaction JSP File FacultyQuery.jsp
8.1.4.3 Build the Help Class FacultyBean
8.1.5 Using Java Beans Technology for Java Web Applications
8.1.5.1 Modify the Help Class FacultyBean to Make It a Java Bean Class
8.1.5.2 Build a New Starting Web Page FacultyBeanPage
8.1.6 Using JavaServer Faces Technology for Java Web Applications
8.1.6.1 The Application Configuration Resource File faces-config.xml
8.1.6.2 Sample JavaServer Face Page Files
8.1.6.3 The Java Bean Class File
8.1.6.4 The Web Deployment Descriptor File web.xml
8.1.6.5 A Complete Running Procedure of JSF Web Applications
8.1.6.5.1 The Java Bean-JSF Page Relationship and Page Navigations
8.1.6.5.2 The Detailed Codes for the Java Bean Class
8.2 Java EE Web Application Model
8.2.1 Java EE Web Applications with and Without EJB
8.3 The Architecture and Components of Java Web Applications
8.3.1 Java EE Containers
8.3.2 Java EE 8 APIs
8.3.2.1 Enterprise Java Beans API Technology
8.3.2.2 Java Servlet API Technology
8.3.2.3 JavaServer Pages API Technology
8.3.2.4 JavaServer Faces API Technology
8.3.2.5 Java Transaction API
8.3.2.6 Java Message Service API
8.3.3 Java Web Application Life Cycle
8.3.4 Java Web Modules
8.3.5 Java Web Frameworks
8.4 Build Java Web Project to Query SQL Server Database
8.4.1 Create Five Web Pages Using Microsoft Office Publisher 2007
8.4.1.1 Create the LogIn Page
8.4.1.2 Create the Selection Page
8.4.1.3 Create the Faculty Page
8.4.1.4 Create the Course Page
8.4.1.5 Create the Student Page
8.4.2 Setup Environments for NetBeans IDE to Build Java Web Applications
8.4.2.1 Download and Install Required Components
8.4.2.2 Configure NetBeans IDE 8.2 and Create Our First Web Application Project
8.4.3 Access and Query the LogIn Table Using JavaServer Pages and Help Class Files
8.4.3.1 Modify the LogIn.jsp Page and Create LogInQuery.jsp File
8.4.3.2 Create the Java Help Class File LogInQuery.java
8.4.3.3 Create a Dialog Box as the Message Box
8.4.3.4 Develop the Codes for the Help Class File
8.4.3.4.1 Import SQL Server-Related Package and Create the Class Constructor
8.4.3.4.2 Build the Codes for the checkLogIn() Method
8.4.3.4.3 Build the Codes for the CloseDBConnection() Method
8.4.4 Develop the Codes for the Selection Page
8.4.5 Query the Faculty Table Using JavaServer Pages and JSP Implicit Session Object
8.4.5.1 Modify the Faculty.jsp Page
8.4.5.2 Create the FacultyProcess.jsp Page
8.4.5.3 Create the Help Class File FacultyQuery.java
8.5 Build Java Web Project to Manipulate SQL Server Database
8.5.1 Modify the Faculty.jsp Page by Adding A File Selection Function
8.5.2 Insert New Records to the Faculty Table Using JavaServer Pages and Java Beans
8.5.2.1 Create a New Java Help Class File FacultyInsertBean.java
8.5.2.2 Modify the FacultyProcess.jsp Page to Handle Faculty Data Collection and Insertion
8.5.3 Update and Delete Data from the Faculty Table Using JSP and Java Beans Techniques
8.5.3.1 Create a New Java Session Bean Class
8.5.3.2 Modify the FacultyProcess Page to Handle Faculty Data Updating
8.5.3.3 Add a Method to the Java Bean to Perform Faculty Data Deleting
8.5.3.4 Modify the FacultyProcess Page to Handle Faculty Data Deleting
8.6 Chapter Summary
Chapter 9: Developing Java Web Services to Access Databases
9.1 Introduction to Java Web Services
9.1.1 REST-Based Web Services
9.1.2 SOAP-Based Web Services
9.2 The Structure and Components of SOAP-Based Web Services
9.3 The Procedure of Building a Typical SOAP-Based Web Service Project
9.3.1 Create a New Java Web Application Project WSTestApplication
9.3.2 Create a New Java SOAP-Based Web Service Project WSTest
9.3.3 Add Desired Operations to the Web Service
9.3.4 Deploy and Test the Web Service on the Selected Container
9.3.5 Create Web Service Clients to Consume the Web Service
9.4 Getting Started with Java Web Services Using NetBeans IDE
9.5 Build Java Web Service Projects to Access SQL Server Database
9.5.1 Create a New Java Web Application Project WebServiceSQLApp
9.5.2 Create a New Java SOAP-Based Web Service Project WebServiceSelect
9.5.3 Add New Operations to Our Web Services to Perform Data Query
9.5.4 Add Another Operation to Our Web Service to Query Faculty Image
9.5.5 Build the User-Defined Method DBConnection()
9.5.6 Deploy the Web Service Project and Test the Data Query Function
9.6 Build a Window-Based Client Project to Consume the Web Service
9.6.1 Copy the FacultyFrame and MsgDislog Components as GUIs
9.6.2 Create a Web Service Reference for Our Window-Based Client Project
9.6.3 Develop the Codes to Call Our Web Service Project
9.6.4 Build and Run Our Client Project to Query Faculty Data via Web Service
9.7 Build a Web-Based Client Project to Consume the Web Service
9.7.1 Create a Web-Based Client Project WebClientSelect
9.7.2 Create a Java Managed Bean FacultyBean
9.7.3 Create a Web Service Reference for Our Web-Based Client Project
9.7.4 Build the Codes to Call the Web Service to Perform Data Query
9.7.5 Build and Run Our Client Project to Query Faculty Data via Web Service
9.8 Build Java Web Service to Insert Data into the SQL Server Database
9.8.1 Add a New Operation InsertFaculty() into Our Web Service Project
9.8.2 Deploy the Web Service Project
9.9 Build a Window-Based Client Project to Consume the Web Service
9.9.1 Refresh the Web Service Reference for Our Window-Based Client Project
9.9.2 Modify the Design View and Develop the Codes to Call Our Web Service Project
9.9.3 Build and Run Our Client Project to Insert Faculty Data via Web Service
9.10 Build a Web-Based Client Project to Consume the Web Service
9.10.1 Add a Web Service Reference to Our Web-Based Client Project
9.10.2 Develop the Codes to Call Our Web Service Project
9.10.3 Build and Run Our Client Project to Insert Faculty Data via Web Service
9.11 Build Java Web Service to Update and Delete Data from the SQL Server Database
9.11.1 Add a New Operation UpdateFaculty() to Perform Faculty Data Updating
9.11.2 Add a New Operation DeleteFaculty() to Perform Faculty Data Deleting Action
9.11.3 Deploy and Test the Web Service Project
9.12 Build a Window-Based Client Project to Consume the Web Service
9.12.1 Refresh the Web Service Reference for Our Window-Based Client Project
9.12.2 Build the Codes to Call the UpdateFaculty() Operation
9.12.3 Build the Codes to Call the DeleteFaculty() Operation
9.12.4 Build and Run Our Client Project to Update and Delete Faculty Record via Web Service
9.13 Build a Web-Based Client Project to Consume the Web Service
9.13.1 Add a Web Service Reference to Our Web-Based Client Project
9.13.2 Develop the Codes to Call Our Web Service Operation UpdateFaculty()
9.13.3 Develop the Codes to Call Our Web Service Operation DeleteFaculty()
9.13.4 Build and Run Our Client Project to Update and Delete Faculty Record via Web Service
9.14 Build Java Web Service Projects to Access Course Table in Our Sample Database
9.14.1 Create a New Java Web Application Project WebServiceCourseApp
9.14.2 Create a New Java SOAP-Based Web Service Project WebServiceCourse
9.14.3 The Organization of Web Service Operations
9.14.4 Create and Build Web Service Operations
9.14.4.1 Create and Build the Web Operation QueryCourseID()
9.14.4.2 Build and Run the Web Service to Test the CourseID Query Function
9.14.4.3 Create and Build the Web Operation QueryCourse()
9.14.4.4 Create and Build the Web Operation InsertCourse()
9.14.4.5 Create and Build the Web Operation UpdateCourse()
9.14.4.6 Create and Build the Web Operation DeleteCourse()
9.15 Build Windows-Based Project to Consume the Web Service Project
9.15.1 Develop the Codes to Query Course Information from our Web Service Project
9.15.2 Build Codes for the Select Button Event Handler to Query CourseIDs
9.15.3 Build Codes for the CourseListValueChanged() Method to Get Course Details
9.15.4 Build Codes for the Insert Button Event Handler to Insert a New Course
9.15.5 Build Codes for the Update Button Method to Update Course Records
9.15.6 Build Codes for the Delete Button Method to Delete Course Records
9.16 Build a Web-Based Project to Consume the Web Service Project WebServiceCourse
9.16.1 Create a Web-Based Client Project WebClientCourse
9.16.2 Add a Web Service Reference to Our Web-Based Client Project
9.16.3 Modify the Course.jsp Page
9.16.4 Build the Transaction JSP File CourseProcess.jsp
9.16.5 Build the Java Bean Class File CourseQuery.java
9.16.6 Build and Run Our Client Project to Query Course Record via Our Web Service
9.16.7 Build Our Client Project to Insert New Course Records via Our Web Service
9.16.8 Build Our Client Project to Update Course Records via Our Web Service
9.16.9 Build Our Client Project to Delete Course Records via Our Web Service
9.17 Chapter Summary
Appendices
Appendix A: Install and Configure SQL Server 2019 Express Database and SQL Server Management Studio
Install SQL Server 2019 Express Database
Install SQL Server Management Studio
Configure and Setup SQL Server 2019 Express Connection Parameters
Appendix B: Download and Install JDK 14 and Apache NetBeans 12
Appendix C: Download and Install DevExpress .NET UI Controls
Appendix D: How to Use Sample Database
Appendix E: Data Type Mappings Between SQL Statements and Java Applications
Appendix F: Download and Install Java JDK 8
Appendix G: Download and Install JDBC 4.2
Appendix H: Download and Install NetBeans IDE 8.2 and Glassfish Server
Appendix I: Modify the HTTP Port Number for Tomcat Server
Index