This blog will help you to connect your java program with SQL Database with all operations like Create Table, Insert , Select , Update and Delete operations.

Step 1: First we required drivers which we can use to connect our SQL database with Java. So in you need to add drivers as shown in the next steps.

Step 2:   Go to you project -> Libraries->Right Click and Add JAR/Folder



Go to C:\Program Files\NetBeans 8.2\ide\modules\ext\ and Select
mysql-connector-java-5.1.23-bin.jar 


Step 3:Again  Go to you project -> Libraries->Right Click and Add Library


 Go to Library and Add Java DB Driver

Step 4: Now we have added all the Libraries so now we will create a database in the SQL. To Create New Database Go to Services->Java DB->Right Click ->Create Database
 
Type New Database Name = STUDENTS, Username=APP, Password=root and Confirm Password=root
 
Now Connect Your Database by Right Click on STUDENTS and Click on Connect
 
Your Database Connected Now


Step 5: Now we will create a table in the STUDENTS database using one Program.
Now Create New Java Class and Save it as Create_Table_DV.java and Add following Code in the file.
package SQL_DB_Connect_DV;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class Create_Table_DV {
   

 
  static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
  static final String DB_URL = "jdbc:derby://localhost:1527/STUDENTS";
                               
 
  static final String USER = "APP";
  static final String PASS = "root";

  public static void main(String[] args) throws Exception
  {
    Connection conn = null;
    Statement stmt = null;

    Class.forName(JDBC_DRIVER);
    //conn = DriverManager.getConnection(DB_URL, USER, PASS);
    conn = DriverManager.getConnection(DB_URL, USER, PASS);
    System.out.println("Connecting database...");
    stmt = conn.createStatement();

     
    String sql = "CREATE TABLE Person2"
            + "(id INTEGER , "
        + " firstName VARCHAR(50), "
            + " lastName VARCHAR(50), "
        + " age INTEGER, "
            + " PRIMARY KEY ( id ))";

    stmt.executeUpdate(sql);
    System.out.println("Table Created in the Database...");
    stmt.close();
    conn.close();
  }
}
Step 6: Now Run the Program your table will be displayed as shown in below figure.

Step 7: Same way you can create new class for Insert Record in the Table. Create a new class and name it Insert_Record_DV.java and add following code in the program.
package SQL_DB_Connect_DV;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class Insert_Record_DV {
  static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
  static final String DB_URL = "jdbc:derby://localhost:1527/STUDENTS";

  static final String USER = "APP";
  static final String PASS = "root";

  public static void main(String[] args) throws Exception {
    Connection conn = null;
    Statement stmt = null;

    Class.forName(JDBC_DRIVER);
    conn = DriverManager.getConnection(DB_URL, USER, PASS);
    System.out.println("Connecting database...");
    stmt = conn.createStatement();

    String sql = "INSERT INTO Person2 VALUES (12, 'Harish', 'Patil', 20)";
    stmt.executeUpdate(sql);
    sql = "INSERT INTO Person2 VALUES (20, 'Priyanka', 'Patel', 25)";
    stmt.executeUpdate(sql);
    sql = "INSERT INTO Person2 VALUES (30, 'Yash', 'Shah', 30)";
    stmt.executeUpdate(sql);
    sql = "INSERT INTO Person2 VALUES(40, 'Digvijaysinh', 'Virpura', 28)";
    stmt.executeUpdate(sql);
    stmt.close();
    conn.close();
  }
}
Step 8:       Create a new class and name it Select_Data_DV.java and add following code in the program.


package SQL_DB_Connect_DV;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Select_Data_DV
{
 static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
  static final String DB_URL = "jdbc:derby://localhost:1527/STUDENTS";
 
  static final String USER = "APP";
  static final String PASS = "root";

  public static void main(String[] args) throws Exception
  {
    Connection conn = null;
    Statement stmt = null;

    Class.forName(JDBC_DRIVER);
    conn = DriverManager.getConnection(DB_URL, USER, PASS);
    System.out.println("Connecting database...");
    stmt = conn.createStatement();

   

    stmt = conn.createStatement();
    String sql = "SELECT id, firstName, lastName, age FROM Person2";
    ResultSet rs = stmt.executeQuery(sql);
    while (rs.next()) {
      // Retrieve by column name
      int id = rs.getInt("id");
      int age = rs.getInt("age");
      String first = rs.getString("firstName");
      String last = rs.getString("lastName");

      // Display values
      System.out.print("ID: " + id);
      System.out.print(", Age: " + age);
      System.out.print(", First: " + first);
      System.out.println(", Last: " + last);
    }
    rs.close();
    stmt.close();
    conn.close();
  }
}
Step 9: Create a new class and name it Update_Record_DV.java and add following code in the program.
package SQL_DB_Connect_DV;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Update_Record_DV {
   
  static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
  static final String DB_URL = "jdbc:derby://localhost:1527/STUDENTS";
 
  static final String USER = "APP";
  static final String PASS = "root";


  public static void main(String[] args) throws Exception {
    Connection conn = null;
    Statement stmt = null;

    Class.forName(JDBC_DRIVER);
    conn = DriverManager.getConnection(DB_URL, USER, PASS);
    System.out.println("Connecting database...");
    stmt = conn.createStatement();

    stmt = conn.createStatement();
   
      String sql = "UPDATE person2 SET age = 300 WHERE id in (30)";
        stmt.executeUpdate(sql);
   
    sql = "SELECT id, firstName, lastName, age FROM Person2";
    ResultSet rs = stmt.executeQuery(sql);
    while (rs.next()) {
      // Retrieve by column name
      int id = rs.getInt("id");
      int age = rs.getInt("age");
      String first = rs.getString("firstName");
      String last = rs.getString("lastName");

      // Display values
      System.out.print("ID: " + id);
      System.out.print(", Age: " + age);
      System.out.print(", First: " + first);
      System.out.println(", Last: " + last);
    }
    rs.close();
    stmt.close();
    conn.close();
  }
}
 
Step 10:   Create a new class and name it Delete_Record_DV.java and add following code in the program.

package SQL_DB_Connect_DV;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class Delete_Record_DV {
  static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
  static final String DB_URL = "jdbc:derby://localhost:1527/STUDENTS";
 
  static final String USER = "APP";
  static final String PASS = "root";

  public static void main(String[] args) throws Exception {
    Connection conn = null;
    Statement stmt = null;

    Class.forName(JDBC_DRIVER);
    conn = DriverManager.getConnection(DB_URL, USER, PASS);
    System.out.println("Connecting database...");
    stmt = conn.createStatement();

    String sql = "DELETE FROM Person2 WHERE id = 30";
    stmt.executeUpdate(sql);
    System.out.println("Deleted from database...");
    stmt.close();
    conn.close();
  }
}

Have a Happy Learning

Regards

Digvijaysinh Virpura