CODEWITHSUNDEEP
javasqlmysqljdbcchar
molleman
molleman

Reputation: 2946

How do you input Java char's into MYSQL database and then also retrieve them as well?

I am using a MYSQL database to store persisted data for a java application.

CREATE TABLE testtable1( key1a CHAR, key1b CHAR, encoded1a CHAR, encoded1b CHAR);

As you can see i create a table that stores for each row a 4 different CHAR's.

the sql i have written for inputing the chars into the table is 

 char k1[] = new char[2]
 char p[] = new char[2];

 k1[0]=0x00aa;
 k1[1]=(char)0xaaaa;

 p[0]=0x0001;
 p[1]=0x0002;

sql = "INSERT INTO testtable1 "
                +"(key1a, key1b , encoded1a,encoded1b) "
                + "VALUES "
                + "('"+ k1[0] + "',"
                + "'"+ k1[1] + "',"
                + "'"+ p[0] + "',"
                + "'"+ p[1] + "')";

The above statement i dont think work. it inpts the data as such alt text

And then into the database like this alt text

So as you can see, it is been inputed with a ?(whatis this?) and then the final two columns are not being populated.

So could any of you guys point me in the right direction to where my thinking is wrong when it comes to storing char variable.

And also , how would i retrieve the chars from the database table? as i cannot find any Doc's on this???

thank you very much

Upvotes: 0

Views: 3508

Answers (2)

Powerlord
Powerlord

Reputation: 88796

In addition to the PreparedStatement answer:

Java's char is a 16-bit UTF-16 value.

MySQL's CHAR is a synonym for CHAR(1)... which is just a single character wide, (number of bytes depends on the table's character set).

Now, PreparedStatement's setString should take care of the encoding conversion for you, but that still leaves one problem: MySQL on some systems defaults to the latin1 character set. Which means CHAR is too small.

There is a fix for this. During table creation, you can force a table to be a specific character set:

CREATE TABLE testtable1( key1a CHAR, key1b CHAR, encoded1a CHAR, encoded1b CHAR) CHARACTER SET utf8;

Note: You can also use utf16 instead of utf8; I just used utf8 for storage size reasons.

Upvotes: 2

Buhake Sindi
Buhake Sindi

Reputation: 89169

I would suggest looking into PreparedStatement.

An example would be as follows:

PreparedStatement ps = nulll;
try {
    ps = connection.prepareStatement("INSERT INTO testtable1 (key1a, key1b , encoded1a,encoded1b) VALUES (? ,? ,?, ?)");
    for (int i = 1; i <= k.length; i++) {
        ps.setString(i, "" + k[i]);
    }

    ps.executeUpdate();
} catch (SQLException) {
    //Log exception
} finally {
    if (ps != null) {
        try {
            ps.close();
            ps = null;
        } catch (SQLException e) {}
    }
}

The problem here is that if char[]k's length is greater than 4, an exception will be thrown.

Upvotes: 3

Related Questions