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

转载

mob60475701b76f 2019-08-19 09:13:00

文章标签 sql java mysql apache sql语句 文章分类 代码人生

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

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

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

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

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

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

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

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

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

可以看出，在登陆的数据表中，并没有与之相对应的用户名和密码，但是也能登录成功。其实在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);     }  }

每个人都会有一段异常艰难的时光。生活的压力，工作的失意，学业的压力。爱的惶惶不可终日。挺过来的，人生就会豁然开朗。挺不过来的，时间也会教你，怎么与它们握手言和，所以不必害怕的。 ——杨绛

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。