Database Design
Database Application Programming
Copyright By PowCoder代写 加微信 powcoder
Need for Database programming
Database programming techniques
Embedding SQL in applications
Stored Procedures
Database Programming
Most database access is not via raw SQL queries (stored, console, or script)
Nobody expects users to write queries to place an order at Amazon, or to check their class schedule
Most database access is via application programs / database applications
Dedicated front-ends developed in a general purpose language
C, C++, Java, etc.
Or web-based applications written in an appropriate language
PHP, JavaScript, VBScript, Java, etc.
Simplified Data Base System Environment
Database Programming
Approaches to application programming
Embed SQL commands directly in your application code
EXEC SQL statement / embedded SQL
May require special pre-processing (language dependent)
Use a library of functions to read/write to the database
Uses a published API (application programming interface)
Common approach for general purpose languages
Design a new language, add programming commands to standard SQL
Oracle’s PL/SQL language
Once a popular option, these days not so popular
API approach is the most used approach these days
Database Programming
Differences between database model and programming language model can cause problems
Known as impedance mismatch
Problem 1 – Data types
Data types available to the programming language may not match data types of data model
Must have a binding between database data types and programming language data types
Database Programming
Problem 2 – Queries return multiple rows
Must have a binding to map query result to a multi-valued data structure in the programming language
Must have a method of iterating over results in the programming language
Commonly known as a cursor
Iterates over a dynamic result from the database
Database Programming
Typical operation – client/server model
Client application communicates with one or more database servers
Client opens a connection to the database
Connects via the network, negotiates authentication
Client submits one or more queries, updates, etc.
Client closes the connection when interaction is finished
Database Programming (API)
Using an application programming interface (API) is the most common approach to database programming
API provides functionality for:
Connecting/disconnecting databases
Performing queries/updates
Iterating over results
Binding query results to language data types
API Example – Java JDBC
Java Database Connectivity (JDBC) library
Provides a standard library for interacting with any RDBMS (known as a data source)
Different RDBMS servers, different RDBMS vendors
Each vendor writes a driver to act as an interface between the Java language and the database
Java JDBC – Basic Usage (Query)
Import the JDBC SQL library
Load the JDBC driver
Create a Connection object
Using the DriverManager, a connection is established with the database
Java JDBC – Basic Usage (Query)
4. Create a PreparedStatement object
PreparedStatement object created using the Connection
Used to execute queries/updates
Pass a string of SQL code to the PreparedStatement object to execute a query/update
Create a ResultSet object
Use the PreparedStatement object to execute a query
Results of query stored in ResultSet
Java JDBC – Basic Usage
Iterate over ResultSet
Each element of ResultSet is a tuple from the database
Individual elements of tuple accessed via “get” methods
When finished, close the PreparedStatement and the Connection
Always close all ResultSets, Statements and the Connection!
Not closing ResultSets and Statements immediately after use is a common problem that leads to memory leaks and application performance degredation.
Java JDBC – Example
import java.sql.*;
public class DBExample {
static final String JDBC_DRIVER = “com.mysql.jdbc.Driver”;
static final String DB_URL = “jdbc:mysql://localhost/EMP”;
static final String USER = “username”;
static final String PASS = “password”;
public static void main(String[] args) {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rSet = null;
Class.forName(JDBC_DRIVER);
System.out.println(“Connecting to database…”);
conn = DriverManager.getConnection(DB_URL,USER,PASS);
Import the JDBC SQL library
Load the JDBC driver
Create a Connection object
Java JDBC – Example
String sql = “SELECT first, last FROM Employees ” +
“WHERE id = ?”;
stmt = conn.prepareStatement(sql);
stmt.setInt(1, 1235550987);
rSet = stmt.executeQuery();
while (rSet.next())
String first = rSet.getString(“first”);
String last = rSet.getString(“last”);
System.out.println(”Name: ”+ last +”,”+ first);
catch (Exception ex)
// handle errors …
4. Create a Prepared Statement object
5. Create a ResultSet object
6. Iterate over ResultSet
Java JDBC – Example
if(rSet != null) { rSet.close(); }
if(stmt != null) { stmt.close(); }
if(conn != null) { conn.close(); }
Exception handling
try, catch and finally
finally block always gets executed, whether there’s an Exception or not
Great place to make sure all ResultSets, PreparedStatements and Connections are closed
But remember to not throw an Exception when closing them!
7. When finished, close the Prepared Statement, ResultSet and the Connection
Database Programming (APIs)
Java not the only language with standard SQL API
Most high-level languages provide some kind of database API
Python – DB-API
Perl – DBI
For your language, consult your language documentation
Alternative Approaches
API framework not the only way to connect databases and applications
Embedded SQL – SQL queries embedded right into the code
Not as popular these days as API framework
But popular at the application level
Spreadsheets and other data manipulation programs
Embedding SQL in Excel
Spreadsheet applications very common in business applications
Data analysis
Data collection
Often need to transfer data from databases into spreadsheets
Or from spreadsheets into databases
Database connectivity built into all modern spreadsheet applications
Embedding SQL in Excel
Excel uses the Windows Data Sources Manager to manage connections
Each database connection you use must be set up as a data source
Embedding SQL in Excel
Can then pull data from the database into the spreadsheet as a query from a data source
Data can then be manipulated using any spreadsheet operations
Data may be automatically refreshed
Stored Procedures
Program modules, usually written in SQL
Stored in the database
Executed on the database server
Stored procedures – aka persistent stored modules
Stored Procedures
Useful in a number of circumstances:
When a piece of code is used by several applications (possibly in multiple languages)
Useful for keeping business logic consistent across applications
When executing code on the server will reduce unnecessary data transfer over the network
Useful for speeding up application processing
Stored Procedures
In databases that implement stored procedures, created using CREATE PROCEDURE
Procedure then defined using the language supported by the RDBMS
Generally, a procedural language that allows embedded SQL statements
Stored Procedures
Once created, stored procedures can be called using a CALL statement
CALL [procedure name] (argument list)
Procedure then executes
Most RDBMSs that implement stored procedures also implement stored functions
As a stored procedure, but return a value instead of just executing a series of statements
Stored Procedures – Example 1
PROCEDURE award_bonus (emp_id NUMBER) IS
bonus REAL;
comm_missing EXCEPTION;
BEGIN — executable part starts here
SELECT comm * 0.15 INTO bonus FROM emp WHERE empno = emp_id;
IF bonus IS NULL THEN
RAISE comm_missing;
UPDATE payroll SET pay = pay + bonus WHERE empno = emp_id;
EXCEPTION — exception-handling part starts here
WHEN comm_missing THEN
END award_bonus;
Execute the procedure with a CALL
CALL award_bonus(1235550987);
Stored Procedures – Example II
USE AdventureWorks
CREATE OR ALTER PROCEDURE dbo.uspGetAddress @City nvarchar(30) = NULL, @AddressLine1 nvarchar(60) = NULL
SELECT AddressLine1, AddressLine2, City, PostalCode
FROM Person.Address
WHERE City =
AND AddressLine1 LIKE ‘%’ + ,AddressLine1) + ‘%’
— return rows where City equals Columbus
EXEC dbo.uspGetAddress @City = ‘Columbus’
— return rows where City equals Columbus and AddresLine1 contains XEC dbo.uspGetAddress @City = ‘Columbus’, @AddressLine1 = ‘Lane’
— return rows where AddresLine1 contains
EXEC dbo.uspGetAddress @AddressLine1 = ‘ ‘
— this will return all rows
EXEC dbo.uspGetAddress
/docProps/thumbnail.jpeg
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com