在实际开发中,一定会需要将资料持久化,常见的持久化技术有Spring 自带的JdbcTemplate
、Spring Boot 提供的JPA
规范以及MyBatis
,这个系列文会逐一介绍上述三种持久化技术,本篇文章先讲最简单的JdbcTemplate
。
JDBC
,全名为Java Database Connectivity,是一种用於执行SQL 语法的Java API,它将资料库存取SQL 语法与Java API 分开,让开发人员开发资料库应用程序更有弹性。
而JdbcTemplate
则是对JDBC
的封装,目的是为了解决JDBC
每次使用都必须重新进行资料库连接、处理SQL 语法、传值、关闭资料库连接等一系列操作所引发的一系列问题,替开发人员完成了所有JDBC 的底层工作,因此使用JdbcTemplate
就不用每次都进行连结、开启、关闭等操作。
batchUpdate
: 批量更新。execute
: 执行SQL 语法。update
: 执行一条插入、更新或删除语法。query
: 查询并返回相应值。queryForList
: 查询并返回一个List。queryForObject
: 查询并返回一个Object。queryForMap
: 查询并返回一个Map。queryForRowSet
: 查询并返回一个RowSet。接下来会实作使用JdbcTemplate
对资料库进行新增、查询、修改等操作,由於修改跟删除都是使用update
方法,这边就不实作了,完整的程序码就放在最下方的Github 连结。
<!-- MySQL Driver -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!-- JDBC API -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
// 设定资料库位址
spring.datasource.url=jdbc:mysql://localhost:3306/test_project?useUnicode=true&characeterEncoding=utf-8&serverTimezone=Asia/Shanghai
// 设定使用者资讯
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
CREATE TABLE IF NOT EXISTS member_account (
ID INT PRIMARY KEY AUTO_INCREMENT COMMENT "会员流水号",
USERNAME VARCHAR(60) UNIQUE NOT NULL COMMENT "帐号",
PASSWORD VARCHAR(60) NOT NULL COMMENT "密码",
SALT VARCHAR(32) NOT NULL COMMENT "盐值",
CREATE_BY VARCHAR(60) NOT NULL COMMENT "建立者",
CREATE_TIME DATETIME NOT NULL COMMENT "建立时间",
UPDATE_BY VARCHAR(60) NOT NULL COMMENT "修改者",
UPDATE_TIME DATETIME NOT NULL COMMENT "修改时间"
)DEFAULT CHARSET=utf8mb4
package com.example.demo.entity;
import lombok.Getter;
import lombok.Setter;
import lombok.ToString;
@Getter
@Setter
@ToString
public class MemberAccount extends Base {
private String id;
private String username;
private String password;
private String salt;
}
package com.example.demo.dao;
import com.example.demo.entity.MemberAccount;
public interface MemberAccountDao {
public Integer insert(MemberAccount memberAccount);
public MemberAccount findMemberAccountByUsername(String username);
public Integer update(MemberAccount memberAccount);
}
package com.example.demo.dao.impl;
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.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import com.example.demo.dao.MemberAccountDao;
import com.example.demo.entity.MemberAccount;
@Repository
public class MemberAccountDaoImpl implements MemberAccountDao {
@Autowired
private JdbcTemplate jdbcTemplate;
@Autowired
private NamedParameterJdbcTemplate jdbcNameTemplate;
@Override
public Integer insert(MemberAccount memberAccount) {
// TODO Auto-generated method stub
String sql = " INSERT INTO test_project.member_account ( "
+ " USERNAME, PASSWORD, SALT, "
+ " CREATE_BY, CREATE_TIME, UPDATE_BY, UPDATE_TIME "
+ " ) "
+ " VALUE ( "
+ " :username, :password, :salt, "
+ " :create_by, NOW(), :update_by, NOW() "
+ " ) ";
SqlParameterSource paramSource = new BeanPropertySqlParameterSource(memberAccount);
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcNameTemplate.update(sql, paramSource, keyHolder);
return keyHolder.getKey().intValue();
}
@Override
public MemberAccount findMemberAccountByUsername(String username) {
// TODO Auto-generated method stub
String sql = " SELECT "
+ " ID, USERNAME, PASSWORD, SALT "
+ " FROM "
+ " test_project.member_account "
+ " WHERE "
+ " USERNAME = ? ";
List<MemberAccount> result = jdbcTemplate.query(sql, new BeanPropertyRowMapper<MemberAccount>(MemberAccount.class), new Object[] { username });
if(result != null && result.size() > 0) {
return result.get(0);
}
return null;
}
@Override
public Integer update(MemberAccount memberAccount) {
// TODO Auto-generated method stub
String sql = " UPDATE "
+ " test_project.member_account "
+ " SET "
+ " PASSWORD = :password, UPDATE_BY = :update_by, UPDATE_TIME = NOW() "
+ " WHERE "
+ " ID = :id ";
SqlParameterSource paramSource = new BeanPropertySqlParameterSource(memberAccount);
return jdbcNameTemplate.update(sql, paramSource);
}
}
package com.example.demo.dao;
import java.util.UUID;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import com.example.demo.entity.MemberAccount;
@SpringBootTest
public class MemberAccountDaoTest {
@Autowired
private MemberAccountDao memberAccountDao;
@Test
public void insert() {
MemberAccount memberAccount = new MemberAccount();
memberAccount.setUsername("[email protected]");
memberAccount.setPassword("password");
String salt = UUID.randomUUID().toString().toUpperCase().replaceAll("-", "");
memberAccount.setSalt(salt);
memberAccount.setCreate_by(memberAccount.getUsername());
memberAccount.setUpdate_by(memberAccount.getUsername());
Integer id = memberAccountDao.insert(memberAccount);
System.out.println(id);
}
@Test
public void findMemberAccountByUsername() {
String username = "[email protected]";
MemberAccount memberAccount = memberAccountDao.findMemberAccountByUsername(username);
if(memberAccount != null) System.out.println(memberAccount.toString());
}
@Test
public void update() {
MemberAccount memberAccount = new MemberAccount();
memberAccount.setId("1");
memberAccount.setPassword("123456");
memberAccount.setUpdate_by("[email protected]");
Integer result = memberAccountDao.update(memberAccount);
System.out.println(result);
}
}
(13) 甚麽是 JDBC、ORM、 JPA、ORM框架、Hibernate
JdbcTemplate (Spring Framework 5.3.10 API)
<<: TailwindCSS 从零开始 - 元件相同时,把共同样式拉出来
>>: Day 11 漏洞分析 - Vulnerability Analysis (nikto)
第十六周噜 专题进行了两周 目前进度就是 刻了一些画面做了一些功能 做好注册与登入功能 在刻画面的时...
【前言】 最後这个 Deploy NFT 才是真正真正真正的大魔王,比我想像中还要难超级多,难到我...
连续 30 天不中断每天上传一支教学影片,教你如何用 React 加上 Firebase 打造社群...
昨天我们提到,明为什麽用 SharePoint 制作的公司入口网站可以组织提升生产力? 这个时候就可...