Java database connection Code Examples

DHARMEDRA SAHU

Introduction

JDBC is an unofficial acronym for java database connectivity. JDBC helps us to connect to a database and execute SQL statements against a database. JDBC api provides set of interfaces and there are different implementations respective to different databases.

JDBC is a next example of difference between abstract class and interface. JDBC provides a set of interfaces and it is a contract between java developers and database providers. Every database is unique in its own way and we don’t want to create separate programs for each database.

Java application developers program using the jdbc interfaces and database developer provide the implementation for that jdbc interfaces. We add the respective implementation to the application and using the corresponding database. A classic use of java interfaces.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Sun Microsystem now Oracle provides JDBC Api and other Database provider/Vendor like MySql, Oracle, Db2 etc. are providing their respective implementation that is called as JDBC Driver Implementation.

In this post, we will try to explain Java Connectivity example with various Database like MySql, Oracle, Db2 and other Open Source Database.

JDBC Pictorial Overview:

Adapter Design Pattern

JDBC Architecture: Please find Self explanatory JDBC architecture explanation using diagram below

JDBC-Architecture

 

JDBC manages these three main programming activities:

  • connecting to a database;
  • sending queries and update statements to the database;
  • retrieving and processing the results received from the database in answer to the query.

Details about JDBC

The JDBC API is in the package java.sql it consists of 8 interfaces, 6 classes and 3 exceptions in JDK1.1.

Interfaces:

  • CallableStatement
  • Connection
  • DatabaseMetaData
  • Driver
  • PreparedStatement
  • ResultSet
  • ResultSetMetaData
  • Statement

Classes:

  • Date
  • DriverManager
  • DriverPropertyInfo
  • Time
  • Timestamp
  • Types

Exceptions:

  • DataTruncation
  • SQLException
  • SQLWarning

The most important ones are DriverManager, Connection, Statement, ResultSet.

JDBC Connection Strings

The following table lists the Connection Strings of some of the popular RDBMS.

DataBase Type DriverClassName Connection String
MySQL com.mysql.jdbc.Driver jdbc:mysql://<hostname>:<portNumber>/<databaseName>Default host: 127.0.0.1
Port# 3306
PostgreSql org.postgresql.Driver jdbc:postgresql://<hostname>:<portNumber>/<databaseName>Default host: 127.0.0.1
Port# 5432
SQLServer com.microsoft.sqlserver.jdbc.SQLServerDriver jdbc:sqlserver://<hostname>:<portNumber>;<databaseName>Default host: 127.0.0.1
Port# 1433
Oracle oracle.jdbc.driver.OracleDriver jdbc:oracle:thin:@<hostname>:<portNumber>:<databaseName>Default host: 127.0.0.1
Port# 1521
DB2 COM.ibm.db2os390.sqlj.jdbc.DB2SQLJDriver
(DB2 JDBC Type 4 Driver)		 jdbc:db2://<hostname>:<portNumber>/<databaseName>Default host: 127.0.0.1
Port# 50000
DB2
(on As/400)		 com.ibm.as400.access.AS400JDBCDriver jdbc:as400://<hostname>:<portNumber>/<databaseName>;
Sybase com.sybase.jdbc.SybDriver jdbc:Sybase:Tds:<hostname>:<portNumber>/<databaseName>
Sybase net.sourceforge.jtds.jdbc.Driver
(jTDS driver)		 jdbc:jtds:Sybase://<hostname>:<portNumber>/<databaseName>
Sybase com.sybase.jdbc2.jdbc.SybDriver
(jdbc2 driver version)		 jdbc:Sybase:Tds:<hostname>:<port>?ServiceName=<databaseName>

Note: We are hoping your database already installed and running in your system, please find required software as given below –

  • Database already installed (Depend on User requirement like MS Access, MySQL etc.)
  • JDK 1.5 or Higher version installed
  • eclipse 3.4 or Higher Version.
  • Ant

MySQL Database Connection

MySQL is a leading open source database management system. It is a multi user, multithreaded database management system. MySQL is especially popular on the web. It is one part of the very popular LAMP platform consisting of Linux, Apache, MySQL, and PHP. Currently MySQL is owned by Oracle. MySQL database is available on most important OS platforms. It runs on BSD Unix, Linux, Windows, or Mac OS.

Please follow below link for more information on MySQL Database Connection with Examples: Please click on below link to navigate examples

MySQL Database Connection With Examples

 

DB2 Database Connection

IBM® DB2® Universal Database™ (UDB) supports all the key Internet standards, making it an ideal database for use on the Web. It has in-memory speed to facilitate Internet searches and complex text matching combined with the scalability and availability characteristics of a relational database. DB2 UDB supports WebSphere®, Java™, and XML technology, making it easy for you to deploy your e-business applications.

DB2 Universal Database supports many types of Java programs. It provides driver support for client applications and applets written in Java using JDBC™. It also supports embedded SQL for Java (SQLJ), Java user-defined functions (UDFs), and Java stored procedures.

This big picture describes the Java application development environment provided by the DB2 UDB Universal Developer’s Edition Version 8 (UDE), DB2 for z/OS™ and OS/390® (V7) and iSeries™ (V5R2).

DB2 Database Connection With Examples

 

Oracle Database Connection

The JDBC DriverManager class defines objects which can connect Java applications to a JDBC driver. DriverManager is considered the backbone of JDBC architecture. DriverManager class manages the JDBC drivers that are installed on the system. Its getConnection() method is used to establish a connection to a database. It uses a username, password, and a jdbc url to establish a connection to the database and returns a connection object. A jdbc Connection represents a session/connection with a specific database. Within the context of a Connection, SQL, PL/SQL statements are executed and results are returned. An application can have one or more connections with a single database, or it can have many connections with different databases. A Connection object provides metadata i.e. information about the database, tables, and fields. It also contains methods to deal with transactions.

JDBC 4.0 is new and advance specification of JDBC. It provides the following advance features

  • Connection Management
  • Auto loading of Driver Interface.
  • Better exception handling
  • Support for large object
  • Annotation in SQL query.

JDBC Driver

JDBC Driver is required to process SQL requests and generate result. The following are the different types of driver available in JDBC.

  • Type-1 Driver or JDBC-ODBC bridge
  • Type-2 Driver or Native API Partly Java Driver
  • Type-3 Driver or Network Protocol Driver
  • Type-4 Driver or Thin Driver

Please find example and more information in below link:

Oracle Database Connection With Examples

 

Microsoft Access Database Connection

 

TBD

 

Microsoft SQL Server Database Connection

TBD

Leave a Reply

Your email address will not be published. Required fields are marked *