JDBC的操作方法补充

JDBC的操作方法补充

JDBC 指 Java 数据库连接，是一种标准Java应用编程接口JAVA API，用来连接 Java 编程语言和广泛的数据库

create table tb_student(
id bigint primary key auto_increment comment '学生编号',
name varchar(32) not null
)engine=innodb default charset utf8mb4;

获取自增长键值

!通过JDBC往数据库的表格中添加一条记录，其中有一个字段是自增的 id bigint primary key auto_increment ，那么在JDBC这边怎么在添加之后直接获取到这个自增的值。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zPsRiuEm-1654433653544)(file:///C:/Users/ADMINI~1/AppData/Local/Temp/msohtmlclip1/01/clip_image004.gif)]PreparedStatement是Statement的子接口。Statement接口中有一些常量值，如Statement.RETURN_GENERATED_KEYS

要先添加后获取到自增的key值：

（1）PreparedStatement pst = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);

（2）添加sql执行完成后,通过PreparedStatement的对象调用getGeneratedKeys()方法来获取自增长键 值，遍历结果集 ResultSet rs = pst.getGeneratedKeys();

try (Connection conn = DriverManager.getConnection("jdbc:mysql:///test?
serverTimezone=UTC", "root",
"123456");) {
String sql = "insert into tb_student(name) values(?)";
// 默认不返回生成的key值，如果需要返回自增长的主键值，则需要添加一个常量值作为参数。默认值
NO_GENERATED_KEYS
PreparedStatement ps = conn.prepareStatement(sql,
Statement.RETURN_GENERATED_KEYS);
ps.setString(1, "赵小胖2");
int pos = ps.executeUpdate();
if (pos > 0) {
System.out.println("插入成功!");
//如果插入成功则会返回插入数据对应的主键值
//从ps中获取到服务器端返回的键值
ResultSet rs = ps.getGeneratedKeys();//因为这里的key值可能多个，因为insert语
句可以同时添加多行，所以用ResultSet封装
//这里因为只添加一条，所以用if判断
    if(rs.next()){
Object key = rs.getObject(1);
System.out.println("自增的key值id =" + key);
}
rs.close();
} else
System.out.println("插入失败!");
ps.close();
}

批处理

批处理：批量处理sql。例如1）订单明细表的多条记录的添加。2）批量添加模拟数据 等

不用批处理，和用批处理有什么不同？ 批处理的效率很多

如何进行批处理操作？

ps.addBatch();
int[] all = ps.executeBatch();

注意：如果批量添加时，insert使用values,不要使用value

try (Connection conn = DriverManager.getConnection("jdbc:mysql:///test?
serverTimezone=UTC", "root",
"123456");) {
String sql = "insert into tb_student(name) values(?)";
PreparedStatement ps = conn.prepareStatement(sql);
// 一次性插入100条数据
for (int i = 0; i < 100; i++) {
ps.setString(1, "张_" + i);
ps.addBatch(); //添加到批处理一组操作中，攒一块处理
//如果一次性提交的数据量过大，允许部分提交
if(i%10==0){
int[] res=ps.executeBatch();
System.out.println(i+":"+Arrays.toString(res));
ps.clearBatch();
}
}
int[] pos = ps.executeBatch(); //执行批处理，返回各个对应处理的结果
System.out.println(Arrays.toString(pos));
ps.close();
}

如果批处理优势不明显，则没有必要使用批处理了，因为限制太多了

可滚动结果集

默认情况下ResultSet结果集是单向的，也就是只能通过rs.next方法单向的读取数据；如果在读取数据库 中的记录时不用逐个逐个的读取，而可以根据具体需要跳过几条记录再进行读取，或者从后向前读取。

PreparedStatement prepareStatement(String sql, int resultSetType,int
resultSetConcurrency) throws SQLException;

• 参数1为要指定的sql语句
• 参数2为结果集的类型，可以使用的ResultSet接口中的3个常量值
  • TYPE_SCROLL_INSENSITIVE 可滚动不敏感
  • TYPE_SCROLL_SENSITIVE 可滚动敏感的
  • TYPE_FORWARD_ONLY 单向滚动，只能调用next
• 参数3用于设置结果集的并发性支持
  • CONCUR_READ_ONLY 并发访问时只读。其中concur译作同时发生，即并发，即当并发访问结果集的时候只能够只读访问，也就是只能读取里面的内容，不能做更改
  • CONCUR_UPDATABLE 访问的同时允许修改

要做到这点就是在创建Statement对象时使用两个属性做参数

Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY) 

try (Connection conn = DriverManager.getConnection("jdbc:mysql:///test?
serverTimezone=UTC", "root", "123456")) {
PreparedStatement ps = conn.prepareStatement("select * from tb_student",
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet rs = ps.executeQuery();
rs.last();// 将行指针指向到最后一行
do {
long id = rs.getLong("id");
String name = rs.getString("name");
System.out.println(id + "\t" + name);
} while (rs.previous());
rs.close();
ps.close();
}

获取一个可滚动结果集ResultSet引用rs之后，就可以使用结果集的方法使行指针任意滚动的方法

• rs.last() //使游标指向最后一行记录
• rs.isLast() //判断是否指向最后一行记录
• rs.isAfterLast() //判断是否指向最行一行记录的上面一行，即倒数第二行rs.getRow() //返回当前游标所指向的是第几行
• rs.previous() //使游标指向当前所指向的行的上一行rs.absolute(int x) // 使游标直接指向第x行

跨数据库平台分页显示

真正在数据显示时，不能一次性加载所有数据，建议采用的是按需加载

public class App2 {
public static void main(String[] args) {
// 显示第5页数据，每页10条数据
int pageNum = 5;
int rowsPerPage = 10;
List<Student> res = new ArrayList<>();
   try (Connection conn =
DriverManager.getConnection("jdbc:mysql:///test?serverTimezone=UTC", "root",
"123456")) {
// 如果考虑跨数据库平台，这里的sql应该使用标准sql
String sql = "select * from tb_student";
PreparedStatement ps = conn.prepareStatement(sql,
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet rs = ps.executeQuery();
// 需要判断页码值的合理性,页码值需要大于0，小于等于最大页码值
// 如果页码值小于1，则为1；如果大于最大页码值则为最大页码值
if (rs.last()) { //将行指针移动到最后一行
int rowNum = rs.getRow(); // 获取总行数
int maxPage = rowNum / rowsPerPage;
if (rowNum % rowsPerPage != 0)
maxPage++;
if (pageNum < 1)
pageNum = 1;
if (pageNum > maxPage)
pageNum = maxPage;
int begin = (pageNum - 1) * rowsPerPage + 1;// 起始行号
rs.absolute(begin);// 将行指针移动到指定的行上
int count = 0;
do {
Student s = new Student();
s.setId(rs.getLong("id"));
s.setName(rs.getString("name"));
res.add(s);
count++;
if (count >= rowsPerPage)
break;
} while (rs.next());
}
} catch (Exception e) {
e.printStackTrace();
}
for (Student tmp : res)
System.out.println(tmp);
}
}
class Student implements Serializable {
private Long id;
private String name;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
   public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "Student [id=" + id + ", name=" + name + "]";
}
}

limit

MySQL针对获取部分数据提供了关键字limit，可以用于获取查询结果集中的部分数据 例如获取前10条数据 select * from tb_student limit 10;

• 实际上等价于select * from tb_student limit 0,10

获取从第5行开始的3条数据select * from tb_student limit 5-1,3;

• 行数从0开始

可修改的结果集

由于执行性能的问题，一般不建议使用

// MySQL驱动支持JDBC4
try (Connection conn = DriverManager.getConnection("jdbc:mysql:///test?
serverTimezone=UTC", "root", "123456")) {
PreparedStatement ps = conn.prepareStatement("select * from tb_student",
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = ps.executeQuery();
//获取第3行数据
rs.absolute(3);
//在当前行上执行修改指定列数据的操作
rs.updateString("name","东方星雨");
rs.updateRow();//修改修改
// 插入数据
rs.moveToInsertRow();//将结果集指针移动到插入行上
rs.updateString("name","张三");
rs.insertRow();//插入数据
//删除当前行数据,删除第5行数据
rs.absolute(5);
rs.deleteRow();//删除当前行
rs.close();
ps.close();
}

特殊占位符

• 模糊查询， select * from t_user where username like ‘%a%’;

  使用 concat(‘%a%’)

  select * from t_user where username like concat(‘%’,?,‘%’)

• 批量删除String ids = “1,2,3”; delete from t_user where id in(‘1,2,3’);

  • 方法一：用 in 并且字符串拼接。 String ids = “1,2,3”; ，执行sql String sql=“delete from t_user where id in(” + ids + “)”;
  • 方法二：用 or ，并且用 1 = 1 或 1 != 1 在 where 的条件判断中，可以使用 1 = 1 更好的拼接 and 条件，使用 1 != 1 更好的拼接 or 条件 呈现结果如： delete from t_user where 1!=1 or id = 1 or id = 2 or id = 3

通用SQL函数

语法select 函数名称(); 或者select 函数名称(列名称，其它参数) from 表名称 ，在mysql中from子句不是必须的

名称	描述
abs()	返回绝对值
rand()	返回一个随机浮点值
+ -	尽量转换为数字进行计算，不能转为0
ceiling()	返回不小于参数的最小整数值
floor()	返回不大于参数的最大整数值
length()和char_length()	以字节返回字串长度
left(被截取字串，截取长度)	返回指定的最左边的字符数，SELECT LEFT(‘www.zhansan3g.com’,8)
right()	返回指定的最右边的字符数
trim()	删除前导和尾随空格
curdate()	返回当前日期
concat(列1，列2，…)	返回串联字符串
ifnull(列名称,‘默认值’)
format()	返回格式化到指定小数位数的数字，select format(salary,1); 四舍五入
date_format(d,f)	按照表达式f要求显示日期select date_format(now(), ‘%Y-%m-%d’)
now()	返回当前日期和时间
uuid()	返回通用唯一标识符
user()	客户端提供的用户名和主机名
database()	返回默认（当前）数据库名称

• CONCAT(A, B) – 连接两个字符串值以创建单个字符串输出。通常用于将两个或多个字段合并为一个字段。
• LENGTH(str)获取以字节为单位的字符串长度；CHAR_LENGTH函数获取字符串的长度，以字符为 单位计算长度
• FORMAT(X, D)- 格式化数字X到D有效数字。

FOMRAT(N,D,locale);将数字N格式化为格式，如 "#,###,###.##" ，舍入到D 位小数。它返回一个值作为字符串。其中N是要格式化的数字。D是要舍入的小数位数。locale是一个可选 参数，用于确定千个分隔符和分隔符之间的分组。如果省略locale操作符，MySQL将默认使 用en_US。

SELECT FORMAT(14500.2018, 2); 返回14,500.20

• CURRDATE(), CURRTIME()- 返回当前日期或时间。
• NOW（） – 将当前日期和时间作为一个值返回。另外MONTH（），DAY（），YEAR（），
• WEEK（），WEEKDAY（） – 从日期值中提取给定数据。HOUR（），MINUTE（），
• SECOND（） – 从时间值中提取给定数据。
• DATEDIFF（A，B） – 确定两个日期之间的天数差异，通常用于计算年龄

SELECT DATED IFF('2008-12-29','2008-12-30') AS DiffDate
ROUND(DATEDIFF(requiredDate, orderDate) / 365, 1)  四舍五入到1位小数

• SUBTIMES（A，B） – 用于执行时间的减法运算。

SUBTIME('2018-10-31 23:59:59','0:1:1') 返回2018-10-31 23:58:58

• FROM_DAYS（INT） – 将整数天数转换为日期值。

TO_DAYS(date) 给出一个日期 date ，返回一个天数(从 0 年开始的天数) SELECT TO_DAYS('1997-10-07'); -> 729669

FROM_DAYS(N)给出一个天数 N，返回一个 DATE 值

SELECT FROM_DAYS(729669); -> '1997-10-07'

IFNULL() 函数用于判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值

SELECT IFNULL(price,0.0);

',‘0:1:1’) 返回2018-10-31 23:58:58

- FROM_DAYS（INT） – 将整数天数转换为日期值。

TO_DAYS(date) 给出一个日期 date ，返回一个天数(从 0 年开始的天数) SELECT TO_DAYS(‘1997-10-07’); -> 729669

FROM_DAYS(N)给出一个天数 N，返回一个 DATE 值

SELECT FROM_DAYS(729669); -> ‘1997-10-07’

IFNULL() 函数用于判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值

SELECT IFNULL(price,0.0);