JDBC PreparedStatement实现数据库增删改查案例-蒲公英云

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

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);
        }
    }
}