What is JDBC?
JDBC is a piece of software that knows how to talk to the actual
database server. JDBC is a registered trade mark.
Steps
There are seven standard steps in querying databases:
Load the JDBC driver.
Define the connection URL.
Establish the connection.
Create a statement object.
Execute a query or update.
Process the results.
Close the connection.
1)Import sql package
Before writing any code we need to import sql package by
import java.sql.*;
import javax.sql.*;
2) Register the driver class/Load the drivers
The forName() method of Class class is used to register the driver class.
This method is used to dynamically load the driver class.
Syntax of forName() method
public static void forName(String className)throws
ClassNotFoundException
Example to register the OracleDriver class
Class.forName("oracle.jdbc.driver.OracleDriver");
Load and register the driver by using class.forName()
Java provide 4 types of drivers
1.Jdbc-Odbc Bridge driver
2.native api driver
3.Intermediate database access server
4.Pure java dirver also called as thin driver .
3)Create a Connection by Connection
interface.
/Define the Connection URL
Create the connection object
The getConnection() method of DriverManager class is used to establish
connection with the database.
Syntax of getConnection() method
1) public static Connection getConnection(String url)throws SQLException
2) public static Connection getConnection(String url,String name,String
password)
throws SQLException
Example to establish connection with the Oracle database
Connection con=DriverManager.getConnection("jdbc:oracle:thin:
@localhost: 1521:xe",
"system","password");
Example to establish connection with the Oracle database
Connection con=DriverManager.getConnection("jdbc:odbc:mydsn"
"scott" "tiger");
jdbc is a protocol odbc is a sub protocol and mydsn is dsn name
scott is username and tiger is password
4)Create a Statement Object
The createStatement() method of Connection interface is used to create
statement. The object of statement is responsible to execute queries with the
database.
Syntax of createStatement() method
public Statement createStatement()throws SQLException
Example to create the statement object
Statement stmt=con.createStatement();
Create a Sql Statement by Statement interface.
there are 3 types of statements
We use all these statement in executing sql queries
1.statement -normal stmt
2.PreparedStatement-Precompiled Statement
3.callableStatement-call stored procedures.
String query ="sql Statement";
Statement stmt=con.createStatement("query");
5)Execute a Query
The executeQuery() method of Statement interface is used to execute
queries to the database. This method returns the object of ResultSet that can
be used to get all the records of a table.
Syntax of executeQuery() method
public ResultSet executeQuery(String sql)throws SQLException
Example to execute query
ResultSet rs=stmt.executeQuery("select * from emp");
while(rs.next()){
System.out.println(rs.getInt(1)+" "+rs.getString(2));
}
6)Process the results
Statement has three methods by which we execute and update the data.
1.execute();insert
2.executeQuery();select
3.executeUpdate();update
7)close the Connection object
By closing connection object statement and ResultSet will be closed
automatically. The close() method of Connection interface is used to close the
connection.
Syntax of close() method
public void close()throws SQLException
Example to close connection
con.close();
Example to close connection
at last we need to close all methods...
finally
{
try
{
con.close();
stmt.close();
rs.close();
}
catch(Exception e)
{
e.printStackTrace();
}
Load the JDBC driver.
try {
Class.forName("connect.microsoft.MicrosoftDriver") ;
//Class.forName("oracle.jdbc.driver.OracleDriver"); for Oracle driver
//Class.forName("com.sybase.jdbc.SybDriver"); for sybase driver
} catch(ClassNotFoundException e) {
System.err.println("Error loading driver: " + e);
}
Connection class include
prepareStatement
prepareCall
rollback
commit
close
isClosed
*********************************
Example to connect to the mysql database in java
For connecting java application with the mysql database, you need to follow 5
steps to perform database connectivity.
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, you need to replace the
sonoo with your database name.
Username: The default username for the mysql database is root.
Password: Password is 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 sonoo;
use sonoo;
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.
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/sonoo","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);}
}
}
download this example
*****************************
Modified example as per the data
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/mydata","root","root");
//here sonoo is database name, root is username and password
Statement stmt=con.createStatement();
ResultSet rs=stmt.executeQuery("select * from Employees");
while(rs.next()){
//Retrieve by column name
int id = rs.getInt("id");
int age = rs.getInt("age");
String first = rs.getString("first");
String last = rs.getString("last");
//Display values
System.out.print("ID: " + id);
System.out.print(", Age: " + age);
System.out.print(", First: " + first);
System.out.println(", Last: " + last);}
// System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3));
con.close();
}catch(Exception e){ System.out.println(e);}
}
}
*****************************
The above example will fetch all the records of emp table.
To connect java application with the mysql database
mysqlconnector.jar file is required to be loaded.
download the jar file mysql-connector.jar
Two ways to load the jar file:
paste the mysqlconnector.jar file in jre/lib/ext folder
set classpath
1) paste the mysqlconnector.jar file in JRE/lib/ext folder:
Download the mysqlconnector.jar file. Go to jre/lib/ext folder and paste the jar
file here.
2) set classpath:
There are two ways to set the classpath:
temporary
permanent
How to set the temporary classpath
open command prompt and write:
C:>set classpath=c:\folder\mysql-connector-java-5.0.8-bin.jar;.;
How to set the permanent classpath
Go to environment variable then click on new tab. In variable name write
classpath and in variable value paste the path to the mysqlconnector.jar file
by appending mysqlconnector.jar;.; as C:\folder\mysql-connector-java-5.0.8-
bin.jar;.;
********************************
/* Do the following steps:
1. Click on mysql command line client in start menu.
2. Enter "root" against enter password :
3. Show databases;- to view all data bases available.
4. Create database mydata;- to create mydata new database.
5. Use mydata; - to select newly created database.
6. Create table Employees
( id int not null, age int not null, fname varchar (255),lname varchar (255));
7. INSET INTO Employees VALUES (100,18,'ram','prasad');
8. Repeate 7th step 4 times with different new record values.
-use down arrow to repeat the command,left arrow to move to next
character.
9. SELECT * FROM Employees;- to view all records
*/
//STEP 1. Import required packages
import java.sql.*;
public class jdbc2 {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost/mydata";
// Database credentials
// static final String USER = "username";
static final String PASS = "root";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try{
//STEP 2: Register JDBC driver
Class.forName("com.mysql.jdbc.Driver");
//STEP 3: Open a connection
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL,USER,PASS);
// conn = DriverManager.getConnection(DB_URL,PASS);
//STEP 4: Execute a query
System.out.println("Creating statement...");
stmt = conn.createStatement();
String sql;
sql = "SELECT id, first, last, age FROM Employees";
//STEP 5: Extract data from result set
try (ResultSet rs = stmt.executeQuery(sql)) {
//STEP 5: Extract data from result set
while (rs.next()) {
//Retrieve by column name
int id = rs.getInt("id");
int age = rs.getInt("age");
String first = rs.getString("first");
String last = rs.getString("last");
//Display values
System.out.print("ID: " + id);
System.out.print(", Age: " + age);
System.out.print(", First: " + first);
System.out.println(", Last: " + last);
}
//STEP 6: Clean-up environment
}
stmt.close();
conn.close();
}catch(SQLException se){
//Handle errors for JDBC
se.printStackTrace();
}catch(Exception e){
//Handle errors for Class.forName
e.printStackTrace();
}finally{
//finally block used to close resources
try{
if(stmt!=null)
stmt.close();
}catch(SQLException se2){
}// nothing we can do
try{
if(conn!=null)
conn.close();
}catch(SQLException se){
se.printStackTrace();
}//end finally try
}//end try
System.out.println("Goodbye!");
}//end main
}//end FirstExample
*******************************************
//JDBCExample.java
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
public class JDBCExample {
public static void main(String[] argv) {
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/mkyongcom","root"
, "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!");
}
}
}
***************************************************
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
public class MySQLConnectExample {
public static void main(String[] args) {
// creates three different Connection objects
Connection conn1 = null;
Connection conn2 = null;
Connection conn3 = null;
try {
// connect way #1
String url1 = "jdbc:mysql://localhost:3306/test1";
String user = "";
String password = "root";
conn1 = DriverManager.getConnection(url1, user, password);
if (conn1 != null) {
System.out.println("Connected to the database test1");
}
// connect way #2
String url2 = "jdbc:mysql://localhost:3306/test2?
user=root&password=secret";
conn2 = DriverManager.getConnection(url2);
if (conn2 != null) {
System.out.println("Connected to the database test2");
}
// connect way #3
String url3 = "jdbc:mysql://localhost:3306/test3";
Properties info = new Properties();
info.put("user", "");
info.put("password", "root");
conn3 = DriverManager.getConnection(url3, info);
if (conn3 != null) {
System.out.println("Connected to the database test3");
}
} catch (SQLException ex) {
System.out.println("An error occurred. Maybe user/password is
invalid");
ex.printStackTrace();
}
}
}
**************************************************
import java.sql.*;
public class JDBCSample {
public static void main( String args[]) {
String connectionURL =
"jdbc:postgresql://localhost:5432/movies;user=java;password=samples";
// Change the connection string according to your db, ip, username and
password
try {
// Load the Driver class.
Class.forName("org.postgresql.Driver");
// If you are using any other database then load the right driver here.
//Create the connection using the static getConnection method
Connection con = DriverManager.getConnection (connectionURL);
//Create a Statement class to execute the SQL statement
Statement stmt = con.createStatement();
//Execute the SQL statement and get the results in a Resultset
ResultSet rs = stmt.executeQuery("select moviename, releasedate from
movies");
// Iterate through the ResultSet, displaying two values
// for each row using the getString method
while (rs.next())
System.out.println("Name= " + rs.getString("moviename") + " Date= "
+ rs.getString("releasedate"));
}
catch (SQLException e) {
e.printStackTrace();
}
catch (Exception e) {
e.printStackTrace();
}
finally {
// Close the connection
con.close();
}
}
}
*************************************
The program gui looks like this
Introduction
This article explains how to search user records from a database by their
name in the Swing GUI of Java. The NetBeans IDE is used to create the
sample examples.
Searching Records from Database in a Windows Forms form using Swing GUI
For creating this app we need the following files:
Java file (SwingSearchApp.java)
SQL table (emp.sql)
1. SwingSearchApp.java
This Java file consists of the entire logic. First of all we initialize the JFrame
components using a constructor then create a database connection and
finally set the database value to the textfield. If the given name is not found
in the database then it displays an error message and displays it by running
the constructor.
2. emp.sql
For fetching records we need a database table; for that we create an "emp"
table in our database.
Syntax
emp.sql
create table emp
(
uname varchar2(20), umail varchar2(30),
upass varchar2(20), ucountry varchar2(20)
);
Insert some rows
The following SQL will insert some rows in it:
1. insert into emp values ('sandeep', 'sandy05.1991@gmail.com', 'welcome',
'India');
2. insert into emp values ('rahul', 'rahul@gmail.com' , '123', 'India');
Now let's start creating this app. Use the following procedure to do that in the
NetBeans IDE.
Step 1
Open the NetBeans IDE.
NetBeans
Step 2
Choose "Java" -> "Java application" as shown below.
Java Application
Step 3
Type your project name as "SwingSearchApp" as in the following.
SwingSearchApp
Step 4
Now write the following code in the "SwingSearchApp.java" file.
Step 4
Now write the following code in the "SwingSearchApp.java" file.
SwingSearchApp.java
import javax.swing.*;
import java.awt.event.*;
import java.awt.*;
import java.sql.*;
public class SwingSearchApp extends JFrame implements ActionListener {
//Initializing Components
JLabel lb, lb1, lb2, lb3, lb4, lb5;
JTextField tf1, tf2, tf3, tf4, tf5;
JButton btn;
//Creating Constructor for initializing JFrame components
SwingSearchApp() {
//Providing Title
super("Fetching Student Information");
lb5 = new JLabel("Enter Name:");
lb5.setBounds(20, 20, 100, 20);
tf5 = new JTextField(20);
tf5.setBounds(130, 20, 200, 20);
btn = new JButton("Submit");
btn.setBounds(50, 50, 100, 20);
btn.addActionListener(this);
lb = new JLabel("Fetching Student Information From Database");
lb.setBounds(30, 80, 450, 30);
lb.setForeground(Color.red);
lb.setFont(new Font("Serif", Font.BOLD, 20));
setVisible(true);
setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
setSize(500, 500);
lb1 = new JLabel("U_Name:");
lb1.setBounds(20, 120, 100, 20);
tf1 = new JTextField(50);
tf1.setBounds(130, 120, 200, 20);
lb2 = new JLabel("U_Mail:");
lb2.setBounds(20, 150, 100, 20);
tf2 = new JTextField(100);
tf2.setBounds(130, 150, 200, 20);
lb3 = new JLabel("U_Pass:");
lb3.setBounds(20, 180, 100, 20);
tf3 = new JTextField(50);
tf3.setBounds(130, 180, 200, 20);
lb4 = new JLabel("U_Country:");
lb4.setBounds(20, 210, 100, 20);
tf4 = new JTextField(50);
tf4.setBounds(130, 210, 100, 20);
setLayout(null);
//Add components to the JFrame
add(lb5);
add(tf5);
add(btn);
add(lb);
add(lb1);
add(tf1);
add(lb2);
add(tf2);
add(lb3);
add(tf3);
add(lb4);
add(tf4);
//Set TextField Editable False
tf1.setEditable(false);
tf2.setEditable(false);
tf3.setEditable(false);
tf4.setEditable(false);
public void actionPerformed(ActionEvent e) {
//Create DataBase Coonection and Fetching Records
try {
String str = tf5.getText();
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con =
DriverManager.getConnection("jdbc:oracle:thin:@mcndesktop07:1521",
"sandeep", "welcome");
PreparedStatement st = con.prepareStatement("select * from emp
where uname=?");
st.setString(1, str);
//Excuting Query
ResultSet rs = st.executeQuery();
if (rs.next()) {
String s = rs.getString(1);
String s1 = rs.getString(2);
String s2 = rs.getString(3);
String s3 = rs.getString(4);
//Sets Records in TextFields.
tf1.setText(s);
tf2.setText(s1);
tf3.setText(s2);
tf4.setText(s3);
} else {
JOptionPane.showMessageDialog(null, "Name not Found");
//Create Exception Handler
} catch (Exception ex) {
System.out.println(ex);
//Running Constructor
public static void main(String args[]) {
new SwingSearchApp();
}
}
***************************************
