欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

jdcb查询SQL Server数据乱码问题解决方法

程序员文章站 2024-01-08 22:15:34
...
用jdcb查询SQL Server数据源数据时,在window环境下数据没有任何异常,但到linux环境下,英文和数字没有任何问题,但中文出现乱码,搜索SQLServer jdbc乱码原因,没有得到想要的答案,于是,从SQLServer驱动的实现开始,查找没有想Mysql配置url是,编码属性配置:
//SQLServerDriver
public Connection connect(String s, Properties properties)
        throws SQLServerException
    {
        try
        {
            Logger.setLogWriter(this, DriverManager.getLogWriter());
        }
        catch(NoSuchMethodError nosuchmethoderror)
        {
            Util.println("This version of sqlserver jdbc driver needs the JVM to support level 1.4 or higher");
            Util.println("The current JVM level is:" + Util.sJDKVersion);
            try
            {
                Logger.setLogStream(this, DriverManager.getLogStream());
            }
            catch(Exception exception) { }
        }
        SQLServerConnection sqlserverconnection = null;
	//解析URL
        Properties properties1 = Util.parseUrl(s);
        if(properties1 == null)
            return null;
        Object obj = null;
        Object obj1 = null;
        int i = DriverManager.getLoginTimeout();
        if(i > 0)
            properties1.put("loginTimeout", (new Integer(i)).toString());
        for(int j = 0; j < driverProperties.length; j++)
        {
            String s1 = driverProperties[j];
            if(properties1.getProperty(s1) != null)
                continue;
            String s2 = properties.getProperty(s1);
            if(s2 != null)
                properties1.put(s1, s2);
        }

        connProperties = properties1;
        String s3 = properties1.getProperty("logfile");
        if(s3 != null)
            Logger.setCustomLogFile(this, s3);
        sqlserverconnection = new SQLServerConnection();
        sqlserverconnection.sConnectURL = s;
        try
        {
	    //获取连接
            sqlserverconnection.connect(properties1, null);
        }
        catch(SQLException sqlexception)
        {
            throw new SQLServerException(sqlserverconnection, "Failed Logon:" + sqlexception.toString() + " url:" + s, sqlexception.getSQLState(), sqlexception.getErrorCode(), false);
        }
        return sqlserverconnection;
    }


先来看解析url:
Properties properties1 = Util.parseUrl(s);


//Util
public static Properties parseUrl(String s)
   {
       Properties properties = new Properties();
       String s1 = s;
       Object obj = null;
       Object obj1 = null;
       String s3 = "jdbc:sqlserver://";
       if(!s1.startsWith(s3))
           return null;
       s1 = s1.substring(s3.length());
       try
       {
           StringTokenizer stringtokenizer = new StringTokenizer(s1, ":/&?;", true);
           String s2 = stringtokenizer.nextToken();
           properties.put("serverName", s2);
           String s4 = stringtokenizer.nextToken();
           if(s4.equals(":"))
           {
               String s7 = stringtokenizer.nextToken();
               properties.put("portNumber", s7);
               String s5 = stringtokenizer.nextToken();
           }
           int i = 0;
           do
           {
               if(!stringtokenizer.hasMoreTokens())
                   break;
               String s6 = stringtokenizer.nextToken();
               if(++i % 2 != 0)
               {
                   StringTokenizer stringtokenizer1 = new StringTokenizer(s6, "=", true);
                   String s8 = stringtokenizer1.nextToken();
                   if(stringtokenizer1.hasMoreTokens())
                   {
                       stringtokenizer1.nextToken();
                       if(stringtokenizer1.hasMoreTokens())
                       {
                           String s9 = stringtokenizer1.nextToken();
                           properties.put(s8, s9);
                       }
                   } else
                   {
                       properties.put("databaseName", s8);
                   }
               }
           } while(true);
       }
       catch(NoSuchElementException nosuchelementexception) { }
       return properties;
}

没有找到字符编码相关的属性配置

再来看获取连接:
//SQLServerConnection
public Connection connect(Properties properties, BasePooledConnection basepooledconnection)
        throws SQLServerException
    {
        int k;
        ConnectionProperties connectionproperties;
        int l;
        int i1;
        int i2;
        activeConnectionProperties = properties;
        pooledConnectionParent = basepooledconnection;
        conPropertiesArray = new ConnectionProperties[10];
        for(int i = 0; i < 10; i++)
            conPropertiesArray[i] = new ConnectionProperties();
        String s = null;
        String s1 = null;
	//获取数据库连接属性配置
        s = properties.getProperty("enableFailover");
        boolean flag = s != null && s.equals("true");
        s = "user";
        s1 = properties.getProperty(s);
        if(s1 == null)
            s1 = "";
        buildConnectionProperties(s, s1, flag);
        s = "password";
        s1 = properties.getProperty(s);
        if(s1 == null)
            s1 = "";
        buildConnectionProperties(s, s1, flag);
        s = "databaseName";
        s1 = properties.getProperty(s);
        if(s1 == null)
            s1 = properties.getProperty("database");
        buildConnectionProperties(s, s1, flag);
        String s2 = s1;
        s = "serverName";
        s1 = properties.getProperty(s);
        if(s1 == null)
            s1 = properties.getProperty("host");
        if(s1 == null)
            s1 = "localhost";
        String s3 = s1;
        int j = s1.indexOf('\\');
        String s4 = null;
        if(j >= 0)
        {
            String s5 = s1.substring(j + 1, s1.length());
            s1 = s1.substring(0, j);
            s4 = getInstancePort(s1, s5);
        } else
        {
            String s6 = properties.getProperty("instanceName");
            if(s6 != null)
                s4 = getInstancePort(s1, s6);
        }
        buildConnectionProperties(s, s1, flag);
        s = "portNumber";
        s1 = properties.getProperty(s);
        if(s1 == null)
            s1 = properties.getProperty("port");
        if(s1 == null || s1.equals("0"))
            if(s4 == null)
                s1 = "1433";
            else
                s1 = s4;
        buildConnectionProperties(s, s1, flag);
        s = "sqlVersion";
        s1 = properties.getProperty(s);
        if(s1 == null)
            s1 = properties.getProperty("sqlversion");
        buildConnectionProperties(s, s1, flag);
        s = "asciiStringParameters";
        s1 = properties.getProperty(s);
        buildConnectionProperties(s, s1, flag);
        s = "booleanLiterals";
        s1 = properties.getProperty(s);
        buildConnectionProperties(s, s1, flag);
        s = "applicationName";
        s1 = properties.getProperty(s);
        buildConnectionProperties(s, s1, flag);
        s = "instanceName";
        s1 = properties.getProperty(s);
        buildConnectionProperties(s, s1, flag);
        s = "lastUpdateCount";
        s1 = properties.getProperty(s);
        buildConnectionProperties(s, s1, flag);
        s = "disableStatementPooling";
        s1 = properties.getProperty(s);
        buildConnectionProperties(s, s1, flag);
        s = "trustedAuthentication";
        s1 = properties.getProperty(s);
        buildConnectionProperties(s, s1, flag);
        s = "codepage";
        s1 = properties.getProperty(s);
        buildConnectionProperties(s, s1, flag);
        s = "loginTimeout";
        s1 = properties.getProperty(s);
        buildConnectionProperties(s, s1, flag);
        s = "lockTimeout";
        s1 = properties.getProperty(s);
        buildConnectionProperties(s, s1, flag);
        s = "xopenStates";
        s1 = properties.getProperty(s);
        buildConnectionProperties(s, s1, flag);
        s = "enableFailover";
        s1 = properties.getProperty(s);
        buildConnectionProperties(s, s1, flag);
        s = "selectMethod";
        s1 = properties.getProperty(s);
        if(s1 == null || !s1.equals("cursor"))
            s1 = "default";
        buildConnectionProperties(s, s1, flag);
        s = "preExecuteMetaData";
        s1 = properties.getProperty(s);
        buildConnectionProperties(s, s1, flag);
        s = "iterativeBatching";
        s1 = properties.getProperty(s);
        buildConnectionProperties(s, s1, flag);
        s = "connectionRetryCount";
        s1 = properties.getProperty(s);
        buildConnectionProperties(s, s1, flag);
        s = "connectionRetryWait";
        s1 = properties.getProperty(s);
        buildConnectionProperties(s, s1, flag);
        s = "trustedAuthenticationPort";
        s1 = properties.getProperty(s);
        buildConnectionProperties(s, s1, flag);
        s = "ntlmAuthentication";
        s1 = properties.getProperty(s);
        buildConnectionProperties(s, s1, flag);
        s = "domain";
        s1 = properties.getProperty(s);
	...
}

仍然没有找到对应的字符编码相关属性:

那会是什么原因呢?

一想,在Mysql jdbc相关的文章中我们看到Mysql jdbc连接与客户端时通过socket的连接来通信的,那么MS SqlServer思想肯定也是一样,那么socket的接收byte数据要转化为String,那肯定要使用
如下方法:
//String
public String(byte bytes[], Charset charset) {
    this(bytes, 0, bytes.length, charset);
}

如果没有配置编码,则使用如方法:
public String(byte bytes[]) {
    this(bytes, 0, bytes.length);
}

public String(byte bytes[], int offset, int length) {
    checkBounds(bytes, offset, length);
    //委托给StringCoding解码
    this.value = StringCoding.decode(bytes, offset, length);
}


//StringCoding
static char[] decode(byte[] ba, int off, int len) {
    //关键在这句获取系统默认字符编码
    String csn = Charset.defaultCharset().name();
    try {
        // use charset name decode() variant which provides caching.
        return decode(csn, ba, off, len);
    } catch (UnsupportedEncodingException x) {
        warnUnsupportedCharset(csn);
    }
    try {
        return decode("ISO-8859-1", ba, off, len);
    } catch (UnsupportedEncodingException x) {
        // If this code is hit during VM initialization, MessageUtils is
        // the only way we will be able to get any kind of error message.
        MessageUtils.err("ISO-8859-1 charset not available: "
                         + x.toString());
        // If we can not find ISO-8859-1 (a required encoding) then things
        // are seriously wrong with the installation.
        System.exit(1);
        return null;
    }
}
public static Charset defaultCharset() {
    if (defaultCharset == null) {
        synchronized (Charset.class) {
	    //在当前线程访问控制权限下,获取系统文件编码
            String csn = AccessController.doPrivileged(
                new GetPropertyAction("file.encoding"));
            Charset cs = lookup(csn);
            if (cs != null)
                defaultCharset = cs;
            else
	       //如果系统编码为空,则默认为UTF-8
                defaultCharset = forName("UTF-8");
        }
    }
    return defaultCharset;
}


猜想是不是系统编码导致的呢?linux系统的编码为UTF-8
donald@linux:~/tomcat/bin>locale
...
LC_ALL=zh_CN.UTF-8
LANG=zh_CN.UTF-8  


修改系统编码为GBK,修改/etc/profile,添加如下两行:
export LC_ALL=zh_CN.G
export LANG=zh_CN.GBK


donald@linux:~/tomcat/bin>source /etc/profile
donald@linux:~/tomcat/bin> tail -n 20 /etc/profile
...
export LC_ALL=zh_CN.GBK  
export LANG=zh_CN.GBK  
donald@linux:~/tomcat/bin> 


重新启动应用,测试,it is ok, feel good...

如果不是这个原因可以尝试从结果结果集中先获取数据,再将数据转换为GBK编码格式:

String oname=rs.getString("name");
byte[] namebyte=oname.getBytes("GBK2312");
name=new String(namebyte);


这种方法理论上没有任何问题,我没有测试,如果上面方法不行,可以尝试用这种方法。
相关标签: sql server

上一篇:

下一篇: