package com.cxs.base;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/*
 * @Project:java-jdbc-demo
 * @Author:cxs
 * @Motto:放下杂念,只为迎接明天更好的自己
 * */
public class UserOperaHandle {

    /**
     * 插入操作
     * @param user
     * @return
     */
    public int insertUserHandle(User user){
        int result = 0;
        // 连接对象
        Connection connection = null;
        // 预编译语句对象
        PreparedStatement statement = null;
        // sql语句
        String insertSql = "insert into t_user values(null, ?, ?)";
        try {
            // 1. 加载注册驱动
            Class.forName(DbConfig.driverClassName);
            // 2. 创建连接对象
            connection = DriverManager.getConnection(DbConfig.url, DbConfig.username, DbConfig.password);
            // 3. 获取语句对象
            statement = connection.prepareStatement(insertSql);
                // 给预编译sql占位符赋值，从第一个？开始，依次赋值
            statement.setObject(1, user.getUserName());
            statement.setObject(2, user.getCreateTime());
            // 4. 执行sql语句
                // executeUpdate()指定DML操作，会返回一个整型，表示对数据库影响的行数
            result = statement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 5. 关闭资源
                // 遵循先开的后关
            try {
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return result;
    }

    /**
     * 查询操作
     * @param id
     * @return
     */
    public User selectByIdUserHandle(Integer id){
        User result = null;
        // 连接对象
        Connection connection = null;
        // 预编译语句对象
        PreparedStatement statement = null;
        // 结果集对象
        ResultSet resultSet = null;
        // sql语句
        String selectSql = "select * from t_user where id = ?";
        try {
            // 1. 加载注册驱动
            Class.forName(DbConfig.driverClassName);
            // 2. 创建连接对象
            connection = DriverManager.getConnection(DbConfig.url, DbConfig.username, DbConfig.password);
            // 3. 获取语句对象
            statement = connection.prepareStatement(selectSql);
            // 给预编译sql占位符赋值，从第一个？开始，依次赋值
            statement.setObject(1, id);
            // 4. 执行sql语句
            // executeQuery()指定DQL操作，会返回一个结果集
            resultSet = statement.executeQuery();
            // 处理结果集，返回User
            if (resultSet.next()) {
                result = new User();
                result.setId(resultSet.getInt(1));
                result.setUserName(resultSet.getString(2));
                result.setCreateTime(resultSet.getTimestamp(3));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 5. 关闭资源
            // 遵循先开的后关
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return result;
    }
}