Homework answers / question archive / Two-Tier Client-Server Application Development With MySQL and JDBC” Objectives: To develop a two-tier Java based client-server application interacting with a MySQL database utilizing JDBC for the connectivity

Two-Tier Client-Server Application Development With MySQL and JDBC” Objectives: To develop a two-tier Java based client-server application interacting with a MySQL database utilizing JDBC for the connectivity

Computer Science

Share With

---

Two-Tier Client-Server Application Development With MySQL and JDBC”

Objectives: To develop a two-tier Java based client-server application interacting with a MySQL database utilizing JDBC for the connectivity. This project is designed to give you some experience using the various features of JDBC and its interaction with a MySQL DB Server environment.

Description: In this assignment you will develop a Java-based GUI front-end (client-side) application that will connect to your MySQL server via JDBC.

You are to develop a Java application that will allow clients (the end-user) to execute commands against a remote database. You will create a Java GUI-based application front-end that will accept any MySQL DDL or DML command, pass this through a JDBC connection to the MySQL database server, execute the statement and return the results to the client. Note that while technically your application must be able to handle any DDL or DML command, we won’t actually use all of the commands available in these sublanguages. For one thing, it would be quite rare to allow a client to create a database or a table within a database. Note too, that the only DML command that uses the

executeQuery() method of JDBC is the Select command, all other DML and DDL commands

utilize executeUpdate(). Some screen shots of what your Java GUI front-end should look like are shown below. Basically, this GUI is an extension of the GUI that was developed in the lecture notes and is available on WebCourses as DisplayQueryResults.java. Your Java application must give the user the ability to execute any SQL DDL or DML command for which the user has the correct permissions. User information for connections will be maintained in properties files, but the user must supply their username and password (for their MySQL server account) via the GUI for

verification purposes (more later). You will be able to start multiple instances of your Java application

and allow different clients to connect simultaneously to the MySQL DB sever, since the default

number of connections is set at 151 (See your Workbench options file under the networking tab). In

addition to the client interactions with your application, a background (business logic) transaction

logging operation will occur which keeps a running total of the number of queries and the number of

updates that have occurred via the user application (aggregate over all users). This is a separate

database (i.e., a completely different database than any to which a client user can connect), that the

application will connect to using root user privileges in a separate properties file. This separate

properties file is not accessible by any end user. Each user operation will cause the application to

make this connection and update the operational logging database table. More details on this aspect

of the application are shown below and will be covered in the Q&A sessions.

Once you’ve created your application, you will execute a sequence of DML and DDL commands and

illustrate the output from each in your GUI for two different users. For this project you will create,

in addition to the root user, a client user with limited permissions on the database (see below). The

root user is assumed to have all permissions on the database, any command they issue will be

executed. The client user will be far more restricted.

CNT 4714 – Project Three – Spring 2023

Page 2

Restrictions:

1.Your source files should begin with comments containing the following information:

/*

Name:

Course: CNT 4714 Spring 2023

Assignment title: Project 3 – A Two-tier Client-Server Application

Date: March 9, 2023

Class:

*/

2. Your application must provide a user interface, similar to the one shown below, that will allow

any user the ability to connect to any database via properties files. Your application must

verify that the user credentials (username and password) entered via the interface match with

the user credentials found in the properties file that was selected via the interface. If the

credentials do no match, then no connection is established.

3. Non-query commands should display a message to the user regarding the status of the

executed command (see below).

References for this assignment:

Notes: Lecture Notes for MySQL and JDBC.

Input Specification:

The first step in this assignment is to login to the MySQL Workbench as the root user and execute/run

the script to create and populate the backend database. This script is available on the assignment page

and is named “project3dbscript.sql”. This script creates a database named project3. You

can use the MySQL Workbench for this step, or the command line whichever you prefer.

The second step is to create authorizations for a client user (in addition to the root user) named

client. By default your root user has all permissions on the project3 database. Use either SQL

Grant statements from the command line or the MySQL Workbench (see separate document for

details on how to accomplish this task) to check and set permissions for the client as follows:

Register the new user named client (assign them the password client – ignore the MySQL warning

on weak password setting) and assign to this user only select privileges on the project3 schema.

The third step is to create the operationslog database using the project3operationslog.sql

script. This script file is also available on WebCourses.

Output Specification:

There are three parts for the output for this project. Part 1 is to provide screen shots from your

application which clearly show the complete query/command expression and results for each of the

commands that appear in the script named: project3rootuserscript.sql available on the

course website. There are eight different commands in this script and some of the commands will

have more than one output capture (see below). Part 2 is to provide screen shots from your

application which clearly show the complete query/command expression and results for each of the

commands that appear in the script named: project3clientuserscript.sql available on

the course website. There are three different commands in this script and some of the commands will

have more than one output capture (see below). To produce your final output, first recreate the

Page 3

database, then run the root user commands followed by the client commands in script order

within each script file.

Deliverables:

1. All of the .java files associated with your application.

2. All 17 screenshots from the execution of the commands specified in the

project3rootuserscript.sql script.

3. All 11 screenshots from the execution of the commands specified in the project3clientuserscript.sql script.

4. A screenshot showing the final state of the operationscount table after executing the command select * from operationscount; once both the root user and client

user command script files have been completely executed.

5. A screenshot showing a mismatch between the user-entered credentials and the selected

properties file resulting in no connection to the database being established.

All should be uploaded to WebCourses no later than 11:59pm Thursday March 9, 2023. Be sure

to clearly label each screen shot. Use the convention: RootCommand1, RootCommand2A,

RootCommand2B, and so on. Similarly for ClientCommand1, ClientCommand2A, and so on.

Details:

Shown on the next page is a screen shot of the initial GUI. Notice that there is a single drop-down

list for selecting the properties file that will be used to make the user connection. The user credentials

along with the JDBC driver and database URL will be specified in these files. The client must enter

only their user credentials (username and password) through the GUI. Your application must verify

that the user-entered credentials match those in the specified properties file before making a

connection to the database. If the user entered credentials do not match those in the specified

properties file, a message will be displayed to the user and no connection to the database will be established.

You should provide buttons for the user to clear the command window as well as the result window.

The status of the connection should be returned to the GUI and displayed in the connection area.

The output of all SQL commands should be returned to the SQL Execution Result window. Please note that only single SQL commands can be executed via this application (will not execute scripts of

commands). We will also not go to the effort of making the application display the results of MySQLspecific commands. (When a MySQL-specific command is executed, the SQL Execution Result window does not need to display any results, if you wanted to you could display the line “MySQL command executed” in the results window, but this is not required.)

As each user command is executed (only successful commands – some of the client command will

not be successful) the operationscount table in the operationslog database must be updated by your

application. Each query and each update will be logged (counted) separately. Your application must

obtain a connection to the operationslog database and perform the update with root user credentials.

Only successful operations will be logged – any transaction erroring will not increment any counter.

These operations are invisible to the end user (regardless of who the user is, including root users).

The application must connect to the operationslog database using a properties file which contains all

necessary connection information.

Page 4

Note that for non-query DML and DDL commands, before and after screen shots must be taken to

illustrate the basic effect of the command. See pages 8-9 for an illustration of this.

The remainder of the document illustrates the application at various phases during execution.

The GUI areas defined.

Button to establish

connection to database Button to clear

the command

window

Button to

execute the

command in

the window

Drop down list for specifying

the properties file to load.

SQL Execution

Results Window

SQL Command

Window

DB Connection area

Status of connection

window

User input areas for

username and password.

Button to clear

the results

window

Page 5

Screen shot illustrating an initial connection.

Connection established to

selected database URL

Page 6

 Illustrating the drop-down list of possible properties files that could be selected.

User has connected to a database and issued a select command. Results are displayed in the SQL

Execution window.

Note the

metadata. Your

application must

print this for the

user.

Drop down menu for various

properties files that could be selected.

You can just use the

root.properties and

client.properties for this

project. But any size list is possible.

Page 7

A more complicated query:

When the user makes a mistake entering a SQL command:

Page 8

The following three screen shots illustrate that your application should be able to handle non-query

commands from the users.

Before screen shot of a subset of the riders relation:

Page 9

Insert command issued:

After screen shot of subset of riders relation after insert command was issued:

Page 10

Screen shot illustrating the client user issuing a select command.

Screen shot illustrating the client user issuing a command for which they do not have permission:

Page 11

The following screenshot illustrates how the user-entered credentials must match those in the selected

properties file in order to establish a connection to the database specified in the properties file.

User entered username

field does not match

with that in the selected

properties file. No

connection is

established. Any

mismatch on username

or password would

generate the same no

connection message.

Page 12

The following screenshot illustrates the operationscount table values after various operations have been completed. This screenshot is taken from a root user account in the MySQL Workbench using the operationslog database. Note that the numbers shown in this screenshot are not the correct numbers that you will see after executing the root user command script followed by the client user command script. This is just an example.

# The client user execution script for Project Three - CNT 4714 - Spring 2023

# all commands assumed to be executed by the client user

# the client user has only selection  privilege on the project3 database schema

#

#Command 1:

#   Query: Which rider won the World Championship - Elite Women in 2021?

select ridername

from racewinners

where racename = 'World Championship - Elite Women' and raceyear = 2021;

#Commands 2A, 2B, and 2C:

#   Delete all the riders from Norway from the riders table.

#   * * * Do a "before" and "after" select * from riders for this command.

#   Note: the before and after select statements will execute, but the delete will

not

#         thus no changes will be reflected in the before and after snapshots.

select * from riders;

delete from riders where nationality = 'Norway';

select * from riders;

#Commands 3A, 3B, and 3C:

#    Update rider Marianne Vos to show number of wins = 245 in the riders table.

# * * Do a "before" and "after" selection on the riders table

#   Note: the before and after select statements will execute, but the delete will

not

#         thus no changes will be reflected in the before and after snapshots.

select * from riders;

update riders set num_pro_wins = 245 where ridername = "Marianne Vos";

select * from riders;

#Command 4:

#   Query: Which rider won the 2021 Tour de France?

select ridername

from racewinners

where racename = "Tour de France" and raceyear = 2021;

#Command 5:

#   How many riders are there?

select count(ridername) as number_of_riders

from riders;

#Command 6:

#   udpating command not valid for the client user

update teams

set registered_nation = "France"

where teamname = "Movistar";

# Command 7:

#   How many races list Ceylin del Carmen Alvarado as the winner?

select count(racename) as Ceylin_listed_as_winner from racewinners where ridername

= 'Ceylin del Carmen Alvarado';
# SQL commands to create and populate the MySQL database for Project Three

# CNT 4714 - Spring 2023

#

# delete the database if it already exists

drop database if exists project3;

# create a new database named project3

create database project3;

# switch to the new database

use project3;

# create the schemas for the four relations in this database

create table riders (

    ridername varchar(30) not null,

    teamname varchar(20) not null,

    nationality varchar(20),

    num_pro_wins integer,

gender varchar(1),

    primary key (ridername)

);

create table teams (

    teamname varchar(20) not null,

    bikename varchar(20) not null,

    registered_nation varchar(20),

    num_riders integer,

    manager varchar(20),

    primary key (teamname)

);

create table bikes (

    bikename varchar(20) not null,

    country_of_origin varchar(20),

    cost integer,

    primary key (bikename)

  );

create table racewinners (

    racename varchar(45) not null,

    raceyear integer,

ridername varchar(30) not null,

    distance integer,

winning_time varchar(8),

    primary key (racename, raceyear)

);

# populate the database tables

insert into riders values ('Andy Schleck', 'Leopard-Trek', 'Luxembourg', 35,'M');

insert into riders values ('Frank Schleck', 'Leopard-Trek','Luxembourg', 28,'M');

insert into riders values ('Fabian Cancellara', 'SaxoBank', 'Switzerland', 58,'M');

insert into riders values ('Stuart OGrady', 'CSC', 'Australia', 20,'M');

insert into riders values ('Jens Voigt', 'SaxoBank','Germany', 38,'M');

insert into riders values ('Alesandro Ballan', 'Lampre', 'Italy', 21,'M');

insert into riders values ('Tom Boonen', 'Quick-Step','Belgium', 67,'M');

insert into riders values ('Mark Cavendish', 'HTC-Columbia', 'Isle of Man',

34,'M');

insert into riders values ('Matti Breschel', 'Cofidis', 'Denmark', 12,'M');

insert into riders values ('Marianne Vos', 'WM3', 'Netherlands', 230, 'F');

insert into riders values ('Sven Nys', 'Landbouwkredit', 'Belgium', 94,'M');

insert into riders values ('Nicolas Roche', 'Cofidis', 'Ireland', 8,'M');

insert into riders values ('Alberto Contador','Astana','Spain',21,'M');

insert into riders values ('Lizzie Armistead', 'Boels-Dolmans','Great Britain', 89,

'F');

insert into riders values ('Niki Sorensen', 'SaxoBank','Norway', 32,'M');

insert into riders values ('Thor Hushovd', 'SaxoBank','Norway',29,'M');

insert into riders values ('George Hincapie', 'BMC', 'USA', 22,'M');

insert into riders values ('Mario Cipolini', 'Acqua & Sapone', 'Italy', 130,'M');

insert into riders values ('Pauline Ferrand','Gitane', 'France',45, 'F');

insert into riders values ('Elisa Borghini','Schenker','Italy',34,'F');

insert into riders values ('Zednek Stybar', 'Quick-Step', 'Czechoslovakia',

40,'M');

insert into riders values ('Johan Museeuw', 'Quick-Step', 'Belgium', 120,'M');

insert into riders values ('Ceylin del Carmen Alvarado', 'Alpecin-Fenix',

'Netherlands', 88, 'F');

insert into riders values ('Fedor den Hertog', 'Acqua & Sapone', 'Netherlands',

20,'M');

insert into riders values ('Peter Post', 'Ti-Raleigh', 'Netherlands', 150,'M');

insert into riders values ('Chris Froome','Sky','Great Britain',23,'M');

insert into riders values ('Dietrich Thurau', 'Ti-Raleigh', 'Germany', 78,'M');

insert into riders values ('Roger deVlaeminck', 'Brooklyn', 'Belgium', 134,'M');

insert into riders values ('Miguel Indurain','BMC','Spain', 76,'M');

insert into riders values ('Phillipe Gilbert','BMC','Belgium',85,'M');

insert into riders values ('Bradley Wiggins', 'Ti-Raleigh','Great Britain',

13,'M');

insert into riders values ('Robbie McKewen','Brooklyn', 'Austalia',55,'M');

insert into riders values ('Peter Sagan','Bora-Hansgrohe','Slovakia',101,'M');

insert into riders values ('Davide van der Poel', 'Jumbo-Visma', 'Alpecin', 0,

'M');

insert into riders values ('Wout van Aert', 'Jumbo-Visma','Belgium',60,'M');

insert into riders values ('Filippo Ganna','Ineos','Italy',25,'M');

insert into riders values ('Mathieu van der Poel','Alpecin','Netherlands',34,'M');

insert into riders values ('Elisa Balsamo','Trek-Segafredo','Italy',20,'F');

insert into teams values ('Quick-Step','Eddy Merckx','Belgium',24,'Patrick

Lafevre');

insert into teams values ('Leopard-Trek', 'Trek','Luxembourg', 20,'Bryan Nygaard');

insert into teams values ('Acqua & Sapone', 'Eddy Merckx', 'Italy', 18,'Felice

Gimondi');

insert into teams values ('UAE', 'Colnago', 'UAE', 29, 'Mauro Gianetti');

insert into teams values ('HTC-Columbia', 'Scott', 'Germany', 28, 'Scott

Sunderland');

insert into teams values ('CSC', 'Cervelo','Denmark', 18,'Sven Nillsen');

insert into teams values ('Astana','Trek','Kazakhstan',19,'Johann Bryneel');

insert into teams values ('WM3','Ridley','Netherlands',15,'Mark Delora');

insert into teams values ('Molteni','Colnago','Italy',23,'Aldo Sassi');

insert into teams values ('Ti-Raleigh', 'Gios','Netherlands',35,'Rene Pijin');

insert into teams values ('Brooklyn', 'Gios', 'Italy', 22, 'Francesco Moser');

insert into teams values ('SaxoBank', 'Look', 'Germany', 23, 'Erik Zabel');

insert into teams values ('BMC', 'BMC', 'Switzerland',24,'Gerben Karstens');

insert into teams values ('Cofidis', 'Look', 'France', 22, 'Marc Sargent');

insert into teams values ('Lampre','Willier','Italy', 24, 'Guiseppe Saronni');

insert into teams values ('Landbouwkredit','Colnago', 'Belgium', 22,'Tomas

Schqvre');

insert into bikes values ('Eddy Merckx','Belgium',9000);

insert into bikes values ('Trek', 'USA', 8500);

insert into bikes values ('Cervelo', 'Canada', 9000);

insert into bikes values ('Colnago','Italy',12000);

insert into bikes values ('Willier', 'Italy', 9800);

insert into bikes values ('Look', 'France', 10000);

insert into bikes values ('Gios', 'Italy', 9800);

insert into bikes values ('BMC','Switzerland',10000);

insert into bikes values ('Scott', 'Germany', 8800);

insert into bikes values ('Ridley','Belgium',10000);

insert into bikes values ('Bianchi','Italy',10000);

insert into racewinners values ('Milan-San Remo',2010,'Mark Cavendish',260,

'7:23:00');

insert into racewinners values ('Paris-Roubaix', 2010,'Fabian Cancellara', 243,

'6:58:12');

insert into racewinners values ('Milan-San Remo', 1972,'Eddy Merckx',252,

'7:05:12');

insert into racewinners values ('Paris-Roubaix',2007,'Fabian Cancellara', 244,

'7:02:13');

insert into racewinners values ('Fleche Wallone', 2004, 'Mario Cipollini', 220,

'6:44:29');

insert into racewinners values ('Fleche Wallone - Feminine', 2014, 'Marianne Vos',

134, '3:22:12');

insert into racewinners values ('Paris-Roubaix', 1972, 'Roger deVlaeminck', 233,

'6:54:22');

insert into racewinners values ('Tour de France', 2010, 'Alberto Contador', 3219,

'73:13:54');

insert into racewinners values ('Liege-Bastogne-Leige', 2008, 'Andy Schlek', 248,

'6:55:12');

insert into racewinners values ('Vuelta Espana', 2008, 'Alberto Contador', 2701,

'78:14:02');

insert into racewinners values ('World Championship - Elite Women', 2021, 'Elisa

Balsamo', 157, '3:52:57');

insert into racewinners values ('Paris-Roubaix', 2008, 'Tom Boonen', 265,

'6:53:33');

insert into racewinners values ('Rund de Flandren', 2008, 'Tom Boonen', 244,

'7:03:48');

insert into racewinners values ('Paris-Roubaix', 2006, 'Johan Museeuw', 254,

'7:12:52');

insert into racewinners values ('Milan-San Remo', 2003, 'Mario Cipollini', 300,

'7:22:19');

insert into racewinners values ('Paris-Roubaix', 1973, 'Roger deVlaeminck', 240,

'7:00:26');

insert into racewinners values ('Paris-Roubaix', 1974, 'Roger deVlaeminck', 244,

'7:05:47');

insert into racewinners values ('Paris Roubaix Femmes', 2021, 'Lizzie Deignan',

116, '2:56:07');

insert into racewinners values ('Fleche Wallone', 2002, 'George Hincapie', 212,

'5:58:47');

insert into racewinners values ('Fleche Wallone - Feminine', 2015, 'Pauline

Ferrand', 145, '3:40:18');

insert into racewinners values ('Telnet Super Prestige', 2023, 'Ceylin del Carmen

Alvarado', 120, '3:10:10');

insert into racewinners values ('Tour de France', 2020, 'Tadej Pogacar', 3484,

'87:20:05');

insert into racewinners values ('Paris-Roubaix', 1971, 'Eddy Merckx', 245,

'6:48:10');

insert into racewinners values ('Tour de France', 1970, 'Eddy Merckx', 3387,

'74:04:51');

insert into racewinners values ('Tour of Netherlands', 1972, 'Fedor den Hertog',

678, '15:48:02');

insert into racewinners values ('Giro Italia', 1970,'Eddy Merckx', 3113,

'82:28:44');

insert into racewinners values ('Amstel Gold', 1999, 'Robbie McKewen', 240,

'6:23:14');

insert into racewinners values ('Ghent-Wevelgem', 2008, 'Johan Musseuw', 230,

'6:14:52');

insert into racewinners values ('Paris-Roubaix', 1970, 'Eddy Merckx', 245,

'6:48:10');

insert into racewinners values ('World Championship - Elite Women', 2015, 'Lizzie

Armistead', 140, '4:10:05');

insert into racewinners values ('World Championship - Elite Women', 2019, 'Annemiek

van Vlueten', 155, '4:22:14');

insert into racewinners values ('GP-E3', 2010, 'Fabian Cancellara', 210,

'5:44:11');

insert into racewinners values ('Rund de Flandren', 2022, 'Mathieu van der Poel',

264, '6:18:30');

insert into racewinners values ('World Cyclocross Championships - Elite Women',

2020, 'Ceylin del Carmen Alvarado', 20, '1:10:22');

insert into racewinners values ('Paris-Roubaix', 1969, 'Eddy Merckx', 248,

'6:53:16');

insert into racewinners values ('Tour de France', 2021, 'Tadej Pogacar', 3414,

'82:56:36');

insert into racewinners values ('Rund de Flandren', 2009, 'Stuart OGrady', 253,

'6:17:29');

insert into racewinners values ('Liege-Bastogne-Liege', 1978, 'Dietrich Thurau',

256, '7:01:00');

insert into racewinners values ('Liege-Bastogne-Liege', 1972, 'Eddy Merckx', 273,

'7:11:47');

# uncomment the following 4 lines if you want to see the results of creating and

populating the database

select * from riders;

select * from teams;

select * from bikes;

select * from racewinners;