JDBC(Java Database Connectivity)

Finally! entered into Advance Java concepts. So, the first one which I explored is JDBC which is nothing but set of APIs/ library/ framework which allows the java application to connect to the database. That connection is made and handled using JDBC drivers. 

We have 4 types of drivers available to us but their usage is requirement dependent. Preferably, Type 4 driver is used as it provides direct communication between java-based driver and vendor’s DB using socket. Different database community Oracle, MySQL, PostgreSQL, MongoDB etc. provide their specific drivers. I am using MySQL. I installed the driver specific to MySQL in following steps:

  1. Download MySQL Connector/J.
  2. Extract the file.
  3. Now, come to eclipse.
  4. Right click on project name.
  5. Choose Build ppath -> Configure Build path.
  6. Go to Libraries tab.
  7. Click “Add External JARs”
  8. Browse your system to find location of extracted folder.
  9. Open that, and choose mysql connector’s jar file.
  10. Click Apply.

Done with driver linking to project. There are basically 5 steps for java database connectivity:

  1. Load the driver
  2. Create the connection
  3. Write SQL statement
  4. Execute SQL statement
  5. Close the connection

  • Loading the driver:

We use following syntax to load the driver in java application


Class.fromName("com.mysql.jdbc.Driver")

forName() and Class are used to register driver class.

Note: Driver class name is different for different vendors. I have written for MySQL.

  • Creating connection

Connection needs to be established with particular DB.


String url = "jdbc:mysql://localhost/dbName";

Connection conn = DriverManager.getConnection(url,username,password);

In url, jdbc is API, mysql is database we are using, localhost is server name, dbName is name pd database we want to connect to.

  • Write SQL statement

Time to write the query to database. Statement interface(java.sql) allows to execute query with database.


String sql = "Select * from Student";

Statement stmt = conn.createStatement();

  • Execute the statement


stmt.executeQuery();

  • Close the connection


conn.close();

Let’s consider a table “Student” having attributes: roll(int) and name(varchar). This table is used for giving examples for coming topics in this post.

Prepared Statements

It is used to eliminate concatenation of strings in case of insert query primarily. If we have large number of columns then using Statement interface is not efficient. We should rather use prepared statement.


String sql ="Insert into Student values(?,?)";

PreparedStatement pStmt = conn.prepareStatement(sql);

//setting rollnumber column

pStmt.setInt(1, 10);

//setting name column

pStmt.setString(2, "Shilpa");

pStmt.executeUpdate();

Note: executeQuery() is used for selecting(retrieving the data) and executeUpdate() is used for update, delete, insert operations.

Batch Processing

It is execution of multiple SQL queries. Needed for faster performance. They must follow ACID properties of transactions if required. Written as:


//important to set the auto commit 'off'

conn.setAutoCommit(false);

String sql1 = Insert into Student VALUES(12, "Radhika");

String sql2 = Insert into Student VALUES(13, "Sonia");

Statement s = conn.createStatement();

//creating batch of statements

s.addBatch(sql1);

s.addBatch(sql2);

s.executeBatch();

conn.commit();

 

Stored procedures:

Stored procedures are useful to execute some business logic. These are precompiled. We create stored procedures in routines tab of phpMyAdmin. We provide parameters(IN, OUT) and the definition of procedure. Coming to java code, how to call stored procedure?


Statement s = conn.createStatement();

String sql = "{call storedProcedureName(?,?)}";

CallableStatement cStmt = conn.prepareCall(sql);

//setting values for '?'

cStmt.setInt(1, 2);

cStmt.setString(2, "Soumya");

cStmt.execute();

Leave a comment