一、添加
(1) 基本插入
<mapper namespace="student">
<insert id="insert" parameterType="com.pojo.po.Student">
INSERT INTO student(name, money) VALUES (#{name}, #{money})
</insert>
</mapper>
public class TestStudent {
@Test
public void testInsert() {
try (SqlSession sqlSession = MyBatisUtil.openSession(true)) {
Student student = new Student();
student.setName("鹿晗");
student.setMoney(100L);
sqlSession.insert("student.insert", student);
}
}
}
注意:
openSession()
的参数默认值是 false(不自动提交事务)
(2) 设置新插入记录的主键(id)★
🌼 设置新插入记录的主键(id)到参数对象中
<mapper namespace="student">
<insert id="insert2" parameterType="com.pojo.po.Student">
INSERT INTO student(name, money) VALUES (#{name}, #{money})
<!-- resultType 需要和参数对象的主键的属性名的类型一样 -->
<!-- keyProperty 是属性名, 不是字段名 -->
<!-- order="AFTER" 插入语句执行完毕后才查询 -->
<selectKey resultType="long" keyProperty="id" order="AFTER">
SELECT LAST_INSERT_ID()
</selectKey>
</insert>
</mapper>
二、更新
<mapper namespace="student">
<update id="update" parameterType="com.pojo.po.Student">
UPDATE student SET money = #{money} WHERE name = #{name}
</update>
</mapper>
三、删除
<mapper namespace="student">
<delete id="delete">
DELETE FROM student WHERE id in ${ids}
</delete>
</mapper>
public class TestStudent {
@Test
public void testDelete() {
try (SqlSession sqlSession = MyBatisUtil.openSession(true)) {
String ids = "(4, 9, 6)";
sqlSession.insert("student.delete", ids);
}
}
}
四、动态 SQL
动态 SQL 官方文档:https://mybatis.org/mybatis-3/zh/dynamic-sql.html
(1) if 标签
<mapper namespace="student">
<select id="dynamicSQL" resultType="com.pojo.po.Student">
SELECT
*
FROM
student
WHERE 1 = 1
<if test="id != null and id > 0">
AND id > #{id}
</if>
<if test="money != null">
AND money > #{money}
</if>
<if test="name != null">
AND NAME LIKE #{name}
</if>
</select>
</mapper>
public class TestStudent {
@Test
public void dynamicSql() {
try (SqlSession sqlSession = MyBatisUtil.openSession(true)) {
HashMap<String, Object> pMap = new HashMap<>();
pMap.put("id", 3);
pMap.put("name", "%杰%");
// pMap.put("money", 5555);
List<Student> list = sqlSession.selectList("student.dynamicSQL", pMap);
for (Student student : list) {
System.out.println("dynamicSql student = " + student);
}
}
}
}
(2) where 标签
<mapper namespace="student">
<select id="dynamicSQL" resultType="com.pojo.po.Student">
SELECT
*
FROM
student
<where>
<if test="id != null and id > 0">
id > #{id}
</if>
<if test="money != null">
AND money > #{money}
</if>
<if test="name != null">
AND NAME LIKE #{name}
</if>
</where>
</select>
</mapper>
(3) foreach 标签
☆ 批量插入:
<mapper namespace="student">
<insert id="batchInsert1" parameterType="List">
INSERT INTO student (name, money) VALUES
<foreach collection="list" item="item" separator=",">
(#{item.name}, #{item.money})
</foreach>
</insert>
</mapper>
💦 批量添加的执行效率比多次单个添加的执行效率要高,但是无法获取到新插入的记录的主键
💦 可以使用 useGeneratedKeys 获取新插入的记录的主键。
💦 假如要添加的记录的字段特别多, 批量添加操作生成的 SQL 语句字符串可能会特别长,SQL 语句的长度可能会超过数据库的限制 【分批插入】
💦 如果传进来的参数是 List
类型,collection 的属性值为 list
就可以遍历这个 List
💦 如果传进来的参数是数组,collection 的属性值为 array
就可以遍历这个数组
☆ 批量删除:
<mapper namespace="student">
<delete id="batchDelete1" parameterType="List">
DELETE FROM student
<where>
id IN (
<foreach collection="list"
separator=","
item="item">
#{item}
</foreach>
)
</where>
</delete>
</mapper>
<mapper namespace="student">
<delete id="batchDelete2">
DELETE FROM student
<where>
id IN
<foreach collection="array"
separator=","
open="("
close=")"
item="item">
#{item}
</foreach>
</where>
</delete>
</mapper>
五、起别名
● <typeAliases>
标签写在 mybatis-config.xml
核心配置文件的configuration 标签中
● 写在 <settings>
标签的后面
● 用于设置类型的别名(不区分大小写)
六、sql 标签
<mapper namespace="student">
<!-- 有抽取公共 SQL 语句的作用 -->
<sql id="sqlListAll">
SELECT * FROM student
</sql>
<resultMap id="resultMapStudent" type="com.pojo.po.Student">
<id property="id" column="id"/>
<result property="createTime" column="create_time"/>
</resultMap>
<select id="list" resultMap="resultMapStudent">
<include refid="sqlListAll"/>
</select>
<select id="getById" resultType="com.pojo.po.Student">
<include refid="sqlListAll"/>
WHERE id = #{id}
</select>
<select id="listByIdAndMoney" resultType="com.pojo.po.Student">
<include refid="sqlListAll"/>
WHERE id < #{id} OR money >= #{money}
</select>
<select id="getByName" resultType="com.pojo.po.Student">
<include refid="sqlListAll"/>
WHERE name LIKE #{name}
</select>
<select id="dynamicSQL" resultType="com.pojo.po.Student">
<include refid="sqlListAll"/>
<where>
<if test="id != null and id > 0">
id > #{id}
</if>
<if test="money != null">
AND money > #{money}
</if>
<if test="name != null">
AND NAME LIKE #{name}
</if>
</where>
</select>
</mapper>
七、在 MyBatis 中集成 druid 连接池
① 引入 Maven 依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.2</version>
</dependency>
② 创建连接池工厂类并继承 PooledDataSourceFactory
或UnpooledDataSourceFactory
;在连接池工厂类的构造方法中设置数据源 dataSource 为 Druid 的数据源实例
/**
* MyBatis 中集成 Druid 数据库连接池
*/
public class DruidDataSourceFactory extends PooledDataSourceFactory {
public DruidDataSourceFactory() {
this.dataSource = new DruidDataSource();
}
}
③ 在 mybatis-config.xml 文件中配置 Druid 数据源
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="com.common.DruidDataSourceFactory">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url"
value="jdbc:mysql://localhost:3306/study_mb?useUnicode=true&characterEncoding=utf8"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
<property name="initialSize" value="5"/>
<property name="maxActive" value="10"/>
<property name="maxWait" value="5000"/>
</dataSource>
</environment>
</environments>
把 druid 数据库连接池的配置放在 druid.properties 文件中
dev.driverClassName=com.mysql.jdbc.Driver
dev.url=jdbc:mysql://localhost:3306/study_mb?useUnicode=true&characterEncoding=utf8
dev.username=root
dev.password=root
dev.initialSize=5
dev.maxActive=10
dev.maxWait=5000
文章来源:https://www.toymoban.com/news/detail-470872.html
文章来源地址https://www.toymoban.com/news/detail-470872.html
<environment id="development">
<!--采用JDBC的事务管理方法-->
<transactionManager type="JDBC"/>
<!--采取druid数据库连接池管理连接-->
<dataSource type="com.common.DruidDataSourceFactory">
<property name="driverClassName" value="${dev.driverClassName}"/>
<property name="url" value="${dev.url}"/>
<property name="username" value="${dev.username}"/>
<property name="password" value="${dev.password}"/>
<property name="initialSize" value="${dev.initialSize}"/>
<property name="maxActive" value="${dev.maxActive}"/>
<property name="maxWait" value="${dev.maxWait}"/>
</dataSource>
</environment>
到了这里,关于【MyBatis】2、MyBatis 的动态 SQL 和增删改操作的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!