Transcript 28jdbc

JDBC
Java Database Connectivity
What is an RDBMS?
• Relational database management system.
• There are other kinds of DBMS.
• Access is a GUI on a JET RBDMS
backend.
• Language for RBMS - > SQL
SQL
•
•
•
•
•
Structured Query Language
There are many standards for SQL
SQL92, SQL 89, SQL99,
Some commands exists for all the standards.
Some commands exists only for specific
products
Why do I need JDBC?
•
•
•
•
•
Connects to a RDBMS
RDBMS Neutral
Standard API (portable)
Allows for transmission of SQL
Can help to process results.
Using JDBC and Tiers
• Java program calls JDBC methods
• These methods transmit SQL statements
• The RDBMS responds, via the JDBC
methods
• This is a 2 Tier system.
SQL Is
• NOT OOP language (yet)
• able to store instances of classes
• able to store blobs (unformatted sequences
of bytes)
RBMS is good for
• storing data
• searching data
• non-programmer updates of data.
JDBC+RDBMS=2 tier
computing
• Client + server = 2 tier computing.
• JDBC does connections, query and result
processing from the RDBMS.
• JDBC uses SQL.
Process
1.
2.
3.
4.
5.
Connect to DBMS
Instance SQL Statement
Execute SQL statement
Process SQL results
close connection to DBMS
3 tier system - distributed
computing
html
client
browser
w/o java
cgi
web server
jdbc results
jdbc
rdbms
4 tier system - distributed
computing
html
client
browser
w/o java
cgi
b-logic
web server
jdbc results
jdbc
rdbms
Jdbc driver ideas
•
•
•
•
•
•
JDBC needs to work with many DBMS
Oracle
mySql
JET
DB2
SQLServer, etc....
Mapping to a DSN...
• JDBC with ODBC driver is called
JDBC/ODBC (microsoft method).
• connect using a protocol and subprotocol
How do I formulate a connect
URL?
• String url
=“jdbc:dbnet://show.docjava.com:356/addre
sses”
• jdbc – primary protocol
• dbnet – sub protocol
• show.docjava.com – FQDN
• 356 – standard port for RDBMS services
What Packages do I need?
• import java.sql.*;
The format of the url string is vendor
specific
• String url = “jdbc:dbnet://localhost:356/Books”;
• where dbnet is an example of a subprotocol, and
//localhost:356/Books is an example of a subname.
The DriverManager class can also take an
optional user name and password. For example:
• Connection c = DriverManager.getConnection(url,
“userid”, “password”);
Setting up a RDBMS
• How to set up your RDBMS
• How to get drivers for JDBC
• How to load the drivers
• How to set up a select few IDEs for
programming
data source name (DSN)
• The DSN can be set using one of several
techniques. Under Windows, double-click
the ODBC Data Sources (32bit) control
panel.
Using Appendix H
• ODBC Data Source Administrator is shown
upon opening the ODBC Data Sources
control panel. Select the MS Access
Database driver and click Add...
Adding a database
Create a new data source
Set up
Add the DSN
MDB Files look like:
Mapping the DSN File:
Using odbc to mysql
• http://www.mysql.com/downloads/apimyodbc.html
• Access to mysql via odbc driver
Configuration
Accessing and ODBC database
Select your Data Source
Select your tables
Click on the tables
Setting up mysql
• >mysql
• Welcome to the MySQL monitor.
Commands end with ; or \g.
• Your MySQL connection id is 4 to server
version: 3.23.22-beta-log
• Type 'help' for help.
• mysql> use test
• Database changed
Make a table
• mysql> create table User ( UserId varchar(10), Password
varchar(10));
• Query OK, 0 rows affected (0.06 sec)
• To create a more more complex table (for example a phone
book) use:
• mysql> create table phonelist (firstname
varchar(20),lastname varchar(20), address1 varchar(255),
address2 varchar(255), phone1 varchar(50), phone2
varchar(50), phone3 varchar(50));
• Query OK, 0 rows affected (0.04 sec)
• mysql> show tables;
Get a driver
• <http://mysql.sourceforge.net/>.
grant access to the user
•
•
•
•
•
•
•
mysql> show grants ;
grant create on *.* to guest;
Query OK, 0 rows affected (0.08 sec)
mysql> use test;
Database changed
mysql> create table flatfile (message char(255));
Query OK, 0 rows affected (0.02 sec)
Loading a table
• Use the load command to import data from
a text file. For example:
• mysql> LOAD DATA LOCAL INFILE
“c:\lyon\j4p\src\addbk\JAddressBook\foo.cs
v" INTO TABLE phonelist FIELDS
TERMINATED BY '\t' (name, kind,
createdDate, modifiedDate);
Selecting records
• select * from flatfile;
class DataBaseMetaData is obtained
automatically by the SqlBean
• Class.forName(driver);
•
c=
DriverManager.getConnection(url,userId,password);
•
dbmd = c.getMetaData();
•
catalogName =
•
c.getCatalog();
•
isReadOnly = c.isReadOnly();
•
usesLocalFiles =
•
dbmd.usesLocalFiles();
•
driverName =
•
dbmd.getDriverName();
For Next Week
• Make the addressbook program index work
so that selecting a letter will display the
index so that it starts from that letter. Use
the first letter that appears in the name field.
• If you select ‘I’ but there are no records that
start with ‘I’ go to the record preceding
where I should be..
• For ‘a’ go to the beginning.
Implements the CSV features
• Using your jtable, import from text files via
a delimiter panel.
• Using the delimiter panel, export your text
files.