JFinal多数据源同时连接Mysql和Impala

悠悠 2022-07-13 13:14 222阅读 0赞

使用Jfinal同时连接Mysql和Impala，连接mysql比较容易，Jfinal的官方文档提供的就有例子，连接Impala的话，其实和连接mysql差不多。

public static final String CONST.DB_MYSQL="mysql";
    public static final String CONST.DB_IMPALA="impala";
    
    @Override
    public void configPlugin(Plugins me)
    {
    	try
    	{
    			//设置mysql数据源
    			Properties p = loadConfigs("db_mysql.properties");
    			String db_url = p.getProperty("db_url");
    			String db_name = p.getProperty("db_name");
    			String db_pwe = p.getProperty("db_pwd");
    			
    			C3p0Plugin dsMysql = new C3p0Plugin(db_url, db_name, db_pwe, "com.mysql.jdbc.Driver");
    			me.add(dsMysql);
    			
    			ActiveRecordPlugin arp = new ActiveRecordPlugin(CONST.DB_MYSQL, dsMysql);
    			arp.setShowSql(true);
    			arp.setDialect(new MysqlDialect());//设置mysql方言
    			me.add(arp);
    			//这里是批量读取表和bean对应关系，然后加入到Mapping中
    			p = loadConfigs("mapping_mysql.properties");
    			Iterator<?> it = p.keySet().iterator();
    			while (it.hasNext())
    			{
    				String key = it.next().toString();
    				String[] value = p.get(key).toString().split(";");
    				String classPath = CONST.MODEL_PATH + value[0];
    				@SuppressWarnings("unchecked")
    				Class<? extends Model<?>> cls = (Class<? extends Model<?>>) Class.forName(classPath);
    				if (value.length > 1)
    				{
    					arp.addMapping(key, value[1], cls);
    				}
    				else
    				{
    					arp.addMapping(key, cls);
    				}
    			}
    			
    			//设置Impala数据源
    			Properties p1 = loadConfigs("db_impala.properties");
    			String url = p1.getProperty("url");
    			String username = p1.getProperty("username");
    			String password = p1.getProperty("password");
    			
    			C3p0Plugin dsImpala = new C3p0Plugin(url, username, password, "org.apache.hive.jdbc.HiveDriver");
    			me.add(dsImpala);
    			
    			ActiveRecordPlugin arp1 = new ActiveRecordPlugin(CONST.DB_IMPALA, dsImpala);
    			arp1.setShowSql(true);
    			me.add(arp1);
    		}
    		catch (Exception e)
    		{
    		}
    }

可以看到，Impala并没有设置表对应的bean到Mapping的操作，这点我尝试了，但是行不通。

在对表进行操作的时候，主要说一下impala的操作，代码如下：

public void showImpala()
    {
    	ResultJson rjJson = new ResultJson();
    	rjJson.setCode(CONST.SUCCESS);
    	rjJson.setDescription("获取成功~");
    	StringBuffer sql = new StringBuffer();
    	sql.append("select substr(cpu_count,11) cpu_count,")
    				.append("substr(operation_system_version,26) operation_system_version,")
    				.append("substr(total_power,13) total_power,")
    				.append("substr(cpu_spec_info,15) cpu_spec_info,")
    				.append("substr(manufacturer_name,19) manufacturer_name,")
    				.append("substr(inlet_temp,12) inlet_temp,")
    				.append("round((cast(substr(cputemp1,10) as int)+cast(substr(cputemp2,10) as int)")
    				.append("+cast(substr(cputemp3,10) as int)+cast(substr(cputemp4,10) as int))/cast(substr(")
    				.append("cpu_count,11) as int),0) cpu_temp,")
    				.append("substr(firmware_revision,19) firmware_revision,")
    				.append("substr(power_status,14) power_status ")
    				.append("from clog.device_kpi_meta_data where substr(commcrtdate,13)>'20170105181010'");
    		//下面是查询
    		rjJson.setResult(Db.use(CONST.DB_IMPALA).find(sql.toString()));
    		renderJson(rjJson);
    }

可以看到，其实使用起来和mysql没多大区别。

但是，如果想执行诸如增加分区、refresh table这样的非常规sql语句的话，就需要使用如下方式：

Db.use(CONST.DB_IMPALA).execute(new ICallback() {
    		@Override
    		public Object call(Connection conn) throws SQLException
    		{
    				Statement stmt = conn.createStatement();
    				String sql = "alter table clog.device_kpi_meta_data add partition(day='" + DateUtil.getTomorrow() + "')";
    				stmt.execute("refresh clog.device_kpi_meta_data");
    				stmt.execute(sql);
    				return null;
    			}
    });

##     综上： ##

1.在进行sql操作的时候，表名前面要带对应的用户名。

2.Impala中诸如新增分区、刷新表这些操作，要使用execute方法进行单独处理。