character_set_client character_set_connection character_set_results

之前一直纠结各种编码的却别：character_set_client character_set_connection character_set_results 还有数据库编码，表编码，字段编码

现在总结下：首先客户端发送query到服务器要通过character_set_client这个编码来编写query来发送到服务器，然后通过 character_set_connection这个编码来连接服务器。这个地方可能有些难理解，其实因为不同的编码对于校对是有区别的：举个极端的例子（只是举例使用）：譬如对于gbk的编码1<2是正确的，但是对于utf8编码就是错误的（当然这是不正确的，只是举例）， character_set_results这个的作用就是根据query查询数据库返回的数据的编码，可能数据库编码是gbk而results是utf8,那么数据库要将gbk的数据转化成utf8当然，可能转换可能会丢失那些不属于两种字符集的字符，数据库的编码作用就是存入数据库的数据的编码，当然前提是表和字段都是采取默认编码，如果表指定了编码那么就会采取表的编码，如果字段制定了编码，那么就会采用字段编码。

从上面可以看出character_set_results的返回结果与client与connection无绝对关系，这个结果主要受服务器返回的结果，然后根据结果的编码转换成character_set_results的编码

 

举例：

 

package com.service;

 

import java.math.BigDecimal;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.Date;

 

public class MysqConnection {

private Connection conn = null;

//连接信息

public void getConnection() {

 try {

 //2005连接

  Class.forName("org.gjt.mm.mysql.Driver").newInstance();

  String URL = "jdbc:mysql://10.8.210.108:3306/test_character?useUnicode=true&amp;characterEncoding=utf8&amp;zeroDateTimeBehavior=convertToNull";

  String USER = "root"; // 根据你自己设置的数据库连接用户进行设置 

  String PASSWORD = "1q2w3e"; // 根据你自己设置的数据库连接密码进行设置 

  conn = DriverManager.getConnection(URL, USER, PASSWORD);

}catch(Exception e){

e.printStackTrace();

}

}

 

public String getData() throws SQLException {

 StringBuilder sb = new StringBuilder();

 if (conn == null)

  this.getConnection();

 try {

  String sql = "insert into test3 values('3','孙强','孙强')";

  Statement stmt = conn.createStatement();

   stmt.executeUpdate(sql);

  int flag = 0;

  

  ResultSet rs = stmt.executeQuery("SELECT * FROM test1") ;   

  while (rs.next()) {

  //组装

//   if(flag == 0){

//   sb = new StringBuilder("insert into crm_base_data(contract_no, city,crm_code,goods_name,shop_price," +

//   "orign_price,supplier_id,supplier_name,payee_name,payee_account,payee_bank,payee_sub_bank,payee_province,payee_city," +

//   "payee_email,payee_mobile,jiesuan_type,operation_fee,55_foregift,logistics_foregift,service_fee,secure_funds,is_has_operation_fee,is_pre_collect_operation_fee" +

//   ")values");

//   }

//   sb = sb.append("(").append(isNullString(rs.getString("contract_no"))).append(",").append(isNullString(rs.getString("city"))).append(",").append(isNullString(rs.getString("crm_code"))).append(",").append(isNullString(rs.getString("goods_name"))).

//   append(",").append(isNullDecimal(rs.getString("shop_price"))).append(",").append(isNullDecimal(rs.getString("orign_price"))).append(",").append(isNullString(rs.getString("supplier_id"))).

//   append(",").append(isNullString(rs.getString("supplier_name"))).append(",").append(isNullString(rs.getString("payee_name"))).append(",").append(isNullString(rs.getString("payee_account"))).

//   append(",").append(isNullString(rs.getString("payee_bank"))).append(",").append(isNullString(rs.getString("payee_sub_bank"))).append(",").append(isNullString(rs.getString("payee_province"))).

//   append(",").append(isNullString(rs.getString("payee_city"))).append(",").append(isNullString(rs.getString("payee_email"))).append(",").append(isNullString(rs.getString("payee_mobile"))).

//   append(",").append((rs.getString("jiesuan_type")==null||rs.getString("jiesuan_type").length()<1)?"1":rs.getString("jiesuan_type")).append(",").append(isNullDecimal(rs.getString("operation_fee"))).append(",").

//   append(isNullDecimal(rs.getString("55_foregift"))).append(",").append(isNullDecimal(rs.getString("logistics_foregift"))).append(",").append(isNullDecimal(rs.getString("service_fee"))).append(",").

//   append(isNullDecimal(rs.getString("secure_funds"))).append(",").append((rs.getString("is_has_operation_fee")==null||rs.getString("is_has_operation_fee").length()<1)?"1":rs.getString("is_has_operation_fee")).append(",").

//   append((rs.getString("is_pre_collect_operation_fee")==null||rs.getString("is_pre_collect_operation_fee").length()<1)?"1":rs.getString("is_pre_collect_operation_fee")).append("),");

  sb = sb.append(rs.getString("cityId")).append(rs.getString("CityName")).append(rs.getString("CityType"));

  }

  System.out.println(sb.toString());

  rs.close();

  stmt.close();

 } 

 finally {

  if (conn != null)

   try {

    conn.close();

   } catch (SQLException e) {

   }

 }

 if(sb!=null&&sb.length()>0){

 //变成 insert into ...values(),(),...();

return  sb.substring(0, sb.length()-1)+";";

 }

 return null;

}

//字符串的如果为null或者或者空取值空字符串，否则是本身值

public String isNullString(String str){

if(str == null||str.length()<1){

return "''";

}

else

return "'"+str+"'";

}

//小数的如果为null或者空取值为0，否则是本身值

public BigDecimal isNullDecimal(String str){

if(str == null||str.length()<1){

return new BigDecimal(0);

}else{

return new BigDecimal(str);

}

}

//小数的如果为null或者空取值为0，否则是本身值

public Integer isNullInteger(String str){

if(str == null||str.length()<1){

return new Integer(0);

}else{

return Integer.valueOf(str);

}

}

public long getTimeStampNow(){

Date date = new Date();

return date.getTime();

}

public static void main(String[] args) {

MysqConnection bean = new MysqConnection();

try {

bean.getData();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

// StringBuilder sb = new StringBuilder("hello");

// System.out.println(sb.subSequence(0, sb.length()-1)+";");

// System.out.println(new BigDecimal("0.21"));

}

}

利用jdbc连接数据库gbk连接

表：CREATE TABLE `test3` (

  `cityId` varchar(255) NOT NULL DEFAULT '',

  `CityName` varchar(255) CHARACTER SET gbk DEFAULT NULL ,

  `CityType` varchar(255)CHARACTER SET utf8  DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=gbk ;

插入之后再命令行运行