使用PreParedStatement实现对数据库的CRUD操作

使用PreParedStatement实现对数据库的CRUD操作

创建工具类JDBCutils

public class JDBCutils {
    public static Connection getConnection() throws IOException, ClassNotFoundException, SQLException {
        //1.通过本类的类加载器读取配置文件中的四个基本信息
        InputStream is = ClassLoader.getSystemResourceAsStream("jdbc.properties");
        Properties pros = new Properties();
        pros.load(is);
        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String url = pros.getProperty("url");
        String driverClass = pros.getProperty("driverClass");
        //2.加载驱动
        Class.forName(driverClass);
        //3.获取连接
        Connection conn = DriverManager.getConnection(url, user, password);
        return conn;
    }
    //关闭连接和statement
    public static void  closeResource(PreparedStatement ps,Connection conn){
        try {
            if (ps!=null)
            ps.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if (conn!=null)
            conn.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }
}

增加数据

    public void  testInsert()  {
        PreparedStatement ps = null;

        Connection conn = null;
        try {
            conn = JDBCutils.getConnection();
            //4.预编译sql语句，返回PreparedStatement的实例
            String sql="insert into student(Sno,Sname,Ssex,Sage,Sdept) values (?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            //5.填写占位符
            ps.setString(1,"54200721");
            ps.setString(2,"李佳祥");
            ps.setString(3,"男");
            ps.setInt(4,18);
            ps.setString(5,"Java");
            //6.执行操作
            ps.execute();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        //关闭资源
        JDBCutils.closeResource(ps,conn);
    }

修改数据库中的数据

 public void  testUpdate()  {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            //获取连接
            conn = JDBCutils.getConnection();
            //预编译sql语句，返回PreparedStatement实例
            String sql="update student set Sname=? where Sno=?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,"李大磊");
            ps.setString(2,"54200721");
            //执行
            ps.execute();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        //关闭资源
        JDBCutils.closeResource(ps,conn);
    }

删除数据库表中的数据

    //删除数据库中的数据
    public void testDelete(){
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            //获取连接
            conn = JDBCutils.getConnection();
            //编译sql语句，返回PreparedStatement实例
            String sql="delete from student where Sname=?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,"李大磊");
            //执行操作
            ps.execute();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        //关闭资源
        JDBCutils.closeResource(ps,conn);
    }

通用的增删改操作

//通用增删改操作
    public void update(String sql,Object...args){//sql中的占位符个数和数组的长度相等，所以用循环解决设置占位符的值
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            //获取连接
            conn = JDBCutils.getConnection();
            //编译sql语句，返回preparedStatement实例
            ps = conn.prepareStatement(sql);
            //通过循环来设置占位符的值
            for(int i=0;i<args.length;i++){
                ps.setObject(i+1,args[i]);
            }
            //执行
            ps.execute();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        //关闭资源
        JDBCutils.closeResource(ps,conn);
    }
    public void testCommonUpdate(){
        String sql="insert into student(Sno,Sname,Ssex,Sage,Sdept) values (?,?,?,?,?)";
        update(sql,"54200721","李佳祥","男",18,"Java");
    }

查询数据库中的数据

用Javabean将数据库中的数据封装成一个对象

package com.jdbc.learn.bean;

public class Student {
    private  String Sno;
    private  String Sname;
  private String Ssex;
    private int Sage;
    private String Sdept;

    public Student() {
    }

    public Student(String sno, String sname, String ssex, int sage, String sdept) {
        Sno = sno;
        Sname = sname;
        Ssex = ssex;
        Sage = sage;
        Sdept = sdept;
    }

    public String getSno() {
        return Sno;
    }

    public void setSno(String sno) {
        Sno = sno;
    }

    public String getSname() {
        return Sname;
    }

    public void setSname(String sname) {
        Sname = sname;
    }

    public String getSsex() {
        return Ssex;
    }

    public void setSsex(String ssex) {
        Ssex = ssex;
    }

    public int getSage() {
        return Sage;
    }

    public void setSage(int sage) {
        Sage = sage;
    }

    public String getSdept() {
        return Sdept;
    }

    public void setSdept(String sdept) {
        Sdept = sdept;
    }

    @Override
    public String toString() {
        return "Student{" +
                "Sno='" + Sno + '\'' +
                ", Sname='" + Sname + '\'' +
                ", Ssex='" + Ssex + '\'' +
                ", Sage=" + Sage +
                ", Sdept='" + Sdept + '\'' +
                '}';
    }
}

查询操作

    public void testQuery1(){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //1.获取连接
            conn = JDBCutils.getConnection();
            //2.编译sql语句，返回prepareStatement实例
            String sql="select *from student where Sno=?";
            ps = conn.prepareStatement(sql);
            //3.设置占位符的值
            ps.setString(1,"54200721");
            //执行，并返回结果集
            rs = ps.executeQuery();
            if(rs.next()){//next():判断结果集的下一条数据，如果有数据返回true，并指向下一条数据
                String Sno = rs.getString(1);
                String Sname = rs.getString(2);
                String Ssex = rs.getString(3);
                int Sage = rs.getInt(4);
                String Sdept = rs.getString(5);
                //将数据分装在在一个类对象当中
                Student student = new Student(Sno, Sname, Ssex, Sage, Sdept);
                System.out.println(student);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        JDBCutils.closeResource(ps,conn,rs);
    }

针对于student表的通用查询方法

    public void testqueryStudent(){
         String sql="select Sno,Sname,Ssex from student where Sno=?";
        Student student = queryForStudent(sql, "201215121");
        System.out.println(student);
    }

        //针对于student表的通用查询写法
    public Student queryForStudent(String sql,Object...args){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //1.获取连接
            conn = JDBCutils.getConnection();
            //2.编译sql语句，返回prepareStatement实例
            ps = conn.prepareStatement(sql);
            //3.通过循环设置占位符的值
            for(int i=0;i<args.length;i++){
                ps.setObject(i+1,args[i]);
            }
            //4.执行并返回结果集
            rs = ps.executeQuery();
            //获取结果集的元数据
            ResultSetMetaData rsm = rs.getMetaData();
            //通过ResultSetMetaData获取结果集中的列数
            int count = rsm.getColumnCount();
            if (rs.next()){
                //如果有值，创建一个student对象
                Student student = new Student();
                for(int i=0;i<count;i++){
                    Object studentValue = rs.getObject(i + 1);
                   //获取每一个列的列名
                    String studentName = rsm.getColumnName(i + 1);
                    //通过反射studentName属性，赋值为studentValue
                    Field filed = Student.class.getDeclaredField(studentName);
                    //属性可能是私有的，通过setAccessible(true)访问
                    filed.setAccessible(true);
                    filed.set(student,studentValue);
                }
                return student;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCutils.closeResource(ps,conn,rs);
        }

        return null;
    }

注：结果集是存储数据的、结果集的元数据是存储修饰数据的内容，比如 列名、列数等