public class JDBCServiceImpl implements JDBCService {
@Autowired
private JdbcTemplate jdbcTemplate;
@Autowired
private DataSource dataSource;
@Override
public Integer batchDelete(List<Long> ids,String tableName)throws Exception {
String sql = "DELETE FROM "+tableName+" where id=? ";
jdbcTemplate.batchUpdate(sql,
new BatchPreparedStatementSetter(){
public void setValues(PreparedStatement ps, int i)throws SQLException {
ps.setLong(1, ids.get(i));
}
public int getBatchSize() {
return ids.size();
}
});
return 0;
}
/**
* 批量保存
* @param objectList
* @throws Exception
*/
@Override
public void batchSave(List objectList) throws Exception {
String insertSql=createInsertSql(objectList);//创建插入的sql
jdbcTemplate.batchUpdate(insertSql);
}
@Override
public long[] batchSaveRetunKeys(List objs) throws Exception{
long[] ids=new long[objs.size()];
String insertSql=createInsertSql(objs);//创建插入的sql
Statement statement=dataSource.getConnection().createStatement();
int row=statement.executeUpdate(insertSql,Statement.RETURN_GENERATED_KEYS);
ResultSet rs = statement.getGeneratedKeys();
int i=0;
while (rs.next()) {
long key = rs.getLong(1);
ids[i]=key;
i++;
}
return ids;
}
/**
* 创建插入的sql
* @param objectList
* @return
* @throws Exception
*/
private String createInsertSql(List objectList)throws Exception{
if (objectList==null||objectList.size()==0)
throw new BaseException("批量插入的数据不问为空");
Field[] fields=getFields(objectList); //获取所有字段
StringBuilder insertSql=new StringBuilder(" insert "+getTableName(objectList) +"("); //封装插入语句
for(int i=0;i<fields.length;i++){
insertSql.append(i==0?getColumnName(fields[i]):(","+getColumnName(fields[i]))); //列名
}
insertSql.append(") values");//占位符
for (int index=0;index<objectList.size();index++){//给占位符设置
insertSql.append(index==0?"(":",(");
for (int i=0;i<fields.length;i++){
int tt=0;
Object object=getMethod(fields[i],objectList.get(index));//调用get方法获取字段的值
if (i>0)
insertSql.append(",");
insertSql.append(object);
}
insertSql.append(")");
}
return insertSql.toString();
}
/**
* 获取对象的所有字段
* @param objectList
* @return
* @throws Exception
*/
private Field[] getFields(List<Object> objectList)throws Exception{
Object object=objectList.get(0);
return object.getClass().getDeclaredFields();
}
/**
* 调用get方法获取字段的值
* @param filed
* @param object
* @return
* @throws Exception
*/
private Object getMethod(Field filed,Object object)throws Exception{
//封装get方法的名称
String methodName=packGetMethodName(filed);
//获取get方法
Method getMod=object.getClass().getMethod(methodName);
Object obj=getMod.invoke(object);
if (methodName.equals("getCreatedDate")||methodName.equals("getUpdatedDate")&&obj==null)
obj=new Date();
if (obj instanceof Date)
return addDot(DateUtil.getDateToString((Date)obj));
if (obj instanceof String)
return addDot((String)obj);
return obj;
}
/**
* 添加单引号
* @param str
* @return
* @throws Exception
*/
private String addDot(String str)throws Exception{
return "‘"+str+"‘";
}
/**
* 封装get方法的名称
* @param filed
* @return
* @throws Exception
*/
private static String packGetMethodName(Field filed)throws Exception{
String name=filed.getName();
String firstName=name.substring(0,1).toUpperCase();
String methodName="get"+firstName+name.substring(1);
return methodName;
}
/**
* 获取表名
* @param objectList
* @return
* @throws Exception
*/
private static String getTableName(List<Object> objectList)throws Exception{
Object object=objectList.get(0);
Table table=object.getClass().getAnnotation(Table.class);
return table.name();
}
/**
* 获取列名
* @param field
* @return
* @throws Exception
*/
private static String getColumnName(Field field)throws Exception{
Column column=(Column)field.getAnnotation(Column.class);
return column.name();
}
/**
* 判断主键是不是int类型
* @return
*/
private boolean keyIsInt(List objs)throws Exception{
Object obj=objs.get(0);
Field field=obj.getClass().getDeclaredField("id");
if (field.getType().equals(String.class))
return false;
return true;
}
private void setValue(PreparedStatement pstmt,Field field,Object object,int index)throws Exception{
Class clazz=field.getType();
if (clazz.equals(Byte.class)||clazz.equals(byte.class)){
Object obje=getMethod(field,object);
if (obje==null)
pstmt.setNull(index, Types.TINYINT);
else {
pstmt.setByte(index,Byte.valueOf(getMethod(field,object)+""));
}
}else if (clazz.equals(Short.class)||clazz.equals(short.class)){
Object obje=getMethod(field,object);
if (obje==null)
pstmt.setNull(index, Types.SMALLINT);
else {
pstmt.setShort(index,Short.valueOf(getMethod(field,object)+""));
}
}else if (clazz.equals(Integer.class)||clazz.equals(int.class)){
Object obje=getMethod(field,object);
if (obje==null)
pstmt.setNull(index, Types.INTEGER);
else {
pstmt.setInt(index,Integer.valueOf(getMethod(field,object)+""));
}
}else if (clazz.equals(Long.class)||clazz.equals(long.class)){
Object obje=getMethod(field,object);
if (obje==null)
pstmt.setNull(index, Types.BIGINT);
else {
pstmt.setLong(index,Long.valueOf(getMethod(field,object)+""));
}
}else if (clazz.equals(Float.class)||clazz.equals(float.class)){
Object obje=getMethod(field,object);
if (obje==null)
pstmt.setNull(index, Types.FLOAT);
else {
pstmt.setFloat(index,Float.valueOf(getMethod(field,object)+""));
}
}else if (clazz.equals(Double.class)||clazz.equals(double.class)){
Object obje=getMethod(field,object);
if (obje==null)
pstmt.setNull(index, Types.DOUBLE);
else {
pstmt.setDouble(index,obje==null?null:Double.valueOf(getMethod(field,object)+""));
}
}else if (clazz.equals(Character.class)||clazz.equals(char.class)||clazz.equals(String.class)){
Object obje=getMethod(field,object);
if (obje==null)
pstmt.setNull(index, Types.CHAR);
else {
pstmt.setString(index,obje==null?null:String.valueOf(getMethod(field,object)));
}
}else if (clazz.equals(Boolean.class)||clazz.equals(boolean.class)){
Object obje=getMethod(field,object);
if (obje==null)
pstmt.setNull(index, Types.BOOLEAN);
else {
pstmt.setBoolean(index,Boolean.valueOf(getMethod(field,object)+""));
}
}else if (clazz.equals(Date.class)){
Object obje=getMethod(field,object);
if (obje==null)
pstmt.setNull(index, Types.DATE);
else {
java.sql.Date date=null;
if (obje instanceof String )
date=new java.sql.Date(DateUtil.getStringToDate((String)obje).getTime());
else
date=new java.sql.Date(((Date)obje).getTime());
pstmt.setDate(index, date);
}
}
}
/**
* 获取所有字段，包含父类的
* @param list
* @return
* @throws Exception
*/
private Field[] getAllField(List list)throws Exception{
Class tempClass=list.get(0).getClass();
List<Field> fieldList = new ArrayList<>() ;
while (tempClass != null&& !tempClass.getName().toLowerCase().equals("java.lang.object")) {
fieldList.addAll(Arrays.asList(tempClass .getDeclaredFields()));
tempClass = tempClass.getSuperclass(); //得到父类,然后赋给自己
}
return (Field[])fieldList.toArray();
}
}原文：https://www.cnblogs.com/risirirui/p/12109741.html