Java Database Connectivity JDBC Objective JDBC Overview Setting
Java Database Connectivity (JDBC)
Objective ØJDBC Overview ØSetting up the dev environment ØCRUD operations ØStatement , Prepared and Callable Statement ØJDBC Transactions ØMeta data – Result. Set. Data, Database. Meta. Data ØHandling BLOB, CLOB ØConfiguration and writing utility class ØConnection pooling 17 October 2021
JDBC Overview §What is JDBC §JDBC API Classes and Interfaces §Features of JDBC §Database Support §Architecture §Development Process 17 October 2021
Introduction to JDBC § When your application creates or uses large amount of data, it is usually necessary for that information to be stored in a database. § Widely used databases are : Oracle, My. Sql, Derby, etc SQL: SQL (pronounced "ess-que-el") stands for Structured Query Language. SQL is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems. 17 October 2021
Three tier architecture Presentation tier The top-most level of the application is the user interface. The main function of the interface is to translate tasks and result to something the user can understand. Logic tier This layer coordinates the application, processes commands, makes logical decisions and evaluations, and perform calculations. It also moves and processes data between the two surrounding layers. Data tier Here information is stored and retrieved from a database or file system. The information is the passed back to the logic tier for processing, and then eventually back to the user. 17 October 2021
What is JDBC? §The JDBC API is a Java API, It helps us to connect to a relational database and execute SQL statements against a database 17 October 2021
Features § Provides portable access to various databases o JDBC is a specification provides a complete set of interfaces that allows for portable access to an underlying database § The JDBC API provides a call-level API for SQL-based database access (supports ANSI SQL 2003) § The JDBC API allows Java programming language to exploit “Write Once, Run Anywhere” capabilities
JDBC API Overview § JDBC API is part of JSE: o java. sql o javax. sql § JDBC provides following activities: o Establish a connection with a database o Send SQL statements o Process the result 17 October 2021
JDBC Supporting database 17 October 2021
JDBC Architecture The JDBC API supports both two-tier and three-tier processing models for database access but in general, JDBC Architecture consists of two layers §JDBC API: o This provides the application-to-JDBC manager connection §JDBC Driver API o This supports the JDBC Manager-to-Driver Connection 17 October 2021
JDBC API Key classes and Interfaces § java. sql. Driver. Manager § java. sql. Connection § java. sql. Statment § java. sql. Prepreare. Statement § java. sql. Result. Set § java. sql. SQLExcpetion § javax. sql. Data. Source § javax. sql. Result. Set. Meta. Data 17 October 2021
JDBC Driver Manager § It helps to connect an application to a databased on the database connection string § The driver class will be loaded in JDBC by using Class. for. Name(Driver class name), but from JDBC 4. 0 driver class will be loaded automatically by the Driver. Manager. (Ensure that driver class is in classpath) 17 October 2021
Development Process 1. Get a connection to database 2. Create a Statement object 3. Execute SQL query 4. Process Result Set 5. Release the resource 17 October 2021
Step-1 : Get a connection to database §In order to connect to database o Need a connection string in the form of JDBC URL § Basic syntax: o jdbc: <driver protocol>: <driver connection details> § Example jdbc: mysql: //localhost: 3306/ jdbc: derby: testdb; create=true jdbc: oracle: thin@localhost: 1521: databasename 17 October 2021
Step-1 : Get a connection to database- Example § Code snippet for connecting to My. SQL: import java. sql. Driver. Manager; import java. sql. SQLException; import java. sql. Connetion; private final String URL = "jdbc: mysql: //localhost: 3306/mysql"; private final String USER = "root"; private final String PWD = "manager"; // DB username // DB password Connection connection=Driver. Manager. get. Connection(URL, USER, PWD); 17 October 2021
Step-2: Creating Statement Object // do the necessary imports import java. sql. Statement; // Create statement object Connection connection=Driver. Manager. get. Connection(URL, USER, PWD); Statement st=connection. create. Statement(); 17 October 2021
Step-3: Creating Result. Set Object // Do the necessary imports import java. sql. Statement; import java. sql. Result. Set; //Create Result. Set Object Connection connection=Driver. Manager. get. Connection(URL, USER, PWD); Statement st=connection. create. Statement(); Result. Set rs=st. execute. Query(“select * from products”); 17 October 2021
Step-4: Processing the Result. Set § Result. Set is initially placed before first row § Result. Set – next() method return true if there are more rows to process and cursor move to next row in the forward direction § Result. Set – get. XXX(int index) or get. XXX(String col. Name) are used for processing the columns Result. Set rs=st. execute. Query("select * from products"); while(rs. next()){ String name=rs. get. String("pname"); double price=rs. get. Double("price"); } 17 October 2021 // Retrieving data from prodcts table
Setp-5: Releasing Resources § Once processing of the Result. Set is done then the resources (Connection, Statement etc…) which are open should be released § It is good practice release this resources in the finally block finally{ …… if(rs!=null) rs. close(); //closing Result. Set if(st!=null) st. close(); // closing Statement if(con!=null) con. close(); // closing Connection …… } 17 October 2021
THANK YOU 17 October 2021
- Slides: 20