1.Mybaits
1.1 Mybatis简介:
MyBatis 是一款优秀的持久层框架,它支持定制化 SQL、存储过程以及高级映射。MyBatis 避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集。MyBatis 可以使用简单的 XML 或注解来配置和映射原生信息,将接口和 Java 的 POJOs(Plain Ordinary Java Object,普通的 Java对象)映射成数据库中的记录。
1.2 Mybatis优点 :
-
简单易学:本身就很小且简单。没有任何第三方依赖,最简单安装只要两个jar文件+配置几个sql映射文件。易于学习,易于使用。通过文档和源代码,可以比较完全的掌握它的设计思路和实现。
-
灵活:mybatis不会对应用程序或者数据库的现有设计强加任何影响。 sql写在xml里,便于统一管理和优化。通过sql语句可以满足操作数据库的所有需求。
-
解除sql与程序代码的耦合:通过提供DAO层,将业务逻辑和数据访问逻辑分离,使系统的设计更清晰,更易维护,更易单元测试。sql和代码的分离,提高了可维护性。
-
提供映射标签,支持对象与数据库的orm字段关系映射。
-
提供对象关系映射标签,支持对象关系组建维护。
-
提供xml标签,支持编写动态sql。
1.3 Mybatis流程 :
加载配置并初始化--->接收调用请求--->处理操作请求--->返回处理结果将最终的处理结果返回
2.Mybatis与Mysql间的增删改查
2.1 Mybatis基本配置
2.1.1引入基本依赖
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>org.example</groupId> <artifactId>SSM</artifactId> <version>1.0-SNAPSHOT</version> <packaging>jar</packaging> <dependencies> <!-- Mybatis核心 --> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.5.10</version> </dependency> <!-- MySQL驱动 --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.11</version> </dependency> <!-- junit测试 --> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.13.1</version> <scope>test</scope> </dependency> </dependencies> <properties> <maven.compiler.source>8</maven.compiler.source> <maven.compiler.target>8</maven.compiler.target> </properties> </project>
2.1.2核心配置
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <properties resource="jdbc.properties"></properties> <!--类别名设置 typealiases: 属性 1.type:别名 2.alias:别名类型 pakage 属性 name --> <typeAliases> <package name="day1.pojo"/> </typeAliases> <environments default="development"> <environment id="development"> <!--transactionManager:设置事务管理方式 属性:type="JDBC|MANAGED" JDBC:表示当前环境中,执行SQL时,使用的是JDBC中原生的事务管理方式,事 务的提交或回滚需要手动处理MANAGED:被管理 --> <transactionManager type="JDBC"/> <!--dataSource:配置数据源 属性:type:设置数据源的类型 type="POOLED|UNPOOLED|JNDI" POOLED:表示使用数据库连接池缓存数据库连接 UNPOOLED:表示不使用数据库连接池 JNDI:表示使用上下文中的数据源 --> <dataSource type="POOLED"> <property name="driver" value="${drive}"/> <property name="url" value="${url}"/> <property name="username" value="${username}"/> <property name="password" value="${password}"/> </dataSource> </environment> </environments> <mappers> <!--<mapper resource="day1.mapper"/>--> <!--以包为单位引入映射文件 要求: 1、mapper接口所在的包要和映射文件所在的包一致 2、mapper接口要和映射文件的名字一致 --> <package name="day1/mapper"/> </mappers> </configuration>
2.1.3 数据库properties配置
drive=com.mysql.cj.jdbc.Driver url=jdbc:mysql://localhost:3306/ssm?useSSL=false&&serverTimezone=UTC username=用户名 password=密码
2.2 Mybaits与Mysql间的操作
2.2.1创建所需要的表
create table user( id int not null primary key auto_increment, username varchar(20), pwd varchar(30), email varchar(30), age int, gender varchar(1) )engine=innodb default charset=utf-8;
2.2.2创建User对象
package day1.pojo; public class User { private Integer id; private String username; private String pwd; private String email; private Integer age; private String gender; public User(Integer id, String username, String pwd, String email, Integer age, String gender) { this.id = id; this.username = username; this.pwd = pwd; this.email = email; this.age = age; this.gender = gender; } public User() { } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getPwd() { return pwd; } public void setPwd(String pwd) { this.pwd = pwd; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public Integer getAge() { return age; } public void setAge(Integer age) { this.age = age; } public String getGender() { return gender; } public void setGender(String gender) { this.gender = gender; } @Override public String toString() { return "User{" + "id=" + id + ", username='" + username + '\'' + ", pwd='" + pwd + '\'' + ", email='" + email + '\'' + ", age=" + age + ", gender='" + gender + '\'' + '}'; } }
2.2.3 创建接口UserMapper
package day1.mapper; import day1.pojo.User; import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.annotations.Param; import java.util.List; import java.util.Map; public interface UserMapper { /** * 添加用户 * @param user * @return */ int insert(User user); /** * 添加功能获取自增的主键 * @param user * @return */ int insertKey(User user); /** * 更新数据库某个字段内容 * @return */ int update(@Param("id") int id,@Param("name") String name,@Param("content") Stringcontent); /** * 根据id 删除指定数据 * @return */ int delete(@Param("id") String id); /** * 通过id查询查询用户 * @return */ User getUserByid(@Param("id") int id); /** * 方式一 * 查询所有用户 * @return */ List<User> getUserAll(); /** * 方式二 * 查询所有用户 * @return */ List<Map<String, Object>> getUserallMap(); /** * 用户登录案列 * @param username 用户账号 * @param pwd 密码 * @return */ User login(@Param("username")String username,@Param("pwd") String pwd); /** * 根据某字段进行关键字模糊查询 * @param word * @return */ List<Map<String,Object>> getUserBylike(@Param("name")String name,@Param("word") String word); }
2.2.4 Mapper目录下的配置文件:UserMapper.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="day1.mapper.UserMapper"> <insert id="insert"> insert into t_user values (null,#{username},#{pwd},#{email},#{age},#{gender}) </insert> <insert id="insertKey" useGeneratedKeys="true" keyProperty="id"> insert into t_user values (null,#{username},#{pwd},#{email},#{age},#{gender}) </insert> <update id="update"> update t_user set ${name}=#{content} where id=${id} </update> <delete id="delete"> delete from t_user where id in(${id}) </delete> <select id="getUserByid" resultType="User"> select*from t_user where id=#{id} </select> <select id="getUserAll" resultType="User"> select*from t_user </select> <select id="getUserallMap" resultType="map"> select *from t_user </select> <select id="login" resultType="User"> select * from t_user where username=#{username} and pwd=#{pwd} </select> <select id="getUserBylike" resultType="map"> select * from t_user where ${name} like "%"#{word}"%" </select> </mapper>
2.2.5 创建SqlsessionUtil 降低代码耦合
package day1.sqlsessionUtil; 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; public class sqlsessionUtil { public static SqlSession getsqlsession(){ SqlSession sqlSession=null; try { //获取配置文件 InputStream resourceAsStream =Resources.getResourceAsStream("mybatis_config.xml"); //创建SqlSessionFactoryBuilder,获取sqlSession SqlSessionFactoryBuilder sqlSessionFactoryBuilder = newSqlSessionFactoryBuilder(); SqlSessionFactory build = sqlSessionFactoryBuilder.build(resourceAsStream); //ture 代表数据自动提交 sqlSession = build.openSession(true); } catch (IOException e) { e.printStackTrace(); } return sqlSession; } }
3.测试代码及功能实现
import day1.mapper.UserMapper; import day1.pojo.User; import day1.sqlsessionUtil.sqlsessionUtil; 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 org.junit.Test; import java.io.IOException; import java.io.InputStream; import java.util.List; import java.util.Map; public class Mybatis { @Test public void demo() throws IOException { //获取配置文件流 InputStream resourceAsStream =Resources.getResourceAsStream("mybatis_config.xml"); //获取sqlSessionFactoryBuilder SqlSessionFactoryBuilder sqlSessionFactoryBuilder = newSqlSessionFactoryBuilder(); //获取SqlSessionFactory对象 SqlSessionFactory build = sqlSessionFactoryBuilder.build(resourceAsStream); //获取sqlSession对话 SqlSession sqlSession = build.openSession(); //获取sqlSession的代理实现类 UserMapper mapper = sqlSession.getMapper(UserMapper.class); int insert = sqlSession.insert("day1.mapper.UserMapper.insertUser"); //int i = mapper.insertUser(); System.out.println(insert); sqlSession.commit(); sqlSession.close(); } @Test public void insert(){ SqlSession sqlSession = sqlsessionUtil.getsqlsession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); User user = new User(null, "10086", "qwe123", "8958@163.com", 12, "男"); int insert = mapper.insert(user); sqlSession.close(); System.out.println(insert); if(insert ==1){ System.out.println("数据添加成功"); }else { System.out.println("fail"); } } @Test public void insertKey() { SqlSession sqlSession = sqlsessionUtil.getsqlsession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); User user = new User(null, "1008610", "qwe123", "8958@163.com", 12, "男"); mapper.insertKey(user); System.out.println(user.getId()); sqlSession.close(); } @Test public void update(){ SqlSession getsqlsession = sqlsessionUtil.getsqlsession(); UserMapper mapper = getsqlsession.getMapper(UserMapper.class); int update = mapper.update(1,"age", String.valueOf(23)); getsqlsession.close(); if(update ==1){ System.out.println("数据更新成功"); }else { System.out.println("fail"); } } @Test public void delete(){ SqlSession getsqlsession = sqlsessionUtil.getsqlsession(); UserMapper mapper = getsqlsession.getMapper(UserMapper.class); int delete = mapper.delete("1"); System.out.println(delete); getsqlsession.close(); if(delete ==1){ System.out.println("数据删除成功"); }else { System.out.println("fail"); } } @Test public void getUserByid(){ SqlSession sqlSession = sqlsessionUtil.getsqlsession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); User userByid = mapper.getUserByid(6); System.out.println(userByid); } @Test public void getUserAll(){ SqlSession sqlSession = sqlsessionUtil.getsqlsession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); List<User> userAll = mapper.getUserAll(); userAll.forEach(System.out::println); } @Test public void getUserallMap(){ SqlSession sqlSession = sqlsessionUtil.getsqlsession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); List<Map<String, Object>> userallMap = mapper.getUserallMap(); userallMap.forEach(System.out::println); } @Test public void login(){ SqlSession sqlSession = sqlsessionUtil.getsqlsession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); User qwe123 = mapper.login("10086", "qwe123"); System.out.println(qwe123); if(qwe123 != null){ System.out.println("sucesse"); }else { System.out.println("fail"); } } @Test public void getUserBylike(){ SqlSession sqlSession = sqlsessionUtil.getsqlsession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); List<Map<String, Object>> userBylike = mapper.getUserBylike("age", String.valueOf(16)); userBylike.forEach(System.out::println); } }
4.小结
1、 useGeneratedKeys:设置使用自增的主键
keyProperty:因为增删改有统一的返回值是受影响的行数,因此只能将获取的自增的主键放在传输的参数user对象的某个属性中
2、查询的标签select必须设置属性resultType或resultMap,用于设置实体类和数据库表的映射关系
resultType:自动映射,用于属性名和表中字段名一致的情况
resultMap:自定义映射,用于一对多或多对一或字段名和属性名不一致的情况
3、获取参数值的两种方式:'${ }'和#{ }
${}的本质就是字符串拼接,#{}的本质就是占位符赋值
${}使用字符串拼接的方式拼接sql,若为字符串类型或日期类型的字段进行赋值时,需要手动加单引号;但是#{}使用占位符赋值的 方式拼接sql,此时为字符串类型或日期类型的字段进行赋值时,可以自动添加单引号
若mapper接口中的方法参数为单个的字面量类型此时可以使用${}和#{}以任意的名称获取参数的值,注意${}需要手动加单引号
4、使用@Param标识参数
可以通过@Param注解标识mapper接口中的方法参数此时,会将这些参数放在map集合中,以@Param注解的value属性值为键, 以参数为值;以param1,param2...为键,以参数为值;只需要通过${}和#{}访问map集合的键就可以获取相对应的值,注意${}需要 手动加单引号
5、当查询的数据为多条时,不能使用实体类作为返回值,否则会抛出异常TooManyResultsException;但是若查询的数据只有一条,可以使用实体类或集合作为返回值
最近学习mybatis顺便整理发一下,有错误望指正~