To avoid duplication of data in database

Manupatel

New member
Hi all,
i'm using the below code to upload XLS file and parse the contents into database, and i'm able to parse all the contents to directory table and its parent and child tables like category,subcategory,category_subcategory,subcategory_directory tables but if i upload the same xls file again its contents are added into the directory table. but it should be avoided (duplication of data should be prevented ) so pls any one help me in doing this.


class DirectoryService {

static transactional = true
StringBuffer query
Sql sql
Connection connection;
PreparedStatement preparedStatement

def path

DirectoryService() {

query = new StringBuffer("")
path = "${CH.platinum.directory.path}"

}

def upload(file) {
if(!file.empty) {
file.transferTo(new File("$path/myfile.xls"))
return true
}
else {
return false
}
}


def synchDirectory(def user) {
println "1 to ka 4..."
String fileName="$path/myfile.xls";
Vector dataHolder=read(fileName);
saveToDatabase(dataHolder);
}
public static Vector read(String fileName) {
Vector cellVectorHolder = new Vector();
try{
FileInputStream myInput = new FileInputStream(fileName);
POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
HSSFSheet mySheet = myWorkBook.getSheetAt(0);
Iterator rowIter = mySheet.rowIterator();
while(rowIter.hasNext()){
HSSFRow myRow = (HSSFRow) rowIter.next();
Iterator cellIter = myRow.cellIterator();
Vector cellStoreVector=new Vector();

while(cellIter.hasNext()){
HSSFCell myCell = (HSSFCell) cellIter.next();

cellStoreVector.addElement(myCell);
}
cellVectorHolder.addElement(cellStoreVector);
}
}catch (Exception e){e.printStackTrace(); }
println"manu----->" +cellVectorHolder
return cellVectorHolder;
}
private static void saveToDatabase(Vector dataHolder) {
def directory = new Directory()
ArrayList listDirectory = new ArrayList();
String code="";
String name="";
String category="";
String subcategory="";
String description="";
String price="";
String quantity="";
for (int i=1;i<dataHolder.size(); i++)
{
Vector cellStoreVector=(Vector)dataHolder.elementAt(i);
println"manu---->" +cellStoreVector
int cellIndex=0;
code = ((HSSFCell)cellStoreVector.elementAt(cellIndex)).toString();
cellIndex++;
name = ((HSSFCell)cellStoreVector.elementAt(cellIndex)).toString()
cellIndex++;
category = ((HSSFCell)cellStoreVector.elementAt(cellIndex)).toString()
cellIndex++;
subcategory = ((HSSFCell)cellStoreVector.elementAt(cellIndex)).toString()
cellIndex++;
description = ((HSSFCell)cellStoreVector.elementAt(cellIndex)).toString()
cellIndex++;
price = ((HSSFCell)cellStoreVector.elementAt(cellIndex)).toString()
cellIndex++;
quantity = ((HSSFCell)cellStoreVector.elementAt(cellIndex)).toString()
cellIndex++;

def tempCategory = Category.findByName(category)
if(!tempCategory){
tempCategory = new Category(name:category)
//tempUser.addToCategories(tempCategory)
}
def tempSubCategory = Subcategory.findByName(subcategory)
if(!tempSubCategory){
tempSubCategory = new Subcategory(name:subcategory)
tempCategory.addToSubcategories(tempSubCategory)
}
//if(tempCategory.subcategories != null && (!tempCategory.subcategories.contains(tempSubCategory)) )
//if(tempUser.categories != null && !tempUser.categories.contains(tempCategory))

def q=new Directory(code:code,name:name,category:category,subcategory:subcategory,description:description,price:(Double.parseDouble(price)),quantity:(int)Double.parseDouble(quantity),file:"").save()
tempSubCategory.addToDirectories(q)
// tempUser.addToDirectories(q)
tempCategory.save()
}
}

Thanks inadvance,
Manu
 
S

sharmaragesh

Guest
i'm using the below code to upload XLS file and parse the contents into database, and i'm able to parse all the contents to directory table and its parent and child tables like category,subcategory,category_subcategory,subcategory_directory tables but if i upload the same xls file again its contents are added into the directory table. but it should be avoided (duplication of data should be prevented ) so pls any one help me in doing this.


Hi all,
i'm using the below code to upload XLS file and parse the contents into database, and i'm able to parse all the contents to directory table and its parent and child tables like category,subcategory,category_subcategory,subcategory_directory tables but if i upload the same xls file again its contents are added into the directory table. but it should be avoided (duplication of data should be prevented ) so pls any one help me in doing this.


class DirectoryService {

static transactional = true
StringBuffer query
Sql sql
Connection connection;
PreparedStatement preparedStatement

def path

DirectoryService() {

query = new StringBuffer("")
path = "${CH.platinum.directory.path}"

}

def upload(file) {
if(!file.empty) {
file.transferTo(new File("$path/myfile.xls"))
return true
}
else {
return false
}
}


def synchDirectory(def user) {
println "1 to ka 4..."
String fileName="$path/myfile.xls";
Vector dataHolder=read(fileName);
saveToDatabase(dataHolder);
}
public static Vector read(String fileName) {
Vector cellVectorHolder = new Vector();
try{
FileInputStream myInput = new FileInputStream(fileName);
POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
HSSFSheet mySheet = myWorkBook.getSheetAt(0);
Iterator rowIter = mySheet.rowIterator();
while(rowIter.hasNext()){
HSSFRow myRow = (HSSFRow) rowIter.next();
Iterator cellIter = myRow.cellIterator();
Vector cellStoreVector=new Vector();

while(cellIter.hasNext()){
HSSFCell myCell = (HSSFCell) cellIter.next();

cellStoreVector.addElement(myCell);
}
cellVectorHolder.addElement(cellStoreVector);
}
}catch (Exception e){e.printStackTrace(); }
println"manu----->" +cellVectorHolder
return cellVectorHolder;
}
private static void saveToDatabase(Vector dataHolder) {
def directory = new Directory()
ArrayList listDirectory = new ArrayList();
String code="";
String name="";
String category="";
String subcategory="";
String description="";
String price="";
String quantity="";
for (int i=1;i<dataHolder.size(); i++)
{
Vector cellStoreVector=(Vector)dataHolder.elementAt(i);
println"manu---->" +cellStoreVector
int cellIndex=0;
code = ((HSSFCell)cellStoreVector.elementAt(cellIndex)).toString();
cellIndex++;
name = ((HSSFCell)cellStoreVector.elementAt(cellIndex)).toString()
cellIndex++;
category = ((HSSFCell)cellStoreVector.elementAt(cellIndex)).toString()
cellIndex++;
subcategory = ((HSSFCell)cellStoreVector.elementAt(cellIndex)).toString()
cellIndex++;
description = ((HSSFCell)cellStoreVector.elementAt(cellIndex)).toString()
cellIndex++;
price = ((HSSFCell)cellStoreVector.elementAt(cellIndex)).toString()
cellIndex++;
quantity = ((HSSFCell)cellStoreVector.elementAt(cellIndex)).toString()
cellIndex++;

def tempCategory = Category.findByName(category)
if(!tempCategory){
tempCategory = new Category(name:category)
//tempUser.addToCategories(tempCategory)
}
def tempSubCategory = Subcategory.findByName(subcategory)
if(!tempSubCategory){
tempSubCategory = new Subcategory(name:subcategory)
tempCategory.addToSubcategories(tempSubCategory)
}
//if(tempCategory.subcategories != null && (!tempCategory.subcategories.contains(tempSubCategory)) )
//if(tempUser.categories != null && !tempUser.categories.contains(tempCategory))

def q=new Directory(code:code,name:name,category:category,subcategory:subcategory,description:description,price:(Double.parseDouble(price)),quantity:(int)Double.parseDouble(quantity),file:"").save()
tempSubCategory.addToDirectories(q)
// tempUser.addToDirectories(q)
tempCategory.save()
}
}

Thanks inadvance,
Manu
 
Top