PR3 Ch04 Accessing Databases with JDBC PDF

Programming 3 CSCI 2308 Chapter 4 Accessing Databases with JDBC  Topics Covered  Introduction  Structured Query Language (SQL)  The Design of JDBC  Registering the Driver Class  Connecting to the Database  Executing SQL Statements  Scrollable & Updatable Re...

Programming 3 CSCI 2308 Chapter 4 Accessing Databases with JDBC  Topics Covered  Introduction  Structured Query Language (SQL)  The Design of JDBC  Registering the Driver Class  Connecting to the Database  Executing SQL Statements  Scrollable & Updatable Result Sets  Metadata  Display Data In JavaFX TableView Jan 19, 2025 Created by: Dr. Abdelkareem Alashqar 2  Introduction  A database is an organized collection of data. There are many different strategies for organizing data to facilitate easy access and manipulation.  A Database Management System (DBMS) provides mechanisms for storing, organizing, retrieving and modifying data for many users.  DBMS allows for the access and storage of data without concern for the internal representation of data. Jan 19, 2025 Created by: Dr. Abdelkareem Alashqar 3  Relational Database  A relational database is a logical representation of data that allows the data to be accessed without consideration of its physical structure  A relational database stores data in tables  Tables are composed of rows, each describing a single entity (such as an employee)  Rows are composed of columns in which values are stored.  Primary key is a column (or group of columns) with a value (not null) that is unique for each row.  Foreign key is a column in a table that matches the primary- key column in another table (referential integrity rule). Jan 19, 2025 Created by: Dr. Abdelkareem Alashqar 4  Entity-Relationship (ER) Diagram  Shows the database tables (entities) and the relationships among them. Employee Employee_Project Project EmployeeID 1 ∞ EmployeeID ∞ 1 ProjectID EName ProjectID PName Department WorkedHours Location Salary Primary Key Foreign Key Jan 19, 2025 Created by: Dr. Abdelkareem Alashqar 5  Employee Table: Sample Data ID Name Department Salary 2301 Ali 211 1200 2417 Huda 211 1400 Row { 4622 Maher 641 950 7810 Ahmad 431 1500 5196 Marwa 641 800 { { Primary Key Column Jan 19, 2025 Created by: Dr. Abdelkareem Alashqar 6  Structured Query Language (SQL)  Structured Query Language (SQL) is a non-procedural language to access and manipulate the relational databases.  Data Definition Language (DDL) Create, Alter, Drop database objects ̶ Tables, Views, Sequences, …etc  Data Manipulation Language (DML) Insert, Update, Delete data to/from the tables  Data Query Language (DQL) Select, to retrieve data from the tables Jan 19, 2025 Created by: Dr. Abdelkareem Alashqar 7  Structured Query Language (SQL) SQL keyword Description SELECT Retrieves data from one or more tables. FROM Tables involved in the query. Required in every SELECT. WHERE Criteria for selection that determine the rows to be retrieved, deleted or updated. Optional in a SQL query or a SQL statement. GROUP BY Criteria for grouping rows. Optional in a SELECT query. ORDER BY Criteria for ordering rows. Optional in a SELECT query. INNER JOIN Merge rows from multiple tables. INSERT Insert rows into a specified table. UPDATE Update rows in a specified table. DELETE Delete rows from a specified table. Jan 19, 2025 Created by: Dr. Abdelkareem Alashqar 8  The Design of JDBC  Java Database Connectivity (JDBC) is an application program interface (API).  JDBC lets developers connect to a database Java Application and then use SQL. JDBC Driver Manager  Using JDBC API enables developers to change the underlying DBMS without Vender-Supported JDBC Driver modifying the Java code that accesses the database.  The ultimate goals of JDBC is to make DB possible the following: Enable Java applications to connect to database Enable programmers to manipulate databases Jan 19, 2025 Created by: Dr. Abdelkareem Alashqar 9  JDBC Configuration  Java programmers needs to gather a number of items before they can write the database program. Registering the Driver Class ̶Each database has its own driver class that is registered by Class.forName() method Connecting to the Database ̶Each database has its URL for Connection Executing SQL Statements ̶Followed by fetching the ResultSet object Jan 19, 2025 Created by: Dr. Abdelkareem Alashqar 10  Registering the Driver Class  Class.forName() method must be used to register the target database into the Java program. Class.forName("C") returns the Class object associated with the "C" class. Java developer must download the suitable driver,.jar file, and add it into the application reference libraries to be able to load the suitable driver.  Examples Driver class for MySQL ̶ Class.forName("com.mysql.jdbc.Driver"); ̶ Class.forName("com.mysql.cj.jdbc.Driver"); //new Driver class for Oracle ̶ Class.forName("oracle.jdbc.driver.OracleDriver"); Driver class for MS SQL Server ̶ Class.forName("com.microsoft.sqlserver.jdbc.SQLSer verDriver"); Jan 19, 2025 Created by: Dr. Abdelkareem Alashqar 11  Connecting to the Database  Java provides a special URL for each DBMS to enable the programmers to connect the desired database.  Database URLs When connecting to a database, you must use various database-specific parameters such as host name, port number, and database name. The following table (next slide) contains the most used databases URLs Jan 19, 2025 Created by: Dr. Abdelkareem Alashqar 12  Connecting to the Database (cont.) RDBMS Database URL format MySQL jdbc:mysql://hostname:portNumber/databaseName ORACLE jdbc:oracle:thin:@hostname:portNumber:databaseName DB2 jdbc:db2:hostname:portNumber/databaseName PostgreSQL jdbc:postgresql://hostname:portNumber/databaseName Java DB/Apache jdbc:derby:dataBaseName (embedded) Derby jdbc:derby://hostname:portNumber/databaseName (network) Microsoft SQL Server jdbc:sqlserver://hostname:portNumber;databaseName=dataBaseName Sybase jdbc:sybase:Tds:hostname:portNumber/databaseName Jan 19, 2025 Created by: Dr. Abdelkareem Alashqar 13  Connecting to the Database (cont.)  Connecting the Database In Java program, database connection can be opened using Connection class (from the package java.sql) with code that is similar to the following example: String url = "jdbc:mysql://127.0.0.1:3306/myDB"; String username = "root"; String password = "toor"; Connection conn = DriverManager.getConnection(url,username,password); Jan 19, 2025 Created by: Dr. Abdelkareem Alashqar 14  Executing SQL Statements  To execute a SQL statement,  1. Create a Statement object. To create statement objects, use the Connection object that you obtained from the call to DriverManager.getConnection() Connection conn = DriverManager.getConnection(…); Statement stat = conn.createStatement();  2. Execute the Statement. To execute Statement call method which take SQL statement as string argument. ̶ ResultSet executeQuery(): to exectue DQL statement ̶ int executeUpdate(): to execute DDL/DML statement Jan 19, 2025 Created by: Dr. Abdelkareem Alashqar 15  Executing SQL Statements (cont.)  To execute a SQL statement:  First place the statement that you want to execute into a String.  For example: String str = "UPDATE employee" + "SET salary = salary + salary*0.05" + " WHERE id = 7876";  Then call the suitable execute method of the Statement class.  In the above example executeUpdate() is used. Jan 19, 2025 Created by: Dr. Abdelkareem Alashqar 16  Executing SQL Statements (cont.)  executeUpdate(str); It can execute SQL actions such as INSERT, UPDATE, and DELETE It also can execute DDL such as CREATE table and DROP table. It returns a count of rows that were affected by the SQL statement, or zero for statements that do not return a row count. Jan 19, 2025 Created by: Dr. Abdelkareem Alashqar 17  Executing SQL Statements (cont.)  executeQuery(str); It can execute SELECT queries. It returns an object of type ResultSet that be used to walk through the result one row at a time. ResultSet rs = stat.executeQuery("SELECT * FROM Fetching the ResultSet employee"); while ( rs.next() ) { //look at a row of the result set int eID = rs.getInt("id"); String eName = rs.getString("name"); double eSalary = rs.getDouble("salary"); System.out.println(eID+"\t "+eName+"\t "+eSalary); } Jan 19, 2025 Created by: Dr. Abdelkareem Alashqar 18  Scrollable & Updatable Result Sets  Connection method createStatement with two arguments receives the result set type and the result set concurrency.  To obtain scrollable and updatable result sets from queries use the following Statement object: Statement stat = conn.createStatement(type, concurrency); type: determine the result set scrolling type ̶Scrolling: let result set cursor to move backward as well as forward concurrency: determine if the result set can be used to update the database or not Jan 19, 2025 Created by: Dr. Abdelkareem Alashqar 19  Scrollable & Updatable Result Sets (cont.)  The resultset type values: TYPE_FORWARD_ONLY The result set is NOT scrollable (default). TYPE_SCROLL_INSENSITIVE The result set is scrollable but NOT sensitive to database changes. TYPE_SCROLL_SENSITIVE The result set is scrollable and sensitive to database changes  The resultset concurrency values: CONCUR_READ_ONLY The result set CANNOT be used to update the database (default). CONCUR_UPDATABLE The result set CAN be used to update the database. Jan 19, 2025 Created by: Dr. Abdelkareem Alashqar 20 Scrollable & Updatable Result Sets (cont.)  Example: Statement stat = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);  After executing the query: ResultSet rs = stat.executeQuery(query)  rs is a scrollable result set and it has a cursor that indicates the current position. Jan 19, 2025 Created by: Dr. Abdelkareem Alashqar 21 Scrollable & Updatable Result Sets (cont.)  Now, the cursor can be moved backward or forward by a number of rows by using the relative() method rs.relative(n); If n is positive, the cursor moves forward. If n is negative, it moves backward. If n is zero, the call has NO effect. If n outside the current set of rows then the cursor does not move and the method returns false. Jan 19, 2025 Created by: Dr. Abdelkareem Alashqar 22 Scrollable & Updatable Result Sets (cont.)  Alternatively, the cursor can be moved backward or forward by a number of rows by using the absolute() method to set the cursor to a particular row number rs.absolute(n);  To move to the first row n = 1, and to move the last row n = -1.  To get the current row number with the call getRow() method int currentRow = rs.getRow();  If the return value is 0, the cursor is not currently on a row It is either before the first row or after the last row.  The convenience methods: first() & last() are also used. Jan 19, 2025 Created by: Dr. Abdelkareem Alashqar 23 Scrollable & Updatable Result Sets (cont.)  ResultSet.CONCUR_UPDATABLE means that the result set data can be edit and the changes automatically reflected in the database.  Example 1: To raise (update) the salary for some employees String query = "SELECT * FROM employee"; ResultSet rs = stat.executeQuery(query); while (rs.next()) { if (...) { double increase =... ; double s = rs.getDouble("salary"); rs.updateDouble("salary", s + increase); rs.updateRow(); } Jan 19, 2025 } Created by: Dr. Abdelkareem Alashqar 24 Scrollable & Updatable Result Sets (cont.)  Example 2: Add a new row to the database rs.moveToInsertRow(); rs.updateInt("id", eID); rs.updateString("name", eName); rs.updateString("department", eDepartment); rs.updateDouble("salary", eSalary); rs.insertRow();  Example 3: To delete a row under the cursor rs.deleteRow(); The deleteRow() method immediately removes the row from both the result set and the database. Jan 19, 2025 Created by: Dr. Abdelkareem Alashqar 25  Metadata  JDBC can give additional information about the structure of a database and its tables.  It can give a list of the tables in a particular database or the column names and types of a table.  To find out more about the database, you request an object of type DatabaseMetaData from the database connection. DatabaseMetaData meta = connection.getMetaData(); Jan 19, 2025 Created by: Dr. Abdelkareem Alashqar 26  Metadata (cont.)  ResultSetMetaData getMetaData() Gives the metadata associated with the current ResultSet columns. ̶ ResultSet rs = stat.executeQuery("SELECT * FROM employee"); ̶ ResultSetMetaData rsMetaData = rs.getMetaData();  int getColumnCount() Returns the number of columns in the current ResultSet object ̶ int ColumnsCount = rsMetaData.getColumnCount();  String getColumnName(int column) Gives the column name associated with the column index specified ̶ String columnName = rsMetaData.getColumnName(i);  String getColumnClassName(int column) Gives the column datatype associated with the column index specified ̶ String columnType = rsMetaData.getColumnClassName(i); Jan 19, 2025 Created by: Dr. Abdelkareem Alashqar 27  Display Data in JavaFX TableView  TableView is a control that displays data in rows and columns in a two-dimensional grid  TableView, TableColumn, and TableCell are used to display and manipulate a table.  TableView displays a table.  TableColumn defines the columns in a table.  TableCell represents a cell in the table.  Creating a TableView is a multistep process.  1. Create an instance of TableView and associate data with the TableView  2. Create columns using the TableColumn class and set a column cell value factory to specify how to populate all cells within a single TableColumn Jan 19, 2025 Created by: Dr. Abdelkareem Alashqar 28  TableView App Example public void start(Stage primaryStage) throws Exception { TableView tblView = new TableView(); TableColumn tcID = new TableColumn("ID"); tcID.setCellValueFactory(new PropertyValueFactory("id")); TableColumn tcName = new TableColumn("Name"); tcName.setCellValueFactory(new PropertyValueFactory("name")); TableColumn tcDept = new TableColumn("Department"); tcDept.setCellValueFactory(new PropertyValueFactory("department")); TableColumn tcSalary= new TableColumn("Salary"); tcSalary.setCellValueFactory(new PropertyValueFactory("salary")); Jan 19, 2025 Created by: Dr. Abdelkareem Alashqar 29  TableView App Example (cont.) ObservableList employees = FXCollections.observableArrayList(); public class Employee { private Integer id; private String name; Button buttonView = new Button("Show"); private String buttonView.setOnAction(event -> { department; ResultSet rs; private Double salary; employees.clear(); public Student() { } try {... //Setters and Getters } Class.forName("com.mysql.cj.jdbc.Driver"); Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/ company","root","toor"); Statement = connection.createStatement(); rs = statement.executeQuery("Select * From Employee"); while(rs.next()){ Employee employee = new Employee(); employee.setID(rs.getInt("id")); Continued … Jan 19, 2025 Created by: Dr. Abdelkareem Alashqar 30  TableView App Example (cont.) employee.setName(rs.getString("name")); employee.setDepartment(rs.getString("department")); employee.setSalary(rs.getDouble("salary")); employees.add(employee); } } catch (SQLException ex) { ex.printStackTrace(); } tableView.setItems(employees); }); //End of button action VBox vBox = new VBox(10, tableView, buttonView); vBox.setAlignment(Pos.CENTER); vBox.setPadding(new Insets(10, 10, 10, 10)); Scene = new Scene(vBox, 300, 250); primaryStage.setTitle("TableView App"); primaryStage.setScene(scene); primaryStage.show(); Jan 19, 2025 Created by: Dr. Abdelkareem Alashqar 31  Display Data in JavaFX TableView  The program creates a TableView. The TableView class is a generic class whose concrete type is Employee.  Therefore, this TableView is for displaying Employee.  The table data is an ObservableList< Employee >.  The program creates a TableColumn for each column in the table.  A PropertyValueFactory object is created and set for each column.This object is used to populate the data in the column.  The PropertyValueFactory class is a generic class. ̶ S is for the class displayed in the TableView, and ̶ T is the class for the values in the column.  The PropertyValueFactory object associates a property in class S with a column. Jan 19, 2025 Created by: Dr. Abdelkareem Alashqar 32  Display Data in JavaFX TableView  The program creates an ObservableList and associates it with the TableView.  This ObservableList is filled with data from the table "employee" of "company" database.  When you create a table in a JavaFX application, it is a best practice to define the data model in a class.  The Employee class defines the data for TableView.  Each property in the class defines a column in the table.  This property should be defined as binding property with the getter and setter methods for the value. Jan 19, 2025 Created by: Dr. Abdelkareem Alashqar 33  That’s it! Jan 19, 2025 Created by: Dr. Abdelkareem Alashqar 34