增删改查——DBUtils

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

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

1、添加

运用JDBC工具类实现连接：

  1. package JDBCUtils;
  2. import java.io.IOException;
  3. import java.io.InputStream;
  4. import java.sql.Connection;
  5. import java.sql.DriverManager;
  6. import java.sql.ResultSet;
  7. import java.sql.SQLException;
  8. import java.sql.Statement;
  9. import java.util.Properties;
  11. public class JDBCUtils {
  12.     private static Connection con;
  13.     private static String driver;
  14.     private static String url;
  15.     private static String username;
  16.     private static String password;
  18.     static {
  19.     // 静态代码块只执行一次，获取一次信息即可
  20.         try {
  21.             readConfig();
  22.             Class.forName(driver);
  23.             con = DriverManager.getConnection(url, username, password);
  24.         } catch (Exception ex) {
  25.             throw new RuntimeException("数据库连接失败");
  26.         }
  27.     }
  28. /*
  29.  * getClassLoader();返回该类的加载器
  30.  * getResourceAsStream();查找具有给定名称的资源 
  31.  */
  32.     private static void readConfig() {
  33.         InputStream in = JDBCUtils.class.getClassLoader().getResourceAsStream("JDBC.properties");
  34.         Properties pro = new Properties();
  35.         try {
  36.             pro.load(in);
  37.         } catch (IOException e) {
  38.             e.printStackTrace();
  39.         }
  40.         driver = pro.getProperty("driver");
  41.         url = pro.getProperty("url");
  42.         username = pro.getProperty("username");
  43.         password = pro.getProperty("password");
  44.     }
  46.     public static Connection getConnection() {
  47.         return con;
  48.     }
  49.     public static void close(Connection con) {
  51.         if (con != null) {
  52.             try {
  53.                 con.close();
  54.             } catch (SQLException e) {
  55.                 e.printStackTrace();
  56.                 System.out.println("con流关闭异常！");
  57.             }
  58.         }
  60.     }
  61.     public static void close(Connection con, Statement stat) {
  63.         if (stat != null) {
  64.             try {
  65.                 stat.close();
  66.             } catch (SQLException e) {
  67.                 e.printStackTrace();
  68.                 System.out.println("stat流关闭异常！");
  69.             }
  70.         }
  72.         if (con != null) {
  73.             try {
  74.                 con.close();
  75.             } catch (SQLException e) {
  76.                 e.printStackTrace();
  77.                 System.out.println("con流关闭异常！");
  78.             }
  79.         }
  81.     }
  83.     public static void close(Connection con, Statement stat, ResultSet rs) {
  84.         if (rs != null) {
  85.             try {
  86.                 rs.close();
  87.             } catch (SQLException e) {
  88.                 e.printStackTrace();
  89.                 System.out.println("rs流关闭异常！");
  90.             }
  91.         }
  93.         if (stat != null) {
  94.             try {
  95.                 stat.close();
  96.             } catch (SQLException e) {
  97.                 e.printStackTrace();
  98.                 System.out.println("stat流关闭异常！");
  99.             }
  100.         }
  102.         if (con != null) {
  103.             try {
  104.                 con.close();
  105.             } catch (SQLException e) {
  106.                 e.printStackTrace();
  107.                 System.out.println("con流关闭异常！");
  108.             }
  109.         }
  111. }
  112. }
  114. import java.sql.Connection;
  115. import java.sql.SQLException;
  116. import org.apache.commons.dbutils.QueryRunner;
  118. import JDBCUtils.JDBCUtils;
  120. public class add {
  122.     public static void main(String[] args) {
  123.         Connection con = null;
  124.         try {
  125.             con = JDBCUtils.getConnection();
  126.             QueryRunner qr = new QueryRunner();
  127.             String sql = "INSERT INTO student(studentno,sname,sex,birthday,classno,point,phone,email) VALUES(?,?,?,?,?,?,?,?)";
  128.             Object[] params = { "20191811", "Jack", "男", "1988-12-01",
  129.                     "201901", "239", "16623540978", "Tom.@3218n.com" };
  131.             int num = qr.update(con, sql, params);
  132.             System.out.println("添加了" + num + "行");
  134.         } catch (SQLException e) {
  135.             throw new RuntimeException(e);
  136.         }
  137.         JDBCUtils.close(con);
  138.     }
  139. }

2、删除

  1. import java.sql.Connection;
  2. import java.sql.SQLException;
  4. import org.apache.commons.dbutils.QueryRunner;
  6. import JDBCUtils.JDBCUtils;
  8. public class DeleteDemo {
  9.     public static void main(String[] args) {
  10.         Connection con = null;
  11.         try {
  12.             con = JDBCUtils.getConnection();
  13.             QueryRunner qr = new QueryRunner();
  14.             String sql = "DELETE from Student where sname =?";
  15.             Object[] delete = { "Tom" };
  16.             qr.update(con, sql, delete);
  18.         } catch (SQLException e) {
  19.             throw new RuntimeException(e);
  20.         }
  21.         JDBCUtils.close(con);
  22.     }
  23. }

3、修改

  1. import java.sql.Connection;
  2. import java.sql.SQLException;
  4. import org.apache.commons.dbutils.QueryRunner;
  6. import JDBCUtils.JDBCUtils;
  8. public class UpdateDemo {
  9.     public static void main(String[] args) {
  10.         Connection con = null;
  11.         try {
  12.             con = JDBCUtils.getConnection();
  13.             QueryRunner qr = new QueryRunner();
  14.             String sql = "Update Student set classno=? Where sname='韩吟秋'";
  15.             Object[] update = { "201901" };
  16.             qr.update(con, sql, update);
  18.         } catch (SQLException e) {
  19.             throw new RuntimeException(e);
  20.         }
  21.         JDBCUtils.close(con);
  22.     }
  23. }

4、查询

（1）

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

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

  1. import java.sql.Connection;
  2. import java.sql.SQLException;
  3. import java.util.Arrays;
  4. import java.util.List;
  6. import org.apache.commons.dbutils.QueryRunner;
  7. import org.apache.commons.dbutils.handlers.ArrayListHandler;
  9. import JDBCUtils.JDBCUtils;
  11. public class SeleteDemo {
  12.     public static void main(String[] args) {
  13.         Connection con = null;
  14.         try {
  15.             con = JDBCUtils.getConnection();
  16.             QueryRunner qr = new QueryRunner();
  17.             String sql = "Select * from Student where studentno=?";
  18.             Object[] select = { 20191811 };
  19.             List<Object[]> list = qr.query(con, sql, new ArrayListHandler(),
  20.                     select);
  21.             // 将记录封装到一个装有Object[]的List集合中
  22.             for (Object[] arr : list) {
  23.                 System.out.println(Arrays.toString(arr));
  24.             }
  26.         } catch (SQLException e) {
  27.             throw new RuntimeException(e);
  28.         }
  29.         JDBCUtils.close(con);
  30.     }
  32. }

（2）

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

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

  1. public class Student {
  2. private String studentno;
  3. private String sname;
  4. private String sex;
  5. private String birthday;
  6. private String classno;
  7. private String point;
  8. private String phone;
  9. private String email;
  10. public String getStudentno() {
  11.     return studentno;
  12. }
  13. public void setStudentno(String studentno) {
  14.     this.studentno = studentno;
  15. }
  16. public String getSname() {
  17.     return sname;
  18. }
  19. public void setSname(String sname) {
  20.     this.sname = sname;
  21. }
  22. public String getSex() {
  23.     return sex;
  24. }
  25. public void setSex(String sex) {
  26.     this.sex = sex;
  27. }
  28. public String getBirthday() {
  29.     return birthday;
  30. }
  31. public void setBirthday(String birthday) {
  32.     this.birthday = birthday;
  33. }
  34. @Override
  35. public String toString() {
  36.     return "Student [studentno=" + studentno + ", sname=" + sname + ", sex="
  37.             + sex + ", birthday=" + birthday + ", classno=" + classno
  38.             + ", point=" + point + ", phone=" + phone + ", email=" + email
  39.             + "]";
  40. }
  41. public String getClassno() {
  42.     return classno;
  43. }
  44. public void setClassno(String classno) {
  45.     this.classno = classno;
  46. }
  47. public String getPoint() {
  48.     return point;
  49. }
  50. public void setPoint(String point) {
  51.     this.point = point;
  52. }
  53. public String getPhone() {
  54.     return phone;
  55. }
  56. public void setPhone(String phone) {
  57.     this.phone = phone;
  58. }
  59. public String getEmail() {
  60.     return email;
  61. }
  62. public void setEmail(String email) {
  63.     this.email = email;
  64. }
  67. }
  69. import java.sql.Connection;
  70. import java.sql.SQLException;
  71. import java.util.List;
  73. import org.apache.commons.dbutils.QueryRunner;
  74. import org.apache.commons.dbutils.handlers.BeanListHandler;
  76. import JDBCUtils.JDBCUtils;
  78. public class SeleteDemo {
  79.     public static void main(String[] args) {
  80.         Connection con = null;
  81.         try {
  82.             con = JDBCUtils.getConnection();
  83.             QueryRunner qr = new QueryRunner();
  84.             String sql = "Select * from Student where studentno=?";
  85.             Object[] select = { 20191811 };
  86.             List<Student> list = qr.query(con, sql,new BeanListHandler<Student>((Student.class)), select);
  87.             // 将记录封装到一个装有Object[]的List集合中
  88.             for (Student s : list) {
  89.                 System.out.println(s);
  90.             }
  92.         } catch (SQLException e) {
  93.             throw new RuntimeException(e);
  94.         }
  95.         JDBCUtils.close(con);
  96.     }
  98. }

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

  1. import java.sql.Connection;
  2. import java.sql.SQLException;
  3. import java.util.List;
  5. import org.apache.commons.dbutils.QueryRunner;
  6. import org.apache.commons.dbutils.handlers.ColumnListHandler;
  8. import JDBCUtils.JDBCUtils;
  10. public class SeleteDemo {
  11.     public static void main(String[] args) {
  12.         Connection con = null;
  13.         try {
  14.             con = JDBCUtils.getConnection();
  15.             QueryRunner qr = new QueryRunner();
  16.             String sql = "Select * from Student where studentno=?";
  17.             Object[] select = {
  18.     20191811};
  19.             List<String> list = qr.query(con, sql,new ColumnListHandler<String>(), select);
  20.             // 将记录封装到一个装有Object[]的List集合中
  21.             for (String str: list) {
  22.                 System.out.println(str);
  23.             }
  25.         } catch (SQLException e) {
  26.             throw new RuntimeException(e);
  27.         }
  28.         JDBCUtils.close(con);
  29.     }
  31. }

查询学生的学号：

  1. import java.sql.Connection;
  2. import java.sql.SQLException;
  3. import java.util.List;
  5. import org.apache.commons.dbutils.QueryRunner;
  6. import org.apache.commons.dbutils.handlers.ColumnListHandler;
  8. import JDBCUtils.JDBCUtils;
  10. public class SeleteDemo {
  11.     public static void main(String[] args) {
  12.         Connection con = null;
  13.         try {
  14.             con = JDBCUtils.getConnection();
  15.             QueryRunner qr = new QueryRunner();
  16.             String sql = "Select studentno from Student  ";
  17.             Object[] select = {};
  18.             List<String> list = qr.query(con, sql,new ColumnListHandler<String>(), select);
  19.             // 将记录封装到一个装有Object[]的List集合中
  20.             for (String str: list) {
  21.                 System.out.println(str);
  22.             }
  24.         } catch (SQLException e) {
  25.             throw new RuntimeException(e);
  26.         }
  27.         JDBCUtils.close(con);
  28.     }
  30. }

（4）ScalarHandler返回一个数据

  1. import java.sql.Connection;
  2. import java.sql.SQLException;
  3. import org.apache.commons.dbutils.QueryRunner;
  4. import org.apache.commons.dbutils.handlers.ScalarHandler;
  6. import JDBCUtils.JDBCUtils;
  8. public class SeleteDemo {
  9.     public static void main(String[] args) {
  10.         Connection con = null;
  11.         try {
  12.             con = JDBCUtils.getConnection();
  13.             QueryRunner qr = new QueryRunner();
  14.             String sql = "SELECT COUNT(sname)   FROM Student";
  15.             Object[] select = {};
  16.             long count= qr.query(con, sql, new ScalarHandler<Long>(), select);
  17.             System.out.println(count);
  18.         } catch (SQLException e) {
  19.             throw new RuntimeException(e);
  20.         }
  21.         JDBCUtils.close(con);
  22.     }
  24. }

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

  1.       MapListHandler：将结果集的多条记录封装到一个集合中
  3. import java.sql.Connection;
  4. import java.sql.SQLException;
  5. import java.util.List;
  6. import java.util.Map;
  8. import org.apache.commons.dbutils.QueryRunner;
  9. import org.apache.commons.dbutils.handlers.MapListHandler;
  11. import JDBCUtils.JDBCUtils;
  13. public class SeleteDemo {
  14.     public static void main(String[] args) {
  15.         Connection con = null;
  16.         try {
  17.             con = JDBCUtils.getConnection();
  18.             QueryRunner qr = new QueryRunner();
  19.             String sql = "Select studentno from Student  ";
  20.             Object[] select = {};
  21.             List<Map<String,Object>> list = qr.query(con, sql, new MapListHandler(),select);
  23.             // 将记录封装到一个装有Object[]的List集合中
  24.             for (Map<String,Object> map : list) {
  25.                 for(String key : map.keySet()){
  26.                     System.out.print(key+"..."+map.get(key));
  27.                 }
  28.                 System.out.println();
  29.             }
  31.         } catch (SQLException e) {
  32.             throw new RuntimeException(e);
  33.         }
  34.         JDBCUtils.close(con);
  35.     }
  37. }

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

发表评论

表情：
评论列表 （有 0 条评论，142人围观）

还没有评论，来说两句吧...

相关阅读