update the picture in database

Question

Hello! I am creating an app for students database. Recently I got a problem when I want to change data that is related to the specific student. Especially problems occur when I want to change the picture for the specific student. I need to check if the picture belongs to the student or not. I store my pictures to the folder if I change the picture, I delete previous one and create a new one. My question about how to check if a picture belongs to the specific student?

I check students in this way.

    // get the name of the student from first table getValueTableName

    // get the name of the picture from first table getValueTablePicture

    getValueTableName = jTable1.getModel()
            .getValueAt(jTable1.getSelectedRow(), 0).toString();

    getValueTablePicture = jTable1.getModel()
            .getValueAt(jTable1.getSelectedRow(), 3).toString();

    File sourceFile = new File(getValueTablePicture);

    setPicture = sourceFile.getName();

    // GET NAME OF THE STUDENT AND THE PICTURE FROM DATABASE AND COMPARE
    // THEM TO THE CURRENT USER
    try {
        CallableStatement statement = null;
        Connection data = getmyConnection();
        statement = data.prepareCall("{call editStudentByName}");

        myResults = statement.executeQuery();

        while (myResults.next()) {
            // COPY PATH IN getEditName
            getEditName = myResults.getString("Name");
            // COPY PATH IN getEditPicture
            getEditPicture = myResults.getString("Picture");

            // add students from database to array
            // mylist.add(getEditName.concat(getEditPicture));
            mylist.add("\n");

        }

        myResults.close();

    } catch (Exception c) {
        c.printStackTrace();
    }

    // I don't know how to move from this point when I check names with loop
    // I check the student with the loop
    for (String person : mylist) {
        if (getValueTableName.concat(sourceFile.getName()).equals(person) == true) {

        }
        System.out.print(getValueTableName.concat(sourceFile.getName())
                .equals(person));
        errors.append(
                "- Please choose another picture or rename it!\n Picture ")
                .append(getEditPicture)
                .append(" is exist for a student " + getEditName)
                .append("\n");
        jTextField3.requestFocusInWindow();
        jTextField3.setText("");

    }

Answer 1

Finally, I got my things done. It was a very painful moment, but I got what I wanted. The problem was hidden in a column "Picture" in the table "Student" of my database. First, I add UNIQUE constraint ensures to my column that all values in a column are different. Second, I created two stored procedures:

CREATE DEFINER=`root`@`localhost` PROCEDURE `checkStudentByPicture`(
in picture_name varchar(100)
)
BEGIN

SELECT COUNT(*) FROM students_center.Student st WHERE st.Picture = picture_name;
END

The first procedure checks if my column has unique names and doesn't allow to add the same name to the column.

And I created a second one:

CREATE DEFINER=`root`@`localhost` PROCEDURE `getStudentNameByPicture`(
in name varchar(45),
in pic_name varchar(100)
)
BEGIN

SELECT COUNT(*) FROM students_center.Student st WHERE st.Name = name and st.Picture=pic_name;
END

The second procedure checks if the column "Picture" is related to the column "Name". If the column "Picture" is not related, the user doesn't allow to change the name.

Here is the code about checking if my data related to context:

private boolean validateFieldEditStudent() {
    StringBuilder errors = new StringBuilder(); 

    // call stored procedure checkStudentByPicture                        
    File sourceFile = new File(jTextField3.getText()); 
    String checkStudentName=jTable2.getValueAt(jTable2.getSelectedRow(), 0).toString(); 

    try {
        CallableStatement statement = null;
        Connection data = getmyConnection(); 
        statement = data.prepareCall("{call checkStudentByPicture(?)}");

        statement.setString(1, sourceFile.getName());
        myResults = statement.executeQuery();

        while (myResults.next()) {
            //COPY PATH IN pictureName 
            getPictureCount = myResults.getInt(1);
        }

        myResults.close();
     } catch (Exception c) {
        c.printStackTrace();
     } 
}

// call stored procedure checkStudentByPicture 
try {
    CallableStatement statement = null;
    Connection data = getmyConnection(); 
    statement = data.prepareCall("{call getStudentNameByPicture(?, ?)}");

    statement.setString(1, checkStudentName);
    statement.setString(2, sourceFile.getName());
    myResults = statement.executeQuery();

    while (myResults.next()) {
        //COPY PATH IN pictureName 
        getStudentNameCount = myResults.getInt(1);
    }

    myResults.close();
} catch (Exception c) {
    c.printStackTrace();
}

//check if data is related to the specific user

if(getFileChooserCount > 0) {
       if(getStudentNameCount != 1) {
           if(getPictureCount == 1) {
            errors.append("- Picture "+sourceFile.getName()+" existed in the database!\n");
        jTextField3.setText("");
        jTextField3.requestFocusInWindow();   
        }
    }  
}

 if (errors.length() > 0) {
           JOptionPane.showMessageDialog(EditStudent, errors, "Warning!", JOptionPane.ERROR_MESSAGE); 
            return false;
        }
    return true;
}

Answer 2

The very first thing I'd do is not using separate Strings with strange names like getEditName - this is confusing. Consider having POJO (Student.class) and working with that

1. So you want to replace single student picture? Why do you have to iterate some array in this case? You should get single student from database (by Id or via some unique set of attributes).

2. Ok, lets say you have a list of students and you iterate over it. But you still have to change picture for single person so that there is no need to check. Simply do

   String pictureFileName = person.getPicture();//assming getPicture() method returns current picture path
   

   and then save new picture with the same name. In this case old picture will be overwritten so that no issue with checks.

UPD: If you want to check for picture existence you can do the same:

String pictureFileName = person.getPicture();
File f = new File(pictureFileName );
if(f.exists() && !f.isDirectory()) { 
    // do something, say report warning
}

UPD: If you don't require an ability for students to share same file as a picture it's better to implement this at DB level as well via this https://www.w3schools.com/sql/sql_unique.asp - so that you simply won't be able to write two different student records with the same picture path field. In this case checks won't matter anymore and you can simply overwrite the picture file because it belongs to single student only