Download SQL and Java: Interacting with Databases using SQL and Java and more Slides Introduction to Database Management Systems in PDF only on Docsity!
SQL and Java G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
Limitations of SQL
purpose language
- It is designed to create, modify, and query databases
- It is non-procedural, so doesn’t have loops, variables, or other common constructs
- Sometimes we need both of these things - Possible solutions - Extend SQL to include general programming language features (eg PL/SQL) - Extend programming languages to include database commands - Provide a way to use SQL and other languages together
SQL and Java G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
Extending SQL
things to SQL
- This is not covered by standards so is very DBMS-specific
- You end up with a new language that includes SQL
- The results are usually not as flexible as more general programming languages - Extensions usually
add (at least)
- Loops (for, while, etc)
- Variables
- Conditionals (if, etc)
- Function or procedure calls
- Examples:
- PL/SQL (Oracle)
- Transact-SQL (Microsoft)
SQL and Java G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
SQL and Other Languages
combine SQL and
another language
- Use SQL to run queries on the database
- Use another language (Java, C, etc) to do the rest of the work
- Need an interface between the two - ODBC (Open DB
Connectivity) is a
common standard
- Provides an API which is widely supported
- Allows you to pass queries to a database, and return the results to a program
SQL and Java G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
JDBC
method for database
connectivity
- It is not the same as ODBC, but is related
- It provides a Java interface to a database, keeping Java style and object orientation - Basic steps when
using JDBC
- Register a database driver
- Open a connection
- Pass some queries to the database
- Process the results as needed
- Close the connection
- Deal with any errors
SQL and Java G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
Register a Driver
• We need to register an appropriate
driver with the DriverManager
- There is a different driver for each DBMS
- We’ll need to use the driver for Oracle:
DriverManager.registerDriver(
new oracle.jdbc.driver.OracleDriver()
SQL and Java G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
Open a Connection
• Next we open a connection to the database
from the DriverManager
- We give the address of the database, a
username and a password
Connection conn = DriverManager.getConnection ( "jdbc:oracle:thin:@oracle.cs.nott.ac.uk:1521:maindb", "smx", "************");
SQL and Java G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
Passing Queries to the DB
queries to the DB
- We do this through a Statement object
- Each Statement can deal with one query at a time
- A single Connection can have several statements open at any time - Statement objects - Are created from a Connection - The executeUpdate() method runs a query that doesn’t return any results (UPDATE, CREATE TABLE, etc) - executeQuery() is used when a result is expected
SQL and Java G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
Passing Queries to the DB
Statement stmt = conn.createStatement();
Stmt.executeUpdate("DROP TABLE Temp");
String SQL =
"UPDATE Student SET Year = Year + 1";
stmt.executeUpdate(SQL);
stmt.close();
SQL and Java G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
Processing Query Results
returns a result
- We use the Statement object’s executeQuery method
- The results are put in a ResultSet object
- Each Statement can deal with a single ResultSet at any one time - The ResultSet object - Is essentially a table - Has a cursor that points to the current row of data - Initially the cursor is positioned before the first row - The next() method moves to the next row, and returns false if there isn’t one
SQL and Java G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
Processing Query Results
Statement stmt = conn.createStatement();
String SQL = "SELECT * FROM Student";
ResultSet rset = stmt.executeQuery(SQL);
// Loop over all the rows of the result
while (rset.next()) {
// Do some stuff with the current row
rset.close();
stmt.close();
SQL and Java G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
Working with ResultSets
• We get values from the ResultSet with
- getInt()
- getString()
- getDouble()
- etc.
• Each takes either
- The name of the column as a String, or
- The index of the column as an integer
SQL and Java G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
Working with ResultSets
Statement stmt = conn.createStatement();
String SQL = "SELECT * FROM Student";
ResultSet rset = stmt.executeQuery(SQL)
while (rset.next()) {
System.out.println(
rset.getInt("stuID") + ": " +
rset.getString("stuName"));
stmt.close();
rset.close();
SQL and Java G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
Dealing with Errors
with all of this
- Incorrect SQL statements
- DBMS might not be available
- DBMS might not support some features
- If something goes
wrong then an
SQLException occurs
thrown:
- We need to deal with it as best we can
- Make sure any database objects are closed
- If a connection is left open it can consume resources and might interfere with later use of the database SQL and Java G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
Exception Handling
// Declaration of any database objects
try {
// Some database code
} catch (Exception e) {
// Error reporting etc.
} finally {
// Make sure all database objects are
// closed and cleaned up
SQL and Java G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
Closing Objects
object is closed
- See if the object exists
- If it does, call its close method
- This might throw an exception itself, which needs to be caught
- At some stage we have to stop handling the exceptions
Connection conn; try { ... } finally { if (conn != null) { try { conn.close(); } catch (...) { // what to do? } } }
SQL and Java G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
Storing Objects in Databases
• Java represents most things as objects
- We might want to use a database to store
the values of objects
- Data values in objects can be simple types
(int, double, String,…), other objects, or
collections of values or objects (arrays,
Vectors, HashMaps,…)
- There are other ways to solve this problem
- we’ll look at OO databases later
SQL and Java G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
Storing Objects in Databases
becomes a table
- Each instance of the class is a row in the table
- The table has columns for each simple member variable (int, String, etc.)
- A foreign key can reference member variables that are objects - Arrays are more
complicated
- They represent a case where more than one value is associated with each instance
- Generally a particular value is associated with more than one instance
- This is a many-to- many relationship
SQL and Java G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
Storing Objects in Databases
Student name: String addr: Address takes: Module[]
Address number: int street: String city: String
Module code: String title: String
aIDaNumber aStreet aCity
sID sNameaID
mID mCodemTitle
sID mID
Address
Student
Module
Takes
SQL and Java G52DBS – Database Systems www.cs.nott.ac.uk/~smx/DBS
Storing Objects in Databases
into hierarchies
- Subclasses inherit from a superclass
- They get all the attributes of the superclass and may add some more
- This is similar to the subclass having a member instance of the superclass
Executive bonus: int
Employee name: String salary: int
emID emNameemSalary
Employee
emID exBonus
Executive
