java database connectivity jdbc icw lecture 12 errol thompson
Post on 20-Dec-2015
219 views
TRANSCRIPT
![Page 1: Java Database Connectivity JDBC ICW Lecture 12 Errol Thompson](https://reader035.vdocuments.us/reader035/viewer/2022062407/56649d535503460f94a2f042/html5/thumbnails/1.jpg)
Java Database Connectivity JDBC
ICW Lecture 12
Errol Thompson
![Page 2: Java Database Connectivity JDBC ICW Lecture 12 Errol Thompson](https://reader035.vdocuments.us/reader035/viewer/2022062407/56649d535503460f94a2f042/html5/thumbnails/2.jpg)
Last Time
• URLs
• A reminder of Sockets.
• Threads: running processes at the same time on the same computer.
• They can be tricky to use.
![Page 3: Java Database Connectivity JDBC ICW Lecture 12 Errol Thompson](https://reader035.vdocuments.us/reader035/viewer/2022062407/56649d535503460f94a2f042/html5/thumbnails/3.jpg)
This lecture
• Relational databases
• Accessing relational databases from Java using JDBC and SQL
• Manipulating database structure from Java
![Page 4: Java Database Connectivity JDBC ICW Lecture 12 Errol Thompson](https://reader035.vdocuments.us/reader035/viewer/2022062407/56649d535503460f94a2f042/html5/thumbnails/4.jpg)
References
• Java JDBC Documentation http://java.sun.com/javase/6/docs/technotes/guides/jdbc/
• JDBC Basics Tutorial http://java.sun.com/docs/books/tutorial/jdbc/basics/index.html
• JDBC Advanced Tutorial http://java.sun.com/developer/Books/JDBCTutorial/index.html
• mySQL Connector http://dev.mysql.com/downloads/connector/j/3.1.html
• Cay Horstmann (2008) Big Java, 3rd Edition. Wiley.
![Page 5: Java Database Connectivity JDBC ICW Lecture 12 Errol Thompson](https://reader035.vdocuments.us/reader035/viewer/2022062407/56649d535503460f94a2f042/html5/thumbnails/5.jpg)
What is a Relational Database?
![Page 6: Java Database Connectivity JDBC ICW Lecture 12 Errol Thompson](https://reader035.vdocuments.us/reader035/viewer/2022062407/56649d535503460f94a2f042/html5/thumbnails/6.jpg)
Tables and Relationships
Riders
Teams
Events
A rider is a member of a team. A team comprises many riders.
A rider competes in an event. A event has many riders competing.
A table contains many records. Each record has a unique primary key. Tables are linked using the keys.
![Page 7: Java Database Connectivity JDBC ICW Lecture 12 Errol Thompson](https://reader035.vdocuments.us/reader035/viewer/2022062407/56649d535503460f94a2f042/html5/thumbnails/7.jpg)
Accessing a book
• If I want to read a book, what do I need to do?
• What do you need to do to access a file?
• Do these same ideas apply to a database?
![Page 8: Java Database Connectivity JDBC ICW Lecture 12 Errol Thompson](https://reader035.vdocuments.us/reader035/viewer/2022062407/56649d535503460f94a2f042/html5/thumbnails/8.jpg)
To access a relational database
• Need to know where the database is• Need to know the structure of the
database– What tables– What columns and keys
• Use SQL to form queries and to manipulate data
![Page 9: Java Database Connectivity JDBC ICW Lecture 12 Errol Thompson](https://reader035.vdocuments.us/reader035/viewer/2022062407/56649d535503460f94a2f042/html5/thumbnails/9.jpg)
How do you open (connect) to a database?
JDBC Database Access
![Page 10: Java Database Connectivity JDBC ICW Lecture 12 Errol Thompson](https://reader035.vdocuments.us/reader035/viewer/2022062407/56649d535503460f94a2f042/html5/thumbnails/10.jpg)
Accessing a database
• What do we need to be able to do to access a database from our program code?• Obtain access (i.e. Open the database)
• Read data from the database
• Write data to the database
• Relinquish access (i.e. Close the database)
• Have transaction control• Group a set of updates
• Rollback changes if errors occur
![Page 11: Java Database Connectivity JDBC ICW Lecture 12 Errol Thompson](https://reader035.vdocuments.us/reader035/viewer/2022062407/56649d535503460f94a2f042/html5/thumbnails/11.jpg)
Linking to database driver
Set the class path to find the driver
Database DriverWhere do you find driver names?
Should be in database vendor documentation
For driver in assignment: “com.mysql.jdbc.Driver” or"org.gjt.mm.mysql.Driver"
Database URLHow is a database URL constructed?
“jdbc:<vendor sub protocol name>:<vendor specific options or commands>
For mySQL: “jdbc:mysql://<Server URL>/<database name>”
There are variations
![Page 12: Java Database Connectivity JDBC ICW Lecture 12 Errol Thompson](https://reader035.vdocuments.us/reader035/viewer/2022062407/56649d535503460f94a2f042/html5/thumbnails/12.jpg)
Java code
Loading the driver classClass.forName(" com.mysql.jdbc.Driver ");
Get a connection object (open the database connectionConnection con = DriverManager.getConnection(url,
username, password);
Use try {…} catch {…} around these
Methods available on connection objectcon.isClosed() returns true if connection is closed
con.close() closes the connection
![Page 13: Java Database Connectivity JDBC ICW Lecture 12 Errol Thompson](https://reader035.vdocuments.us/reader035/viewer/2022062407/56649d535503460f94a2f042/html5/thumbnails/13.jpg)
How do you obtain data from a database?
JDBC Database Access
![Page 14: Java Database Connectivity JDBC ICW Lecture 12 Errol Thompson](https://reader035.vdocuments.us/reader035/viewer/2022062407/56649d535503460f94a2f042/html5/thumbnails/14.jpg)
Building and executing a query
SQL Select statement – Simple formSelect <fields> from <table> where <condition>
Create a statementStatement <variable> = <connectionObject>.createStatement();PreparedStatement <variable> =
<connectionObject>.prepareStatement(<parameterised query>);<preparedStatementObject>.set<dataType>(<parameter number>,
<value>);
Execute queryResultSet <variable> = <statementObject>.executeQuery(<SQL
Select Statement>);
Remember to use try {…} catch {…}
![Page 15: Java Database Connectivity JDBC ICW Lecture 12 Errol Thompson](https://reader035.vdocuments.us/reader035/viewer/2022062407/56649d535503460f94a2f042/html5/thumbnails/15.jpg)
Retrieving data from result set
Stepping through data records<resutSetObject>.next();
Retrieving individual fields<varaible> = <resutSetObject>.get<DataType>(<column
number>);
<varaible> = <resutSetObject>.get<DataType>(<column name>);
dataType can be int, double, String, Date, etc.
Close the result set<resutSetObject>.close();
![Page 16: Java Database Connectivity JDBC ICW Lecture 12 Errol Thompson](https://reader035.vdocuments.us/reader035/viewer/2022062407/56649d535503460f94a2f042/html5/thumbnails/16.jpg)
How can you write data to a database?
JDBC Database Access
![Page 17: Java Database Connectivity JDBC ICW Lecture 12 Errol Thompson](https://reader035.vdocuments.us/reader035/viewer/2022062407/56649d535503460f94a2f042/html5/thumbnails/17.jpg)
SQL commands
To add dataInsert Into <Table> (<field names>) Values
(<values>)
To modify dataUpdate <Table> Set <Assignments> Where
<condition>
![Page 18: Java Database Connectivity JDBC ICW Lecture 12 Errol Thompson](https://reader035.vdocuments.us/reader035/viewer/2022062407/56649d535503460f94a2f042/html5/thumbnails/18.jpg)
JDBC statement execution
Prepare as for SQL SelectExecute
int <var> = <statementObject>.executeUpdate(<SQL statement>);
Returns number of records changed
<statementObject>.execute(<SQL statement>)Returns true if a result set has been created by SQL
statement
<statementObject>.getResultSet()Returns result set
<statementObject>.getUpdateCount()Returns the number of records updated
![Page 19: Java Database Connectivity JDBC ICW Lecture 12 Errol Thompson](https://reader035.vdocuments.us/reader035/viewer/2022062407/56649d535503460f94a2f042/html5/thumbnails/19.jpg)
How do I handle sets of data from a database in my
program?Scrollable and updatable result sets
![Page 20: Java Database Connectivity JDBC ICW Lecture 12 Errol Thompson](https://reader035.vdocuments.us/reader035/viewer/2022062407/56649d535503460f94a2f042/html5/thumbnails/20.jpg)
Changes to the createStatement
Additional options on the createStatement and prepareStatementType
TYPE_FORWARD_ONLY
TYPE_SCROLL_INSENSITIVE
TYPE_SCROLL_SENSITIVE
ConcurrencyCONCUR_READ_ONLY
CONCUR_UPDATEABLE
![Page 21: Java Database Connectivity JDBC ICW Lecture 12 Errol Thompson](https://reader035.vdocuments.us/reader035/viewer/2022062407/56649d535503460f94a2f042/html5/thumbnails/21.jpg)
Scrollable result set
Step forward and backwardsrs.next()
rs.previous()
rs.relative(n)
Skip to a specific rowrs.absolute()
rs.getRow()
Other methodsfirst, last, beforeFirst, afterLast
isFirst, isLast, isBeforeFirst, isBeforeLast
![Page 22: Java Database Connectivity JDBC ICW Lecture 12 Errol Thompson](https://reader035.vdocuments.us/reader035/viewer/2022062407/56649d535503460f94a2f042/html5/thumbnails/22.jpg)
Updateable result set
Must create or prepare statement with CONCUR_UPDATABLEAble to update field values
updateXxx(column, value)updateRow()
Able to add rowsmoveToInsertRow()updateXxx(column, value)insertRow()moveToCurrentRow()
Able to delete rowsdeleteRow()
![Page 23: Java Database Connectivity JDBC ICW Lecture 12 Errol Thompson](https://reader035.vdocuments.us/reader035/viewer/2022062407/56649d535503460f94a2f042/html5/thumbnails/23.jpg)
How do I ensure that no other program is updating the same
data as my program?Transaction control
![Page 24: Java Database Connectivity JDBC ICW Lecture 12 Errol Thompson](https://reader035.vdocuments.us/reader035/viewer/2022062407/56649d535503460f94a2f042/html5/thumbnails/24.jpg)
What is the issue?
Should we treat a group of database updates as though they were one update?Purchase transaction updates
Product inventory
Sales history
Customer transaction history
What constitutes a complete transaction?
![Page 25: Java Database Connectivity JDBC ICW Lecture 12 Errol Thompson](https://reader035.vdocuments.us/reader035/viewer/2022062407/56649d535503460f94a2f042/html5/thumbnails/25.jpg)
Implementation of transaction control
Switch off automatic commitconn.setAutoCommit(false);
Update the result set
Commit the changesconn.commit();
Oops! Something has gone wrong.conn.rollback();
![Page 26: Java Database Connectivity JDBC ICW Lecture 12 Errol Thompson](https://reader035.vdocuments.us/reader035/viewer/2022062407/56649d535503460f94a2f042/html5/thumbnails/26.jpg)
Why is transaction control associated with the
connection and not the statement?
![Page 27: Java Database Connectivity JDBC ICW Lecture 12 Errol Thompson](https://reader035.vdocuments.us/reader035/viewer/2022062407/56649d535503460f94a2f042/html5/thumbnails/27.jpg)
Batch updates
Improves the performance of update operationsstat.addBatch(command);
stat.executeBatch();
Might also treat the batch as a single transaction
![Page 28: Java Database Connectivity JDBC ICW Lecture 12 Errol Thompson](https://reader035.vdocuments.us/reader035/viewer/2022062407/56649d535503460f94a2f042/html5/thumbnails/28.jpg)
Can I access information about the structure of the
database?Metadata
![Page 29: Java Database Connectivity JDBC ICW Lecture 12 Errol Thompson](https://reader035.vdocuments.us/reader035/viewer/2022062407/56649d535503460f94a2f042/html5/thumbnails/29.jpg)
What is Metadata
Data about the structure of the databaseWhat tables are in the database?
How many columns are in a table?
What columns are in the tables?
What are the data types of the columns?
Metadata is held in a result set like conventional data
![Page 30: Java Database Connectivity JDBC ICW Lecture 12 Errol Thompson](https://reader035.vdocuments.us/reader035/viewer/2022062407/56649d535503460f94a2f042/html5/thumbnails/30.jpg)
SQL data management statements
All that are supported by the database software in use.
IncludesCreate Database
Use <database>
Create Table
Create Relationships
Modify Table
Drop table
Drop Database
![Page 31: Java Database Connectivity JDBC ICW Lecture 12 Errol Thompson](https://reader035.vdocuments.us/reader035/viewer/2022062407/56649d535503460f94a2f042/html5/thumbnails/31.jpg)
Conclusion
• Reviewed the nature of a relational database• Connection to a database using JDBC• Retrieving data using SQL and JDBC• Using SQL to update the database• Using a result set to update the database• Database metadata using SQL• Using SQL commands to modify the
database structure
![Page 32: Java Database Connectivity JDBC ICW Lecture 12 Errol Thompson](https://reader035.vdocuments.us/reader035/viewer/2022062407/56649d535503460f94a2f042/html5/thumbnails/32.jpg)
Next Time
• Servlets