java快捷修改数据库所有表编码

2019独角兽企业重金招聘Python工程师标准>>>

1. 需要获取数据库连接工具类 (DB类我就不提供了,以前文章有)

2.执行相对应方法

• 查询库中所有表名 

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA =dbName

• 显示表编码

show create table tableName 

• 修改变编码

alter table tableName convert to character set tableCode

查看代码

package softmanage.actionTest;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import softmanage.util.DBC;

public class UpdTableUTF8Bat{
	//获取数据库链接
	static DBC conn ;
	//编码格式
	static String tableCode = "utf8";
	//数据库名
	static String dbName = "XXXXXX";
	/**
	 * <p class="detail">
	 * 功能：获取所有表名
	 * </p>
	 * @author damowang
	 * @return 
	 * @throws
	 */
	public static List getAllTable() {
		String sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA ="+dbName+"";
		ResultSet rs = conn.executeQuery(sql);
		List tempList = new ArrayList<String>();
		
		try {
			while(rs.next()){
				tempList.add(rs.getString("TABLE_NAME"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return tempList;
	}
	/**
	 * <p class="detail">
	 * 功能：判断是是否Lation编码
	 * </p>
	 * @author damowang
	 * @param tableName
	 * @return 
	 * @throws
	 */
	public static boolean isLation(String tableName) {
		String sql = "show create table "+tableName+"";
		ResultSet rs = conn.executeQuery(sql);
		String collation =null;
		boolean flag =false;
		try {
			while(rs.next()){
				collation = rs.getString("create table");
				if(collation.indexOf("CHARSET=latin1")>0){
					// 发现lation1编码
					flag =true;
					System.out.println(tableName+": 是lation1编码;");
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}
	/**
	 * <p class="detail">
	 * 功能：修改表编码
	 * </p>
	 * @author damowang
	 * @param tableName 
	 * @throws
	 */
	public static void updateLation(String tableName) {
		String sql = "alter table "+tableName+" convert to character set "+tableCode;
		int rs = conn.executeUpdate(sql);
		System.out.println(tableName+"修改编码成功");
	}
	
	public static void main(String[] args) {
		conn = new DBC();
		List tables = getAllTable();
		String tableName;
		for (int i = 0; i < tables.size(); i++) {
			tableName  = (String) tables.get(i);
			boolean flag = isLation(tableName);
			if(flag){
				// 是lation编码
				updateLation(tableName);
			}
		}
	}
}

 

转载于:https://my.oschina.net/java1314/blog/815705