JDBC - Select Database

This chapter provides examples 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 or whatever 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.

Example: Selecting a Database

In this example, we've three static strings containing a dababase connection url, username, password. Now using DriverManager.getConnection() method, we've prepared a database connection. Once connection is prepared, we've printed the success message.

In case of any exception while connecting to the database, a catch block handled SQLException and printed the stack trace.

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();
      } 
   }
}

Output

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:\>

As we've seen how to connect to the database, in following example, we'll get data from the table of the connected database.

Example: Getting Records from a Table of Selected Database

The list of databases is first displayed using "SHOW DATABASES" command. Then, the SQL command "USE TUTORIALSPOINT" is used to select a database. Then, a SQL query is issued on table "EMPLOYEES", to demonstrate that the mentioned database is in use.

In case of any exception while creating the database, a catch block handled SQLException and printed the stack trace.

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

import java.sql.*;
// This class demonstrates use of selecting a database.
public class JDBCExample {
   static final String DB_URL = "jdbc:mysql://localhost/";
   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);) {		      
         System.out.println("Connected database successfully...");
         Statement stmt = conn.createStatement();
         ResultSet rs1 = stmt.executeQuery("SHOW DATABASES");
         System.out.println("DATABASES");
         System.out.println("-------------------------------------------");
         while( rs1.next()){
            System.out.println(rs1.getString(1));
         }

         System.out.println("-------------------------------------------------------");
         // The line below SELECTS a database TUTORIALSPOINT   
         stmt.executeUpdate("use TUTORIALSPOINT");
         ResultSet rs2 = stmt.executeQuery("select * from employees");
         System.out.println("Id of employees");
         while (rs2.next()){
            System.out.println("id= " + rs2.getInt("id"));
         }
      } catch (SQLException e) {
         e.printStackTrace();
      } 
   }
}

Output

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
Connected database successfully..
DATABASES
-------------------------------------
information_schema
mysql
performance_schema
sample_db1
students
sys
tutorialspoint
tutorialspoint_copy
world
-------------------------------------------------------
Id of employees
id= 1
id= 2
id= 3
id= 4
id= 7
id= 8
id= 21
id= 22
id= 25
id= 26
id= 34
id= 35
id= 36
id= 37

C:\>

Let's explore other commands like to show tables in the selected database in example below.

Example: Getting Current Database and Table Names of Selected Database

The SQL command "USE TUTORIALSPOINT" is used to select a database. Now using "SELECT DATABASE()", we're printing the current database selected. Then, a SQL query is issued "SHOW TABLES", to show the tables of the connected database.

In case of any exception while creating the database, a catch block handled SQLException and printed the stack trace.

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

import java.sql.*;

// This class demonstrates use of SELECT DATABASE() command and SHOW TABLES        
public class JDBCExample {

   static final String DB_URL = "jdbc:mysql://localhost/";
   static final String USER = "root";
   static final String PASS = "guest123";

   public static void main(String args[]) {
      // TODO code application logic here
      try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);) {		      
         System.out.println("Connected database successfully...");

         Statement stmt = conn.createStatement();
         // This statement will make TUTORIALSPOINT as the current database.
         stmt.executeUpdate("use TUTORIALSPOINT");

         // This will tell us which is the selected database
         ResultSet rs1 = stmt.executeQuery("SELECT DATABASE()");

         while( rs1.next()){
            System.out.println("Current database: " + rs1.getString(1));
         }

         ResultSet rs2 = stmt.executeQuery("SHOW TABLES");
         System.out.println("List of tables in current database TUTORIALSPOINT");
         System.out.println("---------------------------------------------------");

         while(rs2.next()){
            System.out.println( rs2.getString(1));
         }

      }catch (SQLException e) {
         e.printStackTrace();
      } 
   }
}

Output

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
Connected database successfully...
Current database: tutorialspoint
List of tables in current database TUTORIALSPOINT
---------------------------------------------------
employees
jdbc_blob_clob
officers
students

C:\>

Print Page