package com.myapp.test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Set;
import java.lang.StringBuffer;
public class PcIntUtilSqls
{
public PcIntUtilSqls()
{
}
/*checks if a column with the same value already exists in case of addition of a new record*/
public static boolean isDuplicate(String tableName,String colName,String value,Connection conn) throws Exception
{
ResultSet rs= null;
PreparedStatement stat = null;
String sql = new FastStringBuffer("Select count(1) From ").append(tableName)
.append(" Where upper(trim(").append(colName).append(")) = ?").toString();
try
{
stat = conn.prepareStatement(sql);
stat.setString(1,value.toUpperCase().trim());
rs = stat.executeQuery();
if(rs.next() && rs.getInt(1) >= 1)
return true;
else
return false;
}
finally
{
try
{
if(rs != null) rs.close();
if(stat != null) stat.close();
}
catch (Exception ex) {};
}
}
/*checks if a column with the same value already exists in case of updation of a record*/
public static boolean isDuplicate(String tableName,String colName,String value,int code,Connection conn) throws Exception
{
ResultSet rs= null;
PreparedStatement stat = null;
String sql = new FastStringBuffer("Select count(1) From ").append(tableName)
.append(" Where upper(trim(").append(colName).append(")) = ?")
.append(" And code != ?").toString();
try
{
stat = conn.prepareStatement(sql);
stat.setString(1,value.toUpperCase().trim());
stat.setInt(2,code);
rs = stat.executeQuery();
if(rs.next() && rs.getInt(1) >= 1)
return true;
else
return false;
}
finally
{
try
{
if(rs != null) rs.close();
if(stat != null) stat.close();
}
catch (Exception ex) {};
}
}
/*Updates values stored in hashmap into the database table for 'code'
*The 'key' of the HashMap (hshCols) contains the column name.
*The value stored at the key is the value for the column in the update
*statement.
*/
public static void update(HashMap hshCols,String tableName,String colNames,int value,Connection conn) throws Exception
{
PreparedStatement stat = null;
ArrayList arrValues = new ArrayList();
String key = null;
FastStringBuffer sql = new FastStringBuffer("Update ").append(tableName)
.append(" Set ");
try
{
Set s = hshCols.keySet();
Iterator it = s.iterator();
while (it.hasNext())
{
key = (String) it.next();
sql.append(key).append(" = ?,");
arrValues.add(hshCols.get(key));
}
sql.deleteCharAt(sql.length() - 1);
sql.append(" Where ").append(colNames).append(" = ? ");
int size = arrValues.size();
stat = conn.prepareStatement(sql.toString());
for (int i = 0; i < size; i++)
{
stat.setString(i + 1,(String)arrValues.get(i));
}
stat.setInt(size + 1,value);
stat.executeUpdate();
}
finally
{
try
{
if(stat != null) stat.close();
}
catch (Exception ex) { throw ex; }
}
}
/*inserts values stored in hashmap into the database table
*The 'key' of the HashMap (hshCols) contains the column name.
*The value stored at the key is the value for the column in the insert
*statement.
*/
public static void insert(HashMap hshCols,String tableName,Connection conn) throws Exception
{
PreparedStatement stat = null;
String colNames = "";
ArrayList arrValues = new ArrayList();
String key = null;
FastStringBuffer sql = new FastStringBuffer("Insert into ").append(tableName).append("(");
try
{
Set s = hshCols.keySet();
Iterator it = s.iterator();
while (it.hasNext())
{
key = (String) it.next();
colNames = colNames + "," + key;
arrValues.add(hshCols.get(key));
}
if (colNames.length() > 0 ) colNames = colNames.substring(1, colNames.length());
sql.append(colNames).append(") values(");
int size = arrValues.size();
for (int i = 0; i < size; i++)
sql.append("?,");
sql.deleteCharAt(sql.length() - 1);
sql.append(")");
stat = conn.prepareStatement(sql.toString());
for (int i = 0; i < size; i++)
{
stat.setString(i + 1,(String)arrValues.get(i));
}
stat.executeUpdate();
}
finally
{
try
{
if(stat != null) stat.close();
}
catch (Exception ex) { throw ex; }
}
}
/* Gets next value in a database sequence */
public static int getNextSeqVal(String sequenceName,Connection conn) throws Exception
{
PreparedStatement stat = null;
ResultSet rs = null;
try
{
stat = conn.prepareStatement("select " + sequenceName + ".nextval from dual");
rs = stat.executeQuery();
if (rs.next())
{
return rs.getInt(1);
}
}
finally
{
try
{
if(rs != null) rs.close();
if(stat != null) stat.close();
}
catch (Exception ex) { throw ex;};
}
return -1;
}
