Transcript Persistence in Java

Persistence in Java:
Databases
Lynsay A. Shepherd
What will this lecture cover?
• Databases and Java
– setting up a database
– accessing a database via Java
– statement objects for queries
– processing the results
What will this lecture cover?
• You completed a database module last
semester
• This lecture won’t be a repeat of that content
• Going to build on the skills you have gained
What type of database will we use?
• SQLite works quite nicely with Java in the labs
– Wanted to use something other than MS Access
• Considered MSSQL and MySQL but those are
server-based
• Using a local database, means you can add it
into the required JAR files for the coursework
How do we connect to a database?
• Need a JDBC driver
– Implements the interfaces in the JDBC API
– Allows you to connect/interact with your database
• Also need the java.sql package
– Defines classes and their behaviours
– Behaviours are implemented by 3rd party drivers
What is a JDBC driver?
• A bit of software which allows a Java
application to interact with a database
– Many different kinds...
Different types of JDBC drivers
• JDBC-ODBC bridge
• Native-API Driver
• Network-Protocol Driver(MiddleWare Driver)
• Database-Protocol Driver(Pure Java Driver)
JDBC-ODBC bridge
• JDBC bridge
– Accesses ODBC (Open Database Connectivity)
drivers installed the users machine.
– ODBC- you must configure a Data Source Name
(DSN) that represents the target database
• Not used much any more
• Recommended for experimental use/as a last
resort
JDBC-ODBC bridge
Native-API Driver
• JDBC API calls are converted to native C/C++
API calls
• These are unique to the database.
• Drivers are provided by the database vendors
• Used like the JDBC-ODBC Bridge
• Mostly obsolete nowadays
Native-API Driver
Network-Protocol Driver(MiddleWare
Driver)
• JDBC clients use network sockets to communicate with
an middleware application server
• Socket information is then translated by the
middleware application server into the call format
• Call is forwarded to the database server
• Flexible driver
– No code installed on the client
– Single driver, multiple databases.
Network-Protocol Driver(MiddleWare
Driver)
Database-Protocol Driver(Pure Java
Driver)
• Communicates directly with vendor's
database through socket connection
• Highest performance driver available for the
database
• Driver provided by the vendor
• Extremely flexible
– No special software on the client/server
Database-Protocol Driver(Pure Java
Driver)
Setting up a database
• You will need to download an SQLite JDBC JAR
file
• Step-by-step instructions in the practical (it’s
easy)
• You will need to use the java.sql package
Today’s demonstration
• In the last lecture, I used a phonebook
example to illustrate serialization
• Today’s code snippets will be based around
the same topic
• You will need to build an appropriate
application using the music library case study
Creating a database
import java.sql.*;
public class SQLiteJDBC
{
public static void main( String args[] )
{
Connection dbConnection = null;
try {
dbConnection =
DriverManager.getConnection("jdbc:sqlite:test.db");
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ":
" + e.getMessage() );
System.exit(0);
}
System.out.println("Database created.");
}
}
Adding a table to a database
Connection dbConnection = null;
Statement makeStatement = null;
try {
Class.forName("org.sqlite.JDBC");
dbConnection =
DriverManager.getConnection("jdbc:sqlite:phonebook.db"
);
System.out.println("Database opened");
makeStatement =
dbConnection.createStatement();
String sql = "CREATE TABLE Phonebook " +
"(ID INT PRIMARY KEY NOT NULL," +
" FirstName TEXT, " +
" Surname TEXT, " +
" Phonenumber TEXT )";
makeStatement.executeUpdate(sql);
makeStatement.close();
dbConnection.close();
Adding data into the database
makeStatement = dbConnection.createStatement();
String insertSQL = "INSERT INTO Phonebook
(ID,FirstName,Surname,Phonenumber) " +
"VALUES (2,'Lynsay', 'Shepherd',
'11111111111');";
makeStatement.executeUpdate(insertSQL);
makeStatement.close();
dbConnection.commit();
dbConnection.close();
Selecting data from the database
makeStatement = dbConnection.createStatement();
ResultSet rs = makeStatement.executeQuery( "SELECT *
FROM Phonebook;" );
while ( rs.next() ) {
int id = rs.getInt("id");
String firstname = rs.getString("firstname");
String surname = rs.getString("surname");
String phonenumber = rs.getString("phonenumber");
System.out.println( "ID: " + id );
System.out.println( "Name: " + firstname+" "+surname
);
System.out.println( "Phone number: " + phonenumber );
Updating data in the database
makeStatement = dbConnection.createStatement();
String sql = "UPDATE Phonebook set phonenumber = 33333333
where ID=1;";
makeStatement.executeUpdate(sql);
dbConnection.commit();
Deleting data from the database
makeStatement = dbConnection.createStatement();
String sql = "DELETE from Phonebook where ID=2;";
makeStatement.executeUpdate(sql);
dbConnection.commit();
Sources of data
• Obviously, you don’t want to spend all your
time entering artists, tracks, etc. manually
• You might want to utilise a service which will
generate some data for you to use
• Web services are extremely useful in this
respect
– Last.fm
Last.fm
• http://www.last.fm/api
• Could grab artist’s top tracks
– http://www.last.fm/api/show/artist.getTopTracks
– Still need to pick data out of the XML with XPath
Further Reading
• http://www.tutorialspoint.com/sqlite/sqlite_ja
va.htm