mysql中insert注解的返回值,MyBatis学习笔记

MyBatis 是一款优秀的持久层框架，它支持自定义 SQL、存储过程以及高级映射。MyBatis 免除了几乎所有的 JDBC 代码以及设置参数和获取结果集的工作。

一、第一个mybatis程序

1.1 导入依赖

org.mybatis

mybatis

3.4.6

mysql

mysql-connector-java

5.1.47

src/main/resources

**/*.properties

**/*.xml

false

src/main/java

**/*.properties

**/*.xml

false

1.2 创建数据库配置文件(db.properties)

driver=com.mysql.jdbc.Driver

url=jdbc:mysql://localhost:3306/mybatis_lzf?useUnicode=true&characterEncoding=utf-8

username=root

password=123456

1.3 创建核心配置文件(mybatis-config)

/p>

PUBLIC "-//mybatis.org//DTD Config 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-config.dtd">

1.4 创建实体类(对应数据库中的表)

package com.lzf.entity;

public class User {

private int id;

private String username;

private String password;

public User() {

}

public User(int id, String username, String password) {

this.id = id;

this.username = username;

this.password = password;

}

@Override

public String toString() {

return "User{" +

"id=" + id +

", username='" + username + '\'' +

", password='" + password + '\'' +

'}';

}

public int getId() {

return id;

}

public void setId(int id) {

this.id = id;

}

public String getUsername() {

return username;

}

public void setUsername(String username) {

this.username = username;

}

public String getPassword() {

return password;

}

public void setPassword(String password) {

this.password = password;

}

}

1.5 编写dao接口(UserDao)

public interface UserDao {

User queryUserById(Integer id);

}

1.6 编写对应的daoMapper (userDaoMapper.xml)并注册

SELECT * from t_user WHERE id = #{arg0};

在mybatis的核心配置文件(mybatis-config)中注册mapper

/p>

PUBLIC "-//mybatis.org//DTD Config 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-config.dtd">

1.7 编写测试类

public class TestMybatis {

public static void main(String[] args) throws IOException {

//1.加载配置文件

InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");

//2.构建SqlSessionFactory

SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

//3.创建SqlSession

SqlSession sqlSession = sessionFactory.openSession();

//4.通过sqlSession获得dao实现类对象

UserDao mapper = sqlSession.getMapper(UserDao.class);

//5.测试查询方法

User user = mapper.queryUserById(1);

System.out.println(user);

}

}

1.8 类型别名

在mybatis核心配置文件中加入以下代码

1.9 整体项目结构图

1.10 Mybatis工具类

package com.lzf.util;

import org.apache.ibatis.io.Resources;

import org.apache.ibatis.session.SqlSession;

import org.apache.ibatis.session.SqlSessionFactory;

import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;

import java.io.InputStream;

/**

* 1.加载配置文件

* 2.构建SqlSessionFactory

* 3.创建SqlSession

* 4.事务的管理

* 5.获取mapper

*/

public class MyBatisUtil {

private static SqlSessionFactory sqlSessionFactory;

private static final ThreadLocal THREAD_LOCAL = new ThreadLocal();

static {

//1.加载配置文件

try {

InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");

//2.构建SqlSessionFactory

sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

} catch (IOException e) {

e.printStackTrace();

}

}

//打开sqlSession

public static SqlSession openSession(){

SqlSession sqlSession = THREAD_LOCAL.get();

if(sqlSession==null){

sqlSession = sqlSessionFactory.openSession();

THREAD_LOCAL.set(sqlSession);

}

return sqlSession;

}

//事务提交

public static void commit(){

openSession().commit();

closeSession();

}

//事务回滚

public static void rollback(){

openSession().rollback();

closeSession();

}

//资源释放

public static void closeSession(){

THREAD_LOCAL.get().close();

THREAD_LOCAL.remove();

}

//获取mapper

public static T getMapper(Class mapper){

return openSession().getMapper(mapper);

}

}

二、CRUD

2.1 查询(参数绑定)

序号参数绑定

public interface UserDao {

User queryUserById(Integer id);

}

SELECT * from t_user WHERE id = #{arg0};

SELECT * from t_user WHERE id = #{param1};

注解参数绑定【推荐】

public interface UserDao {

User selectUserByIdAndPwd(@Param("id") Integer id, @Param("pwd") String pwd);

}

select * from t_user WHERE id = #{id} AND password = #{pwd};

Map参数绑定

public interface UserDao {

User selectUserByIdAndPwd_map(Map values);

}

Map values = new HashMap();

values.put("id",1);

values.put("pwd","456");

User user = mapper.selectUserByIdAndPwd_map(values);

select * from t_user WHERE id=#{id} and password = #{pwd};

对象参数绑定

public interface UserDao {

User selectUserByUserInfo(User user);

}

User u = new User(1,"lzf","456");

User user = mapper.selectUserByUserInfo(u);

System.out.println(user);

select * from t_user WHERE id = #{id} and password = #{password};

concat

字符串拼接函数，可用于模糊查询

where name like concat(‘%’，#{keyword},’%’);

拼接了%

2.2 删除

public interface UserDao {

Integer deleteUserById(@Param("id") int id);

}

DELETE FROM t_user WHERE id = #{id}

2.3 添加

public interface UserDao {

Integer addUser(User user);

}

insert into t_user values (NULL,#{username},#{password});

2.4 修改

public interface UserDao {

Integer updataUser(User user);

}

update t_user set username = #{username},password = #{password} where id = #{id};

2.5 注意点

1. 增删改要控制事务

sqlSession.commit();

sqlSession.rollback();

2. 增删改有返回值

2.6 插入中的主键回填

主键为int 且自动增长

select last_insert_id()

insert into t_user values (#{id},#{username},#{password});

主键为string 不主动增长

SELECT replace(uuid(),'-','');

insert into t_student values (#{id},#{name},#{password});

三、ORM映射

Mybatis自动ORM失效(数据库列名和 JAVA中对象属性名不能一一对应)时，解决方案如下：

4.1 列的别名

select id,username,password,regist_time AS registTime from t_user WHERE id = #{id} AND password = #{pwd};

4.2 结果映射(resultMap)

select * from t_user WHERE id = #{id} AND password = #{pwd};

4.3 resultMap处理多表连接ORM映射

持有对象关系属性

持有集合关系属性

四、动态SQL

4.1 sql标签

抽取重复的sql语句，实现复用

select id,username,password from t_user;

WHERE id = #{id};

4.2 if标签

//根据id查询时给id正确赋值，username为null

//根据username查时username正确赋值,id为null

User queryUser(User user);

SELECT * from t_user

WHERE

id = #{id}

username = #{username}

4.3 where标签(忽略前缀)

自动忽略 前缀 如：or | and

SELECT * from t_user

id = #{id}

or username = #{username}

4.4 set标签(忽略后缀)

自动忽略后缀如： ,

Integer updateUser(User user);

//对修改做约定：需要需改的属性就赋值，不需要修改的属性就为null

update t_user

username=#{username},

password=#{password},

gender=#{gender},

regist_time=#{registTime}

where id=#{id}

4.5 trim标签(代替where和set)

username=#{username}

and gender=#{gender}

username=#{username},

password=#{password},

gender=#{gender},

regist_time=#{registTime}

4.6 foreach标签

批量操作,遍历集合

批量删除

Integer deleteManyUser(List ids);

delete from t_user where id in

#{id9}

参数

描述

取值

collection

容器类型

list、array、map

open

起始符

(

close

结束符

)

separator

分隔符

,

index

下标号

从0开始，依次递增

item

当前项

任意名称(循环中通过#{任意名称}表达式进行访问)

批量增加

Integer insertManyUser(List users);

insert into t_user values

(null,#{user9.username},#{user9.password},#{user9.gender},#{user9.registTime})

五、缓存(Cache)

5.1 一级缓存

SqlSession级别的缓存，同一个SqlSession的发起多次同构查询，会将数据保存在一级缓存中。

注意：无需任何配置，默认开启一级缓存

5.2 二级缓存

SqlSessionFactory级别的缓存，同一个SqlSessionFactory构建的SqlSession发起的多次同构查询，会将数据保存在二级缓存中

注意：在SqlSession.commit()或者SqlSession.close()之后生效

对缓存的数据进行增 删 改操作，相关的缓存会被移除

六、连接池(druid)

6.1 导入依赖

com.alibaba

druid

1.1.16

6.2 创建DruidDataSourceFactory

MyDruidDataSourceFactory继承PooledDataSourceFactory，并替换数据源

package com.lzf.datasource;

import com.alibaba.druid.pool.DruidDataSource;

import org.apache.ibatis.datasource.pooled.PooledDataSourceFactory;

public class MyDruidDataSourceFactory extends PooledDataSourceFactory{

public MyDruidDataSourceFactory() {

this.dataSource = new DruidDataSource();//替换数据源

}

}

6.3 修改mybatis配置文件

注意property中name的值，与com.alibaba.druid.pool.DruidAbstractDataSource中一致

七、PageHelper

PageHelper是适用于Mabatis框架的一个分页框架

7.1 引入依赖

com.github.pagehelper

pagehelper

5.1.10

7.2 配置 mybatis-config.xml

/p>

PUBLIC "-//mybatis.org//DTD Config 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-config.dtd">

PageInfofenter保存分页查询结果

@Testls

public void testPageInfo(){

UserDao userDao = MyBatisUti.getMapper记录(UserDao.class);

PageHelper.start(6,2);//第六页 每页2条记录。这条语句之后的第一个查询会执行分页

List users = UserDao.selectAllUsers();

PageInfo pageInfo = new PageInfo(users);//将分页查询的结果集保存在PageInfo对象中

System.out.println(pageInfo);

}

PageInfofenter类相关属性

private int pageNum;//当前页

private int pageSize;//每页的数量

private int size;//当前页的数量

private int startRow;//当前页面第一个元素在数据库中行号

private int endRow;//当前页面最后一个元素在数据库中的行号

private int pages;//总页数

private int prePage;//上一页

private int nextPage;//下一页

private boolean isFirstPage;//是否为第一页

private boolean isLastPage;//是否为最后一页

private boolean hasPreviousPage;//是否有前一页

private boolean hasNextPage;//是否有下一页

private int navigatePages;//导航页码数

private int[] navigatepageNums;//所有导航页码数

private int navigateFirstPage;//导航条上的第一页

private int navigateLastPage;//导航条上的最后一页

protected List list;//数据在父类的list中

八、 注解开发【了解】

8.1 增加

@Options(useGeneratedKeys = true , keyProperty = "id") // 自增key，主键为id

@Insert("insert into t_user values(#{id},#{username},#{password},#{gender},#{registTime})")

Integer insertUser(User user);

8.2 删除

@Delete("delete from t_user where id=#{id}")

Integer deleteUser(@Param("id") Integer id);

8.3 修改

@Update("update t_user set username=#{username},password=#{password},gender=#{gender},regist_time=#{registTime} where id=#{id}")

Integer updateUser(User user);

8.4 查询

@Select("SELECT id,username,password,gender,regist_time FROM t_user")

List queryUsers();