Java Database Connectivity Exercise
Problem Statement-1:
Write a Java program to display the employee id, age, first name and last name using JDBC connectivity.
Test Data: ( In Database )
ID: 100, Age: 23, First: Raj, Last: SharmaID: 101, Age: 24, First: Bala, Last: SinghID: 102, Age: 25, First: Anu, Last: PriyaID: 103, Age: 26, First: Riya, Last: Khan
Expected Output:
Connecting to database...
Creating statement...
ID: 100, Age: 23, First: Raj, Last: Sharma
ID: 101, Age: 24, First: Bala, Last: Singh
ID: 102, Age: 25, First: Anu, Last: Priya
ID: 103, Age: 26, First: Riya, Last: Khan
Solution:
import java.sql.*;
public class FirstExample {
static final String DB_URL = "jdbc:mysql://localhost/ShapeAI";
static final String USER = "scott";
static final String PASS = "tiger";
static final String QUERY = "SELECT id, first, last, age FROM Employees";
public static void main(String[] args) {
// Open a connection
try(Connection con = DriverManager.getConnection(DB_URL, USER, PASS);
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(QUERY);) {
// Extract data from result set
while (rs.next()) {
// Retrieve by column name
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();
}
}
}
Output:
Connecting to database...
Creating statement...
ID: 100, Age: 23, First: Raj, Last: Sharma
ID: 101, Age: 24, First: Bala, Last: Singh
ID: 102, Age: 25, First: Anu, Last: Priya
ID: 103, Age: 26, First: Riya, Last: Khan
Explanation:
The class FirstExample contains static string variables for DB_URL, USER, PASS and QUERY. Get connection with the database using method getConnection() in DriverManager. Create statement for database using createStatement() method and execute the query using executeQuery() method. Get the data from the database using getInt() and getString() methods.