The complete class has only two methods. And class is
import connection.ConnectionUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Calendar;
import javax.sql.DataSource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
/**
*
* @author oracle
*/
public class SponserSummaryDAO {
private Log log = LogFactory.getLog(SponserSummaryDAO.class);
private static final String updateClicksQuery = "update sponser_summary set
sp_sum_clicks = sp_sum_clicks + 1 where sp_sum_sid = ? and sp_sum_date =
trunc(sysdate)";
private static final String getCityIdQuery = "select c_id from cities where
lower(c_name) like lower(?)";
private static final String updateImpByCityQuery = " update
sponser_summ_by_cities set sp_sum_c_imp = sp_sum_c_imp + 1 where
sp_sum_c_sid = ? and lower(sp_sum_c_city) = ?";
private DataSource dataSource;
public SponserSummaryDAO(){
log.info("^^^^^Cretion of SponserSummaryDAO :
"+Calendar.getInstance().getTime().toString());
dataSource = ConnectionUtil.getDataSource();
}
public void updateClicks(int sid){
Connection connection = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
connection = dataSource.getConnection();
pstmt = connection.prepareStatement(updateClicksQuery);
pstmt.setInt(1, sid );
int updated = pstmt.executeUpdate();
log.info(" sponser clicks updated val : "+updated);
}catch(Exception ex){
ex.printStackTrace();
log.error(ex.getMessage());
}finally{
try {if( pstmt != null)pstmt.close();} catch (SQLException ex)
{ex.printStackTrace();}
try {if( connection != null)connection.close();} catch (SQLException ex)
{ex.printStackTrace();}
connection = null;
pstmt = null;
}
}
public void updateImpByCity(int sid, String city){
Connection connection = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
connection = dataSource.getConnection();
pstmt = connection.prepareStatement(updateImpByCityQuery);
pstmt.setInt(1, sid );
pstmt.setString(2, city.toLowerCase());
int updated = pstmt.executeUpdate();
log.info(" sponser imp by city updated val : "+updated);
}catch(Exception ex){
ex.printStackTrace();
log.error(ex.getMessage());
}finally{
try {if( pstmt != null)pstmt.close();} catch (SQLException ex)
{ex.printStackTrace();}
try {if( connection != null)connection.close();} catch (SQLException ex)
{ex.printStackTrace();}
connection = null;
pstmt = null;
}
}
public String getCityId(String city){
String cityID = null;
Connection connection = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
connection = dataSource.getConnection();
pstmt = connection.prepareStatement(getCityIdQuery);
pstmt.setString(1, "%"+city+"%");
rs = pstmt.executeQuery();
if( rs.next() ){
cityID = rs.getString(1);
}else{
cityID = "-1";
}
log.info(" city ID : "+cityID);
}catch(Exception ex){
ex.printStackTrace();
log.error(ex.getMessage());
}finally{
try{if( rs!=null)rs.close();}catch(SQLException ex){ex.printStackTrace();}
try {if( pstmt != null)pstmt.close();} catch (SQLException ex)
{ex.printStackTrace();}
try {if( connection != null)connection.close();} catch (SQLException ex)
{ex.printStackTrace();}
connection = null;
pstmt = null;
rs = null;
}
return cityID;
}
public String getCountryName(long ipSum){
String name = null;
Connection connection = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
connection = dataSource.getConnection();
pstmt = connection.prepareStatement("select country_name from ip_to_geo
where ? between ip_from and ip_to");
pstmt.setString(1, ""+ipSum);
rs = pstmt.executeQuery();
if( rs.next() ){
name = rs.getString(1);
}
}catch(Exception ex){
ex.printStackTrace();
}finally{
try{if( rs!=null)rs.close();}catch(SQLException ex){ex.printStackTrace();}
try {if( pstmt != null)pstmt.close();} catch (SQLException ex)
{ex.printStackTrace();}
try {if( connection != null)connection.close();} catch (SQLException ex)
{ex.printStackTrace();}
connection = null;
pstmt = null;
rs = null;
}
return name;
}
protected void finalize() throws Throwable {
log.info("^^^^^Finalize of SponserSummaryDAO :
"+Calendar.getInstance().getTime().toString());
}
}
