增删改查——DBUtils

迈不过友情╰ 2023-08-17 16:09 21阅读 0赞

利用QueryRunner类实现对数据库的增删改查操作，需要先导入jar包：commons-dbutils-1.6。利用QueryRunner类可以实现对数据步骤的简化。

1、添加

运用JDBC工具类实现连接：

package JDBCUtils;
    import java.io.IOException;
    import java.io.InputStream;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.Properties;
    
    public class JDBCUtils {
        private static Connection con;
        private static String driver;
        private static String url;
        private static String username;
        private static String password;
    
        static {
        // 静态代码块只执行一次，获取一次信息即可
            try {
                readConfig();
                Class.forName(driver);
                con = DriverManager.getConnection(url, username, password);
            } catch (Exception ex) {
                throw new RuntimeException("数据库连接失败");
            }
        }
    /*
     * getClassLoader();返回该类的加载器
     * getResourceAsStream();查找具有给定名称的资源 
     */
        private static void readConfig() {
            InputStream in = JDBCUtils.class.getClassLoader().getResourceAsStream("JDBC.properties");
            Properties pro = new Properties();
            try {
                pro.load(in);
            } catch (IOException e) {
                e.printStackTrace();
            }
            driver = pro.getProperty("driver");
            url = pro.getProperty("url");
            username = pro.getProperty("username");
            password = pro.getProperty("password");
        }
    
        public static Connection getConnection() {
            return con;
        }
        public static void close(Connection con) {
    
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    System.out.println("con流关闭异常！");
                }
            }
    
        }
        public static void close(Connection con, Statement stat) {
    
            if (stat != null) {
                try {
                    stat.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    System.out.println("stat流关闭异常！");
                }
            }
    
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    System.out.println("con流关闭异常！");
                }
            }
    
        }
    
        public static void close(Connection con, Statement stat, ResultSet rs) {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    System.out.println("rs流关闭异常！");
                }
            }
    
            if (stat != null) {
                try {
                    stat.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    System.out.println("stat流关闭异常！");
                }
            }
    
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    System.out.println("con流关闭异常！");
                }
            }
    
    }
    }

import java.sql.Connection;
    import java.sql.SQLException;
    import org.apache.commons.dbutils.QueryRunner;
    
    import JDBCUtils.JDBCUtils;
    
    public class add {
    
        public static void main(String[] args) {
            Connection con = null;
            try {
                con = JDBCUtils.getConnection();
                QueryRunner qr = new QueryRunner();
                String sql = "INSERT INTO student(studentno,sname,sex,birthday,classno,point,phone,email) VALUES(?,?,?,?,?,?,?,?)";
                Object[] params = { "20191811", "Jack", "男", "1988-12-01",
                        "201901", "239", "16623540978", "Tom.@3218n.com" };
    
                int num = qr.update(con, sql, params);
                System.out.println("添加了" + num + "行");
    
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            JDBCUtils.close(con);
        }
    }

2、删除

import java.sql.Connection;
    import java.sql.SQLException;
    
    import org.apache.commons.dbutils.QueryRunner;
    
    import JDBCUtils.JDBCUtils;
    
    public class DeleteDemo {
        public static void main(String[] args) {
            Connection con = null;
            try {
                con = JDBCUtils.getConnection();
                QueryRunner qr = new QueryRunner();
                String sql = "DELETE from Student where sname =?";
                Object[] delete = { "Tom" };
                qr.update(con, sql, delete);
    
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            JDBCUtils.close(con);
        }
    }

3、修改

import java.sql.Connection;
    import java.sql.SQLException;
    
    import org.apache.commons.dbutils.QueryRunner;
    
    import JDBCUtils.JDBCUtils;
    
    public class UpdateDemo {
        public static void main(String[] args) {
            Connection con = null;
            try {
                con = JDBCUtils.getConnection();
                QueryRunner qr = new QueryRunner();
                String sql = "Update Student set classno=? Where sname='韩吟秋'";
                Object[] update = { "201901" };
                qr.update(con, sql, update);
    
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            JDBCUtils.close(con);
        }
    }

4、查询

（1）

ArrayHandler： 将结果集的第一行存储到Object\[\]数组中

ArrayListHandler： 将结果集的每一行存储到Object\[\]数组中

import java.sql.Connection;
    import java.sql.SQLException;
    import java.util.Arrays;
    import java.util.List;
    
    import org.apache.commons.dbutils.QueryRunner;
    import org.apache.commons.dbutils.handlers.ArrayListHandler;
    
    import JDBCUtils.JDBCUtils;
    
    public class SeleteDemo {
        public static void main(String[] args) {
            Connection con = null;
            try {
                con = JDBCUtils.getConnection();
                QueryRunner qr = new QueryRunner();
                String sql = "Select * from Student where studentno=?";
                Object[] select = { 20191811 };
                List<Object[]> list = qr.query(con, sql, new ArrayListHandler(),
                        select);
                // 将记录封装到一个装有Object[]的List集合中
                for (Object[] arr : list) {
                    System.out.println(Arrays.toString(arr));
                }
    
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            JDBCUtils.close(con);
        }
    
    }

（2）

BeanHandler：结果集中第一条记录封装到一个指定的javaBean中。

BeanListHandler：结果集中每一条记录封装到javaBean中，再将javaBean封装到list集合中。

public class Student {
    private String studentno;
    private String sname;
    private String sex;
    private String birthday;
    private String classno;
    private String point;
    private String phone;
    private String email;
    public String getStudentno() {
        return studentno;
    }
    public void setStudentno(String studentno) {
        this.studentno = studentno;
    }
    public String getSname() {
        return sname;
    }
    public void setSname(String sname) {
        this.sname = sname;
    }
    public String getSex() {
        return sex;
    }
    public void setSex(String sex) {
        this.sex = sex;
    }
    public String getBirthday() {
        return birthday;
    }
    public void setBirthday(String birthday) {
        this.birthday = birthday;
    }
    @Override
    public String toString() {
        return "Student [studentno=" + studentno + ", sname=" + sname + ", sex="
                + sex + ", birthday=" + birthday + ", classno=" + classno
                + ", point=" + point + ", phone=" + phone + ", email=" + email
                + "]";
    }
    public String getClassno() {
        return classno;
    }
    public void setClassno(String classno) {
        this.classno = classno;
    }
    public String getPoint() {
        return point;
    }
    public void setPoint(String point) {
        this.point = point;
    }
    public String getPhone() {
        return phone;
    }
    public void setPhone(String phone) {
        this.phone = phone;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    
    
    }

import java.sql.Connection;
    import java.sql.SQLException;
    import java.util.List;
    
    import org.apache.commons.dbutils.QueryRunner;
    import org.apache.commons.dbutils.handlers.BeanListHandler;
    
    import JDBCUtils.JDBCUtils;
    
    public class SeleteDemo {
        public static void main(String[] args) {
            Connection con = null;
            try {
                con = JDBCUtils.getConnection();
                QueryRunner qr = new QueryRunner();
                String sql = "Select * from Student where studentno=?";
                Object[] select = { 20191811 };
                List<Student> list = qr.query(con, sql,new BeanListHandler<Student>((Student.class)), select);
                // 将记录封装到一个装有Object[]的List集合中
                for (Student s : list) {
                    System.out.println(s);
                }
    
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            JDBCUtils.close(con);
        }
    
    }

（3）ColumnListHandler将结果集中指定的列封装到List集合。

import java.sql.Connection;
    import java.sql.SQLException;
    import java.util.List;
    
    import org.apache.commons.dbutils.QueryRunner;
    import org.apache.commons.dbutils.handlers.ColumnListHandler;
    
    import JDBCUtils.JDBCUtils;
    
    public class SeleteDemo {
        public static void main(String[] args) {
            Connection con = null;
            try {
                con = JDBCUtils.getConnection();
                QueryRunner qr = new QueryRunner();
                String sql = "Select * from Student where studentno=?";
                Object[] select = {
        20191811};
                List<String> list = qr.query(con, sql,new ColumnListHandler<String>(), select);
                // 将记录封装到一个装有Object[]的List集合中
                for (String str: list) {
                    System.out.println(str);
                }
    
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            JDBCUtils.close(con);
        }
    
    }

查询学生的学号：

import java.sql.Connection;
    import java.sql.SQLException;
    import java.util.List;
    
    import org.apache.commons.dbutils.QueryRunner;
    import org.apache.commons.dbutils.handlers.ColumnListHandler;
    
    import JDBCUtils.JDBCUtils;
    
    public class SeleteDemo {
        public static void main(String[] args) {
            Connection con = null;
            try {
                con = JDBCUtils.getConnection();
                QueryRunner qr = new QueryRunner();
                String sql = "Select studentno from Student  ";
                Object[] select = {};
                List<String> list = qr.query(con, sql,new ColumnListHandler<String>(), select);
                // 将记录封装到一个装有Object[]的List集合中
                for (String str: list) {
                    System.out.println(str);
                }
    
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            JDBCUtils.close(con);
        }
    
    }

（4）ScalarHandler返回一个数据

import java.sql.Connection;
    import java.sql.SQLException;
    import org.apache.commons.dbutils.QueryRunner;
    import org.apache.commons.dbutils.handlers.ScalarHandler;
    
    import JDBCUtils.JDBCUtils;
    
    public class SeleteDemo {
        public static void main(String[] args) {
            Connection con = null;
            try {
                con = JDBCUtils.getConnection();
                QueryRunner qr = new QueryRunner();
                String sql = "SELECT COUNT(sname)   FROM Student";
                Object[] select = {};
                long count= qr.query(con, sql, new ScalarHandler<Long>(), select);
                System.out.println(count);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            JDBCUtils.close(con);
        }
    
    }

（5）MapHandler：将结果集的第一行封装到Map集合中

MapListHandler：将结果集的多条记录封装到一个集合中

import java.sql.Connection;
    import java.sql.SQLException;
    import java.util.List;
    import java.util.Map;
    
    import org.apache.commons.dbutils.QueryRunner;
    import org.apache.commons.dbutils.handlers.MapListHandler;
    
    import JDBCUtils.JDBCUtils;
    
    public class SeleteDemo {
        public static void main(String[] args) {
            Connection con = null;
            try {
                con = JDBCUtils.getConnection();
                QueryRunner qr = new QueryRunner();
                String sql = "Select studentno from Student  ";
                Object[] select = {};
                List<Map<String,Object>> list = qr.query(con, sql, new MapListHandler(),select);
            
                // 将记录封装到一个装有Object[]的List集合中
                for (Map<String,Object> map : list) {
                    for(String key : map.keySet()){
                        System.out.print(key+"..."+map.get(key));
                    }
                    System.out.println();
                }
    
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            JDBCUtils.close(con);
        }
    
    }

转载于:https://www.cnblogs.com/zhai1997/p/11382079.html