JDBC API: (Java Database Connectivity Application Programming Interface)
----------
--> It is a set of classes and interfaces under "java.sql" package that can be used
to connect java program with the database and make communication.
Classes under JDBC API
----------------------
1. DriverManager -->Used to establish connection to the database
2. SQLException -->represents the exception caused in sql syntax.
3. ClassNotFoundException-->represents the exception caused if suitable database
driver is not found.
Interfaces Under JDBC API:
-------------------------
1.Connection --> used to hold the connection to the database.
eg. Connection con=DriverManager.getConnection("url");
2. Statement
3. PreparedStatement
Both are used to execute sql queries. However, PreparedStatement prevents SQL
injection attack.
4. CallableStatement -->used to execute stored procedure
5. ResultSet --> used to hold the data of database table returned by sql select
query into main memory
6. ResultSetMetaData -->used to get meta data information from ResultSet like name
of columns, number of columns.
Steps for JDBC:
---------------
1. Load the database driver
eg. Class.forName("drivername"); //throws ClassNotFoundException
2. Connect to the database.
eg. Connection con=DriverManager.getConnection("url");
//throws SQLException
3. Create sql query and execute.
eg. String sql="insert into tblname............";
Statement st=con.createStatement();
st.execute(sql); //throws SQLException
4. Close the connection to the database.
eg. con.close(); //throws SQLException
To do:
------
1. Open MySQL and do the following:
a. Create a database db_java.
b. Create a table tbL_person
id int PK AI
name varchar(20) not null
address varchar(20) not null
salary double not null
Inserting record:
----------------
package jdbc;
import java.sql.*;
public class SaveRecord {
public static void main(String[] args) {
String url="jdbc:mysql://localhost:3306/db_java";
String uname="root";
String pwd="";
try{
//step1: Load the database driver
Class.forName("com.mysql.jdbc.Driver");
//step2: connect to the database
Connection con=DriverManager.getConnection(url,uname,pwd);
//step3: create sql query and execute it
String sql="insert into
tbL_person(name,address,salary)values('Ram','Ktm',45000)";
Statement st=con.createStatement();
st.execute(sql);
//step4: close the connection
con.close();
System.out.println("Record Saved");
}catch(Exception ex){
System.out.println(ex);
}
}
}
Inserting multiple records
---------------------------
String sql="insert into tbL_person(name,address,salary)values('sita','ktm',34000),
('rita','bkt',56000)";
To update
----------
String sql="update tbL_person set salary=12000 where id=1";
To delete:
----------
String sql="delete from tbL_person where id=2";
---------------------------------------------------------------------
To display all records
----------------------
package jdbc;
import java.sql.*;
public class DisplayRecord {
public static void main(String[] args) {
String url="jdbc:mysql://localhost:3306/db_java";
String uname="root";
String pwd="";
try{
//step1: Load the database driver
Class.forName("com.mysql.jdbc.Driver");
//step2: connect to the database
Connection con=DriverManager.getConnection(url,uname,pwd);
//step3: create sql query and execute it
String sql="select * from tbL_person";
Statement st=con.createStatement();
ResultSet rs=st.executeQuery(sql);
while(rs.next()){
System.out.println(rs.getString("id"));
System.out.println(rs.getString("name"));
System.out.println(rs.getString"address"));
System.out.println(rs.getString("salary"));
}
//step4: close the connection
con.close();
}catch(Exception ex){
System.out.println(ex);
}
}
}
To do:
------
To display only those records whose salary is more than 25000
To display only those records whose salary is more than 25000 and address is
"Kathmandu".
---------------------------------------------------------------------
Inserting into database table taking input from user
----------------------------------------------------
WAP to input name,address,salary of a person and insert(save) into a table.
WAP to input name,address,salary of 5 persons and insert(save) into a table.
WAP to input id and delete the record matching that id.
WAP to input new salary and id and update the salary for given id.
---------------------------------------------------------------------
Use GUI to insert and display the records.
Save Record:
-----------
package jdbc;
import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
import java.sql.*;
public class SaveRecord_UsingForm implements ActionListener{
JFrame f1;
JButton b1,b2;
JTextField t1,t2,t3;
JLabel L1,L2,L3;
SaveRecord_UsingForm(){
f1=new JFrame();
f1.setSize(400,200);
f1.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
f1.setLayout(new GridLayout(4,2,5,5));
L1=new JLabel("Name");
L2=new JLabel("Address");
L3=new JLabel("Salary");
t1=new JTextField();
t2=new JTextField();
t3=new JTextField();
b1=new JButton("Submit");
b2=new JButton("Reset");
f1.add(L1);f1.add(t1);
f1.add(L2);f1.add(t2);
f1.add(L3);f1.add(t3);
f1.add(b1);f1.add(b2);
b1.addActionListener(this);
b2.addActionListener(this);
f1.setVisible(true);
}
public static void main(String[] args) {
new SaveRecord_UsingForm();
}
public void actionPerformed(ActionEvent e){
if(e.getSource()==b1){
//save the submitted data to the database
String name=t1.getText();
String address=t2.getText();
double salary=Double.parseDouble(t3.getText());
String url="jdbc:mysql://localhost:3306/db_java";
String uname="root";
String pwd="";
try{
//step1: Load the database driver
Class.forName("com.mysql.jdbc.Driver");
//step2: connect to the database
Connection con=DriverManager.getConnection(url,uname,pwd);
//step3: create sql query and execute it
String sql="insert into tbL_person(name,address,salary)values(?,?,?)";
PreparedStatement st=con.prepareStatement(sql);
st.setString(1, name);
st.setString(2, address);
st.setDouble(3, salary);
st.execute();
//step4: close the connection
con.close();
JOptionPane.showMessageDialog(null,"Record Saved");
}catch(Exception ex){
System.out.println(ex);
}
}
else if(e.getSource()==b2){
t1.setText("");
t2.setText("");
t3.setText("");
}
}
}
--------------------------------------------------------------------
Display Record
import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
import java.sql.*;
public class DisplayRecordInTable implements ActionListener{
JFrame f1;
JButton b1;
JTable t1;
DisplayRecordInTable(){
f1=new JFrame();
f1.setSize(500,500);
f1.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
f1.setLayout(new FlowLayout());
b1=new JButton("Show");
f1.add(b1);
b1.addActionListener(this);
f1.setVisible(true);
}
public static void main(String[] args) {
new DisplayRecordInTable();
}
public void actionPerformed(ActionEvent e){
String url="jdbc:mysql://localhost:3306/db_java";
String uname="root";
String pwd="";
try{
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection(url,uname,pwd);
String sql="select * from tbL_person";
Statement st=con.createStatement();
ResultSet rs=st.executeQuery(sql);
String[] cols={"ID","NAME","ADDRESS","SALARY"};
Object[][]data=new Object[50][4];
int i=0;
while(rs.next()){
data[i][0]=rs.getString("id");
data[i][1]=rs.getString("name");
data[i][2]=rs.getString("address");
data[i][3]=rs.getString("salary");
i++;
}
con.close();
t1=new JTable(data,cols);
JScrollPane jp=new JScrollPane(t1);
f1.add(jp);
f1.setVisible(true);
}catch(Exception ex){
System.out.println(ex);
}
}
}
------------------------------------------------------------------
To do:
Create a login interface. When user clicks login button verify whether the user is
valid or invalid.
create a table 'users'
id int pk ai
username varchar(30) not null
password varchar(30) not null
Insert any two records.
----------------------------------------------------------------------
Creating stored procedure and writing java program to execute it.
use db_java;
delimiter //
create procedure insert_record
(n varchar(30), a varchar(30), s double)
begin
insert into tbL_person(name,address,salary)values(n,a,s);
end
//
---Query Ok---message is shown--
delimeter ;
Write a java program that executes a stored procedure
'insert_record(?,?,?)' that inserts name,address and salary.
import java.util.*;
import java.sql.*;
public class InsertRecordUsingStoredProcedure {
public static void main(String[] args) {
Scanner ob=new Scanner(System.in);
System.out.println("Enter name,address,salary");
String name=ob.next();
String address=ob.next();
double salary=ob.nextDouble();
String url="jdbc:mysql://localhost:3306/db_java";
String uname="root";
String pwd="";
try{
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection(url,uname,pwd);
CallableStatement st=con.prepareCall("{call insert_record(?,?,?)}");
st.setString(1, name);
st.setString(2, address);
st.setDouble(3, salary);
st.execute();
con.close();
System.out.println("Record Saved");
}catch(Exception ex){
System.out.println(ex);
}
}
}
------------------------------------------------------------------
Write a java program that executes a strored procedure
'display_record()' that diplays all records (id/name/address/salary)
of the employees.
use db_java;
delimiter //
create procedure display_record
()
begin
select * from tbL_person;
end
//
delimiter ;
---------------------------------------------------------------------
How can you execute SQL queries in Java?
-->SQL queries can be executed by using the interface Statement or
PreparedStatement.PreparedStatement prevents sql injection while Statement does
not.
--> The methods used are executeUpdate(), executeQuery() and
execute().
a. executeUpdate()
-->used to execute sql insert, update and delete queries.
-->returns the number of rows affected.
eg. String sql="delete from tblname";
Statement st=con.createStatement();
int count=st.executeUpdate(sql);
System.out.println(count+" rows affected");
b. executeQuery()
-->used to execute sql select query
-->returns ResultSet
eg. String sql="select * from tblname";
Statement st=con.createStatement();
ResultSet rs=st.executeQuery(sql);
while(rs.next())
{
...........
...........
}
c. execute()
-->used to execute any sql queries.
-->returns "true" if sql query contains select query otherwise it returns false.
--> Is useful in the condition when we do not the know the query to be executed.
eg. void test(String sql)
{
Statement st=con.createStatement();
if(st.execute(sql)==true)
{
ResultSet rs=st.executeQuery(sql);
while(rs.next()){
...........
}
}
else{
System.out.println("Action Completed Successfully");
}
}
What is connection pooling and why it is used?
Connection pooling is a technique of creating and managing a pool of connections
that are ready for use by any thread that needs them. Connection pooling can
greatly increase the performance of your Java application, while reducing overall
resource usage.A JDBC connection pool is a group of reusable connections for a
particular database. Because creating each new physical connection is time
consuming, the server maintains a pool of available connections to increase
performance.
