[jeeplus] 使用笔记 (gridSelect、datetimepicker etc.)

java.sql.SQLException: Connection is read-only. Queries leading to data modification are not allowed

解决方法：事务配置在 service 层，但是你的 service 层的方法是 query 开头的，service 调用的 query 方法根本就没有事务。你的配置文件下面是不是还有关于 query*的配置成了 read-only？修改你 service 方法的开头变成不是 read-only 的那种配置，如 clear 开头。（在 query 方法名(read_only)里面执行了 update 操作，所以就报错了。）

    //删除转化出现异常时已经插入的数据记录
    @Transactional(readOnly = false)
    public void deleteERecord(LevelForMajors levelForMajors){
        mapper.deleteERecord(levelForMajors);
    }

慎用 select *

当自己手写一些 sql 时，如果查找的对象/表中有封装了其他对象，那么，当 sql 中有 select * 时会导致封装对象为 Null 的现象。
因此，如果有会使用到封装的对象，那么 xml 中的 sql 还是加上映射关系吧。
eg:

    <!-- 根据年份、省份、高校、文理科、批次查找记录 findListByYPNWB -->
    <select id="findListByYPNWB" resultType="Eucode" >
        SELECT
        <include refid="EucodeColumns"/>
        FROM e_uecode a
        <include refid="EucodeJoins"/>
        WHERE a.year = #{year} AND a.province = #{province.id} AND a.universityid = #{university.id} AND a.wltype = #{wltype} AND a.schoolbatches = #{schoolBatches} AND (a.r3 != "2" OR  ISNULL(a.r3)) AND (a.r3 != "3"  OR  ISNULL(a.r3))
    </select>

Mysql 中对一个表中的字段进行汇总计算后 赋值给 其中一个字段

如：招生计划表中，按照年份、省份、批次、文理科、学校 分组汇总后，将各专业招生计划数汇总后，赋值给该校该年度该批次该省份该文理科中的 招生计划总数。

查询语句：

SELECT
	COUNT(zhaoshengjihua) AS jihuazongshu,
	zhaoshengjihuazongshu,
	shengfen,
	nianfen,
	wenlike,
	pici,
	daxuedaima,
	daxuemingcheng
FROM
	d_zhaoshengjihuabiao
GROUP BY
	shengfen,
	nianfen,
	wenlike,
	pici,
	daxuedaima;

*zhaoshengjihuazongshu 字段中已经被赋值了，原来该字段中的值为空

最开始写出的 sql 如下，但是完全不正确。

UPDATE d_zhaoshengjihuabiao a
SET a.zhaoshengjihuazongshu = (
	SELECT
		COUNT(b.zhaoshengjihua) AS b.cou,
		b.shengfen,
		b.nianfen,
		b.wenlike,
		b.pici,
		b.daxuedaima
	FROM
		d_zhaoshengjihuabiao b
	GROUP BY
		b.shengfen,
		b.nianfen,
		b.wenlike,
		b.pici,
		b.daxuedaima
)
WHERE
	b.shengfen = a.shengfen
AND b.nianfen = a.nianfen
AND b.wenlike = a.wenlike
AND b.pici = a.pici
AND b.daxuedaima = a.daxuedaima

在网上搜索后，看到如下 2 条 sql 做为参考：

-- 1
UPDATE au_window_re_project aw
SET aw.project_name = (
	SELECT
		ap.SERVICENAME
	FROM
		apas_service ap
	WHERE
		ap.unid = aw.PROJECT_ID
)

-- 2
UPDATE test_user_points AS a
LEFT JOIN (
	SELECT
		user_id,
		sum(point) AS point_count
	FROM
		test_point_log
	GROUP BY
		user_id
) AS b ON a.user_id = b.user_id
SET a.point_count = b.point_count

最终参考第 2 条 SQL 写出如下语句，可以实现要求：

UPDATE d_zhaoshengjihuabiao AS a
LEFT JOIN (
	SELECT
		COUNT(zhaoshengjihua) AS cou,
		shengfen,
		nianfen,
		wenlike,
		pici,
		daxuedaima
	FROM
		d_zhaoshengjihuabiao
	GROUP BY
		shengfen,
		nianfen,
		wenlike,
		pici,
		daxuedaima
) AS b ON (
	a.shengfen = b.shengfen
	AND a.wenlike = b.wenlike
	AND a.nianfen = b.nianfen
	AND a.pici = b.pici
	AND a.daxuedaima = b.daxuedaima
)
SET a.zhaoshengjihuazongshu = b.cou ;