Select A Unicode mysql row in java?

Question

Peace be upon you :)

Say I have:

   ID            Title
 ----- ---------------------------------------------------------------------
|  1  | Maher Zain -  Peace Be Upon You - ماهر زين - عليك صلى الله (Official) |
 ----- ---------------------------------------------------------------------

Now I need to select this row using java JDBC.

The way I'm doing it right now:

String Title = null;
try {
    DBConnect Database = new DBConnect();
    Connection con = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try{
        con = Database.getcon();
        String query2="SELECT TITLE FROM table WHERE ID=?";
        ps = con.prepareStatement(query2);
        ps.setInt(1, 1);
        rs=ps.executeQuery();
        if(rs.next()){
            Title=rs.getString(1);
        }
    } finally {
        if(ps != null)
            ps.close();
        if(rs != null)
            rs.close();
        if(con != null)
            con.close();    
    }
} catch (SQLException e) {
    e.printStackTrace();
}

Connecting to DB:

public DBConnect(){
    try{
        Class.forName("com.mysql.jdbc.Driver");

        String unicode="useSSL=false&useUnicode=yes&characterEncoding=UTF-8";
        con = DriverManager.getConnection("jdbc:mysql://localhost:15501/db?"+unicode, "root", "pwd");
        st = con.createStatement();
    }catch(Exception ex){
        System.out.println(ex.getMessage());
        System.out.println("couldn't connect!");
    }
}

This works But the output that I get is: ???? ??? ?????? ?????? - ???? ??????? - Maher Zain | Mustafa Ceceli - Bika Moulh

So I need to set the query to use Unicode character set.

I would really Appreciate if you tell me how <3

@YCF_L Sorry for my late reply, Anway, I can see the table row to be as `Maher Zain - Peace Be Upon You - ماهر زين - عليك صلى الله (Official)` which is the way i want it to be — Ahmad Nabi, Jun 18 '17 at 15:47

score 0 · Answer 1 · answered Jun 18 '17 at 15:55

0

First of all, you should follow Java naming conventions. Variables and methods should be named in camelCase.

There is no need to create two nested try blocks. You can declare all of these variables outside. Thus resulting in this:

DBConnect database = new DBConnect();
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
String title = null;

try {
// database stuff here
} 
//catch and finally statements next

This would also allow you to reuse these variables later on.

In order to add Unicode "support" into your database, you should add this variable after the database location when you use your getConnection() method.

String unicode = "?useUnicode=yes&characterEncoding=UTF-8";

answered Jun 18 '17 at 15:55

Nothing Nothing

126
1
8

You should try and use `ALTER DATABASE` and `ALTER TABLE`. Use `ALTER DATABASE CHARACTER SET utf8 COLLATE utf8_general_ci;` and then do ˙ALTER TABLE CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;`. This will change the table's and database's characteristics and enable support for future Unicode, while converting existing to Unicode. – Nothing Nothing Jun 18 '17 at 16:49
@AhmadNabi There is this link to another StackOwerflow question addressing this problem: [link](https://stackoverflow.com/questions/6115612/how-to-convert-an-entire-mysql-database-characterset-and-collation-to-utf-8) and the official links can be found [here](https://dev.mysql.com/doc/refman/5.7/en/alter-database.html) and [here](https://dev.mysql.com/doc/refman/5.7/en/alter-table.html) – Nothing Nothing Jun 18 '17 at 16:56
Try entering into `my.cfg˙ and tell me if the `default-character-set` is equal to `utf8` – Nothing Nothing Jun 18 '17 at 17:57
if you mean `my.ini` which i got the location from `services -> MYSQL -> properties -> Path to Executable` then yes it's as `default-character-set=utf8` – Ahmad Nabi Jun 18 '17 at 18:26
Yes. It seems that it's `my.cfg` or `my.ini` depending on the OS used. In that case, did you try creating a new table but with the proper settings now (the included Unicode support)? It might just be that the table itself doesn't support Unicode yet. – Nothing Nothing Jun 18 '17 at 18:29
But the Title Row I can see is to be saved as unicode, as I showed in my question – Ahmad Nabi Jun 18 '17 at 18:34
So in order to figure out the character of my table I did this `select c.character_set_name from information_schema.tables as t, information_schema.collation_character_set_applicability as c where c.collation_name = t.table_collation and t.table_schema = "db" and t.table_name = "table";` which the result is `utf8mb4`, is that okay? – Ahmad Nabi Jun 18 '17 at 18:41

Select A Unicode mysql row in java?

1 Answers1