1. Download JDBC driver for MySQL
First, in order to have Java program working with MySQL, we need a JDBC driver for MySQL. Browse this URL:
to download the latest version of the JDBC driver for MySQL called Connector/J. MySQL Connector/J comes into 2 major versions: 5.1 and 8.0. The latest version 8.0 supports JDBC 4.2 and JDK 8 or higher.
In this example we are using MySql as the database. So we need to know following informations for the mysql database:
Driver class: The driver class for the mysql database is com.mysql.jdbc.Driver.
Connection URL: The connection URL for the mysql database is jdbc:mysql://localhost:3306/sonoo where jdbc is the API, mysql is the database, localhost is the server name on which mysql is running, we may also use IP address, 3306 is the port number and sonoo is the database name. We may use any database, in such case, we need to replace the sonoo with our database name.
Username: The default username for the mysql database is root.
Password: It is the password given by the user at the time of installing the mysql database. In this example, we are going to use root as the password.
Let's first create a table in the mysql database, but before creating table, we need to create database first.
create database p4n;
use p4n;
create table emp(id int(10),name varchar(40),age int(3)); Example to Connect Java Application with mysql database
In this example, sonoo is the database name, root is the username and password both.
import java.sql.*;
class MysqlCon{
public static void main(String args[]){
try{
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection(
"jdbc:mysql://localhost:3306/p4n","root","root");
//here sonoo is database name, root is username and password
Statement stmt=con.createStatement();
ResultSet rs=stmt.executeQuery("select * from emp");
while(rs.next())
System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3));
con.close();
}catch(Exception e){ System.out.println(e);}
}
} Insert, update, delete & select record from database
package Tablep4n.in;
import java.sql.*;
public class InsertRecord
{
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String dburl = "jdbc:mysql://localhost/p4n";
static final String dbuser = "root";
static final String dbpass = "root";
public static void main(String[] args)
{
Connection con = null;
Statement stmt = null;
try
{
//Step 1 : Connecting to server and database
con = DriverManager.getConnection(dburl, dbuser, dbpass);
//Step 2 : Initialize Statement
stmt=con.createStatement();
//Step 3 : SQL Query
String query="INSERT INTO ITEM(PRICE,PRODUCT) VALUES('190','MousePad')";
String query1="INSERT INTO ITEM(PRICE,PRODUCT) VALUES('2870','Stationary')";
String query2="INSERT INTO ITEM(PRICE,PRODUCT) VALUES('765','Books')";
String query3="INSERT INTO ITEM(PRICE,PRODUCT) VALUES('3887','HardDisk')";
String query4="INSERT INTO ITEM(PRICE,PRODUCT) VALUES('953','Ram')";
String query5="INSERT INTO ITEM(PRICE,PRODUCT) VALUES('8746','Printer')";
String query6="INSERT INTO ITEM(PRICE,PRODUCT) VALUES('646','Keyboard')";
String query7="INSERT INTO ITEM(PRICE,PRODUCT) VALUES('947','Mouse')";
String query8="INSERT INTO ITEM(PRICE,PRODUCT) VALUES('850','JoyStick')";
String query9="INSERT INTO ITEM(PRICE,PRODUCT) VALUES('3252','Camera')";
//Step 4 : Run Query
stmt.executeUpdate(query);
stmt.executeUpdate(query1);
stmt.executeUpdate(query2);
stmt.executeUpdate(query3);
stmt.executeUpdate(query4);
stmt.executeUpdate(query5);
stmt.executeUpdate(query6);
stmt.executeUpdate(query7);
stmt.executeUpdate(query8);
stmt.executeUpdate(query9);
System.out.println("Record Inserted Successfully");
}
catch (SQLException e)
{
System.err.println("Cannot connect ! ");
e.printStackTrace();
}
finally {
System.out.println("Closing the connection.");
if (con != null) try { con.close(); } catch (SQLException ignore) {}
}
}
}
Output
Record Inserted Successfully
Closing the connection.
_RETRIEVE RECORDS
Once you inserted record you can retrieve record using ResultSet Object of JDBC. Here is the complete programming example of retrieving data from MySQL using JDBC.
package Tablep4n.in;
import java.sql.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class SelectRecord
{
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String dburl = "jdbc:mysql://localhost/p4n";
static final String dbuser = "root";
static final String dbpass = "root";
public static void main(String[] args)
{
Connection con = null;
Statement stmt = null;
try
{
//Step 1 : Connecting to server and database
con = DriverManager.getConnection(dburl, dbuser, dbpass);
//Step 2 : Initialize Statement
stmt=con.createStatement();
//Step 3 : SQL Query
String query="SELECT * FROM ITEM";
//Step 4 : Run Query In ResultSet
ResultSet rset = stmt.executeQuery(query);
while(rset.next())
{
System.out.print("ID: " + rset.getInt(1));
System.out.print(" Product : "+rset.getString(2));
System.out.println(" Price : "+rset.getString(3));
}
}
catch (SQLException e)
{
System.err.println("Cannot connect ! ");
e.printStackTrace();
}
finally {
System.out.println("Closing the connection.");
if (con != null) try { con.close(); } catch (SQLException ignore) {}
}
}
}Output
ID: 1 Product : ToothPaste Price : 200
ID: 2 Product : MousePad Price : 190
ID: 3 Product : Stationary Price : 2870
ID: 4 Product : Books Price : 765
ID: 5 Product : HardDisk Price : 3887
ID: 6 Product : Ram Price : 953
ID: 7 Product : Printer Price : 8746
ID: 8 Product : Keyboard Price : 646
ID: 9 Product : Mouse Price : 947
ID: 10 Product : JoyStick Price : 850
ID: 11 Product : Camera Price : 3252
Closing the connection.
_UPDATE AND DELETE RECORDS
Updating and Deleting Records in MySQL using JDBC is so simple. You have to follow same procedure and execute update or delete command. Here is the complete program.
package Tablep4n.in;
import java.sql.*;
public class UpdateRecords
{
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String dburl = "jdbc:mysql://localhost/p4n";
static final String dbuser = "root";
static final String dbpass = "root";
public static void main(String[] args)
{
Connection con = null;
Statement stmt = null;
try
{
//Step 1 : Connecting to server and database
con = DriverManager.getConnection(dburl, dbuser, dbpass);
//Step 2 : Initialize Statement
stmt=con.createStatement();
//Step 3 : SQL Query
String updatequery="UPDATE ITEM SET PRODUCT='UsbCable' Where ID=1";
String deletequery="DELETE FROM ITEM WHERE ID=9";
//Step 4 : Run Query
stmt.executeUpdate(updatequery);
System.out.println("Table Updated Successfully");
stmt.executeUpdate(deletequery);
System.out.println("Row Deleted Successfully");
System.out.println("******** Records Are *********");
String selectquery="SELECT * FROM ITEM";
//Step 4 : Run Query In ResultSet
ResultSet rset = stmt.executeQuery(selectquery);
while(rset.next())
{
System.out.print("ID: " + rset.getInt(1));
System.out.print(" Product : "+rset.getString(2));
System.out.println(" Price : "+rset.getString(3));
}
}
catch (SQLException e)
{
System.err.println("Cannot connect ! ");
e.printStackTrace();
}
finally {
System.out.println("Closing the connection.");
if (con != null) try { con.close(); } catch (SQLException ignore) {}
}
}
}Output
Table Updated Successfully
Row Deleted Successfully
******** Records Are *********
ID: 1 Product : UsbCable Price : 200
ID: 2 Product : MousePad Price : 190
ID: 3 Product : Stationary Price : 2870
ID: 4 Product : Books Price : 765
ID: 5 Product : HardDisk Price : 3887
ID: 6 Product : Ram Price : 953
ID: 7 Product : Printer Price : 8746
ID: 8 Product : Keyboard Price : 646
ID: 10 Product : JoyStick Price : 850
ID: 11 Product : Camera Price : 3252
Closing the connection.
_
Comments