Einführung in JDBC - Universität Stuttgart

Einführung in JDBC
Vorlesung im Rahmen des Studienprojekts MUMS
Sommersemester 2003
Mark Giereth
Institut für Intelligente Systeme
Universität Stuttgart
JDBC
The JDBC API provides universal data access from the
Java programming language
To use the JDBC API with a particular database
management system, you need a JDBC driver to mediate
between JDBC technology and the database
The JDBC-ODBC Bridge driver that makes most Open
Database Connectivity (ODBC) drivers available to
programmers using the JDBC API
The JDBC 3.0 API is comprised of two packages:
the java.sql package
the javax.sql package
Einführung in JDBC, Mark Giereth, 25. Juni 2003
2
The java.sql package
Making a connection with a database via the
DriverManager facility
Sending SQL statements to a database
Retrieving and updating the results of a query
Standard mappings for SQL types to classes and interfaces
in the Java programming language
Metadata
Exceptions
Einführung in JDBC, Mark Giereth, 25. Juni 2003
3
Overview
Einführung in JDBC, Mark Giereth, 25. Juni 2003
4
important classes and interfaces
DriverManager -- makes a connection with a driver
Connection -- provides methods for creating statements
and managing connections and their properties
Statement -- used to send basic SQL statements
ResultSet -- Retrieving and updating the results of a query
DatabaseMetaData -- provides information about the
database (feature support, version, … )
ResultSetMetaData -- provides information about the
columns of a ResultSet object
SQLException -- thrown by most methods when there is a
problem accessing data and by some methods for other
reasons
Einführung in JDBC, Mark Giereth, 25. Juni 2003
5
Connection
...
public Connection connect(String user, String pwd)
throws Exception {
//Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Class.forName(org.hsqldb.jdbcDriver);
String dburl = "jdbc:hsqldb:testdb"
Connection con =
DriverManager.getConnection(dburl, user, pwd);
// if we are here we have a valid connection
DatabaseMetaData meta = con.getMetaData();
System.out.print("Connected to " +
meta.getDatabaseProductName());
return con;
}
Einführung in JDBC, Mark Giereth, 25. Juni 2003
6
Statement
public void execute(Connection con,String sql)
throws Exception {
Statement stmt= con.createStatement();
stmt.execute(sql);
stmt.close();
}
Einführung in JDBC, Mark Giereth, 25. Juni 2003
7
SQL (1)
Neue Tabelle anlegen:
CREATE TABLE name (
columnDefinition [, ...] [, constraintDefinition...]) ;
columnDefinition:
columnname Datatype [(columnSize[,precision])]
[DEFAULT 'defaultValue'] [[NOT] NULL] [PRIMARY KEY]
constraintDefinition:
UNIQUE ( column [,column...] ) |
PRIMARY KEY ( column [,column...] )
Tabelle löschen:
DROP TABLE table [IF EXISTS];
Einführung in JDBC, Mark Giereth, 25. Juni 2003
8
SQL (2)
Zeilen einfügen:
INSERT INTO table [ ( column [,...] ) ] {
VALUES(Expression [,...]) | SelectStatement } ;
Zeilen löschen:
DELETE FROM table [ WHERE Expression ] ;
Zeilen aktuallisieren:
UPDATE table SET column = Expression [, ...] ; [WHERE
Expression]
Einführung in JDBC, Mark Giereth, 25. Juni 2003
9
SQL (3)
Queries:
SELECT [DISTINCT]
{ selectExpression | table.* | * } [, ... ]
FROM tableList
[ WHERE Expression ]
[ GROUP BY Expression [, ...] ]
[ ORDER BY orderExpression [, ...] ]
[ { UNION [ALL] | {MINUS|EXCEPT} | INTERSECT }
selectStatement ] ;
Einführung in JDBC, Mark Giereth, 25. Juni 2003
10
Beispiel XML-Ausgabe
Einführung in JDBC, Mark Giereth, 25. Juni 2003
11
Zusammenfasung
Sie haben ein kleines JDBC Beispiel gesehen und dabei
die folgende Klassen kennengelernt:
DriverManager
Connection
Statement
ResultSet
DatabaseMetaData
Einführung in JDBC, Mark Giereth, 25. Juni 2003
12
JDBC Links
Java Tutorial von SUN:
http://java.sun.com/docs/books/tutorial/jdbc/TOC.html
JDBC Seite von SUN:
http://java.sun.com/products/jdbc/index.html
Einführung in JDBC, Mark Giereth, 25. Juni 2003
13