-
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

Comments

Post a Comment

Popular posts from this blog

Custom Authentication in Oracle APEX

-
Image
 Custom Authentication in Oracle APEX  This blog demonstrate the steps for Custom Authentication in Oracle APEX. Step1: We Need a table Login.      CREATE TABLE   "LOGIN"    (            "LOGIN_PK" NUMBER(7,0) NOT NULL ENABLE,                 "ENTITY_OWNER_FK" NUMBER(7,0) NOT NULL ENABLE,                 "ENTITY_FK" NUMBER(2,0) NOT NULL ENABLE,                 "USERNAME" VARCHAR2(20) NOT NULL ENABLE,                 "PASSWORD" VARCHAR2(20) NOT NULL ENABLE, "EMAIL" VARCHAR2(50),   CONSTRAINT "LOGIN_PK" PRIMARY KEY ("LOGIN_PK") ENABLE,         ...
Read more

"Navigating the Oracle APEX Frontier: A Transformative PhD Expedition"

-
Image
My five-year journey of pursuing a Ph.D. in Oracle APEX has been filled with both challenges and invaluable learning experiences. I decided to write this blog to share my insights and to help those who are considering a Ph.D. and are in search of a compelling research topic.   Where did the idea come from? It all started with a simple Online Test Application on a Local Area Network (LAN) using Oracle APEX . The idea of pursuing a Ph.D. in Oracle APEX first came to me in 2011-12.During that time, I developed an application to conduct online MCQ tests for students, designed to run on the LAN. Using a standard computer with minimal configuration, I installed Oracle APEX and set up the application so that students could access the MCQ test on the intranet, allowing us to generate results instantly. ...
Read more

Display Dynamic TextField in APEX based on Table Columns.(APEX_ITEM.TEXT)

-
Image
This blog will demonstrate steps to display TextField dynamically based on Table Columns in Classic Report and insert data into database. Oracle APEX has a large collection of API in which APEX_ITEM.TEXT is used in this example while if you want to explore in detail just click on following link. https://docs.oracle.com/cd/E14373_01/apirefs.32/e13369/apex_item.htm#AEAPI192 Step1: Create a Classic Report based on SQL Query. In this demo i have used a Emp table. So my query look like as follows.     select EMP.EMPNO as EMPNO,               EMP.ENAME as ENAME     from EMP EMP and Result will look like as shown in following figure. Now If we need to display a textbox against each employee name we need to add one dynamic APEX_ITEM.Text. So now our query will change as follows. select  EMP.ENAME as ENAME,    APEX_ITEM.TEXT(01,null,NULL,2,NULL,'Emp_Salary') AS Salary,    APEX_ITEM.HIDD...
Read more