Apache DBUTIL simple useful demo code

Apache DBUTIL simple useful demo code

Required Jar files
1) commons-dbutils-1.5.jar (Apache)
2) ojdbc6-11.0.2.0.jar (Oracle)

Functionality

Create table
Insert Data
Update Data
Update Partial Data
Delete Table

Code

DatabaseWorkController.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;


class DatabaseWorkController{
private Connection connection = null;
private QueryRunner queryRunner = null;

long startTime = 0;

DatabaseWorkController(){ }

public void loadDriverAndMakeConnection(){
System.out.println("Method loadDriverAndMakeConnection()\n");
try
{

      Class.forName("oracle.jdbc.driver.OracleDriver");    
connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.8.87:1521:aaadb", "netvertextrunk", "netvertextrunk");     
queryRunner = new QueryRunner();

}catch(Exception e) {
       System.out.println("Error : "+e);
}  
}

public void insertData(){
System.out.println("\nMethod insertData()");
try {
startTime = System.currentTimeMillis();

int result = queryRunner.update(connection, "INSERT INTO PLAYERS (ID, NAME, TEAM, TOTALRUNS) VALUES(?, ?, ?, ?)","1","SACHIN","India",46000);
result = queryRunner.update(connection, "INSERT INTO PLAYERS (ID, NAME, TEAM, TOTALRUNS) VALUES(?, ?, ?, ?)","1","SAMSON","India",1000);
System.out.println("Inserted Rows: "+result);
System.out.println("Total Time taken: "+(System.currentTimeMillis()-startTime)+" ms");
} catch (SQLException e) {
e.printStackTrace();
}
}

public void selectAllData(){
System.out.println("\nMethod selectAllData()");

ResultSetHandler<List<PlayerData>> rsh = new BeanListHandler<PlayerData>(PlayerData.class);
try {
startTime = System.currentTimeMillis();
List<PlayerData> result = queryRunner.query( connection, "SELECT * FROM PLAYERS",rsh);
for(PlayerData player: result){
System.out.println(player);  
}
System.out.println("Total Time taken: "+(System.currentTimeMillis()-startTime)+" ms");
} catch (SQLException e) {
e.printStackTrace();
} 
}

public void oldWaySelectAllData(){
System.out.println("\nMethod oldWaySelectAllData()");
ResultSet rs = null;
try {
startTime = System.currentTimeMillis();
Statement stmt = connection.createStatement();
rs = stmt.executeQuery("select * from players");
while(rs.next()){
int id = rs.getInt("ID");
String name = rs.getString("name");
String team = rs.getString("team");
String totalRuns = rs.getString("totalruns");
System.out.println("PlayerData [Id=" + id + ", Name=" + name + ", Team=" + team
+ ", TotalRun=" + totalRuns + "]");
}
rs.close();
System.out.println("Total Time taken: "+(System.currentTimeMillis()-startTime)+" ms");
} catch (SQLException e) {
e.printStackTrace();
} 
}

public void selectParticularData(){
System.out.println("\n\nMethod selectParticularData()");

ResultSetHandler<PlayerData> rsh = new BeanHandler<PlayerData>(PlayerData.class);

try {
startTime = System.currentTimeMillis();
PlayerData player = queryRunner.query( connection, "SELECT * FROM PLAYERS WHERE ID = ? ",rsh, "1");
System.out.println("Total Time taken: "+(System.currentTimeMillis()-startTime)+" ms");
System.out.println(player);  
} catch (SQLException e) {
e.printStackTrace();
} 
}

public void updateParticularData(){
System.out.println("\n\nMethod updateParticularData()");
try {
 
startTime = System.currentTimeMillis();
int result = queryRunner.update(connection,"UPDATE PLAYERS SET NAME = ? WHERE ID = ? ","Sachin Tendulkar","1");
System.out.println("Total Time taken: "+(System.currentTimeMillis()-startTime)+" ms");
System.out.println("Update Rows: "+result);
} catch (SQLException e) {
e.printStackTrace();
}
}

public void deleteTable(){
System.out.println("\n\nMethod deleteTable()");
try {
startTime = System.currentTimeMillis();
int result = queryRunner.update(connection,"DROP TABLE PLAYERS");
System.out.println("Total Time taken: "+(System.currentTimeMillis()-startTime)+" ms");
System.out.println("Table deleted successfully: "+result);
} catch (SQLException e) {
e.printStackTrace();
}

}

public void destroyConnection(){
System.out.println("\n\nMethod destroyConnection()");
try {
startTime = System.currentTimeMillis();
DbUtils.close(connection);
System.out.println("Total Time taken: "+(System.currentTimeMillis()-startTime)+" ms");
} catch (SQLException e) {
e.printStackTrace();
}
}

public void createTable() {
System.out.println("\n\nMethod createTable()");
try {

startTime = System.currentTimeMillis();
int result = queryRunner.update(connection,"CREATE TABLE PLAYERS ( ID int, Name varchar(255), Team varchar(255), TotalRuns varchar(255))");
System.out.println("Total Time taken: "+(System.currentTimeMillis()-startTime)+" ms");
System.out.println("Table created successfully: "+result);
} catch (SQLException e) {
e.printStackTrace();
}
}
}

public class DBUtilFlight {

    public static void main(String[] dataBag) 
    {
    DatabaseWorkController controller = new DatabaseWorkController();
    controller.loadDriverAndMakeConnection();
   
    controller.createTable();
    controller.insertData();
    controller.selectAllData();
    controller.oldWaySelectAllData();
   
    controller.selectParticularData();
    controller.updateParticularData();
    controller.selectParticularData();
   
    controller.deleteTable();
    }
}


PlayerData.java

public class PlayerData {

private String id;
private String name;
private String team;
private Long totalRuns;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getTeam() {
return team;
}
public void setTeam(String team) {
this.team = team;
}
public Long getTotalRuns() {
return totalRuns;
}
public void setTotalRuns(Long totalRuns) {
this.totalRuns = totalRuns;
}

@Override
public String toString() {
return "PlayerData [Id=" + id + ", Name=" + name + ", Team=" + team
+ ", TotalRun=" + totalRuns + "]";
}


}