This chapter provides an example on how to sort records from a table using JDBC application. This would use asc and desc keywords to sort records in ascending or descending order. Before executing the following example, make sure you have the following in place −

• To execute the following example you can replace the username and password with your actual user name and password.

• Your MySQL database you are using, is up and running.

Required Steps

The following steps are required to create a new Database using JDBC application −

• Import the packages − Requires that you include the packages containing the JDBC classes needed for database programming. Most often, using import java.sql.* will suffice.

• Open a connection − Requires using the DriverManager.getConnection() method to create a Connection object, which represents a physical connection with a database server.

• Execute a query − Requires using an object of type Statement for building and submitting an SQL statement to sort records from a table. These Queries make use of asc and desc clauses to sort data in ascending and descening orders.

• Clean up the environment − try with resources automatically closes the resources.

Sample Code

Copy and paste the following example in TestApplication.java, compile and run as follows −

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

public class TestApplication {
   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "guest";
   static final String PASS = "guest123";
   static final String QUERY = "SELECT id, first, last, age FROM Registration";

   public static void main(String[] args) {
      // Open a connection
      try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         Statement stmt = conn.createStatement();) {
         System.out.println("Fetching records in ascending order...");
         ResultSet rs = stmt.executeQuery(QUERY + " ORDER BY first ASC");
         while(rs.next()){
            //Display values
            System.out.print("ID: " + rs.getInt("id"));
            System.out.print(", Age: " + rs.getInt("age"));
            System.out.print(", First: " + rs.getString("first"));
            System.out.println(", Last: " + rs.getString("last"));
         }

         System.out.println("Fetching records in descending order...");
         rs = stmt.executeQuery(QUERY + " ORDER BY first DESC");
         while(rs.next()){
            //Display values
            System.out.print("ID: " + rs.getInt("id"));
            System.out.print(", Age: " + rs.getInt("age"));
            System.out.print(", First: " + rs.getString("first"));
            System.out.println(", Last: " + rs.getString("last"));
         }
         rs.close();
      } catch (SQLException e) {
         e.printStackTrace();
      }
   }
}

Now let us compile the above example as follows −

C:>javac TestApplication.java
C:>

When you run TestApplication, it produces the following result −

C:>java TestApplication
Fetching records in ascending order...
ID: 103, Age: 28, First: Sumit, Last: Mittal
ID: 102, Age: 30, First: Zaid, Last: Khan
ID: 100, Age: 30, First: Zara, Last: Ali
Fetching records in descending order...
ID: 100, Age: 30, First: Zara, Last: Ali
ID: 102, Age: 30, First: Zaid, Last: Khan
ID: 103, Age: 28, First: Sumit, Last: Mittal
C:>

This chapter provides an example on how to select records from a table using JDBC application. This would add additional conditions using LIKE clause while selecting records from the table. Before executing the following example, make sure you have the following in place −

• To execute the following example you can replace the username and password with your actual user name and password.

• Your MySQL database you are using, is up and running.

Required Steps

The following steps are required to create a new Database using JDBC application −

• Import the packages − Requires that you include the packages containing the JDBC classes needed for database programming. Most often, using import java.sql.* will suffice.

• Open a connection − Requires using the DriverManager.getConnection() method to create a Connection object, which represents a physical connection with a database server.

• Execute a query − Requires using an object of type Statement for building and submitting an SQL statement to fetch records from a table which meet given condition. This Query makes use of LIKE clause to select records to select all the students whose first name starts with “za”.

• Clean up the environment − try with resources automatically closes the resources.

Sample Code

Copy and paste the following example in TestApplication.java, compile and run as follows −

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

public class TestApplication {
   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "guest";
   static final String PASS = "guest123";
   static final String QUERY = "SELECT id, first, last, age FROM Registration";

   public static void main(String[] args) {
      // Open a connection
      try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         Statement stmt = conn.createStatement();) {
         System.out.println("Fetching records without condition...");
         ResultSet rs = stmt.executeQuery(QUERY);
         while(rs.next()){
            //Display values
            System.out.print("ID: " + rs.getInt("id"));
            System.out.print(", Age: " + rs.getInt("age"));
            System.out.print(", First: " + rs.getString("first"));
            System.out.println(", Last: " + rs.getString("last"));
         }

         // Select all records having ID equal or greater than 101
         System.out.println("Fetching records with condition...");
         String sql = "SELECT id, first, last, age FROM Registration" +
            " WHERE first LIKE ''%za%''";
         rs = stmt.executeQuery(sql);

         while(rs.next()){
            //Display values
            System.out.print("ID: " + rs.getInt("id"));
            System.out.print(", Age: " + rs.getInt("age"));
            System.out.print(", First: " + rs.getString("first"));
            System.out.println(", Last: " + rs.getString("last"));
         }
         rs.close();
      } catch (SQLException e) {
         e.printStackTrace();
      }
   }
}

Now let us compile the above example as follows −

C:>javac TestApplication.java
C:>

When you run TestApplication, it produces the following result −

C:>java TestApplication
Fetching records without condition...
ID: 100, Age: 30, First: Zara, Last: Ali
ID: 102, Age: 30, First: Zaid, Last: Khan
ID: 103, Age: 28, First: Sumit, Last: Mittal
Fetching records with condition...
ID: 100, Age: 30, First: Zara, Last: Ali
ID: 102, Age: 30, First: Zaid, Last: Khan
C:>

This chapter provides an example on how to delete records from a table using JDBC application. Before executing following example, make sure you have the following in place −

• To execute the following example you can replace the username and password with your actual user name and password.

• Your MySQL database you are using is up and running.

Required Steps

The following steps are required to create a new Database using JDBC application −

• Import the packages − Requires that you include the packages containing the JDBC classes needed for database programming. Most often, using import java.sql.* will suffice.

• Register the JDBC driver − Requires that you initialize a driver so you can open a communications channel with the database.

• Open a connection − Requires using the DriverManager.getConnection() method to create a Connection object, which represents a physical connection with a database server.

• Execute a query − Requires using an object of type Statement for building and submitting an SQL statement to delete records from a table. This Query makes use of the WHERE clause to delete conditional records.

• Clean up the environment − try with resources automatically closes the resources.

Sample Code

Copy and paste the following example in TestApplication.java, compile and run as follows −

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

public class TestApplication {
   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "guest";
   static final String PASS = "guest123";
   static final String QUERY = "SELECT id, first, last, age FROM Registration";

   public static void main(String[] args) {
      // Open a connection
      try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         Statement stmt = conn.createStatement();
      ) {
         String sql = "DELETE FROM Registration " +
            "WHERE id = 101";
         stmt.executeUpdate(sql);
         ResultSet rs = stmt.executeQuery(QUERY);
         while(rs.next()){
            //Display values
            System.out.print("ID: " + rs.getInt("id"));
            System.out.print(", Age: " + rs.getInt("age"));
            System.out.print(", First: " + rs.getString("first"));
            System.out.println(", Last: " + rs.getString("last"));
         }
         rs.close();
      } catch (SQLException e) {
         e.printStackTrace();
      }
   }
}

Now let us compile the above example as follows −

C:>javac TestApplication.java
C:>

When you run TestApplication, it produces the following result −

C:>java TestApplication
ID: 100, Age: 30, First: Zara, Last: Ali
ID: 102, Age: 30, First: Zaid, Last: Khan
ID: 103, Age: 28, First: Sumit, Last: Mittal
C:>

This chapter provides an example on how to update records in a table using JDBC application. Before executing the following example, make sure you have the following in place −

• To execute the following example you can replace the username and password with your actual user name and password.

• Your MySQL database you are using is up and running.

Required Steps

The following steps are required to create a new Database using JDBC application −

• Import the packages − Requires that you include the packages containing the JDBC classes needed for database programming. Most often, using import java.sql.* will suffice.

• Open a connection − Requires using the DriverManager.getConnection() method to create a Connection object, which represents a physical connection with a database server.

• Execute a query − Requires using an object of type Statement for building and submitting an SQL statement to update records in a table. This Query makes use of IN and WHERE clause to update conditional records.

• Clean up the environment − try with resources automatically closes the resources.

Sample Code

Copy and paste the following example in TestApplication.java, compile and run as follows −

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

public class TestApplication {
   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "guest";
   static final String PASS = "guest123";
   static final String QUERY = "SELECT id, first, last, age FROM Registration";

   public static void main(String[] args) {
      // Open a connection
      try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         Statement stmt = conn.createStatement();
      ) {
         String sql = "UPDATE Registration " +
            "SET age = 30 WHERE id in (100, 101)";
         stmt.executeUpdate(sql);
         ResultSet rs = stmt.executeQuery(QUERY);
         while(rs.next()){
            //Display values
            System.out.print("ID: " + rs.getInt("id"));
            System.out.print(", Age: " + rs.getInt("age"));
            System.out.print(", First: " + rs.getString("first"));
            System.out.println(", Last: " + rs.getString("last"));
         }
         rs.close();
      } catch (SQLException e) {
         e.printStackTrace();
      }
   }
}

Now let us compile the above example as follows −

C:>javac TestApplication.java
C:>

When you run TestApplication, it produces the following result −

C:>java TestApplication
ID: 100, Age: 30, First: Zara, Last: Ali
ID: 101, Age: 30, First: Mahnaz, Last: Fatma
ID: 102, Age: 30, First: Zaid, Last: Khan
ID: 103, Age: 28, First: Sumit, Last: Mittal
C:>

This chapter provides an example on how to select/ fetch records from a table using JDBC application. Before executing the following example, make sure you have the following in place −

• To execute the following example you can replace the username and password with your actual user name and password.

• Your MySQL database you are using is up and running.

Required Steps

The following steps are required to create a new Database using JDBC application −

• Import the packages − Requires that you include the packages containing the JDBC classes needed for database programming. Most often, using import java.sql.* will suffice.

• Open a connection − Requires using the DriverManager.getConnection() method to create a Connection object, which represents a physical connection with a database server.

• Execute a query − Requires using an object of type Statement for building and submitting an SQL statement to select (i.e. fetch ) records from a table.

• Extract Data − Once SQL query is executed, you can fetch records from the table.

• Clean up the environment − try with resources automatically closes the resources.

Sample Code

Copy and paste the following example in TestApplication.java, compile and run as follows −

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

public class TestApplication {
   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "guest";
   static final String PASS = "guest123";
   static final String QUERY = "SELECT id, first, last, age FROM Registration";

   public static void main(String[] args) {
      // Open a connection
      try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         Statement stmt = conn.createStatement();
         ResultSet rs = stmt.executeQuery(QUERY);
      ) {
         while(rs.next()){
            //Display values
            System.out.print("ID: " + rs.getInt("id"));
            System.out.print(", Age: " + rs.getInt("age"));
            System.out.print(", First: " + rs.getString("first"));
            System.out.println(", Last: " + rs.getString("last"));
         }
      } catch (SQLException e) {
         e.printStackTrace();
      }
   }
}

Now let us compile the above example as follows −

C:>javac TestApplication.java
C:>

When you run TestApplication, it produces the following result −

C:>java TestApplication
ID: 100, Age: 18, First: Zara, Last: Ali
ID: 101, Age: 25, First: Mahnaz, Last: Fatma
ID: 102, Age: 30, First: Zaid, Last: Khan
ID: 103, Age: 28, First: Sumit, Last: Mittal
C:>

This chapter provides an example on how to select records from a table using JDBC application. This would add additional conditions using WHERE clause while selecting records from the table. Before executing the following example, make sure you have the following in place −

• To execute the following example you can replace the username and password with your actual user name and password.

• Your MySQL database you are using, is up and running.

Required Steps

The following steps are required to create a new Database using JDBC application −

• Import the packages − Requires that you include the packages containing the JDBC classes needed for the database programming. Most often, using import java.sql.* will suffice.

• Register the JDBC driver − Requires that you initialize a driver so you can open a communications channel with the database.

• Open a connection − Requires using the DriverManager.getConnection() method to create a Connection object, which represents a physical connection with a database server.

• Execute a query − Requires using an object of type Statement for building and submitting an SQL statement to fetch records from a table, which meet the given condition. This Query makes use of the WHERE clause to select records.

• Clean up the environment − try with resources automatically closes the resources.

Sample Code

Copy and paste the following example in TestApplication.java, compile and run as follows −

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

public class TestApplication {
   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "guest";
   static final String PASS = "guest123";
   static final String QUERY = "SELECT id, first, last, age FROM Registration";

   public static void main(String[] args) {
      // Open a connection
      try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         Statement stmt = conn.createStatement();) {
         System.out.println("Fetching records without condition...");
         ResultSet rs = stmt.executeQuery(QUERY);
         while(rs.next()){
            //Display values
            System.out.print("ID: " + rs.getInt("id"));
            System.out.print(", Age: " + rs.getInt("age"));
            System.out.print(", First: " + rs.getString("first"));
            System.out.println(", Last: " + rs.getString("last"));
         }

         // Select all records having ID equal or greater than 101
         System.out.println("Fetching records with condition...");
         String sql = "SELECT id, first, last, age FROM Registration" +
            " WHERE id >= 101 ";
         rs = stmt.executeQuery(sql);

         while(rs.next()){
            //Display values
            System.out.print("ID: " + rs.getInt("id"));
            System.out.print(", Age: " + rs.getInt("age"));
            System.out.print(", First: " + rs.getString("first"));
            System.out.println(", Last: " + rs.getString("last"));
         }
         rs.close();
      } catch (SQLException e) {
         e.printStackTrace();
      }
   }
}

Now let us compile the above example as follows −

C:>javac TestApplication.java
C:>

When you run TestApplication, it produces the following result −

C:>java TestApplication
Fetching records without condition...
ID: 100, Age: 30, First: Zara, Last: Ali
ID: 102, Age: 30, First: Zaid, Last: Khan
ID: 103, Age: 28, First: Sumit, Last: Mittal
Fetching records with condition...
ID: 102, Age: 30, First: Zaid, Last: Khan
ID: 103, Age: 28, First: Sumit, Last: Mittal
C:>

This chapter provides an example on how to insert records in a table using JDBC application. Before executing following example, make sure you have the following in place −

• To execute the following example you can replace the username and password with your actual user name and password.

• Your MySQL database you are using is up and running.

Required Steps

The following steps are required to create a new Database using JDBC application −

• Import the packages − Requires that you include the packages containing the JDBC classes needed for database programming. Most often, using import java.sql.* will suffice.

• Register the JDBC driver − Requires that you initialize a driver so you can open a communications channel with the database.

• Open a connection − Requires using the DriverManager.getConnection() method to create a Connection object, which represents a physical connection with a database server.

• Execute a query − Requires using an object of type Statement for building and submitting an SQL statement to insert records into a table.

• Clean up the environment − try with resources automatically closes the resources.

Sample Code

Copy and paste the following example in TestApplication.java, compile and run as follows −

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

public class TestApplication {
   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "guest";
   static final String PASS = "guest123";

   public static void main(String[] args) {
      // Open a connection
      try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         Statement stmt = conn.createStatement();
      ) {
         // Execute a query
         System.out.println("Inserting records into the table...");
         String sql = "INSERT INTO Registration VALUES (100, ''Zara'', ''Ali'', 18)";
         stmt.executeUpdate(sql);
         sql = "INSERT INTO Registration VALUES (101, ''Mahnaz'', ''Fatma'', 25)";
         stmt.executeUpdate(sql);
         sql = "INSERT INTO Registration VALUES (102, ''Zaid'', ''Khan'', 30)";
         stmt.executeUpdate(sql);
         sql = "INSERT INTO Registration VALUES(103, ''Sumit'', ''Mittal'', 28)";
         stmt.executeUpdate(sql);
         System.out.println("Inserted records into the table...");
      } catch (SQLException e) {
         e.printStackTrace();
      }
   }
}

Now let us compile the above example as follows −

C:>javac TestApplication.java
C:>

When you run TestApplication, it produces the following result −

C:>java TestApplication
Inserting records into the table...
Inserted records into the table...
C:>

This chapter provides an example on how to delete a table using JDBC application. Before executing the following example, make sure you have the following in place −

• To execute the following example you can replace the username and password with your actual user name and password.

• Your MySQL database you are using, is up and running.

NOTE − This is a serious operation and you have to make a firm decision before proceeding to delete a table, because everything you have in your table would be lost.

Required Steps

The following steps are required to create a new Database using JDBC application −

• Import the packages − Requires that you include the packages containing the JDBC classes needed for database programming. Most often, using import java.sql.* will suffice.

• Open a connection − Requires using the DriverManager.getConnection() method to create a Connection object, which represents a physical connection with a database server.

• Execute a query − Requires using an object of type Statement for building and submitting an SQL statement to drop a table in a seleted database.

• Clean up the environment − try with resources automatically closes the resources.

Sample Code

Copy and paste the following example in TestApplication.java, compile and run as follows −

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

public class TestApplication {
   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "guest";
   static final String PASS = "guest123";

   public static void main(String[] args) {
      // Open a connection
      try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         Statement stmt = conn.createStatement();
      ) {
         String sql = "DROP TABLE REGISTRATION";
         stmt.executeUpdate(sql);
         System.out.println("Table deleted in given database...");
      } catch (SQLException e) {
         e.printStackTrace();
      }
   }
}

Now let us compile the above example as follows −

C:>javac TestApplication.java
C:>

When you run TestApplication, it produces the following result −

C:>java TestApplication
Table deleted in given database...
C:>

This chapter provides an example on how to create a table using JDBC application. Before executing the following example, make sure you have the following in place −

• To execute the following example you can replace the username and password with your actual user name and password.

• Your MySQL is up and running.

Required Steps

The following steps are required to create a new Database using JDBC application −

• Import the packages − Requires that you include the packages containing the JDBC classes needed for database programming. Most often, using import java.sql.* will suffice.

• Open a connection − Requires using the DriverManager.getConnection() method to create a Connection object, which represents a physical connection with a database server.

• Execute a query − Requires using an object of type Statement for building and submitting an SQL statement to create a table in a seleted database.

• Clean up the environment − try with resources automatically closes the resources.

Sample Code

Copy and paste the following example in TestApplication.java, compile and run as follows −

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

public class TestApplication {
   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "guest";
   static final String PASS = "guest123";

   public static void main(String[] args) {
      // Open a connection
      try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         Statement stmt = conn.createStatement();
      ) {
          String sql = "CREATE TABLE REGISTRATION " +
                   "(id INTEGER not NULL, " +
                   " first VARCHAR(255), " +
                   " last VARCHAR(255), " +
                   " age INTEGER, " +
                   " PRIMARY KEY ( id ))";

         stmt.executeUpdate(sql);
         System.out.println("Created table in given database...");
      } catch (SQLException e) {
         e.printStackTrace();
      }
   }
}

Now let us compile the above example as follows −

C:>javac TestApplication.java
C:>

When you run TestApplication, it produces the following result −

C:>java TestApplication
Created table in given database...
C:>

This chapter provides an example on how to select a Database using JDBC application. Before executing the following example, make sure you have the following in place −

• To execute the following example you need to replace the username and password with your actual user name and password.

• Your MySQL database you are using, is up and running.

Required Steps

The following steps are required to create a new Database using JDBC application −

• Import the packages − Requires that you include the packages containing the JDBC classes needed for the database programming. Most often, using import java.sql.* will suffice.

• Open a connection − Requires using the DriverManager.getConnection() method to create a Connection object, which represents a physical connection with a selected database.

• Selection of database is made while you prepare database URL. Following example would make connection with STUDENTS database.

• Clean up the environment − try with resources automatically closes the resources.

Sample Code

Copy and paste the following example in JDBCExample.java, compile and run as follows −

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

public class JDBCExample {
   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "guest";
   static final String PASS = "guest123";

   public static void main(String[] args) {
      System.out.println("Connecting to a selected database...");
      // Open a connection
      try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);) {
         System.out.println("Connected database successfully...");
      } catch (SQLException e) {
         e.printStackTrace();
      }
   }
}

Now let us compile the above example as follows −

C:>javac JDBCExample.java
C:>

When you run JDBCExample, it produces the following result −

C:>java JDBCExample
Connecting to a selected database...
Connected database successfully...
C:>