Thursday, April 10, 2025

Why use PreparedStatement in Java JDBC? Example Tutorial

PreparedStatement in Java is one of several ways to execute SQL queries using JDBC API. Java provides Statement,
PreparedStatement and CallableStatement for executing queries. Out of these three, Statement is used for general-purpose queries, PreparedStatement is used for executing a parametric query, and CallableStatement is used for executing Stored Procedures. PreparedStatement is also a popular topic in java interviews. Questions like Difference between Statement and PreparedStatement in Java and How to prevent SQL Injection attacks in Java are popular java interview questions.

Friday, March 21, 2025

How to Fix java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/test [Solved]

The error "java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/test" occurs when you try to connect MySQL database running on your localhost, listening on port 3306 port from Java program but either you don't have MySQL JDBC driver in your classpath or driver is not registered before calling the getConnection() method. Since JDBC API is part of JDK itself, when you write a Java program to connect any database like MySQL, SQL Server, or Oracle, everything compiles fine, as you only use classes from JDK but at runtime, when the JDBC driver which is required to connect to the database is not available, JDBC API either throws this error or "java.lang.ClassNotFoundException: com.mysql.jdbc.Driver".

Tuesday, September 17, 2024

How to convert java.sql.Date to java.util.Date in Java - JDBC Example

You often need to convert java.sql.Date to java.util.Date while interacting with databases from Java application. Since JDBC the Java API for database connectivity uses java.sql.Date to represent and most of the java code accepts java.util.Date, you have no choice but to convert SQL date to util date in Java. Though, there is some fundamental difference between them e.g. java.sql.Date only contains date part e.g. day, month, and year, it doesn't contain any time attributes, but java.util.Date contains both date and time attributes e.g. hour, minutes, seconds, and milliseconds. So, when you convert a java.sql.Date to java.util.Date, the resultant java.util.Date instance has the time part as zero i.e 00:00:00 to reflect midnight.

JDBC Batch INSERT and UPDATE example in Java with PreparedStatement

JDBC API in Java allows the program to batch insert and update data into the database, which tends to provide better performance by simple virtue of fact that it reduces a lot of database round-trip which eventually improves overall performance. In fact, it’s one of JDBC best practices to insert and update data in batches. For those who don’t know what is batch insert and update, Java provides several ways to execute SQL queries, one of them is JDBC batch insert and update, on which instead of executing SQL query one by one using either Statement or PreparedSatement, you execute a query in batch and send a batch of the query to the database for execution instead of a single query. 

Friday, September 6, 2024

6 JDBC Performance Tips for Java Developers

JDBC Performance tips are a collection of some tried and tested ways of coding and applying process which improves the performance of JDBC code. The performance of core Java application or J2EE web application is very important, especially if its user database in the back end which tends to slow down performance drastically. do you experience your java j2ee web application being very slow (taking few seconds to process simple requests which involve database access, paging, sorting, etc) then the below tips may improve the performance of your Java application? these tips are simple in nature and can be applied to other programming language application which uses the database as back-end. 

Monday, September 2, 2024

10 Difference between Hibernate and JDBC in Java

JDBC is the core technology to access database from Java application but it require lot of boiler plate coding. Hibernate on the other hand an ORM (Object Relational Mapping) framework, which automatically map Java object to tables and records in database. You don't need to execute query to populate objects, Hibernate will do that for you. It also provides several benefit as compared to JDBC e.g. performance improvement via Proxy, lazy loading of associations and Caching, that's why both enterprise Java application and web application uses Hibernate to implement the DAO layer of application.

Friday, May 10, 2024

10 Examples of JdbcTemplate in Spring Framework

Hello Java programmers, If you have been using the Spring framework then you may know that the JdbcTempalte is one of the most useful classes of the Spring framework. The JdbcTempalte class provides lots of convenient methods for performing database-related tasks that take the pain away from using the JDBC API. If you have worked in any real-life Java project which connects to a Database using JDBC then you know that JDBC API is not very convenient to use as it requires a lot of boilerplate code. For example, to execute a simple SELECT query, you first need to create a Connection and Statement object to execute the query and iterate through a ResultSet Object to get the result of the query. Finally, you need to close all of these resources to prevent resource leaks in Java.

Saturday, September 2, 2023

How to use Named Query SQL in Spring Framework? Example Tutorial

Hello guys, many Java developers don't know that Spring Framework also provides support for NamedQuery where you can pass parameters in a much readable way by using named placeholders than the default placeholder of ? (question mark) which is used with PreparedStatement in Java. If you want to learn Spring JDBC then you have come to the right place. In the past, I have shared the best Spring Framework courses, books, and tutorials to learn Spring Framework and its different features, and in this example, I will show you how to use NamedQuery in Spring Framework. You can then use this technique to write much more readable queries on the Java layer for your application. 

Thursday, August 17, 2023

JDBC Database Connection Pool in Spring Framework – How to Setup Example

Setting up JDBC Database Connection Pool in Spring framework is easy for any Java application, just matter of changing a few configurations in the spring configuration file. If you are writing core java application and not running on any web or application server like Tomcat or  Weblogic,  Managing Database connection pool using Apache Commons DBCP and Commons Pool along-with Spring framework is a nice choice but if you have the luxury of having web server and managed J2EE Container, consider using Connection pool managed by J2EE server those are a better option in terms of maintenance, flexibility and also help to prevent java.lang.OutofMemroyError: PermGen Space in tomcat by avoiding loading of JDBC driver in web-app class-loader.

How to call Stored Procedures from Java using IN and OUT parameter - Spring Framework Example Tutorial

Spring Framework provides excellent support to call stored procedures from Java application. In fact, there are multiple ways to call stored procedures in Spring Framework, e.g. you can use one of the query() method from JdbcTemplate to call stored procedures, or you can extend abstract class StoredProcedure to call stored procedures from Java. In this Java Spring tutorial, we will see the second approach to call a stored procedure. It's more object-oriented, but at the same time requires more coding. StoredProcedure class allows you to declare IN and OUT parameters and call stored procedure using it's various execute() method, which has protected access and can only be called from a subclass.

Monday, July 3, 2023

How to setup JNDI Database Connection pool in Tomcat - Spring Tutorial Example

Setting the JNDI Database Connection pool in Spring and Tomcat is pretty easy. Tomcat server documentation gives enough information on how to set up a connection pool in Tomcat 5, 6, 7, 8, or 9. Here we will use Tomcat 7 along with the spring framework for creating a connection pool in the Tomcat server and accessing them in Spring using JNDI code. In our last article, we have seen how to set up a database connection pool in Spring for a core Java application that doesn't run on a web server or application server and doesn't have a managed Java EE container.

Thursday, May 25, 2023

What is difference between java.sql.Time, java.sql.Timestamp and java.sql.Date - JDBC interview Question

Difference between java.sql.Time, java.sql.Timestamp and java.sql.Date  is the most common JDBC question appearing on many core Java interviews. As JDBC provides three classes java.sql.Date, java.sql.Time and java.sql.Timestamp to represent date and time and you already have java.util.Date which can represent both date and time, this question poses a lot of confusion among Java programmer and that’s why this is one of those tricky Java questions are tough to answer. It becomes really tough if the differences between them are not understood correctly.

Wednesday, May 10, 2023

Why Timestamp cannot be used in place of Date in Java? Example

One of the tricky questions from the Java Interview is, "Can we pass a Timestamp instance to a method expecting java.util.Date?", it's a tricky question because the answer is both Yes and No. You can, of course, pass a Timestamp object to a method with the argument as Date because first, Timestamp is a subclass of java.util.Date and second it contains both date and time values which are missing in either java.sql.Date and java.sql.Time. So there is more reason to pass a Timestamp value to Date but you should not be doing that. Why? because Timestamp is not exactly Date.

Tuesday, May 9, 2023

Difference between Connected vs Disconnected RowSet in Java JDBC | RowSetProvider, RowSetFactory Example

RowSet interface is another key interface from JDBC API, which extends the ResultSet interface to provide support for the JavaBean component model. Along with convenient methods to set connection parameters like the setUrl(), setUsername(), setPassword() and setCommand(), it also defines getters and setters for different data types e.g. getLong() and setLong(). Another feature of RowSet is that it supports event listeners. You can attach listeners to RowSet object to listen for events, much like Observer design pattern. RowSet acts as Subject and notifies Observer on events like change in cursor location, change in a row, or change in the entire RowSet. There are two types of RowSet available in JDBC 4.1 API, connected and disconnected.

Thursday, April 20, 2023

How to Fix org.springframework.jdbc.BadSqlGrammarException: Implicit conversion from data type varbinary to date is not allowed

Hello Java programmers, if you are using JdbcTemplate of Spring Framework to interact with the database from Java program and getting "org.springframework.jdbc.BadSqlGrammarException: Implicit conversion from data type varbinary to date is not allowed" error then you have come to the right place. Earlier, I have taught you how to use JdbcTemplate in Java and Spring, and in this article, I will teach you how to fix "org.springframework.jdbc.BadSqlGrammarException: Implicit conversion from data type varbinary to date is not allowed" error in Spring-based Java application. 

Friday, April 14, 2023

SQL Server JDBC Error: The TCP/IP connection to the host Failed

I had installed SQL SERVER 2014 Express edition and I was trying to connect to SQL SERVER from Java program using JDBC, but I was repeatedly getting the following error:

com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host localhost, port 1433 has failed. Error: "connect timed out. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".Error while closing connection !!null

Wednesday, February 1, 2023

Difference between Statement vs PreparedStatement vs CallableStatement in Java JDBC

Hello Java programmers, if you are looking to find out the difference between Statement, PreparedStatement, and CallableStatement in Java then you have come to the right place. Earlier, I have shared common JDBC Interview Questions and in this article, I am going to explain what is the real difference between these Statement types in Java and when to use Statement, PreparedStatement, and CallableStatement in Java programs. JDBC API provides several classes and interfaces for various things, but three of the most important types of Statement classes are Statement, PreparedStatment, and CallableStatement. They are designed to execute different types of SQL queries. 

Saturday, August 27, 2022

Data Access Object (DAO) design pattern in Java - Tutorial Example

Data Access Object or DAO design pattern is a popular design pattern to implement the persistence layer of Java application. DAO pattern is based on abstraction and encapsulation design principles and shields the rest of the application from any change in the persistence layer e.g. change of database from Oracle to MySQL, change of persistence technology e.g. from File System to Database. For example, if you are authenticating the user using a relational database and later your company wants to use LDAP to perform authentication. If you are using the DAO design pattern to access the database, it would be relatively safe as you only need to make a change on Data Access Layer. DAO design pattern also keeps the coupling low between different parts of an application.

Friday, August 5, 2022

[Solved] How to solve java.lang.UnsatisfiedLinkError: no ocijdbc11 in Java with Oracle 11g, 12c, 19c and 21c

"java.lang.unsatisfiedlinkerror no ocijdbc11 in java.library.path" error comes when you try to connect to Oracle 11g database using OCI (thick) driver by using tns name, but the ocijdbc11.dll file is not available in PATH or java.library.path environment variable. ocijdbc11.dll is a native library and if you know Java searches native library in PATH or a location specified by java.library.path system property, if it doesn't find the dll, then it throws java.lang.unsatisfiedlinkerror no ocijdbc11 in java.library.path error. This dll is usually found in C:\Programs\Oracle\ora11g\bin\ocijdbc11.dll, but it could vary depending upon your Oracle installation.

Thursday, August 4, 2022

How to fix java.sql.SQLException: Invalid column index? Example

"java.sql.SQLException: Invalid column index" is a frequent error while working in Java Database Connectivity (JDBC). As the name suggests "Invalid column index" its related to accessing or setting column in your SQL Query using prepared statement in Java. I have seen "java.sql.SQLException: Invalid column index" coming mostly due to two reason:

1) Setting column data using setXXXX(int coloumIndex) e.g. setInt(0) setString(0)
2) Getting column data using getXXX(int columnIndex) e.g. getInt(0) getString(0)

The most common cause of "java.sql.SQLException: Invalid column index" is a misconception that column index started with "0" like array or String index but that's not true instead column index starts with "1" so whenever you try to get or Set column data with column index "0" you will get "java.sql.SQLException: Invalid column index".