Transcript JAVA Database Access

JAVA Database Access
JDBC
• The Java Database Connectivity (JDBC) API is
the industry standard for databaseindependent connectivity between the Java
programming language and a wide range of
databases – SQL databases and other tabular
data sources, such as spreadsheets or flat
files.
• The JDBC API provides a call-level API for SQLbased database access.
JDBC
• Using the JDBC, you can access virtually any data
source, from relational databases to spreadsheets and
flat files.
• JDBC technology also provides a common base on
which tools and alternate interfaces can be built.
• The JDBC 3.0 API is comprised of two packages:
– the java.sql package
– the javax.sql package, which adds server-side capabilities
– You automatically get both packages when you download
the Java Platform Standard Edition (Java SE) 6.
JDBC Driver
• To use JDBC to access a database, you need to
get the JDBC driver from the database vendor
– For example, to use MySQL’s JDBC driver, i.e.,
Connector/J, you can download it from
http://dev.mysql.com/downloads/connector/j/
• Connctor/J documetation:
– http://dev.mysql.com/doc/refman/5.6/en/connec
tor-j.html
JDBC Driver
• Where you should install the driver?
– When using it in a web application, put it in the dir
WEB-INF/lib under your web application’s
directory of TOMCAT
– You can also put in the corresponding place inside
the WAR file
MySQL JDBC Driver
• When you are using JDBC outside of an
application server, the DriverManager class
manages the establishment of Connections.
• The DriverManager needs to be told which
JDBC drivers it should try to make Connections
with. The easiest way to do this is to use
Class.forName() on the class that implements
the java.sql.Driver interface.
• With MySQL Connector/J, the name of this
class is com.mysql.jdbc.Driver.
Load the JDBC Driver for MySQL
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class LoadDriver {
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
} catch (Exception ex) {
// handle the error
}
}
Get the Connection object
This example shows how you can obtain a Connection instance from the DriverManager.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
... try {
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/test?user=monty&password=“)
// Do something with the Connection
....
} catch (SQLException ex) {
// handle any errors
User password
Host name or ip address
(no password in this case)
System.out.println("SQLException: " + ex.getMessage());
System.out.println("SQLState: " + ex.getSQLState());
System.out.println("VendorError: " + ex.getErrorCode());
Database user
}
Database name
// assume conn is an already created JDBC connection
Statement stmt = null;
ResultSet rs = null;
try {
stmt = conn.createStatement();
rs = stmt.executeQuery("SELECT foo FROM bar");
}
// Now do something with the ResultSet ....
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException sqlEx) { // ignore }
rs = null;
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException sqlEx) { // ignore }
stmt = null;
}
}
Execute query
More info
• MySQL JDBC driver tutorial
– http://dev.mysql.com/doc/refman/5.0/en/connec
tor-j.html
• Sun’s JDBC tutorial
– http://java.sun.com/docs/books/tutorial/jdbc/bas
ics/index.html
• In depth tutorial from Sun and JGuru
– http://java.sun.com/developer/onlineTraining/Dat
abase/JDBCShortCourse/index.html