创建基础的 spring boot项目就不说了,参考上一个文章:
https://blog.csdn.net/puyinggong/article/details/80653014
一、添加对应的pom文件
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.0</version>
</dependency>
第一个主要是为了添加mysql的包支持,当然这个也根据项目情况,也可以添加oracle的,等到
第二个主要是为了添加数据库连接池,这个是阿里开发的开源项目,当然还有很多功能
具体参考:https://www.cnblogs.com/niejunlei/p/5977895.html
二、生成对应代码和sql映射文件
然后就是创建对应的实体类啦,这个没什么好说的,大家都知道。
我创建了大概的几个类和sql映射
需要注意的是,UserServiceImpl这个类需要用到类注解
@Service("userService")
控制层代码如下:
package com.zhz.springboot.controller;
import com.zhz.springboot.entity.User;
import com.zhz.springboot.service.UserService;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
@Controller
@RequestMapping("/user")
public class UserController {
@Resource
private UserService userService;
@RequestMapping("/showUser")
@ResponseBody
public User toIndex(HttpServletRequest request, Model model){
int userId = Integer.parseInt(request.getParameter("id"));
User user = this.userService.getUserById(userId);
return user;
}
}
接口层代码如下:
package com.zhz.springboot.Dao;
import java.util.List;
import com.zhz.springboot.entity.User;
import org.apache.ibatis.annotations.Param;
public interface UserMapper {
int deleteByPrimaryKey(Integer userId);
int insert(User record);
int insertSelective(User record);
User selectByPrimaryKey(Integer userId);
int updateByPrimaryKeySelective(User record);
int updateByPrimaryKey(User record);
/**
* 查询用户信息并分页
* @param skip
* @param size
* @return
*/
public List<User> queryUserPager(@Param("skip") int skip,@Param("size") int size);
/**
* 查询用户总数
* @return
*/
public int queryUserCount();
/**
* 删除多个用户
* @param userIds
* @return
*/
public int deleteUsers(int[] userIds);
}
sql映射文件如下:
<?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.zhz.springboot.Dao.UserMapper" >
<resultMap id="BaseResultMap" type="com.zhz.springboot.entity.User" >
<id column="user_id" property="userId" jdbcType="INTEGER" />
<result column="user_name" property="userName" jdbcType="VARCHAR" />
<result column="user_sex" property="userSex" jdbcType="VARCHAR" />
<result column="user_birthday" property="userBirthday" jdbcType="DATE" />
<result column="user_email" property="userEmail" jdbcType="VARCHAR" />
<result column="user_edu" property="userEdu" jdbcType="VARCHAR" />
<result column="user_telephone" property="userTelephone" jdbcType="VARCHAR" />
<result column="user_address" property="userAddress" jdbcType="VARCHAR" />
<result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
<result column="user_sex_desc" property="userSexDesc" jdbcType="TIMESTAMP" />
<result column="user_edu_desc" property="userEduDesc" jdbcType="TIMESTAMP" />
</resultMap>
<sql id="Base_Column_List" >
user_id, user_name, user_sex, user_birthday, user_email, user_edu, user_telephone,
user_address, create_time
</sql>
<!--查询用户信息并分页 -->
<select id="queryUserPager" resultMap="BaseResultMap">
select t.user_id,t.user_name,t.user_sex,date_format(t.user_birthday,'%Y-%m-%d')user_birthday,
t.user_email,t.user_edu,t.user_telephone,t.user_address,p.codedesc as user_sex_desc,
p1.codedesc as user_edu_desc
from tb_user t inner join tb_dict p on t.user_sex=p.code and p.field='SEX'
inner join tb_dict p1 on t.user_edu = p1.code and p1.field = 'EDU'
order by t.create_time desc
limit #{skip},#{size}
</select>
<!--查询用户总数 -->
<select id="queryUserCount" resultType="int">
select count(*) from tb_user
</select>
<!--删除多个用户 -->
<delete id="deleteUsers" parameterType="java.util.List">
delete from tb_user where user_id in
<!-- <foreach>标签有循环的功能,可以用来生成有规律的SQL语句,主要属性有:
item:表示集合每一个元素进行迭代时的别名
index:表示在迭代过程中,每次迭代到的位置
open:表示该语句以什么开始
separator:表示每次迭代之间以什么符号作为分隔
close:表示该语句以什么结束
collection:要循环的集合 -->
<foreach item="item" index="index" collection="array" open="(" separator="," close=")">
#{item}
</foreach>
</delete>
<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
select
<include refid="Base_Column_List" />
from tb_user
where user_id = #{userId,jdbcType=INTEGER}
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
delete from tb_user
where user_id = #{userId,jdbcType=INTEGER}
</delete>
<insert id="insert" parameterType="com.zhz.springboot.entity.User" >
insert into tb_user ( user_name, user_sex,
user_birthday, user_email, user_edu,
user_telephone, user_address, create_time
)
values ( #{userName,jdbcType=VARCHAR}, #{userSex,jdbcType=VARCHAR},
#{userBirthday,jdbcType=DATE}, #{userEmail,jdbcType=VARCHAR}, #{userEdu,jdbcType=VARCHAR},
#{userTelephone,jdbcType=VARCHAR}, #{userAddress,jdbcType=VARCHAR}, #{createTime,jdbcType=TIMESTAMP}
)
</insert>
<insert id="insertSelective" parameterType="com.zhz.springboot.entity.User" >
insert into tb_user
<trim prefix="(" suffix=")" suffixOverrides="," >
<if test="userName != null" >
user_name,
</if>
<if test="userSex != null" >
user_sex,
</if>
<if test="userBirthday != null" >
user_birthday,
</if>
<if test="userEmail != null" >
user_email,
</if>
<if test="userEdu != null" >
user_edu,
</if>
<if test="userTelephone != null" >
user_telephone,
</if>
<if test="userAddress != null" >
user_address,
</if>
<if test="createTime != null" >
create_time,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides="," >
<if test="userId != null" >
#{userId,jdbcType=INTEGER},
</if>
<if test="userName != null" >
#{userName,jdbcType=VARCHAR},
</if>
<if test="userSex != null" >
#{userSex,jdbcType=VARCHAR},
</if>
<if test="userBirthday != null" >
#{userBirthday,jdbcType=DATE},
</if>
<if test="userEmail != null" >
#{userEmail,jdbcType=VARCHAR},
</if>
<if test="userEdu != null" >
#{userEdu,jdbcType=VARCHAR},
</if>
<if test="userTelephone != null" >
#{userTelephone,jdbcType=VARCHAR},
</if>
<if test="userAddress != null" >
#{userAddress,jdbcType=VARCHAR},
</if>
<if test="createTime != null" >
#{createTime,jdbcType=TIMESTAMP},
</if>
</trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="com.zhz.springboot.entity.User" >
update tb_user
<set >
<if test="userName != null" >
user_name = #{userName,jdbcType=VARCHAR},
</if>
<if test="userSex != null" >
user_sex = #{userSex,jdbcType=VARCHAR},
</if>
<if test="userBirthday != null" >
user_birthday = #{userBirthday,jdbcType=DATE},
</if>
<if test="userEmail != null" >
user_email = #{userEmail,jdbcType=VARCHAR},
</if>
<if test="userEdu != null" >
user_edu = #{userEdu,jdbcType=VARCHAR},
</if>
<if test="userTelephone != null" >
user_telephone = #{userTelephone,jdbcType=VARCHAR},
</if>
<if test="userAddress != null" >
user_address = #{userAddress,jdbcType=VARCHAR},
</if>
<if test="createTime != null" >
create_time = #{createTime,jdbcType=TIMESTAMP},
</if>
</set>
where user_id = #{userId,jdbcType=INTEGER}
</update>
<update id="updateByPrimaryKey" parameterType="com.zhz.springboot.entity.User" >
update tb_user
set user_name = #{userName,jdbcType=VARCHAR},
user_sex = #{userSex,jdbcType=VARCHAR},
user_birthday = #{userBirthday,jdbcType=DATE},
user_email = #{userEmail,jdbcType=VARCHAR},
user_edu = #{userEdu,jdbcType=VARCHAR},
user_telephone = #{userTelephone,jdbcType=VARCHAR},
user_address = #{userAddress,jdbcType=VARCHAR},
create_time = #{createTime,jdbcType=TIMESTAMP}
where user_id = #{userId,jdbcType=INTEGER}
</update>
</mapper>
方便的,还是建议写个自动生成的,使用
MybatisGenerator
自己写个项目,导入依赖包,平时只要使用的时候,改下配置文件,运行main方法自动生成,然后拷贝到自己项目中,还是很方便的。
这些都完成了,那么下一步就是关键了,就是怎样让这些东西都能生效呢。
三、添加配置文件,使mybatis生效
在resources文件夹下面创建配置文件,application.yml,然后里面添加对应的配置信息
#公共配置与profiles选择无关 mapperLocations指的路径是src/main/resources
mybatis:
typeAliasesPackage: com.zhz.springboot.entity
mapperLocations: classpath:mapper/*.xml
#开发配置
spring:
profiles: dev
datasource:
url: jdbc:mysql://localhost:3306/test
username: root
password: 123456
driver-class-name: com.mysql.jdbc.Driver
# 使用druid数据源
type: com.alibaba.druid.pool.DruidDataSource
# 连接池设置
druid:
initial-size: 5
min-idle: 5
max-active: 20
# 配置获取连接等待超时的时间
max-wait: 60000
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
time-between-eviction-runs-millis: 60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
min-evictable-idle-time-millis: 300000
# Oracle请使用select 1 from dual
validation-query: SELECT 'x'
test-while-idle: true
test-on-borrow: false
test-on-return: false
# 打开PSCache,并且指定每个连接上PSCache的大小
pool-prepared-statements: true
max-pool-prepared-statement-per-connection-size: 20
# 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
filters: stat,wall,slf4j
# 通过connectProperties属性来打开mergeSql功能;慢SQL记录
connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
# 合并多个DruidDataSource的监控数据
use-global-data-source-stat: true
mvc:
view:
prefix: /WEB-INF/jsp/
suffix: .jsp
OK,到这一步,整个配置就完成了,是不是很简单,方便了太多太多,节约了大部分配置。
然后回到man方法,启动整个项目。
输入对应地址
localhost:8080/user/showUser?id=1
浏览器展示从数据获取的值,测试成功。