JDBC
Package : java.sql.*
https://jdbc.postgresql.org/download.html
https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
Steps to implement JDBC program
1. Load the driver class
Class.forName("org.postgresql.Driver");
2. Establish the connection
con = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/postgres", "postgres", "abcd12345");
3. Create a statement
Statement stmt = con.createStatement();
4. Prepare the Query and Execute it
String sqlQuery = "select username, password from register where username='abcd'"
ResultSet rs = stmt.executeQuery(sqlQuery);
5. Process the results
while(rs.next()){
user = rs.getString("username");
pass = rs.getString("password");
}
6. Close the statement and Connection
stmt.close();
con.close();
Types of Statements:
1) Statement
Statement stmt = con.createStatement();
While creating the statement, we're not passing anyQuery params
ResultSet rs = stmt.executeQuery(sqlQuery);
sqlQuery is passed while executing execute(), executeUpdate(), executeQuery(). The following things happen everytime,
.query submission
.query complilation
.query execution
2) PreparedStatement
PreparedStatment ps = con.prepareStatement(sqlQuery);
.query submission
.query compilation
ResultSet rs = ps.executeQuery();
.query execution
When ur creating the preparestatement by passing query as a query parameter, then query will be submitted to the sql engine 7 will be compiled. This compiled query will be stored in the database memory not in JVM memory.
3) CallableStatement
Used to execute the stored procedures avaliable in database.
String getDBProcedures = "{call getDBProcedure()}";
callableStatement = dbConnection.prepareCall(getDBProcedures);
Difference between execute(), executeUpdate() and executeQuery()
.boolean execute()
return true when the query is executed successfully returns false otherwise.
.int executeUpdate()
returns number of rows affected with the given query
.ResultSet executeQuery()
use this method, for the select stmts when u execute this, number of rows will be returned and will be stored in the resultSet objects. We can iterate the ResultSet object with the following methods.
rs = ps.executeQuery();
while(rs.next()){
user = rs.getString("username");
pass = rs.getString("password");
}
package com.bellinfo.advanced.jdbc;
import java.sql.*;
import java.util.Properties;
public class DepartmentRepository {
private static final String CREATE_SQL = "CREATE TABLE Department ( deptid int, depname varchar, location varchar)";
private static final String INSERT_SQL = "INSERT INTO Department VALUES(?,?,?)";
private static final String SELECT_SQL = "select * from Department";
private static final String UPDATE_SQL = "Update Department set location=? where depname=?";
private static final String VALIDATE_SQL= "select exists (select 1 from pg_tables where schemaname='public' and tablename='department')";
private static final String URL = "jdbc:postgresql://:5432/belljavasep";
Connection con = null;
void getConnection(){
try {
Class.forName("org.postgresql.Driver");
Properties prop = new Properties();
prop.setProperty("user","postgres");
prop.setProperty("password","");
con = DriverManager.getConnection(URL,prop);
// return con;
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
void createDepartment() throws SQLException {
getConnection();
PreparedStatement psValidate = con.prepareStatement(VALIDATE_SQL);
ResultSet rs = psValidate.executeQuery();
boolean isTableExsist = false;
while(rs.next()){
isTableExsist = rs.getBoolean(1);
}
if(isTableExsist){
System.out.println("Hey ..Table already created in your schema, so...skiping it");
}else{
PreparedStatement ps = con.prepareStatement(CREATE_SQL);
boolean isCreated = ps.execute();
System.out.println("Hey Table has been created");
}
}
void insertDepartmentRecord(Department d){
getConnection();
try {
PreparedStatement ps = con.prepareStatement(INSERT_SQL);
ps.setInt(1,d.getId());
ps.setString(2,d.getName());
ps.setString(3,d.getLoc());
ps.executeUpdate();
System.out.println("Record created");
} catch (SQLException e) {
e.printStackTrace();
}
}
void updateDeparmentRecord(String loc, String name){
getConnection();
try {
PreparedStatement ps = con.prepareStatement(UPDATE_SQL);
ps.setString(1,loc);
ps.setString(2,name);
int count = ps.executeUpdate();
System.out.println("Records Updated count "+count);
} catch (SQLException e) {
e.printStackTrace();
}
}
void fetchRecords() throws SQLException {
getConnection();
PreparedStatement ps = con.prepareStatement(SELECT_SQL);
ResultSet rs = ps.executeQuery();
while(rs.next()){
System.out.println(rs.getInt("deptid") + " "+rs.getString("depname") + " "+rs.getString("location") );
}
}
}
Demo Class
package com.bellinfo.advanced.jdbc;
import java.sql.SQLException;
import java.util.Scanner;
public class DepartmentDemo {
enum OPERATIONS{ INSERT, UPDATE};
public static void main(String[] args) throws SQLException {
DepartmentRepository dr = new DepartmentRepository();
dr.createDepartment();
Scanner scan = new Scanner(System.in);
System.out.println("Hey what do you want do");
String oper= scan.next();
if(oper.equalsIgnoreCase(OPERATIONS.INSERT.name())){
System.out.println("How many records");
int count = scan.nextInt();
Department d =null;
for(int i=0;i<count;i++){
d = new Department();
System.out.println("id");
d.setId(scan.nextInt());
System.out.println("name");
d.setName(scan.next());
System.out.println("location");
d.setLoc(scan.next());
dr.insertDepartmentRecord(d);
}
}else if(oper.equalsIgnoreCase(OPERATIONS.UPDATE.name())){
System.out.println("which deptarment you want to update");
String dept = scan.next();
System.out.println("what is the new location");
String loc = scan.next();
dr.updateDeparmentRecord(loc,dept);
}
System.out.println("After requested operation, records in DB");
dr.fetchRecords();
}
}
Department.java
package com.bellinfo.advanced.jdbc;
import java.util.Objects;
public class Department {
private int id;
private String name;
private String loc;
public Department() {
}
public Department(int id, String name, String loc) {
this.id = id;
this.name = name;
this.loc = loc;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getLoc() {
return loc;
}
public void setLoc(String loc) {
this.loc = loc;
}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
Department that = (Department) o;
return id == that.id &&
Objects.equals(name, that.name) &&
Objects.equals(loc, that.loc);
}
@Override
public int hashCode() {
return Objects.hash(id, name, loc);
}
@Override
public String toString() {
return "Department{" +
"id=" + id +
", name='" + name + '\'' +
", loc='" + loc + '\'' +
'}';
}
}
