数据准备
在iDesktop 9D(2019)产品中,新建了一个sql server型的数据源,为了测试新建了一个面数据集,其中有两个面对象。
并在sql server数据库中在同一个数据库内新建一个表,字段分别为text类型的name和int类型的id,也插入两条记录,其中name和iDesktop中新建的面数据的name一致,id和SmID一致。
实现代码
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title data-i18n="resources.title_queryBySQL"></title>
<style type="text/css">
body {
margin: 0;
overflow: hidden;
background: #fff;
width: 100%;
height: 100%
}
#map {
position: absolute;
width: 100%;
height: 100%;
border: 1px solid #3473b7;
}
#toolbar {
position: absolute;
top: 50px;
right: 10px;
text-align: center;
z-index: 100;
border-radius: 4px;
}
</style>
</head>
<body>
<div id="toolbar" class="panel panel-primary">
<div class='panel-heading'>
<h5 class='panel-title text-center' data-i18n="resources.title_queryBySQL"></h5></div>
<div class='panel-body content'>
<input type="button" class="btn btn-default" data-i18n="[value]resources.text_query" onclick="queryBySQL()"/>
<input type="button" class="btn btn-default" data-i18n="[value]resources.text_input_value_clear" onclick="clearFeatures()"/>
</div>
</div>
<div id="map"></div>
<script type="text/javascript" include="bootstrap,widgets.alert" src="../js/include-web.js"></script>
<script type="text/javascript" exclude="iclient-classic" src="../../dist/classic/include-classic.js"></script>
<script>
var map, local, layer, vectorLayer,
style = {
strokeColor: "#304DBE",
strokeWidth: 1,
fillColor: "#304DBE",
fillOpacity: "0.8"
},
host = window.isLocal ? window.server : "http://support.supermap.com.cn:8090",
url = "http://localhost:10008/iserver/services/map-test/rest/maps/test";
init();
function init() {
map = new SuperMap.Map("map", {
controls: [
new SuperMap.Control.ScaleLine(),
new SuperMap.Control.Zoom(),
new SuperMap.Control.Navigation({
dragPanOptions: {
enableKinetic: true
}
})]
});
map.addControl(new SuperMap.Control.LayerSwitcher(), new SuperMap.Pixel(42, 80));
layer = new SuperMap.Layer.TiledDynamicRESTLayer("World", url, {
transparent: true,
cacheEnabled: true
}, {maxResolution: "auto"});
layer.events.on({"layerInitialized": addLayer});
vectorLayer = new SuperMap.Layer.Vector("Vector Layer");
}
function addLayer() {
map.addLayers([layer, vectorLayer]);
map.setCenter(new SuperMap.LonLat(110.3, 50), 0);
}
function queryBySQL() {
vectorLayer.removeAllFeatures();
var joinItem = new SuperMap.REST.JoinItem({
foreignTableName: "ceshi1",
joinFilter: "ceshi1.name = test_1.name",
joinType: "LEFTJOIN"
})
关联数据库中的表和iDesktop新建的数据的表。
需要注意的是字段需要完全一致,一开始我自己新建的数据库中的表的name字段是TEXT类型的,在做关联的时候失败了,发现iDesktop新建的表的name字段是varchar(255)类型的。
var queryParam, queryBySQLParams, queryBySQLService;
queryParam = new SuperMap.REST.FilterParameter({
name: "ceshi@ceshi",
数据集@数据源
attributeFilter: "ceshi1.name = 'yh' ",
查询条件设置为数据库中的记录,查询成果的话结果通过console.log打印
joinItems: [joinItem]
});
queryBySQLParams = new SuperMap.REST.QueryBySQLParameters({
queryParams: [queryParam]
});
queryBySQLService = new SuperMap.REST.QueryBySQLService(url, {
eventListeners: {"processCompleted": processCompleted, "processFailed": processFailed}
});
queryBySQLService.processAsync(queryBySQLParams);
}
function processCompleted(queryEventArgs) {
var i, j, feature,
result = queryEventArgs.result;
console.log(result);
打印结果
initialize {totalCount: 1, currentCount: 1, customResponse: null, recordsets: Array(1)}
}
function processFailed(e) {
widgets.alert.showAlert(e.error.errorMsg, false);
}
function clearFeatures() {
//先清除上次的显示结果
vectorLayer.removeAllFeatures();
vectorLayer.refresh();
}
</script>
</body>
</html>
joinitem接口可以参考iClient Joinitem接口链接.
结果
数据库中表的设计比较简单
桌面软件的数据