Insert HTML content in mysql using jdbc

Viewed 84

I am trying to insert HTML content into my MySQL table. When I execute this query using MySQL Workbench, it is inserting without any error, but when I try to insert it through JDBC code, it is throwing exception:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' table { border-collapse: collapse; } table, td, th { border: 1px s' at line 2"

My insert statement is:

insert into event_template (HTML_ID,CONTENT,ACTIVE,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE)
values('Employee','<div><style> table { border-collapse: collapse; } table, td, th { border: 1px solid black; }
 table.row-table > tbody > tr:not(:last-of-type) > td {
 border-bottom: 3px solid black;
 }</style>
 <span><b><span>Dear User,</span></b></span>
 <span><br></span>
 </div>
 <div>
 <span><br></span>
 </div>
 <div>
 <span>This html span.</span>
 <span><br></span>
 </div>
 <div>
 <span>The is span2 between {fromDate} to {toDate}.&nbsp;</span>
 </div>
 <br>
 <span>This is timestamp {timestamp}.</span>
 <br>&nbsp;
 <br>
 <table width="100%" class="row-table">
 <thead>
 <tr>
 <td>emp #</td>
 <td>emp Name</td>
 <td>emp email</td>
 <td>DOB</td>
 <td>Age</td>
 </tr>
 </thead>
 <tbody>
 <tr>
 <td>{emp}</td>
 <td>{empName}</td>
 <td>{empEmail}</td>
 <td>{empDob}</td>
 <td>{empAge}</td>
 </tr>
 </tbody>
 </table>
 <div>
 &nbsp;
 <br>
 </div>
 <div>
 Thank you, &nbsp;
 </div>
 <b><p>User&nbsp;Support</p></b>
 <br><p></p>',true,'admin',now(),'admin',now());

Code to execute query through JDBC, this runScript() will call execCommand() :

private void runScript(Connection conn, Reader reader) throws IOException, SQLException {
        StringBuffer command = null;
        try {
            LineNumberReader lineReader = new LineNumberReader(reader);
            String line;
            while ((line = lineReader.readLine()) != null) {
                if (command == null) {
                    command = new StringBuffer();
                }
                String trimmedLine = line.trim();
                final Matcher delimMatch = delimP.matcher(trimmedLine);
                if (trimmedLine.length() < 1 || trimmedLine.startsWith("//")) {
                    // Do nothing
                } else if (delimMatch.matches()) {
                    setDelimiter(delimMatch.group(2), false);
                } else if (trimmedLine.startsWith("--")) {
                    println(trimmedLine);
                } else if (trimmedLine.length() < 1 || trimmedLine.startsWith("--")) {
                    // Do nothing
                } else if (!fullLineDelimiter && trimmedLine.endsWith(getDelimiter())
                        || fullLineDelimiter && trimmedLine.equals(getDelimiter())) {
                    command.append(line.substring(0, line.lastIndexOf(getDelimiter())));
                    command.append(" ");
                    this.execCommand(conn, command, lineReader);
                    command = null;
                } else {
                    command.append(line);
                    command.append("\n");
                }
            }
            if (command != null) {
                this.execCommand(conn, command, lineReader);
            }
            if (!autoCommit) {
                conn.commit();
            }
        } catch (Exception e) {
            throw new IOException(String.format("Error executing '%s': %s", command, e.getMessage()), e);
        } finally {
            conn.rollback();
            flush();
        }
    }
private void execCommand(Connection conn, StringBuffer command, LineNumberReader lineReader) throws SQLException {
        Statement statement = conn.createStatement();

        println(command);

        boolean hasResults = false;
        try {
            hasResults = statement.execute(command.toString());
        } catch (SQLException e) {
            final String errText = String.format("Error executing '%s' (line %d): %s", command,
                    lineReader.getLineNumber(), e.getMessage());
            if (stopOnError) {
                throw new SQLException(errText, e);
            } else {
                println(errText);
            }
        }

        if (autoCommit && !conn.getAutoCommit()) {
            conn.commit();
        }

        ResultSet rs = statement.getResultSet();
        if (hasResults && rs != null) {
            ResultSetMetaData md = rs.getMetaData();
            int cols = md.getColumnCount();
            for (int i = 1; i <= cols; i++) {
                String name = md.getColumnLabel(i);
                print(name + "\t");
            }
            println("");
            while (rs.next()) {
                for (int i = 1; i <= cols; i++) {
                    String value = rs.getString(i);
                    print(value + "\t");
                }
                println("");
            }
        }

        try {
            statement.close();
        } catch (Exception e) {
            // Ignore to workaround a bug in Jakarta DBCP
        }
    }

Please guide me to fix it.

0 Answers
Related