Spring jdbc实例

Spring jdbc实例

Storage：

import com.xykj.koala.commons.db.DbClient;import com.xykj.koala.commons.db.DbUtils;import com.xykj.koala.users.common.data.UserSignIn;import com.xykj.koala.users.server.storage.UserSignInStorage;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Repository;import org.springframework.util.CollectionUtils;import java.util.Arrays;import java.util.List;import java.util.stream.Collectors;@Repositorypublic class DBUserSignInStorage implements UserSignInStorage {    @Autowired    DbClient dbClient;    @Override    public void create(long userId, long lastSignTime) {        String sql = "insert into user_sign_in (userId, lastSignInTime, serialCount, totalCount)" +                "values(?,?,?,?)";        dbClient.update(sql, userId, lastSignTime, 1, 1);    }    @Override    public UserSignIn findByUserId(long userId) {        String sql = "select * from user_sign_in where userId = ?";        return dbClient.queryForObject(sql, UserSignIn.rowMapper, userId);    }    @Override    public void updateSignIn(UserSignIn userSignIn, UserSignIn.Field... updateFields) {        List<UserSignIn.Field> fields = Arrays.asList(updateFields);        if (CollectionUtils.isEmpty(fields)) {            return;        }        String sql = "update `user_sign_in` set " + DbUtils.getValueList(fields.stream()                .map(UserSignIn.Field::getUpdateSql)                .collect(Collectors.toList())) +                " where userId=?";        List<Object> args = fields.stream()                .map(field -> field.getValue().apply(userSignIn))                .collect(Collectors.toList());        args.add(userSignIn.getUserId());        dbClient.update(sql, args.toArray());    }}

data：

import lombok.Getter;import lombok.Setter;import lombok.experimental.Accessors;import org.springframework.jdbc.core.RowMapper;import java.util.function.Function;@Getter@Setter@Accessors(chain = true)public class UserSignIn {    private long userId;    private long lastSignInTime;    private long serialCount;    private long totalCount;    public static final RowMapper<UserSignIn> rowMapper = (rs, rc) -> new UserSignIn()            .setUserId(rs.getLong("userId"))            .setLastSignInTime(rs.getLong("lastSignInTime"))            .setSerialCount(rs.getLong("serialCount"))            .setTotalCount(rs.getLong("totalCount"));    public enum Field {        lastSignInTime("lastSignInTime=?", UserSignIn::getLastSignInTime),        serialCount("serialCount=?", UserSignIn::getSerialCount),        totalCount("totalCount=?", UserSignIn::getTotalCount);        private String updateSql;        private Function<UserSignIn, Object> value;        Field(String updateSql, Function<UserSignIn, Object> value) {            this.updateSql = updateSql;            this.value = value;        }        public String getUpdateSql() {            return updateSql;        }        public Function<UserSignIn, Object> getValue() {            return value;        }    }}

RowMapper的写法使用Lamda表达式，原始写法如下：

new RowMapper<User>() {  

    @Override  
    public User mapRow(ResultSet rs, int rowNum) throws SQLException {  
        User user = new User();  
        user.setId(rs.getInt("id"));  
        user.setUsername(rs.getString("username"));  
        user.setPassword(rs.getString("password"));  
        return user;  
    }  
}
等效于下面lamda写法：
public static final RowMapper<User> rowMapper = (rs, rc) -> new User()
    .setId(rs.getInt("id")) 
    .setUsername(rs.getString("username")) 
    .setPassword(rs.getString("password"));