JDBC – Example with MySQL

Home/Java - Tutorial/JDBC - Tutorial/JDBC – Example with MySQL
JDBC – Example with MySQL 2017-08-14T13:54:22+00:00

JDBC connection for MySQL:

To connect java application to MySQL database we must have at least one database created in MySQL.

And to create database in MySQL, it should be installed on your system.

So first of all install MySQL database in your system.

After installing it open MySQL console.

 

Create database with syntax create database databasename; and press enter.
You can see message like Query OK, 1 row affected (0.03 sec)
Now our database is created in MySQL. Second step is to create table in our database.
For creating table in particular database type Use databasename; and press enter and you can see message like database changed.
Now for creating table type create table tablename (field1 type of field1, field2 type of field2, field3 type of field3);
Now press enter again you can see the message like Query OK, 0 row affected (0.01 sec).
EX:
Create database java2all;
Use java2all;
Create table data (id int,name char(20),city char(20),age int);
Now the next step is to insert data into our table.
For inserting data simply type insert into table name (field1,field2,field3) values (value1,value2,value3);
EX:
insert into data (id,name,city,age) values (1,”java”,”abc”,300);
So by that’s way you can insert as many data as you want in your table. Now for viewing our data from table just type select * from tablename;
EX:
select * from data;

Now we have data in our table, table in our database, database in our MySQL and MySQL in our system.

So lets now move to JDBC program with MySQL.

Simple Statement in JDBC with MySQL:

EX:


 


Output:
Driver is loaded
Connection created
ID   Name  City    Age
1     java      abc    300
2     JDBC  xyz   200
3     JSP     mno  100
Key point:
String which we are writing in Class.forName("com.mysql.jdbc.Driver"); to load the driver.
String which we are writing in Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/java2all","root","root") to create connection with particular database.
Here the string jdbc:mysql://localhost:3306 is for connecting MySQL to JDBC in our local system and the name /java2all is our database name and 1st "root" is username of MySQL and 2nd "root" is password of MySQL.
Here no need of Data Source Name as in access but one jar file named mysql-connector-java-5.1.6-bin.jar must be loded in your java IDE.
In eclipse for adding jar file simply right click on your project and select build path ? configure build path and you can see window like this.

 

Click on libraries tab then click on Add External JARs..
And select proper path where your jar file is located on your system.
If you don’t have jar file you can easily download it from the web for free
After selecting your jar file you can see its now added to our library for that particular project.
 

 

After adding jar file in our library we can now easily run our program.

Statement in JDBC with MySQL for inserting data:


 

Output:
Driver is loaded
Connection created
Inserted

PreparedStatement in JDBC with MySQL:


 


Output:
Driver is loaded
Connection created
Inserted
You can run PreparedStatement program for JSP too with dynamic data.
For this we will cretae two JSP file one for inserting data (simple form with text box as per our table).
Second JSP file contains logic for connecting data base as well as PreparedStatement logic for inserting data.
Insert_data.jsp

 

Now insert value in text box as you want to insert in database as shown below.
 

 

You can see the data here which i want to insert in our database.
datamysql.jsp

 


Output:
Driver is loaded
Connection created
Inserted
You can see your data as we inserted through program.

 

CallableStatement in MySQL:

Now we all know that for CallableStatement first of all we must have stored procedure in our database.
Now how can we create stored procedure in MySQL.
For that follow the steps given below.
We already create a database in MySQL now for creating stored procedure for that particular database
Use java2all; (use databasename;)
Example stored procedure for addition of two numbers.
Copy and paste it in your MySQL console after selecting your database.
DELIMITER $$
CREATE PROCEDURE addproc( IN a INT, IN b INT, OUT c INT)
BEGIN
SET c = a + b;
END$$
You can get message like Query OK, 0 rows affected (0.04 sec)
It means your stored procedure is created successfully.
Here we create stored procedure for add two int number.
The stored procedure has 2 IN parameter and 1 OUT parameter so total 3 parameters
Now let us move to JDBC program for CallableStatement in MySQL

 


Output:
Driver is loaded
Connection created
addition = 30
To call a storedprocedure you can see the syntax in our program.
Call storedprocedurename(parameter);
Here two more methods are introduced 1st is registerOutParameter(3, Types);
We have to register our out parameter with the method given above.
This method has two arguments 1st is sequence of question mark we passed in calling of stored procedure.
2nd is out parameter type here it is integer type and question mark sequence is 3 so we write
cs.registerOutParameter(3, Types.INTEGER);
2nd method is getXXX(int sequence_number);
The working of this method is same as setXXX(), which we used in PreparedStatement and CallableStatement.
But setXXX() is for set a value and getXXX() is for getting a particular value as you can see in our program.
Now let’s take one more example of CallableStatement.
Here we are going to create stroredprocedure that returns the data as per the id.
Our data in table is something like that.
 

 

Stored Procedure:
DELIMITER $$
CREATE PROCEDURE datainfo`( IN sid INT,OUT sname VARCHAR(20), OUT scity VARCHAR(20),OUT sage INT)
BEGIN
select name,city,age INTO sname,scity,sage from data where id=sid;
END$$
We will pass the id when we call the stored procedure and it will return name,city and age as per the id we passed.
EX:

 


Output:
Enter ID
1
Driver is loaded
Connection created
Name = java
City = abc
Age  = 300

For closing the connection in each and every program of JDBC we should call the method close() through Connection object c.close();

As my friend suggest me we should call close() method to close our connection with database from finally block so close() method will execute in all circumstances.

Even if a try block has an exception our connection will be closed safely.

EXample with finally block:


 

Prev Next