Jdbc
jdk提供的最基础的访问数据库的方式
package com.sjcredit.orm.jdbc;
import org.springframework.util.StringUtils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
/**
* @author dingyn
* @description JdbcDemo类,jdk提供的最基础的访问数据库的方式
* @create 2021-07-22 19:15
*/
public class JdbcDemo {
public static final String URL = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&serverTimezone=GMT%2B8";
public static final String USER = "root";
public static final String PASSWORD = "admin123";
public static void main(String[] args) throws Exception {
//1.加载驱动程序
Class.forName("com.mysql.cj.jdbc.Driver");
//2. 获得数据库连接
Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
//3.操作数据库,实现增删改查
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM country where name ='China'");
//如果有数据,rs.next()返回true
while (rs.next()) {
System.out.println(rs.getString("name") + " region:" + rs.getString("region"));
}
// 需要手动关闭连接
if (!StringUtils.isEmpty(rs)) {
rs.close();
}
if (StringUtils.isEmpty(conn)) {
conn.close();
}
}
}
JdbcTemplate
简介
JdbcTemplate是Spring对JDBC的封装,目的是使JDBC更加易于使用
JdbcTemplate是Spring的一部分。JdbcTemplate处理了资源的建立和释放。
他帮助我们避免一些常见的错误,比如忘了总要关闭连接。
pom
<!--JdbcTemplate-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
demo
package com.sjcredit.management.demo;
import com.sjcredit.management.demo.entity.BizAuthEntity;
import com.sjcredit.management.demo.entity.SiPayinfoEntity;
import lombok.RequiredArgsConstructor;
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.stereotype.Repository;
import java.util.*;
/**
* @author dingyn
* @description JdbcTemplateDemo
* @create 2022-02-07 9:46
*/
@Repository
/**
* 生成必须参数的构造方法
*/
@RequiredArgsConstructor
public class JdbcTemplateDemo {
private final JdbcTemplate jdbcTemplate;
private final NamedParameterJdbcTemplate namedParameterJdbcTemplate;
/**
* @Description: insert 对象
* @Param: [bizAuthEntity]
* @return: int
* @Author: dingyn
* @Date: 2022/2/7 9:47
*/
public int insert(BizAuthEntity bizAuthEntity) {
String sql = "insert into biz_auth (id,auth_no,corp_id,start_date,approval_status,approval_desc,dead_line,create_by,create_time,update_by,update_time,auth_org_code,file_name)" +
"values (:id, :authNo, :corpId, :startDate, :approvalStatus, :approvalDesc, :deadLine, :createBy, :createTime, :updateBy, :updateTime, :authOrgCode, :fileName)";
int count = namedParameterJdbcTemplate.update(sql, new BeanPropertySqlParameterSource(bizAuthEntity));
return count;
}
/**
* @Description: insert 参数
* @Param: [corpName, year, param]
* @return: int
* @Author: dingyn
* @Date: 2022/2/7 14:19
*/
public int insert(String corpName, String year, Double param) {
String sql = "INSERT INTO score_result VALUES (?,?,?)";
int result = jdbcTemplate.update(sql, corpName, year, param == null ? 0.00 : param);
return result;
}
/**
* @Description: delete 通过条件删除
* @Param: [corpId]
* @return: java.lang.Integer
* @Author: dingyn
* @Date: 2022/2/7 9:48
*/
public Integer deleteBy(String corpId) {
String sql = "DELETE from biz_auth where corp_id = ?";
return jdbcTemplate.update(sql, corpId);
}
/**
* @Description: update
* @Param: [deadLine, id]
* @return: int
* @Author: dingyn
* @Date: 2022/2/7 10:29
*/
public int update(Date deadLine, String id) {
String sql = "UPDATE biz_auth SET dead_line = ? where id = ?";
int update = jdbcTemplate.update(sql, deadLine, id);
return update;
}
/**
* @Description: select 返回对象
* @Param: [orgCode, corpName, corpCode]
* @return: com.sjcredit.ccbsiapi.entity.BizAuthEntity
* @Author: dingyn
* @Date: 2022/2/7 9:48
*/
public BizAuthEntity selectObject(String orgCode, String corpName, String corpCode) {
String sql = "select auth.* from biz_auth auth left join biz_corp corp on auth.corp_id = corp.id " +
"where auth.auth_org_code = ? and (corp.corp_name = ? or corp.corp_code = ?) ";
BizAuthEntity bizAuthEntity = null;
//查询结果为空的时候会报异常
try {
bizAuthEntity = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(BizAuthEntity.class), orgCode, corpName, corpCode);
} catch (Exception e) {
}
return bizAuthEntity;
}
/**
* @Description: select 返回字符串
* @Param: [orgCode, corpName, corpCode]
* @return: java.lang.String
* @Author: dingyn
* @Date: 2022/2/7 9:49
*/
public String selectString(String orgCode, String corpName, String corpCode) {
String sql = "select corp.id from biz_corp corp left join biz_auth auth on auth.corp_id = corp.id " +
"where auth.auth_org_code = ? and (corp.corp_name = ? or corp.corp_code = ?)";
return jdbcTemplate.queryForObject(sql, String.class, orgCode, corpName, corpCode);
}
/**
* @Description: select 返回list
* 查询list的,如果查询到0条数据,则不会报错,会返回一个包含0个元素的list对象,所以这里不需要捕获异常
* @Param: [dwmc, tyshxydm]
* @return: java.util.List<com.sjcredit.ccbsiapi.entity.SiPayinfoEntity>
* @Author: dingyn
* @Date: 2022/2/7 9:50
*/
public List<SiPayinfoEntity> selectEntityList(String dwmc, String tyshxydm) {
String sql = "SELECT * FROM si_payinfo WHERE dwmc = ? and tyshxydm = ?";
List<SiPayinfoEntity> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(SiPayinfoEntity.class), dwmc, tyshxydm);
return list;
}
public List<String> selectStringList(String ruleId) {
String sql = "SELECT name FROM pub_rule_detail where rule_id = ? and status = 1";
List<String> columnList = jdbcTemplate.queryForList(sql, String.class, ruleId);
return columnList;
}
/**
* @Description: select 返回map list
* @Param: [dwmc, tyshxydm]
* @return: java.util.List<java.util.Map < java.lang.String, java.lang.Object>>
* @Author: dingyn
* @Date: 2022/2/7 10:29
*/
public List<Map<String, Object>> selectMapList(String dwmc, String tyshxydm) {
String sql = "SELECT * FROM si_latefeeinfo WHERE dwmc = ? and tyshxydm = ?";
List<Map<String, Object>> listResult = jdbcTemplate.query(sql, (resultSet, i) -> {
Map<String, Object> map = new HashMap<>();
map.put("TYSHXYDM", resultSet.getString("TYSHXYDM"));
map.put("DWMC", resultSet.getString("DWMC"));
map.put("JFSSQQ", resultSet.getString("JFSSQQ"));
map.put("JFSSQZ", resultSet.getString("JFSSQZ"));
map.put("ZNJ", resultSet.getBigDecimal("ZNJ"));
return map;
}, dwmc, tyshxydm);
return listResult;
}
/**
* @Description: select 返回Object list 传入多个参数或list
* @Param: [dwmc, tyshxydm]
* @return: java.util.List<java.lang.Object>
* @Author: dingyn
* @Date: 2022/2/7 15:28
*/
public List<Object> selectObjectList() {
String sql = "SELECT * FROM si_latefeeinfo WHERE dwmc = :dwmc and tyshxydm = :tyshxydm";
List<String> ids = new ArrayList<>();
Map<String, Object> paramMap = new HashMap<>();
paramMap.put("corp", "corp_name");
paramMap.put("ids", ids);
NamedParameterJdbcTemplate jdbc = new NamedParameterJdbcTemplate(jdbcTemplate);
List<Object> listResult = jdbc.query(sql, paramMap, (resultSet, i) -> {
Map<String, String> map = new HashMap<>();
map.put("corp", resultSet.getString("corp_name"));
map.put("code", resultSet.getString("corp_code"));
return map;
});
return listResult;
}
/**
* @Description: execute
* @Param: []
* @return: void
* @Author: dingyn
* @Date: 2022/2/7 10:41
*/
public void execute() {
String sql = "commit";
jdbcTemplate.execute(sql);
}
}