Interview Questions

JDBC

How to insert BLOB/CLOB data in DB using JDBC ?

Use the following code to insert LOB data in DB using JDBC.

public static void insertLob() throws Exception
    {
            String dbUrl = "jdbc:oracle:thin:@<host_name>:<port_number>:<DB_Name>";
            Connection con = null;
            ResultSet rs = null;
            PreparedStatement psIns = null;
           
            try
            {
                    Class.forName("<Driver_Name>");

                    con = DriverManager.getConnection(dbUrl, "<userName>", "<passWord>");

                    psIns = con

MySQL - CreateTrigger Using JDBC connection

/**
 * THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS ``AS IS''
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED
 * TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
 * PURPOSE IS DISCLAIMED.  IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS
 * BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
 * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
 * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS

MySQL - Fetch records from table using JDBC connection

/**
 * THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS ``AS IS''
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED
 * TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
 * PURPOSE ARE DISCLAIMED.  IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS
 * BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
 * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
 * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS

JDBC Handling Huge ResultSet

In this article lets discuss about querying huge database tables using JDBC api.

Consider a scenario where we have a huge database table with (say 1 million records), and the requirement is to display those records in your application UI.

Generally we will use the JDBC api in java to query the database table, iterate the ResultSet object and display the results in UI.

ORA-01795 Oracle In Clause Limitation

In this section we will see about how to handle IN clause limitation exceeding > 1000 values(ORA-01795 Error) in oracle.

Consider the following query

SELECT * FROM BOOKS WHERE AUTHOR_NAME IN (‘J.K ROWLING’,’PAULO COELHO ’,’JOHN GRISHAM’);

The above query will return results containing for the given author names in where clause. But there is a limitation that the IN clause cannot have more than 1000 values. Mostly the work around will be to loop through the select statement for 10 times if there are 10000 values.