每日总结

今日进行了hive数据库的增删改查

package ceshi.spark;

import crud.pojo.LoadEst;

import java.sql.*;
import java.time.LocalTime;
import java.util.ArrayList;
import java.util.List;

public class Dao {
    public List<LoadEst> selectAll(String d) {
        List<LoadEst> list = new ArrayList<>();
        Connection conn = null;
        try {
            conn = JDBC.getConnection();
            // 创建Statement对象
            Statement stmt = conn.createStatement();

            // 执行Hive查询
            String query = "select * from test1 where search_word LIKE '%" + d + "%'";
            ResultSet resultSet = stmt.executeQuery(query);

            // 处理查询结果
            while (resultSet.next()) {
                // 检索数据
                String dt = resultSet.getString("dt");
                String userId = resultSet.getString("user_id");
                String searchWord = resultSet.getString("search_word");
                String url = resultSet.getString("url");
                LoadEst loadEst = new LoadEst(dt, userId, searchWord, url);
                list.add(loadEst);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return list;
    }

    public void add(LoadEst loadEst) {
        // 创建连接
        try (Connection connection = JDBC.getConnection();) {
            String insertQuery = "INSERT INTO test1 (dt, user_id, search_word, url) VALUES (?,?,?,?)";
            PreparedStatement ps = connection.prepareStatement(insertQuery);
            LocalTime currentTime = LocalTime.now();
            // 提取时分秒
            int hour = currentTime.getHour();
            int minute = currentTime.getMinute();
            int second = currentTime.getSecond();
            String time = "" + hour + minute + second;
            ps.setString(1, time);
            ps.setString(2,loadEst.getUserId());
            ps.setString(3,loadEst.getSearchWord());
            ps.setString(4,loadEst.getUrl());
            ps.executeUpdate();
            System.out.println("Insert successful");
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
    }

    public void delete(Integer id) {
        try (Connection connection = JDBC.getConnection();) {
            String deleteQuery = "DELETE FROM test1 WHERE user_id = ?";
            PreparedStatement ps = connection.prepareStatement(deleteQuery);
            ps.setString(1, String.valueOf(id));
            int rowsDeleted = ps.executeUpdate();

            if (rowsDeleted != 0) {
                System.out.println("Delete successful");
            } else {
                System.out.println("No rows were deleted. User with ID " + id + " not found.");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
    }
    public void update(LoadEst loadEst){
        // 创建连接
        try (Connection connection = JDBC.getConnection();) {
            String insertQuery = "update test1 set dt = ?, search_word = ?, url = ? where user_id = ?";
            PreparedStatement ps = connection.prepareStatement(insertQuery);
            LocalTime currentTime = LocalTime.now();
            // 提取时分秒
            int hour = currentTime.getHour();
            int minute = currentTime.getMinute();
            int second = currentTime.getSecond();
            String time = "" + hour + minute + second;
            ps.setString(1, time);
            ps.setString(2,loadEst.getSearchWord());
            ps.setString(3,loadEst.getUrl());
            ps.setString(4,loadEst.getUserId());
            ps.executeUpdate();
            System.out.println("update successful");
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
    }
}