JDBC Connectivity Examples

Example: A Java Program to show only JDBC Connectivity with the MySql Database.

import java.sql.*;

public class MySQLConnection {
   public static void main(String[] args) {
      Connection conn = null;
      try {
         // Load the MySQL JDBC driver
         Class.forName("com.mysql.jdbc.Driver");

         // Create a connection to the database
         String url = "jdbc:mysql://localhost:3306/databasename";
         String uname = "username";
         String passwd = "password";
         conn = DriverManager.getConnection(url, uname, passwd);

         System.out.println("Connected to the database");

      } catch (ClassNotFoundException e) {
         System.out.println("Could not load the MySQL JDBC driver");
         e.printStackTrace();
      } catch (SQLException e) {
         System.out.println("Could not connect to the database");
         e.printStackTrace();
      } finally {
         // Close the connection
         try {
            if (conn != null) {
               conn.close();
               System.out.println("Connection closed");
            }
         } catch (SQLException e) {
            e.printStackTrace();
         }
      }
   }
}

Example: A Java Program to show JDBC connectivity with MySql database to Retrieve/Search data from a table.

import java.sql.*;

public class DBConnect {
   public static void main(String[] args) {
      try {
         Class.forName("com.mysql.jdbc.Driver");
         Connection con = DriverManager.getConnection(
            "jdbc:mysql://localhost:3306/databasename", "username", "password");
         Statement stmt = con.createStatement();
         ResultSet rs = stmt.executeQuery("SELECT * FROM tablename");
         while (rs.next()) {
            System.out.println(rs.getString(1) + " " + rs.getString(2));
         }
         con.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

-----------  OR  ------------

import java.sql.*;

public class MySQLSelect {
   public static void main(String[] args) {
      Connection conn = null;
      Statement stmt = null;
      ResultSet rs = null;

      try {
         // Load the MySQL JDBC driver
         Class.forName("com.mysql.jdbc.Driver");

         // Create a connection to the database
         String url = "jdbc:mysql://localhost:3306/databasename";
         String uname = "username";
         String passwd = "password";
         conn = DriverManager.getConnection(url, uname, passwd);

         // Create a statement object
         stmt = conn.createStatement();

         // Execute a query
         rs = stmt.executeQuery("SELECT * FROM tablename");

         // Process the result set
         while (rs.next()) {
            int id2 = rs.getInt("id1");
            String name2 = rs.getString("name1");
            String email2 = rs.getString("email1");
            System.out.println(id2 + ", " + name2 + ", " + email2);
         }

      } catch (ClassNotFoundException e) {
         System.out.println("Could not load the MySQL JDBC driver");
         e.printStackTrace();
      } catch (SQLException e) {
         System.out.println("Could not connect to the database");
         e.printStackTrace();
      } finally {
         // Close the connection
         try {
            if (conn != null) {
               conn.close();
               System.out.println("Connection closed");
            }
         } catch (SQLException e) {
            e.printStackTrace();
         }
      }
   }
}

NB: Here, id1, name1 and email1 are the fields name of MySql database tablename.

---------- OR  -----------

import java.sql.*;

public class JdbcExample {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/testdb"; // database URL
      String user = "username"; // database username
      String passwd = "password"; // database password
      
      try {
         // establish connection to database
         Connection connection = DriverManager.getConnection(url, user, passwd);
         
         // create statement
         Statement statement = connection.createStatement();
         
         // execute query
         ResultSet resultSet = statement.executeQuery("SELECT * FROM tablename");
         
         // process results
         while (resultSet.next()) {
            int id2 = resultSet.getInt("id1");
            String name2 = resultSet.getString("name1");
            double salary2 = resultSet.getDouble("salary1");
            
            System.out.println("ID: " + id2 + ", Name: " + name2 + ", Salary: " + salary2);
         }
         
         // close resources
         resultSet.close();
         statement.close();
         connection.close();
      } catch (SQLException e) {
         e.printStackTrace();
      }
   }
}

Here, id1, name1 and email1 are the fields name of MySql database tablename.

NB:
In the above example, we establish a connection to a MySQL database using the DriverManager.getConnection() method. We then create a Statement object to execute a query and obtain a ResultSet object to process the query results. Finally, we iterate over the ResultSet object and extract the data from each row.

Example: A Java Program to show JDBC connectivity with MySql database to Add/Store/Insert data in a table.

import java.sql.*;

public class DBInsert {
   public static void main(String[] args) {
      try {
         Class.forName("com.mysql.jdbc.Driver");
         Connection con = DriverManager.getConnection(
            "jdbc:mysql://localhost:3306/databasename", "username", "password");
         PreparedStatement stmt = con.prepareStatement(
            "INSERT INTO tablename (id, name) VALUES (?, ?)");
         stmt.setInt(1, 101);
         stmt.setString(2, "Robert");
         stmt.executeUpdate();
         con.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

Example: A Java Program to show JDBC connectivity with MySql database and to Update/Edit/Modify data of a table.

import java.sql.*;

public class DBUpdate {
   public static void main(String[] args) {
      try {
         Class.forName("com.mysql.jdbc.Driver");
         Connection con = DriverManager.getConnection(
            "jdbc:mysql://localhost:3306/databasename", "username", "password");
         PreparedStatement stmt = con.prepareStatement(
            "UPDATE tablename SET name = ? WHERE id = ?");
         stmt.setString(1, "John");
         stmt.setInt(2, 105);
         stmt.executeUpdate();
         con.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

Example: A Java Program to show JDBC connectivity with MySql database and to Delete/Remove data or records from a table.

import java.sql.*;

public class JdbcDeleteExample {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/Databasename"; // database URL
      String user = "username"; // database username
      String passwd = "password"; // database password
      
      try {
         // establish connection to database
         Connection connection = DriverManager.getConnection(url, user, passwd);
         
         // create statement
         Statement statement = connection.createStatement();
         
         // execute query to delete a row
         int rowsDeleted = statement.executeUpdate("DELETE FROM tablename WHERE id1 = 100");
         
         // display number of rows deleted
         System.out.println("Rows deleted: " + rowsDeleted);
         
         // close resources
         statement.close();
         connection.close();
      } catch (SQLException e) {
         e.printStackTrace();
      }
   }
}

Here, id1 is the primary key field name of a table.