How to sort Database table(Many columns) using Comparable in collections:
we can sort the Database Table of multiple columns using the Comparable in the Collection concept.
package com.nt.extensive;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Scanner;
public class User {
public static void main(String[] args) throws IOException {
final String DB_URL = "jdbc:mysql://localhost:3307/organization";
Scanner sc=new Scanner(System.in);
// Database credentials
final String USER = "root";
final String PASS = "root";
Connection conn = null;
Statement stmt = null;
try {
// STEP 2: Register JDBC driver
Class.forName("com.mysql.jdbc.Driver");
// STEP 3: Open a connection
System.out.println("Connecting to database");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
if (conn != null)
// STEP 4: Execute a query
System.out.println("Creating statement...");
stmt = conn.createStatement();
String sql = null;
if (stmt != null)
sql = "SELECT ename,epassword,phonenumber,email,salary,Age FROM employee";
ResultSet rs = stmt.executeQuery(sql);
if (rs != null)
while (rs.next()) {
String sname = rs.getString("ename");
String password = rs.getString("epassword");
Long phonenumbers = rs.getLong("phonenumber");
String eemail = rs.getString("email");
Double Salary = rs.getDouble("salary");
int Age = rs.getInt("age");
System.out.print(sname);
System.out.print("\t");
System.out.print(Age);
System.out.print("\t");
System.out.print(password);
System.out.print("\t\t");
System.out.print(phonenumbers);
System.out.print("\t\t\t");
System.out.print(eemail);
System.out.print("\t\t\t");
System.out.print(Salary);
System.out.println();
}
ResultSetMetaData rsmd=rs.getMetaData();
int num=rsmd.getColumnCount();
System.out.println(num+" Columns");
for(int i=1;i<=num;i++)
{
System.out.println("Column Names "+rsmd.getColumnName(i));
}
System.out.println("Enter 1. For Salary \t ");
System.out.println("Enter 2. For Ename \t ");
int i=sc.nextInt();
ResultSet rs1 = stmt.executeQuery("Select * from employee");
ArrayList<Newsort> al = new ArrayList<Newsort>();
while (rs1.next()) {
Newsort e = new Newsort();
String sname = (rs1.getString("ename"));
String password = (rs1.getString("epassword"));
Long phonenum = (rs1.getLong("phonenumber"));
String Email = (rs1.getString("Email"));
Double sal = (rs1.getDouble("salary"));
int age=(rs1.getInt("Age"));
e.setEname(sname);
e.setEpassword(password);
e.setPhonenumber(phonenum);
e.setEmail(Email);
e.setSalary(sal);
e.setAge(age);
e.setManipulate(i);
al.add(e);
}
for (Newsort em : al) {
System.out.println(em);
}
Collections.sort(al);
System.out.println("Sort Employees based on your Requirement ");
for (Newsort em : al) {
System.out.println(em);
}
}
catch (NullPointerException Ne) {
System.out.println("NullPointerException");
}
catch (ClassNotFoundException e) {
System.out.println("UnRegistered Driver");
} catch (SQLException sw) {
System.out.println("SqlException");
} finally {
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (stmt != null) {
stmt.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
Java Bean Class Related to the Creation of NewSort;
In this Newsort Class we created the object of lncluding several types of objects under a Single JavaBean Class that is Newsort.
package com.nt.extensive;
public class Newsort implements Comparable<Newsort> {
String Ename;
String Epassword;
int Age;
Double Salary;
Long Phonenumber;
String Email;
int i;
void NewSort(String ename, String epassword, int age, Double salary,
Long phonenumber) {
setEname(ename);
setEpassword(epassword);
setAge(age);
setSalary(salary);
setPhonenumber(phonenumber);
}
public String getEname() {
return Ename;
}
public void setEname(String ename) {
Ename = ename;
}
public String getEpassword() {
return Epassword;
}
public void setEpassword(String epassword) {
Epassword = epassword;
}
public int getAge() {
return Age;
}
public void setAge(int age) {
Age = age;
}
public Double getSalary() {
return Salary;
}
public void setSalary(double salary) {
Salary = salary;
}
public Long getPhonenumber() {
return Phonenumber;
}
public void setPhonenumber(Long phonenumber) {
Phonenumber = phonenumber;
}
public String getEmail() {
return Email;
}
public void setEmail(String email) {
Email = email;
}
public int getManipulate() {
return i;
}
public void setManipulate(int z) {
i = z;
}
@Override
public String toString() {
return "Newsort [Ename=" + Ename + ", Epassword=" + Epassword
+ ", Age=" + Age + ", Salary=" + Salary + ", Phonenumber="
+ Phonenumber + ", Email=" + Email + "]";
}
@Override
public int compareTo(Newsort z) {
int value = 0;
int x = this.getManipulate();
// System.out.println("Enter the number");
if (x == 1) {
{
value = this.Salary.compareTo(z.getSalary());
}
} else if (x == 2) {
{
value = this.Ename.compareTo(z.getEname());
}
}
return value;
}
}
