建表语句完全可以用可视化的navicat等,建好,然后直接导出sql
更新语句,更新两个字段
@Update("update bank_file_info set save_type = #{saveType},format_type_id=#{formatTypeId} where id = #{fileId}") void updateSaveType(@Param("fileId") Integer fileId, @Param("saveType") Integer saveType, @Param("formatTypeId") Integer formatTypeId);
批量插入一个对象集合:
@Insert({ "<script>", "insert into bank_file_info(p_id, rate, folder_name, source_name, file_code, size, format, file_name, source_url,new_source_url,file_type,upload_time,is_border,is_turn) values ", "<foreach collection='list' item='item' index='index' separator=','>", "(#{item.pId}, #{item.rate}, #{item.folderName}, #{item.sourceName}, #{item.fileCode}, #{item.size}, #{item.format}, #{item.fileName}, #{item.sourceUrl}," + "#{item.newSourceUrl},#{item.fileType},#{item.uploadTime},#{item.isBorder},#{item.isTurn})", "</foreach>", "</script>" }) @Options(useGeneratedKeys = true, keyProperty = "id") void insertFileList(@Param(value = "list") List<BankFileInfo> fileInfoList);
数据库新增字段,这里要注意版本问题,有的版本是这种写法default(0),后边是注释
ALTER TABLE bank_file_info add save_type TINYINT(1) DEFAULT 0 COMMENT '保存状态 0:版式与表头均未存储1:存储版式2:存储表头信息';
删除语句:
@Delete("delete from bank_table_head where file_id = #{fileId} ") void delData(@Param("fileId") Integer fileID);
查询语句:
@Select("select * from bank_file_info where rate = '2' and format = 'pdf' and del_flag = 0 and progress = 0 limit 1") BankFileInfo selectPdfFile();
创建索引:create index 索引名 on 表名(列名)注释
CREATE INDEX PersonIndex ON Person (LastName) COMMENT('持续时间索引')
union all 允许重复的 limit0,10的意思是# 查询10条数据,索引从0到9,第1条记录到第10条记录
@Select("select * from bank_file_info where is_border = 2 and rate = 1 and del_flag = '0' and format != 'pdf' and file_name = '1' " + "union all select * from bank_file_info a where a.is_border = 2 and a.rate = 1 and a.del_flag = '0' AND a.format != 'pdf' " + "and file_code in (select file_code from bank_file_info b where del_flag = '0' and(rate BETWEEN 4 and 8) and format != 'pdf' ) limit 0,10") List<BankFileInfo> getNoBorderBankFile();
实际开发中,难免遇到往数据库插入的情况:
Insert into finance_project_other (id,type,name,msg) VALUES(372,'11','xxx','xxxx');
左连接:这个业务场景就是同一张表但存的type不一样,要根据不同的类型取出id对应相等的
SELECT
l.qr_code,
l.page_no,
l.f_name,
l2.f_name,
l2.page_no,
l2.qr_code,
l2.company
FROM
letter_img_info l
LEFT JOIN letter_img_info l2 ON (l.qr_code = l2.qr_code)
AND (l.file_type = '0')
AND (l2.file_type = '1')
AND l2.id IS NOT NULL
AND (l.qr_code=xxxx)
AND (l2.qr_code=xxxx)
实际效果:
这个是业务的一个实际场景
SELECT
s.qr_code,
s.f_name,
s.page_no,
s2.qr_code,
s2.f_name,
s2.page_no,
s2.company
FROM
(
SELECT
*
FROM
letter_img_info
WHERE
file_type = '0'
AND del_flag = '0'
AND p_id=4
GROUP BY
qr_code
) AS s
LEFT JOIN (
SELECT
*
FROM
letter_img_info
WHERE
file_type = '1'
AND del_flag = '0'
AND p_id=4
) AS s2 ON (s.qr_code = s2.qr_code)
WHERE s.qr_code='15'
AND s2.qr_code is NOT NULL;
批量修改:
UPDATE letter_img_info SET qr_code = CASE <foreach collection='list' item='item' index='index'> WHEN page_no = #{item.pageNo} THEN #{item.QrCode} </foreach> END, rate = CASE <foreach collection='list' item='item' index='index'> WHEN page_no = #{item.pageNo} THEN #{item.rate} </foreach> END WHERE f_id = #{fId} AND del_flag = '0'