gbase8s数据库检测非法字符

在实际项目中，数据库有可能会出现非法字符，其原因有多种，如下提供一种非法字符的检测方法，仅供参考

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;
import java.util.concurrent.Executors;
import java.util.concurrent.LinkedBlockingQueue;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;

public class test {
    private static List<String> tablist= new ArrayList<String>();
    private static String url = "jdbc:gbasedbt-sqli://172.16.39.170:9099/xms:GBASEDBTSERVER=gbase01;DB_LOCALE=zh_CN.utf8;CLIENT_LOCALE=zh_CN.utf8;IFX_LOCK_MODE_WAIT=10;LOGINTIMEOUT=2000;IFX_SOC_TIMEOUT=3000;DELIMIDENT=Y";
    private static String username = "root";
    private static String password = "123456";
    private static String gettab_sql="select tabname from systables where tabid >99 and tabtype='T'";
    static{
        try {
            Class.forName("com.gbasedbt.jdbc.Driver");
        }catch (ClassNotFoundException e){
            System.out.println("class is not found\n");
        }
    }

    public static void main(String[] args) throws Exception {

        Connection connection=getConnection();
        tablist=getTabList(connection);
        System.out.println(tablist.size());
        ThreadPoolExecutor executorService = new ThreadPoolExecutor(
                5, 5, 60L, TimeUnit.SECONDS,
                new LinkedBlockingQueue<>(10),
                Executors.defaultThreadFactory(),
                new ThreadPoolExecutor.AbortPolicy()
        );


        for (String item:tablist) {
            executorService.submit(()->{
                try {
                    checkTab(item);

                }catch (Exception e){
                    e.printStackTrace();
                }
            });

        }

        executorService.shutdown();

    }

    public static synchronized void  checkTab(String tabname) throws SQLException{

            String str= UUID.randomUUID().toString();
            System.out.println("线程"+Thread.currentThread().getName()+str+" 开始");
            System.out.println("处理表名:"+tabname);
            List<String> collist = new ArrayList<String>();
            String sql = "select colname from syscolumns where tabid =(select tabid from systables where tabname='" + tabname +
                    "')" + "and coltype in (13,16,0,15,63,64,40,269,272,256,271,319,320,296);";
            Connection connection = getConnection();
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet resultSet = ps.executeQuery();
            while (resultSet.next()) {
                collist.add(resultSet.getString(1));
            }
            for (String item : collist) {
                String sql_v1 = "select rowid," + item + " from " + tabname + ";";
                ps = connection.prepareStatement(sql_v1);
                resultSet = ps.executeQuery();
                while (resultSet.next()) {
                    try {
                        resultSet.getString(2);
                    } catch (SQLException e) {
                        int rowid = resultSet.getInt(1);
                        System.out.println("发现乱码数据-->表名:"+tabname + ",rowid:" + rowid + ",列名" + item);
                        continue;
                    }

                }

            }
            closeConnection(connection, ps, resultSet);


        System.out.println("线程"+Thread.currentThread().getName()+str+" 结束");

    }

    public static List<String> getTabList(Connection connection) throws SQLException{
        PreparedStatement ps = connection.prepareStatement(gettab_sql);
        ResultSet resultSet=ps.executeQuery();
        while(resultSet.next()){
            tablist.add(resultSet.getString(1));
        }
        closeConnection(connection,ps,resultSet);
        return tablist;
    }

    public static  Connection getConnection(){
        Connection conn =null;
        try {
            conn = DriverManager.getConnection(url, username, password);
        }catch (SQLException e){
            System.out.println("get connect error");
        }
        return conn;
    }

    public static void closeConnection(Connection conn,PreparedStatement preparedStatement,ResultSet resultSet) throws SQLException{
        resultSet.close();
        preparedStatement.close();
        conn.close();
    }
}

运行效果如图

线程pool-1-thread-1f36dfb11-f6e4-4f49-88f3-0bbd865a1d56 开始
处理表名:pca
线程pool-1-thread-1f36dfb11-f6e4-4f49-88f3-0bbd865a1d56 结束
线程pool-1-thread-576a1acb5-380f-4bc2-b4d4-f7d72be76206 开始
处理表名:mm
线程pool-1-thread-576a1acb5-380f-4bc2-b4d4-f7d72be76206 结束
线程pool-1-thread-48d159c0c-f2b0-463f-ad4e-b7a97120c33b 开始
处理表名:vv1
线程pool-1-thread-48d159c0c-f2b0-463f-ad4e-b7a97120c33b 结束
线程pool-1-thread-38e2b0f83-3f2a-45f6-9419-77f705fd23cc 开始
处理表名:vv
线程pool-1-thread-38e2b0f83-3f2a-45f6-9419-77f705fd23cc 结束
线程pool-1-thread-2f5e65d90-16a1-459d-bd72-4df87c6ce201 开始
处理表名:nr_md_alarm_current
线程pool-1-thread-2f5e65d90-16a1-459d-bd72-4df87c6ce201 结束
线程pool-1-thread-363574a97-069a-47d4-bd45-efd76fc80144 开始
处理表名:ac
线程pool-1-thread-363574a97-069a-47d4-bd45-efd76fc80144 结束
线程pool-1-thread-43f35833a-c9e4-44e5-b6d3-bdc56370e2a2 开始
处理表名:cc
线程pool-1-thread-43f35833a-c9e4-44e5-b6d3-bdc56370e2a2 结束
线程pool-1-thread-54d32cf29-4f13-4909-abf0-ff8fbbc9d5b5 开始
处理表名:bb
线程pool-1-thread-54d32cf29-4f13-4909-abf0-ff8fbbc9d5b5 结束
线程pool-1-thread-13da931da-32f3-496b-b9b7-5cdbb70913be 开始
处理表名:kk
发现乱码数据-->表名:kk,rowid:257,列名c1
线程pool-1-thread-13da931da-32f3-496b-b9b7-5cdbb70913be 结束