1.整合增删改查集合类
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Component
public class MysqlMake {
private String myTable;
private String myPrefix="ly_";
private String mySqlStr="";
private String myFiled="*";
@Autowired
JdbcTemplate jdbcTemplate;
public MysqlMake table(String table){
myTable = myPrefix+table;
return this;
}
public MysqlMake field(String field){
myFiled = field;
return this;
}
// where 条件判断
public MysqlMake where(String where){
if(where != null){
int intIndex = mySqlStr.indexOf("where");
if(intIndex == - 1){
mySqlStr = mySqlStr + " where "+where;
}else{
mySqlStr = mySqlStr + " "+where;
}
}
return this;
}
public MysqlMake where(String key,String value){
if (key != null && value != null) {
int intIndex = mySqlStr.indexOf("where");
if (intIndex == -1) {
mySqlStr = mySqlStr + " where " + key + "=\"" + value + "\" ";
} else {
mySqlStr = mySqlStr + " and " + key + "=\"" + value + "\" ";
}
}
return this;
}
public MysqlMake where(String key,String eq,String value){
if (key != null && eq != null && value != null) {
int intIndex = mySqlStr.indexOf("where");
if (intIndex == -1) {
mySqlStr = mySqlStr + " where " + key + eq + "\"" + value + "\" ";
} else {
mySqlStr = mySqlStr + " and " + key + eq + "\"" + value + "\" ";
}
}
return this;
}
// Where IN in查询
public MysqlMake whereIn(String key,String value){
if(key != null && value != null){
int intIndex = mySqlStr.indexOf("where");
if (intIndex == -1) {
mySqlStr = mySqlStr + " where " + key + " in ("+value+")";
}else{
mySqlStr = mySqlStr + " and " + key + " in ("+value+")";
}
}
return this;
}
// whereBetween Between查询
public MysqlMake whereBetween(String key,String start,String end){
if (key != null && start != null && end != null) {
int intIndex = mySqlStr.indexOf("where");
if (intIndex == -1) {
mySqlStr = mySqlStr + " where " + key + " between " + start + " and " + end;
}else{
mySqlStr = mySqlStr + " and " + key + " between " + start + " and " + end;
}
}
return this;
}
// 链表查询 左链表
public MysqlMake leftJoin(String table,String str){
if(table != null && str != null ){
mySqlStr = mySqlStr + " left join "+myPrefix+table+" on "+str;
}
return this;
}
// 排序
public MysqlMake orderBy(String order,String sort){
mySqlStr = mySqlStr + " order by "+order+" "+sort+" ";
return this;
}
//查询数量
public String count(){
String sql = "select count(" + myFiled + ") from " + myTable + mySqlStr;
cleanInit();
return sql;
}
// 分页查询总条数
public Integer pageTotalCount(){
String sql2 = "select count(*) from " + myTable + mySqlStr;
try {
// 查询总数
int total= jdbcTemplate.queryForObject(sql2,new Object[]{},Integer.class);
return total;
} catch (Exception e) {
e.printStackTrace();
return 0;
}
}
// 分页查询
public HashMap<String, Object> page(Integer pageNum , Integer pageSize){
// 总条数
Integer total = pageTotalCount();
System.out.println(total);
// 构造查询语句
Integer startNum = pageNum>0 ? (pageNum - 1) : 0;
Integer pageStart = startNum * pageSize;//提取分页开始索引
String sql = "select "+ myFiled + " from "+ myTable + mySqlStr + " limit "+pageStart+","+pageSize+" ";
System.out.println(sql);
// 置空
cleanInit();
try {
HashMap<String, Object> result = new HashMap<String, Object>();
List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
if(list != null && list.isEmpty() == false){
// 总页数
Integer pages = (int) Math.ceil(total/pageSize);
result.put("data",list);
result.put("pages",pages);
result.put("last_page",pages);
result.put("total",total);
System.out.println(result);
return result;
}
return null;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
// 列表查询
public List<Map<String, Object>> select(){
String sql = "select " + myFiled + " from " + myTable + mySqlStr;
cleanInit();
try {
List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
if(list != null && !list.isEmpty()){
return list;
}
return null;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
// 单条查询
public Map<String, Object> find(){
// sql语句
String sql = "select " + myFiled + " from " + myTable + mySqlStr+ " limit 0,1 ";
System.out.println(sql);
cleanInit();
try {
List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
if(list != null && !list.isEmpty()){
Map<String, Object> info = list.get(0);
return info;
}
return null;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
// 单条单字段查询
public String findOne(){
// sql语句
String sql = "select " + myFiled + " from " + myTable + mySqlStr+ " limit 0,1 ";
cleanInit();
try {
String info = jdbcTemplate.queryForObject(sql,new Object[]{},String.class);
return info;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
// 插入数据
public Integer insert(Map<String, Object> dataMap){
if(dataMap != null && !dataMap.isEmpty()){
// 拼接插入参数
String keyStr = "";
String valueStr = "";
for(Map.Entry<String, Object> entry : dataMap.entrySet()){
String mapKey = entry.getKey();
String mapValue = entry.getValue().toString();
keyStr = keyStr + mapKey + ",";
valueStr = valueStr + "\"" + mapValue + "\",";
}
// 删除拼接字符串最后的逗号
keyStr = keyStr.substring(0,keyStr.length() - 1);
valueStr = valueStr.substring(0,valueStr.length() - 1);
// 插入sql 语句
String sql = "insert " + myTable + " ("+keyStr+") values ("+valueStr+")";
cleanInit();
try {
// 插入数据后 获取自增ID
KeyHolder keyHolder = new GeneratedKeyHolder();
PreparedStatementCreator preparedStatementCreator = con -> {
PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
return ps;
};
jdbcTemplate.update(preparedStatementCreator,keyHolder);
// 获取自增ID
return keyHolder.getKey().intValue();
} catch (Exception e) {
e.printStackTrace();
return 0;
}
}
return 0;
}
// 删除数据
public Integer delete(){
// 更新sql语句
String sql = "delete from " + myTable + mySqlStr;
cleanInit();
try {
// 可以成功更新数据库
Integer info = jdbcTemplate.update(sql);
return info;
} catch (Exception e) {
e.printStackTrace();
return 0;
}
}
// 更新数据 单个字段更新
public Integer updateOne(String key,String value){
// 更新sql语句
String sql = "update " + myTable + " SET " + key + "="+ value + mySqlStr;
cleanInit();
try {
// 可以成功更新数据库
Integer info = jdbcTemplate.update(sql);
return info;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
// 更新数据 整条数据更新
public Integer update(Map<String, String> dataMap){
if(dataMap !=null && !dataMap.isEmpty()){
// 拼接更新参数
String sqlStr = null;
for(Map.Entry<String, String> entry : dataMap.entrySet()){
String mapKey = entry.getKey();
String mapValue = entry.getValue();
sqlStr = sqlStr + mapKey + "=\"" + mapValue + "\",";
}
// 更新sql语句
String sql = "update " + myTable + " SET " + sqlStr + mySqlStr;
cleanInit();
try {
// 执行
Integer info = jdbcTemplate.update(sql);
return info;
} catch (Exception e) {
e.printStackTrace();
return 0;
}
}
return 0;
}
// 字段数据自增
public Integer increment(String key,String value){
// 更新sql语句
String sql = "update " + myTable + " SET "+key+"="+key+"+"+value+mySqlStr;
System.out.println(sql);
cleanInit();
try {
// 执行
Integer info = jdbcTemplate.update(sql);
return info;
} catch (Exception e) {
e.printStackTrace();
return 0;
}
}
// 字段数据自减
public Integer decrement(String key,String value){
// 更新sql语句
String sql = "update " + myTable + " SET "+key+"="+key+"-"+value+mySqlStr;
cleanInit();
try {
// 执行
Integer info = jdbcTemplate.update(sql);
return info;
} catch (Exception e) {
e.printStackTrace();
return 0;
}
}
// 置空还原数据
public Boolean cleanInit(){
myTable = "";
myFiled = "*";
mySqlStr = "";
return true;
}
}
2.使用
// 引入
@Resource
MysqlMake mysqlMake;
// 查询单个字段
String info = mysqlMake.table("account").field("money").where("account_id" , "1").findOne();
// 单条数据查询
Map<String, Object> info = mysqlMake.table("account").where("account_id" , "1").find();
// 分页查询
Map<String, Object> list = mysqlMake.table("account_money_change as a")
.field("a.money,a.created_at,n.nfc_sn,n.type")
.leftJoin("account as ac", "ac.id=a.account_id")
.leftJoin("account as at", "at.id=a.to_account_id")
.leftJoin("user_nfc as n", "n.id=at.account_id")
.where("a.consume_type","13")
.whereIn("a.account_id",type)
.whereBetween("a.created_at",startTime,endTime)
.orderBy("a.id","desc")
.page(pageNum,pageSize);
if(list !=null){
// 修改数据
List<HashMap<String, Object>> lista = (List<HashMap<String, Object>>) list.get("data");
for (Map<String, Object> info : lista) {
String newTime = DateUtil.timeStamp2Date(info.get("created_at").toString(), "yyyy-MM-dd");
info.put("created_at",newTime);
}
}
// 数据写入
String nowTime = DateUtil.timeStamp();
// 构造写入参数
Map<String, Object> info = new HashMap<String, Object>();
info.put("consume_type",consume_type);
info.put("name",getMoneyChangeName(consume_type.toString()));
info.put("account_id",account_id);
info.put("money",-money);
info.put("to_account_id",to_account_id);
info.put("order_id",order_id);
info.put("created_at",nowTime);
info.put("updated_at",nowTime);
// 写入数据 并获取ID
Integer account_change_id = mysqlMake.table("account_money_change").insert(info);
// 数据更新
// 出账后金额减少
mysqlMake.table("account").where("id",account_id.toString()).decrement("money",money.toString());
// 出账后更新出账后的金额
mysqlMake.table("account_money_change").where("id",account_change_id.toString()).updateOne("money_after",money_after.toString());
网友评论