IntelliJ IDEA Spring Boot(3) 数据库JDBC的使用

来源：互联网发布：送男生什么礼物知乎编辑：程序博客网时间：2024/05/29 15:08

学习web技术，任何新的东西我总是想着要去与数据库打交道。这篇我来学习如何用springboot 集成数据库的使用。这里简单的集成mysql和jdbcTemplate。所有的框架集成都是由少到多的嘛。

首先我们在pom文件中加上依赖

<dependency>   <groupId>org.springframework.boot</groupId>   <artifactId>spring-boot-starter-jdbc</artifactId>   <exclusions>      <exclusion><!--排除默认的数据源 -->         <groupId>org.apache.tomcat</groupId>         <artifactId>tomcat-jdbc</artifactId>      </exclusion>   </exclusions></dependency><dependency>   <groupId>mysql</groupId>   <artifactId>mysql-connector-java</artifactId>   <version>5.1.44</version></dependency><dependency>   <groupId>com.alibaba</groupId>   <artifactId>druid</artifactId>   <version>1.0.31</version></dependency>

接下来，我们配置mysql的东西，最起码用户名密码什么的吧。在application.properties文件中配置如下代码

#mysqlspring.datasource.driver-class-name= com.mysql.jdbc.Driverspring.datasource.url=jdbc\:mysql\://localhost\:3306/dadmin?useUnicode\=true&amp;characterEncoding\=UTF-8spring.datasource.username=rootspring.datasource.password=123456#druidspring.datasource.type=com.alibaba.druid.pool.DruidDataSourcespring.datasource.filters=statspring.datasource.maxActive= 20spring.datasource.initialSize= 1spring.datasource.maxWait= 60000spring.datasource.minIdle =1spring.datasource.timeBetweenEvictionRunsMillis= 60000spring.datasource.minEvictableIdleTimeMillis=300000spring.datasource.validationQuery= select 'x'spring.datasource.testWhileIdle= truespring.datasource.testOnBorrow=falsespring.datasource.testOnReturn= falsespring.datasource.poolPreparedStatements=truespring.datasource.maxOpenPreparedStatements= 20

开启事务驱动

@SpringBootApplication@EnableTransactionManagementpublic class SpringbootApplication {   public static void main(String[] args) {      SpringApplication.run(SpringbootApplication.class, args);   }}

接下来，跑起来。一切正常。

接下来。创建一个实体类和对应的表，进行简单的增删改查。

public class SystemUser implements Serializable {    private String uuid;     // 表主键    private String username; // 登录用户名    private String password; // 登录密码    private String isRoot; // 最高权限管理员; "1":root "0": notRoot    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")    private Date registerDate;    private Integer status; // 账号状态： 0：正常；1：禁用

public interface SystemUserDao {     SystemUser findUserByUsername(String username);     List<SystemUser> findAllUser();     String saveUser(SystemUser user);     void deleteUserByUUID(String uuid);     void updateUser(SystemUser user);}

public interface SystemUserService {    SystemUser findUserByUsername(String username);    List<SystemUser> findAllUser();    String saveUser(SystemUser user);    void deleteUserByUUID(String uuid);    void updateUser(SystemUser user);}

@Repository("systemUserDao")public class SystemUserDaoImpl implements SystemUserDao{    @Autowired    JdbcTemplate jdbcTemplate;    static class UserRowMapper implements RowMapper<SystemUser>{        @Override        public SystemUser mapRow(ResultSet rs, int rowNum) throws SQLException {            SystemUser systemUser = new SystemUser();            systemUser.setUuid(rs.getString("uuid"));            systemUser.setUsername(rs.getString("username"));            systemUser.setPassword(rs.getString("password"));            systemUser.setIsRoot(rs.getString("isRoot"));            systemUser.setRegisterDate(new java.util.Date(rs.getDate("registerDate").getTime()));            systemUser.setStatus(rs.getInt("status"));            // if the value is SQL NULL, the value returned is 0            // 如果希望得到null，可以getObject()判断是否为null            return systemUser;        }    }    @Override    public SystemUser findUserByUsername(String username) {        List<SystemUser> list = jdbcTemplate.query(                "select * from system_user where username=?",                new Object[]{username},                new int[]{Types.VARCHAR},                new UserRowMapper()                );        if(null!=list&&!list.isEmpty()){            return list.get(0);        }        return null;    }    @Override    public List<SystemUser> findAllUser() {        return jdbcTemplate.query("select * from system_user",new UserRowMapper());    }    @Override    public String saveUser(SystemUser user) {        final String sql = "insert into system_user(uuid,username,password,isRoot,registerDate,status) values(?,?,?,?,?,?)";        user.setUuid(UUID.randomUUID().toString().replace("-",""));        jdbcTemplate.update(new PreparedStatementCreator() {            @Override            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {                PreparedStatement ps = connection.prepareStatement(sql);                ps.setString(1, user.getUuid());                ps.setString(2, user.getUsername());                ps.setString(3,user.getPassword());                ps.setString(4,user.getIsRoot());                ps.setDate(5,new java.sql.Date(user.getRegisterDate().getTime()));                ps.setInt(6,user.getStatus());                return ps;            }        });        return user.getUuid();    }    @Override    public void deleteUserByUUID(String uuid) {        jdbcTemplate.update("delete from system_user where uuid=?",                new Object[]{uuid},                new int[]{Types.VARCHAR});    }    @Override    public void updateUser(SystemUser user) {        jdbcTemplate.update(                "update system_user set password=?,status=? where uuid=?",                new Object[]{user.getPassword(), user.getStatus(), user.getUuid()});    }}

@Service("systemUserService")public class SystemUserServiceImpl implements SystemUserService{    @Autowired    SystemUserDao systemUserDao;    @Transactional(readOnly = true)    @Override    public SystemUser findUserByUsername(String username) {        return systemUserDao.findUserByUsername(username);    }    @Transactional(readOnly = true)    @Override    public List<SystemUser> findAllUser() {        return systemUserDao.findAllUser();    }    @Transactional    @Override    public String saveUser(SystemUser user) {        return systemUserDao.saveUser(user);    }    @Transactional    @Override    public void deleteUserByUUID(String uuid) {        systemUserDao.deleteUserByUUID(uuid);    }    @Transactional    @Override    public void updateUser(SystemUser user) {        systemUserDao.updateUser(user);    }}

解析来，我们来做单元测试，暂时不做业务逻辑处理。

日志级别调整成debug

@RunWith(SpringJUnit4ClassRunner.class)@SpringBootTestpublic class TestJDBC {    Logger logger= LoggerFactory.getLogger(getClass());    @Autowired    SystemUserService systemUserService;    @Test    public void saveUser(){        SystemUser user = new SystemUser();        user.setStatus(0);        user.setRegisterDate(new Date());        user.setPassword("123456");        user.setIsRoot("0");        user.setUsername("testZhang1");       String uuid= systemUserService.saveUser(user);        logger.info("增加成功：uuid：{}",uuid);    }    @Test    public void findUser1(){        SystemUser user = systemUserService.findUserByUsername("testZhang");        logger.info("user:{}",user);    }    @Test    public void findUser2(){        List<SystemUser> list = systemUserService.findAllUser();        logger.info("userlist:{}",list);    }    @Test    public void updateUser(){        SystemUser user = systemUserService.findUserByUsername("testZhang");        user.setPassword("1234567");        user.setStatus(1);        systemUserService.updateUser(user);    }    @Test    public void deleteUser(){        systemUserService.deleteUserByUUID("cbb0759e58114171bb33437c7db2a276");    }

我们举其中一个例子，来看看log 日志

看到log日志中关键信息，表示我们这次写的完全正确。

由此，我来写一个简单的登录案例

<div class="form-bottom">    <form role="form" action="index.html" th:action="@{/admin/login.do}" method="post" class="login-form">        <div class="form-group">            <label class="sr-only" for="form-username">Username</label> <input                type="text" name="username" placeholder="请输入用户名"                class="form-username form-control" id="form-username" />        </div>        <div class="form-group">            <label class="sr-only" for="form-password">Password</label> <input                type="password" name="password" placeholder="请输入密码"                class="form-password form-control" id="form-password" />        </div>        <button type="submit" class="btn">登 陆</button>    </form></div>

看到 action和 th:action了吗？静态的action，（写静态action，只是为了在使用本地html展示的时候，便于看到效果）th:action就是web应用发挥作用的url。

日志级别还是调回 info； debug级别运行application太慢了。Controller中补充登录逻辑代码。随后我们修改一下表单

@AutowiredSystemUserService systemUserService;@RequestMapping("/admin/login.do")public String adminLoginDo(String username, String password, Map<String,Object> map){   SystemUser systemUser= systemUserService.findUserByUsername(username);    if(null==systemUser){        map.put("loginResult","用户名不存在");        return  "admin/login";    }else if(!password.equals(systemUser.getPassword())){        map.put("loginResult","密码错误");        return  "admin/login";    }else{        // 登录成功！        return "admin/index";    }}

<form role="form" action="index.html" th:action="@{/admin/login.do}" method="post" class="login-form">    <div class="form-group">        <span th:text="${loginResult}" style="color: red"></span>    </div>    <div class="form-group">        <label class="sr-only" for="form-username">Username</label> <input            type="text" name="username" placeholder="请输入用户名"            class="form-username form-control" id="form-username" />    </div>    <div class="form-group">        <label class="sr-only" for="form-password">Password</label> <input            type="password" name="password" placeholder="请输入密码"            class="form-password form-control" id="form-password" />    </div>    <button type="submit" class="btn">登 陆</button></form>

这样，一个简单的登录逻辑功能，就使用 spring boot 完成了。本篇博客到此结束。

阅读全文

1 0