Jdbc codes connection 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

Источник

How to connect to a database with JDBC

In this JDBC tutorial, you will learn how to write Java code to establish connection to a relational database.

In order to make a connection to a specific database system, it requires doing the following 2 steps:

• • Load appropriate JDBC driver class using Class.forName() statement.
  • Establish a connection using DriverManager.getConnection() statement.

  The DriverManager class is available from package java.sql . There are three versions of getConnection() method which allow us to establish a connection to a database in three different ways:

  Establishes a connection from the given database URL in the form of: jdbc: subprotocol : subname

  Establishes a connection from the given database URL and a Properties object which includes additional information such as user and password.

  Establishes a connection from the given database URL, user and password.

  As you notice, all methods require a database URL which is a string in a special format that contains information about the database to connect to, such as server name, database name, user, password… Database URL is specific to a particular database system. Following is an example of a database URL for MySQL:

  jdbc:mysql://localhost:3306/test

  where localhost is server name, 3306 is port number, and test is database name.

  All the methods return a Connection object which is used for making SQL queries to the connected database.

  The following code examples illustrate establishing connection to a MySQL database. Follow this tutorial to download JDBC driver for MySQL.

  1. Java Connect to Database Example with JDBC 3.0 or older

  import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class ConnectJDBC3 < public static void main(String[] args) < String databaseURL = "jdbc:mysql://localhost:3306/test"; String user = "user"; String password = "password"; Connection conn = null; try < Class.forName("com.mysql.jdbc.Driver"); conn = DriverManager.getConnection(databaseURL, user, password); if (conn != null) < System.out.println("Connected to the database"); >> catch (ClassNotFoundException ex) < System.out.println("Could not find database driver class"); ex.printStackTrace(); >catch (SQLException ex) < System.out.println("An error occurred. Maybe user/password is invalid"); ex.printStackTrace(); >finally < if (conn != null) < try < conn.close(); >catch (SQLException ex) < ex.printStackTrace(); >> > > >

  2. Java Connect to Database Example with JDBC 4.0 or newer

  From JDBC 4.0 (Java 6), we can safely remove the Class.forName() statement. Following are three examples of connection to the same database in three different ways.

  Example 1: getConnection(String url)

  In this way, we must pass the user and password directly into the database URL:

  import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class DBConnect1 < public static void main(String[] args) < String databaseURL = "jdbc:mysql://localhost:3306/test?user=root&password=root123"; Connection conn = null; try < conn = DriverManager.getConnection(databaseURL); if (conn != null) < System.out.println("Connected to the database"); >> catch (SQLException ex) < System.out.println("An error occurred. Maybe user/password is invalid"); ex.printStackTrace(); >finally < if (conn != null) < try < conn.close(); >catch (SQLException ex) < ex.printStackTrace(); >> > > >

  Example 2: getConnection(String url, Properties info) :

  In this way, we put the user and password in a Properties object:

  import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.Properties; public class DBConnect2 < public static void main(String[] args) < String databaseURL = "jdbc:mysql://localhost:3306/test"; Connection conn = null; try < Properties props = new Properties(); props.put("user", "root"); props.put("password", "root123"); conn = DriverManager.getConnection(databaseURL, props); if (conn != null) < System.out.println("Connected to the database"); >> catch (SQLException ex) < System.out.println("An error occurred. Maybe user/password is invalid"); ex.printStackTrace(); >finally < if (conn != null) < try < conn.close(); >catch (SQLException ex) < ex.printStackTrace(); >> > > >

  Example 3: getConnection(String url, String user, String password)

  In this way, we supply the user and password directly into the method’s arguments:

  import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class DBConnect3 < public static void main(String[] args) < String databaseURL = "jdbc:mysql://localhost:3306/test"; String user = "root"; String password = "root123"; Connection conn = null; try < conn = DriverManager.getConnection(databaseURL, user, password); if (conn != null) < System.out.println("Connected to the database"); >> catch (SQLException ex) < System.out.println("An error occurred. Maybe user/password is invalid"); ex.printStackTrace(); >finally < if (conn != null) < try < conn.close(); >catch (SQLException ex) < ex.printStackTrace(); >> > > >

  NOTE: Since Java 1.7, you can use the try-with-resources statement to make connection to database without explicitly closing the connection, for example:

  public void connectDatabase(String url, String user, String password) < try (Connection conn = DriverManager.getConnection(url, user, password)) < if (conn != null) < System.out.println("Connected to the database"); >> catch (SQLException ex) < System.out.println("An error occurred. Maybe user/password is invalid"); ex.printStackTrace(); >>

  That’s some Java code examples for connecting to a database with JDBC.

  JDBC API References:

  Connect to a specific database Tutorials:

  Other JDBC Tutorials:

  About the Author:

  

  Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He started programming with Java in the time of Java 1.4 and has been falling in love with Java since then. Make friend with him on Facebook and watch his Java videos you YouTube.

  Источник