@Test
public void testQuerySQLParamCleaner() {
// sql 有条件查询
SQLParamCleaner.FormatParam formatParam =
SQLParamCleaner.formatSql("select * from mm_incoterm where description = :description OR code like :code ",
Map.of("description", "work", "code", "CI")
);
List<Incoterm> incotermList = baseService.getTableDAO().select(Incoterm.class, formatParam.formatSql(), formatParam.formatMap());
print(incotermList);
System.out.println("-------------------");
formatParam =
SQLParamCleaner.formatSql("description = :description AND code like :code",
Map.of("description", "work"),
true // 如果没有值,条件设置NULL,这里没有给 code 值,formatSql = description = :description AND code IS NULL
);
// condition 有条件查询
incotermList = baseService.select(formatParam.formatSql(), formatParam.formatMap());
print(incotermList);
}
private void print(List<Incoterm> incotermList) {
for (Incoterm incoterm : incotermList) {
System.out.println(incoterm.getCode() + ":" + incoterm.getDescription());
}
}
sharp-database 添加多数据源
手动配置后, JdbcTemplateAutoConfiguration,将不会自动注入,所有的数据源都需要自行注入管理!!!
配置类
@Configuration
public class PostgresConfig {
@Bean
public TableDAO postgresTableDAO(NamedParameterJdbcTemplate postgresNamedParameterJdbcTemplate) {
return new TableDAOImpl(postgresNamedParameterJdbcTemplate);
}
@Bean
public DataSource postgresDataSource() {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("org.postgresql.Driver");
dataSource.setUrl("jdbc:postgresql://127.0.0.1:5432/test");
dataSource.setUsername("postgres");
dataSource.setPassword("123");
return dataSource;
}
@Bean
public NamedParameterJdbcTemplate postgresNamedParameterJdbcTemplate(DataSource postgresDataSource) {
return new NamedParameterJdbcTemplate(postgresDataSource);
}
@Bean
public TableGenerator postgresTableGenerator(NamedParameterJdbcTemplate postgresNamedParameterJdbcTemplate) {
return new PostgresSQLTableGenerator(postgresNamedParameterJdbcTemplate.getJdbcTemplate());
}
}
PostgresEntityDAOImpl.java
@FieldDefaults(level = AccessLevel.PRIVATE)
@Validated
public class PostgresEntityDAOImpl<T, ID> extends EntityDAOImpl<T, ID> {
@Resource
@Qualifier("postgresTableDAO")
@Getter
private TableDAO tableDAO;
}
测试
@SpringBootTest
public class PostgresTableDAOTest {
@Autowired
private TableDAO postgresTableDAO;
@Autowired
private ProductDAO productDAO;
@Autowired
private TableGenerator postgresTableGenerator;
@Test
public void testTableGenerator() {
postgresTableGenerator.createTable(Product.class);
}
@Test
public void testPostgresTableDAO() {
productDAO.insert(Product.builder().code("11").description("Product A").build());
}
}
sharp-admin 集成第三方登录
sharp-admin 添加 SSOTokenAuthenticationFilter.java
public class SSOTokenAuthenticationFilter extends OncePerRequestFilter {
@Override
protected void doFilterInternal(HttpServletRequest request, HttpServletResponse response, FilterChain chain) throws ServletException, IOException {
String header = request.getHeader("Authorization");
String accessToken;
if (header == null || !header.startsWith("Bearer ")) {
accessToken = request.getParameter("access_token");
} else {
accessToken = header.substring(7);
}
if (StringUtils.isBlank(accessToken)) {
chain.doFilter(request, response);
return;
}
try {
SecurityContext securityContext = (SecurityContext) request.getSession().getAttribute("SPRING_SECURITY_CONTEXT");
if (Objects.isNull(securityContext)) {
// 根据 accessToken 解析用户信息和权限信息 构造 authentication
String code = "";
UserDAO userDAO = WebApplicationContextUtils.getWebApplicationContext(request.getServletContext()).getBean(UserDAO.class);
User user = userDAO.selectByCode(code).get();
user.setPassword(accessToken);
AdminUserDetails userDetails = new AdminUserDetails(user, AuthorityUtils.createAuthorityList("ROLE_STUDENT"));
Authentication authentication = UsernamePasswordAuthenticationToken.authenticated(userDetails, userDetails.getPassword(), userDetails.getAuthorities());
request.getSession().setAttribute("SPRING_SECURITY_CONTEXT", authentication);
SecurityContextHolder.getContext().setAuthentication(authentication);
}
chain.doFilter(request, response);
} catch (Exception e) {
response.setStatus(HttpStatus.FORBIDDEN.value());
HttpServletResponseUtils.write(response, "application/json;charset=UTF-8"
, JsonUtils.toJson(ResultUtils.fail(HttpStatus.FORBIDDEN.value(), e.getMessage())));
e.printStackTrace();
}
}
}
WebSecurityConfig.java 注册 SSOTokenAuthenticationFilter.java
http.addFilterBefore(new SSOTokenAuthenticationFilter(), UsernamePasswordAuthenticationFilter.class);
sharp-admin 如何登录到其他 App
希望用户登录 sharp-admin 后,转发到 App 携带 jsessionid ,App 获取 jsessionid , 获取用户的登录信息(类似 SSOTokenAuthenticationFilter.java 的处理流程)
比如登录 sharp-admin 后,获取jsessionid=abc,那么跳转到 App 需要把 jsessionid=abc 加入到 url 中,做地址重写。http://app.com;jsessionid=abc app获取请求中的 jsessionid ,通过接口 /api/user-info;jsessionid=abc获取用户信息,然后做后续的授权操作。
sharp-admin 获取当前用户的信息
/**
* 获取当前登录用户的信息
* @return
*/
@GetMapping("api/user-info")
@ResponseBody
public User userInfo() {
User user = UserContextHolder.get();
return user;
}
/api/user-info;jsessionid=abc jsessionid 是通过路径参数传递的。
/api/user-info 不需要认证
.antMatchers("/api/user-info").permitAll()
MvcConfig.java
@Bean
public HttpFirewall getHttpFirewall() {
StrictHttpFirewall strictHttpFirewall = new StrictHttpFirewall();
strictHttpFirewall.setAllowSemicolon(true); // 允许;
return strictHttpFirewall;
}
注意:如果 cookies 和 地址栏 jsessionid 同时提交,那么 cookies 中的 JSESSIONID 会覆盖地址栏中的 jsessionid,测试的时候如果用浏览器测试地址栏就会被覆盖,postman 测试需要检查 cookies 是否携带了JSESSIONID。大多数情况下,地址栏 jsessionid 的使用,都是禁用 cookie 的情况下。jsessionid 也会有一些安全风险。
sharp-database 根据实体对象获取 insert 语句
根据实体类 t 序列化成 insert 语句存储。使用的场景有很多,比如对象的快照,对象的复制等。
@Test
public void getInsertSql() {
Material material = materialDAO.selectByParamsWithoutCascade(Material.builder().id(729584355378208768L).build()).get(0);
// String insertSQL = materialDAO.getInsertSQL(material);
// System.out.println(insertSQL);
material.setId(null); // 忽略 id 字段
String insertSQL1 = EntityDAOManager.getEntityDAO(Material.class).getInsertSQL(material);
System.out.println(insertSQL1);
}
文件备份到 OSS
- 配置环境变量
vim ~/.bash_profile
export OSS_ACCESS_KEY_ID=
export OSS_ACCESS_KEY_SECRET=
source ~/.bash_profile
- 程序
upload.py
# -*- coding: utf-8 -*-
import glob
import os
import oss2
from oss2.credentials import EnvironmentVariableCredentialsProvider
directory = '/usr/local/projects/sharp-admin/mysqldump/'
files = glob.glob(os.path.join(directory, 'backup-*.sql'))
if not files:
print("没有找到备份文件")
else:
latest_file = max(files, key=os.path.getmtime)
print("最新的备份文件是:", latest_file)
# 从环境变量中获取访问凭证。运行本代码示例之前,请确保已设置环境变量OSS_ACCESS_KEY_ID和OSS_ACCESS_KEY_SECRET。
auth = oss2.ProviderAuthV4(EnvironmentVariableCredentialsProvider())
# 填写Bucket所在地域对应的Endpoint。以华东1(杭州)为例,Endpoint填写为https://oss-cn-hangzhou.aliyuncs.com。
endpoint = "https://oss-cn-beijing.aliyuncs.com"
# 填写Endpoint对应的Region信息,例如cn-hangzhou。注意,v4签名下,必须填写该参数
region = "cn-beijing"
# 填写Bucket名称,例如examplebucket。
bucketName = "sqldump-all"
# 创建Bucket实例,指定存储空间的名称和Region信息。
bucket = oss2.Bucket(auth, endpoint, bucketName, region=region)
# 使用put_object_from_file方法将本地文件上传至OSS
bucket.put_object_from_file("py/" + os.path.basename(latest_file), latest_file)
- 安装依赖
python3 -m pip install oss2
- 运行
python3 upload.py
- 如果定时运行的话,需要带上环境变量,cron 默认使用的是「精简的 shell 环境」,不会加载你的用户登录配置文件(如 .bash_profile, .bashrc)
40 23 * * * bash -c 'source ~/.bash_profile && /usr/bin/python3 /usr/local/projects/sharp-admin/upload.py'