Java-JDBC基础知识

Java数据库连接,(Java Database Connectivity,简称JDBC)是Java语言中用来规范客户端程序如何来访问数据库应用程序接口,提供了诸如查询和更新数据库中数据的方法。JDBC也是Sun Microsystems的商标。我们通常说的JDBC面向关系型数据库的。

连接数据库

  1. 导入mysql-jdbc的jar包

访问MySQL数据库需要用到第三方的类,为了代码能够使用第三方的类,需要为项目导入mysql的专用Jar包。

连接数据库用到的包为:mysql-connector-java-5.0.8-bin.jar

通常都会把项目用到的jar包统一放在项目的lib目录下。

  1. 初始化驱动

通过Class.forName("com.mysql.jdbc.Driver"); 初始化驱动类com.mysql.jdbc.Driver

就在 mysql-connector-java-5.0.8-bin.jar中,如果忘记了第一个步骤的导包,就会抛出ClassNotFoundException

Class.forName是把这个类加载到JVM中,加载的时候,就会执行其中的静态初始化块,完成驱动的初始化的相关工作。

  1. 建立与数据库的连接

建立与数据库的Connection连接,这里需要提供:

  • 数据库所处于的ip:127.0.0.1 (本机)
  • 数据库的端口号: 3306 (mysql专用端口号)
  • 数据库名称 how2java
  • 编码方式 UTF-8
  • 账号 root
  • 密码 admin

: 这一步要成功执行,必须建立在mysql中有数据库how2java的基础上,若没有创建数据库,可执行以下SQL语句来创建数据库:

1
create database how2java
  1. 创建Statement

Statement是用于执行SQL语句的,比如增加,删除

  1. 执行SQL语句

s.execute执行sql语句执行成功后,用mysql-front进行查看,明确插入成功

执行SQL语句之前要确保数据库how2java中有表hero的存在,如果没有,需要事先创建表:

1
2
3
4
5
6
7
CREATE TABLE hero (
id int(11) AUTO_INCREMENT,
name varchar(30) ,
hp float ,
damage int(11) ,
PRIMARY KEY (id)
) DEFAULT CHARSET=utf8;
  1. 关闭连接

数据库的连接是有限资源,相关操作结束后,养成关闭数据库的好习惯

先关闭Statement,后关闭Connection

上述步骤所有代码如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
package 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) {

Connection c = null;
Statement s = null;
try {
//初始化驱动
//驱动类com.mysql.jdbc.Driver
//就在 mysql-connector-java-5.0.8-bin.jar中
//如果忘记了第一个步骤的导包,就会抛出ClassNotFoundException
Class.forName("com.mysql.jdbc.Driver");

// 建立与数据库的Connection连接
// 这里需要提供:
// 数据库所处于的ip:127.0.0.1 (本机)
// 数据库的端口号: 3306 (mysql专用端口号)
// 数据库名称 how2java
// 编码方式 UTF-8
// 账号 root
// 密码 admin
c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8", "root",
"admin");

// 注意:使用的是 java.sql.Statement
// 不要不小心使用到: com.mysql.jdbc.Statement;
s = c.createStatement();

// 准备sql语句
// 注意: 字符串要用单引号'
String sql = "insert into hero values(null," + "'提莫'" + "," + 313.0f + "," + 50 + ")";

s.execute(sql);

} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
// 数据库的连接时有限资源,相关操作结束后,养成关闭数据库的好习惯
// 先关闭Statement
if (s != null)
try {
s.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// 后关闭Connection
if (c != null)
try {
c.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

}

}
}
  1. 使用try-with-resource的方式自动关闭连接

如果觉得上一步的关闭连接的方式很麻烦,可以参考关闭流的方式,使用try-with-resource的方式自动关闭连接,因为ConnectionStatement都实现了AutoCloseable接口

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
package 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.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}

try (
Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8",
"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();
}
}
}

增加/删除/修改

CRUD是最常见的数据库操作,即增删改查

  • C 增加(Create)
  • R 读取查询(Retrieve)
  • U 更新(Update)
  • D 删除(Delete)

在JDBC中增加,删除,修改的操作都很类似,只是传递不同的SQL语句就行了。
查询因为要返回数据,所以和上面的不一样,后面会有介绍。

增加

同上面连接数据库的代码相同

删除

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
package 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.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}

try (
Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8",
"root", "admin");
Statement s = c.createStatement();
)
{
String sql = "delete from hero where id = 5";
s.execute(sql);

} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}

修改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
package 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.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}

try (
Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8",
"root", "admin");
Statement s = c.createStatement();
)
{
String sql = "update hero set name = 'name 5' where id = 3";
s.execute(sql);

} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}

查询

  1. 查询语句

executeQuery 执行SQL查询语句

注意: 在取第二列的数据的时候,用的是rs.get(2) ,而不是get(1). 这个是整个Java自带的api里唯二的地方,使用 基1 的,即2就代表第二个。另一个地方是在PreparedStatement这里。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
package jdbc;

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.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}

try (Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8",
"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);
}
// 不一定要在这里关闭ReultSet,因为Statement关闭的时候,会自动关闭ResultSet
// rs.close();

} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}

  1. SQL语句判断账号密码是否正确
  • 创建一个用户表,有字段name,password
  • 插入一条数据
1
2
3
4
5
6
7
CREATE TABLE user (
id int(11) AUTO_INCREMENT,
name varchar(30) ,
password varchar(30),
PRIMARY KEY (id)
) ;
insert into user values(null,'dashen','thisispassword');
  • SQL语句判断账号密码是否正确

判断账号密码的正确方式是根据账号和密码到表中去找数据,如果有数据,就表明密码正确了,如果没数据,就表明密码错误。

不恰当的方式 是把uers表的数据全部查到内存中,挨个进行比较。 如果users表里有100万条数据呢? 内存都不够用的。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
package jdbc;

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.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}

try (Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8",
"root", "admin");
Statement s = c.createStatement();

) {
String name = "dashen";
//正确的密码是:thisispassword
String password = "thisispassword1";

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

}
}
  1. 获取总数

执行的sql语句为

1
select count(*) from hero

然后通过ResultSet获取出来:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
package jdbc;

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.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}

try (Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8",
"root", "admin"); Statement s = c.createStatement();) {

String sql = "select count(*) from hero";

ResultSet rs = s.executeQuery(sql);
int total = 0;
while (rs.next()) {
total = rs.getInt(1);
}

System.out.println("表Hero中总共有:" + total+" 条数据");

} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

}
}

PreparedStatement

使用方法

Statement一样,PreparedStatement也是用来执行sql语句的

与创建Statement不同的是,需要根据sql语句创建PreparedStatement

除此之外,还能够通过设置参数,指定相应的值,而不是Statement那样使用字符串拼接

: 这是JAVA里唯二基1 的地方,另一个是查询语句中的ResultSet也是基1的。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
package jdbc;

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.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/how2java?characterEncoding=UTF-8","root", "admin");
// 根据sql语句创建PreparedStatement
PreparedStatement ps = c.prepareStatement(sql);
) {

// 设置参数
ps.setString(1, "提莫");
ps.setFloat(2, 313.0f);
ps.setInt(3, 50);
// 执行
ps.execute();

} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

}
}

优点

优点1-参数设置

Statement 需要进行字符串拼接,可读性和维护性比较差

1
String sql = "insert into hero values(null,"+"'提莫'"+","+313.0f+","+50+")";

PreparedStatement 使用参数设置,可读性好,不易犯错

1
String sql = "insert into hero values(null,?,?,?)";
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

public class TestJDBC {
public static void main(String[] args) {

try {
Class.forName("com.mysql.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/how2java?characterEncoding=UTF-8","root", "admin");
Statement s = c.createStatement();
PreparedStatement ps = c.prepareStatement(sql);
) {
// Statement需要进行字符串拼接,可读性和维修性比较差
String sql0 = "insert into hero values(null," + "'提莫'" + "," + 313.0f + "," + 50 + ")";
s.execute(sql0);

// PreparedStatement 使用参数设置,可读性好,不易犯错
// "insert into hero values(null,?,?,?)";
ps.setString(1, "提莫");
ps.setFloat(2, 313.0f);
ps.setInt(3, 50);
ps.execute();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

}
}

优点2-性能表现

PreparedStatement有预编译机制,性能比Statement更快

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

public class TestJDBC {
public static void main(String[] args) {

try {
Class.forName("com.mysql.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/how2java?characterEncoding=UTF-8","root", "admin");
Statement s = c.createStatement();
PreparedStatement ps = c.prepareStatement(sql);
) {
// Statement执行10次,需要10次把SQL语句传输到数据库端
// 数据库要对每一次来的SQL语句进行编译处理
for (int i = 0; i < 10; i++) {
String sql0 = "insert into hero values(null," + "'提莫'" + ","
+ 313.0f + "," + 50 + ")";
s.execute(sql0);
}
s.close();

// PreparedStatement 执行10次,只需要1次把SQL语句传输到数据库端
// 数据库对带?的SQL进行预编译

// 每次执行,只需要传输参数到数据库端
// 1. 网络传输量比Statement更小
// 2. 数据库不需要再进行编译,响应更快
for (int i = 0; i < 10; i++) {
ps.setString(1, "提莫");
ps.setFloat(2, 313.0f);
ps.setInt(3, 50);
ps.execute();
}

} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

}
}

优点3-防止SQL注入式攻击

假设name是用户提交来的数据

1
String name = "'盖伦' OR 1=1";

使用Statement就需要进行字符串拼接,拼接出来的语句是:

1
select * from hero where name = '盖伦' OR 1=1

因为有OR 1=1,这是恒成立的,那么就会把所有的英雄都查出来,而不只是盖伦

如果Hero表里的数据是海量的,比如几百万条,把这个表里的数据全部查出来,会让数据库负载变高,CPU100%,内存消耗光,响应变得极其缓慢

而PreparedStatement使用的是参数设置,就不会有这个问题

execute与executeUpdate

相同点

execute与executeUpdate的相同点:都可以执行增加,删除,修改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
package 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.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}

try (Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8","root", "admin");
Statement s = c.createStatement();) {

String sqlInsert = "insert into Hero values (null,'盖伦',616,100)";
String sqlDelete = "delete from Hero where id = 100";
String sqlUpdate = "update Hero set hp = 300 where id = 100";

// 相同点:都可以执行增加,删除,修改

s.execute(sqlInsert);
s.execute(sqlDelete);
s.execute(sqlUpdate);
s.executeUpdate(sqlInsert);
s.executeUpdate(sqlDelete);
s.executeUpdate(sqlUpdate);

} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

}
}

不同点

  1. 不同点一
  • execute可以执行查询语句,然后通过getResultSet,把结果集取出来
  • executeUpdate不能执行查询语句
  1. 不同点二:
  • execute返回boolean类型,true表示执行的是查询语句,false表示执行的是insert,delete,update等等
  • executeUpdate返回的是int,表示有多少条数据受到了影响
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
package jdbc;

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.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}

try (Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8","root", "admin");
Statement s = c.createStatement();) {

// 不同1:execute可以执行查询语句
// 然后通过getResultSet,把结果集取出来
String sqlSelect = "select * from hero";

s.execute(sqlSelect);
ResultSet rs = s.getResultSet();
while (rs.next()) {
System.out.println(rs.getInt("id"));
}

// executeUpdate不能执行查询语句
// s.executeUpdate(sqlSelect);

// 不同2:
// execute返回boolean类型,true表示执行的是查询语句,false表示执行的是insert,delete,update等等
boolean isSelect = s.execute(sqlSelect);
System.out.println(isSelect);

// executeUpdate返回的是int,表示有多少条数据受到了影响
String sqlUpdate = "update Hero set hp = 300 where id < 100";
int number = s.executeUpdate(sqlUpdate);
System.out.println(number);

} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

}
}

特殊操作

获取自增长id

Statement通过execute或者executeUpdate执行完插入语句后,MySQL会为新插入的数据分配一个自增长id,(前提是这个表的id设置为了自增长,在Mysql创建表的时候,AUTO_INCREMENT就表示自增长)

1
2
3
4
CREATE TABLE hero (
id int(11) AUTO_INCREMENT,
...
}

但是无论是execute还是executeUpdate都不会返回这个自增长id是多少。需要通过StatementgetGeneratedKeys获取该id

: 第20行的代码,后面加了个Statement.RETURN_GENERATED_KEYS参数,以确保会返回自增长id。 通常情况下不需要加这个,有的时候需要加,所以先加上,保险一些

1
PreparedStatement ps = c.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
package jdbc;

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.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/how2java?characterEncoding=UTF-8","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();
}

}
}

获取表的元数据

元数据概念:和数据库服务器相关的数据,比如数据库版本,有哪些表,表有哪些字段,字段类型是什么等等。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
package jdbc;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
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) throws Exception {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}

try (Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8","root", "admin");) {

// 查看数据库层面的元数据
// 即数据库服务器版本,驱动版本,都有哪些数据库等等

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

}
}

事务

在事务中的多个操作,要么都成功,要么都失败

通过 c.setAutoCommit(false);关闭自动提交

使用 c.commit();进行手动提交

在22行-35行之间的数据库操作,就处于同一个事务当中,要么都成功,要么都失败
所以,虽然第一条SQL语句是可以执行的,但是第二条SQL语句有错误,其结果就是两条SQL语句都没有被提交。 除非两条SQL语句都是正确的。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
package 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.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}

try (Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8","root", "admin");
Statement s = c.createStatement();) {

// 有事务的前提下
// 在事务中的多个操作,要么都成功,要么都失败

c.setAutoCommit(false);

// 加血的SQL
String sql1 = "update hero set hp = hp +1 where id = 22";
s.execute(sql1);

// 减血的SQL
// 不小心写错写成了 updata(而非update)

String sql2 = "updata hero set hp = hp -1 where id = 22";
s.execute(sql2);

// 手动提交
c.commit();

} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

}
}

注意事项:

MYSQL 表的类型必须是INNODB才支持事务

在Mysql中,只有当表的类型是INNODB的时候,才支持事务,所以需要把表的类型设置为INNODB,否则无法观察到事务.

修改表的类型为INNODB的SQL:

1
alter table hero ENGINE  = innodb;

查看表的类型的SQL

1
show table status from how2java;

不过有个前提,就是当前的MYSQL服务器本身要支持INNODB。

ORM

ORM(Object Relationship Database Mapping

对象和关系数据库的映射

简单说,一个对象,对应数据库里的一条记录

根据id返回一个Hero对象

提供方法get(int id),返回一个Hero对象

  • Hero.java
1
2
3
4
5
6
7
8
9
10
package charactor;

public class Hero {
//增加id属性
public int id;
public String name;
public float hp;
public int damage;

}
  • TestJDBC.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import charactor.Hero;

public class TestJDBC {

public static Hero get(int id) {
Hero hero = null;
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}

try (Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8","root", "admin");
Statement s = c.createStatement();) {

String sql = "select * from hero where id = " + id;

ResultSet rs = s.executeQuery(sql);

// 因为id是唯一的,ResultSet最多只能有一条记录
// 所以使用if代替while
if (rs.next()) {
hero = new Hero();
String name = rs.getString(2);
float hp = rs.getFloat("hp");
int damage = rs.getInt(4);
hero.name = name;
hero.hp = hp;
hero.damage = damage;
hero.id = id;
}

} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return hero;

}

public static void main(String[] args) {

Hero h = get(22);
System.out.println(h.name);

}
}

DAO

DAO(Data Access Object

数据访问对象

实际上就是把数据库相关的操作都封装在一个类里面,其他地方看不到JDBC的代码

  1. DAO接口
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
package jdbc;

import java.util.List;

import charactor.Hero;

public interface DAO{
//增加
public void add(Hero hero);
//修改
public void update(Hero hero);
//删除
public void delete(int id);
//获取
public Hero get(int id);
//查询
public List<Hero> list();
//分页查询
public List<Hero> list(int start, int count);
}
  1. 设计类HeroDAO,实现接口DAO
  • 把驱动的初始化放在了构造方法HeroDAO里;

因为驱动初始化只需要执行一次,所以放在这里更合适,其他方法里也不需要写了,代码更简洁

  • 提供了一个getConnection方法返回连接

所有的数据库操作都需要事先拿到一个数据库连接Connection,以前的做法每个方法里都会写一个,如果要改动密码,那么每个地方都需要修改。 通过这种方式,只需要修改这一个地方就可以了。 代码变得更容易维护,而且也更加简洁。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
package jdbc;

import java.sql.Connection;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import charactor.Hero;

public class HeroDAO implements DAO{

public HeroDAO() {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}

public Connection getConnection() throws SQLException {
return DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8", "root",
"admin");
}

public int getTotal() {
int total = 0;
try (Connection c = getConnection(); Statement s = c.createStatement();) {

String sql = "select count(*) from hero";

ResultSet rs = s.executeQuery(sql);
while (rs.next()) {
total = rs.getInt(1);
}

System.out.println("total:" + total);

} catch (SQLException e) {

e.printStackTrace();
}
return total;
}

public void add(Hero hero) {

String sql = "insert into hero values(null,?,?,?)";
try (Connection c = getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {

ps.setString(1, hero.name);
ps.setFloat(2, hero.hp);
ps.setInt(3, hero.damage);

ps.execute();

ResultSet rs = ps.getGeneratedKeys();
if (rs.next()) {
int id = rs.getInt(1);
hero.id = id;
}
} catch (SQLException e) {

e.printStackTrace();
}
}

public void update(Hero hero) {

String sql = "update hero set name= ?, hp = ? , damage = ? where id = ?";
try (Connection c = getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {

ps.setString(1, hero.name);
ps.setFloat(2, hero.hp);
ps.setInt(3, hero.damage);
ps.setInt(4, hero.id);

ps.execute();

} catch (SQLException e) {

e.printStackTrace();
}

}

public void delete(int id) {

try (Connection c = getConnection(); Statement s = c.createStatement();) {

String sql = "delete from hero where id = " + id;

s.execute(sql);

} catch (SQLException e) {

e.printStackTrace();
}
}

public Hero get(int id) {
Hero hero = null;

try (Connection c = getConnection(); Statement s = c.createStatement();) {

String sql = "select * from hero where id = " + id;

ResultSet rs = s.executeQuery(sql);

if (rs.next()) {
hero = new Hero();
String name = rs.getString(2);
float hp = rs.getFloat("hp");
int damage = rs.getInt(4);
hero.name = name;
hero.hp = hp;
hero.damage = damage;
hero.id = id;
}

} catch (SQLException e) {

e.printStackTrace();
}
return hero;
}

public List<Hero> list() {
return list(0, Short.MAX_VALUE);
}

public List<Hero> list(int start, int count) {
List<Hero> heros = new ArrayList<Hero>();

String sql = "select * from hero order by id desc limit ?,? ";

try (Connection c = getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {

ps.setInt(1, start);
ps.setInt(2, count);

ResultSet rs = ps.executeQuery();

while (rs.next()) {
Hero hero = new Hero();
int id = rs.getInt(1);
String name = rs.getString(2);
float hp = rs.getFloat("hp");
int damage = rs.getInt(4);
hero.id = id;
hero.name = name;
hero.hp = hp;
hero.damage = damage;
heros.add(hero);
}
} catch (SQLException e) {

e.printStackTrace();
}
return heros;
}

}

数据库连接池

与线程池类似的,数据库也有一个数据库连接池。 不过他们的实现思路是不一样的。

本小节讲解了自定义数据库连接池类:ConnectionPool,虽然不是很完善和健壮,但是足以帮助大家理解ConnectionPool的基本原理。

数据库连接池原理-传统方式

当有多个线程,每个线程都需要连接数据库执行SQL语句的话,那么每个线程都会创建一个连接,并且在使用完毕后,关闭连接。

创建连接和关闭连接的过程也是比较消耗时间的,当多线程并发的时候,系统就会变得很卡顿。

同时,一个数据库同时支持的连接总数也是有限的,如果多线程并发量很大,那么数据库连接的总数就会被消耗光,后续线程发起的数据库连接就会失败。

数据库连接池原理-使用池

与传统方式不同,连接池在使用之前,就会创建好一定数量的连接。

如果有任何线程需要使用连接,那么就从连接池里面借用,而不是自己重新创建。

使用完毕后,又把这个连接归还给连接池供下一次或者其他线程使用。

倘若发生多线程并发情况,连接池里的连接被借用光了,那么其他线程就会临时等待,直到有连接被归还回来,再继续使用。

整个过程,这些连接都不会被关闭,而是不断的被循环使用,从而节约了启动和关闭连接的时间。

ConnectionPool构造方法和初始化

  1. ConnectionPool() 构造方法约定了这个连接池一共有多少连接

  2. init() 初始化方法中,创建了size条连接。 注意,这里不能使用try-with-resource这种自动关闭连接的方式,因为连接恰恰需要保持不关闭状态,供后续循环使用

  3. getConnection,判断是否为空,如果是空的就wait等待,否则就借用一条连接出去

  4. returnConnection,在使用完毕后,归还这个连接到连接池,并且在归还完毕后,调用notifyAll,通知那些等待的线程,有新的连接可以借用了。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ConnectionPool {

List<Connection> cs = new ArrayList<Connection>();

int size;

public ConnectionPool(int size) {
this.size = size;
init();
}

public void init() {

//这里恰恰不能使用try-with-resource的方式,因为这些连接都需要是"活"的,不要被自动关闭了
try {
Class.forName("com.mysql.jdbc.Driver");
for (int i = 0; i < size; i++) {
Connection c = DriverManager
.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8", "root", "admin");
cs.add(c);
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

public synchronized Connection getConnection() {
while (cs.isEmpty()) {
try {
this.wait();
} catch (InterruptedException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
Connection c = cs.remove(0);
return c;
}

public synchronized void returnConnection(Connection c) {
cs.add(c);
this.notifyAll();
}

}

测试类

首先初始化一个有3条连接的数据库连接池

然后创建100个线程,每个线程都会从连接池中借用连接,并且在借用之后,归还连接。 拿到连接之后,执行一个耗时1秒的SQL语句。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
package jdbc;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

import jdbc.ConnectionPool;

public class TestConnectionPool {

public static void main(String[] args) {
ConnectionPool cp = new ConnectionPool(3);
for (int i = 0; i < 100; i++) {
new WorkingThread("working thread" + i, cp).start();
}
}
}

class WorkingThread extends Thread {
private ConnectionPool cp;

public WorkingThread(String name, ConnectionPool cp) {
super(name);
this.cp = cp;
}

public void run() {
Connection c = cp.getConnection();
System.out.println(this.getName()+ ":\t 获取了一根连接,并开始工作" );
try (Statement st = c.createStatement()){

//模拟时耗1秒的数据库SQL语句
Thread.sleep(1000);
st.execute("select * from hero");

} catch (SQLException | InterruptedException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
cp.returnConnection(c);
}
}

运行程序,就可以观察到如图所示的效果:

感谢您的支持!