/*
Source Code :
OracleDBApplication.java
-----------------------------
*/
package com.luckyfusion.customdbconnector;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;
/**
* @author Lakshmi Prasad Reddy
*
*/
public class OracleDBApplication {
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
/***
* Properties connectionProps = new Properties();
connectionProps.setProperty("user", "mobileadmin");
connectionProps.setProperty("password", "oimdb");
String jdbcUrl = "jdbc:oracle:thin:@" +"192.168.1.100" +":" +"1521" +":"+"oimdb";
System.out.println("JDBC URL :: " + jdbcUrl);
String jdbcDriver = "oracle.jdbc.driver.OracleDriver";
OracleDBApplication oDbApp = new OracleDBApplication();
oDbApp.createUser("192.168.1.100", "1521", "oimdb", "mobileadmin", "oimdb", jdbcDriver, "Lakshmi", "Prasad", "lucky", "1149", "LREDDY", "IN", "IDM", "LuckyFusion");
oDbApp.modifyUser("192.168.1.100", "1521", "oimdb", "mobileadmin", "oimdb", jdbcDriver, "YYY", "EMAIL", "YTR", "CCC");
oDbApp.deleteUser("192.168.1.100", "1521", "oimdb", "mobileadmin", "oimdb", jdbcDriver, "MOBILEDATA", "USERID", "USERID");
*
*
*
*/
}
// Get Oracle Database connector Connection
public Connection getDatabaseConnection(String url, Properties properties, String jdbcDriver)
{
System.out.println("Entering getConnectionWithTimeout");
Connection connection = null;
try {
Class.forName(jdbcDriver);
connection = DriverManager.getConnection(url,properties);
System.out.println("Connection :: " + connection);
}
catch (SQLException e)
{
System.out.println("Error :: Error while creating the connection with database :: " + e.getMessage());
e.printStackTrace();
}
catch (ClassNotFoundException e)
{
System.out.println("Error :: Error while creating the connection with database :: " + e.getMessage());
e.printStackTrace();
}
System.out.println("Exiting getConnectionWithTimeout");
return connection;
}
// Create an User
public String createUser(String serverAddress,String portNumber,String sid,String dbAdminID,String dbAdminPassword,String jdbcDriver,String userID,String firstName,String lastName,String mobileNumber,String manager,
String country,String company,String email)
{
System.out.println("\n\n*********** Entering createUser ***************");
System.out.println("User ID :: " + userID + " First Name :: " + firstName + " Last Name :: " + lastName +" Email :: " + email);
System.out.println("Manager :: " + manager + " Country :: " + country + " Company :: " + company +" Mobile :: " + mobileNumber);
String taskResponse = "NOT_COMPLETED"; // It's the default response
String insertQuery = "INSERT INTO MOBILEDATA (USERID, FIRSTNAME, LASTNAME, EMAIL, MOBILENUMBER, MANAGER, COUNTRY, COMPANY) VALUES (?,?,?,?,?,?,?,?)";
Connection connection = null;
Properties connectionProps = new Properties();
connectionProps.setProperty("user",dbAdminID);
connectionProps.setProperty("password",dbAdminPassword);
String jdbcUrl = "jdbc:oracle:thin:@" +serverAddress +":" +portNumber +":"+sid;
System.out.println("JDBC URL :: " + jdbcUrl);
connection = getDatabaseConnection(jdbcUrl, connectionProps, jdbcDriver);
if(connection != null){
try
{
// JDBC CODE SAMPLE: http://www.mkyong.com/jdbc/jdbc-preparestatement-example-insert-a-record/
PreparedStatement preparedStatement = connection.prepareStatement(insertQuery);
preparedStatement.setString(1, userID);
preparedStatement.setString(2, firstName);
preparedStatement.setString(3, lastName);
preparedStatement.setString(4, email);
preparedStatement.setString(5, mobileNumber);
preparedStatement.setString(6, manager);
preparedStatement.setString(7, country);
preparedStatement.setString(8, company);
int count = preparedStatement.executeUpdate(); // It returns how many records are inserted
if(count == 1)
{
taskResponse = "CREATE_SUCCESS";
}
else
{
taskResponse = "CREATION_FAILURE";
}
}
catch (SQLException e)
{
taskResponse = "CREATION_FAILURE";
System.out.println("Error :: While creating the user" );
e.printStackTrace();
}
}
else {
taskResponse = "NULL_CONNECTION";
}
System.out.println("\n\n*********** Exiting createUser ***************");
return taskResponse;
}
// Update or Modify an user
public String modifyUser(String serverAddress, String portNumber, String sid, String dbAdminID, String dbAdminPassword, String jdbcDriver, String userID, String fieldName, String fieldValueNew, String fieldValueOld){
System.out.println("\n\n*********** Entering modifyUser ***************");
System.out.println("User ID :: " + userID + " Field Name :: " + fieldName + " Field Value (New) :: " + fieldValueNew +" Field Value (Old) :: " + fieldValueOld);
String taskResponse = "NOT_COMPLETED"; // It's the default response
String updateQuery = "UPDATE MOBILEDATA SET ? = ? WHERE USERID = ?";
Connection connection = null;
Properties connectionProps = new Properties();
connectionProps.setProperty("user", dbAdminID);
connectionProps.setProperty("password", dbAdminPassword);
String jdbcUrl = "jdbc:oracle:thin:@" +serverAddress +":" +portNumber +":"+sid;
System.out.println("JDBC URL :: " + jdbcUrl);
connection = getDatabaseConnection(jdbcUrl, connectionProps, jdbcDriver);
if(connection != null){
try {
// JDBC CODE SAMPLE: http://www.mkyong.com/jdbc/jdbc-preparestatement-example-insert-a-record/
PreparedStatement preparedStatement = connection.prepareStatement(updateQuery);
preparedStatement.setString(1, fieldName);
preparedStatement.setString(2, fieldValueNew);
preparedStatement.setString(3, userID);
int count = preparedStatement.executeUpdate(); // It returns how many records are inserted
System.out.println("\n*******************************");
System.out.println("\nUser Modified :: " + userID );
System.out.println("\n*******************************");
if(count == 1){
taskResponse = "MODIFY_SUCCESS";
}
else {
taskResponse = "MODIFY_FAILURE";
}
}
catch (SQLException e)
{
taskResponse = "MODIFY_FAILURE";
System.out.println("Error :: While Modifying the user" );
e.printStackTrace();
}
}
else
{
taskResponse = "NULL_CONNECTION";
}
System.out.println("\n\n*********** Exiting modifyUser ***************");
return taskResponse;
}
// Delete an User
public String deleteUser(String serverAddress, String portNumber, String sid, String dbAdminID, String dbAdminPassword, String jdbcDriver, String userID, String tableName,
String userIDColumn){
System.out.println("\n\n*********** Entering Delete User ***************");
System.out.println("User ID :: " + userID );
String taskResponse = "NOT_COMPLETED"; // It's the default response
String insertQuery = "DELETE FROM ? where ? = ?";
Connection connection = null;
Properties connectionProps = new Properties();
connectionProps.setProperty("user", dbAdminID);
connectionProps.setProperty("password", dbAdminPassword);
String jdbcUrl = "jdbc:oracle:thin:@" +serverAddress +":" +portNumber +":"+sid;
System.out.println("JDBC URL :: " + jdbcUrl);
connection = getDatabaseConnection(jdbcUrl, connectionProps, jdbcDriver);
if(connection!= null){
try {
PreparedStatement preparedStatement = connection.prepareStatement(insertQuery);
preparedStatement.setString(1, tableName);
preparedStatement.setString(2, userIDColumn);
preparedStatement.setString(3, userID);
int count = preparedStatement.executeUpdate(); // It returns how many records are inserted
System.out.println("\n*******************************");
System.out.println("\nUser Got Deleted :: " + userID);
System.out.println("\n*******************************");
if(count == 1){
taskResponse = "DELETE_SUCCESS";
} else {
taskResponse = "DELETE_FAILURE";
}
} catch (SQLException e) {
taskResponse = "DELETE_FAILURE";
System.out.println("Error :: While Deleting the user" );
e.printStackTrace();
}
} else {
taskResponse = "NULL_CONNECTION";
}
System.out.println("\n\n*********** Exiting Delete User ***************");
return taskResponse;
}
// Enable an User
// Disable an User
// Lock an User
// Unlock an User
// Search User
}
Thanks & Regards
Lakshmi Reddy N
Source Code :
OracleDBApplication.java
-----------------------------
*/
package com.luckyfusion.customdbconnector;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;
/**
* @author Lakshmi Prasad Reddy
*
*/
public class OracleDBApplication {
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
/***
* Properties connectionProps = new Properties();
connectionProps.setProperty("user", "mobileadmin");
connectionProps.setProperty("password", "oimdb");
String jdbcUrl = "jdbc:oracle:thin:@" +"192.168.1.100" +":" +"1521" +":"+"oimdb";
System.out.println("JDBC URL :: " + jdbcUrl);
String jdbcDriver = "oracle.jdbc.driver.OracleDriver";
OracleDBApplication oDbApp = new OracleDBApplication();
oDbApp.createUser("192.168.1.100", "1521", "oimdb", "mobileadmin", "oimdb", jdbcDriver, "Lakshmi", "Prasad", "lucky", "1149", "LREDDY", "IN", "IDM", "LuckyFusion");
oDbApp.modifyUser("192.168.1.100", "1521", "oimdb", "mobileadmin", "oimdb", jdbcDriver, "YYY", "EMAIL", "YTR", "CCC");
oDbApp.deleteUser("192.168.1.100", "1521", "oimdb", "mobileadmin", "oimdb", jdbcDriver, "MOBILEDATA", "USERID", "USERID");
*
*
*
*/
}
// Get Oracle Database connector Connection
public Connection getDatabaseConnection(String url, Properties properties, String jdbcDriver)
{
System.out.println("Entering getConnectionWithTimeout");
Connection connection = null;
try {
Class.forName(jdbcDriver);
connection = DriverManager.getConnection(url,properties);
System.out.println("Connection :: " + connection);
}
catch (SQLException e)
{
System.out.println("Error :: Error while creating the connection with database :: " + e.getMessage());
e.printStackTrace();
}
catch (ClassNotFoundException e)
{
System.out.println("Error :: Error while creating the connection with database :: " + e.getMessage());
e.printStackTrace();
}
System.out.println("Exiting getConnectionWithTimeout");
return connection;
}
// Create an User
public String createUser(String serverAddress,String portNumber,String sid,String dbAdminID,String dbAdminPassword,String jdbcDriver,String userID,String firstName,String lastName,String mobileNumber,String manager,
String country,String company,String email)
{
System.out.println("\n\n*********** Entering createUser ***************");
System.out.println("User ID :: " + userID + " First Name :: " + firstName + " Last Name :: " + lastName +" Email :: " + email);
System.out.println("Manager :: " + manager + " Country :: " + country + " Company :: " + company +" Mobile :: " + mobileNumber);
String taskResponse = "NOT_COMPLETED"; // It's the default response
String insertQuery = "INSERT INTO MOBILEDATA (USERID, FIRSTNAME, LASTNAME, EMAIL, MOBILENUMBER, MANAGER, COUNTRY, COMPANY) VALUES (?,?,?,?,?,?,?,?)";
Connection connection = null;
Properties connectionProps = new Properties();
connectionProps.setProperty("user",dbAdminID);
connectionProps.setProperty("password",dbAdminPassword);
String jdbcUrl = "jdbc:oracle:thin:@" +serverAddress +":" +portNumber +":"+sid;
System.out.println("JDBC URL :: " + jdbcUrl);
connection = getDatabaseConnection(jdbcUrl, connectionProps, jdbcDriver);
if(connection != null){
try
{
// JDBC CODE SAMPLE: http://www.mkyong.com/jdbc/jdbc-preparestatement-example-insert-a-record/
PreparedStatement preparedStatement = connection.prepareStatement(insertQuery);
preparedStatement.setString(1, userID);
preparedStatement.setString(2, firstName);
preparedStatement.setString(3, lastName);
preparedStatement.setString(4, email);
preparedStatement.setString(5, mobileNumber);
preparedStatement.setString(6, manager);
preparedStatement.setString(7, country);
preparedStatement.setString(8, company);
int count = preparedStatement.executeUpdate(); // It returns how many records are inserted
if(count == 1)
{
taskResponse = "CREATE_SUCCESS";
}
else
{
taskResponse = "CREATION_FAILURE";
}
}
catch (SQLException e)
{
taskResponse = "CREATION_FAILURE";
System.out.println("Error :: While creating the user" );
e.printStackTrace();
}
}
else {
taskResponse = "NULL_CONNECTION";
}
System.out.println("\n\n*********** Exiting createUser ***************");
return taskResponse;
}
// Update or Modify an user
public String modifyUser(String serverAddress, String portNumber, String sid, String dbAdminID, String dbAdminPassword, String jdbcDriver, String userID, String fieldName, String fieldValueNew, String fieldValueOld){
System.out.println("\n\n*********** Entering modifyUser ***************");
System.out.println("User ID :: " + userID + " Field Name :: " + fieldName + " Field Value (New) :: " + fieldValueNew +" Field Value (Old) :: " + fieldValueOld);
String taskResponse = "NOT_COMPLETED"; // It's the default response
String updateQuery = "UPDATE MOBILEDATA SET ? = ? WHERE USERID = ?";
Connection connection = null;
Properties connectionProps = new Properties();
connectionProps.setProperty("user", dbAdminID);
connectionProps.setProperty("password", dbAdminPassword);
String jdbcUrl = "jdbc:oracle:thin:@" +serverAddress +":" +portNumber +":"+sid;
System.out.println("JDBC URL :: " + jdbcUrl);
connection = getDatabaseConnection(jdbcUrl, connectionProps, jdbcDriver);
if(connection != null){
try {
// JDBC CODE SAMPLE: http://www.mkyong.com/jdbc/jdbc-preparestatement-example-insert-a-record/
PreparedStatement preparedStatement = connection.prepareStatement(updateQuery);
preparedStatement.setString(1, fieldName);
preparedStatement.setString(2, fieldValueNew);
preparedStatement.setString(3, userID);
int count = preparedStatement.executeUpdate(); // It returns how many records are inserted
System.out.println("\n*******************************");
System.out.println("\nUser Modified :: " + userID );
System.out.println("\n*******************************");
if(count == 1){
taskResponse = "MODIFY_SUCCESS";
}
else {
taskResponse = "MODIFY_FAILURE";
}
}
catch (SQLException e)
{
taskResponse = "MODIFY_FAILURE";
System.out.println("Error :: While Modifying the user" );
e.printStackTrace();
}
}
else
{
taskResponse = "NULL_CONNECTION";
}
System.out.println("\n\n*********** Exiting modifyUser ***************");
return taskResponse;
}
// Delete an User
public String deleteUser(String serverAddress, String portNumber, String sid, String dbAdminID, String dbAdminPassword, String jdbcDriver, String userID, String tableName,
String userIDColumn){
System.out.println("\n\n*********** Entering Delete User ***************");
System.out.println("User ID :: " + userID );
String taskResponse = "NOT_COMPLETED"; // It's the default response
String insertQuery = "DELETE FROM ? where ? = ?";
Connection connection = null;
Properties connectionProps = new Properties();
connectionProps.setProperty("user", dbAdminID);
connectionProps.setProperty("password", dbAdminPassword);
String jdbcUrl = "jdbc:oracle:thin:@" +serverAddress +":" +portNumber +":"+sid;
System.out.println("JDBC URL :: " + jdbcUrl);
connection = getDatabaseConnection(jdbcUrl, connectionProps, jdbcDriver);
if(connection!= null){
try {
PreparedStatement preparedStatement = connection.prepareStatement(insertQuery);
preparedStatement.setString(1, tableName);
preparedStatement.setString(2, userIDColumn);
preparedStatement.setString(3, userID);
int count = preparedStatement.executeUpdate(); // It returns how many records are inserted
System.out.println("\n*******************************");
System.out.println("\nUser Got Deleted :: " + userID);
System.out.println("\n*******************************");
if(count == 1){
taskResponse = "DELETE_SUCCESS";
} else {
taskResponse = "DELETE_FAILURE";
}
} catch (SQLException e) {
taskResponse = "DELETE_FAILURE";
System.out.println("Error :: While Deleting the user" );
e.printStackTrace();
}
} else {
taskResponse = "NULL_CONNECTION";
}
System.out.println("\n\n*********** Exiting Delete User ***************");
return taskResponse;
}
// Enable an User
// Disable an User
// Lock an User
// Unlock an User
// Search User
}
Thanks & Regards
Lakshmi Reddy N
