使用jdbc对数据进行增删改查-优快云博客

package dao2;

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

public class BaseDao {
    
    protected Connection connection;
    protected Statement stm;
    protected PreparedStatement pstm;
    protected ResultSet rs;
    
    // 获取数据库连接
    public void getConnection() {
        // 读取配置信息
        String driver = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/kgcnews?useSSL=false&serverTimezone=UTC";
        String user = "root";
        String password = "123456";
        
        // 加载驱动
        try {
            Class.forName(driver);
            // 获取连接
            connection = DriverManager.getConnection(url,user,password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 增删改 
    public int excuteUpdate(String sql,Object[] obj) {
        getConnection();
        int rows = 0;
        try {
            pstm = connection.prepareStatement(sql);
            for(int i=0; i<obj.length;i++) {
                pstm.setObject(i+1, obj[i]);
            }
            rows = pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rows;
    }

    // 查询
    public ResultSet executeSql(String sql,Object[] obj) {
        getConnection();
        try {
            pstm = connection.prepareStatement(sql);
            for(int i=0;i<obj.length;i++) {
                pstm.setObject(i+1, obj[i]);
            }
            rs = pstm.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }

    // 关闭资源
    public void closeAll() {
        try {
            if(rs!=null) {
                rs.close();
            }
            if(pstm!=null) {
                pstm.close();
            }
            if(stm!=null) {
                stm.close();
            }
            if(connection!=null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

基类Dao

package dao2;

import java.util.Date;

public interface NewsDao {
    
    // 查询
    public void getNewsList(String sql,Object[] obj);
    
    // 增改
    public void undateTable(String sql, Object... obj);
    
    // 删除
    public void delete(int id);
    
}

接口

package dao2;

import java.sql.SQLException;
import java.util.*;
import java.util.Date;

public class NewsDaoImpl extends BaseDao implements NewsDao {
    // 查询
    public void getNewsList(String sql, Object[] obj) {
        // 返回结果集
        rs = executeSql(sql, obj);
        // 处理结果集
        List list = new ArrayList();
        try {
            while (rs.next()) {
                list.add(rs.getInt("id"));
                list.add(rs.getString("title"));
                list.add(rs.getString("summary"));
                list.add(rs.getString("content"));
                list.add(rs.getString("author"));
                list.add(rs.getTimestamp("createDate"));
            }
            // 遍历输出信息
            for (Object s : list) {
                System.out.println(s.toString());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 释放资源
            closeAll();
        }

    }

    // 增改
    public void undateTable(String sql, Object... obj) {
        int i = excuteUpdate(sql, obj);
        if (i > 0) {
            System.out.println("更新操作完成！");
        } else {
            System.out.println("操作有误！");
        }
        closeAll();
    }

    // 删
    public void delete(int id) {
        String sql = "delete from news_detail where id=?";
        int i = this.excuteUpdate(sql, new Object[] { id });
        if (i > 0) {
            System.out.println("删除成功！");
        } else {
            System.out.println("操作有误！");
        }
    }

    public static void main(String[] args) {
        NewsDaoImpl nd = new NewsDaoImpl();

        // 查询
//        String sql = "select * from news_detail where id = ?";
//        Object[] obj = {2};
//        nd.getNewsList(sql, obj);

        // 增加
//        String sql = "insert into news_detail(id,categoryId,title,summary,content,createDate) values(?,?,?,?,?,?)";
//        Object[] obj = {3,2,"大发大股东","dfshsdfhhsdf","dffsh打发点发给",new java.sql.Date(new Date().getTime())};
//        nd.undateTable(sql, obj);

        // 修改
//        String sql = "update news_detail set id=? where id=?";
//        Object[] obj = {3,5};
//        nd.undateTable(sql, obj);

        // 删除
        String sql = "delete from news_detail where id = ?";
        nd.undateTable(sql, 3);
    }

}