1,mybatis
MyBatis 是一款优秀的持久层框架,它支持定制化 SQL、存储过程以及高级映射。MyBatis 避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集。MyBatis 可以使用简单的 XML 或注解来配置和映射原生类型、接口和 Java 的 POJO(Plain Old Java Objects,普通老式 Java 对象)为数据库中的记录。
2,mybatis的简单实例
mysql 建表
CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(45) DEFAULT NULL, `sex` varchar(45) DEFAULT NULL, `birthday` date DEFAULT NULL, `address` varchar(45) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8; SELECT * FROM testdb.user;
maven 依赖
<dependencies> <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis --> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.4.4</version> </dependency> <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.16</version> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.8.2</version> <scope>test</scope> </dependency> </dependencies>
User 表对应的类:
import java.util.Date; import lombok.Getter; import lombok.Setter; @Setter @Getter public class User { private int id; private String username; private String sex; private Date birthday; private String address_city; }
mybatis的配置文件mybatis-xml,目标位置src/main/resources 下
<?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> <!--别名 在XXXmapper.xml 的resultType 或者 parameterType 就可以写User了 --> <typeAliases> <typeAlias type="com.ys.entity.User" alias="User" /> </typeAliases> <!-- 数据库环境配置 --> <environments default="development"> <environment id="development"> <!-- jdbc管理 --> <transactionManager type="JDBC" /> <!-- 用什么连接池 --> <dataSource type="POOLED"> <!-- jdbc驱动 --> <property name="driver" value="com.mysql.cj.jdbc.Driver" /> <!-- 数据库名字url --> <property name="url" value="jdbc:mysql://127.0.0.1:3306/testdb" /> <!-- 数据库用户 --> <property name="username" value="root" /> <!-- 数据库用户密码 --> <property name="password" value="123456" /> </dataSource> </environment> </environments> <!-- 把User.xml注册到mybatis的配置文件中,User.xml下面配置 --> <mappers> <mapper resource="com/ys/mappersxml/UserMapper.xml"></mapper> </mappers> </configuration>
mybatis.xml 的标签的作用:
<!--别名 在XXXmapper.xml 的resultType 或者 parameterType 就可以写User了 --> <typeAliases> <typeAlias type="com.ys.entity.User" alias="User" /> </typeAliases>
将每个mapper.xml 注册到mybatis 中
<mappers> <mapper resource="com/ys/mappersxml/UserMapper.xml"></mapper> </mappers>
接下来就是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="com.ys.mappers.UserMapper"> <!-- 根据 id 查询 user 表中的数据 id:唯一标识符,此文件中的id值不能重复 resultType:返回值类型,一条数据库记录也就对应实体类的一个对象 parameterType:参数类型,也就是查询条件的类型 --> <select id="selectUserById" resultType="User" parameterType="int"> <!-- 这里和普通的sql 查询语句差不多,对于只有一个参数,后面的 #{id}表示占位符,里面不一定要写id,写啥都可以,但是不要空着,如果有多个参数则必须写pojo类里面的属性 --> select * from user where id = #{id} </select> <insert id="insertUser" parameterType="com.ys.entity.User"> insert into user(id,username,sex,birthday,address) value(#{id},#{username},#{sex},#{birthday},#{address}) </insert> <select id="selectAllUser" resultType="com.ys.entity.User"> select * from user </select> <select id="selectUserNameLike" parameterType="java.lang.String" resultType="com.ys.entity.User"> select * from user where username like '%${value}%' </select> <select id="selectByName" parameterType="java.lang.String" resultType="com.ys.entity.User"> select * from user where username = #{username} </select> <delete id="deleteUserById" parameterType="int"> delete from user where id=#{id} </delete> <update id="updateUserById" parameterType="java.util.Map"> update user set username=#{username} where id=#{id} </update> <!-- resultMap --> <resultMap id="userResultMap" type="User"> <id property="id" column="id" /> <result property="username" column="username" /> <result property="address_city" column="address" /> </resultMap> <select id="selectUserByIdMap" resultMap="userResultMap" parameterType="int"> <!-- 数据库address 对应address_city --> select id,username,address from user where id =#{id} </select> </mapper>
UserMapper 接口,定义了需要的查询方法:UserMapper.xml 里面的方法名称要和 UserMapper.java 里面一样:
package com.ys.mappers; import java.util.List; import java.util.Map; import org.apache.ibatis.annotations.Param; import org.apache.ibatis.annotations.Select; import com.ys.entity.User; public interface UserMapper { void insertUser(User user); User selectUserById(int id); List<User> selectAllUser(); //模糊查询的 List<User> selectUserNameLike(String username); List<User> selectByName(String username); void deleteUserById(int id); // 多参数 传递方式1 // public void updateUserById(@Param("id")int id,@Param("username")String // username); // 多参数 传递方式2 void updateUserById(Map<String, Object> map); // 使用注解 字符串替换 不用写根据id查 根据username查 根据address 查 @Select("select * from user where ${column} = #{value}") List<User> findByColumn(@Param("column") String column, @Param("value") String value); // resultMap 结果映射 只想输出部分的属性 User selectUserByIdMap(int id); }
测试:
ublic class App { public static void main(String[] args) { String resource = "mybatis.xml"; // 加载 mybatis 全局配置文件 InputStream inputStream = CRUDTest.class.getClassLoader().getResourceAsStream(resource); // 构建sqlSession的工厂 SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream); // 根据 sqlSessionFactory 产生 session SqlSession session = sessionFactory.openSession(); UserMapper mapper = session.getMapper(UserMapper.class); User user = mapper.selectUserById(1); System.out.println(user.getUsername()); // 插入 // User u = new User(); // u.setUsername("Jimmy"); // u.setSex("female"); // u.setAddress_city("Pekin"); // mapper.insertUser(u); // 查询所有 List<User> users = mapper.selectAllUser(); for (User user2 : users) { System.out.println("selectAll: " + user2.getUsername()); } // 模糊查询 List<User> userLikes = mapper.selectUserNameLike("L"); for (User user2 : userLikes) { System.out.println("selectUserNameLike: " + user2.getUsername()); } // 根据姓名查询 List<User> selectByName = mapper.selectByName("Jim"); for (User user2 : selectByName) { System.out.println("selectByName: " + user2.getUsername()); } //根据id 删除 //mapper.deleteUserById(3); //更新 多个不同类型的传参 map HashMap<String, Object> map = new HashMap<String, Object>(); map.put("id", 1); map.put("username", "Linda"); mapper.updateUserById(map); //字符串替换 List<User> findByColumn = mapper.findByColumn("username","Linda"); for (User user2 : findByColumn) { System.out.println("findByColumn: " + user2.getUsername()); //数据库的字段是address javaBean是属性是address 所以直接查询不能映射,需要resultMap System.out.println("findByColumn: "+user2.getAddress_city()); } //resultMap 结果映射 User selectUserByIdMap = mapper.selectUserByIdMap(1); System.out.println("resultMapSelect "+selectUserByIdMap.getAddress_city()); session.close(); } }