Programs for MySql Connectivity using Different JDBC Drivers

MySQL connectivity can be established in Java using various JDBC drivers, each offering distinct methods of interaction with the database. The JDBC-ODBC Bridge (Type 1) driver allows communication through an ODBC Data Source, though it has been deprecated and is no longer supported in newer Java versions. The Native-API Driver (Type 2) requires native MySQL client libraries and provides better performance. The Network Protocol Driver (Type 3) utilizes middleware for database communication, which can be beneficial in distributed systems. The Thin Driver (Type 4), a pure Java driver, is the most widely used, offering a direct and efficient connection to MySQL databases.

JDBC-ODBC Type 1 Driver for MySQL Connectivity 

            import java.sql.*;

public class JdbcTypeOne {

    public static void main(String[] args) {
        try {
            // Load the driver
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            System.out.println("Driver Loaded Successfully!");

            // Establish a connection 
            Connection conn = DriverManager.getConnection("jdbc:odbc:std", "username", "password");
            System.out.println("Connection Established Successfully!");

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

            // Execute the query
            String query = "SELECT id, Name FROM student"; // Correct SQL syntax
            ResultSet rs = stmt.executeQuery(query);

            // Process the result set
            while (rs.next()) { // Loop through the result set
                System.out.println("ID = " + rs.getInt("id") + ", Name = " + rs.getString("Name"));

            }
            // Close resources
            rs.close();
            stmt.close();
            conn.close();
            System.out.println("Connection Closed Successfully!");
        }
         catch (Exception e) {
            System.out.println("Something went wrong: " + e.getMessage());
        }
    }
}

JDBC Network Protocol Driver (Type 3) using Oracle 

            import java.sql.*;

public class JdbcTypeThree {
              
      public static void main(String[] args) {
            try {
                Class.forName("oracle.jdbc.OracleDriver");  
                System.out.println("Oracle JDBC Type 3 Driver Loaded Successfully!");
              
                // Oracle JDBC URL for Type 3 Driver (replace with your actual details)
                String jdbcUrl = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)
                (HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SID=orcl)))"; 
                
                // Replace localhost, 1521, and orcl with your actual database details
                          
                String username = "username";  // Replace with your Oracle DB username
                String password = "password";  // Replace with your Oracle DB password
              
                // Establish a connection
                Connection conn = DriverManager.getConnection(jdbcUrl, username, password);
                System.out.println("Connection Established Successfully!");
              
                // Create a statement
                Statement stmt = conn.createStatement();
              
                // Execute the query
                String query = "SELECT id, Name FROM student"; // Correct SQL syntax
                ResultSet rs = stmt.executeQuery(query);
              
                // Process the result set
                while (rs.next()) {  // Loop through the result set
                    System.out.println("ID = " + rs.getInt("id") + ", Name = " + rs.getString("Name"));
                }
              
                // Close resources
                rs.close();
                stmt.close();
                conn.close();
                System.out.println("Connection Closed Successfully!");
                
            } catch (Exception e) {
                System.out.println("Something went wrong: " + e.getMessage());
      }
    }
}

JDBC Type 4 Thin Driver using Oracle 

            import java.sql.*;
              
        public class JdbcOracleThin {
              
            public static void main(String[] args) {
              
                try {
                     // Oracle JDBC URL (replace with your actual details)
                      String jdbcUrl = "jdbc:oracle:thin:@localhost:1521:orcl"; // Replace with your database host, port, and SID
                      String username = "username";  // Replace with your Oracle DB username
                      String password = "password";  // Replace with your Oracle DB password
                      // Establish connection
                      Connection conn = DriverManager.getConnection(jdbcUrl, username, password);
                      Statement stmt = conn.createStatement();
                      ResultSet rs = stmt.executeQuery("SELECT id, name FROM student");
              
                      // Print results
                      while (rs.next()) {
                          int id = rs.getInt("id");
                          String name = rs.getString("name");
                          System.out.println("ID: " + id + ", Name: " + name);
                      }
              
                      rs.close();
                      stmt.close();
                      conn.close();
              
                    } catch (Exception e) {
                        System.out.println("Database error: " + e.getMessage());
                    }
                }
            }