package com.springboot.blob.service;

import com.mysql.jdbc.Connection;
import com.springboot.blob.domain.Picture;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.*;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.jdbc.support.lob.DefaultLobHandler;
import org.springframework.jdbc.support.lob.LobHandler;
import org.springframework.stereotype.Component;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;

/**
 * @author Simon
 */
@Component
public class QueryService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public int insertBlob(Picture picture) {
        final FileInputStream fis;
        KeyHolder keyHolder = new GeneratedKeyHolder();
        final File f = new File(picture.getUrl());
        if (!f.exists()) {
            System.out.println("文件不存在!");
        }
        try {
            fis = new FileInputStream(f);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            return -1;
        }
        String sql = "insert into picture(url, filename, path, data) values (?,?,?,?)";
        jdbcTemplate.update(con -> {
            PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, picture.getUrl());
            ps.setString(2, picture.getFilename());
            ps.setString(3, picture.getPath());
            ps.setBinaryStream(4, fis, (int) f.length());
            return ps;
        }, keyHolder);
        return keyHolder.getKey().intValue();
    }

    public Picture getBlob(String id) {
        System.out.println("============ query for object! ==============");
        final LobHandler lobHandler = new DefaultLobHandler();
        // sql + 指定返回类型方式访问
        // 使用这种sql的有点就是方便使用反射方式，实现PO的赋值
        String sql =
                "select id, url, filename, path, data from picture where id = " + id;
        Picture picture = jdbcTemplate.queryForObject(sql, (rs, rowNum) -> {
            Picture po = new Picture();
            po.setId(rs.getLong(1));
            po.setUrl(rs.getString(2));
            po.setFilename(rs.getString(3));
            po.setPath(rs.getString(4));
            po.setData(lobHandler.getBlobAsBytes(rs, 5));
            return po;
        });
        System.out.println("queryFroObject by RowMapper: " + picture);

        picture = jdbcTemplate.queryForObject(sql, (rs, rowNum) -> {
            Picture po = new Picture();
            po.setId(rs.getLong("id"));
            po.setUrl(rs.getString("url"));
            po.setFilename(rs.getString("filename"));
            po.setPath(rs.getString("path"));
            po.setData(lobHandler.getBlobAsBytes(rs, "data"));
            return po;
        });
        System.out.println("queryFroObject by RowMapper: " + picture);


        // 更简单的方式，直接通过BeanPropertyRowMapper来实现属性的赋值，前提是sql返回的列名能正确匹配
        picture = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(Picture.class));
        System.out.println("queryForObject by BeanPropertyRowMapper: " + picture);


        String sql2 = "select id from picture where id = ?";
        Long res = jdbcTemplate.queryForObject(sql2, Long.class, id);
        System.out.println("queryForObject by requireId return: " + res);

        System.out.println("============ query for object! ==============");
        return picture;
    }

}