www.cardiff.ac.uk/medic/irg-clinicalepidemiology
programming languages (Java)
Copyright By PowCoder代写 加微信 powcoder
Information modelling
& database systems
SQL, Java and JDBC
for more information:
Sun Java tutorial: http://java.sun.com/docs/books/tutorial/jdbc
Connolly and Begg 29.7
SQL & programming languages
when SQL is combined with a programming language:
SQL is used to run queries on the database
programming language (e.g. Java or Python) is used to do the rest of the processing (e.g. user interface or more complicated processing)
this requires an interface between the programming language and the DBMS
ODBC (Open DB Connectivity) is a common standard
provides a widely supported API
allows a program to pass SQL queries to a database and return the results to the program
Java Database Connectivity (JDBC)
a Java API for database connectivity
not the same as ODBC, but implements a similar specification
JDBC enables programmers to write Java applications that
connect to a database
send queries and update statements to the DBMS
retrieve and process the results received from the DBMS in response to the query
JDBC architecture
supports the application-to-JDBC Manager connection
ensures that the correct driver is used to access each data source
driver manager can support multiple concurrent drivers connected to multiple heterogeneous databases
Main components of JDBC
Component Role
DriverManager manages a list of database drivers and matches connection requests from the Java program with the proper database driver
Driver the database communication link handling all communication with the database
Connection interface with all methods for contacting a database; all communication with DBMS is through connection object only
Statement an object that encapsulates an SQL statement, which is passed to DBMS to be parsed, compiled, planned and executed
ResultSet a set of rows retrieved following query execution
Using JDBC
preamble: import java.sql.*;
basic steps
register a database driver
open a connection
pass queries to the database
process query results as needed
close the connection
deal with any errors
https://docs.oracle.com/cd/F49540_01/DOC/java.815/a64685/basic1.htm
Register a database driver
preamble: driver must be installed
you need to provide the code to register the driver in your Java program
class: DriverManager
method: registerDriver()
declare a specific driver name string, e.g.
DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
register the driver only once in your Java program
Register a database driver
alternative way to load the JDBC driver directly
class: java.lang.Class
method: forName()
Class.forName (“oracle.jdbc.driver.OracleDriver”);
NOTE: this method is valid only for JDK–compliant Java virtual machines and is not valid for Microsoft Java virtual machines
Open a connection
class: DriverManager
method: getConnection()
input: URL, user, password
URL contains information about the driver,
server and the database
returns an object of the JDBC Connection class, e.g.
Connection conn = DriverManager.getConnection “scxyz”, “password123”);
Passing queries to the database
queries are sent to the DBMS through a Statement object
each Statement deals with one query at a time
a single connection can have multiple statements open
at any time
Statement objects are created from a Connection
Statement stmt = conn.createStatement();
two methods
executeUpdate() runs a query that does not return
any results, e.g. UPDATE
executeQuery() is used when a result is expected,
e.g. SELECT
Query results
we use the Statement object’s executeQuery method to send a query
this method takes an SQL statement as input and returns a ResultSet object, e.g.
ResultSet rs =
stmt.executeQuery(“SELECT name FROM emp;”);
each Statement deal with only one query at a time
therefore, each Statement can deal with only one ResultSet at a time
Processing query results
a ResultSet object is essentially a table
it has a cursor that points to the current row
initially, the cursor is positioned before the first row
next() method moves the cursor to the next row or returns false if there isn’t one
while (rs.next())
/* process the current row */
Processing query results
we extract values from the ResultSet object using get methods
getString()
getDouble()
each method either of the following as input
column name e.g. rs.getString(“name”)
column index e.g. rs.getInt(1)
Closing the ResultSet & Statement objects
if you do not explicitly close your ResultSet and Statement objects, then
serious memory leaks could occur
you could also run out of cursors in the database
therefore, you must explicitly close the ResultSet and Statement objects after you finish using them
rs.close();
stmt.close();
Closing the connection
when you close a Statement object, the database connection itself remains open
you must close the connection to the database once you finish all your work
conn.close();
/docProps/thumbnail.jpeg
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com