Closing mysql connection in java

Establishing JDBC Connection in Java

Before establishing a connection between the front end i.e your Java Program and the back end i.e the database we should learn what precisely a JDBC is and why it came into existence. Now let us discuss what exactly JDBC stands for and will ease out with the help of real-life illustration to get it working.

What is JDBC?

JDBC is an acronym for Java Database Connectivity. It’s an advancement for ODBC ( Open Database Connectivity ). JDBC is a standard API specification developed in order to move data from frontend to the backend. This API consists of classes and interfaces written in Java. It basically acts as an interface (not the one we use in Java) or channel between your Java program and databases i.e it establishes a link between the two so that a programmer could send data from Java code and store it in the database for future use.

Illustration: Working of JDBC co-relating with real-time

Why JDBC Come into Existence?

As previously told JDBC is an advancement for ODBC, ODBC being platform-dependent had a lot of drawbacks. ODBC API was written in C, C++, Python, and Core Java and as we know above languages (except Java and some part of Python )are platform-dependent. Therefore to remove dependence, JDBC was developed by a database vendor which consisted of classes and interfaces written in Java.

Steps For Connectivity Between Java Program and Database

1. Import the Packages
2. Load the drivers using the forName() method
3. Register the drivers using DriverManager
4. Establish a connection using the Connection class object
5. Create a statement
6. Execute the query
7. Close the connections

Let us discuss these steps in brief before implementing by writing suitable code to illustrate connectivity steps for JDBC/

Step 1: Import the Packages

Step 2: Loading the drivers

In order to begin with, you first need to load the driver or register it before using it in the program. Registration is to be done once in your program. You can register a driver in one of two ways mentioned below as follows:

2-A Class.forName()

Here we load the driver’s class file into memory at the runtime. No need of using new or create objects. The following example uses Class.forName() to load the Oracle driver as shown below as follows:

Class.forName(“oracle.jdbc.driver.OracleDriver”);

2-B DriverManager.registerDriver()

DriverManager is a Java inbuilt class with a static member register. Here we call the constructor of the driver class at compile time. The following example uses DriverManager.registerDriver()to register the Oracle driver as shown below:

DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver())

Step 3: Establish a connection using the Connection class object

After loading the driver, establish connections as shown below as follows:

Connection con = DriverManager.getConnection(url,user,password)

• user: Username from which your SQL command prompt can be accessed.
• password: password from which the SQL command prompt can be accessed.
• con: It is a reference to the Connection interface.
• Url: Uniform Resource Locator which is created as shown below:

String url = “ jdbc:oracle:thin:@localhost:1521:xe”

Where oracle is the database used, thin is the driver used, @localhost is the IP Address where a database is stored, 1521 is the port number and xe is the service provider. All 3 parameters above are of String type and are to be declared by the programmer before calling the function. Use of this can be referred to form the final code.

Step 4: Create a statement

Once a connection is established you can interact with the database. The JDBCStatement, CallableStatement, and PreparedStatement interfaces define the methods that enable you to send SQL commands and receive data from your database.
Use of JDBC Statement is as follows:

Statement st = con.createStatement();

Note: Here, con is a reference to Connection interface used in previous step .

Step 5: Execute the query

Now comes the most important part i.e executing the query. The query here is an SQL Query. Now we know we can have multiple types of queries. Some of them are as follows:

The executeQuery() method of the Statement interface is used to execute queries of retrieving values from the database. This method returns the object of ResultSet that can be used to get all the records of a table.
The executeUpdate(sql query) method of the Statement interface is used to execute queries of updating/inserting.

Pseudo Code:

int m = st.executeUpdate(sql); if (m==1) System.out.println("inserted successfully : "+sql); else System.out.println("insertion failed");

Here sql is SQL query of the type String

Источник

JDBC MySQL database Connection example

In the previous couple of posts What is Java Database Connectivity (JDBC)? and Types of JDBC drivers in java?
We got to know some Jdbc basics.

In this post, we will learn how to connect a java program with a database. Here I am using the MYSQL database but you are free to choose any database you like to use. Steps would be similar for another database as well.

Project structure in Eclipse:

JDBC connection steps for MySQL database.

1. Register JDBC driver class
2. Creating Jdbc connection object
3. Creating Jdbc statement object
4. Executing SQL queries
5. Closing connection object

1. Register JDBC driver class

The forName() method of java.lang.Class class is used to register the JDBC driver class. This method is used to dynamically load the JDBC driver class. Note that forName method is static so directly we can refer this method my class name.
Syntax of forName() method

public static Class forName(String className) throws ClassNotFoundException

Note: From JDBC 4.0 onwards explicitly registering the Jdbc driver is optional. We just need to put database vender’s provided Jar in the project classpath, and then JDBC driver manager can detect and load the driver automatically.

Blow code snippet shows how we load MySQL database driver to establish database connection
Class.forName(“com.mysql.jdbc.Driver”);

2. Creating Jdbc connection object

We have three Jdbc methods available in java.sql.DriverManager class to connect jdbc program with any database.

1. public static Connection getConnection(String url,String user, String password) throws SQLException
2. public static Connection getConnection(String url,java.util.Properties info) throws SQLException
3. public static Connection getConnection(String url) throws SQLException

These all methods are static so we can refer it directly by Class Name.

All above three variants of getConnection() methods you can easily understand by looking at blow example covered.

3. Creating Jdbc statement object
Once we get Connection object using any one of method mentioned in step 2 we can create an object of jdbc statement.
We have hands full of methods available in Connection interface

Like below code snippet shows how we can create java.sql.Statement object using createStatement() method.
Statement st=con.createStatement();

Where con is an object or reference of java.sql.Connection Interface

4. Executing SQL queries

We have three execute(),executeUpdate() and executeQuery methods available in Jdbc to execute SQL query.
To learn how to make use of these method you can refer CRUD(Create,Read,Update and Delete) Operation using JDBC Statement and CRUD(Create,Read,Update and Delete) Operation using JDBC PreparedStatement

5.Closing connection object

If we close connection object statement then Statement and ResultSet objects will be closed automatically.The java.sql. Connection interface is used to close the connection by calling close() method.

Syntax of close() method

void close() throws SQLException;

Code snippet to close connection

Where conn is an object of java.sql.Connection Interface.

Note: Fron Java 7, JDBC can be used to try-with-resources statement to automatically close resources of type java.sql .Connection, java.sql.ResultSet, and java.sql.Statement.

As in this program, we are going to connect the java program with the MySQL database and not going to execute any SQL query so steps 3 and 4 are not involved.

In this example, we have used below getConnection method of java.sql.DriverManager class
public static Connection getConnection(String url,String user, String password) throws SQLException

This method takes three parameters database URL,username and password.

Here in all examples I have used MySQL database username =”root”,password = “root” what password had supplied during MYSQL database installation time and finally database URL = “jdbc:mysql://localhost:3306/jdbcdb” Where jdbc is the API, mysql is the database, localhost is the server name on which mysql database server is running, we may also use IP address instead machine name, 3306 is the port number and jdbcdb(Make you you have created this schema in MySQL database) is the database name. You may use any database name, in that case, You need to replace the jdbcdb with your database name.

Источник

Java Database Connectivity with MySQL

There are many ways you can connect to a MySQL database from Java. MySQL is a popular Open Source RDBMS (Relational Database Management System) commonly used in web applications due to its speed, flexibility and reliability.

In order to connect and access the MySQL database from Java, you can use Java Database Connectivity (JDBC) API. It allows you to connect to any database like Oracle, Microsoft SQL Server, or MySQL, provided you have the vendor’s implementation of the JDBC driver interface, which is required to connect the database.

Steps to develop JDBC application

1. Import JDBC Packages
2. Loading and Register a driver
3. Establishing a connection between java and database
4. Creation of statement object
5. Send and Execute SQL Query
6. Processing the Result Set
7. Closing Connections

Import JDBC Packages

You must include the import statements at the beginning of your program, which allows you to select, insert, update, and delete data in SQL tables.

Loading and Register a driver

You have to provide the code to register your installed driver with your program. You can register a driver in one of two ways. The most common approach to register a driver is to use Java’s Class.forName() method of the java.lang.Class class to load the JDBC drivers directly.

However, Class.forName() method is valid only for JDK-compliant Java virtual machines. It is not valid for Microsoft Java virtual machines. In this case you can use DriverManager.registerDriver() method. This class provides a basic service for managing a set of JDBC drivers.

Establishing a connection between java and database (URL Formulation)

This step is necessary to create a properly formatted address that points to the database to which you want to connect. Once you loaded the driver, you can establish a connection to the database with the static getConnection() method of the JDBC DriverManager class. Java provide three overloaded DriverManager.getConnection() methods:

1. getConnection(String url)
2. getConnection(String url, Properties prop)
3. getConnection(String url, String username, String password)

The getConnection() method method returns an object of the JDBC Connection class which needs as input a userid, password, connect string that identifies the JDBC driver to use, and the name of the database to which you want to connect.

Creation of statement object

The createStatement() method of your JDBC Connection object returns an object of the JDBC Statement class. When you execute Statement objects , it generate ResultSet objects, which is a table of data representing a database result set.

Send and Execute SQL Query

To query the MySql database , use the executeQuery() method of your Statement object. This method takes a SQL statement as input and returns an object of the JDBC ResultSet class.

Processing the Result Set

If you want to process the result set, to pull data out of the result set and iterate through it. You can use the next() method of your ResultSet object to loop through the results. This method iterate through the result set row by row, detecting the end of the result set when it is reached.

Closing Connections

Finally, to end the database session , you need to close all the database resources which immediately release the resources it’s using.

Alternatively, you can use a try-with-resources statement to automatically close the resources regardless of whether an SQLException has been thrown.

Источник