Category: jdbc

JDBC – Batch Processing ”; Previous Next Batch Processing allows you to group related SQL statements into a batch and submit them with one call to the database. When you send several SQL statements to the database at once, you reduce the amount of communication overhead, thereby improving performance. JDBC drivers are not required to support this feature. You should use the DatabaseMetaData.supportsBatchUpdates() method to determine if the target database supports batch update processing. The method returns true if your JDBC driver supports this feature. The addBatch() method of Statement, PreparedStatement, and CallableStatement is used to add individual statements to the batch. The executeBatch() is used to start the execution of all the statements grouped together. The executeBatch() returns an array of integers, and each element of the array represents the update count for the respective update statement. Just as you can add statements to a batch for processing, you can remove them with the clearBatch() method. This method removes all the statements you added with the addBatch() method. However, you cannot selectively choose which statement to remove. Batching with Statement Object Here is a typical sequence of steps to use Batch Processing with Statement Object − Create a Statement object using either createStatement() methods. Set auto-commit to false using setAutoCommit(). Add as many as SQL statements you like into batch using addBatch() method on created statement object. Execute all the SQL statements using executeBatch() method on created statement object. Finally, commit all the changes using commit() method. Example The following code snippet provides an example of a batch update using Statement object − // Create statement object Statement stmt = conn.createStatement(); // Set auto-commit to false conn.setAutoCommit(false); // Create SQL statement String SQL = “INSERT INTO Employees (id, first, last, age) ” + “VALUES(200,”Zia”, ”Ali”, 30)”; // Add above SQL statement in the batch. stmt.addBatch(SQL); // Create one more SQL statement String SQL = “INSERT INTO Employees (id, first, last, age) ” + “VALUES(201,”Raj”, ”Kumar”, 35)”; // Add above SQL statement in the batch. stmt.addBatch(SQL); // Create one more SQL statement String SQL = “UPDATE Employees SET age = 35 ” + “WHERE id = 100”; // Add above SQL statement in the batch. stmt.addBatch(SQL); // Create an int[] to hold returned values int[] count = stmt.executeBatch(); //Explicitly commit statements to apply changes conn.commit(); For a better understanding, let us study the Batching – Example Code. Batching with PrepareStatement Object Here is a typical sequence of steps to use Batch Processing with PrepareStatement Object − Create SQL statements with placeholders. Create PrepareStatement object using either prepareStatement() methods. Set auto-commit to false using setAutoCommit(). Add as many as SQL statements you like into batch using addBatch() method on created statement object. Execute all the SQL statements using executeBatch() method on created statement object. Finally, commit all the changes using commit() method. The following code snippet provides an example of a batch update using PrepareStatement object − // Create SQL statement String SQL = “INSERT INTO Employees (id, first, last, age) ” + “VALUES(?, ?, ?, ?)”; // Create PrepareStatement object PreparedStatement pstmt = conn.prepareStatement(SQL); //Set auto-commit to false conn.setAutoCommit(false); // Set the variables pstmt.setInt( 1, 400 ); pstmt.setString( 2, “Pappu” ); pstmt.setString( 3, “Singh” ); pstmt.setInt( 4, 33 ); // Add it to the batch pstmt.addBatch(); // Set the variables pstmt.setInt( 1, 401 ); pstmt.setString( 2, “Pawan” ); pstmt.setString( 3, “Singh” ); pstmt.setInt( 4, 31 ); // Add it to the batch pstmt.addBatch(); //add more batches . . . . //Create an int[] to hold returned values int[] count = stmt.executeBatch(); //Explicitly commit statements to apply changes conn.commit(); For a better understanding, let us study the Batching – Example Code. Print Page Previous Next Advertisements ”;

JDBC – Useful Resources ”; Previous Next The following resources contain additional information on JDBC. Please use them to get more in-depth knowledge on this topic. Java Servlet Online Training 20 Lectures 5 hours Tutorialspoint More Detail Java Made Easy for Beginners, Testers, Selenium and Appium 249 Lectures 62 hours Arun Motoori More Detail Java Database Connectivity (JDBC) | Database Design In Java Most Popular 19 Lectures 3.5 hours Emenwa Global, Ejike IfeanyiChukwu More Detail Print Page Previous Next Advertisements ”;

JDBC – LIKE Clause ”; Previous Next This chapter provides examples on how to select records from a table using JDBC application. This would add additional conditions using LIKE clause while selecting records from the table. Before executing the following example, make sure you have the following in place − To execute the following example you can replace the username and password with your actual user name and password. Your MySQL or whatever database you are using, is up and running. Required Steps The following steps are required to create a new Database using JDBC application − Import the packages − Requires that you include the packages containing the JDBC classes needed for database programming. Most often, using import java.sql.* will suffice. Open a connection − Requires using the DriverManager.getConnection() method to create a Connection object, which represents a physical connection with a database server. Execute a query − Requires using an object of type Statement for building and submitting an SQL statement to fetch records from a table which meet given condition. This Query makes use of LIKE clause to select records to select all the students whose first name starts with “za”. Clean up the environment − try with resources automatically closes the resources. Example: Selecting Record from a Table In this example, we”ve four static strings containing a dababase connection url, username, password and a SELECT query. Now using DriverManager.getConnection() method, we”ve prepared a database connection. Once connection is prepared, we”ve prepared a Statement object using createStatement() method. As next step, We”ve prepared and executed a query on table REGISTRATION by calling statement.executeQuery() method where all records are fetched and stored in a ResultSet object. ResultSet is iterated and all records are printed. As next, we”ve prepared a SQL query with LIKE clause to get result where first name is having “za”. Query is executed using statement.executeQuery() method where relevant records are fetched and stored in a ResultSet object. ResultSet is iterated and records are printed Copy and paste the following example in JDBCExample.java, compile and run as follows − import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class JDBCExample { static final String DB_URL = “jdbc:mysql://localhost/TUTORIALSPOINT”; static final String USER = “guest”; static final String PASS = “guest123”; static final String QUERY = “SELECT id, first, last, age FROM Registration”; public static void main(String[] args) { // Open a connection try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS); Statement stmt = conn.createStatement();) { System.out.println(“Fetching records without condition…”); ResultSet rs = stmt.executeQuery(QUERY); while(rs.next()){ //Display values System.out.print(“ID: ” + rs.getInt(“id”)); System.out.print(“, Age: ” + rs.getInt(“age”)); System.out.print(“, First: ” + rs.getString(“first”)); System.out.println(“, Last: ” + rs.getString(“last”)); } // Select all records having ID equal or greater than 101 System.out.println(“Fetching records with condition…”); String sql = “SELECT id, first, last, age FROM Registration” + ” WHERE first LIKE ”%za%””; rs = stmt.executeQuery(sql); while(rs.next()){ //Display values System.out.print(“ID: ” + rs.getInt(“id”)); System.out.print(“, Age: ” + rs.getInt(“age”)); System.out.print(“, First: ” + rs.getString(“first”)); System.out.println(“, Last: ” + rs.getString(“last”)); } rs.close(); } catch (SQLException e) { e.printStackTrace(); } } } Output Now let us compile the above example as follows − C:>javac JDBCExample.java C:> When you run JDBCExample, it produces the following result − C:>java JDBCExample Fetching records without condition… ID: 100, Age: 30, First: Zara, Last: Ali ID: 102, Age: 30, First: Zaid, Last: Khan ID: 103, Age: 28, First: Sumit, Last: Mittal Fetching records with condition… ID: 100, Age: 30, First: Zara, Last: Ali ID: 102, Age: 30, First: Zaid, Last: Khan C:> Example: Selecting Record from a Table In this example, we”ve three static strings containing a dababase connection url, username and password. Now using DriverManager.getConnection() method, we”ve prepared a database connection. Once connection is prepared, we”ve prepared a Statement object using createStatement() method. As next step, We”ve prepared and executed a query on table STUDENTS by calling statement.executeQuery() method where records are fetched where last name starts with A and stored in a ResultSet object. ResultSet is iterated and all records are printed. Copy and paste the following example in JDBCExample.java, compile and run as follows − import java.sql.*; public class JDBCExample { static final String DB_URL = “jdbc:mysql://localhost/TUTORIALSPOINT”; static final String USER = “root”; static final String PASS = “guest123”; public static void main(String args[]) { try{ Connection conn = DriverManager.getConnection(DB_URL, USER, PASS); Statement stmt = conn.createStatement(); String sel_qry = “select studentID, firstName, lastName from students where lastName like ”A%””; ResultSet rs = stmt.executeQuery(sel_qry); System.out.println(“Displaying records where LastName begins with ”A”” ); System.out.println(“——————————————————-“); while(rs.next()){ System.out.print(“studentID: ” + rs.getInt(1)); System.out.print(“, FirstName: ” + rs.getString(2)); System.out.println(“, LastName: ” + rs.getString(3)); } System.out.println(“——————————————–“); rs.close(); stmt.close(); conn.close(); }catch(SQLException e){ e.printStackTrace(); } } } Now let us compile the above example as follows − C:>javac JDBCExample.java C:> Output When you run JDBCExample, it produces the following result − C:>java JDBCExample Displaying records where LastName begins with ”A” ——————————————————- studentID: 1000, FirstName: Bonny, LastName: Agarwal studentID: 1004, FirstName: Mohammed, LastName: Ali ——————————————– C:> Example: Selecting Record from a Table In this example, we”ve three static strings containing a dababase connection url, username and password. Now using DriverManager.getConnection() method, we”ve prepared a database connection. Once connection is prepared, we”ve prepared a Statement object using createStatement() method. As next step, We”ve prepared and executed a query on table EMPLOYEES by calling statement.executeQuery() method where records are fetched where age begins with 5 and have only one literal after it and stored in a ResultSet object. ResultSet is iterated and all records are printed. Copy and paste the following example in JDBCExample.java, compile and run as follows − import java.sql.*; // This class demonstrates use of LIKE with underscore ”_” public class JDBCExample { static final String DB_URL =

JDBC – Connection Pooling ”; Previous Next For web sites with high traffic, resources to acquire a database connection can be expensive. Also, imagine a situation where there are 500 concurrent users of a web site and each is trying to get hold of a connection to select or update data. In this scenario, to prevent multiple users to corrupt the data, the database has locks. When a user does a select on a table, the table is locked. No other user can get hold of the table unless the lock is freed. For 500 users to wait for a connection is not acceptable. Connection pooling in Java is a technique used to improve the performance of database-driven applications. Instead of creating a new connection every time the application needs to interact with the database, a pool of connections is created and managed by a connection pool manager. This eliminates the overhead of establishing a new connection each time, resulting in faster response times and better resource utilization. Advantages of connection pooling Following are the advantages of the connection pooling. Improved performance − Connection pooling reduces the overhead of creating and closing connections, resulting in faster response times for your application. Resource optimization − By reusing connections, connection pooling helps to conserve system resources, such as memory and network connections. Scalability − Connection pools can be configured to grow or shrink dynamically based on demand, allowing your application to handle varying workloads. Popular Connection Pooling Libraries Following are popular connection pooling libraries. HikariCP − Known for its performance and efficiency. Apache Commons DBCP − Widely used and well-established. c3p0 − Another popular option with a focus on stability. Example: Connection Pooling Using HikariCP We will use HikariCP connection pool library. In Eclipse, go to File -> New -> Java Project. Set the Name of project as HikariCPExample. Click Finish. Now in Project explorer, right click on the project name, select Build Path -> Add External Archives. Add following 3 jars: HikariCP-5.0.1.jar − Download from HikariCP-5.0.1.jar mysql-connector-j-8.4.0.jar − Download from mysql-connector-j-8.4.0.jar slf4j-api-2.1.0-alpha1.jar − Download from slf4j-api-2.1.0-alpha1.jar In Eclipse, click on menu File -> New class to create a new class. Name the file as HikariCPManager. Don”t write anything for package name. Click Finish. In Eclipse, click on File -> New Class. Name the file TestPooledConnection. Don”t give any package name. Click <Finish>. HikariCPManager.java We”ve created a HikariConfig() instance and set JDBC url, username and password. Maximum pool size is set as 10. getPooledConnection() method is used to get a database connection. import java.sql.Connection; import java.sql.SQLException; import com.zaxxer.hikari.HikariConfig; import com.zaxxer.hikari.HikariDataSource; public class HikariCPManager { public static HikariDataSource dataSource; public static HikariConfig config = null; // Set properties in constructor HikariCPManager(){ config = new HikariConfig(); config.setJdbcUrl(“jdbc:mysql://localhost/TUTORIALSPOINT”); config.setUsername(“root”); config.setPassword(“guest123″); // Set maximum connection pool size config.setMaximumPoolSize(10); dataSource = new HikariDataSource(config); } public static Connection getPooledConnection() throws SQLException { return dataSource.getConnection(); } public static void close () { if (dataSource != null) { dataSource.close(); } } } TestPooledConnection.java In this example, we”ve created HikariCPManager instance and using getPooledConnection(), we”ve prepared a pooled connection. Once connection object is ready, we”ve prepared a PreparedStatement instance with a SELECT query. Using executeQuery(), we”ve executed the query and printed all the employees by iterating the resultSet received. import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class TestPooledConnection { public static void main(String[] args) { Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; try { HikariCPManager hcpm = new HikariCPManager(); conn = hcpm.getPooledConnection(); if (conn != null) { // Prepare statement String sql = “SELECT * FROM employees”; pstmt = conn.prepareStatement(sql); // Execute query rs = pstmt.executeQuery(); // Process and print results while (rs.next()) { int id = rs.getInt(“id”); String fname = rs.getString(“first”); String lname = rs.getString(“last”); System.out.println(“ID: ” + id + “, First Name: ” + fname + “, Last Name: ” + lname); } } else { System.out.println(“Error getting connection.”); } } catch (SQLException e) { e.printStackTrace(); } finally { // Close all resources try { if (rs != null) rs.close(); if (pstmt != null) pstmt.close(); if (conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); } } // Close connection pool HikariCPManager.close(); } } Output In Eclipse, on Package explorer, right-click on TestPooledConnection.java, go to Run as -> Java application. On the console, you will see − ID: 1, First Name: Shahbaz, Last Name: Ali ID: 2, First Name: Mahnaz, Last Name: Fatma ID: 4, First Name: Sumit, Last Name: Mittal ID: 21, First Name: Jeevan, Last Name: Rao ID: 22, First Name: Dinesh, Last Name: Kumar ID: 25, First Name: Jeevan, Last Name: Rao ID: 26, First Name: Aditya, Last Name: Chaube ID: 34, First Name: Ahmed, Last Name: Ali ID: 35, First Name: Raksha, Last Name: Agarwal Print Page Previous Next Advertisements ”;

JDBC – Questions Answers ”; Previous Next JDBC Questions and Answers has been designed with a special intention of helping students and professionals preparing for various Certification Exams and Job Interviews. This section provides a useful collection of sample Interview Questions and Multiple Choice Questions (MCQs) and their answers with appropriate explanations. SN Question/Answers Type 1 JDBC Interview Questions This section provides a huge collection of JDBC Interview Questions with their answers hidden in a box to challenge you to have a go at them before discovering the correct answer. 2 JDBC Online Quiz This section provides a great collection of JDBC Multiple Choice Questions (MCQs) on a single page along with their correct answers and explanation. If you select the right option, it turns green; else red. 3 JDBC Online Test If you are preparing to appear for a Java and JDBC Framework related certification exam, then this section is a must for you. This section simulates a real online test along with a given timer which challenges you to complete the test within a given time-frame. Finally you can check your overall test score and how you fared among millions of other candidates who attended this online test. 4 JDBC Mock Test This section provides various mock tests that you can download at your local machine and solve offline. Every mock test is supplied with a mock test key to let you verify the final score and grade yourself. Print Page Previous Next Advertisements ”;

JDBC – Statements, PreparedStatement and CallableStatement ”; Previous Next Once a connection is obtained we can interact with the database. The JDBC Statement, CallableStatement, and PreparedStatement interfaces define the methods and properties that enable you to send SQL or PL/SQL commands and receive data from your database. They also define methods that help bridge data type differences between Java and SQL data types used in a database. The following table provides a summary of each interface”s purpose to decide on the interface to use. Interfaces Recommended Use Statement Use this for general-purpose access to your database. Useful when you are using static SQL statements at runtime. The Statement interface cannot accept parameters. PreparedStatement Use this when you plan to use the SQL statements many times. The PreparedStatement interface accepts input parameters at runtime. CallableStatement Use this when you want to access the database stored procedures. The CallableStatement interface can also accept runtime input parameters. The Statement Objects Creating Statement Object Before you can use a Statement object to execute a SQL statement, you need to create one using the Connection object”s createStatement( ) method, as in the following example − Statement stmt = null; try { stmt = conn.createStatement( ); . . . } catch (SQLException e) { . . . } finally { . . . } Once you”ve created a Statement object, you can then use it to execute an SQL statement with one of its three execute methods. boolean execute (String SQL): Returns a boolean value of true if a ResultSet object can be retrieved; otherwise, it returns false. Use this method to execute SQL DDL statements or when you need to use truly dynamic SQL. int executeUpdate (String SQL) − Returns the number of rows affected by the execution of the SQL statement. Use this method to execute SQL statements for which you expect to get a number of rows affected – for example, an INSERT, UPDATE, or DELETE statement. ResultSet executeQuery (String SQL) − Returns a ResultSet object. Use this method when you expect to get a result set, as you would with a SELECT statement. Closing Statement Object Just as you close a Connection object to save database resources, for the same reason you should also close the Statement object. A simple call to the close() method will do the job. If you close the Connection object first, it will close the Statement object as well. However, you should always explicitly close the Statement object to ensure proper cleanup. Statement stmt = null; try { stmt = conn.createStatement( ); . . . } catch (SQLException e) { . . . } finally { stmt.close(); } For a better understanding, we suggest you to study the Statement – Example tutorial. The PreparedStatement Objects The PreparedStatement interface extends the Statement interface, which gives you added functionality with a couple of advantages over a generic Statement object. This statement gives you the flexibility of supplying arguments dynamically. Creating PreparedStatement Object PreparedStatement pstmt = null; try { String SQL = “Update Employees SET age = ? WHERE id = ?”; pstmt = conn.prepareStatement(SQL); . . . } catch (SQLException e) { . . . } finally { . . . } All parameters in JDBC are represented by the ? symbol, which is known as the parameter marker. You must supply values for every parameter before executing the SQL statement. The setXXX() methods bind values to the parameters, where XXX represents the Java data type of the value you wish to bind to the input parameter. If you forget to supply the values, you will receive an SQLException. Each parameter marker is referred by its ordinal position. The first marker represents position 1, the next position 2, and so forth. This method differs from that of Java array indices, which starts at 0. All of the Statement object”s methods for interacting with the database (a) execute(), (b) executeQuery(), and (c) executeUpdate() also work with the PreparedStatement object. However, the methods are modified to use SQL statements that can input the parameters. Closing PreparedStatement Object Just as you close a Statement object, for the same reason you should also close the PreparedStatement object. A simple call to the close() method will do the job. If you close the Connection object first, it will close the PreparedStatement object as well. However, you should always explicitly close the PreparedStatement object to ensure proper cleanup. PreparedStatement pstmt = null; try { String SQL = “Update Employees SET age = ? WHERE id = ?”; pstmt = conn.prepareStatement(SQL); . . . } catch (SQLException e) { . . . } finally { pstmt.close(); } For a better understanding, let us study Prepare – Example Code. The CallableStatement Objects Just as a Connection object creates the Statement and PreparedStatement objects, it also creates the CallableStatement object, which would be used to execute a call to a database stored procedure. Creating CallableStatement Object Suppose, you need to execute the following Oracle stored procedure − CREATE OR REPLACE PROCEDURE getEmpName (EMP_ID IN NUMBER, EMP_FIRST OUT VARCHAR) AS BEGIN SELECT first INTO EMP_FIRST FROM Employees WHERE ID = EMP_ID; END; NOTE − Above stored procedure has been written for Oracle, but we are working with MySQL database so, let us write same stored procedure for MySQL as follows to create it in EMP database − DELIMITER $$ DROP PROCEDURE IF EXISTS `EMP`.`getEmpName` $$ CREATE PROCEDURE `EMP`.`getEmpName` (IN EMP_ID INT, OUT EMP_FIRST VARCHAR(255)) BEGIN SELECT first INTO EMP_FIRST FROM Employees WHERE ID = EMP_ID; END $$ DELIMITER ; Three types of parameters exist: IN,

JDBC – Create Database ”; Previous Next This tutorial provides examples on how to create a Database and Schema using JDBC application. Before executing the following example, make sure you have the following in place − You should have admin privilege to create a database in the given schema. To execute the following example, you need to replace the username and password with your actual user name and password. Your MySQL or whatever database is up and running. Required Steps The following steps are required to create a new Database using JDBC application − Import the packages − Requires that you include the packages containing the JDBC classes needed for database programming. Most often, using import java.sql.* will suffice. Open a connection − Requires using the DriverManager.getConnection() method to create a Connection object, which represents a physical connection with the database server. To create a new database, you need not give any database name while preparing database URL as mentioned in the below example. Execute a query − Requires using an object of type Statement for building and submitting an SQL statement to the database. Clean up the environment . try with resources automatically closes the resources. Example: Creating a Database In this example, we”ve three static strings containing a dababase connection url, username, password. Now using DriverManager.getConnection() method, we”ve prepared a database connection. Once connection is prepared, we”ve created a Statement object using connection.createStatement() method. Then using statement.executeUpdate(), we”ve run the query to create a new database named Students and printed the success message. In case of any exception while creating the database, a catch block handled SQLException and printed the stack trace. Copy and paste the following example in JDBCExample.java, compile and run as follows − import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class JDBCExample { static final String DB_URL = “jdbc:mysql://localhost/”; static final String USER = “guest”; static final String PASS = “guest123”; public static void main(String[] args) { // Open a connection try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS); Statement stmt = conn.createStatement(); ) { String sql = “CREATE DATABASE STUDENTS”; stmt.executeUpdate(sql); System.out.println(“Database created successfully…”); } catch (SQLException e) { e.printStackTrace(); } } } Output Now let us compile the above example as follows − C:>javac JDBCExample.java C:> When you run JDBCExample, it produces the following result − C:>java JDBCExample Database created successfully… C:> As we”ve successfully created the database, on similar lines, we can create the schema as well as shown in example below: Example: Creating a Schema In this example, we”ve three static strings containing a dababase connection url, username, password. Now using DriverManager.getConnection() method, we”ve prepared a database connection. Once connection is prepared, we”ve created a Statement object using connection.createStatement() method. Then using statement.executeUpdate(), we”ve run the query to create a new schema named Sample_db1 and printed the success message. In case of any exception while creating the database, a catch block handled SQLException and printed the stack trace. Copy and paste the following example in JDBCExample.java, compile and run as follows − import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class JDBCExample { static final String DB_URL = “jdbc:mysql://localhost/”; static final String USER = “guest”; static final String PASS = “guest123”; public static void main(String[] args) { // Open a connection try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS); Statement stmt = conn.createStatement(); ) { String sql = “CREATE SCHEMA Sample_db1”; stmt.executeUpdate(sql); System.out.println(“Schema created successfully…”); } catch (SQLException e) { e.printStackTrace(); } } } Output Now let us compile the above example as follows − C:>javac JDBCExample.java C:> When you run JDBCExample, it produces the following result − C:>java JDBCExample Schema created successfully… C:> Print Page Previous Next Advertisements ”;

JDBC – Create Table ”; Previous Next This chapter provides examples on how to create table, temporary table and duplicate table using JDBC application. Before executing the following example, make sure you have the following in place − To execute the following example you can replace the username and password with your actual user name and password. Your MySQL is up and running. Required Steps The following steps are required to create a new Database using JDBC application − Import the packages − Requires that you include the packages containing the JDBC classes needed for database programming. Most often, using import java.sql.* will suffice. Open a connection − Requires using the DriverManager.getConnection() method to create a Connection object, which represents a physical connection with a database server. Execute a query − Requires using an object of type Statement for building and submitting an SQL statement to create a table in a seleted database. Clean up the environment − try with resources automatically closes the resources. Example: Creating a Table In this example, we”ve three static strings containing a dababase connection url, username, password. Now using DriverManager.getConnection() method, we”ve prepared a database connection. Once connection is prepared, we”ve prepared a Statement object using createStatement() method. As next step, We”ve prepared a SQL string to create a new table REGISTRATION and created the table in database by calling statement.executeUpdate() method. In case of any exception while connecting to the database, a catch block handled SQLException and printed the stack trace. Copy and paste the following example in TestApplication.java, compile and run as follows − import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class TestApplication { static final String DB_URL = “jdbc:mysql://localhost/TUTORIALSPOINT”; static final String USER = “guest”; static final String PASS = “guest123”; public static void main(String[] args) { // Open a connection try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS); Statement stmt = conn.createStatement(); ) { String sql = “CREATE TABLE REGISTRATION ” + “(id INTEGER not NULL, ” + ” first VARCHAR(255), ” + ” last VARCHAR(255), ” + ” age INTEGER, ” + ” PRIMARY KEY ( id ))”; stmt.executeUpdate(sql); System.out.println(“Created table in given database…”); } catch (SQLException e) { e.printStackTrace(); } } } Now let us compile the above example as follows − C:>javac TestApplication.java C:> When you run TestApplication, it produces the following result − C:>java TestApplication Created table in given database… C:> Example: Creating a Temporary Table We can create a temporary table, which exists only during an active session. Temporary tables are supported in MySQL, SQL Server, Oracle etc. In this example, we”ve three static strings containing a dababase connection url, username, password. Now using DriverManager.getConnection() method, we”ve prepared a database connection. Once connection is prepared, we”ve prepared a Statement object using createStatement() method. As next step, We”ve prepared a SQL string to create a new Temporary table EMPLOYEES_COPY and created the table in database by calling statement.execute() method. In next line of code, we”ve created a query string to get all records from the newly created temporary table EMPLOYEES_COPY. Query is fired using statement.executeQuery() method and result is stored in a ResultSet. ResultSet is iterated to print all the employees. In case of any exception while connecting to the database, a catch block handled SQLException and printed the stack trace. Copy and paste the following example in TestApplication.java, compile and run as follows − import java.sql.*; public class TestApplication { static final String DB_URL = “jdbc:mysql://localhost/TUTORIALSPOINT”; static final String USER = “guest”; static final String PASS = “guest123”; public static void main(String args[]) { try{ Connection conn = DriverManager.getConnection(DB_URL, USER, PASS); Statement stmt = conn.createStatement(); String QUERY1 = “CREATE TEMPORARY TABLE EMPLOYEES_COPY SELECT * FROM EMPLOYEES”; stmt.execute(QUERY1); String QUERY2 = “SELECT * FROM EMPLOYEES_COPY”; ResultSet rs = stmt.executeQuery(QUERY2); while (rs.next()){ System.out.print(“Id: ” + rs.getInt(“id”)); System.out.print(” Age: ” + rs.getInt(“age”)); System.out.print(” First: ” + rs.getString(“first”)); System.out.println(” Last: ” + rs.getString(“last”)); System.out.println(“——————————————“); } }catch (SQLException e){ e.printStackTrace(); } } } Now let us compile the above example as follows − C:>javac TestApplication.java C:> When you run TestApplication, it produces the following result − C:>java TestApplication Id: 1 Age: 18 First: Zara Last: Ali —————————————— Id: 2 Age: 25 First: Mahnaz Last: Fatma —————————————— Id: 3 Age: 20 First: Zaid Last: Khan —————————————— Id: 4 Age: 28 First: Sumit Last: Mittal —————————————— Id: 7 Age: 20 First: Rita Last: Tez —————————————– Id: 8 Age: 20 First: Sita Last: Singh —————————————— Id: 21 Age: 35 First: Jeevan Last: Rao —————————————— Id: 22 Age: 40 First: Aditya Last: Chaube —————————————— Id: 25 Age: 35 First: Jeevan Last: Rao —————————————— Id: 26 Age: 35 First: Aditya Last: Chaube —————————————— Id: 34 Age: 45 First: Ahmed Last: Ali —————————————— Id: 35 Age: 50 First: Raksha Last: Agarwal —————————————— C:> Example: Creating a Duplicate Table We can create a TABLE which is exactly similar to an existing table. The syntax is: CREATE new_table_name LIKE orig_table_name; After executing the command, all data from original table is copied to new table. In this example, we”ve three static strings containing a dababase connection url, username, password. Now using DriverManager.getConnection() method, we”ve prepared a database connection. Once connection is prepared, we”ve prepared a Statement object using createStatement() method. As next step, We”ve prepared a SQL string to create a new duplicate table EMPLOYEES_O and created the table in database by calling statement.execute() method. In next line of code, we”ve created a query string to get all records from the newly created duplicate table EMPLOYEES_O. Query is fired using statement.executeQuery() method and result is stored in a ResultSet. ResultSet is iterated to print all the employees. In case of

JDBC – Drop Database ”; Previous Next This chapter provides examples on how to drop an existing Database using JDBC application and MySQLAdmin console. Before executing the following example, make sure you have the following in place − To execute the following example you need to replace the username and password with your actual user name and password. Your MySQL is up and running. NOTE: This is a serious operation and you have to make a firm decision before proceeding to delete a database because everything you have in your database would be lost. Required Steps The following steps are required to create a new Database using JDBC application − Import the packages − Requires that you include the packages containing the JDBC classes needed for database programming. Most often, using import java.sql.* will suffice. Open a connection − Requires using the DriverManager.getConnection() method to create a Connection object, which represents a physical connection with a database server. Deleting a database does not require database name to be in your database URL. Following example would delete STUDENTS database. Execute a query − Requires using an object of type Statement for building and submitting an SQL statement to delete the database. Clean up the environment − try with resources automatically closes the resources. Example: Dropping a Database In this example, we”ve three static strings containing a dababase connection url, username, password. Now using DriverManager.getConnection() method, we”ve prepared a database connection. Once connection is prepared, we”ve created a Statement object using connection.createStatement() method. Then using statement.executeUpdate(), we”ve run the query to drop a database named Students and printed the success message. In case of any exception while creating the database, a catch block handled SQLException and printed the stack trace. Copy and paste the following example in JDBCExample.java, compile and run as follows − import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class JDBCExample { static final String DB_URL = “jdbc:mysql://localhost/”; static final String USER = “guest”; static final String PASS = “guest123”; public static void main(String[] args) { // Open a connection try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS); Statement stmt = conn.createStatement(); ) { String sql = “DROP DATABASE STUDENTS”; stmt.executeUpdate(sql); System.out.println(“Database dropped successfully…”); } catch (SQLException e) { e.printStackTrace(); } } } Output Now let us compile the above example as follows − C:>javac JDBCExample.java C:> When you run JDBCExample, it produces the following result − C:>java JDBCExample Database dropped successfully… C:> In next example, we”re dropping a database TUTORIALSPOINT_COPY. Before deleting, we”re showing all available databases and then after deleting the same list is reprinted to check if database is dropped or not. See the example below using JDBC code: Example: Dropping a Database In this example, we”ve three static strings containing a dababase connection url, username, password. Now using DriverManager.getConnection() method, we”ve prepared a database connection. Once connection is prepared, we”ve created a Statement object using connection.createStatement() method. The SQL command “SHOW DATABASES” is used to show list of available databases. Now using “DROP DATABASE TUTORIALSPOINT_COPY”, we”re dropping the TUTORIALSPOINT_COPY database. Then, again SQL query is issued “SHOW DATABASES”, to show the updated list of databases. In case of any exception while creating the database, a catch block handled SQLException and printed the stack trace. Copy and paste the following example in JDBCExample.java, compile and run as follows − import java.sql.*; // Drop database example public class JDBCExample { static final String DB_URL = “jdbc:mysql://localhost/”; static final String USER = “guest”; static final String PASS = “guest123”; public static void main(String args[]) { try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);) { System.out.println(“Connected to database successfully…”); Statement stmt = conn.createStatement(); ResultSet rs1 = stmt.executeQuery(“SHOW DATABASES”); System.out.println(“DATABASES”); System.out.println(“——————————————-“); while( rs1.next()){ System.out.println(rs1.getString(1)); } //Now DROP DATABASE int r = stmt.executeUpdate(“DROP DATABASE TUTORIALSPOINT_COPY”); System.out.println(“TutorialsPoint_copy database successfully deleted. No. of tables removed = ” + r); System.out.println(“——————————————-“); ResultSet rs2 = stmt.executeQuery(“SHOW DATABASES”); System.out.println(“DATABASES after DROP DATABASE has been called.”); System.out.println(“——————————————-“); while( rs2.next()){ System.out.println(rs2.getString(1)); } }catch(SQLException e){ e.printStackTrace(); } } } Output Now let us compile the above example as follows − C:>javac JDBCExample.java C:> When you run JDBCExample, it produces the following result − C:>java JDBCExample Connected to database successfully… DATABASES ——————————————- information_schema mysql performance_schema sample_db1 students sys tutorialspoint tutorialspoint_copy world TutorialsPoint_copy database successfully deleted. No. of tables removed = 4 ——————————————- DATABASES after DROP DATABASE has been called. ——————————————- information_schema mysql performance_schema sample_db1 students sys tutorialspoint world C:> Another way to delete a database is through mysqladmin console. From command prompt, go to the bin directory of MySQL installation directory. For example: C:Program FilesMySQLMySQL Server 8.4bin> From this directory, type: following command C:Program FilesMySQLMySQL Server 8.4bin> mysqladmin -u root -p drop sample_db1 Once you type this, you will be asked for password. Enter the password for user ”root”. After dropping, type SHOW DATABASES in MySQL prompt. The database removed will not show. See screenshot below: Print Page Previous Next Advertisements ”;

JDBC – Exception Handling ”; Previous Next Exception handling allows you to handle exceptional conditions such as program-defined errors in a controlled fashion. When an exception condition occurs, an exception is thrown. The term thrown means that current program execution stops, and the control is redirected to the nearest applicable catch clause. If no applicable catch clause exists, then the program”s execution ends. JDBC Exception handling is very similar to the Java Exception handling but for JDBC, the most common exception you”ll deal with is java.sql.SQLException. SQLException Methods An SQLException can occur both in the driver and the database. When such an exception occurs, an object of type SQLException will be passed to the catch clause. The passed SQLException object has the following methods available for retrieving additional information about the exception − Method Description getErrorCode( ) Gets the error number associated with the exception. getMessage( ) Gets the JDBC driver”s error message for an error, handled by the driver or gets the Oracle error number and message for a database error. getSQLState( ) Gets the XOPEN SQLstate string. For a JDBC driver error, no useful information is returned from this method. For a database error, the five-digit XOPEN SQLstate code is returned. This method can return null. getNextException( ) Gets the next Exception object in the exception chain. printStackTrace( ) Prints the current exception, or throwable, and it”s backtrace to a standard error stream. printStackTrace(PrintStream s) Prints this throwable and its backtrace to the print stream you specify. printStackTrace(PrintWriter w) Prints this throwable and it”s backtrace to the print writer you specify. By utilizing the information available from the Exception object, you can catch an exception and continue your program appropriately. Here is the general form of a try block − try { // Your risky code goes between these curly braces!!! } catch(Exception ex) { // Your exception handling code goes between these // curly braces, similar to the exception clause // in a PL/SQL block. } finally { // Your must-always-be-executed code goes between these // curly braces. Like closing database connection. } Using try catch block in JDBC Example Study the following example code to understand the usage of try….catch blocks. In this example, we”ve four static string for database connection string, username, password and a sql query to call a stored procedure. In main method, we”re preparing connection to the database using DriverManager.getConnection() method. We prepared a CallableStatement using connection.prepareCall() method. As next step, place holder values are set and out parameter is registered. Finally stored procedure is called using CallableStatement.execute() method and employee name is retrieved using getString() method. In catch() statement, we are handling a SQL exception. Being try with resources, finally statement is not needed and connection object is automatically closed after try-catch statements completion. import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class JDBCExample { static final String DB_URL = “jdbc:mysql://localhost/TUTORIALSPOINT”; static final String USER = “guest”; static final String PASS = “guest123”; static final String QUERY = “{call getEmpName (?, ?)}”; public static void main(String[] args) { // Open a connection try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS); CallableStatement stmt = conn.prepareCall(QUERY); ) { // Bind values into the parameters. stmt.setInt(1, 1); // This would set ID // Because second parameter is OUT so register it stmt.registerOutParameter(2, java.sql.Types.VARCHAR); //Use execute method to run stored procedure. System.out.println(“Executing stored procedure…” ); stmt.execute(); //Retrieve employee name with getXXX method String empName = stmt.getString(2); System.out.println(“Emp Name with ID: 1 is ” + empName); } catch (SQLException e) { e.printStackTrace(); } } } Now, let us compile the above example as follows − C:>javac JDBCExample.java C:> When you run JDBCExample, it produces the following result if there is no problem, otherwise the corresponding error would be caught and error message would be displayed − C:>java JDBCExample Executing stored procedure… Emp Name with ID: 1 is Zara C:> Try the above example by passing wrong database name or wrong username or password and check the result. Facing SQLException for Invalid Table Name and using Error Codes in JDBC Example In this example, we”ve three static string for database connection string, username, password. In main method, we”re preparing connection to the database using DriverManager.getConnection() method. We prepared a Statement using connection.createStatement() method. Using statement.executeQuery(), a query with invalid table name is executed which results in a SQLException. Exception is handled in catch block and error code, sql state and a detailed error message is printed. import java.sql.*; public class JDBCExceptionExample { static final String MYSQL_URL = “jdbc:mysql://localhost/TUTORIALSPOINT”; static final String USER_NAME = “guest”; static final String PASSWORD = “guest123”; public static void main(String args[]) { try{ Connection conn = DriverManager.getConnection(MYSQL_URL, USER_NAME, PASSWORD); Statement stmt = conn.createStatement(); // Giving incorrect table name to get Exceptions ResultSet rs = stmt.executeQuery(“SELECT * FROM EMPLOYEES10”); while( rs.next()){ System.out.println(“ID:” + rs.getInt(“id”)); System.out.println(“, Age: ” + rs.getInt(“age”)); } }catch(SQLException e){ int errorCode = e.getErrorCode(); String sqlState = e.getSQLState(); String errorMsg = e.getMessage(); System.out.println(“Error code: ” + errorCode); System.out.println(“SqlState: ” + sqlState); System.out.println(“Error Message: ” + errorMsg); e.printStackTrace(); } } } Output Now let us compile the above example as follows − C:>javac JDBCExceptionExample.java C:> When you run JDBCExceptionExample, it produces the following result − C:>java JDBCExceptionExample Error code: 1146 SqlState: 42S02 Error Message: Table ”tutorialspoint.employees10” doesn”t exist java.sql.SQLSyntaxErrorException: Table ”tutorialspoint.employees10” doesn”t exist at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:112) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:113) at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1286) at JDBCExceptionExample.main(JDBCExceptionExample.java:26) at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103) at java.base/java.lang.reflect.Method.invoke(Method.java:580) at jdk.compiler/com.sun.tools.javac.launcher.Main.execute(Main.java:484) at jdk.compiler/com.sun.tools.javac.launcher.Main.run(Main.java:208) at jdk.compiler/com.sun.tools.javac.launcher.Main.main(Main.java:135) C:> Facing SQLException for Invalid Column Name and using Error Codes in JDBC Example