lundi 20 juillet 2015

Insert or update on duplicate

I am working on a Java application which stores information into a sqlite database.

The columns in the table are fontName fontTrait fontSize fontPath data which are all of type TEXT except for fontSize which is an INT and data which is a BLOB.

I am following this question SQLite UPSERT - ON DUPLICATE KEY UPDATE but I do not think it fits my situation well since I am working with blobs. Specifically, I want to insert a new entry if fontName fontTrait fontSize fontPath do not match, but update the data if there is a match.

    @Override
    public void setUp() throws SQLException, ClassNotFoundException
    {
        establishConnection();
        if (!checkIfTableExists()) {
            Statement stmt = connection.createStatement();
            String s1 = "CREATE TABLE OCR_TRAINER " +
                    "(ID INT PRIMARY KEY NOT NULL, " +
                    " FONT_NAME TEXT NOT NULL, " +
                    " FONT_TRAIT TEXT NOT NULL, " +
                    " FONT_SIZE INT NOT NULL, " +
                    " FONT_PATH TEXT NOT NULL, " +
                    " REGEX TEXT NOT NULL, " +
                    " ANALYSIS_DATA BLOB NOT NULL)";
            stmt.executeUpdate(s1);
            stmt.close();
        }
    }

    @Override
    public void export(IExportableData exportableData) throws SQLException, IOException
    {
        String fontName = exportableData.getFontName();
        String fontTrait = exportableData.getFontTrait();
        int fontSize = exportableData.getFontSize();
        String fontPath = exportableData.getFontPath();
        String regex = exportableData.getRegex();

        String s1 = "INSERT OR IGNORE INTO " + tableName +
                " (FONT_NAME) (FONT_TRAIT) (FONT_SIZE) (FONT_PATH) (REGEX) VALUES (?)";
        PreparedStatement pstmt = connection.prepareStatement(s1);
        pstmt.setString(1, fontName);
        pstmt.setString(2, fontTrait);
        pstmt.setInt(3, fontSize);
        pstmt.setString(4, fontPath);
        pstmt.setString(5, regex);

        List<IAnalysisData> data = exportableData.getAnalysisData();
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        ObjectOutputStream oos = new ObjectOutputStream(baos);
        oos.writeObject(data);
        oos.flush();
        oos.close();

        InputStream is = new ByteArrayInputStream(baos.toByteArray());
        pstmt.setBlob(6, is);

        /**
         * Update data if entry already exists
         */
    }

Aucun commentaire:

Enregistrer un commentaire