springboot获取usermapper空指针_spring boot 集成 mysql

spring boot 集成mysql 很简单，介绍两种方式供小伙伴阅读学习，并比较两种方式的区别：

A 采用 jpa连接mysql

pom依赖

org.springframework.boot

spring-boot-starter-data-jpa

org.springframework.boot

spring-boot-starter-jdbc

配置文件：

#连接mysql数据库

spring.datasource.url=jdbc:mysql://localhost:3306/mwdb?useSSL=false&characterEncoding=UTF-8&?useUnicode=true

spring.datasource.username=admin

spring.datasource.password=123456

spring.datasource.driver-class-name=com.mysql.jdbc.Driver

server.tomcat.uri-encoding=UTF-8

#JPA Configuration:

spring.jpa.database=MySQL

spring.jpa.show-sql=true

spring.jpa.generate-ddl=true

spring.jpa.hibernate.ddl-auto=update

spring.jpa.database-platform=org.hibernate.dialect.MySQL5Dialect

实体类很容易写，省略，推荐使用lombok工具操作容易

controller层，与前端交互，前端可以使用freemaker 或者thymeleaf 展示，这两个模板引擎已经比较成熟；

@Autowired

UserService userService;

@RequestMapping(value = "/name",method = RequestMethod.GET)

public String list(String name,Model model){

List user=userService.findByName(name);

model.addAttribute("user",user);

return "index";

}

@RequestMapping(value = "/address",method = RequestMethod.GET)

public String findByAddress(String address,Model model){

List user= userService.findByAddress(address);

model.addAttribute("user",user);

return "index";

}

@RequestMapping(value = "/params",method = RequestMethod.GET)

public String withNameAndAddressQuery(String name,String address,Model model){

List user=userService.withNameAndAddressQuery(name,address);

model.addAttribute("user",user);

return "index";

}

@RequestMapping(value = "/del_name",method = RequestMethod.GET)

public String deleteByName(String name,Model model){

int a=userService.deleteByName(name);

model.addAttribute("user",a);

return "index";

}

@RequestMapping(value = "/add",method = RequestMethod.GET)

public String Add(String name,int age,String address,Model model){

int a=userService.Add(name,age,address);

return "index";

}

@RequestMapping(value = "/modify",method = RequestMethod.GET)

public int modify(int id,String name){

int a=userService.modify(id,name);

return a;

}

业务层，一些复杂的逻辑处理与业务可以在这层完成；

@Autowired

private UserRepository userRepository;

public List findByName(String name){

return userRepository.findByName(name);

}

public List findByAddress(String address){

return userRepository.findByAddress(address);

}

public List withNameAndAddressQuery(String name,String address){

return userRepository.withNameAndAddressQuery(name,address);

}

public int deleteByName(String name){

return userRepository.deleteByName(name);

}

public int Add(String name, int age,String address){

return userRepository.Add(name,age,address);

}

public int modify(int id,String name){

return userRepository.modify(id,name);

}

}

dao层是业务层与数据库操作的隔离的抽象，相当于消息中间件的一个作用;

@Repository

public interface UserRepository extends JpaRepository {

// @Query(value = "select * from User p where p.name= ?1", nativeQuery = true)

public List findByName(String name);

//@Query(value = "select * from User p where p.address= ?1", nativeQuery = true)

public List findByAddress(String address);

@Query(value = "select p from User p where p.name=:name and p.address=:address")

public List withNameAndAddressQuery(@Param("name") String name,@Param("address")String address);

@Modifying

@Query(value = "delete from User p where p.name= :name")

public int deleteByName(@Param("name") String name);

@Modifying

@Query(value = "insert into User(name,age,address) value(?1,?2,?3)",nativeQuery = true)

public int Add(String name, int age,String address);

@Modifying

@Query(value = "update User set name=?2 where id=?1",nativeQuery = true)

public int modify(int id,String name);

}

分页查询

// 封装分页对象

public List queryByPage(int page){

PageUtils pageUtils=new PageUtils ();

pageUtils.setPage(page);//传过来的参数为第page页

pageUtils.setSize(2);//每页条数

//下面是排序方法

//获取排序对象

Sort.Direction sort_direction=Sort.Direction.ASC.toString().equalsIgnoreCase(pageUtils.getSidx()) ? Sort.Direction.ASC:Sort.Direction.DESC;

//设置排序参数

Sort sort=new Sort(sort_direction,pageUtils.getSidx());

PageRequest pageRequest=new PageRequest(pageUtils.getPage()-1,pageUtils.getSize());

return userJPA.findAll(pageRequest).getContent();//返回结果内容

}

B 采用mybais连接mysql

pom依赖

org.mybatis.spring.boot

mybatis-spring-boot-starter

1.1.1

mysql

mysql-connector-java

application.properties配置文件

mybatis.config-locations=classpath:mybatis/mybatis-config.xml

mybatis.mapper-locations=classpath:mybatis/mapper/*.xml

mybatis.type-aliases-package=com.pro.entity

spring.datasource.driverClassName = com.mysql.jdbc.Driver

spring.datasource.url = jdbc:mysql://localhost:3306/mwdb?useUnicode=true&characterEncoding=utf-8

spring.datasource.username = admin

spring.datasource.password = 123456

controller层与前端交互

@Autowired

private UserMapper userMapper;

@RequestMapping("/getUsers")

public List getUsers() {

List users=userMapper.getAll();

return users;

}

@RequestMapping("/getUser")

public UserInfo getUser(Long id) {

UserInfo user=userMapper.getOne(id);

return user;

}

@RequestMapping("/add")

public void save(UserInfo user) {

userMapper.insert(user);

}

@RequestMapping(value="update")

public void update(UserInfo user) {

userMapper.update(user);

}

@RequestMapping(value="/delete/{id}")

public void delete(@PathVariable("id") Long id) {

userMapper.delete(id);

}

省略业务层，直接mapper层，相当于dao

public interface UserMapper {

List getAll();

UserInfo getOne(Long id);

void insert(UserInfo user);

void update(UserInfo user);

void delete(Long id);

}

mapper.xml 数据库的增删改查

id, userName, passWord, user_sex, nick_name

SELECT

FROM users

SELECT

FROM users

WHERE id = #{id}

INSERT INTO

users

(userName,passWord,user_sex)

VALUES

(#{userName}, #{passWord}, #{userSex})

UPDATE

users

SET

userName = #{userName},

passWord = #{passWord},

nick_name = #{nickName}

WHERE

id = #{id}

DELETE FROM

users

WHERE

id =#{id}

mybatis.config文件配置，一些数据库字段的驼峰命名之类的

启动 扫描mapper

@SpringBootApplication

@MapperScan("com.pro.mapper")

public class Application {

public static void main(String[] args) {

SpringApplication.run(Application.class, args);

}

}

上述简单的demo 可以基本实现，

在加上分页查询；

######### 分页插件 ##########

pom 依赖

com.github.pagehelper

pagehelper-spring-boot-starter

1.2.10

##pagehelper.reasonable： 分页合理化参数，默认值为false。

# 当该参数设置为 true 时，pageNum<=0 时会查询第一页， pageNum>pages(超过总数时)，会查询最后一页。默认false 时，直接根据参数进行查询

#support-methods-arguments： 支持通过 Mapper 接口参数来传递分页参数，默认值false，分页插件会从查询方法的参数值中，

# 自动根据上面 params 配置的字段中取值，查找到合适的值时就会自动分页

pagehelper.helper-dialect=mysql

pagehelper.params=count=countSql

pagehelper.reasonable=false

pagehelper.support-methods-arguments=true

关键查询语句

PageInfo pageInfo = PageHelper.startPage(1, 10).setOrderBy("id desc")

.doSelectPageInfo(() -> this.userMapper.getAll());

log.info("[lambda写法] - [分页信息] - [{}]", pageInfo.toString());

PageHelper.startPage(1, 10).setOrderBy("id desc");

PageInfo userPageInfo = new PageInfo<>(this.userMapper.getAll());

log.info("[普通写法] - [{}]", userPageInfo);

金三银四的春天多准备些干货