An Introduction to Embedded Database Programming

1
An Introduction to Embedded Database Programming
JDBC

• By Turkan Adatepe

2

• What is Database?
• Oracle
• SQL SQLPlus
• Embedded SQL Programming
• JDBC basics
• A JDBC programming Example
• Q A

3
What is a Database?

• A database is a collection of related data or
  collection of related tables and the software
  that manipulates data files within any large
  organization such as a university or a company.
• A Relational database can be pictured as set of
  spreadsheets which some data in one table might
  related to a data in another table.
• A DBMS is a system that allows to us to create,
  update and alter a database and provide for
  controlling to the data.

4
Database

• A Database is set of tables
• A Table called a entity or relation which is
  collection of columns and rows
• A Column or attribute has a name and list of
  definitions and constraints
• A Row is called a tuple or record

5
What is RDBMS?

• A Relational Database Management System stores
  information in the form of tables and then links
  or relates those tables to provide answer to
  users diverse questions.
• RDBMS were invented around 1970 by Codd.
• There are many RDBMS on market today such as
  ORACLE, DB2, Microsoft SQL, etc

6
DBMS Oracle

• Oracle was the first major relational application
  product
• Oracle is the most widely used database in the
  world. It can run on virtually all kind of
  computer

7
(No Transcript)
8
How to get and put data to a DBMS?

• All the work with a relational database is done
  with a SQL-based programming language
• SQLPlus is the command line console to interact
  with the Oracle db.
• Oracles SQLPlus program lets the user to
  connect to Oracle and provides an environment to
  execute SQL commands
• Csy01gtsqlplus username/oraclepasswd_at_WMUCS
• SQLgt

9
SQL

• Structured Query Language (SQL) is an
  international standard, which is an English like
  non-procedural language.
• SQL is an ANSI standard, which is used to
  retrieve and update data in a database. 
•   The purpose of SQL is the provide an interface
  to a relational database by using SQL statements
  as instructions to the database.

10
SQL Commands

• SQL has very limited feature. Therefore its easy
  to learn. Basic SQL commands are
• Create
• Insert
• Update
• Select
• Delete

11
Example

• SQL is a very simple language. Its like English
  for talking to a database.
• As an example, Lets create a Customer table with
  customer Id and Customer Name
• SQLgt create table member(
• Member_Id number(4 ) primary key,
• Member_Name Varchar2(15) )
• SQLgtinsert into member values( 12, Jim Smith)

12
Embedded SQL

• The term embedded SQL refers to SQL
  statements placed within an application program
  is called host program and the language that is
  written is called host language.
•   

13
Why Embedded SQL?

• For large and complex queries and applications
  SQLPlus will not be enough and useful.
• Embedded SQL provides an environment to use
  high-level languages to allow to connect database
  and to run SQL commands.
• Embedded SQL is a method of combining the
  database manipulation capabilities of SQL and a
  high level language like C/C or java.
• Embedded SQL allows to user to execute an SQL
  statement from an application program.

14
Embedded Languages

• There are variety of programs for embedded SQL
• like PROC/C, PROCOBOL, PROFORTRAN,
• ODBC, JDBC, SQLJ .

15
An Introduction to JDBC

• JDBC (Java Database Connectivity) API
  (Application Program Interface) is a Java API for
  accessing virtually any kind of data source.
• The JDBC API makes it easy to send SQL statements
  to relational database systems and supports all
  dialectics of SQL.
• Oracle 8i JVM schedules transactions of thousands
  of users.

16
What does JDBC?

• Opens a connection to a Relational Database
• Sends SQL statements and commands
• Process the results
• Makes it possible on-line applications

17
Prerequisites

• Install the Java platform and JDBC which
  includes JDBC API
• Drivers
• Installing a DBMS

18
Installing a Java Platform Which includes JDBC
API

• Java 2 SDK Standard Edition includes the JDBC
• 2.0 core API.
• Latest release of the java 2 Platform is
  available at
• http//java.sun.com/products/JDK/CurrentRelease
• http//java.sun.com/product/jdbc

19
Drivers

• JDBC is a database access protocol that enables
  user to connect to a database.
• JDBC-ODBC Bridge driver Java 2SDK will get it
  automatically. JDBC-ODBC Bridge drivers
  communicate to server via ODBC(Open Database
  Connectivity) Drivers.
• JDBC-Thin Driver is suitable to write 100 java
  applications and applets that access a database.
• JDBC OCI (Oracle Call Interface )Driver accesses
  Oracle specific Native code libraries written in
  C language.

20
Getting Started

• JDBC basic steps are
• Import JDBC classes
• Loading a driver
• Making a Connection
• Interaction with the Database
• Closing the connection

21
Import JDBC classes

• To use the JDBC commands, it is necessary to
  import the package, where classes and interfaces
  are implemented is java.sql.
• include java.sql.
• Java 2 SDK Standard edition includes java.sql.
  For advance application, you will need javax.sql
  which is included in the Java 2 SDK Enterprise
  Edition.

22
Loading a Driver

• In the beginning of the program we need to
  tell to driver manager which DBMS we will use.It
  is only one line code, which loads driver or
  drivers you wish to use.
• Class.ForName(location of driver)
• To use JDBC-ODBC bridge driver
• Class.ForName(sun.Jdbc.odbc.jdbcodbcDriver)
• If you are working WMU csy machines then
• Class.ForName(oracle.Jdbc.Driver.OracleDriver)

23
Making a Connection

• After loading a driver, the next step is
  establishing the connection to a DBMS with using
  getConnection call
• Connection Conn Driver.getConnection(url,
• login, passwd)

24
Making a Connection

• For csy machines your code will be
• Connection conn DriverManager.getConnection
• ("jdbcoraclethin_at_kosh.cs.wmich.edu1521WMUCS",
  loginname,orapasswd)
• The first string is the URL for the database
  including the protocol (Jdbc), the vendor
  (Oracle), the driver (thin), the server
  (kosh.cs.wmich.edu) (we install the oracle into
  the kosh in our cs server). The port number
  (1521) and a server instance (WMUCS). Second
  string is your own cs login name, And the last
  one is your Oracle password

25
Creating JDBC Statement

• A JDBC statement object is used to send SQL
  statements to the DBMS.
• A statement object in JDBC is associated with an
  open connection but not any SQL statement.
• A JDBC statement is like a channel on the
  connection and passing one or more SQL
  transactions (statements) to execute to the DBMS.
• Statement stmtconn.createStatment ()
• After this line, in your program a statement is
  created but nothing is passed to the database or
  any SQL statement is executed yet

26
executeUpdate() Statement

• For DDL (Data Definition Language) statements
  such as create, update, insert executeUpdate
  statement is used. These statements will change
  the state of the database.
• Stmt.executeUpdate (Create table Member
• (Member_Id number(4 ) primary key,
• Member_Name Varchar2(15)))
• stmt.executeUpdate("insert into Member values
• (12, Jim Smith
  )")

27
executeQuery() Statement

•    A select statement will do nothing to the
  database tables or to the DBMS but return some
  values or tuples. Therefore JDBC has a different
  method for it called executeQuery.
• ExecuteQuery statement gets the result from
  database

28
Example of executeQuery()

• ResultSet rs
• rsstmt.executeQuery (select from Member)
• While (rs.next ())
• int MIdrs.getInt (1)
• String Mnamers.getString (2)
• System.out.Println (Member IdMId Member
  Name Mname)
•  

29
Transactions  

• JDBC allows SQL statements to be grouped
  together into a single transaction. The
  connection object performs transaction control.
  Default is auto-commit mode. It means after each
  execution SQL statement will be written to the
  database permanently.
• conn.commit() 
• conn.rollback ()

30
Errors and Warnings

• The SQLException class brings the error about the
  accessing database.Its derived from the
  lava.lang.Exception class.
• JDBC lets user to see the error and warnings
  generated by the DBMS and the java compiler by
  using catch block.

31
Example for Error statement

• Statement stmt conn.createStatement()
• try stmt.executeUpdate(select from
  Member) catch (SQLException e)
• while (e ! null)
• System.out.println("Message "
  e.getMessage()) e e.getNextException(
  ) return
  System.out.println(Member table selected!")

32

• Now, Lets see a small JDBC program.

33
Example JDBC programming

• import java.sql.
• import java.io.
• class record
• public static void main (String args )
• throws SQLException, IOException
• Class.forName("Oracle.jdbc.driver.OracleDriver")
  
• Connection connDriverManager.getConnection
• ("jdbcOraclethin_at_kosh.cs.wmich.edu1521WMUCS",
  
• "lname","Oraclepasswd") 

34

• String member "create table member("
• "member_id number(4) primary key,"
• "last_name varchar(15) not null,"
• "first_name varchar(15) not null,"
• "address varchar(50) not null,"
• "phone number(7) not null,"
• "balance number(5.2) default 0.00)"

35

• Statement stmt conn.createStatement()
• try
• stmt.executeUpdate(member)
• catch (SQLException e)
• while (e ! null)
• System.out.println("Message "
  e.getMessage()) e e.getNextException(
  ) return
• System.out.println("Member Table is created")

36

• stmt.executeUpdate("insert into Member values
• (12, 'Smith', John' , 1290 W. Main Kalamazoo
  MI. 49006', 3333333, 0.00)")
• stmt.commit()
• stmt.close()
• conn.close()

37

• All these information and the programs are
  available
• http//www.cs.wmich.edu/cs543/tutorial
• http//yakko.cs.wmich.edu
• Thank you