Introduction to Database Systems and SQL Programming
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
