1

I have following program which insert emoji and any text to my MySql AWS Database. I was unable to add Emojis in my MySql database, but then i fixed this problem by changing collation and adding this-> SET NAMES utf8mb4; query before my previous insert query but now i am unable to get last inserted id from it. what should i do in order to insert emoji as well as to get last inserted id from it.

Here is my code.

public static JSONObject emoji(String comment) {
        JSONObject json = new JSONObject();
        Connection con = null;
        PreparedStatement stmt = null;
        String newInsertId = "";
        try {
            BasicDataSource bds = DBConnection.getInstance().getBds();
            con = bds.getConnection();
            String query = "SET NAMES utf8mb4; insert into emojis set message = '" + comment + "';";
            stmt = con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
            if (stmt.executeUpdate() > 0) {
                json.put("success", 1);
            }
            ResultSet rs = stmt.getGeneratedKeys();
            if (rs.next()) {
                newInsertId = rs.getString(1); //giving empty values cause of that SET NAMES utf8mb4; query
            }
            System.out.println(newInsertId); //empty
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
        try {
            DbUtils.close(con);
            DbUtils.close(stmt);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
        return json;
    }
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • Are you sure that you can execute multiple queries at once? You may need to add the connection property `allowMultiQueries=true` to the database string. – Mehmed May 29 '19 at 08:59
  • @Mehmed i have already added that to my jdbc url.-> "jdbc:mysql://dev-test-db.cgi56jm0yif4veej5.ap-north-1.rds.amazonaws.com/testdb?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&allowMultiQueries=true"; –  May 29 '19 at 09:07
  • 1
    You shouldn't execute things like `set names` from a JDBC driver. You should handle that through driver specific settings. See also https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-charsets.html – Mark Rotteveel May 29 '19 at 11:48
  • Are you using the latest version of MySQL Connector/J (currently version 8.0.16)? – Gord Thompson Jun 05 '19 at 12:28

1 Answers1

-1
static int create() throws SQLException { 
    Connection conn = null; 
    PreparedStatement ps = null; 
    ResultSet rs = null; 
    try { 
        // 2.建立连接 
        conn = JdbcUtils.getConnection(); 
        // conn = JdbcUtilsSing.getInstance().getConnection(); 
        // 3.创建语句 
        String sql = "insert into user(name,birthday, money) values ('name2 gk', '1987-01-01', 400) "; 
        ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);//参数2最好写上，虽然Mysql不写也能获取但是不代表别的数据库可以做到 
        ps.executeUpdate();

        rs = ps.getGeneratedKeys(); 
        int id = 0; 
        if (rs.next()) 
            id = rs.getInt(1); 
        return id; 
    } finally { 
        JdbcUtils.free(rs, ps, conn); 
    } 
}

——————————————————————————————————————————————————————

Focus on this 'Statement.RETURN_GENERATED_KEYS'
stevetn lee
  • 3