JDBC Program to Create and Drop Database in MySQL

We had developed many JDBC programs. But one question can come into our mind that “can we create a database using the JDBC program?”. Yes, we can. In this tutorial, we will develop a Java or JDBC program to create a database in MySQL. Later we will develop a JDBC program to drop database in MySQL database,

MySQL JDBC Driver details,

URL syntax::

=> for local MySQL:: 
jdbc:mysql:///<logical-database-name>

=> for remote MySQL:: 
jdbc:mysql://<host>:<port-no>/<logical-database-name>

Username: <username of MySQL database>
Password: <password of MySQL database>

We can create a database from root user. If you are developing JDBC program on your local computer/laptop then you can use url of local MySQL. You can also use url of remote MySQL, in this case use “localhost” as host, and 3306 is the default port of MySQL database.

We can use getConnection() method with remote MySQL url as:- getConnection(“jdbc:mysql://localhost:3306″,”root”,”rootPassword”); Here you must place your own password for the root user.

We can use the getConnection method with local MySQL url as:- getConnection("jdbc:mysql:///<existing-logical-database-name>","root","rootPassword") Here also you must place your own password for the root user.

In my MySQL database in the root user, there are many databases are there, you can view the database as,

show databases;

Output:-

information_schema

knowprogram

performance_schema

sakila

sys

So, we can use the getConnection method as getConnection("jdbc:mysql:///knowprogram","root","rootPassword")

---

JDBC Program to Create Database in MySQL

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

public class CreateDatabaseMySQL {

   public static void main(String[] args ) {

      // declare variables
      Scanner scan = null;
      String query = null, dbname = null;
      Connection con = null;
      Statement st = null;
      int result = 0;

      try {
           // read input
           scan = new Scanner(System.in);
           if(scan != null) {
              System.out.println("Enter the  name of  database: ");
              dbname = scan.next();
           }

           // prepare query to create database
           query = "CREATE DATABASE "+ dbname;

           // establish the connection
           con = DriverManager.getConnection(
                      "jdbc:mysql://localhost:3306","root","password@22");

           // place the password for the root user
           // create statement object
           if(con != null)
           st = con.createStatement();

           // execute SQL query
           if(st != null)
           result = st.executeUpdate(query);

           // process the result
           if(result == 0)
           System.out.println("Database not created");
           else
           System.out.println("Database created");

       } catch(SQLException se) {
          se.printStackTrace();
       } catch(Exception e) {
          e.printStackTrace();
       } // end of try-catch block 

       finally {
          // close JDBC objects
          try {
             if(st != null) st.close();
          } catch(SQLException se) {
             se.printStackTrace();
          }
          try {
             if(con != null) con.close();
          } catch(SQLException se) {
             se.printStackTrace();
          }
          try {
             if(scan != null) scan.close();
          } catch(Exception e) {
             e.printStackTrace();
          }
       }

   } //end of main
} //end of class

Output:-

Enter the name of the database:
MyNewDB
Database created

we can observe that if block will be never executed. Either executeUpdate() method returns 0 for success or it throws exceptions. So, writing else block is meaningless. We are writing else block only for understanding purpose, there is no use of else block in these programs.

JDBC Program to Drop Database

To drop database in MySQL database, the required SQL query is,

DROP DATABASE databasename;

In order to do that in the above program Just change the query to

// prepare query to drop database
query = "DROP DATABASE "+ dbname;

Now, execute the program.

Note that, For create statement the executeUpdate() method return 0 on failure and a non-zero value on success. But for the drop table, it is just the opposite. For drop statement the executeUpdate() method return 0 on success and non-zero on failure.

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

public class DropDatabaseMySQL {

   public static void main(String[] args ) {

      // declare variables
      Scanner scan = null;
      String query = null, dbname = null;
      Connection con = null;
      Statement st = null;
      int result = 0;

      try {
         // read input
         scan = new Scanner(System.in);
         if(scan != null) {
            System.out.println("Enter name of "
                   + " the database to drop: ");
            dbname = scan.next();
         }

         // prepare query to drop database
         query = "DROP DATABASE "+ dbname;

         // establish the connection
         con = DriverManager.getConnection(
            "jdbc:mysql://localhost:3306","root", "root2020@");

         // create statement object
         if(con != null)
           st = con.createStatement();

         // execute SQL query
         if(st != null)
            result = st.executeUpdate(query);

         // process the result
         if(result == 0)
         System.out.println("Database dropped successfully");
         else
         System.out.println("Database not exist");

       } catch(SQLException se) {
          se.printStackTrace();
       } catch(Exception e) {
          e.printStackTrace();
       } // end of try-catch block 

       finally {
          // close JDBC objects
          try {
             if(st != null) st.close();
          } catch(SQLException se) {
             se.printStackTrace();
          }
          try {
            if(con != null) con.close();
          } catch(SQLException se) {
             se.printStackTrace();
          }
          try {
             if(scan != null) scan.close();
          } catch(Exception e) {
             e.printStackTrace();
          }
      }

   } //end of main
} //end of class

Output:-

Enter name of the database to drop:
mynewdb
Database dropped successfully

In both of the cases (creating database, and dropping database) we can observe that else block will be never executed. Either executeUpdate() method returns 0 for success or we will get an exception. So, writing else block is meaningless. We are writing else block only for understanding purpose, there is no use of else block in these programs.

Oracle

Oracle does not have a simple database model like MySQL or MS SQL Server. We can think of a MySQL “database” as a schema/user in Oracle. If you have the privileges, you can query the DBA_USERS view to see the list of schemas.

SQL> SELECT * FROM DBA_USERS;

SQL> conn system/manager;
Connected.

SQL> SELECT * FROM DBA_USERS;
It will display all the users.

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Also See:-

• Difference between Statement and PreparedStatement
• Different Types of Statements in JDBC
• JDBC Statement Example
• JDBC Statement Interface

Search