![\"\"](\"https:\/\/prepbytes-misc-images.s3.ap-south-1.amazonaws.com\/assets\/1672834024788-JDBC%20Program%20in%20Java.jpg\")
<\/p>\n<\/p>\n
JDBC in Java stands for Java Database Connectivity, representing a progression from ODBC (Open Database Connectivity). JDBC serves as a standardized API specification designed to facilitate the transfer of data from the frontend to the backend. This API encompasses Java-written classes and interfaces, essentially serving as a conduit \u2013 distinct from the Java programming concept of an interface \u2013 that bridges your Java program and databases. This bridge establishes a connection between the two entities, enabling programmers to transmit data from Java code and securely store it within the database for subsequent utilization.<\/p>\n
Steps to implement JDBC in Java<\/p>\n
We need to import the existing packages for the JDBC program so that the required APIs are available for the program. First, we should load\/register the driver in the program before connecting to the Database. You need to register it only once per database in the program. After loading the 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.<\/p>\n 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.<\/p>\n The getConnection() method is an overloaded method. The 2 methods are:<\/strong><\/p>\n 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.<\/p>\n 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.<\/p>\n There are 3 statement interfaces are available in the java.sql package. These are explained below:<\/strong><\/p>\n a) Statement<\/strong> b) PreparedStatement<\/strong> 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.<\/p>\n c) CallableStatement<\/strong> The CallableStatement instance is created by calling the prepareCall method of the Connection object.<\/p>\n There are 4 important methods to execute the query in Statement interface. These are explained below:<\/strong><\/p>\n a) ResultSet executeQuery(String sql)<\/strong> b) executeUpdate(String sql)<\/strong> c) execute(String sql)<\/strong> d) executeBatch()<\/strong> When we 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.<\/p>\n We can use the executeQuery() method for the SELECT query. When someone tries to execute the insert\/update query, it will throw SQLExecption with the message 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.<\/p>\n 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.<\/p>\n We will get to know more about the ResultSet in the next tutorial.<\/p>\n Finally, we are done with manipulating data in DB. Now we can close the JDBC connection. We need to make sure that we have closed the resource after we have used it. If we don\u2019t close them properly we may end up out of connections.<\/p>\n When we close the connection object, Statement and ResultSet objects will be closed automatically.<\/p>\n 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.<\/p>\n Once the execution exits the try block, it will automatically close the connection. In this case, we don\u2019t need to close the connection by calling conn.close method in the Java program.<\/p>\n In this example, you will see how to implement the 6 basic steps to connect with database using JDBC in Java program.<\/p>\n Before that, first, create one table and add some entries into it.<\/p>\n Below is the SQL query to create a table.<\/strong><\/p>\n Created the \u201cemployee_details\u201d table in Oracle DB.<\/p>\n Using the following queries, insert the data into the \u201cemployee_details\u201d table.<\/p>\n Download the JDBC jar file and import it into the Java project.<\/strong><\/p>\n\t\t\t\t\t\t
\nIrrespective of the JDBC Driver, add the following import statement in the Java program.<\/strong><\/p>\nimport java.sql.*;\nImport the other classes based on the functionality which you will use in the program. <\/code><\/pre>\n2) Load Driver<\/h2>\n
\nWe can load the driver in the following 2 ways:<\/p>\n\n
3) Establish Connection<\/h2>\n
\n
4) Create And Execute Statement<\/h2>\n
(i) Create Statement<\/h3>\n
\nThis interface is used to implement simple SQL statements with no parameter. It returns the ResultSet object.<\/p>\nStatement statemnt1 = conn.createStatement();<\/code><\/pre>\n
\nThis 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.<\/p>\nString select_query = \u201cSelect * from states where state_id = 1\u201d;\nPreparedStatement prpstmt = conn.prepareStatement(select_query);<\/code><\/pre>\n
\nCallableStatement 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.<\/p>\nCallableStatementcallStmt = con.prepareCall(\"{call procedures(?,?)}\");<\/code><\/pre>\n(ii) Execute The Query<\/h3>\n
\n
\nThe 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.<\/p>\n
\nThe 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.<\/p>\n
\nThe execute() method is used to execute the SQL query. It returns true<\/strong> if it executes the SELECT query. And, it returns false<\/strong> if it executes INSERT or UPDATE query.<\/p>\n
\nThis 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.<\/p>\n5) Retrieve Results<\/h2>\n
ResultSet rs 1= statemnt1.executeQuery(QUERY));<\/code><\/pre>\n
\n\u201cexecuteQuery method can not be used for update\u201d.<\/strong><\/p>\n6) Close Connection<\/h2>\n
conn.close();<\/code><\/pre>\ntry(Connection conn = DriverManager.getConnection(url, user, password))\n{ \n \/\/database connection and operation\n}<\/code><\/pre>\nJDBC Program in Java connection Example<\/h2>\n
Create Table<\/h3>\n
create table employee_details (empNum number(10), lastName varchar(50), firstName varchar(50), email varchar(255) , deptNum number(10), salary number(10));<\/code><\/pre>\n![\"\"](\"https:\/\/prepbytes-misc-images.s3.ap-south-1.amazonaws.com\/assets\/1672836187774-JDBC%20Program%20in%20Java1.png\")
<\/p>\nInsert Data Into Table<\/h3>\n
insert into employee_details values (1001, 'Luther', 'Martin', 'ml@gmail.com', 1, 13000);\ninsert into employee_details values (1002, 'Murray', 'Keith', 'km@gmail.com', 2, 25000);\ninsert into employee_details values (1003, 'Branson', 'John', 'jb@gmail.com', 3, 15000);\ninsert into employee_details values (1004, 'Martin', 'Richard', 'rm@gmail.com', 4, 16000);\ninsert into employee_details values (1005, 'Hickman', 'David', 'dh@gmail.com', 5, 17000);<\/code><\/pre>\n![\"\"](\"https:\/\/prepbytes-misc-images.s3.ap-south-1.amazonaws.com\/assets\/1672836243455-JDBC%20Program%20in%20Java2.png\")
<\/p>\nJava JDBC Program<\/h2>\n