Microsoft's ODBC (Open Database Connectivity) is the most commonly used driver to connect to the database as it can connect to almost all databases on most of the platforms. However, ODBC uses the concept of pointers and other constructs that are not supported by Java.
Therefore, JDBC-ODBC bridge driver was developed which translates the JDBC API to the ODBC API and vice versa. This bridge acts as interface that enables all DBMS which support ODBC (Open Data Base Connectivity) to interact with Java Applications. JDBC-ODBC bridge is implemented as a class file and a native library. The name of the class file is JdbcOdbc.class.
Figure shows the JDBC application architecture in which a front-end application uses JDBCAPI for interacting with JDBC Driver Manager. Here, JDBC Driver Manager is the backbone of JDBC architecture. It acts as interface that connects a Java application to the driver specified in the Java program. Next, is the JDBC-ODBC bridge which helps the JDBC to access ODBC data sources.
Making ODBC Connection
//JDBC-ODBC Bridge (But removed from Java 8)
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb,
*.accdb)};DBQ=" + "D:\\mydb.accdb"; Connection conn = DriverManager.getConnection(url);
Example-1
import java.sql.*;
public class DB_Test {
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver"); Connection conn=DriverManager.getConnection ("jdbc:mysql://localhost:3306/bca","root","");
//database="", username=root and password=""
System.out.println("Database connected"); Statement st=conn.createStatement();
//clearing data
String sql="DELETE FROM student"; st.execute(sql);
//inserting data
String sql1="INSERT INTO student(sid,name,address,contact) VALUES ('1','Raaju','Btm','9862612723'),
('2','Ram','Ktm','8811111111')";
st.execute(sql1);
System.out.println("Data Inserted Successfully\n");
//retrieving data
System.out.println("Data Before Update and Delete"); String sql4="SELECT * FROM student";
ResultSet rs=st.executeQuery(sql4);
System.out.println("Sid\t"+"Name\t"+"address\t"+"Contact");
while(rs.next()) {
String sid=rs.getString(1); String name=rs.getString(2); String address=rs.getString(3); String contact=rs.getString(4);
System.out.println(sid+"\t"+name+"\t"+address+"\t"+contact);
}
//updating data
String sql2="UPDATE student SET name='Hari',address='Ktm' WHERE sid='1'";
st.execute(sql2);
System.out.println("\nData Updated Successfully");
//deleting data
String sql3="DELETE FROM student WHERE sid='2'"; st.execute(sql3);
System.out.println("Data Deleted Successfully\n");
//retrieving data
System.out.println("Data After Update and Delete"); String sql5="SELECT * FROM student";
ResultSet rs1=st.executeQuery(sql5);
System.out.println("Sid\t"+"Name\t"+"address\t"+"Contact");
while(rs1.next()) {
String sid=rs1.getString(1); String name=rs1.getString(2); String address=rs1.getString(3); String contact=rs1.getString(4);
System.out.println(sid+"\t"+name+"\t"+address+"\t"+contact);
}
}cath(Exception e) { System.out.println(e);
}
}
}
Output
Database connected
Data Inserted Successfully
Data Before Update and Delete
Sid Name address Contact
1 Raaju Btm 9862612723
2 Ram Ktm 8811111111
Data Updated Successfully Data Deleted Successfully
Data After Update and Delete Sid Name address Contact 1 Hari Ktm 9862612723
Example-2
import javax.swing.*;
import javax.swing.table.*;
import java.awt.event.*;
import java.sql.*;
public class SwingDatabase {
Connection conn; Statement st;
//creating connection
void getConnection() {
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection
("jdbc:mysql://localhost:3306/bca","root","");
st=conn.createStatement();
} catch (Exception e) { JOptionPane.showMessageDialog(null, e);
}
}
SwingDatabase(){
getConnection();
JFrame jframe=new JFrame("This is a simple JFrame App");
jframe.setSize(600, 250); jframe.setLocationRelativeTo(null);
jframe.getContentPane().setLayout(null); jframe.setVisible(true);
JLabel lbl1=new JLabel("Student Id:"); lbl1.setBounds(20, 12, 100, 10); jframe.add(lbl1);
JTextField txt1=new JTextField();
txt1.setBounds(120, 10, 150, 20);
jframe.add(txt1);
JLabel lbl2=new JLabel("Student Name:");
lbl2.setBounds(20, 55, 100, 10);
jframe.add(lbl2);
JTextField txt2=new JTextField();
txt2.setBounds(120, 50, 150, 20);
jframe.add(txt2);
JLabel lbl3=new JLabel("Student Address: ");
lbl3.setBounds(20,85,120,30);
jframe.add(lbl3);
JTextField txt3=new JTextField();
txt3.setBounds(120, 90, 150, 20);
jframe.add(txt3);
JButton insert=new JButton("Insert"); I
nsert.setBounds(10, 140, 80, 20);
jframe.add(insert);
JButton update=new JButton("Update");
update.setBounds(100, 140, 80, 20);
jframe.add(update);
JButton delete=new JButton("Delete");
delete.setBounds(200, 140, 80, 20);
jframe.add(delete);
JButton view=new JButton("View"); view.setBounds(300, 140, 80, 20); jframe.add(view);
//insert
insert.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent ae) { String id=txt1.getText().toString();
String name=txt2.getText().toString();
String address=txt3.getText().toString();
try {
String sql="INSERT INTO student (sid,name,address) VALUES('"+id+"','"+name+"','"+address+"')";
st.execute(sql);
JOptionPane.showMessageDialog
(null, "Data Inserted Successfully");
}
catch(Exception e) { JOptionPane.showMessageDialog(null, e);
}
}
});
//update
update.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent ae) { String id=txt1.getText().toString();
String name=txt2.getText().toString(); String address=txt3.getText().toString();
try {
String sql="UPDATE student SET name='"+name+"',address='"+address+"' WHERE sid='"+id+"'";
st.execute(sql);
JOptionPane.showMessageDialog
(null, "Data Updated Successfully");
}catch(Exception e) { JOptionPane.showMessageDialog(null, e);
}
}
});
//delete
delete.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent ae) { String id=txt1.getText().toString();
try {
String sql="DELETE FROM student WHERE sid='"+id+"'";
st.execute(sql);
JOptionPane.showMessageDialog
(null, "Data Deleted Successfully");
}
catch(Exception e) {
JOptionPane.showMessageDialog(null, e);
}
}
});
//view
view.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent ae) {
//plotting in JTable
String[] columns= {"Sid","Name","Address"};
JTable jt=new JTable();
DefaultTableModel model = new DefaultTableModel();
jt.setModel(model);
model.setColumnIdentifiers(columns);
try {
String sql="SELECT * FROM student"; ResultSet rs=st.executeQuery(sql);
while(rs.next()) {
String id=rs.getString(1);
String name=rs.getString(2);
String address=rs.getString(3);
model.addRow(new String[]{id,name,address});
}
JScrollPane sp=new JScrollPane(jt);
sp.setBounds(300, 10, 250, 100);
jframe.add(sp);
}
catch(Exception e) {
JOptionPane.showMessageDialog(null, e);
}
}
});
}
public static void main(String[] args) {
SwingUtilities.invokeLater(new Runnable() {
public void run() {
new SwingDatabase();
}
});
}
}