Database Connection using JDBC/JAVA

Here some code for database connection which one help to create database connection….

DataTransaction.java


public class DataTransaction  
{
	private static final String userName = "mtnl";
	private static final String passWord = "matix";
	
	/*********************************** DRIVER **************************************/
	/** ORACLE **/
	private static final String driver = "oracle.jdbc.driver.OracleDriver"; // Option 1
	private static final String driver = "sun.jdbc.odbc.JdbcOdbcDriver"; // Option 2
		
	/** MySQL **/
	private static final String driver = "com.mysql.jdbc.Driver";
	
	/*********************************** URL **************************************/
	/** ORACLE **/
	private static final String url = "jdbc:oracle:thin:@192.168.100.63:1521:DBName"; 
	/** MySQL **/	
	private static final String url = "jdbc:mysql://192.168.50.89/DBName";
		
	// private static final String url = "jdbc:oracle:thin:@192.168.100.63:1521:RUBY;failoverPartner=192.168.100.114:1521:RUBY;integratedsecurity=true";
	// private static final String url = "jdbc:oracle:thin(DESCRIPTION = (FAILOVER = on ) (ADDRESS_LIST =  (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.63)(PORT=1521)) (ADDRESS=(PROTOCOL=IPC)(HOST= 192.168.100.114)(PORT=1521)) (LOAD_BALANCE = ON)) (CONNECT_DATA=(SERVICE_NAME = RUBY ) (FAILOVER_MODE = (TYPE=session) (METHOD=basic) (RETRIES = 180) (DELAY =5) )))" ;
	
	public static Connection connection=null;
	public static int connectionCount=0;
	public Statement statement;
	public PreparedStatement pstmt = null;
	private int id;

	/**
	*	Constructor
	*/
	public DataTransaction(){}

	public void createConnection(){}

	public DataTransaction(boolean setCon)
	{
		try
		{
			setConnection();
		}
		catch(Exception e)
		{
			System.out.println("Error in Connection:"+ e.toString());
		}
	}

	/**
	*	setConnection method for setting connection with drivers and database
	*/
	public void setConnection() throws SQLException
	{
		try
		{
			try
			{
				Class.forName(driver); 
				/**
				*  Create a connection to the database
				*/
				if(connection == null || connection.isClosed())
				{
					System.out.println(" MAKING CONNECTION WITH FIRST SERVER.");
					connection = DriverManager.getConnection(url,userName,passWord);
				}
			}
			catch (SQLException e)
			{
				try
				{
					System.out.println(" MAKING CONNECTION WITH SECOND SERVER.");
					connection = DriverManager.getConnection(url,userName,passWord);
				}
				catch(SQLException ex)
				{
					System.out.println ("Could not connect to the database msg second connection failed :" + ex.getMessage());
				}
			}
		}
		catch(Exception e)
		{
			System.out.println("Connection Open Exception"+e);
		}
	}
	
	/**
	*	Closes the connection with database and drivers
	*/
	public void closeConnection() throws SQLException
	{
		try
		{
			if(statement != null)
			{
				statement.close();
			}
			else
			{
				System.out.println("Close Connection else  of DataTransaction ");
			}
		}
		catch(Exception e)
		{
			System.out.println("Fianlize of DataTransaction in catch : "+e);
		}
	}
	
	/**
	*	Pass SQL query through this method. this method returns Result Set containing requested data
	*/
	public ResultSet viewQuery(String query) throws SQLException, Exception
	{
		if(isLowerCaseReq)
		{
			query = query.toLowerCase();
		}
		else
		{
			isLowerCaseReq = true;
		}	
		
		try
		{
			if(connection.isClosed())
			{
				setConnection();
			}
			
			statement=connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			rs=statement.executeQuery(query);
			connection.commit();
		}
		catch(Exception e)
		{
			System.out.println("Error in dt.viewQuery() " + e.getMessage() + " : " + query);
		}
		return rs;
	}
	
	/**
	*	InsertInTo method to insert in database from JSP PAGE  returns true if inserted in database otherwise false using synchronisation
	*/
	
	public synchronized boolean insertQuery(String query) throws SQLException, Exception
	{
		try
		{
			if(connection.isClosed())
			{
				setConnection();
			}
			
			statement=connection.createStatement();
			
			if(!(statement.execute(query)))
			{
				connection.commit();
				return true;
			}
			else
			{
				return false;
			}
		}
		catch(Exception e)
		{
			System.out.println("Insert exception data transaction "+e  );
			return false;
		}
	}	
	
	/**
	*	Delete method to delete row in database from JSP PAGE returns true if inserted in database otherwise false
	*/
	public boolean deleteQuery(String query) throws SQLException, Exception
	{    
		return insertQuery(query);        
	}

	/**
	*	Update method to update data in database from JSP PAGE accepts string from user and returns returns true if inserted in database otherwise false
	**/	
	public boolean updateQuery(String query) throws SQLException, Exception
	{  
		return  insertQuery(query);        
	}
	
	/**
	*	For Search Functionality Search query passed through this method and it returns Result Set containing requested data
	*/
	public ResultSet searchQuery(String query) throws SQLException, Exception
	{      
		try
		{
			searchRS = viewQuery(query);                
		}
		catch(Exception e)
		{
			System.out.println("Search exception data transaction "+e  );
		}
		return searchRS;
	}
	 
	public static void main(String[] args){}

	public Connection getConnection()
	{
		return this.connection;
	}
	
	public Statement getStatement()
	{
		try
		{
			this.setConnection();
			statement = this.connection.createStatement();
			return this.statement;
		}
		catch(Exception e)
		{
			System.out.println("Cannot create Statement" + e.getMessage());
			return null;
		}
	}
	
	public void gccall()
	{
		System.gc();
	}
	
	protected void finalize()
	{
		try
		{
			if(statement != null)
			{
				statement.close();
			}	
		}
		catch(Exception e)
		{
			System.out.println("Fianlize of DataTransaction in catch");
		}
		System.gc();
	}
	
}

Advertisements

About Sanju
I am Software Programmer. I am working in JAVA/J2EE Technologies.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: