JDBC PreparedStatement实现数据库增删改查案例

╰+攻爆jí腚メ 2022-06-08 11:15 255阅读 0赞

JDBC PreparedStatement实现数据库增删改查案例:

博主在数据库中创建person表,字段信息如下：

![Center][]

项目中需要的包为：ojdbc.jar

![Center 1][]

示例代码：

1.Main2.java

package com.company;
    
    import java.sql.ResultSet;
    import java.sql.SQLException;
    
    public class Main2 {
    
        public static void main(String[] args) {
            //执行插入语句
            String sql = "INSERT INTO person(name,psw,age,sex,description) VALUES(?,?,?,?,?)";
            int update = DBUtils2.executeUpdate(sql, "李四", "123", 18, "男", "个人说明");
            System.out.println(update > 0 ? "添加成功" : "添加失败");
    
            //执行查询语句
            String sql1 = "SELECT name,psw,age FROM person WHERE personid=?";
            DBUtils2.RS dbRS = DBUtils2.executeQuery(sql1, 56);
            ResultSet rs = dbRS.getRs();
            try {
                if (rs.next()) {
                    String name = rs.getString("name");
                    String psw = rs.getString("psw");
                    int age = rs.getInt("age");
                    System.out.println("查询结果： 姓名：" + name + "  密码：" + psw + "  年龄：" + age);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                dbRS.close();
            }
            //执行修改语句
            String sql2 = "UPDATE person SET age=36 WHERE personid=?";
            int i = DBUtils2.executeUpdate(sql2, 56);
            System.out.println(i > 0 ? "修改成功" : "修改失败");
    
    //        //执行删除语句
            String sql3 = "DELETE FROM person WHERE personid=?";
            int i1 = DBUtils2.executeUpdate(sql3, 56);
            System.out.println(i1 > 0 ? "删除成功" : "删除失败");
        }
    }

2.DBUtils2.java

package com.company;
    
    import java.sql.*;
    
    public class DBUtils2 {
        /**
         * 数据库的属性信息
         */
    //    数据库主机 IP地址
        private static final String IP = "127.0.0.1";
        //    数据库端口
        private static final String PORT = "1521";
        //    数据库名称？
        private static final String DB_NAME = "orcal";
        //    数据库URL
        private static final String URL = "jdbc:oracle:thin:@" + IP + ":" + PORT + ":" + DB_NAME;
        //     数据库连接用户名
        private static final String USER = "scott";
        //     用户密码
        private static final String PSW = "tiger";
    
        //    获取数据库连接
        public static Connection getConn() {
            Connection conn = null;
            try {
                conn = DriverManager.getConnection(URL, USER, PSW);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return conn;
        }
    
        //    执行查询语句方法
        public static RS executeQuery(String sql, Object... params) {
            Connection conn = null;
            PreparedStatement ps = null;
            try {
                conn = DBUtils2.getConn();
                ps = conn.prepareStatement(sql);
                for (int i = 0; i < params.length; i++) {
                    ps.setObject(i + 1, params[i]);
                }
                ResultSet rs = ps.executeQuery();
                return new RS(conn, ps, rs);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return null;
        }
    
        //    执行更新语句方法
        public static int executeUpdate(String sql, Object... params) {
            Connection conn = null;
            PreparedStatement ps = null;
            try {
                conn = DBUtils2.getConn();
                ps = conn.prepareStatement(sql);
                for (int i = 0; i < params.length; i++) {
                    ps.setObject(i + 1, params[i]);
                }
                int i = ps.executeUpdate();
                return i;
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                close(conn, ps);
            }
            return 0;
        }
    
        //    释放连接资源
        public static void close(ResultSet rs) {
            close(null, null, rs);
        }
    
        //    释放连接资源
        public static void close(Connection conn) {
            close(conn, null, null);
        }
    
        //    释放连接资源
        public static void close(Connection conn, Statement st) {
            close(conn, st, null);
        }
    
        //    释放连接资源
        public static void close(Connection conn, Statement st, ResultSet rs) {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (st != null) {
                try {
                    st.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    
        public static class RS {
            private Connection conn = null;
            private Statement st = null;
            private ResultSet rs;
    
            public RS(Connection conn, Statement st, ResultSet rs) {
                this.conn = conn;
                this.st = st;
                this.rs = rs;
            }
    
            public ResultSet getRs() {
                return this.rs;
            }
    
            public void close() {
                DBUtils2.close(conn, st, rs);
            }
    
        }
    }

结果：

添加结果：

![Center 2][]

修改结果：

![Center 3][]

查询结果：

![Center 4][]

删除结果：

![Center 5][]

[Center]: /images/20220608/d8368f5dcb9341c394efb2a5b73c8384.png
[Center 1]: /images/20220608/0606e11d1baa475699233acaeee94848.png
[Center 2]: /images/20220608/7c4e449cf84b4ea0b00125ac19753e98.png
[Center 3]: /images/20220608/e2949ea05a004c3a8535a5d727d1ddeb.png
[Center 4]: /images/20220608/b82a56c167e6471cb414107cf7fe0184.png
[Center 5]: /images/20220608/0a04f6c774874dd6b0329c5cdf887a2d.png