JDBC Definition-
Jdbc is concept where java program connect with database.
JDBC API-
1-java.sql
2-javax.sql
JDBC driver is s/w component that enables java application to interact with database.
There are 4 types of JDBC drivers.
[a]JDBC-ODBC bridge driver
[b]Native-API driver (partially java driver)
[c]Network Protocol driver (fully java driver)
[d]Thin driver (fully java driver)[*widely used]
-:[5 STEPS TO CONNECT TO DATABASE]:-
[a] Register the driver class
[b] Creating connection
[c] Creating statement
[d] Executing queries
[e] Closing connection
1- Register the Driver
Class.forName()
is used to load the driver class explicitly.
Example for MySql –
Class.forName("com.mysql.jdbc.Driver");
2-Create a Connection
getConnection()
method of DriverManager class is used to create a connection.
Syntax
getConnection(String url) getConnection(String url, String username, String password) getConnection(String url, Properties info)
Example for MySql-
String url ="jdbc:mysql://localhost/test";
here "test" is a name of database.
String username="root";
here “root” is a username of mysql.
String password="root";
here "root" is a password of mysql.
Connection con = DriverManager.getConnection(url, username, password);
3-Create SQL Statement
createStatement() or
prepareStatement() method is invoked on current Connection object to create a Statement. Types of statements:- a-Statement b-PreparedStatement c-CallableStatement(Used for procedures and functions) Example for MySql- a-Statement
Statement s=con.createStatement();
or b-PreparedStatement
PreparedStatement ps=con.prepareStatement(String sql);
4- Execute SQL Statement
Methods of SQL statement execution a-execute b-executeUpdate c-execute
5-Closing the connection
The close()
method of Connection interface is used to close the connection. Example of closing a connection
con.close();
code samples are here:
PreStep 1- install mysql database from here
MySql official database= click here
PreStep 2- Include mysql jar file in java project-
Download link= click here
1- Table creation and Data insertion
database =test
mysql username=root
mysql password=root
table=user
package jd; import java.sql.*; public class ExInsertStatement { public static void main(String[] args) throws ClassNotFoundException, SQLException { Class.forName("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection("jdbc:mysql://localhost/test", "root", "root"); Statement st = con.createStatement(); st.execute("CREATE TABLE IF NOT EXISTS user(name VARCHAR(20), age INT(10))"); PreparedStatement pst = con.prepareStatement("insert into user values (?,?)"); pst.setString(1, "java"); pst.setInt(2, 10); pst.executeUpdate(); System.out.println("done"); } }
2- Data Updation
package jd; import java.sql.*; public class ExUpdateStatement { public static void main(String[] args) throws ClassNotFoundException, SQLException { Class.forName("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection("jdbc:mysql://localhost/test", "root", "root"); PreparedStatement pst = con.prepareStatement("update user set name=? where age=?"); pst.setString(1, "core"); pst.setInt(2, 10); pst.executeUpdate(); System.out.println("done"); } }
3-Data Fetching/Retrieve
package jd; import java.sql.*; public class ExSelectStatement { public static void main(String[] args) throws ClassNotFoundException, SQLException { Class.forName("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection("jdbc:mysql://localhost/test", "root", "root"); PreparedStatement pst = con.prepareStatement("select * from user"); ResultSet rs = pst.executeQuery(); while (rs.next()) { System.out.println(rs.getString("name")); System.out.println(rs.getInt("age")); } } }
4- Data deletion
package jd; import java.sql.*; public class ExDeleteStatement { public static void main(String[] args) throws ClassNotFoundException, SQLException { Class.forName("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection("jdbc:mysql://localhost/test", "root", "root"); PreparedStatement pst = con.prepareStatement("delete from user"); pst.executeUpdate(); System.out.println("done"); } }