专注于 JetBrains IDEA 全家桶，永久激活，教程
持续更新 PyCharm，IDEA，WebStorm，PhpStorm，DataGrip，RubyMine，CLion，AppCode 永久激活教程
当前位置：搜云库技术团队 Java汇总 正文

增删改查（Statement接口、PreparedStatement接口、DBUtils（QueryRunner类））

2020-07-21 分类：Java汇总 阅读(460)

1、Statement接口实现增删改查

（1）增加数据表中的元组：

package pers.datebase.zsgc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBC {
    public static void main(String[] args) throws ClassNotFoundException,
            SQLException {
        // 加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        // 链接到数据库
        String url = "jdbc:mysql://localhost:3306/student mangement system";
        String username = "root";
        String password = "root";
        Connection con = DriverManager.getConnection(url, username, password);

        // 获取到SQL语句的执行者对象
        Statement stat = con.createStatement();

        // 增加
        stat.executeUpdate("insert into student(studentno,sname,sex,birthday,classno,point,phone,email)"
                + "values('201712219','张涵','女','1989-12-18','080601','666','15878945612','han@163.com')");

        // 释放资源
        stat.close();
        con.close();
    }
}

35_1.png

（2）删除元组：

package pers.datebase.zsgc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBC {
    public static void main(String[] args) throws ClassNotFoundException,
            SQLException {
        // 加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        // 链接到数据库
        String url = "jdbc:mysql://localhost:3306/student mangement system";
        String username = "root";
        String password = "root";
        Connection con = DriverManager.getConnection(url, username, password);

        // 获取到SQL语句的执行者对象
        Statement stat = con.createStatement();

        // 删除
        String sql = "delete from student where sname='张涵'"; // SQL语句
        stat.executeUpdate(sql); // 将sql语句上传至数据库执行

        // 释放资源
        stat.close();
        con.close();
    }
}

（3）修改元组：

package pers.datebase.zsgc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBC {
    public static void main(String[] args) throws ClassNotFoundException,
            SQLException {
        // 加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        // 链接到数据库
        String url = "jdbc:mysql://localhost:3306/student mangement system";
        String username = "root";
        String password = "root";
        Connection con = DriverManager.getConnection(url, username, password);

        // 获取到SQL语句的执行者对象
        Statement stat = con.createStatement();

        String sql="update student set classno='201718' where sname='平靖' ";   //SQL语句
        stat.executeUpdate(sql);         //将sql语句上传至数据库执行

        // 释放资源
        stat.close();
        con.close();
    }
}

35_2.png

（4）查询数据表：

package pers.datebase.zsgc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBC {
    public static void main(String[] args) throws ClassNotFoundException,
            SQLException {
        // 加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        // 链接到数据库
        String url = "jdbc:mysql://localhost:3306/student mangement system";
        String username = "root";
        String password = "root";
        Connection con = DriverManager.getConnection(url, username, password);

        // 获取到SQL语句的执行者对象
        Statement stat = con.createStatement();

        String sql="select * from score";           //sql语句
        ResultSet rs=stat.executeQuery(sql);          //ResultSet 是java中执行select后，返回的结果集类。 rs 就是结果集的变量。
        while(rs.next()){    //next（）获取里面的内容
            System.out.println(rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3)+""+rs.getString(4));
        }

        // 释放资源
        stat.close();
        con.close();
        rs.close();
    }
}

35_3.png

2、PreparedStatement接口

（1）添加：

package pers.Pre.add;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class Add {
    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement ps = null;

        try { Class.forName("com.mysql.jdbc.Driver"); String url = "jdbc:mysql://localhost:3306/student mangement system"; String username = "root"; String password = "root"; con = DriverManager.getConnection(url, username, password); String insertSql = "insert into score(studentno,courseno,usually,final)values(?,?,?,?)"; ps = con.prepareStatement(insertSql);// 获取预处理对象 ps.setString(1, "20191832"); ps.setString(2, "b221"); ps.setDouble(3, 123); ps.setDouble(4, 123); int num = ps.executeUpdate(); System.out.println("添加了" + num + "条记录"); } catch (Exception e) { e.printStackTrace(); } finally { try { if (ps != null) ps.close(); if (con != null) con.close(); } catch (Exception e) { e.printStackTrace(); } } } }

（2）删除：

package pers.Pre.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class DeleteDemo {
    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement ps = null;

        try { Class.forName("com.mysql.jdbc.Driver"); String url = "jdbc:mysql://localhost:3306/student mangement system"; String username = "root"; String password = "root"; con = DriverManager.getConnection(url, username, password); String deleteSql = "Delete from Student where sname=?"; ps = con.prepareStatement(deleteSql);// 获取预处理对象 ps.setString(1,"秀儿"); int num = ps.executeUpdate(); System.out.println("删除了" + num + "条信息"); } catch (Exception e) { e.printStackTrace(); } finally { try { if (ps != null) ps.close(); if (con != null) con.close(); } catch (Exception e) { e.printStackTrace(); } } } }

（3）修改：

package pers.Pre.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class UpdateDemo {
    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement ps = null;

        try { Class.forName("com.mysql.jdbc.Driver"); String url = "jdbc:mysql://localhost:3306/student mangement system"; String username = "root"; String password = "root"; con = DriverManager.getConnection(url, username, password); String updateSql = "Update Login set password=? where account ='学前班秀儿'"; ps = con.prepareStatement(updateSql);// 获取预处理对象 ps.setString(1,"201908"); int num = ps.executeUpdate(); System.out.println("更改了" + num + "条信息"); } catch (Exception e) { e.printStackTrace(); } finally { try { if (ps != null) ps.close(); if (con != null) con.close(); } catch (Exception e) { e.printStackTrace(); } } } }

（4）查询：

package pers.Pre.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class SelectDemo {
    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null; try { Class.forName("com.mysql.jdbc.Driver"); String url = "jdbc:mysql://localhost:3306/student mangement system"; String username = "root"; String password = "root"; con = DriverManager.getConnection(url, username, password); String selectSql = "select * from score"; ps = con.prepareStatement(selectSql);// 获取预处理对象 rs = ps.executeQuery(); System.out.println(" "+"学号"+" "+"班级"+" "+"平时成绩"+" "+"期末成绩"); while (rs.next()) { System.out.println( rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3)+" "+ rs.getString(4)); } } catch (Exception e) { e.printStackTrace(); } finally { try { if (ps != null) ps.close(); if (con != null) con.close(); if (rs!=null) rs.close(); } catch (Exception e) { e.printStackTrace(); } } } }

（5）Statement接口与PreparedStatement接口的对比：

Statement接口用于处理不带参数的静态sql语句，PreparedStatement接口可以处理带参数的sql语句。

（6）防注入攻击

可以注入攻击的登录：

package pers.jdbc.log;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.*;

public class Log {

    public static void main(String[] args) throws Exception {
        // 输入用户名和密码
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入账号"); String zh = sc.nextLine(); System.out.println("请输入密码"); String mm = sc.nextLine(); // 到数据库验证用户名和密码是否正确 Class.forName("com.mysql.jdbc.Driver"); String url = "jdbc:mysql://localhost:3306/student mangement system"; String username = "root"; String password = "root"; Connection con = DriverManager.getConnection(url, username, password); Statement state = con.createStatement(); String sql = "select * from Login where account='" + zh + "' and password='" + mm + "'"; ResultSet re = state.executeQuery(sql); // 输出：正确显示欢迎，不正确显示错误 if (re.next()) { System.out.println("登陆成功！"); } else { System.out.println("输入账号或密码错误"); } state.close(); con.close(); re.close(); } }

35_4.png

35_5.png

可以看出，在登陆的数据表中，并没有与之相对应的用户名和密码，但是也能登录成功。 其实在java代码中，运行的查询语句是这样的：select * from Login where account=’zhai’and password=’1′ or 1=1，运行结果为true。

注意：对是否登录成功的判断不能以是否为空作为标准，要以next()方法作为标准去判断

注入攻击的防止：

package pers.jdbc.log;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner;

public class Log1 {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/student mangement system";
        String username = "root"; String password = "root"; Connection con = DriverManager.getConnection(url, username, password); Scanner sc = new Scanner(System.in); System.out.println("请输入账号"); String account = sc.nextLine(); System.out.println("请输入密码"); String password1 = sc.nextLine(); // 执行SQL语句,数据表,查询用户名和密码,如果存在,登录成功,不存在登录失败 String sql = "SELECT * FROM login WHERE account=? AND password=?"; // 调用Connection接口的方法prepareStatement,获取PrepareStatement接口的实现类 // 方法中参数,SQL语句中的参数全部采用问号占位符 PreparedStatement pst = con.prepareStatement(sql); // 调用p s t对象set方法,设置问号占位符上的参数 pst.setObject(1, account); pst.setObject(2, password1); // 调用方法,执行SQL,获取结果集 ResultSet rs = pst.executeQuery(); while (rs.next()) { System.out.println(rs.getString("account") + "欢迎你"); } rs.close(); pst.close(); con.close(); sc.close(); } }

在java代码中，如果还进行注入攻击，运行的查询语句是这样的：select * from Login where account=’zhai’and password=1′ or ‘1=1，将运行错误。

3、DBUtils实现增删改查（QueryRunner类）

利用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）查询：

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

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

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

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

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://tech.souyunku.com/22632

AD：【JetBrains 全家桶，激活、破解、教程】获取 IDEA 激活码、PyCharm 激活码、WebStorm 激活码和 DataGrip 激活码，提供详细破解教程与永久激活方法。支持 IDEA 永久激活与破解，免费获取注册码与激活码，解决 2024/2025 版本激活问题，轻松实现所有 JetBrains 工具的激活。
未经允许不得转载：搜云库技术团队 » 增删改查（Statement接口、PreparedStatement接口、DBUtils（QueryRunner类））

相关推荐

JetBrains 全家桶，激活、破解、教程

提供 JetBrains 全家桶激活码、注册码、破解补丁下载及详细激活教程，支持 IntelliJ IDEA、PyCharm、WebStorm 等工具的永久激活。无论是破解教程，还是最新激活码，均可免费获得，帮助开发者解决常见激活问题，确保轻松破解并快速使用 JetBrains 软件。获取免费的破解补丁和激活码，快速解决激活难题，全面覆盖 2024/2025 版本！

联系我们联系我们