JDBCTemPlate的增删改查
总结下最近项目用到的JDBCTemplate的增删改查用到的方法
1、update()方法 【此方法主要用于新增插入一条记录、或者进行修改】
a、新增一条记录, 这里用到org.springframework.jdbc.core.JdbcTemplate包下封装的JdbcTemplate类
jdbctemplate.update(sql,?,?,?,?)
第一种语法：
public boolean insertOrderLog(Integer OrdId, String LogType, String Content, String UserId, String SalesId, Integer CurrentOrderStatus, String OperateTime) { String sql = "insert into F_OD_OrderLog (OrdId,LogType,Content,UserId,SalesId,CurrentOrderStatus,OperateTime) values(?,?,?,?,?,?,?)"; Integer count = jdbcTemplate.update(sql, OrdId, LogType, Content, UserId, SalesId, CurrentOrderStatus, OperateTime);
第二种语法：
第二种可获得当前表插入的最新主键
1 String sql = "insert into F_OD_CompanyContacts(IdCardPicFrontUrl,IdCardPicReverseUrl,CompId,FullName,LicenseNumber,Address,MobilePhone,Email) values(?,?,?,?,?,?,?,?)"; 2 // i = jdbcTemplate.update(sql, IdCardPicFrontUrl, IdCardPicReverseUrl, compId, FullName, LicenseNumber, Address, MobilePhone, Email, 1); 3 GeneratedKeyHolder keyHolder = new GeneratedKeyHolder(); 4 jdbcTemplate.update(new PreparedStatementCreator() { 5 public PreparedStatement createPreparedStatement(Connection conn) throws SQLException { 6 int i = 0; 7 java.sql.PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); 8 ps.setObject(++i, IdCardPicFrontUrl); 9 ps.setObject(++i, IdCardPicReverseUrl); 10 ps.setInt(++i, compId); 11 ps.setString(++i, FullName); 12 ps.setString(++i, LicenseNumber); 13 ps.setString(++i, Address); 14 ps.setString(++i, MobilePhone); 15 ps.setString(++i, Email); 16 return ps; 17 } 18 }, 19 keyHolder); 20 Integer FiId = keyHolder.getKey().intValue();
b、修改
//更改数据 switch (Ispoeple){ case 1: //法人联系人相同 String sqq="update F_LYJ_SanWangNetLog set isIdCard=?,isMobilePhone=?,ispoeple=? where ordId=?"; result= jdbcTemplate.update(sqq, map.get("isIdCard"), map.get("isMobilePhone"),Ispoeple ,ordId); break; case 2: //法人联系人不相同 String sqp="update F_LYJ_SanWangNetLog set isIdCard=?,isMobilePhone=?,isConFullName=?,isConLicenseNumber=?,isConMobilePhone=?,ispoeple=?, where ordId=?"; result= jdbcTemplate.update(sqp, map.get("isIdCard"), map.get("isMobilePhone"),map.get("isConFullName"),map.get("isConLicenseNumber"),map.get("isConMobilePhone"), Ispoeple,ordId); break; } return result;
2、queryforObject()方法 【此方法主要用于查询一条记录字段数据，或者一个count(*)聚合记录】
a、查询一条记录或者多个字段
//查找当前法人,联系人主键 String sqq = " SELECT fcl.LegalId,fcc.id FROM F_OD_OrderInfo fo LEFT JOIN F_OD_CompanyContacts fcc ON fo.FiId=fcc.id LEFT JOIN \n" + " F_OD_CompanyLegal fcl ON fo.LegalId=fcl.LegalId WHERE fo.`OrdId`=?"; Map map = jdbcTemplate.queryForObject(sqq, new Object[]{ordId}, new RowMapper<Map<String, String>>() { @Override public Map<String, String> mapRow(ResultSet resultSet, int i) throws SQLException { HashMap<String, String> map = new HashMap<>(); map.put("LegalId", resultSet.getString("LegalId")); map.put("id", resultSet.getString("id")); return map; } });
b、查询记录数
String sql="select count(*) from F_LYJ_SanWangNetLog where ordId=?"; Integer count = jdbcTemplate.queryForObject(sql, new Object[]{ordId}, Integer.class);
3、query()方法 【此方法主要用于多表联查，可查询多条list记录】
1 SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 2 List<Map<String, String>> list = jdbcTemplate.query(sql, obj, (rs, rowNum) -> { 3 boolean flag = false; 4 Map<String, String> m = new HashMap<>(); 5 m.put("ordNum", rs.getString("OrdNum")); 6 m.put("OrdId", rs.getString("OrdId")); 7 m.put("GoodsServiceName", rs.getString("ServiceName")); 8 m.put("relationId", rs.getString("RelationId")); 9 m.put("onlinePayMoney", rs.getString("OnlinePayMoney")); 10 m.put("payment", rs.getString("Payment")); 11 m.put("ordStatus", rs.getString("OrdStatus")); 12 m.put("createTime", rs.getTimestamp("CreateTime") == null ? "" : simpleDateFormat.format(rs.getTimestamp("CreateTime"))); 13 m.put("salesName", rs.getString("SalesName")); 14 m.put("compName", rs.getString("CompName")); 15 m.put("EASCompanyName", rs.getString("EASCompanyName")); 16 m.put("ContactNumber", rs.getString("Phone")); 17 m.put("OrdKind", rs.getString("OrdKind")); 18 m.put("OrdType", rs.getString("OrdType")); 19 m.put("RenewedTime", rs.getString("RenewedTime")); 20 m.put("ReletId", rs.getString("ReletId")); 21 m.put("EasContractNum", rs.getString("EasContractNum")); 22 if (null != rs.getString("RenewedTime")) { 23 m.put("RenewedTimeStatus", "success"); 24 flag = true; 25 } else { 26 m.put("RenewedTimeStatus", "waring"); 27 } 28 m.put("PaymentTime", rs.getString("PaymentTime")); 29 if (null != rs.getString("PaymentTime")) { 30 m.put("PaymentTimeStatus", "success"); 31 flag = true; 32 } else { 33 if (flag) { 34 m.put("PaymentTimeStatus", "waring"); 35 flag = false; 36 } else { 37 m.put("PaymentTimeStatus", ""); 38 } 39 } 40 m.put("LetterTime", rs.getString("LetterTime")); 41 if (null != rs.getString("LetterTime")) { 42 m.put("LetterTimeStatus", "success"); 43 } else { 44 if (flag) { 45 m.put("LetterTimeStatus", "waring"); 46 flag = false; 47 } else { 48 m.put("LetterTimeStatus", ""); 49 } 50 51 } 52 m.put("AoCransferTime", rs.getString("AoCransferTime")); 53 if (null != rs.getString("AoCransferTime")) { 54 m.put("AoCransferTimeStatus", "success"); 55 } else { 56 if (flag) { 57 m.put("AoCransferTimeStatus", "waring"); 58 flag = false; 59 } else { 60 m.put("AoCransferTimeStatus", ""); 61 } 62 } 63 m.put("SigintureTime", rs.getString("SigintureTime")); 64 if (null != rs.getString("SigintureTime")) { 65 m.put("SigintureTimeStatus", "success"); 66 } else { 67 if (flag) { 68 m.put("SigintureTimeStatus", "waring"); 69 flag = false; 70 } else { 71 m.put("SigintureTimeStatus", ""); 72 } 73 } 74 m.put("OurSigintureTime", rs.getString("OurSigintureTime")); 75 if (null != rs.getString("OurSigintureTime")) { 76 m.put("OurSigintureTimeStatus", "success"); 77 } else { 78 if (flag) { 79 m.put("OurSigintureTimeStatus", "waring"); 80 flag = false; 81 } else { 82 m.put("OurSigintureTimeStatus", ""); 83 } 84 } 85 m.put("HandoverTime", rs.getString("HandoverTime")); 86 if (null != rs.getString("HandoverTime")) { 87 m.put("HandoverTimeStatus", "success"); 88 } else { 89 if (flag) { 90 m.put("HandoverTimeStatus", "waring"); 91 flag = false; 92 } else { 93 m.put("HandoverTimeStatus", ""); 94 } 95 } 96 m.put("ComId", rs.getString("ComId")); 97 return m;
4、查询中取数据库特殊时间字段，这里不能用getStrig 或者getdata（getdata不能取完整时间）
1 Object[] obj = objList.toArray(); 2 SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 3 4 List<Map<String, String>> list = jdbcTemplate.query(sql, obj, (rs, rowNum) -> { 5 Map<String, String> m = new HashMap<>(); 8 m.put("RefoundOrderNumber", rs.getString("RefoundOrderNumber")); 12 m.put("StartingTime", rs.getTimestamp("StartingTime") == null ? "" : simpleDateFormat.format(rs.getTimestamp("StartingTime"))); 13 m.put("ContinueTime", rs.getTimestamp("ContinueTime") == null ? "" : simpleDateFormat.format(rs.getTimestamp("ContinueTime"))); 15 m.put("salesName", rs.getString("SalesName")); 16 m.put("HireContractNumber", rs.getString("HireContractNumber")); 17 m.put("Initiator", rs.getString("Initiator")); 18 m.put("reletReason", rs.getString("ReletReason")); 19 m.put("compName", rs.getString("CompName")); 20 m.put("EASCompanyName", rs.getString("EASCompanyName")); 21 m.put("reletContractStatus", rs.getString("ReletContractStatus")); 26 return m;
