JDBC

What are the types of statements in JDBC?

JDBC API has 3 Interfaces and their key features of these are as follows:

Statement: which is used to run simple SQL statements like select and update. Statement interfaces use for general-purpose access to your database. It is useful when you are using static SQL statements at runtime. The Statement interface cannot accept parameters.

Prepared Statement: A SQL statement is pre-compiled and stored in a Prepared Statement object. It is used to run Pre compiled SQL. This object can then be used to efficiently execute this statement multiple times. The object of Prepared Statement class can be created using Connection.prepareStatement() method. This extends Statement interface.

Callable Statement: This interface is used to execute the stored procedures. This extends Prepared Statement interface. The object of Callable Statement class can be created using Connection.prepareCall() method.

What causes No suitable driver error?

"No suitable driver" is occurs during a call to the DriverManager.getConnection method, may be of any of the following reason:

• Due to failing to load the appropriate JDBC drivers before calling the getConnection method.
• It can be specifying an invalid JDBC URL, one that is not recognized by JDBC driver.
• This error can occur if one or more the shared libraries needed by the bridge cannot be loaded

What does setAutoCommit do?

setAutoCommit() invoke the commit state query to the database. To perform batch updation we use the setAutoCommit() which enable us to execute more than one statement together, which in result minimize the database call and send all statement in one batch.

setAutoCommit() allowed us to commit the transaction commit state manually the default values of the setAutoCommit() is true.

Why Prepared Statements are faster?

Prepared execution is faster than direct execution for statements executed more than three or four times because the statement is compiled only once. Prepared statements and JDBC driver are linked with each other. We can bind drivers with columns by triggering the query into the database. When we execute Connection.prepareStatement(), all the columns bindings take place, in oder to reduce the time.

What restrictions are placed on method overriding?

The restriction on method overloading is the signature of the method.

• The signature is the number, type, and order of the arguments passed to a method.
• Overridden methods must have the same name, argument list, and return type.
• Any method which has the same name cannot have the same signature.
• They can have the same return types in the same scope.
• The compiler uses the signature to detect which overloaded method to refer when a overloaded method is called.
• If two methods have the same name and signature the compiler will throw a runtime error.

What are types of JDBC drivers?

There are four types of drivers defined by JDBC as follows:

• JDBC/ODBC: These require an ODBC (Open Database Connectivity) driver for the database to be installed. It is used for local connection.
• Native API (partly-Java driver): This type of driver uses a database API to interact with the database. It also provides no host redirection.
• Network Protocol Driver: It makes use of a middle-tier between the calling program and the database. The client driver communicates with the net server using a database-independent protocol and the net server translates this protocol into database calls.
• Native Protocol Drive: This has a same configuration as a type 3 driver but uses a wire protocol specific to a particular vendor and hence can access only that vendor's database.

Is it possible to connect to multiple databases simultaneously? Using single statement can one update or extract data from multiple databases?

Yes, it is possible but it depends upon the capabilities of the specific driver implementation, we can connect to multiple databases at the same time. We doing following steps:

• Minimum one driver will be used to handle the commits transaction for multiple connections.
• To update and extract data from the different database we use single statement for this we need special middleware to deal with multiple databases in a single statement or to effectively treat them as one database.

What are the differences between setMaxRows(int) and SetFetchSize(int)?

The difference between setFetchSize and setMaxRow are:

• setFetchSize(int) defines the number of rows that will be read from the database when the ResultSet needs more rows whereas setMaxRows(int) method of the ResultSet specifies how many rows a ResultSet can contain at a time.
• In setFetchSize(int), method in the java.sql.Statement interface will set the 'default' value for all the ResultSet derived from that Statement whereas in setMaxRow(int) default value is 0, i.e. all rows will be included in the ResultSet.
• the setMaxRows affects the client side JDBC object while the setFetchSize affects how the database returns the ResultSet data.

How can I manage special characters when I execute an INSERT query?

The special characters meaning in SQL can be preceded with a special escape character in strings, e.g. "\". In order to specify the escape character used to quote these characters, include the following syntax on the end of the query:
{escape 'escape-character'}

For example, the query

SELECT NAME FROM IDENTIFIERS WHERE ID LIKE '\_%' {escape '\'}
finds identifier names that begin with an underscore.

What is the benefit of having JdbcRowSet implementation? Why do we need a JdbcRowSet like wrapper around ResultSet?

The JdbcRowSet implementation is a wrapper around a ResultSet object has following advantages over ResultSet:

• It makes possible to use the ResultSet object as a JavaBeans component.
• A JdbcRowSet can be used as a JavaBeans component, thus it can be created and configured at design time and executed at run time.
• It can be used to make a ResultSet object scrollable and updatable. All RowSet objects are by default scrollable and updatable.

Explain Basic Steps in writing a Java program using JDBC.

JDBC makes the interaction with RDBMS simple and intuitive. When a Java application needs to access database :

• Load the RDBMS specific JDBC driver because this driver actually communicates with the database.
• Open the connection to database, for sending SQL statements and get results back.
• Create JDBC Statement object containing SQL query.
• Execute statement which returns result set. ResultSet contains the tuples of database table as a result of SQL query.
• Process the result set.
• Close the connection.

I have the choice of manipulating database data using a byte[] or a java.sql.Blob. Which has best performance?

We use java.sql.Blob, because of following reason:

• It does not extract any data from the database until we trigger a query to the databse.
• We use byte[] for inserting data in the database when data is not upload in the database till yet.
• java.sql.Blob is used when extraction of the data is performed.

What are DML and DDL?

Data Manipulation Language (DDL) this portion of the SQL standard is concerned with manipulating the data in a database as opposed to the structure of a database. The DML deals with the SELECT, INSERT, DELETE, UPDATE, COMMIT and ROLLBACK.

Data Definition Language (DDL) this portion of the SQL standard is concerned with the creation, deletion and modification of database objects like tables, indexes and views. The core verbs for DDL are CREATE, ALTER and DROP. While most DBMS engines allow DDL to be used dynamically, it is often not supported in transactions.

How can you load the drivers?

It is very simple and involves just one line of code to load the driver or drivers we want to use.

For example, We want to use the JDBC-ODBC Bridge driver, the following code will load it:

Class.forName(”sun.jdbc.odbc.JdbcOdbcDriver”);

Driver documentation will give you the class name to use. For instance, if the class name is jdbc.DriverHELLO, you would load the driver with the following line of code:

Class.forName(”jdbc.DriverHELLO”);

How do I insert an image file (or other raw data) into a database?

All raw data types should be read and uploaded to the database as an array of bytes, byte[].

• Originating from a binary file.
• Read all data from the file using a FileInputStream.
• Create a byte array from the read data.
• Use method setBytes(int index, byte[] data); of java.sql.PreparedStatement to upload the data.

More JDBC........

1. What is JDBC API and when do we use it?

   Java DataBase Connectivity API allows us to work with relational databases. JDBC API interfaces and classes are part of java.sql and javax.sql package. We can use JDBC API to get the database connection, run SQL queries and stored procedures in the database server and process the results.
   JDBC API is written in a way to allow loose coupling between our Java program and actual JDBC drivers that makes our life easier in switching from one database to another database servers easily.

2. What are different types of JDBC Drivers?

   There are four types of JDBC drivers. Any java program that works with database has two parts, first part is the JDBC API and second part is the driver that does the actual work.
   
   1. JDBC-ODBC Bridge plus ODBC Driver (Type 1): It uses ODBC driver to connect to database. We should have ODBC drivers installed to connect to database, that’s why this driver is almost obsolete.
   2. Native API partly Java technology-enabled driver (Type 2): This driver converts JDBC class to the client API for the database servers. We should have database client API installed. Because of extra dependency on database client API drivers, this is also not preferred driver.
   3. Pure Java Driver for Database Middleware (Type 3): This driver sends the JDBC calls to a middleware server that can connect to different type of databases. We should have a middleware server installed to work with this driver. This adds to extra network calls and slow performance and thats why not widely used JDBC driver.
   4. Direct-to-Database Pure Java Driver (Type 4): This driver converts the JDBC calls to the network protocol understood by the database server. This solution is simple and suitable for database connectivity over the network. However for this solution, we should use database specific drivers, for example OJDBC jars by Oracle for Oracle DB and MySQL Connector/J for MySQL databases.

3. How does JDBC API helps us in achieving loose coupling between Java Program and JDBC Drivers API?

   JDBC API uses Java Reflection API to achieve loose coupling between java programs and JDBC Drivers. If you look at a simple JDBC example, you will notice that all the programming is done in terms of JDBC API and Driver comes in picture only when it’s loaded through reflection using Class.forName() method.
   I think this is one of the best example of using Reflection in core java classes to make sure that our application doesn’t work directly with Drivers API and that makes it very easy to move from one database to another. Please read more at JDBC Example.

4. What is JDBC Connection? Explain steps to get Database connection in a simple java program.

   JDBC Connection is like a Session created with the database server. You can also think Connection is like a Socket connection from the database server.
   Creating a JDBC Connection is very easy and requires two steps:
   1. Register and Load the Driver: Using Class.forName(), Driver class is registered to the DriverManager and loaded in the memory.
   2. Use DriverManager to get the Connection object: We get connection object fromDriverManager.getConnection() by passing Database URL String, username and password as argument.

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

   Connection con = null; try{     // load the Driver Class     Class.forName("com.mysql.jdbc.Driver");       // create the connection now     con = DriverManager.getConnection("jdbc:mysql://localhost:3306/UserDB",                     "pankaj",                     "pankaj123");     }catch (SQLException e) {             System.out.println("Check database is UP and configs are correct");             e.printStackTrace();     }catch (ClassNotFoundException e) {             System.out.println("Please include JDBC MySQL jar in classpath");             e.printStackTrace();     }

5. What is the use of JDBC DriverManager class?

   JDBC DriverManager is the factory class through which we get the Database Connection object. When we load the JDBC Driver class, it registers itself to the DriverManager, you can look up the JDBC Driver classes source code to check this.
   Then when we call DriverManager.getConnection() method by passing the database configuration details, DriverManager uses the registered drivers to get the Connection and return it to the caller program.

6. How to get the Database server details in java program?

   We can use DatabaseMetaData object to get the database server details. When the database connection is created successfully, we can get the meta data object by calling getMetaData() method. There are so many methods in DatabaseMetaData that we can use to get the database product name, it’s version and configuration details.

   1 2	DatabaseMetaData metaData = con.getMetaData(); String dbProduct = metaData.getDatabaseProductName();

7. What is JDBC Statement?

   JDBC API Statement is used to execute SQL queries in the database. We can create the Statement object by calling Connection getStatement() method. We can use Statement to execute static SQL queries by passing query through different execute methods such as execute(), executeQuery(), executeUpdate() etc.
   Since the query is generated in the java program, if the user input is not properly validated it can lead to SQL injection issue, more details can be found at SQL Injection Example.
   By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if we want to work with multiple ResultSet objects, then each must have been generated by different Statement objects. All execute() methods in the Statement interface implicitly close a statment’s current ResultSet object if an open one exists.

8. What is the difference between execute, executeQuery, executeUpdate?

   Statement execute(String query) is used to execute any SQL query and it returns TRUE if the result is an ResultSet such as running Select queries. The output is FALSE when there is no ResultSet object such as running Insert or Update queries. We can use getResultSet() to get the ResultSet and getUpdateCount()method to retrieve the update count.
   Statement executeQuery(String query) is used to execute Select queries and returns the ResultSet. ResultSet returned is never null even if there are no records matching the query. When executing select queries we should use executeQuery method so that if someone tries to execute insert/update statement it will throw java.sql.SQLException with message “executeQuery method can not be used for update”.
   Statement executeUpdate(String query) is used to execute Insert/Update/Delete (DML) statements or DDL statements that returns nothing. The output is int and equals to the row count for SQL Data Manipulation Language (DML) statements. For DDL statements, the output is 0.
   You should use execute() method only when you are not sure about the type of statement else use executeQuery or executeUpdate method.

9. What is JDBC PreparedStatement?

   JDBC PreparedStatement object represents a precompiled SQL statement. We can use it’s setter method to set the variables for the query.
   Since PreparedStatement is precompiled, it can then be used to efficiently execute this statement multiple times. PreparedStatement is better choice that Statement because it automatically escapes the special characters and avoid SQL injection attacks.

10. How to set NULL values in JDBC PreparedStatement?

    We can use PreparedStatement setNull() method to bind the null variable to a parameter. The setNull method takes index and SQL Types as argument, for example
    ps.setNull(10, java.sql.Types.INTEGER);.

11. What is the use of getGeneratedKeys() method in Statement?

    Sometimes a table can have auto generated keys used to insert the unique column value for primary key. We can use Statement getGeneratedKeys() method to get the value of this auto generated key.