SpringBoot+JDBC+H2简单配置实现（不使用Jpa、Mybatis）

SpringBoot+JDBC+H2简单配置实现（不使用Jpa、Mybatis）

---

整体框架

在IntellJ+Maven+JDK1.8环境下，使用Controller控制Service和Dao接口，再分别实现Dao和Service。

一、依赖搭建 pow.xml文件

<?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>H2Study</artifactId>
   <version>1.0-SNAPSHOT</version>


   <properties>
      <maven.compiler.source>8</maven.compiler.source>
      <maven.compiler.target>8</maven.compiler.target>
   </properties>

   <parent>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-parent</artifactId>
      <version>2.1.4.RELEASE</version>
   </parent>

   <dependencies>
      <dependency>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-starter-web</artifactId>
         <version>2.1.4.RELEASE</version>
      </dependency>

      <dependency>
         <groupId>junit</groupId>
         <artifactId>junit</artifactId>
         <scope>test</scope>
      </dependency>

      <dependency>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-starter-jdbc</artifactId>
      </dependency>

      <dependency>
         <groupId>com.h2database</groupId>
         <artifactId>h2</artifactId>
         <version>1.4.197</version>
         <scope>runtime</scope>
         <!--            <scope>test</scope>-->
      </dependency>
   </dependencies>

</project>

二、entity层

这层定义了数据库表的属性(可以理解为，建立了与数据库表对应的类)

package com.jege.spring.boot.jdbc.entity;

/**
 *创建相关的User对象，和表T_USER相关联
 */
public class User {
  private Long id;
  private String name;
  private Integer age;
  private String pwd;

  public User() {

  }

  public User(String name, Integer age, String pwd) {
    this.name = name;
    this.age = age;
    this.pwd = pwd;
  }

  public Long getId() {
    return id;
  }

  public void setId(Long id) {
    this.id = id;
  }

  public String getName() {
    return name;
  }

  public void setName(String name) {
    this.name = name;
  }

  public Integer getAge() {
    return age;
  }

  public void setAge(Integer age) {
    this.age = age;
  }

  public String getPwd(){return pwd;}

  public void setPwd(String pwd){this.pwd = pwd;}

}

三、DAO层

这层主要是为了实现对数据库的增删改查，分为接口和实现两部分。

IUserDao.java

package com.jege.spring.boot.jdbc.dao;

import java.util.List;

import com.jege.spring.boot.jdbc.entity.User;

/**
 *database crud
 */
public interface IUserDao {

  void dropTable();

  void createTable();

  void save(User user);

  List<User> findAll();

  void deleteAll();

  List<User> findByNameLike(String name);

}

DefaultUserImpl.java

package com.jege.spring.boot.jdbc.dao;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.jege.spring.boot.jdbc.dao.IUserDao;
import com.jege.spring.boot.jdbc.entity.User;
/**
 * curd implements
 * */

@Repository
public class DefaultUserDaoImpl implements IUserDao {
  @Autowired
  JdbcTemplate jdbcTemplate;

  @Override
  public void dropTable() {
    jdbcTemplate.update("drop table t_user if exists");
  }

  @Override
  public void createTable() {
    jdbcTemplate.update(
	"create table t_user (id bigint generated by default as identity, age integer, name varchar(255), primary key (id))");
  }

  @Override
  public void save(User user) {
    jdbcTemplate.update("insert into t_user(name,age,pwd) values(?,?,?)", user.getName(), user.getAge(),user.getPwd());
  }

  @Override
  public List<User> findAll() {
    return jdbcTemplate.query("select * from t_user", BeanPropertyRowMapper.newInstance(User.class));
  }

  @Override
  public void deleteAll() {
    jdbcTemplate.update("delete from t_user");
  }

  @Override
  public List<User> findByNameLike(String name) {
    return jdbcTemplate.query("select id,name,age from t_user where name like ?", new Object[] { name },
	BeanPropertyRowMapper.newInstance(User.class));
  }

}

四、service层

service区别于dao层，主要负责业务上一些工作。

IService.java

package com.jege.spring.boot.jdbc.service;


public interface IService {

    void register(String name, int age, String pwd);

    void updateAge(int id, int newAge);

    void updatePwd(int id, String newPwd);

}

DefaultServiceImpl.java

package com.jege.spring.boot.jdbc.service;

import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;


import com.jege.spring.boot.jdbc.service.IService;
import com.jege.spring.boot.jdbc.entity.User;

@Repository
public class DefaultServiceImpl implements IService {
    @Autowired
    JdbcTemplate jdbcTemplate;

    @Override
    public void register(String name, int age, String pwd){
        jdbcTemplate.update("insert into t_user(name,age,pwd) values(?,?,?)", name, age, pwd);

    }

    @Override
    public void updateAge(int id, int newAge){
        jdbcTemplate.update(
                "update t_user SET age = ? WHERE id = ?", newAge,id);
    }

    @Override
    public void updatePwd(int id, String newPwd){
        jdbcTemplate.update(
                "update t_user SET pwd = ? WHERE id = ?", newPwd,id);
    }

}

五、controller层

调用service和dao层的接口

package com.jege.spring.boot.jdbc.controller;

//导入Dao和Service接口
import com.jege.spring.boot.jdbc.dao.IUserDao;
import com.jege.spring.boot.jdbc.service.IService;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;//请求池，包含了各种请求
import org.springframework.web.bind.annotation.RestController;


import com.jege.spring.boot.jdbc.entity.User;

@RestController
public class Controller {

    @Autowired
    private IUserDao userDaoImpl;
    @Autowired
    private IService serviceImpl;
    /**
     * 保存数据
     * @return
     */

    @RequestMapping("/save")
    public String save(){
        //内存数据库操作
        userDaoImpl.save(new User("title1",17,"123"));
        userDaoImpl.save(new User("title2",18,"123"));
        userDaoImpl.save(new User("title3",19,"123"));
        userDaoImpl.save(new User("title4",20,"123"));
        userDaoImpl.save(new User("title5",21,"123"));
        return "save ok";
    }


    /**
     * 获取所有数据
     * @return
     */
    @RequestMapping("/findAll")
    public Iterable<User> findAll(){
        //内存数据库操作
        return userDaoImpl.findAll();
    }

    /**
     * 注册用户
     */
    @RequestMapping("/register")
    public String register(){
        serviceImpl.register("MAN",119,"xx");
        return "register ok";
    }


    /**
     * 更新用户年龄
     */
    @RequestMapping("/updateAge")
    public String update(){
        serviceImpl.updateAge(3,120);
        return "update age ok";
    }
}

六、resource设置

1、application.properties参数设置

spring.h2.console.enabled=true
spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=TRUE;AUTO_RECONNECT=TRUE;
spring.datasource.username=sa
spring.datasource.password=

#spring某个版本之后需要加上这句，否则不会自动执行sql文件
spring.datasource.initialization-mode=always
# schema.sql中一般存放的是建表sql语句
spring.datasource.schema = classpath:schema.sql
# data.sql中一般存放的是需要插入更新等sql语句
spring.datasource.data =  classpath:data.sql

spring.datasource.initialization-mode有三个值选项：always为始终执行初始化，embedded只初始化内存数据库（默认值）,如h2等，never为不执行初始化。

2、.sql文件

.sql文件放置在resource文件夹下。一般schema.sql中存放建表语句，data.sql存放数据更新语句。

**注意：**spring.datasource.schema = classpath:schema.sql必不可少，因为这条语句创建了表，少了这个，h2数据库初始化之后是没有表的，对于表的增删改查都会失败报错。

schema.sql

DROP TABLE IF EXISTS T_USER;

CREATE TABLE T_USER(
--  id INT AUTO_INCREMENT  PRIMARY KEY,
--  user VARCHAR(250) NOT NULL,
--  pwd VARCHAR(250) NOT NULL,
--  status INT AUTO_INCREMENT  NOT NULL,
--  tole INT AUTO_INCREMENT NOT NULL.
    id INT AUTO_INCREMENT  PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    age INT NOT NULL,
);

data.sql

INSERT INTO T_USER (name, age) VALUES
  ('Aliko', 1),
  ('Bill', 2);

[参考资料]

springboot自动执行sql文件需要的.properties设置

Java之JDBC数据库连接+实体层和数据访问层

Spring Boot 菜鸟教程 4 JDBC

Spring Boot和内存数据库中H2的使用教程