基本增删改查
PreparedStatement的引入
获取数据库连接后, 要对数据库进行各种操作.
在 java.sql 包中有3个接口分别定义了对数据库不同的调用方式:
- Statement：用于执行静态 SQL 语句并返回它所生成结果的对象。
- PrepatedStatement：SQL 语句被预编译并存储在此对象中，可以使用此对象多次高效地执行该语句。
- 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了
PreParedStatement 是 Statement 的子接口, 大概意思就是这个接口的实现类对每一条SQL语句都进行了预编译, 我们在使用时只用在对应的位置填充相应的参数就可以了. 很显然, 这种方式会带来两个好处:
- 更安全了, 防止SQL注入
- 由于预编译了, 所以效率会快一点
PreparedStatement的使用
整体步骤:
- 在
String sql中保存SQL语句(可以使用占位符占位) - 用连接的
preparedStatement(String sql)方法获取一个PreparedStatement对象 - 如果有, 填充第一步中的占位符
- 用
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类型 boolean BIT byte TINYINT short SMALLINT int INTEGER long BIGINT String CHAR,VARCHAR,LONGVARCHAR byte array BINARY , VAR BINARY java.sql.Date DATE java.sql.Time TIME java.sql.Timestamp TIMESTAMP
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表进行, 也就是说, 如果我们想针对多个表进行查询, 则要修改这个语句, 使得可以动态地决定要创建的类是什么(也就是要利用泛型技术):
下面代码改动的要点:
用泛型<T>代表类型, 让用户自行传入类的类型
创建类的代码改为了用反射机制动态创建:
T c = clazz.getDeclaredConstructor().newInstance();
- 操作类的域时, 使用用户传入的类的类型
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类型(除了在存储的最大信息量上不同外，他们是等同的)
-
需要注意的是：如果存储的文件过大，数据库的性能会下降。
-
如果在指定了相关的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);
评论区