Introduction to Database Systems and SQL Programming
JAVA JDBC
JAVA JDBC
Java Database Programming
Lamiaa Said
What is a Database System?
e.g., Access, MySQL,
Oracle, and MS SQL
Server
Database Application
Systems
The application program
may use more than one
DBMS
Examples of Simple SQL
Statements
•
Select statement
select firstName, mi, lastName
from Student
where deptId = 'CS';
select firstName, mi, lastName
from Student
where deptId = 'CS' and zipCode = '31411';
select *
from Student
where deptId = 'CS' and zipCode = '31411';
Examples of Simple SQL
Statements, cont.
•
Insert statement
insert into Course (courseId, subjectId,
courseNumber, title)
values ('11113', 'CSCI', '3720', 'DatabaseSystems', 3);
Examples of Simple SQL
Statements, cont.
•
Update statement
update Course
set numOfCredits = 4
where title = 'Database Systems';
Examples of Simple SQL
Statements, cont.
•
Delete statement
delete Course
where title = 'Database System';
Java Database Connectivity
(JDBC)
•
Is a java API that allow java programs to
access Database.
•
The JDBC classes are contained in the Java
package
java.sql
The Architecture of JDBC
The JDBC-ODBC Bridge
•
Is a database driver that utilize the ODBC
driver to connect the database.
•
This driver translates JDBC method calls into
ODBC function calls.
•
ODBC (
O
pen
D
ata
B
ase
C
onnectivity), is used
to make it possible to access any data from
any application, regardless of which (DBMS)
is handling the data
JDBC Drivers
•
A JDBC driver allows a Java
application/client to communicate with a
SQL database.
•
A JDBC driver is a Java class.
•
A JDBC driver converts program (and
typically SQL) requests for a particular
database.
Developing JDBC Programs
The JDBC Interfaces
JDBC Drivers
•
A JDBC driver allows a Java
application/client to communicate with a
SQL database.
•
A JDBC driver is a Java class.
•
A JDBC driver converts program (and
typically SQL) requests for a particular
database.
Loading Drivers
•
Statement to load a driver:
–
Class.forName("JDBCDriverClass
");
–
or
DriverManager.registerDriver(new
JDBCDriverClass
());
•
A driver is a class. For example:
•
The JDBC-ODBC driver for Access is bundled in JDK.
•
MySQL driver class is in mysqljdbc.jar
•
Oracle driver class is in classes12.jar
Establishing Connections
•
Connection connection =
DriverManager.getConnection(
databaseURL
);
Establishing Connections
Examples
•
For Access:
–
Connection connection = DriverManager.getConnection
("jdbc:odbc:ExampleMDBDataSource");•
For MySQL:
–
Connection connection = DriverManager.getConnection
("jdbc:mysql://localhost/test");•
For Oracle:
–
Connection connection = DriverManager.getConnection
("jdbc:oracle:thin:@liang.armstrong.edu:1521:ora9i", "scott", "tiger");
Creating and Executing
Statements
•
Creating statement:
–
Statement statement = connection.createStatement();
•
Executing statement (for update, delete,
insert):
–
statement.executeUpdate("create table Temp (col1char(5), col2 char(5))");
•
Executing statement (for select):
–
ResultSet resultSet = stmt.executeQuery ("selectfirstName, mi, lastName from Student where lastName
" + " = 'Smith'");
Processing ResultSet
•
Executing statement (for select):
–
ResultSet resultSet = stmt.executeQuery ("selectfirstName, mi, lastName from Student where lastName
" + " ='Smith'");
•
Processing ResultSet (for select):
//Iterate through the result and print the student
names
while (resultSet.next())
{System.out.println(resultSet.getString(1) + " " +
resultSet.getString(2) + " " +
resultSet.getString(3));
}
Simple JDBC Example
import java.sql.*;
public class SimpleJdbc
{public static void main(String[] args)
{try {DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Connection connection = DriverManager.getConnection
("jdbc:mysql://localhost/test");Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery ("selectfirstName, mi, lastName from Student where lastName " + " =
'Smith'");
while (resultSet.next())
System.out.println(resultSet.getString(1) + "\t" +
resultSet.getString(2) + "\t" + resultSet.getString(3));
connection.close();
} catch (Exception e) {System.err.println("Exception: "+e.getMessage());}
}
}
Processing Statements
•
Once a connection to a particular database
is established, it can be used to send SQL
statements from your program to the
database.
•
JDBC provides the
Statement,
PreparedStatement, and CallableStatement
interfaces to facilitate sending statements to
a database for execution and receiving
execution results from the database.
The
executeQuery
executeQuery
, and
executeUpdate
executeUpdate
Methods
•
The methods for executing SQL statements are execute,
executeQuery, and executeUpdate, each of which
accepts a string containing a SQL statement as an
argument.
•
This string is passed to the database for execution.
•
The executeQuery method should be used if the
execution produces a single result set, such as the SQL
select statement.
•
The executeUpdate method should be used if the
statement results in a single update count or no update
count, such as a SQL INSERT, DELETE, UPDATE, or DDL
statement.
PreparedStatement
•
The PreparedStatement interface is
designed to execute dynamic SQL
statements and SQL-stored procedures.
•
These SQL statements and stored
procedures are precompiled for efficient use
when repeatedly executed.
PreparedStatement
pstmt = connection.prepareStatement
("insert into Student (firstName, mi, lastName) +values (?, ?, ?)");
Pstmt.setStirng(1, name); ……
Questions
24
Understanding database systems is crucial in today's digital world. Explore the basics of database systems, SQL statements, and Java Database Connectivity (JDBC) in this comprehensive overview. Learn about DBMS, SQL querying, JDBC API, and more to enhance your understanding of managing and interacting with databases effectively.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
E N D
Presentation Transcript
JAVA JDBC Java Database Programming Lamiaa Said
What is a Database System? Application Users Application Programs System Users Database Management System (DBMS) e.g., Access, MySQL, Oracle, and MS SQL Server database
Database Application Systems The application program may use more than one DBMS Application Users Application Programs Database Management System Database Management System database
Examples of Simple SQL Statements Select statement select firstName, mi, lastName from Student where deptId = 'CS'; select firstName, mi, lastName from Student where deptId = 'CS' and zipCode = '31411'; select * from Student where deptId = 'CS' and zipCode = '31411';
Examples of Simple SQL Statements, cont. Insert statement insert into Course (courseId, subjectId, courseNumber, title) values ('11113', 'CSCI', '3720', 'Database Systems', 3);
Examples of Simple SQL Statements, cont. Update statement update Course set numOfCredits = 4 where title = 'Database Systems';
Examples of Simple SQL Statements, cont. Delete statement delete Course where title = 'Database System';
Java Database Connectivity (JDBC) Is a java API that allow java programs to access Database. The JDBC classes are contained in the Java package java.sql
The Architecture of JDBC Java Applications/ Applets JDBC API Oracle JDBC Driver JDBC-ODBC Bridge Driver Oracle ODBC Driver Microsoft ODBC Driver Local or remote ORACLE DB Microsoft Access Database
The JDBC-ODBC Bridge Is a database driver that utilize the ODBC driver to connect the database. This driver translates JDBC method calls into ODBC function calls. ODBC (Open DataBase Connectivity), is used to make it possible to access any data from any application, regardless of which (DBMS) is handling the data
JDBC Drivers A JDBC driver allows a Java application/client to communicate with a SQL database. A JDBC driver is a Java class. A JDBC driver converts program (and typically SQL) requests for a particular database.
The JDBC Interfaces Driver Manager Connection Connection Statement Statement Statement Statement ResultSet ResultSet ResultSet ResultSet
JDBC Drivers A JDBC driver allows a Java application/client to communicate with a SQL database. A JDBC driver is a Java class. A JDBC driver converts program (and typically SQL) requests for a particular database.
Loading Drivers Statement to load a driver: Class.forName("JDBCDriverClass"); or DriverManager.registerDriver(new JDBCDriverClass()); A driver is a class. For example: Database Driver Class Source Access sun.jdbc.odbc.JdbcOdbcDriver Already in JDK MySQL com.mysql.jdbc.Driver Website oracle.jdbc.driver.OracleDriver Website Oracle The JDBC-ODBC driver for Access is bundled in JDK. MySQL driver class is in mysqljdbc.jar Oracle driver class is in classes12.jar
Establishing Connections Connection connection = DriverManager.getConnection(databaseURL); Database URL Pattern Access jdbc:odbc:dataSource MySQL jdbc:mysql://hostname/dbname Oracle jdbc:oracle:thin:@hostname:port#:oracleDBSID
Establishing Connections Examples For Access: Connection connection = DriverManager.getConnection ("jdbc:odbc:ExampleMDBDataSource"); For MySQL: Connection connection = DriverManager.getConnection ("jdbc:mysql://localhost/test"); For Oracle: Connection connection = DriverManager.getConnection ("jdbc:oracle:thin:@liang.armstrong.edu:1521:ora9i" , "scott", "tiger");
Creating and Executing Statements Creating statement: Statement statement = connection.createStatement(); Executing statement (for update, delete, insert): statement.executeUpdate("create table Temp (col1 char(5), col2 char(5))"); Executing statement (for select): ResultSet resultSet = stmt.executeQuery ("select firstName, mi, lastName from Student where lastName " + " = 'Smith'");
Processing ResultSet Executing statement (for select): ResultSet resultSet = stmt.executeQuery ("select firstName, mi, lastName from Student where lastName " + " ='Smith'"); Processing ResultSet (for select): //Iterate through the result and print the student names while (resultSet.next()) { System.out.println(resultSet.getString(1) + " " + resultSet.getString(2) + " " + resultSet.getString(3)); }
Simple JDBC Example import java.sql.*; public class SimpleJdbc { public static void main(String[] args) { try { DriverManager.registerDriver(new com.mysql.jdbc.Driver()); Connection connection = DriverManager.getConnection ("jdbc:mysql://localhost/test"); Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery ("select firstName, mi, lastName from Student where lastName " + " = 'Smith'"); while (resultSet.next()) System.out.println(resultSet.getString(1) + "\t" + resultSet.getString(2) + "\t" + resultSet.getString(3)); connection.close(); } catch (Exception e) { System.err.println("Exception: "+e.getMessage()); } } }
Processing Statements Once a connection to a particular database is established, it can be used to send SQL statements from your program to the database. JDBC provides the Statement, PreparedStatement, and CallableStatement interfaces to facilitate sending statements to a database for execution and receiving execution results from the database.
The executeQuery, and executeUpdate Methods The methods for executing SQL statements are execute, executeQuery, and executeUpdate, each of which accepts a string containing a SQL statement as an argument. This string is passed to the database for execution. The executeQuery method should be used if the execution produces a single result set, such as the SQL select statement. The executeUpdate method should be used if the statement results in a single update count or no update count, such as a SQL INSERT, DELETE, UPDATE, or DDL statement.
PreparedStatement The PreparedStatement interface is designed to execute dynamic SQL statements and SQL-stored procedures. These SQL statements and stored procedures are precompiled for efficient use when repeatedly executed. PreparedStatementpstmt = connection.prepareStatement ("insert into Student (firstName, mi, lastName) + values (?, ?, ?)"); Pstmt.setStirng(1, name);
Questions 24
