Bài giảng Lập trình Java (Java Programming) - Chương 6: Lập trình cơ sở dữ liệu với JDBC

Chương 6: LẬP TRÌNH CSDL VỚI JDBC
Khoa CNTT
ĐH GTVT TP.HCM
Khoa CNTT (ĐH GTVT TP.HCM) Java Programming 1 / 15
Nội dung
1 JDBC là gì?
2 Kiến trúc JDBC
3 Kết nối đến CSDL
4 Các thao tác cơ bản trên CSDL
Khoa CNTT (ĐH GTVT TP.HCM) Java Programming 2 / 15
JDBC là gì?
Định nghĩa
JDBC (Java Database Connectivity), which is a standard Java API
for database-independent connectivity between the Java program and
a wide range of databases.
Khoa CNTT (ĐH GTVT TP.HCM) Java Programming 3 / 15
Kiến trúc JDBC
Khoa CNTT (ĐH GTVT TP.HCM) Java Programming 4 / 15
JDBC Application Program Interface (API)
Khoa CNTT (ĐH GTVT TP.HCM) Java Programming 5 / 15
JDBC Application Program Interface (API)
Classes & Interfaces
* DriverManager: This class loads JDBC drivers in memory. It is a “factory”
class and can also be used to create java.sql.Connection objects to data
sources (such as Oracle, MySQL, etc.).
* Connection: This interface represents a connection with a data source. The
Connection object is used for creating Statement, PreparedStatement, and
CallableStatement objects.
* DatabaseMetaData: This interface provides detailed information about the
database as a whole. The Connection object is used for creating Database
MetaData objects.
* Statement: This interface represents a static SQL statement. It can be used
to retrieve ResultSet objects.
Khoa CNTT (ĐH GTVT TP.HCM) Java Programming 6 / 15
JDBC Application Program Interface (API)
Classes & Interfaces
* PreparedStatement: This interface extends Statement and represents a
precompiled SQL statement. It can be used to retrieve ResultSet objects.
* CallableStatement: This interface represents a database stored procedure. It
can execute stored procedures in a database server.
* ResultSet: This interface represents a database result set generated by using
SQL’s SELECT statement. Statement, PreparedStatement,
CallableStatement, and other JDBC objects can create ResultSet objects.
* ResultSetMetaData: This interface provides information about the types and
properties of the columns in a ResultSet object.
* SQLException: This class is an exception class that provides information on
a database access error or other errors.
Khoa CNTT (ĐH GTVT TP.HCM) Java Programming 7 / 15
Kết nối đến CSDL
Connection
static private Connection con = null;
static String driver = "com.mysql.jdbc.Driver";
static String host = "jdbc:mysql://localhost:3306/banhang
?useUnicode=yes&characterEncoding=UTF-8";
static String uName = "root";
static String uPass = "root";
static private Connection createConnection() throws
SQLException, ClassNotFoundException {
//Class.forName(driver);
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
return DriverManager.getConnection(host, uName, uPass);
}
Khoa CNTT (ĐH GTVT TP.HCM) Java Programming 8 / 15
Các thao tác cơ bản trên CSDL
Đọc dữ liệu
static public ResultSet getResultSet(String query, Object...
params) throws SQLException, ClassNotFoundException {
con = createConnection();
PreparedStatement pst = con.prepareStatement(query);
int j=1;
for (int i = 0; i < params.length-1; i+=2) {
pst.setObject(j, params[i], (int) params[i+1]);
j++;
}
return pst.executeQuery();
}
Khoa CNTT (ĐH GTVT TP.HCM) Java Programming 9 / 15
Các thao tác cơ bản trên CSDL
Đọc dữ liệu
try {
ResultSet rs = DAL.getResultSet("select P.*, C.name as
categoryname from product as P "
+ "inner join category as C on P.cat = C.id where P.cat=?",
1, Types.INTEGER);
if (rs == null) {
throw new SQLException();
}
while (rs.next()) {
System.out.println(rs.getString("categoryname"));
}
} catch (Exception ex) {
ex.printStackTrace();
}
Khoa CNTT (ĐH GTVT TP.HCM) Java Programming 10 / 15
Các thao tác cơ bản trên CSDL
Thêm / Xóa / Sửa trên CSDL
static public int executeNonQuery(String query, Object...
params) throws SQLException, ClassNotFoundException {
con = createConnection();
PreparedStatement pst = con.prepareStatement(query);
int j=1;
for (int i = 0; i < params.length-1; i+=2) {
pst.setObject(j, params[i], (int) params[i+1]);
j++;
}
int kq = pst.executeUpdate();
con.close();
return kq;
}
Khoa CNTT (ĐH GTVT TP.HCM) Java Programming 11 / 15
Các thao tác cơ bản trên CSDL
Thêm / Xóa / Sửa trên CSDL
String query = "insert into product(name,price,description)
values (?,?,?)";
try {
int kq = DAL.executeNonQuery(query, "product demo",
Types.VARCHAR, 100, Types.DECIMAL, "no description",
Types.LONGVARCHAR);
if (kq == 1) {
System.out.println("1 row effected");
}
} catch (Exception ex) {
ex.printStackTrace();
}
Khoa CNTT (ĐH GTVT TP.HCM) Java Programming 12 / 15
Các thao tác cơ bản trên CSDL
Metadata - Get table names
static public ArrayList getTableMetadata() throws Exception {
con = createConnection();
DatabaseMetaData meta = con.getMetaData();
String[] table = {"TABLE"};
ResultSet rs = null;
ArrayList tables = new ArrayList();
// System.out.println(meta.getDriverName() + "\n" +
meta.getURL());
rs = meta.getTables(null, null, null, table);
while (rs.next()) {
tables.add(rs.getString("TABLE_NAME"));
}
con.close();
return tables;
}
Khoa CNTT (ĐH GTVT TP.HCM) Java Programming 13 / 15
Các thao tác cơ bản trên CSDL
Metadata - Get column names
static public ArrayList getColumnsMetadata() throws Exception {
con = createConnection();
DatabaseMetaData meta = con.getMetaData();
String[] table = {"TABLE"}; ResultSet rs = null;
ArrayList tables = getTableMetadata();
ArrayList columns = new ArrayList();
for(Object t:tables){
rs = meta.getColumns(null, null, (String) t, null);
while(rs.next()){columns.add(rs.getString("COLUMN_NAME"));
//"COLUMN_SIZE" or "TYPE_NAME"
}
}
con.close(); return columns;
}
Khoa CNTT (ĐH GTVT TP.HCM) Java Programming 14 / 15
—Hết—
Khoa CNTT (ĐH GTVT TP.HCM) Java Programming 15 / 15