JDBC Crud Operations: Group 3

JDBC CRUD OPERATIONS
GROUP 3:
ALZATE
DELUNA
OSABEL
PAYAT
SUMANPAN
CRUD
CRUD IS THE ACRONYM FOR THE FOLLOWING
FOUR OPERATIONS.
• C- CREATION/INSERTION
• R- RETRIEVAL
• U- UPDATION
• D- DELETION
PREREQUISITES
• JDK
• MYSQL
• JDBC DRIVER FOR MYSQL
SAMPLE DATABASE
JDBC INTERFACES AND
CLASSES
JDBC INTERFACES AND CLASSES
•DriverManager: this class is used to register driver for a specific

database type (e.g. MySQ) and to establish a database connection with the
server via its getConnection() method.
•Connection: this interface represents an established database connection
(session) from which we can create statements to execute queries and
retrieve results, get metadata about the database, close connection, etc.
JDBC INTERFACES AND CLASSES (CONT.)
• STATEMENT AND PREPAREDSTATEMENT: THESE INTERFACES ARE USED TO EXECUTE
STATIC SQL QUERY AND PARAMETERIZED SQL QUERY, RESPECTIVELY. STATEMENT IS THE
SUPER INTERFACE OF THE PREPAREDSTATEMENT INTERFACE. THEIR COMMONLY USED
METHODS ARE:
1.BOOLEAN EXECUTE(STRING SQL): EXECUTES A GENERAL SQL STATEMENT. IT
RETURNS TRUE IF THE QUERY RETURNS A RESULTSET, FALSE IF THE QUERY RETURNS
AN UPDATE COUNT OR RETURNS NOTHING. THIS METHOD CAN BE USED WITH A
STATEMENT ONLY.
2.INT EXECUTEUPDATE(STRING SQL): EXECUTES AN INSERT, UPDATE OR DELETE
STATEMENT AND RETURNS AN UPDATE ACCOUNT INDICATING NUMBER OF ROWS
AFFECTED (E.G. 1 ROW INSERTED, OR 2 ROWS UPDATED, OR 0 ROWS AFFECTED).
3.RESULTSET EXECUTEQUERY(STRING SQL): EXECUTES A SELECT STATEMENT AND
RETURNS A RESULTSET OBJECT WHICH CONTAINS RESULTS RETURNED BY THE QUERY.
JDBC INTERFACES AND CLASSES (CONT.)
• A PREPARED STATEMENT IS ONE THAT CONTAINS PLACEHOLDERS (IN

FORM QUESTION MARKS ?) FOR DYNAMIC VALUES WILL BE SET AT
RUNTIME. FOR EXAMPLE:
SELECT * FROM USERS WHERE USER_ID=?
• HERE THE VALUE OF USER_ID IS PARAMETERIZED BY A QUESTION
MARK AND WILL BE SET BY ONE OF THE SETXXX() METHODS FROM
THE PREPAREDSTATEMENT INTERFACE, E.G. SETINT(INT INDEX,
INT VALUE).
CONNECTING TO DATABASE
CONNECTING TO THE DATABASE
Once the connection was established, we have a Connection object which can be used to create statements in
order to execute SQL queries. In the above code, we have to close the connection explicitly after finish working
with the database:
CONNECTING TO THE DATABASE(CONT.)
Using Try-Catch statement to automatically close connection:

JDBC EXECUTE INSERT
STATEMENT
JDBC EXECUTE INSERT STATEMENT
INSERT A NEW RECORD INTO THE TABLE USERS WITH FOLLOWING DETAILS:
• USERNAME: BILL
• PASSWORD: SECRETPASS
• FULLNAME: BILL GATES
• EMAIL: BILL.GATES@MICROSOFT.COM
JDBC EXECUTE INSERT STATEMENT(CONT.)
• ON THE CODE SHOWN EARLIER, WE CREATE A PARAMETERIZED SQL INSERT
STATEMENT AND CREATE A PREPAREDSTATEMENT FROM THE CONNECTION
OBJECT. TO SET VALUES FOR THE PARAMETERS IN THE INSERT STATEMENT,
WE USE THE PREPAREDSTATEMENT‘S SETSTRING() METHODS BECAUSE ALL
THESE COLUMNS IN THE TABLE USERS ARE OF TYPE VARCHAR WHICH IS
TRANSLATED TO STRING TYPE IN JAVA.
• THE PREPAREDSTATEMENT INTERFACE PROVIDES VARIOUS SETXXX()
METHODS CORRESPONDING TO EACH DATA TYPE, FOR EXAMPLE:
• SETBOOLEAN(INT PARAMETERINDEX, BOOLEAN X)
• SETDATE(INT PARAMETERINDEX, DATE X)
• SETFLOAT(INT PARAMETERINDEX, FLOAT X)
JDBC EXECUTE INSERT STATEMENT(CONT.)
• FINALLY WE CALL THE PREPAREDSTATEMENT’S EXECUTEUPDATE() METHOD
TO EXECUTE THE INSERT STATEMENT. THIS METHOD RETURNS AN UPDATE
COUNT INDICATING HOW MANY ROWS IN THE TABLE WERE AFFECTED BY
THE QUERY, SO CHECKING THIS RETURN VALUE IS NECESSARY TO ENSURE
THE QUERY WAS EXECUTED SUCCESSFULLY. IN THIS CASE,
EXECUTEUPDATE() METHOD SHOULD RETURN 1 TO INDICATE ONE RECORD
WAS INSERTED.
JDBC EXECUTE SELECT
STATEMENT
JDBC EXECUTE SELECT STATEMENT
The following code snippet queries all records from the Users table and print out details
for each record:
JDBC EXECUTE SELECT STATEMENT(CONT.)
Output:
• BECAUSE THE SQL SELECT QUERY HERE IS STATIC SO WE JUST CREATE A

STATEMENT OBJECT FROM THE CONNECTION. THE WHILE LOOP ITERATES
OVER THE ROWS CONTAINED IN THE RESULT SET BY REPEATEDLY CHECKING
RETURN VALUE OF THE RESULTSET’S NEXT() METHOD. THE NEXT() METHOD
MOVES A CURSOR FORWARD IN THE RESULT SET TO CHECK IF THERE IS ANY
REMAINING RECORD. FOR EACH ITERATION, THE RESULT SET CONTAINS
DATA FOR THE CURRENT ROW, AND WE USE THE RESULTSET’S
GETXXX(COLUMN INDEX/COLUMN NAME) METHOD TO RETRIEVE VALUE OF A
SPECIFIC COLUMN IN THE CURRENT ROW, FOR EXAMPLE THIS STATEMENT:
JDBC EXECUTE SELECT STATEMENT(CONT.)
• THE PREVIOUS LINE RETRIEVES VALUE OF THE SECOND COLUMN IN THE
CURRENT ROW, WHICH IS THE USERNAME FIELD. THE VALUE IS CASTED TO A
STRING BECAUSE WE KNOW THAT THE USERNAME FIELD IS OF TYPE VARCHAR
BASED ON THE DATABASE SCHEMA MENTIONED PREVIOUSLY. KEEP IN MIND
THAT THE COLUMN INDEX HERE IS 1-BASED, THE FIRST COLUMN WILL BE AT
INDEX 1, THE SECOND AT INDEX 2, AND SO ON. IF YOU ARE NOT SURE OR
DON’T KNOW EXACTLY THE INDEX OF COLUMN, SO PASSING A COLUMN NAME
WOULD BE USEFUL:
For other data types, the ResultSet provide appropriate getter methods:
• getString()
• getInt()
• getFloat()
• getDate()
• getTimestamp()
JDBC EXECUTE UPDATE
STATEMENT
JDBC EXECUTING UPDATE STATEMENT
The following code snippet will update the record of “Bill Gates” as we inserted previously:
This code looks very similar to the INSERT code above, except the query type is UPDATE.
JDBC EXECUTE DELETE
STATEMENT
JDBC EXECUTE DELETE STATEMENT
The following code snippet will delete a record whose username field contains “bill”:
KEYPOINTS
KEYPOINTS TO REMEMBER:
• USING A STATEMENT FOR A STATIC SQL QUERY.
• USING A PREPAREDSTATEMENT FOR A PARAMETERIZED SQL QUERY AND
USING SETXXX() METHODS TO SET VALUES FOR THE PARAMETERS.
• USING EXECUTE() METHOD TO EXECUTE GENERAL QUERY.
• USING EXECUTEUPDATE() METHOD TO EXECUTE INSERT, UPDATE OR
DELETE QUERY
• USING EXECUTEQUERY() METHOD TO EXECUTE SELECT QUERY.
• USING A RESULTSET TO ITERATE OVER ROWS RETURNED FROM A
SELECT QUERY, USING ITS NEXT() METHOD TO ADVANCE TO NEXT ROW
IN THE RESULT SET, AND USING GETXXX() METHODS TO RETRIEVE
VALUES OF COLUMNS.
END

JDBC Crud Operations: Group 3

Uploaded by

Copyright:

Available Formats

JDBC Crud Operations: Group 3

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

JDBC Crud Operations: Group 3

Uploaded by

Copyright:

Available Formats

JDBC CRUD OPERATIONS

•DriverManager: this class is used to register driver for a specific

• A PREPARED STATEMENT IS ONE THAT CONTAINS PLACEHOLDERS (IN

Using Try-Catch statement to automatically close connection:

• BECAUSE THE SQL SELECT QUERY HERE IS STATIC SO WE JUST CREATE A

You might also like