Java Database Connectivity (JDBC)

1. Java Database Connectivity (JDBC)

2. Objective • JDBC Overview • Setting up the dev environment • CRUD operations • Statement , Prepared and Callable Statement • JDBC Transactions • Meta data – ResultSetData, DatabaseMetaData • Handling BLOB, CLOB • Configuration and writing utility class • Connection pooling

3. JDBC Overview • What is JDBC • JDBC API Classes and Interfaces • Features of JDBC • Database Support • Architecture • Development Process

4. 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, MySql, 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.

5. 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.

6. 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

7. Features • Provides portable access to various databases • 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 SQL2003) • The JDBC API allows Java programming language to exploit “Write Once, Run Anywhere” capabilities

8. JDBC API Overview • JDBC API is part of JSE: • java.sql • javax.sql • JDBC provides following activities: • Establish a connection with a database • Send SQL statements • Process the result

9. JDBC Supporting database

10. 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: • This provides the application-to-JDBC manager connection • JDBC Driver API • This supports the JDBC Manager-to-Driver Connection

11. JDBC API Key classes and Interfaces • java.sql.DriverManager • java.sql.Connection • java.sql.Statment • java.sql.PrepreareStatement • java.sql.ResultSet • java.sql.SQLExcpetion • javax.sql.DataSource • javax.sql.ResultSetMetaData

12. JDBC Driver Manager • It helps to connect an application to a database based on the database connection string • The driver class will be loaded in JDBC by using Class.forName(Driver class name), but from JDBC 4.0 driver class will be loaded automatically by the DriverManager. (Ensure that driver class is in classpath)

13. Development Process • Get a connection to database • Create a Statement object • Execute SQL query • Process Result Set • Release the resource

14. Step-1 : Get a connection to database • In order to connect to database • Need a connection string in the form of JDBC URL • Basic syntax: • jdbc:<driver protocol>:<driver connection details> • Example jdbc:mysql://localhost:3306/ jdbc:derby:testdb;create=true jdbc:oracle:thin@localhost:1521:databasename

15. Step-1 : Get a connection to database- Example • Code snippet for connecting to MySQL: • import java.sql.DriverManager; • import java.sql.SQLException; • import java.sql.Connetion; private final String URL = "jdbc:mysql://localhost:3306/mysql"; private final String USER = "root"; // DB username private final String PWD = "manager"; // DB password • Connection connection=DriverManager.getConnection(URL, USER, PWD);

16. Step-2: Creating Statement Object // do the necessary imports • import java.sql.Statement; // Create statement object • Connection connection=DriverManager.getConnection(URL, USER, PWD); • Statement st=connection.createStatement();

17. Step-3: Creating ResultSet Object // Do the necessary imports • import java.sql.Statement; import java.sql.ResultSet; //Create ResultSet Object • Connection connection=DriverManager.getConnection(URL, USER, PWD); • Statement st=connection.createStatement(); • ResultSetrs=st.executeQuery(“select * from products”);

18. Step-4: Processing the ResultSet • ResultSet is initially placed before first row • ResultSet – next() method return true if there are more rows to process and cursor move to next row in the forward direction • ResultSet – getXXX(int index) or getXXX(String colName) are used for processing the columns ResultSetrs=st.executeQuery("select * from products"); // Retrieving data from prodcts table while(rs.next()){ String name=rs.getString("pname"); double price=rs.getDouble("price"); }

19. Setp-5: Releasing Resources • Once processing of the ResultSet 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 ResultSet if(st!=null) st.close(); // closing Statement if(con!=null) con.close(); // closing Connection …… }

20. THANK YOU