JDBC Connectivity with MySql

In this tutorial, you will know how to JDBC connectivity with Mysql. Java Database Connectivity is a technology that enables the java program to manipulate data stored into the database. The interface for accessing relational databases from Java is Java Database Connectivity (JDBC).

JDBC Connectivity with MySql

You have to use the JDBC driver from MySQL. The MySQL JDBC driver is called MySQL Connector/J. You find the latest MySQL JDBC driver under the following URL: http://dev.mysql.com/downloads/connector/j. The download contains a JAR file. by following example it’s easy to understand.

Create Database with MySQL

Create a new database called Vedansh using following command.

create database vedansh;

Now create a table

CREATE TABLE userinfo (id INT NOT NULL AUTO_INCREMENT,
username VARCHAR(30) NOT NULL,
email VARCHAR(30),
PRIMARY KEY (ID));

Java file with JDBC Connection

package com.vedanshinfoway.mysql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

public class JDBCConn
{
private Connection con = null;
private Statement stmt = null;
private PreparedStatement preparedStmt = null;
private ResultSet rs = null;

public void readDataBase() throws Exception
{
try
{
// Load Driver for MYSQL
Class.forName(“com.mysql.jdbc.Driver”);
// connection with Database
con = DriverManager.getConnection(“jdbc:mysql://localhost/vedansh?” + “user=sqluser&password=sqluserpw”);

// statements allow to issue SQL queries to the database
stmt = con.createStatement();
String selquery = “SELECT * FROM vedansh.userinfo”;
// resultSet gets the result of the SQL query
rs = stmt.executeQuery(selquery);
writeResultSet(rs);

// preparedStatements can use variables and are more efficient
preparedStmt = con.prepareStatement(“INSERT INTO vedansh.userinfo values (default, ?, ?)”);
// parameters start with 1
preparedStmt.setString(1, “mayur”);
preparedStmt.setString(2, “info@vedanshinfoway.com”);
preparedStmt.executeUpdate();

// remove again the insert comment
preparedStmt = con.prepareStatement(“delete from vedansh.userinfo where username= ? ; “);
preparedStmt.setString(1, “mayur”);
preparedStmt.executeUpdate();

rs = stmt.executeQuery(“select * from vedansh.userinfo”);
writeMetaData(rs);

}
catch (Exception e)
{
throw e;
}
finally
{
rs.close();
stmt.close();
con.close();
}

}

private void writeMetaData(ResultSet rs) throws SQLException
{
//get metadata from the database
System.out.println(“The columns in the table are: “);
System.out.println(“Table: ” + rs.getMetaData().getTableName(1));
for (int i = 1; i<= rs.getMetaData().getColumnCount(); i++)
{
System.out.println(“Column ” +i + ” “+ rs.getMetaData().getColumnName(i));
}
}

private void writeResultSet(ResultSet rs) throws SQLException
{
// resultSet is initialised before the first data set
while (rs.next())
{
// it is possible to get the columns via name
// also possible to get the columns via the column number
// e.g., resultSet.getSTring(2);
String username = rs.getString(“username”);
String email = rs.getString(“email”);
System.out.println(“Username: ” + username);
System.out.println(“Email: ” + email);
}
}
}

Java Main class file

package com.vedanshinfoway.mysql.main;

import com.vedanshinfoway.mysql.*;

public class TestMain
{
public static void main(String…args)
{
JDBCConn jdbccon = new JDBCConn();
jdbccon.readDataBase();
}
}

Leave a Reply

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