sql复制代码create or replace procedure queryEmpinfo(eno in number,
                                         pename out varchar2,
                                         psal   out number,
                                         pjob   out varchar2)
as
begin
  select ename,sal,empjob into pename,psal,pjob from emp where empno=eno;
end;

sql复制代码--查询某个员工的年收入
create or replace function queryEmpIncome(eno in number)
return number
as
   psal emp.sal%type;
   pcomm emp.comm%type;
begin
   select sal,comm into psal,pcomm from emp where empno=eno;

   --返回年收入
   return psal*12+nvl(pcomm,0);

end;

sql复制代码--在out参数中使用光标
查询某个部门中所有员工的所有信息


--包头
CREATE OR REPLACE PACKAGE MYPACKAGE AS

  type empcursor is ref cursor;
  procedure queryEmpList(dno in number,empList out empcursor);

END MYPACKAGE;


--包体
CREATE OR REPLACE
PACKAGE BODY MYPACKAGE AS

  procedure queryEmpList(dno in number,empList out empcursor) AS
  BEGIN
    open empList for select * from emp where deptno=dno;
  END queryEmpList;

END MYPACKAGE;

java复制代码import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

import oracle.jdbc.driver.OracleCallableStatement;
import oracle.jdbc.driver.OracleTypes;

import org.junit.Test;

public class TestOracle {

    /*
     * CallableStatement 接口
     *     调用存储函数,等号左边有一个返回值
     *    {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
     *  调用存储过程. 没有返回值
             {call <procedure-name>[(<arg1>,<arg2>, ...)]}

     *
     */

    /*存储过程 查询某个员工的姓名  月薪 职位
     * create or replace procedure queryEmpinfo(eno in number,
                                             pename out varchar2,
                                             psal   out number,
                                             pjob   out varchar2)
     */

    @Test
    public void testProcedure(){
        //{call <procedure-name>[(<arg1>,<arg2>,...)]}
        String sql = "{call queryEmpinfo(?,?,?,?)}";//4个问号中,第一个是输入参数,其余是输出参数
        Connection conn = null;
        //要用CallableStatement这个接口,用于执行 SQL 存储过程的接口
        CallableStatement call = null;

        try {
            conn = JDBCUtils.getConnection();
            call = conn.prepareCall(sql);
            //对于in参数,需要赋值
            call.setInt(1,7839);
            //对于out参数,需要声明
            call.registerOutParameter(2, OracleTypes.VARCHAR);//第二个是字符串
            call.registerOutParameter(3, OracleTypes.NUMBER);//第三个是数字
            call.registerOutParameter(4, OracleTypes.VARCHAR);//第四个是字符串

            call.execute();
            //取出结果
            String name = call.getString(2);
            double sal = call.getDouble(3);
            String job = call.getString(4);
            System.out.println(name+"\t"+sal+"\t"+job+"\t");
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            JDBCUtils.release(conn, call, null);//没有最后一个参数就传入null
        }
    }

    /*存储函数  查询某个员工的姓名,月薪和职位
     * create or replace function queryEmpIncome(eno in number)
        return number
     */
    @Test
    public void testFunction(){
        //{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
        //第一个问号是函数的返回值,第二个问号是输入参数.  返回值的作用和输出参数是一样的.
        String sql = "{?=call QUERYEMPINCOME(?)}";//这个call后面的存储过程名或者是存储函数名大写或者是小写是没有要求的.
        Connection conn = null;
        //要用CallableStatement这个接口,用于执行 SQL 存储过程的接口
        CallableStatement call = null;

        try {
            conn = JDBCUtils.getConnection();
            call = conn.prepareCall(sql);

            //对于in参数,赋值
            call.setInt(2,7839);

            //对于out参数，申明
            call.registerOutParameter(1, OracleTypes.NUMBER);
            call.execute();
            //取出结果
            //取出结果
            double income = call.getDouble(1);
            System.out.println(income);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            JDBCUtils.release(conn, call, null);//没有最后一个参数就传入null
        }


    }

    /*
        查询某个部门中所有员工的所有信息
        包头
        CREATE OR REPLACE PACKAGE MYPACKAGE AS

          type empcursor is ref cursor;
          procedure queryEmpList(dno in number,empList out empcursor);

        END MYPACKAGE;


        包体
        CREATE OR REPLACE
        PACKAGE BODY MYPACKAGE AS

          procedure queryEmpList(dno in number,empList out empcursor) AS
          BEGIN
            open empList for select * from emp where deptno=dno;
          END queryEmpList;

        END MYPACKAGE;
     */
    @Test
    public void testCursor(){
        //{call <procedure-name>[(<arg1>,<arg2>, ...)]}
        String sql = "{call MYPACKAGE.queryEmpList(?,?)}";

        Connection conn = null;
        CallableStatement call = null;
        //有游标,就有结果集
        ResultSet rest = null;
        try {
            conn = JDBCUtils.getConnection();
            call = conn.prepareCall(sql);

            //对于in参数，赋值
            call.setInt(1, 20);

            //对于out参数，申明
            call.registerOutParameter(2, OracleTypes.CURSOR);
            call.execute();
            //取出集合
            //这个地方要强转!!!OracleCallableStatement是抽象类,继承了CallableStatement
            //不强转没有getCursor()方法...
            rest = ((OracleCallableStatement)call).getCursor(2);
            while(rest.next()){
                String name = rest.getString("ename");
                double sal = rest.getDouble("sal");
                System.out.println(name+"\t"+sal);
            }
        }catch (Exception e) {
            e.printStackTrace();
        }finally{
            JDBCUtils.release(conn, call, rest);//上面打开了光标,再这个地方关闭结果集rest,也就关闭了光标
        }
    }
}

sql复制代码create or replace procedure queryEmpinfo(eno in number,
                                         pename out varchar2,
                                         psal   out number,
                                         pjob   out varchar2)
as
begin
  select ename,sal,empjob into pename,psal,pjob from emp where empno=eno;
end;

sql复制代码--在out参数中使用光标
--查询某个部门中所有员工的所有信息


--包头
CREATE OR REPLACE PACKAGE MYPACKAGE AS

  type empcursor is ref cursor;
  procedure queryEmpList(dno in number,empList out empcursor);

END MYPACKAGE;


--包体
CREATE OR REPLACE
PACKAGE BODY MYPACKAGE AS

  procedure queryEmpList(dno in number,empList out empcursor) AS
  BEGIN
    open empList for select * from emp where deptno=dno;
  END queryEmpList;

END MYPACKAGE;

java复制代码io.shardingsphere.core.routing.router.sharding.ParsingSQLRouter#processLimit

private void processLimit(final List<Object> parameters, final SelectStatement selectStatement, final boolean isSingleRouting) {
     boolean isNeedFetchAll = (!selectStatement.getGroupByItems().isEmpty() || !selectStatement.getAggregationSelectItems().isEmpty()) && !selectStatement.isSameGroupByAndOrderByItems();
    selectStatement.getLimit().processParameters(parameters, isNeedFetchAll, databaseType, isSingleRouting);
}

io.shardingsphere.core.parsing.parser.context.limit.Limit#processParameters

/**
* Fill parameters for rewrite limit.
*
* @param parameters parameters
* @param isFetchAll is fetch all data or not
* @param databaseType database type
* @param isSingleRouting is single routing or not
*/
public void processParameters(final List<Object> parameters, final boolean isFetchAll, final DatabaseType databaseType, final boolean isSingleRouting) {
    fill(parameters);
    rewrite(parameters, isFetchAll, databaseType, isSingleRouting);
}


private void rewrite(final List<Object> parameters, final boolean isFetchAll, final DatabaseType databaseType, final boolean isSingleRouting) {
    int rewriteOffset = 0;
    int rewriteRowCount;
    if (isFetchAll) {
        rewriteRowCount = Integer.MAX_VALUE;
    } else if (isNeedRewriteRowCount(databaseType) && !isSingleRouting) {
         rewriteRowCount = null == rowCount ? -1 : getOffsetValue() + rowCount.getValue();
    } else {
       rewriteRowCount = rowCount.getValue();
    }
    if (null != offset && offset.getIndex() > -1 && !isSingleRouting) {
       parameters.set(offset.getIndex(), rewriteOffset);
     }
     if (null != rowCount && rowCount.getIndex() > -1) {
        parameters.set(rowCount.getIndex(), rewriteRowCount);
      }
}

bash复制代码# 备份 repo 文件
cp /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.bak

# 下载
wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo

yum clean all # 清除缓存
yum makecache # 生成缓存
yum update

bash复制代码yum install -y mariadb

cron复制代码# 定时任务中的 % 需要使用斜杆进行转义
0 1 * * * mysqldump -uzhangsan -ppassword -h127.0.0.1 dbName > /data/dbName_`date '+\%Y\%m\%d\%H\%M\%S'`.sql 2>&1

java复制代码public void getXXX(Map<String, String> params) {
    Map<String, String> map = params;
    if (map == params) {
        map = new HashMap<>();
    }
}

java复制代码public void getXXX(Map<String, String> params) {
    Map<String, String> map = Optional.ofNullable(params).orElse(new HashMap<>());
}

java复制代码String cityCode = customer.getAddress().getCity().getCityCode().substring(0,3);

java复制代码public void getCityCode(Customer customer) {
    String cityCode = "000";
    if (customer != null) {
        Address address = customer.getAddress();
        if (null != address) {
            City city = address.getCity();
            if (city != null) {
                String code = city.getCityCode();
                if (null != code && code.length() >= 3) {
                    cityCode = code.substring(0, 3);
                }
            }
        }
    }
    System.out.println(cityCode);
}

java复制代码public void getCityCode(Customer customer) {
    String cityCode = Optional.ofNullable(customer)
            .map(c -> c.getAddress())
            .map(a -> a.getCity())
            .map(c -> c.getCityCode())
            .filter(s -> s.length() >= 3)
            .map(s -> s.substring(0, 3))
            .orElse("000");
}

xml复制代码<dependency>
    <groupId>io.vavr</groupId>
    <artifactId>vavr</artifactId>
    <version>0.10.3</version>
</dependency>

java复制代码public class SleepSort implements Runnable {
    private int num;
    public SleepSort(int num) {
        this.num = num;
    }
    @Override
    public void run() {
        try {
            Thread.sleep(num * 10);
        } catch (Exception e) {
            e.printStackTrace();
        }
        System.out.print(num + " ");
    }
    public static void main(String[] args) {
        int[] nums = {5, 22, 10, 7, 59, 3, 16, 4, 11, 8, 14, 24, 27, 25, 26, 28, 23, 99};
        Arrays.stream(nums).forEach(n->new Thread(new SleepSort(n)).start());
    }
}

java复制代码Try.run(()->Thread.sleep(num*10))
        .andThen(()->System.out.print(num + " "));

java复制代码String json = Try.of(() -> objectMapper.writeValueAsString(str)).getOrElse("{}");

java复制代码public String vavrMatch(String input) {
    return Match(input).of(
            Case($("a"), "a1"),
            Case($("b"), "b2"),
            Case($("c"), "c3"),
            Case($(), "unknown")
    );
}

java复制代码// (Java, 8)
Tuple2<String, Integer> java8 = Tuple.of("Java", 8); 

// "Java"
String s = java8._1; 

// 8
Integer i = java8._2;

java复制代码Lazy<Double> lazy = Lazy.of(Math::random);
lazy.isEvaluated(); // = false
lazy.get();         // = 0.123 (random generated)
lazy.isEvaluated(); // = true
lazy.get();         // = 0.123 (memoized)

java复制代码String s = new String("xyz"); 创建了几个实例？

java复制代码两个，一个堆区的“xyz”，一个栈区指向“xyz”的s。

java复制代码String s = new String("xyz");创建几个String实例？

java复制代码两个。
一个是字符串字面量"xyz"所对应的、存在于全局共享的常量池中的实例，
另一个是通过new String(String)创建并初始化的、内容（字符）与"xyz"相同的实例。
考虑到如果常量池中如果有这个字符串，就只会创建一个。
同时在栈区还会有一个对new出来的String实例的s。

java复制代码问题：String s = new String("xyz"); 创建了几个实例？
答案：两个，一个堆区的“xyz”，一个栈区指向“xyz”的s

java复制代码String s1 = "xyz";  
String s2 = s1.concat("");  
String s3 = null;  
new String(s1);

arduino复制代码如果包括JVM中的C++实例的话，
有两个Java的String实例，
两个String实例对应的instanceOopDesc实例，
还有一个char[]数组对应的typeArrayOopDesc实例。
加一起一共是5个，也可以说2个String实例加上3个oop实例。

java复制代码@Data
public class Cart {
    List<CartItem> items;
    private Integer countNum;           // 商品数量
    private Integer countType;         // 商品类型的个数
    private BigDecimal totalAmount;   // 当前购物车总价格
    private BigDecimal reduce = new BigDecimal(0);       // 优惠价格

    public Integer getCountNum() {
        int count = 0;
        if (items != null && items.size() > 0) {
            for (CartItem item : items) {
                count += item.getCount();
            }
        }
        setCountNum(count);
        return count;
    }

    public void setCountNum(Integer countNum) {
        this.countNum = countNum;
    }

    public Integer getCountType() {
        int count = 0;
        if (items != null && items.size() > 0) {
            for (CartItem item : items) {
                count += 1;
            }
        }
        setCountType(count);
        return countType;
    }

    public void setCountType(Integer countType) {
        this.countType = countType;
    }

    public BigDecimal getTotalAmount() {
        BigDecimal count = new BigDecimal(0);
        if (items != null && items.size() > 0) {
            for (CartItem item : items) {
                count = count.add(item.getTotalPrice();
            }
        }
        count = count.subtract(reduce);
        setTotalAmount(count);
        return totalAmount;
    }

    public void setTotalAmount(BigDecimal totalAmount) {
        this.totalAmount = totalAmount;
    }

    public BigDecimal getReduce() {
        return reduce;
    }

    public void setReduce(BigDecimal reduce) {
        this.reduce = reduce;
    }
}

java复制代码@Data
public class CartItem {

    private Long skuId;
    private Boolean check = true;
    private String title;
    private String image;
    private List<String> skuAttr;
    private BigDecimal price;
    private Integer count;
    private BigDecimal totalPrice;

    public BigDecimal getTotalPrice() {
        totalPrice = price.multiply(new BigDecimal(count));
        return totalPrice;
    }
}

java复制代码@Component
public class CartInterceptor implements HandlerInterceptor {

    // 共享数据
    public static ThreadLocal<UserInfo> userInfoLocal = new ThreadLocal<>();

    /**
     * 方法执行前
     */
    @Override
    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {
        UserInfo userInfo = new UserInfo();

        // 封装userInfo
        HttpSession session = request.getSession();
        MemberVo user = (MemberVo) session.getAttribute(AuthConstant.LOGIN_USER);
        if (user != null) {
            // 获取登录用户的购物车 -> userId
            userInfo.setUserId(user.getId());
        }
        // 获取离线购物车 -> user-key
        Cookie[] cookies = request.getCookies();
        if (cookies != null && cookies.length > 0) {
            for (Cookie cookie : cookies) {
                if (cookie.getName().equals(CartConstant.User_COOKIE_NAME)) {
                    userInfo.setUserKey(cookie.getValue());
                    userInfo.setTemp(true);
                    break;
                }
            }
        }
        // 用户第一次登录分配一个随机的user-key
        if (StringUtils.isBlank(userInfo.getUserKey())) {
            userInfo.setUserKey(UUID.randomUUID().toString());
        }
        // 目标方法执行前
        userInfoLocal.set(userInfo);
        return true;
    }

    /**
     * 方法执行后
     */
    @Override
    public void postHandle(HttpServletRequest request, HttpServletResponse response, Object handler, ModelAndView modelAndView) throws Exception {
        UserInfo userInfo = userInfoLocal.get();

        // 如果是false就表明是第一次
        if (!userInfo.isTemp()) {
            Cookie cookie = new Cookie(CartConstant.User_COOKIE_NAME, userInfo.getUserKey());
            cookie.setDomain("localhost");
            cookie.setMaxAge(CartConstant.COOKIE_TTL);
            response.addCookie(cookie);
        }
    }
}

java复制代码@Configuration
public class WebConfig implements WebMvcConfigurer {

    @Override
    public void addInterceptors(InterceptorRegistry registry) {
        // 注册拦截器 -> 拦截所有请求
        registry.addInterceptor(new CartInterceptor()).addPathPatterns("/**");
    }
}

java复制代码@GetMapping("/addToCart")
public String addToCart(@RequestParam String skuId, @RequestParam Integer num, Model model) throws ExecutionException, InterruptedException {
    CartItem cartItem = cartService.addToCart(skuId, num);
    model.addAttribute("item", cartItem);
    return "success";
}

java复制代码@Override
public CartItem addToCart(String skuId, Integer num) throws ExecutionException, InterruptedException {
    BoundHashOperations<String, Object, Object> ops = getCartOps();
    CartItem cartItem;
    
    // 判断这个商品在购物车中是否存在
    Object o = ops.get(JSON.toJSONString(skuId)); // fix 保存格式为json 所以读取格式也要是json
    if (Objects.isNull(o)) {
        cartItem = new CartItem();
        // 添加新商品：
        // 1.查询当前要添加的商品信息
        CompletableFuture<Void> getSkuInfoTask = CompletableFuture.runAsync(() -> {
            R r = productFeignService.info(Long.parseLong(skuId));  // 远程调用
            SkuInfoEntity info = BeanUtil.toBean(r.get("skuInfo"), SkuInfoEntity.class);
            cartItem.setSkuId(info.getSkuId());
            cartItem.setCheck(true);
            cartItem.setTitle(info.getSkuTitle());
            cartItem.setImage(info.getSkuDefaultImg());
            cartItem.setPrice(info.getPrice());
            cartItem.setCount(num);
            cartItem.setTotalPrice(info.getPrice().multiply(new BigDecimal(num)));
        }, thread);
        // 2.查询属性信息
        CompletableFuture<Void> getAttrTask = CompletableFuture.runAsync(() -> {
            List<String> value = productFeignService.getSkuSaleAttrValue(skuId.toString());  // 远程调用
            cartItem.setSkuAttr(value);
        }, thread);
        
        CompletableFuture.allOf(getAttrTask, getSkuInfoTask).get();
    } else {
        // 1.修改数量
        cartItem = (CartItem) o;
        cartItem.setCount(cartItem.getCount() + num);
        cartItem.setTotalPrice(cartItem.getTotalPrice());
    }
    // 3.保存到redis中
    ops.put(JSON.toJSONString(skuId), cartItem);
    
    return cartItem;
}

java复制代码private static final String cart_prefix = "cart:";

/**
 * 获取购物车
 *
 * @return {@link BoundHashOperations<String, Object, Object>}
 */
private BoundHashOperations<String, Object, Object> getCartOps() {
    UserInfo user = CartInterceptor.userInfoLocal.get();

    // 1.生成redis中的key
    StringBuilder cartKey = new StringBuilder(cart_prefix);
    if (user.getUserId() != null) {
        cartKey.append(user.getUserId());
    } else {
        cartKey.append(user.getUserKey());
    }

    BoundHashOperations<String, Object, Object> ops = redisTemplate.boundHashOps(cartKey.toString());
    return ops;
}

java复制代码@Override
public CartItem getCartItem(String skuId) {
    BoundHashOperations<String, Object, Object> ops = getCartOps();
    String s = (String) ops.get(JSON.toJSONString(skuId));
    return JSON.parseObject(s, new TypeReference<CartItem>() {});
}

java复制代码/**
 * 购物车列表
 * 浏览器有一个cookie：user-key，用来表示用户的身份
 * 登录：按session
 * 没有登录：user-key
 * 第一次：创建user-key
 *
 * @return {@link String}
 */
@GetMapping("/cartList.html")
public String cartList(Model model) throws ExecutionException, InterruptedException {
    // 获取当前登录用户的信息
    Cart cart = cartService.getCart();
    model.addAttribute("cart",cart);
    return "cartList";
}

java复制代码@Override
public Cart getCart() throws ExecutionException, InterruptedException {
    UserInfo user = CartInterceptor.userInfoLocal.get();
    Cart cart = new Cart();

    // 1.获取离线购物车
    List<CartItem> items = getCartItems(cart_prefix+user.getUserKey());
    // 判断离线购物车中是否有内容
    if (items != null && items.size() > 0) {
        // 2.获取登录购物车
        Long userId = user.getUserId();
        if (userId != null) {
            // 3.用戶已经登录->合并购物车->清空离线购物车
            for (CartItem cartItem : items) {
                addItemToCart(cartItem.getSkuId().toString(),cartItem.getCount());  // 合并购物车
            }
            deleteCart(cart_prefix+ user.getUserKey());  // 清空离线购物车
            items = getCartItems(cart_prefix + userId);   // 获取合并后的购物车内容
        }
    }
    cart.setItems(items);

    return cart;
}

/**
 * 删除购物车
 *
 * @param key user key
 */
private void deleteCart(String key) {
    redisTemplate.delete(key);
}

/**
 * 根据购物项的key,获取对应购物项
 *
 * @param key 关键
 * @return {@link List<CartItem>}
 */
private List<CartItem> getCartItems(String key) {
    BoundHashOperations<String, Object, Object> ops = redisTemplate.boundHashOps(key);
    List<Object> values = ops.values();
    if (values != null && values.size() > 0)
        return values.stream()
                .map(s -> (CartItem) s)
                .collect(Collectors.toList());
    return null;
}

java复制代码@Override
public Cart getCart() throws ExecutionException, InterruptedException {
    UserInfo user = CartInterceptor.userInfoLocal.get();
    System.out.println(user);
    Cart cart = new Cart();

    // 1.获取离线购物车
    List<CartItem> items = getCartItems(cart_prefix + user.getUserKey());
    // 判断离线购物车中是否有内容

    // 2.获取登录购物车
    Long userId = user.getUserId();
    if (userId != null) {
        // 3.用戶已经登录->合并购物车->清空离线购物车
        if (items != null && items.size() > 0) {
            for (CartItem cartItem : items) {
                addItemToCart(cartItem.getSkuId().toString(), cartItem.getCount());  // 合并购物车
            }
        }
        deleteCart(cart_prefix + user.getUserKey());  // 清空离线购物车
        items = getCartItems(cart_prefix + userId);   // 获取合并后的购物车内容
    }

    cart.setItems(items);

    return cart;
}