一本码簿

Postgres实用技巧

发表于 2017-08-09 更新于 2025-12-31 分类于开发

管理命令

数据库备份

1
2
3

alias pgbackup='thisFun(){pg_dump -U postgres -F t -ovf /var/pgbak/$1_$(date '+%Y%m%d%H%M%S').tar $1};thisFun'
alias pgbak='pg_dump -h localhost -U postgres -F t -ovf /var/pgbak/njqdb.tar njqdb'
pg_dump -h localhost -U postgres -d njqwebdb > /hdc/njqdbbak/njqweb$(date '+%Y%m%d%H%M%S').bak -v

数据库恢复

1
2
3

alias pgrecover='thisFun(){var=${1%%_*};var=${var##*/};echo $var;pg_restore -U postgres -d ${var} -cv $1};thisFun'
alias pgrec='pg_restore -h localhost -U postgres -d njqdb -cv /var/pgbak/njqdb.tar
psql -h localhost -U postgres -d databasename <  C:\databasename.bak

Linux管理

登录数据库 sudo psql -u postgres
导出到表格

COPY (SELECT a.corp_name,a.area_code,a.entity_code,b.addr_type,b.addr_content,b.symbol_code,b.geo_addr FROM geo_entity_info as a left join geo_space_info as b on a.corp_id=b.corp_id) TO '/mnt/d/a.csv' WITH csv HEADER;

开启模块扩展

创建数据库

1 2	create database geoTemplate; \c geoTemplate;

启用空间数据库模块

CREATE EXTENSION postgis;
CREATE EXTENSION pgrouting;
CREATE EXTENSION postgis_topology;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION address_standardizer;
CREATE EXTENSION "uuid-ossp";

管理SQL

创建空间索引

CREATE INDEX name ON table USING gist(column);
CREATE INDEX name ON table USING gin(column);

查询所有数据库大小

1	select pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size from pg_database ORDER BY size DESC

查看全部表大小

1	select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname='public' order by pg_relation_size(relid) desc

查看全部表索引大小

1	select indexrelname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_indexes where schemaname='public' order by pg_relation_size(relid) desc

查看数据库连接情况

1	select * from pg_stat_activity

结束数据库连接

1	SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='njqwebdb' AND pid<>pg_backend_pid()

查询数据库中的表

1	select * from pg_tables WHERE schemaname='public'

语句耗时统计

1	select * FROM pg_stat_statements ORDER BY total_time DESC

索引缓存命中率

SELECT
  sum(idx_blks_read) as idx_read,
  sum(idx_blks_hit)  as idx_hit,
  (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
FROM
  pg_statio_user_indexes;

查看数据库物理大小

1	select pg_size_pretty(pg_database_size('postgres'));

清空带外键约束的表格

TRUNCATE njq_user_basic CASCADE

导入函数创建脚本

1	select proname, pg_get_functiondef(proname::regproc) from pg_proc where proname LIKE 'njq_%';

日常应用

导入数据库

1	shp2pgsql -a -D -s 4326 -W utf-8 point.shp geo_point > result.sql

-s 代表指定数据的SRID为3857
-c 代表数据将新建一个表
-d 删除旧的表，重新建表并插入数据
-a 向现有表中追加数据
-p 仅创建表结构，不添加数据，这四个参数是互斥的
-W Shape文件中属性的字符集，通常Postgresql的字符集是UTF-8，有时候可能Shape数据中的字符集是其他，就可能报“Unable to convert data value to UTF-8 (iconv reports “无效或不完整的多字节字符或宽字符”). Current encoding is “UTF-8”. Try “LATIN1” (Western European)”错误，这时候指定正确的字符集即可解决方问题。

常用查询

跨表查询统计

SELECT xcz_item.code,xcz_region."name","type","count"(xcz_item.code) AS count
FROM xcz_item
LEFT JOIN xcz_region
ON xcz_item.code LIKE '330604%' AND xcz_item.code=xcz_region.code
GROUP BY xcz_item.code,"type",xcz_region."name"
ORDER BY xcz_item.code ,"type"

跨表条件统计

SELECT DISTINCT a.code,a.name,
COALESCE(count(b.*),0) AS total,
sum(CASE WHEN b.state = 4 THEN 1 ELSE 0 END) AS finished,
sum(CASE WHEN b.state <>4 OR b.state IS NULL THEN 1 ELSE 0 END) AS unfinished,
st_astext(st_centroid(a.geom)) AS position
FROM xcz_region a left join xcz_item b
ON a.code=b.code
WHERE a.need=1 AND b.type='1001'
GROUP BY a.code,a.name,a.geom
ORDER BY a.code

图形操作

空间查询

1
2

SELECT count(*) FROM njq_dk WHERE st_intersects(geom,st_geomfromtext('MULTIPOLYGON(((120.92 29.37,120.93 29.37,120.93 29.38,120.92 29.37,120.92 29.37)))',4326))
SELECT count(*) FROM njq_dk WHERE st_intersects(geom,st_geomfromtext('POLYGON((120.92 29.37,120.93 29.37,120.93 29.38,120.92 29.37,120.92 29.37))',4326))

创建空间索引

create extension pg_trgm;
update pg_opclass set opcdefault = true where opcname='gin_trgm_ops';
CREATE index on njq_dk USING gist (geom);
CREATE index on njq_dk USING gin (geom gin_trgm_ops);

外包多边形

1	select ST_AsText(ST_ConvexHull(ST_Collect(geom))) FROM xcz_region

外包矩形

1	select ST_AsText(st_Envelope(ST_Collect(geom))) FROM xcz_region

中心点（面质心）

1	select st_astext(st_centroid(geom)) FROM xcz_region

面上点（和面lable位置一致）

1	select st_astext(st_PointOnSurface(geom)) FROM xcz_region

vuejs

发表于 2017-05-03 更新于 2025-12-31 分类于开发

系统环境

1.安装node.js

2.安装cnpm

1	npm i -g cnpm --registry=https://registry.npm.taobao.org

3.安装vue-cli

1	cnpm i vue-cli

4.下载代码并进入代码目录下进行模块安装

cnpm i

IDEA配置

File->settings->plugins 安装 node 插件和 vue.js 插件，重启IDEA
配置启动项如下图:

结构图

Java常用代码

发表于 2017-04-27 更新于 2025-12-31 分类于开发

获取文件夹指定类型的文件

private File[] getFilterFiles(String path, String[] suffix) {

    File baseFolder = new File(path);
    if(!baseFolder.isDirectory())return null;

    FileFilter imageFilter = pathname -> {
        String fileName = pathname.getName().toLowerCase();
        for (String s : suffix) {
            if (fileName.endsWith(s.toLowerCase())) return true;
        }
        return false;
    };

    return baseFolder.listFiles(imageFilter);
}

二维表转树Json

class itemType;
class itemTypeTree extetends itemType{
    List<itemTypeTree> children;
}

private List<ItemTypeTree> recursionNode(List<ItemType> list, ItemType itemTree) {

    if(list==null)return null;
    if(itemTree==null)itemTree=new ItemTypeTree();
    return  getChildNode(list,itemTree).stream()
            .map(item -> {
                if(getChildNode(list,item).size()>0){
                    item.setChildren(recursionNode(list,item));
                }
               return  item;
            })
            .collect(Collectors.toList());
}
private List<ItemTypeTree> getChildNode(List<ItemType> list,ItemType itemTree){
    return list.stream()
            .filter(item -> {
                if(StringUtils.isBlank(itemTree.getTypecode())){
                    return StringUtils.isBlank(item.getParentcode());
                }else {
                    return item.getParentcode().equals(itemTree.getTypecode());
                }
            })
            .map(itemType -> {
                ItemTypeTree itemTypeTree=new ItemTypeTree();
                BeanUtils.copyProperties(itemType,itemTypeTree);
                return itemTypeTree;
            })
            .collect(Collectors.toList());
}

Git实用技巧

发表于 2017-04-13 更新于 2025-12-31 分类于开发

生成密钥

ssh-keygen -t rsa -C “username@email.com”

添加本地仓库到github

git init
git remote add origin git@github.com:bxxfighting/NowToDo.git
git branch -u origin/master
git pull

忽略已提交文件

先用如下命令: git rm -r --cached filename
再更新.gitignore文件忽略相关文件

创建git使用步骤

生成ssh key： git bash下运行 ssh-keygen -t rsa -C”username”,然后三个回车，密码为空，上传用户文件夹下的公钥 id_rsa.pub 到服务器
配置全局用户名 **git config –global user.name “hai”**、email git config –global user.email “username@email.com“
配置cr、crlf转换 git config –global core.autocrlf input
配置safecrlf转换 git config –global core.safecrlf true
下载代码 git clone git@l.zjchey.com:code/NjqWeb.git
创建并切换分支 git checkout -b dev
建立当前本地分支dev和远程dev分支track git branch -u origin/dev
获取代码 git pull（相当向下箭头），提交代码 git add. git commit -m “message” git push（相当在vcs右键 commit changes）

git分支

创建分支 git branch < branch_name >,切换分支 git checkout < branch_name >,创建并切换分支 git checkout -b < branch_name >
显示本地分支 git branch,显示远程分支 git branch -r,显示本地和远程分支 git branch -a
获取远程分支 git pull origin < branch_name > 或者 git pull
推送到远程分支，git push origin < local_branch_name >:< remote_branch_name >,若本地分支和远程分支tracked，可简写为 git push,若 < local_branch_name > 为空，则删除远程分支
获取远程新分支 git checkout -b < local-branchname > origin/ < remote_branchname>
删除本地分支 git branch -d < branch_name >,删除远程分支 git branch -d -r orign/< branch_name >
合并分支 git merge < branch_name >
建立当前本地分支dev和远程dev分支track git branch -u origin/dev，新建和远程dev分支tracked的本地分支 git branch –track dev origin/dev
查看本地分支和远程分支是否tracked git branch -vv
分支重命名 git branch -m oldbranch newbranch

git撤销

撤销add git checkout – filefullname
撤销commit git reset HEAD filefullname
- git reset –mixed：此为默认方式，不带任何参数的git reset，即时这种方式，它回退到某个版本，只保留源码，回退commit和index信息
- git reset –soft：回退到某个版本，只回退了commit的信息，不会恢复到index file一级。如果还要提交，直接commit即可
- git reset –hard：彻底回退到某个版本，本地的源码也会变为上一个版本的内容

git区图解

git区

git其他命令

创建裸库： git init --bare NjqWeb.git
查看本地设置 git config –list
work和index的比较 git diff，index和repository比较 git diff –staged，work和repository比较 git diff HEAD
git status中文被转义 git config –global core.quotePATH false
master分支仅发布不干活，dev分支仅干活不发布

git多远程仓库

[core]
 repositoryformatversion = 0
 filemode = false
 bare = false
 logallrefupdates = true
 symlinks = false
 ignorecase = true
[remote "origin"]
 url = git@l.zjchey.com:haies.github.io
 url = git@git.oschina.net:haies/blog.git
 url = git@git.coding.net:haies/blog.git
 url = git@code.aliyun.com:haies/blog.git
 fetch = +refs/heads/*:refs/remotes/origin/*
[branch "master"]
 remote = origin
 merge = refs/heads/master

Mybatis实用技巧

发表于 2017-03-23 更新于 2025-12-31 分类于开发

insert返回主键

1
2
3

<insert id="insertSelective" parameterType="com.web.quickwork.entity.UserProject"
  useGeneratedKeys="true" keyProperty="id">
</insert>

<insert id="insertSelective" parameterType="com.zjchey.xxfy.geoCity.entity.Report">
  <selectKey keyProperty="uid" order="BEFORE" resultType="java.lang.Object">
    SELECT uuid_generate_v4()
  </selectKey>
</insert>

<insert id="insertSelective" parameterType="com.web.quickwork.entity.UserProject" >
  <selectKey resultType="long" order="AFTER" keyProperty="id">
    SELECT MAX (ID) FROM USERPROJECT
  </selectKey>
</insert>

Vim实用技巧

发表于 2017-03-18 更新于 2025-12-31 分类于开发

跳转

>跳转到第一行：**gg**
跳转到最后一行：**GG**
跳转到第N行：**Ngg**

搜索

>向后搜索：**: /**
向前搜索：**:?**
下一个：**n**
上一个：**N**

Gulp

发表于 2017-03-17 更新于 2025-12-31 分类于开发

常用插件

npm install gulp-autoprefixer gulp-minify-css gulp-jshint gulp-concat gulp-uglify gulp-imagemin gulp-notify gulp-rename gulp-connect gulp-cache del –save-dev

常用命令

gulp.task(name[, deps], fn) 定义任务 name：任务名称 deps：依赖任务名称 fn：回调函数
**gulp.run(tasks…)**：尽可能多的并行运行多个task
**gulp.watch(glob, fn)**：当glob内容发生改变时，执行fn
**gulp.src(glob)**：置需要处理的文件的路径，可以是多个文件以数组的形式，也可以是正则
**gulp.dest(path[, options])**：设置生成文件的路径

IDEA

发表于 2017-03-16 更新于 2025-12-31 分类于开发

跳转

查找对象方法实现类：Ctrl+ Shift+ H
查看类继承关系：Ctrl + H

设置

Settings–>Editor–>Code Style–>Line Seperator(Unix)
Settings–>Editor–>File Encoding–>Properties Files(UTF-8)