CS计算机代考程序代写 SQL database Java JDBC DTA (M) Database Theory & Applications

DTA (M) Database Theory & Applications

Lab 8 Tasks

Before Starting
 Step 1, read: ‘Java Programming with SQL Self-Study Material.ppt’

 Step 2: We are using Eclipse in the COSE Remote Desktop, thus, store the Eclipse Workspace in

your personal drive ‘H’ or ‘M’ (if you are using the COSE Desktop), or locally to your PC/Laptop.

 Step 3: Download to your personal drive the JDBC/PostgreSQL driver from the Moodle link.

 Step 4: Set off!

Task 1: Connect to SQL Server via Java (JBDC)
Note: The source code for Task 1 is in file: SQLConnectionExample.java

The basic steps for connecting a Java program to a SQL server are:

 Establish a Connection

 Create JDBC Statements

 Execute SQL Statements

 Get SQL ResultSet

 Close Statements

 Close Connections

Let’s start with establishing a JDBC connection. We need a specific Connection string to invoke the

Database server/service, which is for Remote access:

jdbc:postgresql://socs-db.dcs.gla.ac.uk:5432/

or , for your local access (i.e., you have your local database; not connected to COSE):

jdbc:postgresql://localhost:5432/

We also need the username and the password, e.g., Username = lev3_20_GUID, Password = GUID, or the

username and password you had set up during your local installation, e.g., username = “postgres” and

password=”postgres”. Then, we need to instantiate the Connection object to connect to the JDBC:

Connection connection = DriverManager.getConnection(connectionString,

username, password);

In the following Java Code 1, we are trying to connect to the Database server. If the connection is succesful,

i.e., the connection object is instantiated (not NULL), then we get the message ‘Controlling your

database…”’. Otherwise, ‘Failed to establish connection!’. In the successul case, we start off playing

around with SQL statements!

DTA (M) Database Theory & Applications

Java Code 1: Establishing JDBC Connection.

public static void main(String args[]) {
Connection c = null;

try {
Class.forName(“org.postgresql.Driver”);
} catch (ClassNotFoundException e) {

System.out.println(“Could not find JDBC Driver”);
e.printStackTrace();
return;
}
Connection connection = null;

try {
connection = DriverManager.getConnection(“jdbc:postgresql://socs-db.dcs.gla.ac.uk:5432/”,
“lev3_20_GUID”, “GUID”);
} catch (SQLException e) {

System.out.println(“Connection Failed!”);
e.printStackTrace();
return;
}
if (connection != null) {
System.out.println(“Controlling your database…”);
}

else {
System.out.println(“Failed to establish connection!”);
}

}

DTA (M) Database Theory & Applications

Task 2: Manage your Database via Java
Note: The source code for Task 2 is in file: SQLCREATEExample.java

Task 2.1: Firstly, create a table with name: Tobedeleted with an attribute Name as Integer. Then, drop that

table. You are advised to write the SQL statement and then copy & paste this SQL statement in the Java

class.

SQL CREATE/DROP STATEMENTS:

CREATE TABLE Tobedeleted(name integer)

DROP TABLE IF EXISTS Tobedeleted

The Java Code 2 is provided; experiment with other ‘fictitious’ tables and then drop them out of your

database. Check also in the pgAdmin4 for the new created table.

CREATE the table…

Java Code 2 CREATE a Table in JDBC

if (connection != null) {

try {

System.out.println(“Controlling your database…”);

Statement statement = connection.createStatement();

String sqlString = “CREATE TABLE Tobedeleted(name integer)”;

statement.executeUpdate(sqlString);

}catch (SQLException e) {

e.printStackTrace();}

// try-catch exception//try

}//end of create Table

DTA (M) Database Theory & Applications

…and then DROP the table as shown in Java Code 3.

Java Code 3 DROP a Table in JDBC

Task 2.2: Now, create again a table with name: Tobedeleted with an attribute Name as Integer. Then, alter

the table via Java. That is, we add the new column Surname which is Varchar(50). In turn, we drop this

column, while we rename the Name attribute to NameID. The SQL statements for this task are:

CREATE TABLE Tobedeleted(name integer)

ALTER TABLE Tobedeleted ADD COLUMN surname VARCHAR(50)

ALTER TABLE Tobedeleted DROP COLUMN surname

ALTER TABLE Tobedeleted RENAME name TO nameid

The Java Code 4 is then:

if (connection != null) {

try {

System.out.println(“Controlling your database…”);

Statement statement = connection.createStatement();

String sqlString = “DROP TABLE IF EXISTS Tobedeleted”;

statement.executeUpdate(sqlString);

}catch (SQLException e) {

e.printStackTrace();}

// try-catch exception//try

}//end of drop experiment

DTA (M) Database Theory & Applications

Java Code 4 ALTER statements in JDBC

Task 2.3: Your turn now! Add a Primary Key constraint to the table via Java by setting the NameID as the

primary key. The SQL statement is:

ALTER TABLE Tobedeleted ADD CONSTRAINT CPK PRIMARY KEY (nameid)

if (connection != null) {

try {

//ALTERATIONS…
sqlString = “CREATE TABLE Tobedeleted(name integer)”;
statement.executeUpdate(sqlString);

String sql = “ALTER TABLE Tobedeleted ADD COLUMN surname VARCHAR(50)”;
statement.executeUpdate(sql);

sql = “ALTER TABLE Tobedeleted DROP COLUMN surname”;
statement.executeUpdate(sql);

sql = “ALTER TABLE Tobedeleted RENAME name TO nameid”;

statement.executeUpdate(sql);

}catch (SQLException e) {

e.printStackTrace();}

// try-catch exception//try

}//alter experiment—go with the rest alterations…

DTA (M) Database Theory & Applications

Task 3: Insert, Delete & Update your Database via Java
Note: The source code for Task 3 is in file: SQLINSERTExample.java

Task 3.1: Create and insert tuples in the table Owner: Owner(ownerid, name, phone), with ownerid as

Integer, name and phone as Strings. The corresponding SQL statements are provided:

INSERT INTO OWNER(ownerid, name, phone) VALUES(101, ‘John’,

‘0131223454’)

INSERT INTO OWNER(ownerid, name, phone) VALUES(102, ‘Tony’,

‘0442877454’)

If both owners have been successfully added, you will be getting the message:

Controlling your database…
Owner is registered
Owner is registered

In Java, we obtain the following Java Code 5:

Java Code 5 INSERT in JDBC

//INSERT
Statement statement;
int status;
statement = connection.createStatement();

String SQLInsert =
“INSERT INTO OWNER(ownerid, name, phone) VALUES(101, ‘John’, ‘0131223454’)”;

//invoke executeUpdate to insert the tuple
status = statement.executeUpdate(SQLInsert);

//check the insertion
if (status==1)
System.out.println(“Owner is registered”);
else
System.out.println(“No insertion completed”);

SQLInsert =
“INSERT INTO OWNER(ownerid, name, phone) VALUES(102, ‘Tony’, ‘0442877454’)”;

//invoke executeUpdate to insert the second tuple
status = statement.executeUpdate(SQLInsert);
if (status==1)
System.out.println(“Owner is registered”);
else
System.out.println(“No insertion completed”);

DTA (M) Database Theory & Applications

Task 3.2: Try to insert the following tuple in the table Owner as follows:

INSERT INTO OWNER(ownerid, name, phone) VALUES(101, ‘Chris’,

‘01413307252’)

Which is the output of your Java program? Do we violate any constraint? Can you explain the provided

message from the Database server?

We are expecting to get the message:

ERROR: duplicate key value violates unique constraint “owner_pkey”
Detail: Key (ownerid)=(101) already exists.

Task 3.3: Update the following tuple(s) in our database based on the SQL UPDATE statement via your

Java program:

UPDATE OWNER SET phone = ‘01413307252’ WHERE ownerid = 101

In Java, we obtain the following Java Code 6:

Java Code 6 UPDATE in JDBC

Task 3.4: Write the Java code to execute the following tuple update and report the output of your Java

program.

UPDATE OWNER SET phone = ‘01413307252’ WHERE ownerid = 300

Which is the output of your Java program? Do we violate any constraint? Can you explain the provided

message from the Database server?

We are expecting to get the message: ‘No Update Done’

String SQLUpdate = “UPDATE OWNER SET phone = ‘01413307252’ WHERE ownerid = 101”;
//invoke executeUpdate to execute the update
status = statement.executeUpdate(SQLUpdate);

// Check whether an update was made
if (status==1)
System.out.println(“Owner’s phone update succeeded”);
else

System.out.println(“No Update Done”);

DTA (M) Database Theory & Applications

Task 3.5: Let us now delete the following tuples and report on the number of tuples deleted in our Java

program. Use the method: statement.executeUpdate to execute a DELETE SQL statement. The

deletion sequence is the following:

DELETE FROM OWNER WHERE ownerid = 101

DELETE FROM OWNER WHERE ownerid = 102

DELETE FROM OWNER WHERE ownerid = 101

DELETE FROM OWNER WHERE ownerid = 300

Java Code 7 DELETE in JDBC

Task 4: Batch Processing
Note: The source code for Task 3 is in file: SQLBatchExample.java

Idea: Batch Processing groups together related SQL statements into a batch and submit them with one

call/invocation to the database server. The benefit of the batch processing is that it reduces the amount of

communication overhead! The steps for batch processing are:

 Step1: addBatch() method of Statement is invoked to add individual statements to the batch.

 Step 2: executeBatch() is invoked to start the execution of all the statements grouped together.

Note: executeBatch() returns an array of integers; each array element represents the counter for the

respective statement.

You can remove all the statements added with the addBatch() by invoking clearBatch(). In practise now!

Task 4.1: Let’s write a Java code that inserts ten (10) owners in the table Owner as follows (INSERT SQL):

String SQLDelete = “”;
SQLDelete = “DELETE FROM OWNER WHERE ownerid = 101”;
status = statement.executeUpdate(SQLDelete);
System.out.println(“Deletion of owner 101: “+status);

SQLDelete = “DELETE FROM OWNER WHERE ownerid = 102”;
status = statement.executeUpdate(SQLDelete);
System.out.println(“Deletion of owner 102: “+status);

SQLDelete = “DELETE FROM OWNER WHERE ownerid = 101”;
status = statement.executeUpdate(SQLDelete);
System.out.println(“Deletion of owner 101: “+status);

SQLDelete = “DELETE FROM OWNER WHERE ownerid = 300”;
status = statement.executeUpdate(SQLDelete);

System.out.println(“Deletion of owner 300: “+status);

DTA (M) Database Theory & Applications

INSERT INTO OWNER(ownerid, name, phone) VALUES(500+i, ‘Name-i’, ‘0141’)

with i = 1,…,10. The corresponding source is shown in Java Code 8:

Java Code 8 BATCH Statements in JDBC

You are expecting to get the following:

Controlling your database…
SQL INSERT STATEMENT: INSERT INTO OWNER(ownerid, name, phone) VALUES(501, ‘Name-1’,
‘0141’);
SQL INSERT STATEMENT: INSERT INTO OWNER(ownerid, name, phone) VALUES(502, ‘Name-2’,
‘0141’);
SQL INSERT STATEMENT: INSERT INTO OWNER(ownerid, name, phone) VALUES(503, ‘Name-3’,
‘0141’);
SQL INSERT STATEMENT: INSERT INTO OWNER(ownerid, name, phone) VALUES(504, ‘Name-4’,
‘0141’);
SQL INSERT STATEMENT: INSERT INTO OWNER(ownerid, name, phone) VALUES(505, ‘Name-5’,
‘0141’);
SQL INSERT STATEMENT: INSERT INTO OWNER(ownerid, name, phone) VALUES(506, ‘Name-6’,
‘0141’);
SQL INSERT STATEMENT: INSERT INTO OWNER(ownerid, name, phone) VALUES(507, ‘Name-7’,
‘0141’);
SQL INSERT STATEMENT: INSERT INTO OWNER(ownerid, name, phone) VALUES(508, ‘Name-8’,
‘0141’);
SQL INSERT STATEMENT: INSERT INTO OWNER(ownerid, name, phone) VALUES(509, ‘Name-9’,
‘0141’);
SQL Statement 0 status: 1
SQL Statement 1 status: 1
SQL Statement 2 status: 1
SQL Statement 3 status: 1
SQL Statement 4 status: 1

//INSERT
Statement statement;
statement = connection.createStatement();

//make a loop for all ten statements
for (int i = 1; i < 10; i++){ int ID = 500+i; String insertion = "INSERT INTO OWNER(ownerid, name, phone) VALUES("+ID+", 'Name- "+i+"', '0141');"; System.out.println("SQL INSERT STATEMENT: "+ insertion); //add the statement to the batch statement.addBatch(insertion); }//for-loop //check all the statements individually int[] success = statement.executeBatch(); for(int i=0; i < success.length; i++){ System.out.println("SQL Statement "+i+" status: "+success[i]); }//just a check statement.close(); DTA (M) Database Theory & Applications SQL Statement 5 status: 1 SQL Statement 6 status: 1 SQL Statement 7 status: 1 SQL Statement 8 status: 1 Task 4.2: Let’s now write a Java code that deletes these ten owners in the table Owner with ownerid from 1 to 10 in a batch execution mode. Note: The transaction control is performed by the Connection object. The default mode is auto-commit, i.e., each SQL statement is treated as an individual transaction to be committed. To turn off the auto- commit mode, we set: connection.setAutoCommit(false); In this case, no SQL statement will be committed until we explicit invoke: connection.commit(); To turn on auto-commit, we set connection.setAutoCommit(true); At this point, after invoking commit(), all changes done by the SQL statements will be made permanent in the database. Check with the pgAdmin4 the results. Java Code 9 Batch deletions in JDBC And this could be our output: SQL DELETION STATEMENT: DELETE FROM OWNER WHERE ownerid = 501 SQL DELETION STATEMENT: DELETE FROM OWNER WHERE ownerid = 502 SQL DELETION STATEMENT: DELETE FROM OWNER WHERE ownerid = 503 SQL DELETION STATEMENT: DELETE FROM OWNER WHERE ownerid = 504 SQL DELETION STATEMENT: DELETE FROM OWNER WHERE ownerid = 505 SQL DELETION STATEMENT: DELETE FROM OWNER WHERE ownerid = 506 SQL DELETION STATEMENT: DELETE FROM OWNER WHERE ownerid = 507 SQL DELETION STATEMENT: DELETE FROM OWNER WHERE ownerid = 508 SQL DELETION STATEMENT: DELETE FROM OWNER WHERE ownerid = 509 SQL Deletion 0 status: 1 connection.setAutoCommit(false); //make a loop for all ten statements for (int i = 1; i < 10; i++){ int ID = 500+i; String deletion = "DELETE FROM OWNER WHERE ownerid = "+ID; System.out.println("SQL DELETION STATEMENT: "+ deletion); //add the statement to the batch statement.addBatch(deletion); }//for-loop //check all the statements individually int[] success = statement.executeBatch(); for(int i=0; i < success.length; i++){ System.out.println("SQL Deletion "+i+" status: "+success[i]); }//just a check //I am sure to delete these owners, thus, committing my deletions! connection.commit(); statement.close(); DTA (M) Database Theory & Applications SQL Deletion 1 status: 1 SQL Deletion 2 status: 1 SQL Deletion 3 status: 1 SQL Deletion 4 status: 1 SQL Deletion 5 status: 1 SQL Deletion 6 status: 1 SQL Deletion 7 status: 1 SQL Deletion 8 status: 1 Here are some useful links for the JDBC: • JDBC Data Access API – JDBC Technology Homepage – http://java.sun.com/products/jdbc/index.html • JDBC Database Access – The Java Tutorial – http://java.sun.com/docs/books/tutorial/jdbc/index.html • JDBC Documentation – http://java.sun.com/j2se/1.4.2/docs/guide/jdbc/index.html • java.sql package – http://java.sun.com/j2se/1.4.2/docs/api/java/sql/package-summary.html • JDBC Technology Guide: Getting Started – http://java.sun.com/j2se/1.4.2/docs/guide/jdbc/getstart/GettingStartedTOC.fm.html • JDBC API Tutorial and Reference (book) – http://java.sun.com/docs/books/jdbc/ http://java.sun.com/products/jdbc/index.html http://java.sun.com/docs/books/tutorial/jdbc/index.html http://java.sun.com/j2se/1.4.2/docs/guide/jdbc/index.html http://java.sun.com/j2se/1.4.2/docs/api/java/sql/package-summary.html http://java.sun.com/j2se/1.4.2/docs/guide/jdbc/getstart/GettingStartedTOC.fm.html http://java.sun.com/docs/books/jdbc/