1)Read data from Oracle table using HashMap
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Scanner;
import java.util.logging.Level;
import java.util.logging.Logger;
//create a Employee3 class
class Employee3 {
private int Id;
private String Fname;
private String Lname;
private int Age;
public Employee3(int id, String fname, String lname, int age) {
this.Id = id;
this.Fname = fname;
this.Lname = lname;
this.Age = age;
}
public int getId() {
return this.Id;
}
public String getFname() {
return this.Fname;
}
public String getLname() {
return this.Lname;
}
public int getAge() {
return this.Age;
}
// create a tostring method to diplay data
public String ToString() {
return this.Id + " " + this.Fname + " " + this.Lname + " " + this.Age;
}
}
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
@Controller
@RequestMapping("/storedQuery")
public class StoredProcedureController {
@Value("${spring.datasource.url}")
private String url;
@Value("${spring.datasource.password}")
private String password;
@Value("${spring.datasource.username}")
private String user;
@RequestMapping(value = "/fetchAllRecords")
public @ResponseBody List<HashMap<String, Object>> fetchAllRecords() throws SQLException {
String query = "SELECT * FROM employee";
Connection dbConnection = DriverManager.getConnection(url, user, password);
PreparedStatement stmt = dbConnection.prepareStatement(query);
ResultSet resultSet = stmt.executeQuery();
ResultSetMetaData rsmd = resultSet.getMetaData();
int columns = rsmd.getColumnCount();
ArrayList<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
while (resultSet.next()) {
HashMap<String, Object> row = new HashMap<String, Object>(columns);
for (int i = 1; i <= columns; ++i) {
row.put(rsmd.getColumnName(i), resultSet.getObject(i));
}
list.add(row);
}
dbConnection.close();
return list;
}
}
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Scanner;
import java.util.logging.Level;
import java.util.logging.Logger;
//create a Employee3 class
class Employee3 {
private int Id;
private String Fname;
private String Lname;
private int Age;
public Employee3(int id, String fname, String lname, int age) {
this.Id = id;
this.Fname = fname;
this.Lname = lname;
this.Age = age;
}
public int getId() {
return this.Id;
}
public String getFname() {
return this.Fname;
}
public String getLname() {
return this.Lname;
}
public int getAge() {
return this.Age;
}
// create a tostring method to diplay data
public String ToString() {
return this.Id + " " + this.Fname + " " + this.Lname + " " + this.Age;
}
}
public class Oracle {
public static void main(String[] args) throws Exception {
Connection con = null;
Statement st = null;
ResultSet rs = null;
Employee3 u;
// create the hashmap
HashMap<Integer, Employee3> map = new HashMap<Integer, Employee3>();
try {
Class.forName("oracle.jdbc.OracleDriver");
con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "system", "swamy");
st = con.createStatement();
DatabaseMetaData dbm = con.getMetaData();
ResultSet rs1 = dbm.getTables(null, null, "employee", null);
if (rs1.next()) {
System.out.println("Table exists");
} else {
System.out.println("Table does not exist");
// create table
int x = st.executeUpdate(
"create table employee(id number NOT NULL, fname VARCHAR(100) NOT NULL,lname VARCHAR(40) NOT NULL, age number(3))");
if (x == -1)
System.out.println("table created");
}
// insert row
Scanner s = new Scanner(System.in);
System.out.println("enter user id");
int n = s.nextInt();
System.out.println("enter user fname");
String str = s.next();
System.out.println("enter user lname");
String lname = s.next();
System.out.println("enter user age");
int m = s.nextInt();
int x1 = st.executeUpdate("insert into employee values(" + n + ",'" + str + "','" + lname + "'," + m + ")");
if (x1 == -1)
System.out.println("1 row inserted");
// select data
rs = st.executeQuery("SELECT * FROM employee");
while (rs.next()) {
Integer id = rs.getInt("id");
String fname = rs.getString("fname");
String lname1 = rs.getString("lname");
int age = rs.getInt("age");
u = new Employee3(id, fname, lname1, age);
// set data in the hashmap
map.put(id, u);
}
// display data from the hashmap
for (Integer i : map.keySet()) {
Employee3 us = map.get(i);
System.out.println(us.getId() + " " + us.getFname() + " " + us.getLname() + " " + us.getAge());
}
// show data from hashmap using our ToString Method
System.out.println("______With ToString______");
for (Integer i : map.keySet()) {
Employee3 us = map.get(i);
System.out.println(us.ToString());
}
} catch (SQLException ex) {
Logger.getLogger(Oracle.class.getName()).log(Level.SEVERE, null, ex);
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
2)Read data from My SQL table using HashMap
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Scanner;
import java.util.logging.Level;
import java.util.logging.Logger;
//create a Employee2 class
class Employee2 {
private int Id;
private String Fname;
private String Lname;
private int Age;
public Employee2() {
}
public Employee2(int id, String fname, String lname, int age) {
this.Id = id;
this.Fname = fname;
this.Lname = lname;
this.Age = age;
}
public int getId() {
return this.Id;
}
public String getFname() {
return this.Fname;
}
public String getLname() {
return this.Lname;
}
public int getAge() {
return this.Age;
}
// create a tostring method to diplay data
public String ToString() {
return this.Id + " " + this.Fname + " " + this.Lname + " " + this.Age;
}
}
public class MySql {
public static void main(String[] args) {
Connection con = null;
Statement st = null;
ResultSet rs = null;
Employee2 u;
// create the hashmap
HashMap<Integer, Employee2> map = new HashMap<Integer, Employee2>();
try {
con = DriverManager.getConnection("jdbc:mysql://localhost/testdb", "root", "root");
st = con.createStatement();
DatabaseMetaData dbm = con.getMetaData();
ResultSet rs1 = dbm.getTables(null, null, "employee", null);
if (rs1.next()) {
System.out.println("Table exists");
} else {
System.out.println("Table does not exist");
// create table
int x = st.executeUpdate(
"create table employee(id INT NOT NULL, fname VARCHAR(100) NOT NULL,lname VARCHAR(40) NOT NULL, age int(3))");
if (x == -1)
System.out.println("table created");
}
// insert row
Scanner s = new Scanner(System.in);
System.out.println("enter user id");
int n = s.nextInt();
System.out.println("enter user fname");
String str = s.next();
System.out.println("enter user lname");
String lname = s.next();
System.out.println("enter user age");
int m = s.nextInt();
int x1 = st.executeUpdate("insert into employee values(" + n + ",'" + str + "','" + lname + "'," + m + ")");
if (x1 == -1)
System.out.println("1 row inserted");
// select data
rs = st.executeQuery("SELECT * FROM employee");
while (rs.next()) {
Integer id = rs.getInt("id");
String fname = rs.getString("fname");
String lname1 = rs.getString("lname");
int age = rs.getInt("age");
u = new Employee2(id, fname, lname1, age);
// set data in the hashmap
map.put(id, u);
}
// display data from the hashmap
for (Integer i : map.keySet()) {
Employee2 us = map.get(i);
System.out.println(us.getId() + " " + us.getFname() + " " + us.getLname() + " " + us.getAge());
}
// show data from hashmap using our ToString Method
System.out.println("______With ToString______");
for (Integer i : map.keySet()) {
Employee2 us = map.get(i);
System.out.println(us.ToString());
}
} catch (SQLException ex) {
Logger.getLogger(MySql.class.getName()).log(Level.SEVERE, null, ex);
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
3)Read data from My SQL table using HashMap without passing field names.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
@Controller
@RequestMapping("/storedQuery")
public class StoredProcedureController {
@Value("${spring.datasource.url}")
private String url;
@Value("${spring.datasource.password}")
private String password;
@Value("${spring.datasource.username}")
private String user;
@RequestMapping(value = "/fetchAllRecords")
public @ResponseBody List<HashMap<String, Object>> fetchAllRecords() throws SQLException {
String query = "SELECT * FROM employee";
Connection dbConnection = DriverManager.getConnection(url, user, password);
PreparedStatement stmt = dbConnection.prepareStatement(query);
ResultSet resultSet = stmt.executeQuery();
ResultSetMetaData rsmd = resultSet.getMetaData();
int columns = rsmd.getColumnCount();
ArrayList<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
while (resultSet.next()) {
HashMap<String, Object> row = new HashMap<String, Object>(columns);
for (int i = 1; i <= columns; ++i) {
row.put(rsmd.getColumnName(i), resultSet.getObject(i));
}
list.add(row);
}
dbConnection.close();
return list;
}
}
4)Read data from Mongo DB
import java.net.UnknownHostException;
import com.mongodb.BasicDBObject;
import com.mongodb.DB;
import com.mongodb.DBCollection;
import com.mongodb.DBCursor;
import com.mongodb.MongoClient;
import com.mongodb.MongoException;
class Employee extends BasicDBObject {
private static final long serialVersionUID = 2105061907470199595L;
public String FNAME = "fname";
public String LNAME = "lname";
public String AGE = "age";
public static final String COLLECTION_NAME = "employee";
public void setFname(String fname) {
put(FNAME, fname);
}
public void setLname(String lname) {
put(LNAME, lname);
}
public void setAge(String age) {
put(AGE, age);
}
}
public class MongoDb {
public static void main(String[] args) throws UnknownHostException, MongoException {
// create the hashmap
MongoClient mongo = new MongoClient("localhost", 27017);
DB db = mongo.getDB("testdb");
DBCollection employeeCollection = db.getCollection(Employee.COLLECTION_NAME);
Employee employee = new Employee();
employee.setFname("swamy");
employee.setLname("Bairu");
employee.setAge("31");
// display values
DBCursor cursor = employeeCollection.find();
while (cursor.hasNext()) {
System.out.println(cursor.next());
}
}
}
5) To run above java files, we have to use pom.xml file and application.properties file
pom.xml file
application.properties file
## Spring DATASOURCE (DataSourceAutoConfiguration & DataSourceProperties)
spring.datasource.url = jdbc:mysql://localhost:3306/shristestdb?useSSL=true
spring.datasource.username = root
spring.datasource.password = swamy
## Hibernate Properties
# The SQL dialect makes Hibernate generate better SQL for the chosen database
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect
# Hibernate ddl auto (create, create-drop, validate, update)
spring.jpa.hibernate.ddl-auto = update
5) To run above java files, we have to use pom.xml file and application.properties file
pom.xml file
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.corejava</groupId>
<artifactId>read-data-from-db</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<repositories>
<!-- Repository for ORACLE ojdbc6. -->
<repository>
<id>codelds</id>
<url>https://code.lds.org/nexus/content/groups/main-repo</url>
</repository>
</repositories>
<dependencies>
<dependency>
<groupId>org.mongodb</groupId>
<artifactId>mongo-java-driver</artifactId>
<version>2.12.3</version>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.21</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.oracle.jdbc/ojdbc8 -->
<dependency>
<groupId>com.oracle.jdbc</groupId>
<artifactId>ojdbc8</artifactId>
<version>18.0.0.0</version>
</dependency>
</dependencies>
</project>
## Spring DATASOURCE (DataSourceAutoConfiguration & DataSourceProperties)
spring.datasource.url = jdbc:mysql://localhost:3306/shristestdb?useSSL=true
spring.datasource.username = root
spring.datasource.password = swamy
## Hibernate Properties
# The SQL dialect makes Hibernate generate better SQL for the chosen database
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect
# Hibernate ddl auto (create, create-drop, validate, update)
spring.jpa.hibernate.ddl-auto = update
