Chapter 10 JDBC

Chapter 10 JDBC
[email protected]
Xiang Zhang
Content
2
 Basic Concepts in Database
 SQL Grammar
 JDBC Principle
 JDBC Examples
COSE Java
Basic Concepts in Database
3
 Database and DBMS
 RDBMS (关系型数据库管理系统)

Store data in rows and columns

Rows called Record (记录) , and columns called Field (字段)

A set of rows and columns are called Table (表)

A table usually represents an Entity (实体)

There are Relations (关系) between Entities,  ER Map

Query through SQL（结构化查询语言）
Java CoSE
Java CoSE
Java CoSE
Primary Key and Foreign Key
6
Primary key
StudentInfo Table
Foreign key
Primary key
3-6
CourseInfo Table
Basic Concepts in Database
7
 Usual RDBMS

MySQL / PostgreSQL / Berkeley DB

Oracle

SQL Server

DB2
Java CoSE
DB Ranking
8
 http://db-engines.com/en/ranking
Java CoSE
9
Java CoSE
Basic Concepts in Database
SQL Grammar
10
 CRUD and CRUD Applications

Create – Insert Statement in SQL

Retrieval – Select Statement in SQL

Update – Update Statement in SQL

Delete – Delete Statement in SQL
Java CoSE
SQL - Select
11
 Data
 Query
 Result
Java CoSE
SQL – Select Distinct
12
 Data
 Query
 Result
Java CoSE
SQL - Select
13
 Data
 Query
 Result
Java CoSE
SQL – Select where
14
 Data
 Query
 Result
Java CoSE
SQL – Select in
15
 Data
 Query
 Result
Java CoSE
SQL – Select between
16
 Data
 Query
 Result
Java CoSE
SQL – Select like
17
 Data
 Query
 Result
Java CoSE
SQL – Select order by
18
 Data
 Query
 Result
Java CoSE
SQL – Select count
19
 Data
 Query
 Result
Java CoSE
SQL – Delete
20
 Data
 Query
 Result
Java CoSE
SQL – Insert
21
 Data
 Query
Java CoSE
SQL – Update
22
 Data
 Query
 Result
Java CoSE
Test
23
 Write following SQL query

Query for all store names

Query for the number of stores with sales > 500

Query for all info of stores with sales in (100, 500)

Query for sales of stores with name containing “an”,
and rank the result descendingly according to sales

Insert into table a new record (any record will do)

Modify all the store sales in LA to 1000

Delete all records related to LA
Java CoSE
Self-study
24
 SQL completed grammar

Recommendation:

http://sql.1keydata.com/cn/sql.php
Java CoSE
JDBC
25
 JDBC - Java Database Connectivity
 Provides API for database access
Java Apps
Client
DB Server
JSP/Servlet
JDBC
Client
App Server
DB Server
JDBC Principle
26
JDBC Driver
Oracle
JDBC ODBC
MySQL
Java APPs
JDBC API
JDBC Driver Manager
JDBC Driver
Sql
Server
JDBC Principle
27
Connection
3
Client
2
Statement
4
ResultSet
DriverManager
DB Server
1
JDBC API
28
 Provider: Sun
 Package

java.sql

javax.sql
 Major Classes and Interfaces

DriverManager Class

Connection Interface

Statement Interface

ResultSet Interface
29
JDBC Example
30
JDBC Example
31
ResultSetMetaData
Two Way of Executing Statement
32
 executeUpdate

For queries with no results returned, usually Insert \
Delete \ Update
 executeQuery

For queries with results returned, usually Select
PreparedStatement Interface
33
 Derived from Statement interface
 For repeatedly executed SQL
 Usually for queries with no result, such as Insert
\ Delete \ Update
 Together with addBatch() and executeBatch()
Java CoSE
34
PreparedStatement
Self Study
35
 NOSQL Databases

Document Store

Key-value DB

Graph DB
Java CoSE
Self-study
36
 Database Connection Pool

Resource Pool design pattern
 Usual Database

Installation and test of MySQL server

MySQL client
Java CoSE