NutzCN Logo
问答 dao查询mysql JSON字段出现乱码
发布于 2579天前 作者 qq_8c799e25 2059 次浏览 复制 上一个帖子 下一个帖子
标签:

我定义了一个bean:

package com.hans.stock.ban.bean;

import java.util.List;

import org.nutz.dao.entity.annotation.ColDefine;
import org.nutz.dao.entity.annotation.ColType;
import org.nutz.dao.entity.annotation.Column;
import org.nutz.dao.entity.annotation.PK;
import org.nutz.dao.entity.annotation.Table;

@Table("t_test_obj")
@PK(value = { "zqdm"})
public class TestObj {
    @Column
    private String zqdm; //
    
    @Column
    private String zqmc; //
    
    @Column
    @ColDefine(type=ColType.MYSQL_JSON)
    private List<String> reasons; //原因
    
    public TestObj(){
    	
    }

	public String getZqdm() {
		return zqdm;
	}

	public void setZqdm(String zqdm) {
		this.zqdm = zqdm;
	}

	public List<String> getReasons() {
		return reasons;
	}

	public void setReasons(List<String> reasons) {
		this.reasons = reasons;
	}

	public String getZqmc() {
		return zqmc;
	}

	public void setZqmc(String zqmc) {
		this.zqmc = zqmc;
	}
    
    
}

然后采用Daos.createTablesInPackage(dao, "com.xxx.bean", false);这种方式自动建表
最后在方法中,插入到数据库(write方法),之后再查询出来(read方法),但是发现查询后的reasons 字段里面都是乱码

	private void write(){
		TestObj t = new TestObj();
		t.setZqdm("002038");
		t.setZqmc("双鹭药业");
		List<String> reasons = new ArrayList<String>();
		reasons.add("日跌幅偏离值达到7%的前五只证券");
		reasons.add("日换手率达到20%的前五只证券");
		t.setReasons(reasons);
		
		dao.insert(t);
		

	}

private void read(){
		List<TestObj> list = dao.query(TestObj.class, Cnd.where("zqdm", "=", "002038"));
		if(list!=null && list.size()>0){
			TestObj t2 = list.get(0);
			System.out.println(t2.getZqmc()); //这是对的,没有乱码
			for(String r: t2.getReasons()){
				System.out.println(r); //这是错的,乱了
			}
		}

我的数据库里面确实是正常的,中文都是正常,说明写入正常,这是bug吗?
用的nutz版本1.r.60

9 回复

我是单机程序,eclipse.ini设置了 -Dfile.encoding=UTF-8,
数据库也是utf-8:
CREATE TABLE t_test_obj (
zqdm varchar(128) NOT NULL,
zqmc varchar(128) DEFAULT NULL,
reasons json DEFAULT NULL,
PRIMARY KEY (zqdm)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

但是还是出现乱码:

2017-03-28 17:44:56,516 INFO  1 - testFunc arg1:arg1, arg2:arg2
双鹭药业
日跌幅偏离值达到7%的前五只证券
日换手率达到20%的前五只证券

注意看,前一个中文(zqmc :双鹭药业)是正常的,后面的reasons的都是乱码了。
这环境我一直在用,各种字段中文都是正常,但是第一回在json字段中使用中文就出问题了。

额, jdbcURL发一下

jdbc我没指定utf8,但是数据库和表都是utf8,我明早再看看

来自炫酷的 NutzCN

我用你代码试了一下, 没有乱码哦


DEBUG - JDBC Driver --> mysql-connector-java-5.1.40 ( Revision: 402933ef52cad9aa82624e80acbea46e3a701ce6 ) DEBUG - JDBC Name --> MySQL Connector Java DEBUG - JDBC URL --> jdbc:mysql://127.0.0.1:3306/nutztest?useUnicode=true&characterEncoding=UTF-8 DEBUG - Mysql : character_set_client=utf8 DEBUG - Mysql : character_set_connection=utf8 DEBUG - Mysql : character_set_database=utf8 DEBUG - Mysql : character_set_filesystem=binary DEBUG - Mysql : character_set_results= DEBUG - Mysql : character_set_server=utf8 DEBUG - Mysql : character_set_system=utf8 DEBUG - Mysql : character_sets_dir=C:\Program Files\MySQL\MySQL Server 5.7\share\charsets\ DEBUG - Mysql : binlog_format=ROW DEBUG - Mysql : database=nutztest DEBUG - Mysql : user=root@localhost

这问题还奇怪了:

我的数据库:

mysql> show variables like "char%";
+--------------------------+--------------------------------------------+
| Variable_name            | Value                                      |
+--------------------------+--------------------------------------------+
| character_set_client     | utf8                                       |
| character_set_connection | utf8                                       |
| character_set_database   | utf8                                       |
| character_set_filesystem | binary                                     |
| character_set_results    | utf8                                       |
| character_set_server     | utf8                                       |
| character_set_system     | utf8                                       |
| character_sets_dir       | D:\Run\mysql-5.7.11-winx64\share\charsets\ |
+--------------------------+--------------------------------------------+
8 rows in set, 1 warning (0.00 sec)

mysql>

jdbc:
jdbc:mysql://127.0.0.1:3306/stockmarket?useUnicode=true&characterEncoding=UTF-8

	private synchronized void test(){
		TestObj t = new TestObj();
		t.setZqdm("002038");
		t.setZqmc("双鹭药业");
		List<String> reasons = new ArrayList<String>();
		reasons.add("日跌幅偏离值达到7%的前五只证券");
		reasons.add("日换手率达到20%的前五只证券");
		t.setReasons(reasons);
		
		TestObj tt = dao.fetch(t);
		if(tt==null){
			dao.insert(t);
		}else{
			dao.update(t);
		}
		
		System.out.println(t.getZqmc());
		for(String r: t.getReasons()){
			System.out.println(r);
		}
		
		
		List<TestObj> list = dao.query(TestObj.class, Cnd.where("zqdm", "=", "002038"));
		if(list!=null && list.size()>0){
			TestObj t2 = list.get(0);
			System.out.println(t2.getZqmc());
			for(String r: t2.getReasons()){
				System.out.println(r);
			}
		}
	}

输出结果:

双鹭药业
日跌幅偏离值达到7%的前五只证券
日换手率达到20%的前五只证券
双鹭药业
日跌幅偏离值达到7%的前五只证券
日换手率达到20%的前五只证券

我知道了,换成你用的mysql-connector-java-5.1.40 就正常,我用的是mysql-connector-java-5.1.38

查证了一下

https://dev.mysql.com/doc/relnotes/connector-j/5.1/en/news-5-1-40.html

ResultSet.getString() sometimes returned garbled data for columns of the JSON data type. This was because JSON data was binary encoded by MySQL using the utf8mb4 character set, but decoded by Connector/J using the ISO-8859-1 character set. This patch fixes the decoding for JSON data. Thanks to Dong Song Ling for contributing to the fix. (Bug #22891845, Bug #80631)
添加回复
请先登陆
回到顶部