Declarative Programming Paradigm: Java Database Connectivity

Declarative Programming Paradigm: Java Database Connectivity

• 1.
  Declarative Programming Paradigm: JavaDatabase Connectivity (JDBC) Connectivity with MySQL — Query Execution
• 2.
  JDBC • JDBC standsfor Java Database Connectivity. JDBC is a Java API to connect and execute the query with the database. It is a part of JavaSE (Java Standard Edition). JDBC API uses JDBC drivers to connect with the database. • JDBC API to access tabular data stored in any relational database. By the help of JDBC API, we can save, update, delete and fetch data from the database.
• 3.
  JDBC Connection Steps •There are 6 basic steps to connect with JDBC.
• 4.
  Import Packages • First,to import the existing packages to use it in our Java program. Import will make sure that JDBC API classes are available for the program. We can then use the classes and subclasses of the packages. import java.sql.*; JDBC API 4.0 mainly provides 2 important packages: • java.sql • javax.sql
• 5.
  java.sql package • Thispackage provides classes and interfaces to perform most of the JDBC functions like creating and executing SQL queries.
• 6.
  javax.sql package • Itis a JDBC extension API and provides server-side data access and processing in Java Program.
• 7.
  Load Driver First, weshould load/register the driver in the program before connecting to the Database. You need to register it only once per database in the program. We can load the driver in the following 2 ways: 1.Class.forName() 2.DriverManager.registerDriver()
• 8.
  Class.forName() • In thisway, the driver’s class file loads into the memory at runtime. It implicitly loads the driver. While loading, the driver will register with JDBC automatically.
• 9.
  DriverManager.registerDriver() DriverManager is aninbuilt class that is available in the java.sql package. It acts as a mediator between Java application and database which you want to connect. Before you connect with the database, you need to register the driver with DriverManager. The main function of DriverManager is to load the driver class of the Database and create a connection with DB. • Public static void registerDriver(driver) – This method will register the driver with the Driver Manager. If the driver is already registered, then it won’t take any action. • It will throw SQLException if the database error occurs. • It will throw NullPointerException if the driver is null. DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()) DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver())
• 10.
  Establish Connection After loadingthe driver, the next step is to create and establish the connection. Once required, packages are imported and drivers are loaded and registered, then we can go for establishing a Database connection. DriverManager class has the getConnection method, we will use this method to get the connection with Database. To call getConnection() method, we need to pass 3 parameters. The 3 parameters are string data type URL, a username, and a password to access the database. • The getConnection() method is an overloaded method. The 2 methods are: • getConnection(URL,username,password); – It has 3 parameters URL, username, password. • getConnection(URL); – It has only one parameter. URL has a username and password also.
• 11.
  Establish Connection • Thefollowing table lists the JDBC connection strings for the different databases: • Example: • Connection con = DriverManager.getConnection(jdbc:oracle:thin:@ localhost:1521:xe,System,Pass123@) • Here in this example, • thin refers to the Driver type. • localhost is where the Oracle database is running. • 1521 is the port number to connect to DB. • xe – SID • System – User name to connect to the Oracle Database. • Pass123@ – Password
• 12.
  Create And ExecuteStatement Once the connection has established, we can interact with the connected Database. First, we need to create the statement to perform the SQL query and then execute the statement. (i) Create Statement Now we will create the statement object that runs the query with the connected database. We use the createStatement method of the Connection class to create the query. There are 3 statement interfaces are available in the java.sql package. These are explained below: a) Statement This interface is used to implement simple SQL statements with no parameter. It returns the ResultSet object. Statement statemnt1 = conn.createStatement();
• 13.
  Create And ExecuteStatement b) PreparedStatement This PreparedStatement interface extends the Statement interface. So, it has more features than the Statement interface. It is used to implement parameterized and precompiled SQL statements. The performance of the application increases because it compiles the query only once. • It is easy to reuse this interface with a new parameter. It supports the IN parameter. Even we can use this statement without any parameter. String select_query = “Select * from states where state_id = 1”; PreparedStatement prpstmt = conn.prepareStatement(select_query);
• 14.
  Create And ExecuteStatement c) CallableStatement CallableStatement interface extends the PreparedStatement interface. So, it has more features than the PreparedStatement interface. It is used to implement a parameterized SQL statement that invokes procedure or function in the database. A stored procedure works like a method or function in a class. It supports the IN and OUT parameters. • The CallableStatement instance is created by calling the prepareCall method of the Connection object. CallableStatementcallStmt = con.prepareCall("{call procedures(?,?)}");
• 15.
  Create And ExecuteStatement (ii) Execute The Query There are 4 important methods to execute the query in Statement interface. These are explained below: • ResultSet executeQuery(String sql) • int executeUpdate(String sql) • boolean execute(String sql) • int []executeBatch() a) ResultSet executeQuery(String sql) The executeQuery() method in Statement interface is used to execute the SQL query and retrieve the values from DB. It returns the ResultSet object. Normally, we will use this method for the SELECT query. b) executeUpdate(String sql) The executeUpdate() method is used to execute value specified queries like INSERT, UPDATE, DELETE (DML statements), or DDL statements that return nothing. Mostly, we will use this method for inserting and updating. c) execute(String sql) The execute() method is used to execute the SQL query. It returns true if it executes the SELECT query. And, it returns false if it executes INSERT or UPDATE query. d) executeBatch() This method is used to execute a batch of SQL queries to the Database and if all the queries get executed successfully, it returns an array of update counts. We will use this method to insert/update the bulk of records.
• 16.
  Retrieve Results • Whenwe execute the queries using the executeQuery() method, the result will be stored in the ResultSet object. • The returned ResultSet object will never be null even if there is no matching record in the table. ResultSet object is used to access the data retrieved from the Database. ResultSet rs 1= statemnt1.executeQuery(QUERY)); • The executeQuery() method for the SELECT query. When someone tries to execute the insert/update query, it will throw SQLExecption with the message “executeQuery method can not be used for update”. • A ResultSet object points to the current row in the Resultset. To iterate the data in the ResultSet object, call the next() method in a while loop. If there is no more record to read, it will return FALSE. • ResultSet can also be used to update data in DB. We can get the data from ResultSet using getter methods such as getInt(), getString(), getDate(). We need to pass the column index or column name as the parameter to get the values using Getter methods.
• 17.
  Close Connection • Toclose the JDBC connection. need to make sure that we have closed the resource after we have used it. If we don’t close them properly we may end up out of connections. • When we close the connection object, Statement and ResultSet objects will be closed automatically. conn.close(); • From Java 7 onwards, we can close the JDBC connections automatically using a try-catch block. JDBC connection should be opened in the parenthesis of the try block. Inside the try block, you can do the database connections normally as we do. • Once the execution exits the try block, it will automatically close the connection. In this case, we don’t need to close the connection by calling conn.close method in the Java program. try(Connection conn = DriverManager.getConnection(url, user, password)) { //database connection and operation }
• 18.
  Java JDBC ConnectionExample Implement the 6 basic steps to connect with database using JDBC in Java program. Create Table • Before that, first, create one table and add some entries into it. • Below is the SQL query to create a table. create table employee_details (empNum number(10), lastName varchar(50), firstName varchar(50), email varchar(255) , deptNum number(10), salary number(10)); Insert Data Into Table • Using the following queries, insert the data into the “employee_details” table. insert into employee_details values (1001, 'Luther', 'Martin', 'ml@gmail.com', 1, 13000); insert into employee_details values (1002, 'Murray', 'Keith', 'km@gmail.com', 2, 25000); insert into employee_details values (1003, 'Branson', 'John', 'jb@gmail.com', 3, 15000); insert into employee_details values (1004, 'Martin', 'Richard', 'rm@gmail.com', 4, 16000); insert into employee_details values (1005, 'Hickman', 'David', 'dh@gmail.com', 5, 17000);
• 19.
  Java Program -Oracle import java.sql.*; public class Sample_JDBC_Program { public static void main(String[] args) throws ClassNotFoundException, SQLException { // store the SQL statement in a string String QUERY = "select * from employee_details“ //register the oracle driver with DriverManager Class.forName("oracle.jdbc.driver.OracleDriver"); /*Here we have used Java 8 so opening the connection in try statement*/ try(Connection conn = DriverManager.getConnection("jdbc:oracle:thin:system/pass123@loc alhost:1521:XE")) { Statement statemnt1 = conn.createStatement(); //Created statement and execute it ResultSet rs1 = statemnt1.executeQuery(QUERY); { //Get the values of the record using while loop while(rs1.next()) { int empNum = rs1.getInt("empNum"); String lastName = rs1.getString("lastName"); String firstName = rs1.getString("firstName"); String email = rs1.getString("email"); String deptNum = rs1.getString("deptNum"); String salary = rs1.getString("salary"); //store the values which are retrieved using ResultSet and print it System.out.println(empNum + "," +lastName+ "," +firstName+ "," +email +","+deptNum +"," +salary); } } } catch (SQLException e) { //If exception occurs catch it and exit the program e.printStackTrace(); } } }
• 20.
  Java Program -Mysql import java.sql.*; public class javamysql { public static void main(String arg[]) { Connection connection = null; try { // below two lines are used for connectivity. Class.forName("com.mysql.cj.jdbc.Driver"); connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb","mydbuser", "mydbuser"); // mydb is database; mydbuser is name of database ;mydbuser is password of database Statement statement; statement = connection.createStatement(); ResultSet resultSet; resultSet = statement.executeQuery("select * from designation"); int code; String title; while (resultSet.next()) { code = resultSet.getInt("code"); title = resultSet.getString("title").trim(); System.out.println("Code : " + code + " Title : " + title); } resultSet.close(); statement.close(); connection.close(); } catch (Exception exception) { System.out.println(exception); } } }