目录
一、整合mybatis
1、建表phone_type
DROP TABLE IF EXISTS `phone_type`;
CREATE TABLE `phone_type` (
`type_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '类型id',
`number_part` varchar(50) DEFAULT NULL COMMENT '号码段',
`type_name` varchar(50) DEFAULT NULL COMMENT '类型名称',
`type_remark` varchar(255) DEFAULT NULL COMMENT '类型备注',
PRIMARY KEY (`type_id`),
KEY `type_id` (`type_id`,`type_name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO `phone_type` VALUES ('1', '131', '中国联通', '老号码');
2、导入依赖
<!-- mybatis -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.3</version>
</dependency>
3、model
@Entity
@Table(name = "phone_type")
public class PhoneType {
@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
private String type_id;
private String number_part;
private String type_name;
private String type_remark;
// 省略get/set方法
}
4、dao
@Mapper
public interface PhoneTypeDao {
PhoneType findByNumber(@Param("number") String number);
}
5、mapper
<?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.example.jpa.demo_jpa.dao.PhoneTypeDao" >
<resultMap id="phoneTypeMap" type="com.example.jpa.demo_jpa.model.PhoneType" >
<id column="type_id" property="type_id" jdbcType="INTEGER" />
<result column="number_part" property="number_part" jdbcType="VARCHAR" />
<result column="type_name" property="type_name" jdbcType="VARCHAR" />
<result column="type_remark" property="type_remark" jdbcType="VARCHAR" />
</resultMap>
<select id="findByNumber" resultMap="phoneTypeMap" parameterType="String">
select * from phone_type
<where>
number_part = #{number}
</where>
</select>
</mapper>
6、配置文件
server.port=8080
spring.datasource.url=jdbc:mysql://xxx/xxx?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
spring.datasource.username=
spring.datasource.password=
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
# mybatis
mybatis.type-aliases-package=com.example.jpa.demo_jpa.dao
mybatis.mapper-locations=classpath:/mappers/*Mapper.xml
注意这里配置mybatis的路径,要跟自己的包所对应
7、service
public interface PhoneTypeService {
//通过号码查找信息
PhoneType findByNumber(String number);
}
8、serviceImpl
@Service
public class PhoneTypeServiceImpl implements PhoneTypeService {
@Autowired
private PhoneTypeDao phoneTypeDao;
@Override
public PhoneType findByNumber(String number) {
return phoneTypeDao.findByNumber(number);
}
}
9、controller
@RestController
public class HelloController {
@Autowired
PhoneTypeService phoneTypeService;
@RequestMapping("/findByNumber")
public PhoneType findByNumber(String number){
return phoneTypeService.findByNumber("131"); //这里为了方便测试,直接写定了这个“131”,实际应该是由前端传来的。
}
}
10、测试
成功查询到号码为“131”的数据信息:
11、增删改查的mapper(示例)
<!--通过文章标题和内容精准查询-->
<select id="findByTitleText" resultType="com.tweet.tweetapi.domain.Tweet" resultMap="TweetResultMap">
select *
from tweet
where title = #{title} and text = #{text}
</select>
<!--添加-->
<insert id="save" parameterType="com.tweet.tweetapi.domain.Tweet" >
insert into tweet values (#{id}, #{title},#{caid},#{tid},#{text})
</insert>
<!--删除-->
<delete id="delete" parameterType="String">
delete from tweet where id = #{id}
</delete>
<!--修改-->
<update id="update" parameterType="com.tweet.tweetapi.domain.Tweet">
update tweet
set title = #{title}, caid = #{caid}, tid = #{tid}, text = #{text}
where id = #{id}
</update>
二、mybatis一对多查询
场景: 一篇文章(tweet)有多个关键词(keyword),多个关键词对应一篇文章
1、文章实体类
2、xml文件
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.tweet.tweetservice.dao.TweetDao">
<resultMap id="TweetResultMap" type="com.tweet.tweetapi.domain.Tweet">
<result column="id" property="id" jdbcType="VARCHAR"/>
<result column="title" property="title" jdbcType="VARCHAR"/>
<result column="caid" property="caid" jdbcType="VARCHAR"/>
<result column="tid" property="tid" jdbcType="VARCHAR"/>
<result column="text" property="text" jdbcType="VARCHAR"/>
<collection property="keyWordList" ofType="com.tweet.tweetapi.domain.KeyWord">
<result column="kwid" property="kwid"/>
<result column="kwname" property="kwname"/>
<result column="twid" property="twid"/>
</collection>
</resultMap>
<!--通过id查询-->
<select id="findById" resultMap="TweetResultMap">
select *
from tweet t,keyword k
where t.id = k.twid and t.id = #{twid}
</select>
</mapper>