Transcript Example - Intranet DCC

Java Database Connectivity JDBC
JDBC is an API, containing classes and
interfaces in the java programming language, to
execute SQL sentences over an external database.
Offers a standard interface for connecting to any
database available.
Helps obtaining the data more easily and
comfortable in a client-server environments
through the Internet/Intranet.
What does JDBC do?
Allow a connection to a database server.
Allow sending SQL sentences.
Process the results of these Sentences.
The classes that allow this, are in the
package java.sql (it has to be imported)
2
Loading the Driver
It is necessary to load a class with the driver
of the Database (This is provided by the
DBMS vendor)
Example:
Class c =
Class.forName(“com.informix.jdbc.IfxDriver");
Class.forName(“com.novell.sql.LDAPDriver”);
Class.forName("com.mysql.jdbc.Driver");
This depends on the database that will be
used
Then an instance of the class must be created.
c.newInstance();
3
Establishing a Connection
Connection con = DriverManager.getConnection (
url,"login", "password");
In the examples the login and password
will be discarded
A Connection Object represents a
connection to a database
The class “DriverManager” tries to find a
driver to connect to the database
represented in the URL.
4
Connection
URL Examples:
 jdbc:mysql://localhost/test (for examples)
 jdbc:oracle://oraserver
 jdbc:odbc:mydatabase
 jdbc:informixsqli://hostname:port/dbname:INFORMIXSER
VER=serverName (for CGE databases)
 jdbc:ldap://server;baseDN=baseDN;useClearte
xt=true
5
...Connection
URL JDBC Syntaxis:
 jdbc:<subprotocol>:<subname>
 <subprotocol> is the name of the driver or
mechanism of the connection. Example: odbc.
 <subname> is the identification of the
database. The format changes depending of the
driver. Example: //server:port/db and
parameters
6
Sending SQL sentences
JDBC allows to send any SQL sentence.
although this is dependent to the database,
the only risk is the incompatibility between
different databases.
7
...Sending SQL Sentences
JDBC provides 3 classes:
 “Statement”: This object is used to send simple
SQL sentences. It is created by the method:
createStatement.
 “PreparedStatement”: This object is used for
sentences that needs one or more parameter.
The sentence is pre-compiled and saved for a
future use.
 “CallableStatement”: It is used to execute
procedures saved in the database.
8
The Statement class
Statement stmt = con.createStatement();
In this moment the statement exists but
doesn't have a SQL sentence to execute.
This can be sended using the methods:
 executeUpdate(String), used to create/modify
tables (there is no results), commonly used for
the create,update,delete sentences.
 executeQuery(String) to make queries, returns
results in an object from the class ResulSet,
tipically for a select statement.
9
excuteUpdate Examples
 stmt.executeUpdate(“create table
students (name varchar(32), id integer,
address varchar(40), yearborn integer,
...);
 stmt.executeUpdate(“insert into students
(‘Valeria Valencia ’, 00998852678,
‘Calle bonita 223 Vitacura Stgo’, 1974,
...);
 stmt.executeUpdate(“update students set
direccion = ‘?????’ where name =
‘Valeria Valencia’ ”);
10
excuteUpdate Examples
• CreateCoffees: Creates a Coffees table in the test
database.
• CreateSuppliers: Creates a Suppliers table in the
test Databes.
• DropCoffees: Delete both tables.
• MyConnection: encapsulates the connection to the
database (to be used for other applications)
• InsertCoffees: Inserts rows in the table.
• InsertSupliers: Inserts rows in the table.
• InsertCoffeesDatos: Inserts rows from data readed
from a file.
• ListCoffes, ListSuppliers: Show the content (uses
11
executeQuery)
executeUpdate return
• The executeUpdate method returns an integer value
corresponding to the number of rows that the SQL
sentences modified.
• If it is a creation of a table, the return value is 0
• If it is an insert of a value in a row the value is 1.
• The results is interesting when using the SQL
sentence with the instruction: “update table set
field = <expression> where <condition>
12
excuteQuery Examples
 stmt.executeQuery(“select * from
students “ + “where yearofborn =
1974”);
The results of a query is received in an ResultSet
object.
ResultSet rs = stmt.executeQuery(....);
A ResultSet can be seen as an enumeration of
rows that represents the results.
There are adequate methods to go through all the
elements of this enumeration and obtain the
values of the fields
13
The ResultSet
ResultSet rs = stmt.executeQuery(“select
name where address like Santiago”);
while (rs.next()) {
String s = rs.getString(“name”);
int y = rs.getInt(“yearofborn”);
System.out.println(s+”
“+y);
}
 ResultSet rs contains a collection of rows with the results of
the query instruction next advances a pointer which
informs in what row are we now. At the beginning this is
before the first row, so it is necessary to execute a next()
method to point to the first row.
14
The getXXX Instruction
It can be used with two types of
parameters:
 getXXX(“Name of the field of the table”)
example: getString(“name”)
 getXXX(number of field of the table)
example: getString(1)
getXXX tries to read what is on the field
and converts it to the type specified in the
“XXX”
15
The Instruction getXXX
Sometimes, even if the SQL data type does not
exactly matches the XXX type a conversion is
possible ( with getString & getObject almost
everything can be retrieved)
In other cases the conversion is possible but with
some loss of information (getByte for a numeric
or longvarchar)
In other cases it is just impossible (use getLong
for Time)
16
Prepared Statements
 Everywhere a Statement has been used it is possible to
use a PreparedStatement in order to make queries more
efficient
 An instruction containing a PreparedStatement will be
translated to a native SQL statement of the database
 Another advanatage is that it allows the usage of
parameters
 PreparedStatement us =
con.prepareSatatement(“update alumnos set
comuna = ? where direccion like = ?);
 us.setString(1,’Vitacura’)
us.setString(2,’Hualtatas’);
17
Prepared Statements: Example
PreparedStatement updateSales;
String updateString = "update COFFEES " +
"set SALES = ? where COF_NAME like ?";
updateSales = con.prepareStatement(updateString);
int [] salesForWeek = {175, 150, 60, 155, 90};
String [] coffees = {"Colombian", "French_Roast", "Espresso",
"Colombian_Decaf", "French_Roast_Decaf"};
int len = coffees.length;
for(int i = 0; i < len; i++) {
updateSales.setInt(1, salesForWeek[i]);
updateSales.setString(2, coffees[i]);
updateSales.executeUpdate();
}
18
Transactions
A transaction consists in one or more
sentences that have been executed and then
confirmed (commit) or deleted (rolled
back)
Auto-commit is pre-set.
if Auto-commit is deactivated, the methods
commit or rollback have to be used in a
explicit way.
19
Transactions
To use Transactions, the auto-commit must
be disabled.
 con.setAutoCommit(false)
 PreparedStatement ps = .....
 ....
 ps.executeUpdate() ....
 ps.excecuteUpdate() ...
 con.commit();
20
Stored Procedures
Is a group of SQL sentences that are grouped
logically in a unit to do a specific work.
Exists in most of the DBMS, but they are
dependent to this (The way of how these
procedures are written or executed isn't so
standard)
Generally receives parameters.
They are “written” with an Update and executed
with a Query.
21
An Example
To create a Stored procedure
String crearSP = “create prodcedure SHOW_SUPPLIERS”+
“ as ”+
“select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME”+
“from SUPPLIERS, COFFEES ”+
“where SUPPLIERS.SUP_ID = COFFEES.SUP_ID”
Statement stmt = con.CreateStatement();
stmt.executeQuery(createSP);
to call the Stored Procedure
CallableStatement cs;
cs = con.prepareCall(“{call SHOW_SUPPLIERS}”);
ResultSet rs = cs.executeQuery();
22
Using metadata
 The metadata is the information about the structure of a
databaste or a ResultSet
 It is obtained with the method getMetaData()
stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(query);
ResultSetMetaData rsmd = rs.getMetaData();
int numberOfColumns = rsmd.getColumnCount();
int rowCount = 1;
while (rs.next()) {
System.out.println("Row " + rowCount + ": ");
for (int i = 1; i <= numberOfColumns; i++) {
System.out.print("
Column " + i + ": ");
System.out.println(rs.getString(i));
}
System.out.println("");
rowCount++;
}
23
Using metadata
 Example to know the tables of a database.
DatabaseMetaData dbmd = con.getMetaData();
String dbmsName = dbmd.getDatabaseProductName();
ResultSet rs = dbmd.getTableTypes();
System.out.print("The following types of tables are ");
System.out.println("available in " + dbmsName + ": ");
while (rs.next()) {
String tableType = rs.getString("TABLE_TYPE");
System.out.println("
" + tableType);
}
24