侧边栏壁纸
博主头像
LYMTICS

海纳百川,有容乃大

  • 累计撰写 45 篇文章
  • 累计创建 37 个标签
  • 累计收到 19 条评论

目 录CONTENT

文章目录

JDBC系列二:基本增删改查

LYMTICS
2021-07-30 / 0 评论 / 0 点赞 / 65 阅读 / 9,765 字 / 正在检测是否收录...
温馨提示:
本文最后更新于 2022-02-01,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。

基本增删改查

PreparedStatement的引入

获取数据库连接后, 要对数据库进行各种操作.

java.sql 包中有3个接口分别定义了对数据库不同的调用方式:

  1. Statement:用于执行静态 SQL 语句并返回它所生成结果的对象。
  2. PrepatedStatement:SQL 语句被预编译并存储在此对象中,可以使用此对象多次高效地执行该语句。
  3. CallableStatement:用于执行 SQL 存储过程

PreparedStatement 之前, 我们用的是Statement 类, 但是这个类有SQL注入 的安全漏洞, 且存在拼串操作,繁琐的问题, 所以被我们弃用. 下面我们稍微举一个例子, 来了解一下什么是SQL注入:

// 获取用户输入
Scanner scan = new Scanner(System.in);
System.out.print("用户名:");
String userName = scan.nextLine();
System.out.print("密   码:");
String password = scan.nextLine();

// 拼接字符串(拼串操作看着不清晰, 麻烦)
String sql = "SELECT user,password FROM user_table WHERE USER = '" + userName + "' AND PASSWORD = '" + password + "'";

// 对用户输入进行验证
User user = get(sql, User.class);
if (user != null) {
    System.out.println("登陆成功!");
} else {
    System.out.println("用户名或密码错误!");
}

// 用户输入的字符串如果不经过检查就直接拼接进来的话, 可能会造成SQL注入问题
// 比如, 假如用户输入了某些内容后, sql语句经过拼接后可能会形成:
// SELECT user,password FROM user_table 
// WHERE USER = '1' or ' AND PASSWORD = '='1' or '1' = '1';
// 这样user就会!=null了

PreParedStatementStatement 的子接口, 大概意思就是这个接口的实现类对每一条SQL语句都进行了预编译, 我们在使用时只用在对应的位置填充相应的参数就可以了. 很显然, 这种方式会带来两个好处:

  1. 更安全了, 防止SQL注入
  2. 由于预编译了, 所以效率会快一点

image-20210715203845914

PreparedStatement的使用

整体步骤:

  1. String sql 中保存SQL语句(可以使用占位符占位)
  2. 用连接的 preparedStatement(String sql) 方法获取一个PreparedStatement对象
  3. 如果有, 填充第一步中的占位符
  4. preparedStatement对象执行相应的操作

示例: 插入一条数据

这里省略了连接、关闭数据库的相关代码以及对异常的处理.

// 1. 编写SQL语句, 注意这里会有?作为占位符
String sql = "INSERT INTO customers(name, email, birth) VALUES(?,?,?);";

// 2. 获取PreparedStatement对象
PreparedStatement ps = conn.preparedStatement(sql);

// 3. 替换占位符(按照占位符需要的数据类型)
ps.setString("张三"); // name 是 String 类型
ps.setString("123@qq.com"); // email 也是
ps.setString("1999-2-10");  // birth 可以是String类型

// 4. 执行SQL语句
ps.execute();

插入操作是一个很常用的功能, 我们不妨这个操作进行进一步的封装, 这样可以让我们增加代码的复用性(少敲一点代码):

// 尽管这块代码并不算漂亮, 但是相信可以提供一种编程的思想
public static void commonUpdate(String sql, Object ...args){
    Connection connection = null;
    PreparedStatement ps = null;
    try {
        // getConnect()是自己封装的方法
        // 用来获取一个Connection对象, 连接数据库
        connection = getConnection();
        ps = connection.prepareStatement(sql);
        for (int i = 0; i < args.length; i++) {
            // i 从 1 开始
            ps.setObject(i+1, args[i]);
        }
        ps.execute();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        closeConnection(connection, ps);
    }
}

实现删改功能

插入操作和删除操作、修改操作的区别不大, 所以如果搞明白了上一块是怎么封装的, 这一块也是同样的道理(甚至代码都不用改):

/**
     * 完成增删改的通用代码
     * @param sql String sql语句
     * @param args 可变参数列表
     */
public static void commonUpdate(String sql, Object ...args){
    Connection connection = null;
    PreparedStatement ps = null;
    try {
        connection = getConnection();
        ps = connection.prepareStatement(sql);
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i+1, args[i]);
        }
        ps.execute();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        closeConnection(connection, ps);
    }
}

查找操作

基本查找实现

查找操作增删改操作相比, 由于查找需要数据库返回一定的数据, 所以相对而言稍微复杂了一点.

由于不同的查询语句返回值的结构可能不同, 所以, 首先我们需要一种可以保存返回值的类.

ORM思想(Object Relational Mapping)

  • 一个数据表对应一个Java类
  • 表中一条记录对应Java类的一个对象
  • 表中一个字段对应Java类的一个属性

比如, 假如我们想查询customer表, 就需要有一个如下的java类:

public class Customer {
   // 这些数据域对应SQL中表的项, 也就是查询的结果包含哪些数据
   private int id;
   private String name;
   private String email;
   private Date birth;
}
Java类型SQL类型
booleanBIT
byteTINYINT
shortSMALLINT
intINTEGER
longBIGINT
StringCHAR,VARCHAR,LONGVARCHAR
byte arrayBINARY , VAR BINARY
java.sql.DateDATE
java.sql.TimeTIME
java.sql.TimestampTIMESTAMP
Connection connection = null;
PreparedStatement ps = null;
ResultSet resultSet = null;
try {
    // 自己封装的方法, 获取Connection对象
    connection = JDBCUtils.getConnection();
    String sql = "SELECT id, name, email, birth " +
        "FROM customers";
    ps = connection.prepareStatement(sql);

    // 执行, 并返回结果集
    resultSet = ps.executeQuery();
    // 处理结果集
    // 判断结果集的下一条是否有数据, 并且有则下移
    // 相当于迭代器中hasNext()和next()的功能
    while (resultSet.next()) {
        int id = resultSet.getInt(1);
        String name = resultSet.getString(2);
        String email = resultSet.getString(3);
        Date birth = resultSet.getDate(4);

        // 展示:
        // ORM思想 (Object Relational Mapping)
        // 一个数据表对应一个Java类
        // 表中一条记录对应Java类的一个对象
        // 表中一个字段对应Java类的一个属性
        System.out.println(new Customer(id, name ,email, birth));
}

针对单表通用查询

下面尝试将代码通用化.

我们通过next()方法来不断遍历, 直到所有的行都被遍历完. 这只是在行层面上的遍历, 然而, 每一次用户查询了多少列是未知的, 我们如何获取列数和对应的数据名称呢?

通过修改上述代码, 我们可以写一个只针对Customer表的、通用的查询方法:

/**
     * 针对Customer表的通用查找操作
     * @param sql SQL语句
     * @param args 其他参数
     * @throws Exception 错误
     */
public static void commonQueryForCustomer(String sql, Object ...args) throws Exception {
    // 获取数据库连接
    Connection connection = getConnection();
    PreparedStatement ps = connection.prepareStatement(sql);
    // 填充参数
    for (int i = 0; i < args.length; i++) {
        ps.setObject(i+1, args[i]);
    }
    // 执行
    ResultSet resultSet = ps.executeQuery();
    // 获取源数据, 之后会用到
    ResultSetMetaData metaData = resultSet.getMetaData();
	// 用源数据获取列数
    int columnCount = metaData.getColumnCount();
    try {
        // 遍历每一行
        while (resultSet.next()) {
            Customer c = new Customer();
            for (int i = 0; i < columnCount; ++i) {
                // 遍历每一列
                Object object = resultSet.getObject(i+1);
                String columnName = metaData.getColumnName(i+1);
                // 利用反射机制为Customer c的变量域赋值
                Field field = Customer.class.getDeclaredField(columnName);
                field.setAccessible(true);
                field.set(c, object);
            }
            System.out.println(c);
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        closeConnection(connection, ps);
    }
}

回顾上面的代码, 我们获取了元数据, 然后又用元数据中的相关参数获取了列数和列对应的名称.

ResultSetMetaData常用方法

  • 获取: ResultSetMetaData meta = rs.getMetaData();

  • getColumnName(int column):获取指定列的名称

  • getColumnLabel(int column):获取指定列的别名

  • getColumnCount():返回当前 ResultSet 对象中的列数。

  • getColumnTypeName(int column):检索指定列的数据库特定的类型名称。

  • getColumnDisplaySize(int column):指示指定列的最大标准宽度,以字符为单位。

  • isNullable(int column):指示指定列中的值是否可以为 null。

  • isAutoIncrement(int column):指示是否自动为指定列进行编号,这样这些列仍然是只读的。

另外还有一个需要注意的问题:

由于数据库中往往是下划线命名法, 但是在Java中常采用小驼峰命名, 所以有可能出现名称不一致的情况, 比如:

// MySQL中: student(s_name, s_id, s_age);
class Student {
    private String sName;
    private String sId;
    private String sAge;
    // ....
}

此时的解决方法是起别名, 并用getColumnLabel(int column)方法代替getColumnName

// SQL语句中要起别名
String sql = "SELECT s_id sID, s_name sName, s_age sAge" +
    "FROM customers";
// ...
// 获取列的名称, 不然怎么知道赋值给谁
// String columnName = rsmd.getColumnName(i + 1);
String columnLabel = rsmd.getColumnLabel(i + 1);

// 通过反射, 将对象指定名columnName的属性赋值为指定的columnValue
// Field field = Order.class.getDeclaredField(columnName);
Field field = Order.class.getDeclaredField(columnLabel);

注意不要写错了!

getFields():获得某个类的所有的公共(public)的字段,包括父类中的字段。
getDeclaredFields():获得某个类的
所有声明的字段
,即包括public、private和proteced,但是不包括父类的申明字段

任意表通用查询

上一节中代码之所以是只针对Customer 表, 是因为如下两段代码:

Customer c = new Customer();

// 利用反射机制为Customer c的变量域赋值
Field field = Customer.class.getDeclaredField(columnName);

这两段代码是写死的, 所以我们的查询操作只能针对于Customer表进行, 也就是说, 如果我们想针对多个表进行查询, 则要修改这个语句, 使得可以动态地决定要创建的类是什么(也就是要利用泛型技术):

下面代码改动的要点:

  1. 用泛型<T>代表类型, 让用户自行传入类的类型

  2. 创建类的代码改为了用反射机制动态创建:

T c = clazz.getDeclaredConstructor().newInstance();
  1. 操作类的域时, 使用用户传入的类的类型
Field field = clazz.getDeclaredField(columnName);
/**
     * 通用查询(只能返回一个值)
     * @param clazz 类别
     * @param sql   SQL语句
     * @param args 参数
     * @param <T> 类别
     * @return 查询结果对象
     * @throws Exception 出错
     */
public static <T> T commonQuery(Class<T> clazz, String sql, Object ...args) throws Exception {
    Connection connection = getConnection();
    PreparedStatement ps = connection.prepareStatement(sql);
    for (int i = 0; i < args.length; i++) {
        ps.setObject(i+1, args[i]);
    }
    ResultSet resultSet = ps.executeQuery();
    ResultSetMetaData metaData = resultSet.getMetaData();

    int columnCount = metaData.getColumnCount();
    try {
        // 遍历每一行
        while (resultSet.next()) {

            // Customer c = new Customer();
            // 创建类
            T c = clazz.getDeclaredConstructor().newInstance();

            for (int i = 0; i < columnCount; ++i) {
                // 遍历每一列
                Object object = resultSet.getObject(i+1);
                String columnName = metaData.getColumnLabel(i+1);
                // 利用反射机制为Customer c的变量域赋值
                Field field = clazz.getDeclaredField(columnName);
                field.setAccessible(true);
                field.set(c, object);
            }
            return c;
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        closeConnection(connection, ps, resultSet);
    }
    return null;
}

注意: 有时数据库中表名是数据库的关键词, 此时应用`括住, 如:

SELECT order_id orderId, order_name orderName FROM `Order` WHERE order_id = 1;

通用查询(返回多个结果)

将返回值改为ArrayList<T> T即可:

/**
* 通用查询的进阶版:可以返回多个结果
* @param clazz 类别
* @param sql SQL语句
* @param args 参数
* @param <T> 类别
* @return 返回多个结果的ArrayList
*/
public static <T>ArrayList<T> commonQueryPlus(Class<T> clazz, String sql, Object ...args) throws Exception {
    Connection connection = JDBCUtils.getConnection();
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    for (int i = 0; i < args.length; i++) {
        preparedStatement.setObject(i+1, args[i]);
    }

    ArrayList<T> arrayList = new ArrayList<>();

    ResultSet resultSet = preparedStatement.executeQuery();
    ResultSetMetaData metaData = resultSet.getMetaData();
    int columnCount = metaData.getColumnCount();

    // 遍历行
    try {
        while (resultSet.next()) {
            T t = clazz.newInstance();
            for (int i = 0; i < columnCount; i++) {
                Object object = resultSet.getObject(i + 1);
                String columnLabel = metaData.getColumnLabel(i + 1);

                // 通过反射复制
                Field field = clazz.getDeclaredField(columnLabel);
                field.setAccessible(true);
                field.set(t, object);
            }
            arrayList.add(t);
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtils.closeConnection(connection, preparedStatement, resultSet);
    }
    return arrayList;
}

P.S.和Statement比较

操作BLOB类型字段

BLOB类型

  • MySQL中,BLOB是一个二进制大型对象,是一个可以存储大量数据的容器,它能容纳不同大小的数据。

  • 插入BLOB类型的数据必须使用PreparedStatement,因为BLOB类型的数据无法使用字符串拼接写的。

  • MySQL的四种BLOB类型(除了在存储的最大信息量上不同外,他们是等同的)

    image-20210721103455235

  • 需要注意的是:如果存储的文件过大,数据库的性能会下降。

  • 如果在指定了相关的Blob类型以后,还报错:xxx too large,那么在mysql的安装目录下,找my.ini文件加上如下的配置参数: max_allowed_packet=16M。同时注意:修改了my.ini文件之后,需要重新启动mysql服务。


删除操作不涉及具体的类型, 所以这里重点书写插入、修改和读取的操作:

插入

与插入普通数据不同, 插入Blob只需将相应文件的流传入即可(记得关闭is)

// ...
String sql = "INSERT INTO customers VALUES(?,?,?,?,?)";
// ...
FileInputStream is = new FileInputStream("moon.png");
ps.setObject(5, is);
// ...

修改

同理, 修改也很简单(记得关闭is)

// ...
String sql = "update customers set photo = ? where id = ?";
// ...
// 填充占位符
// 操作Blob类型的变量
FileInputStream fis = new FileInputStream("coffee.png");
ps.setBlob(1, fis);
ps.setInt(2, 25);
// ...

读取

读取就是将数据库中的图片保存到本地文件目录中, 结合上面的操作, 不难想象, 读取操作应该也和流有关, 并且是获取一个流, 然后利用OutputStream流传输到本地(很显然, 你要清楚流相关的操作)

// ...
String sql = "SELECT photo FROM customers where id = ?";
// ...
Blob blob = resultSet.getBlob(1);
is = blob.getBinaryStream();
OutputStream os = new FileOutputStream("result.png");
byte[] buffer = new byte[1024];
int len = 0;
while ((len = is.read(buffer)) != -1) {
    os.write(buffer, 0, len);
}
// ...

批量插入

当一次需要插入多条数据时, 采用合理的方法和策略可以有效地优化语句.

易错点

序号问题

问题描述: 在SQL中一般是从0开始计数, 而在Java中则是从0开始计数. 编写代码时要注意区分, 防止越界.

从1开始的几个地方:

// 1. 给占位符赋值的时候
for (int i = 0; i < args.length; i++) {
    ps.setObject(i+1, args[i]);		// 注意第一个从1开始
}

// 2. 遍历每一行时, 对每一列的操作:
Object object = resultSet.getObject(i+1);
String columnName = metaData.getColumnLabel(i+1);
0

评论区