@Repository("mysqlDao")
public class MysqlDao {
@Autowired
private JdbcTemplate jdbcTemplate;
protected int add(String sql,Object[]params){
System.out.println("[sql]"+sql);
return jdbcTemplate.update(sql, params);
}
protected int update(String sql,Object[]params){
System.out.println("[sql]"+sql);
return jdbcTemplate.update(sql, params);
}
/**
* 插入
* @param table
* @param map
* @return 返回更新条数
*/
public int add(String table,Map<String, Object> map){
if (map==null) {
return 0;
}
List<String> fields_list = new ArrayList<String>();
List<Object> values_list = new ArrayList<Object>();
Set<String> fields = map.keySet();
for (String field : fields) {
Object value = map.get(field);
if (value!=null) {
fields_list.add(field);
values_list.add(value);
}
}
if (fields_list.size()>0) {
String sql_fields = StringUtil.listToString(fields_list, ",");
String sql_questions = StringUtil.loop("?", fields_list.size(), ",");
Object[] sql_values = values_list.toArray();
StringBuffer sql_sb = new StringBuffer();
sql_sb.append("insert into "+table+"(");
sql_sb.append(sql_fields);
sql_sb.append(") values (");
sql_sb.append(sql_questions);
sql_sb.append(")");
String sql = sql_sb.toString();
System.out.println("[sql]"+sql);
return jdbcTemplate.update(sql, sql_values);
}else {
return 0;
}
}
/**
* 插入
* @param table
* @param map
* @return 返回id
*/
public long addForID(String table,Map<String, Object> map){
if (map==null) {
return 0;
}
List<String> fields_list = new ArrayList<String>();
List<Object> values_list = new ArrayList<Object>();
Set<String> fields = map.keySet();
for (String field : fields) {
Object value = map.get(field);
if (value!=null) {
fields_list.add(field);
values_list.add(value);
}
}
if (fields_list.size()>0) {
String sql_fields = StringUtil.listToString(fields_list, ",");
String sql_questions = StringUtil.loop("?", fields_list.size(), ",");
Object[] sql_values = values_list.toArray();
StringBuffer sql_sb = new StringBuffer();
sql_sb.append("insert into "+table+"(");
sql_sb.append(sql_fields);
sql_sb.append(") values (");
sql_sb.append(sql_questions);
sql_sb.append(")");
String sql = sql_sb.toString();
System.out.println("[sql]"+sql);
//----------
KeyHolder keyHolder = new GeneratedKeyHolder();
final String _sql = sql;
final Object[] params_sql = sql_values;
int updatecount = jdbcTemplate.update(new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
PreparedStatement ps = connection.prepareStatement(_sql, Statement.RETURN_GENERATED_KEYS);
for (int i = 0; i < params_sql.length; i++) {
ps.setObject(i+1, params_sql[i]);
}
return ps;
}
}, keyHolder);
if (updatecount>0) {
long id = keyHolder.getKey().longValue();
return id;
}else {
return 0;
}
// return jdbcTemplate.update(sql, sql_values);
}else {
return 0;
}
}
/**
* 修改
* @param table
* @param map
* @param id
*/
public int update(String table,Map<String, Object> map,Object id){
if (map==null) {
return 0;
}
List<String> fields_list = new ArrayList<String>();
List<Object> values_list = new ArrayList<Object>();
Set<String> fields = map.keySet();
for (String field : fields) {
Object value = map.get(field);
if (value!=null) {
String field2 = field+" = ?";
fields_list.add(field2);
values_list.add(value);
}
}
if (fields_list.size()>0) {
String sql_fields = StringUtil.listToString(fields_list, ",");
StringBuffer sql_sb = new StringBuffer();
sql_sb.append("update "+table+" set ");
sql_sb.append(sql_fields);
sql_sb.append(" where id = ? ");
String sql = sql_sb.toString();
values_list.add(id);
Object[] sql_values = values_list.toArray();
System.out.println("[sql]"+sql);
return jdbcTemplate.update(sql, sql_values);
}else {
return 0;
}
}
protected int delete(String sql,Object[]params){
System.out.println("[sql]"+sql);
return jdbcTemplate.update(sql, params);
}
protected int delete(String table,int id){
String sql = "delete from "+table+ " where id = ? ";
System.out.println("[sql]"+sql);
return jdbcTemplate.update(sql, id);
}
protected int[] batchUpdate(String sql,List<Object[]> params){
final List<Object[]> args = params;
BatchPreparedStatementSetter batchParams = new BatchPreparedStatementSetter() {
public void setValues(PreparedStatement ps, int i) throws SQLException {
Object[]param=args.get(i);
for (int j = 0; j < param.length; j++) {
ps.setObject(j+1, param[j]);
}
}
public int getBatchSize() {
return args.size();
}
};
System.out.println("[sql]"+sql);
return jdbcTemplate.batchUpdate(sql, batchParams);
}
protected int[] batchDelete(String sql,List<Object[]> params){
final List<Object[]> args = params;
BatchPreparedStatementSetter batchParams = new BatchPreparedStatementSetter() {
public void setValues(PreparedStatement ps, int i) throws SQLException {
Object[]param=args.get(i);
for (int j = 0; j < param.length; j++) {
ps.setObject(j+1, param[j]);
}
}
public int getBatchSize() {
return args.size();
}
};
System.out.println("[sql]"+sql);
return jdbcTemplate.batchUpdate(sql, batchParams);
}
protected int queryCount(String sql,Object[]params){
System.out.println("[sql]"+sql);
return jdbcTemplate.queryForInt(sql,params);
}
protected List<Map<String, Object>> queryBySql(String sql,Object[]params){
System.out.println("[sql]"+sql);
return jdbcTemplate.queryForList(sql, params);
}
protected Map<String, Object> queryOneBySql(String sql,Object[]params){
System.out.println("[sql]"+sql);
return jdbcTemplate.queryForMap(sql, params);
}
protected int getLastId(){
String sql_lastId = "select LAST_INSERT_ID()";
return jdbcTemplate.queryForInt(sql_lastId);
}
}