JDBC is a Java database connectivity technology from Oracle Corporation. This technology is an API for the Java programming language that defines how a client may access a database. It provides methods for querying and updating data in a database. JDBC is oriented towards relational databases such as MySQL. The JDBC classes are contained in the java.sql package.
To work with database from Java programs, it needs to first load the appropriate driver and then get a connection to the database via JDBC APIs. The JDBC connection supports creating and executing SQL statements. These may be update statements such as INSERT, UPDATE and DELETE, or they may be query statements such as SELECT.
import java.sql.*;
public class JDBCConnectivity {
public static void main(String[] args) {
System.out.println("-------- MySQL JDBC Connection Testing ------------");
try {
Class.forName("com.mysql.jdbc.Driver");
}
catch (ClassNotFoundException e) {
System.out.println("Where is your MySQL JDBC Driver?");
e.printStackTrace();
return;
}
System.out.println("MySQL JDBC Driver Registered!");
Connection connection = null;
try {
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/testDB","root", "");
}
catch (SQLException e) {
System.out.println("Connection Failed! Check output console");
e.printStackTrace();
return;
}
if (connection != null) {
System.out.println("You made it, take control your database now!");
}
else {
System.out.println("Failed to make connection!");
}
}
}
A small project on JDBC
//Java DataBase Connectivity
//bookName is the primary key here for the table book in database library
import java.awt.*;
import java.awt.event.*;
import java.sql.*;
import java.util.Vector;
import javax.swing.*;
import javax.swing.table.*;
public class CRUDJava extends JFrame implements ActionListener {
JButton add = new JButton("Add");
JButton select = new JButton("Select");
public CRUDJava() {
this.setTitle("Library IS");
setLayout(new FlowLayout());
add(add);
add(select);
add.addActionListener(this);
select.addActionListener(this);
setSize(200, 200);
setResizable(false);
setVisible(true);
setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
}
public Connection getConnection() {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/library","root","");
return con;
}
catch(Exception e) {
e.printStackTrace();
}
return null;
}
public static void main(String []args) {
new CRUDJava();
}
@Override
public void actionPerformed(ActionEvent e) {
Object o = e.getSource();
if(o == add) {
JLabel l1 = new JLabel("Book Name:");
JLabel l2 = new JLabel("Book Price:");
JTextField f1 = new JTextField(10);
JTextField f2 = new JTextField(10);
JButton insert = new JButton("Insert");
JButton clear = new JButton("Clear");
this.add(l1);
this.add(f1);
this.add(l2);
this.add(f2);
this.add(insert);
this.add(clear);
this.setVisible(true);
insert.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
String str1 = f1.getText();
String str2 = f2.getText();
float f = Float.parseFloat(str2);
try{
Connection conn = getConnection();
Statement stat = conn.createStatement();
String sql = "insert into book (bookName, bookPrice) values ('"+str1+"',"+f+")";
stat.executeUpdate(sql);
JOptionPane.showMessageDialog(null, "New record added!!!");
}
catch(Exception ex) {
ex.printStackTrace();
}
}
});
clear.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
String str1 = null;
String str2 = null;
f1.setText(str1);
f2.setText(str2);
}
});
}
if(o == select) {
JFrame f = new JFrame();
try{
Connection conn = getConnection();
Statement stat = conn.createStatement();
String sql = "select * from book";
ResultSet rs = stat.executeQuery(sql);
ResultSetMetaData md = rs.getMetaData();
JTable tbl;
DefaultTableModel model;
Vector <String> columnNames = new Vector <String>();
int columnCount = md.getColumnCount();
for(int column=1; column<=columnCount;column++) {
columnNames.add(md.getColumnName(column));
}
Vector <Vector<Object>> data = new Vector <Vector<Object>>();
while(rs.next()) {
Vector <Object> vector = new Vector<Object>();
for(int columnIndex = 1; columnIndex <= columnCount; columnIndex++) {
vector.add(rs.getObject(columnIndex));
}
data.add(vector);
}
model = new DefaultTableModel(data, columnNames);
tbl = new JTable(model);
f.add("Center", new JScrollPane(tbl));
tbl.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);
JPanel p = new JPanel();
JButton delete = new JButton("Delete");
JButton update = new JButton("Update");
p.add(delete);
p.add(update);
f.add("South",p);
f.setSize(300, 300);
f.setVisible(true);
f.setResizable(false);
setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
delete.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
if(tbl.getSelectedRow()!=-1) {
// to remove from database
int row = tbl.getSelectedRow();
String bname = (String) model.getValueAt(row, 0);
try {
Connection con = getConnection();
Statement stat = con.createStatement();
String sql = "delete from book where bookName = '" + bname + "'";
stat.executeUpdate(sql);
}
catch(Exception except) {
except.printStackTrace();
}
model.removeRow(tbl.getSelectedRow()); // to remove from view
}
}
});
update.addActionListener(new ActionListener() {
@Override
public void actionPerformed(ActionEvent e) {
if(tbl.getSelectedRow()!=-1) {
String newvalue = JOptionPane.showInputDialog(null, "Enter value:");
int row = tbl.getSelectedRow();
int col = tbl.getSelectedColumn();
String bname = (String) model.getValueAt(row, 0);
try {
Connection con = getConnection();
Statement stat = con.createStatement();
String sql = "update book set bookPrice = "+Float.parseFloat(newvalue)+"where bookName = '"+ bname + "'";
stat.executeUpdate(sql);
}
catch(Exception except) {
except.printStackTrace();
}
// to change the view
if(row!=-1 && col!=-1) {
tbl.setValueAt(newvalue, row, col);
}
}
}
});
}
catch(Exception ex) {
ex.printStackTrace();
}
}
}
}
No comments:
Post a Comment