The SELECT assertion is utilized to recover information from a table. This profits the information as a table known as result set.
Syntax
Following is the punctuation of the SELECT assertion −
ij> SELECT column_name, column_name, ... FROM table_name;
Or,
Ij>SELECT * from table_name
Example
Allow us to assume we have a table named Employees in the data set as demonstrated underneath −
ij> CREATE TABLE Employees (
Id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
Name VARCHAR(255),
Salary INT NOT NULL,
Location VARCHAR(255),
PRIMARY KEY (Id)
);
> > > > > > > 0 rows inserted/updated/deleted
Also, embedded four records in it as demonstrated underneath −
ij> INSERT INTO Employees (Name, Salary, Location) VALUES
('Amit', 30000, 'Hyderabad'),
('Kalyan', 40000, 'Vishakhapatnam'),
('Renuka', 50000, 'Delhi'),
('Archana', 15000, 'Mumbai');
> > > > 4 rows inserted/updated/deleted
The accompanying SQL explanation recovers the name, age and compensation subtleties of the multitude of representatives in the table;
ij> SELECT Id, Name, Salary FROM Employees;
The yield of this question is −
ID |NAME |SALARY
------------------------------------------------------------------------
1 |Amit |30000
2 |Kalyan |40000
3 |Renuka |50000
4 |Archana |15000
4 rows selected
In the event that you need to get all the records of this table immediately, use * rather than the names of the segments.
ij> select * from Employees;
This will deliver the accompanying outcome −
ID |NAME |SALARY |LOCATION
------------------------------------------------------------------
1 |Amit |30000 |Hyderabad
2 |Kalyan |40000 |Vishakhapatnam
3 |Renuka |50000 |Delhi
4 |Archana |15000 |Mumbai
4 rows selected
Retrieve Data using JDBC program
This segment shows you how to Retrieve information from a table in Apache Derby data set utilizing JDBC application.
On the off chance that you need to demand the Derby network worker utilizing network customer, ensure that the worker is going. The class name for the Network customer driver is org.apache.derby.jdbc.ClientDriver and the URL is jdbc:derby://localhost:1527/DATABASE_NAME;create=true;user=USER_NAME;passw ord=PASSWORD"
Follow the means offered underneath to Retrieve information from a table in Apache Derby −
Step 1: Register the driver
To speak with the data set, above all else, you need to enroll the driver. The forName() technique for the Class acknowledges a String esteem speaking to a class name loads it in to the memory, which consequently enrolls it. Register the driver utilizing this technique.
Step 2: Get the connection
When all is said in done, the initial step we do to convey to the information base is to associate with it. The Connection class speaks to the actual association with a data set worker. You can make an association object by summoning the getConnection() strategy for the DriverManager class. Make an association utilizing this strategy.
Step 3: Create a statement object
You need to make a Statement or PreparedStatement or, CallableStatement objects to send SQL proclamations to the information base. You can make these utilizing the strategies createStatement(), prepareStatement() and, prepareCall() individually. Make both of these items utilizing the fitting technique.
Step 4: Execute the query
In the wake of making an assertion, you need to execute it. The Statement class gives different strategies to execute an inquiry like the execute() technique to execute an explanation that profits more than one outcome set. The executeUpdate() strategy executes questions like INSERT, UPDATE, DELETE. The executeQuery() technique to results that profits information and so forth Use both of these techniques and execute the assertion made already.
Example
Following JDBC model exhibits how to Retrieve information from a table in Apache Derby utilizing JDBC program. Here, we are associating with an information base named sampleDB (will make in the event that it doesn't exist) utilizing the installed driver.
The executeQuery() strategy restores a ResultSet object which holds the consequence of the assertion. At first the outcome set pointer will be at the main record, you can print the substance of the ResultSet object utilizing its next() and getXXX() techniques.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class RetrieveData {
public static void main(String args[]) throws SQLException,
ClassNotFoundException {
//Registering the driver
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
//Getting the Connection object
String URL = "jdbc:derby:sampleDB;create=true";
Connection conn = DriverManager.getConnection(URL);
//Creating the Statement object
4Statement stmt = conn.createStatement();
//Creating a table and populating it
String query = "CREATE TABLE Employees("
+ "Id INT NOT NULL GENERATED ALWAYS AS IDENTITY, "
+ "Name VARCHAR(255), Salary INT NOT NULL, "
+ "Location VARCHAR(255), "
+ "PRIMARY KEY (Id))";
String query = "INSERT INTO Employees("
+ "Name, Salary, Location) VALUES "
+ "('Amit', 30000, 'Hyderabad'), "
+ "('Kalyan', 40000, 'Vishakhapatnam'), "
+ "('Renuka', 50000, 'Delhi'), "
+ "('Archana', 15000, 'Mumbai'), "
+ "('Trupthi', 45000, 'Kochin'), "
+ "('Suchatra', 33000, 'Pune'), "
+ "('Rahul', 39000, 'Lucknow'), "
+ "('Trupti', 45000, 'Kochin')";
//Executing the query
String query = "SELECT Id, Name, Salary FROM Employees";
ResultSet rs = stmt.executeQuery(query);
while(rs.next()) {
System.out.println("Id: "+rs.getString("Id"));
System.out.println("Name: "+rs.getString("Name"));
System.out.println("Salary: "+rs.getString("Salary"));
System.out.println(" ");
}
}
}
Output
On executing the above program, you will get the accompanying yield
Id: 1
Name: Amit
Salary: 30000
Id: 2
Name: Kalyan
Salary: 43000
Id: 3
Name: Renuka
Salary: 50000
Id: 4
Name: Archana
Salary: 15000
Id: 5
Name: Trupthi
Salary: 45000
Id: 6
Name: Suchatra
Salary: 33000
Id: 7
Name: Rahul
Salary: 39000