CRUD
CRUD是最常见的数据库操作,即增删改查
- C 增加(Create)
- R 读取查询(Retrieve)
- U 更新(Update)
- D 删除(Delete)
在JDBC中增加、删除、修改的操作都很类似,只是传递不同的SQL语句就行了。
查询因为要返回数据,和上面不同。
1. 增加
这段Hello JDBC中的代码,用于向数据库插入数据。
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class TestJDBC {
public static void main(String[] args) {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try (
Connection c = DriverManager.getConnection(
"jdbc:mysql://127.0.0.1:3306/jdbcTest?serverTimezone=Asia/Shanghai&characterEncoding=utf8",
"root", "admin");
Statement s = c.createStatement();
)
{
String sql = "insert into hero values(null," + "'提莫'" + "," + 313.0f + "," + 50 + ")";
s.execute(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
2. 删除
与增加很类似,只不过是指向的SQL语句不一样罢了。
String sql = "delete from hero where id = 5";
3. 修改
String sql = "update hero set name = 'name 5' where id = 3";
4. 查询
4.1 查询语句
executeQuery执行SQL查询语句
注意:在读取第二列的数据的时候,用的是rs.get(2),而不是get(1)。这个是整个Java自带的api里唯二的地方,使用基1的,即2就代表第二个。
另一个地方是在PreparedStatement。
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestJDBC {
public static void main(String[] args) {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try (
Connection c = DriverManager.getConnection(
"jdbc:mysql://127.0.0.1:3306/jdbcTest?serverTimezone=Asia/Shanghai&characterEncoding=utf8",
"root", "admin");
Statement s = c.createStatement();
)
{
String sql = "select * from hero";
//执行查询语句,并把结果集返回给ResultSet
ResultSet rs = s.executeQuery(sql);
while(rs.next()) {
int id = rs.getInt("id");//可以使用字段名
String name = rs.getString(2);//也可以使用字段的顺序
float hp = rs.getFloat("hp");
int damage = rs.getInt(4);
System.out.printf("%d\t%s\t%f\t%d\n",id,name,hp,damage);
}
//不一定要在这里关闭ResultSet,因为Statement关闭的时候,会自动关闭ResultSet
//rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
4.2 SQL语句判断账号密码是否正确
- 创建一个用户表,有字段name,password
- 插入一条数据:insert into values(null,'dashen','thisispassword');
- SQL语句判断账号密码是否正确
判断账号密码的正确方式是根据账号和密码到表中去找数据,如果有数据,就表明密码正确了,如果没有数据,就表明密码错误。
不恰当的方式是吧user表的数据全部查到内存中,挨个进行比较。如果users表里有100万条数据的话,内存是不够用的。
SQL
CREATE TABLE user (
id int(11) AUTO_INCREMENT,
name varchar(30) ,
password varchar(30),
PRIMARY KEY (id)
) ;
insert into user values(null,'dashen','thisispassword');
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestJDBC {
public static void main(String[] args) {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try (
Connection c = DriverManager.getConnection(
"jdbc:mysql://127.0.0.1:3306/jdbcTest?serverTimezone=Asia/Shanghai&characterEncoding=utf8",
"root", "admin");
Statement s = c.createStatement();
)
{
String name = "dashen";
String password = "thisispassword";
String sql = "select * from user where name = '"+name+"' and password = '"+password+"'";
//执行查询语句,并把结果集返回给ResultSet
ResultSet rs = s.executeQuery(sql);
if(rs.next())
System.out.println("账号密码正确");
else
System.out.println("账号密码错误");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
4.3 获取总数
执行的sql语句为
select count(*) from hero
通过ResultSet获取出来
{
String sql = "select count(*) from hero";
//执行查询语句,并把结果集返回给ResultSet
ResultSet rs = s.executeQuery(sql);
int total = 0;
while(rs.next()) {
total = rs.getInt(1);
}
System.out.println("表hero中共有:"+total+"条数据");
}
预编译Statement
1. 使用PreparedStatement
和Statement一样,PreparedStatement也是用来执行sql语句的。
和创建Statement不同的是,需要根据sql语句创建PreparedStatement
除此之外,还能够通过设置参数,指定相应的值,而不是Statement那样使用字符串拼接。
注:这是Java里唯二的基1的地方,另一个是查询语句的ResultSet也是基1的。
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestJDBC {
public static void main(String[] args) {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
String sql = "insert into hero values(null,?,?,?)";
try (
Connection c = DriverManager.getConnection(
"jdbc:mysql://127.0.0.1:3306/jdbcTest?serverTimezone=Asia/Shanghai&characterEncoding=utf8",
"root", "admin");
//根据sql语句创建PreparedStatement
PreparedStatement ps = c.prepareStatement(sql);
)
{
//设置参数
ps.setString(1,"老王");
ps.setFloat(2, 813.0f);
ps.setInt(3, 95);
//执行
ps.execute();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
2. PreparedStatement的优点
2.1 参数设置
Statement需要进行字符串拼接,可读性和维护性比较差
String sql = "insert into hero vlaues(null,"+"'提莫'"+","+313.0f+","+50+")";
PreparedStatement使用参数设置,可读性好,不易犯错
String sql = "insert into hero values(null,?,?,?)";
2.2 性能表现
PreparedStatement有预编译机制,性能比Statement更快。
2.3 防止SQL注入式攻击
假设name是用户提交来的数据
String name = "'盖伦'OR 1=1";
使用Statement就需要进行字符串拼接,拼接出来的语句是:
select * from hero where name = '盖伦' OR 1=1
因为有 OR 1=1,这是恒成立的,那么久会把所有英雄都查出来,而不只是盖伦。如果hero表里的数据是海量的,比如几百万条,把这个表里的数据全部查出来会让数据库负载变高,CPU100%,内存消耗光,响应变得极为缓慢,而PreparedStatement使用的是参数设置,就不会有这个问题。
execute executeUpdate
相同点:都可以执行增加,修改,删除
不同点:
- execute可以执行查询语句,然后通过getResultSet把结果集取出来;executeUpdate不能执行查询语句
- execute返回boolean类型,true表示执行的是查询语句,false表示执行的是insert,delete,update等等;executeUpdate返回的是int,表示有多少条数据受到了影响。
特殊操作
1. 获取自增长
在Statement通过execute或者executeUpdate执行完插入语句后,MySQL会为新插入的数据分配一个自增长id,(前提是这个表的id设置为了自增长,在MySQL创建表的时候,AUTO_INCREMENT就表示自增长)。
但是无论是execute还是executeUpdate都不会返回这个自增长id是多少。需要通过Statement的getGeneratedKeys获取该id。
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestJDBC {
public static void main(String[] args) {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
String sql = "insert into hero values(null,?,?,?)";
try (
Connection c = DriverManager.getConnection(
"jdbc:mysql://127.0.0.1:3306/jdbcTest?serverTimezone=Asia/Shanghai&characterEncoding=utf8",
"root", "admin");
PreparedStatement ps = c.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
)
{
ps.setString(1, "盖伦");
ps.setFloat(2, 616);
ps.setInt(3, 100);
//执行插入语句
ps.execute();
//在执行完插入语句后,MySQL会为新插入的书分配一个自增长id
//JDBC通过getGeneratedKeys获取该id
ResultSet rs = ps.getGeneratedKeys();
if(rs.next()) {
int id = rs.getInt(1);
System.out.println(id);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
2. 获取表的元数据
元数据概念:和数据库服务器相关的数据,比如数据库版本,有哪些表,表有哪些字段,字段类型是什么等等。
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestJDBC {
public static void main(String[] args) {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
String sql = "insert into hero values(null,?,?,?)";
try (
Connection c = DriverManager.getConnection(
"jdbc:mysql://127.0.0.1:3306/jdbcTest?serverTimezone=Asia/Shanghai&characterEncoding=utf8",
"root", "admin");
//PreparedStatement ps = c.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
)
{
//查看数据库层面的元数据
//即数据库服务器版本,驱动器版本,都有哪些数据库等等
DatabaseMetaData dbmd = c.getMetaData();
//获取数据库服务器产品名称
System.out.println("数据库产品名称:\t"+dbmd.getDatabaseProductName());
//获取数据库服务器产品版本号
System.out.println("数据库产品名称:\t"+dbmd.getDatabaseProductVersion());
//获取数据库服务器用作类别和表名之间的分隔符 如test.user
System.out.println("数据库产品名称:\t"+dbmd.getCatalogSeparator());
//获取驱动版本
System.out.println("数据库产品名称:\t"+dbmd.getDriverVersion());
System.out.println("可用的数据库列表:");
//获取数据库名称
ResultSet rs = dbmd.getCatalogs();
while(rs.next()) {
System.out.println("数据库产品名称:\t"+rs.getString(1));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
JDBC事务
1. 不使用事务的情况
假设业务操作是:加血,减血各做一次
结束后,英雄的血量不变
而减血的SQL不小心写成了updata(而非update),那么结果是血量增加了,而不是期望的不变。
2. 使用事务
在事务中的多个操作,要么都成功,要么都失败。
- 通过c.setAutoCommit(false);关闭自动提交
- 使用c.commit();j进行手动提交
如下代码之间的数据库操作,就处于同一个事务当中,要么都成功,要么都失败。所以,虽然第一条SQL语句是可以执行的,但是第二条SQL语句有错误,其结果就是两条SQL语句都没有被提交。除非两条SQL语句都是正确的。
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class TestJDBC {
public static void main(String[] args) {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try (
Connection c = DriverManager.getConnection(
"jdbc:mysql://127.0.0.1:3306/jdbcTest?serverTimezone=Asia/Shanghai&characterEncoding=utf8",
"root", "admin");
Statement s = c.createStatement();){
//有事务的前提下,在事务中的多个操作,要么都成功,要么都失败
c.setAutoCommit(false);
//加血的SQL
String sql1 = "update hero set hp = hp +1 where id =5";
s.execute(sql1);
//减血的SQL,不小心写错成了updata(而非update)
String sql2 = "updata hero set hp = hp -1 where id =5";
s.execute(sql2);
//手动提交
c.commit();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
3. MySQL表的类型必须是InnoDB才支持事务
在MySQL中,只有当表的类型是InnoDB的时候,才支持事务,所以需要把表的类型设置为InnoDB,否则无法观察到事务。
修改表的类型为InnoDB的SQL:
alter table hero ENGINE = innodb;
查看表的类型的SQL:
show table status from jdbcTest;