1. JDBC概念说明
JDBC是Java Database Connectivity的简称,是Java语言访问数据库的接口规范,具体详细规范内容可以参考JSR221
2. SQL语句分类
2.1 DML语句:
Data Manipulation Language的简称,数据操作语言(对数据表中的数据进行操作的行为);主要是由insert、update和delete三个关键词完成!
2.2 DCL语句:
Data Control language的简称,数据控制语言(数据库及数据表权限相关的行为);主要是由grant和revoke两个关键词完成!
2.3 DDL语句:
Data Definition Language的简称,数据定义语言(对数据库中数据表进行操作的行为);主要是由create、alter、drop和truncate四个关键词完成!
2.4 DQL语句:
Data Query Language的简称,数据库查询语言(对数据表中的数据进行查询的行为);主要是由select关键词完成!
2.5 TCL语句:
Transaction Control Language的简称,事物控制语言(数据库中事物相关操作的行为);主要是由commit、rollback和savepoint三个关键词完成!
2. JDBC使用说明
2.1 JDBC与数据库的链接:
JDBC链接数据库主要是通过DriverManager类和Connection类实现:
public class JDBCTest {
public static void main(String[] args) throws Exception {
/*通过DriverManager类的getConnection()静态方法获取数据库链接,再通过数据库链接访问数据库*/
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql_test?serverTimezone=UTC", "root", "root");
}
return;
}
}
Ps:
- 示例环境使用的是mysql数据库,用户名为root,密码为root,端口号为3306
- 5.1.6及以上版本的mysql,已经不需要手动通过调用Class.forName("com.mysql.jdbc.Driver")方法进行mysql数据库驱动的注册了,已经可以通过JDK的SPI功能自动完成加载注册驱动类!
- 不同类型的数据库都会实现自己的Driver驱动类;在类加载的时候,会将Driver驱动类注册到DriverManager类中,当使用DriverManager.getConnection()方法获取对应数据库的Connection连接类的时候,需要通过不同的URL进行区分!
- 不同数据库的URL存在不同的格式:
RDBMS | JDBC驱动程序名称 | URL格式 | 默认端口 | 举例 |
---|---|---|---|---|
oracle | oracle.jdbc.driver.OracleDriver | jdbc:oracle:thin:@dbip:port:databasename | 1521 | jdbc:oracle:thin:@10.10.10.10:1521:dataBase |
mysql | com.mysql.jdbc.Driver | jdbc:mysql://dbip:port/databasename | 3306 | jdbc:mysql://10.10.10.10:3306/dataBase |
SQLServer | com.microsoft.jdbc.sqlserver.SQLServerDriver | jdbc:microsoft:sqlserver://dbip:port; DatabaseName=databasename | 1433 | jdbc:sqlserver://10.10.10.10:1433;DatabaseName=dataBase |
DB2 | com.ibm.db2.jdbc.app.DB2Driver | jdbc:db2://dbip:port/databasename | 5000 | jdbc:db2://10.10.10.10:5000/dataBase |
PostgreSQL | org.postgresql.Driver | jdbc:postgresql://dbip:port/databasename | 5432 | jdbc:postgresql://10.10.10.10:5432/dataBase |
- DriverManager.getConnection()方法主要是通过URL及数据库连接参数对数据库进行连接,参数可以拼接到URL上(URL与参数直接通过?连接,key-value之间通过=连接,参数之间通过&连接),也可以作为配置项传入方法中;<mysql连接参数说明>
- 切记每个Connection连接类都是连接到数据库中的一个具体的数据库(database),但并不是当前Connection连接类只能操作这个database;只要用户权限满足依然可以切换到其他database上面进行操作!
2.2 JDBC操作数据库:
JDBC操作数据库主要是通过Statement、PreparedStatement、CallableStatement这三个类实现,这三个类分别是由Connection类的createStatement()方法、prepareStatement()方法、prepareCall()方法获取!
示例数据表格式:
建表语句:
CREATE TABLE `person`(
`id` INT UNSIGNED AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL,
`info` VARCHAR(40) NOT NULL,
`age` INT,
PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
2.2.1 Statement类:
Statement类提供了如下四个主要的方法:
- execute()方法:可以执行所有类型的语句,如果语句类型为DQL语句的时候,返回值为true,并可以通过Statement类的getResultSet()方法获取到结果集;如果语句类型为DML语句的时候,返回值为false,并可以通过Statement类的getUpdateCount()方法获取Sql指令影响的行数;如果语句类型为其他类型,返回值为false,并不任何结果可获取!
- executeQuery()方法:只能执行DQL语句,并返回结果集!
- executeUpdate()方法:可以执行DML语句和DDL语句,当执行DML语句的时候,返回Sql指令影响函数;当执行DDL语句的时候,返回0!
- executeLargeUpdate()方法:与executeUpdate()方法类似,只是返回值从int改为了long,当Sql指令影响行数大于Interge.MAX_VALUE的时候,可以考虑使用executeLargeUpdate()方法,但是mysql不支持!
代码示例:
public class JDBCTest {
public static void main(String[] args) throws Exception {
/*获取数据库连接*/
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql_test?serverTimezone=UTC&user=root&password=root");
Statement stmt = conn.createStatement();
/*executeQuery方法的使用*/
/*使用executeQuery方法进行数据库查询,并返回结果集*/
ResultSet rs = stmt.executeQuery("SELECT * FROM person");
/*遍历结果集展示查询结果数据*/
while (rs.next()) {
System.out.println(rs.getString("id"));
System.out.println(rs.getString("name"));
System.out.println(rs.getString("info"));
System.out.println(rs.getString("age"));
}
/*execute方法的使用*/
boolean b = stmt.execute("SELECT * FROM person");
ResultSet rs1 = null;
if (b) {
rs1 = stmt.getResultSet();
}
while ((null != rs1) && (rs1.next())) {
System.out.println(rs1.getString("id"));
System.out.println(rs1.getString("name"));
System.out.println(rs1.getString("info"));
System.out.println(rs1.getString("age"));
}
/*executeUpdate方法的使用*/
int n = stmt.executeUpdate("INSERT INTO person (name,info,age) VALUES ('qq','qq_info',20)");
System.out.println(n);
/*关闭所有资源*/
rs.close();
if (null != rs1) {
rs1.close();
}
stmt.close();
conn.close();
return;
}
}
Ps:ResultSet结果集、Connection连接类、Statement类用完后,都需要手动关闭;Statement类还提供了两个方法,closeOnCompletion()方法和isCloseOnCompletion()方法;Statement类调用closeOnCompletion()方法后,当所有依赖Statement类的ResultSet结果集都关闭后,Statement类会自动关闭!同时可以使用isCloseOnCompletion()方法来判断Statement类是否打开了这个功能!
2.2.2 PreparedStatement类:
PreparedStatement类与Statement类相似,也提供上述四个操作方法(只是没有参数),使用原则也一致;只是在通过Connection类获取PreparedStatement类的时候,使用带占位符(?符号)的Sql和设置预编译参数,多进行一步预编译过程!
代码示例:
public class JDBCTest {
public static void main(String[] args) throws Exception {
/*获取数据库连接*/
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql_test?serverTimezone=UTC&user=root&password=root");
/*DML示例*/
/*通过Sql指令+占位符获取PreparedStatement对象*/
PreparedStatement preparedStatement = conn.prepareStatement("INSERT INTO person (name,info,age) VALUES (?,?,?)");
/*给预编译设置参数*/
preparedStatement.setString(1,"n2");
preparedStatement.setString(2,"info2");
preparedStatement.setInt(3,100);
/*执行executeUpdate方法*/
preparedStatement.executeUpdate();
/*关闭资源*/
preparedStatement.close();
/*DQL示例*/
preparedStatement = conn.prepareStatement("SELECT * FROM person WHERE name = ? ");
/*给预编译设置参数*/
preparedStatement.setString(1,"n2");
/*执行executeQuery方法*/
ResultSet rs = preparedStatement.executeQuery();
while (rs.next()) {
System.out.println(rs.getString("id"));
System.out.println(rs.getString("name"));
System.out.println(rs.getString("info"));
System.out.println(rs.getString("age"));
}
/*关闭所有资源*/
rs.close();
preparedStatement.close();
conn.close();
return;
}
}
Ps:
- PreparedStatement类在设置预编译参数的时候,下标序号是从1开始,不是从0开始
- 更加推荐使用PreparedStatement方式,性能更好、无需拼接Sql、而且通过占位符预编译可以有效的防止Sql注入,更加安全!
- 占位符只能替换Sql语句的普通值,不能替换库名、表名、字段名和关键词!!!
2.2.3 CallableStatement类:
CallableStatement类是JDBC提供用来调用数据库存储过程的一个操作类,使用说明如下:
存储过程示例:
//存储过程示例
delimiter $$
create procedure sum(a int, b int, out sum)
begin
set sum = a + b;
end$$
代码示例:
public class JDBCTest {
public static void main(String[] args) throws Exception {
/*获取数据库连接*/
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql_test?serverTimezone=UTC&user=root&password=root");
/*生成CallableStatement实例*/
CallableStatement call = conn.prepareCall("call fun(?,?,?)");
/*设置存储过程输入参数*/
call.setInt(1, 100);
call.setInt(2, 100);
/*设置存储过程输出类型*/
call.registerOutParameter(3,Types.INTEGER);
/*执行存储过程*/
call.execute();
/*获取存储过程输出值*/
System.out.println(call.getInt(3));
call.close();
conn.close();
return;
}
}
2.3 JDBC获取结果集
2.3.1 ResultSet结果集:
在实例化PreparedStatement类和Statement类的时候,可以设置返回结果集的类型,是否可以更新、是否可以自由移动指针等等!
- resultSetType参数:
- ResultSet.TYPE_FORWARD_ONLY:设置指针只能向前移动
- ResultSet.TYPE_SCROLL_INSENSITIVE:设置指针可以自由移动,但是底层数据的改变不会影响ResultSet结果集的内容
- ResultSet.TYPE_SCROLL_SENSITIVE:设置指针可以自由移动,但是底层数据的改变会影响ResultSet结果集的内容
- resultSetConcurrency参数:
- ResultSet.CONCUR_READ_ONLY:设置ResultSet结果集为只读模式
- ResultSet.CONCUR_UPDATABLE:设置ResultSet结果集为可更新模式
Statement createStatement(int resultSetType, int resultSetConcurrency,
int resultSetHoldability)
PreparedStatement prepareStatement(String sql, int resultSetType,
int resultSetConcurrency)
ResultSet结果集常用方法:
/*光标移动相关方法*/
//向前滚动
rs.previous();
//向后滚动
rs.next();
//得到当前行号
rs.getRow();
//光标定位到n行
rs.absolute(n);
//相对移动n行
rs.relative(int n);
//将光标定位到结果集中首行
rs.first();
//将光标定位到结果集中尾行
rs.last();
//将光标定位到结果集中首行之前
rs.beforeFirst();
//将光标定位到结果集中尾行之后
rs.afterLast();
/*确认光标位置相关方法*/
//光标是否在首行
rs.isFirst();
//光标是否在尾行
rs.isLast();
//光标是否在首行之前
rs.isBeforeFirst();
//光标是否在尾行之后
rs.isAfterLast();
/*更新结果集相关方法*/
//将数据插入结果集和数据表中
rs.insertRow();
//将数据从结果集和数据表中删除
rs.deleteRow();
//更新当前行某一列的数据
rs.updateXXX(int column, XXX data);
//更新当前行某一列的数据
rs.updateXXX(String columnName, XXX Data);
//将结果集中的更新的数据推送到数据表中
rs.updateRow();
当指针移动到指定行后,可以通过getXXX(int column)方法或者getXXX(String columnName)方法,获取当前行,特定列的数据!也可以使用<T> T getObject(int colum, Class<T> type)方法或者<T> T getObject(String columnName, Class<T> type)获取任意类型的值!
Ps:
- 使用可更新的结果集,要保证结果集中的数据全部来自一张数据表,并且结果集中有包含主键列,否则会更新失败!
- ResultSet结果集是一个与数据库长连接的结果集,使用过程中要保证Connection对象处于连接状态,否则访问ResultSet结果集的时候会报错!
- 结果集的列数据获取,下标都是从1开始的!
2.3.1 RowSet结果集:
ResultSet结果集是一个与数据库长连接的结果集,RowSet是ResultSet的子接口,其实现类有JdbcRowSet、CachedRowSet、FilteredRowSet、JoinRowSet、WebRowSet!其中只有JdbcRowSet是长连接结果集,其余的都是离线结果集!离线结果集便于存储、传输、序列化,而且也提高了处理性能!
2.3.2 CachedRowSet离线结果集:
ResultSet结果集属于数据库长连接结果集,如果断开与数据库的连接,在访问Result结果集,程序会报错!所有更多的情况,是将ResultSet结果集在内存合理的范围内转换为离线结果集,再提供给外部使用!
代码示例:
public class JDBCTest {
public static void main(String[] args) throws Exception {
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql_test?serverTimezone=UTC&user=root&password=root");
preparedStatement = conn.prepareStatement("SELECT * FROM person WHERE name = ? ");
preparedStatement.setString(1,"n1");
ResultSet rs = preparedStatement.executeQuery();
/*通过ResultSet结果集生成离线CachedRowSet结果集*/
RowSetFactory factory = RowSetProvider.newFactory();
CachedRowSet cachedRowSet = factory.createCachedRowSet();
cachedRowSet.populate(rs);
/*关闭所有资源*/
rs.close();
preparedStatement.close();
conn.close();
/*通过CachedRowSet结果集获取数据*/
while (cachedRowSet.next()) {
System.out.println(cachedRowSet.getString("id"));
System.out.println(cachedRowSet.getString("name"));
System.out.println(cachedRowSet.getString("info"));
System.out.println(cachedRowSet.getString("age"));
}
return;
}
}
CachedRowSet结果集的分页:
如果ResultSet结果集比较大的时候,就不能全量加载到CachedRowSet结果集,否则可能会造成内存不足的情况;所有需要对CachedRowSet结果集进行分页操作;每次只从ResultSet结果集中获取一部分数据!当然这样的操作依然需要保证底层与数据库处于连接状态!
分页相关方法:
2.4 JDBC操作事物:
JDBC对于数据库事物相关操作都是基于Connection连接类的如下方法实现的
Ps:
- 通过setAutoCommit()方法关闭自动提交后,即开启了事务;当前连接中的所有DML语句都不会立即生效,除非执行commit()方法显示提交事务,或者通过当执行DDL语句、DCL语句、session退出的方式隐形提交事务;提交事务后,之前的DML语句才会生效!
- 除了通过关闭自动提交开启事务,也可以通过begin命令或者start transaction命令,开启事务!
- 不管是隐性事务提交、显性事务提交或者回滚操作,都会导致事务终止,不再继续执行!
- 还可以根据保存点进行回滚操作,这种回滚操作,会保留保存点之前的事务行为;由于这种回滚行为保留了保存点之前的事务,所以已经记得及时commit事物,保证数据更新!
- 使用try-with-resource打开Connection连接类的时候,如果抛出一个未处理的SqlException异常,Connection类会自动回滚事务,并关闭事务!否则就需要人工去手动捕获SqlException异常,在catch代码块中进行回滚操作!
2.5 Java8批量更新数据:
Java8后的版本新增了批量更新数据库操作,可以执行DDL语句和DML语句;以前版本只能通过execute()方法一条条的去执行Sql,并发送给数据库;现在可以使用addBatch()方法和executeBatch()方法批量执行Sql,再批量发送给数据库!
public class JDBCTest {
public static void main(String[] args) throws Exception {
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql_test?serverTimezone=UTC&user=root&password=root");
/*获取当前自动提交状态*/
boolean autoCommitFlag = conn.getAutoCommit();
Statement statement = conn.createStatement();
/*将自动提交状态,设置为flase,开启事务*/
conn.setAutoCommit(false);
/*添加批量更新sql*/
statement.addBatch(sql1);
statement.addBatch(sql2);
statement.addBatch(sql3);
/*同时执行所有批量更新sql*/
statement.executeBatch();
/*提交事务*/
conn.commit();
/*还原自动提交状态*/
conn.setAutoCommit(autoCommitFlag);
statement.close();
conn.close();
return;
}
}
Ps:
- 批量更新数据可以执行DDL语句和DML语句,针对DML语句建议在事务中处理,这样可以保证批量处理数据的一致性!
- 在使用批量更新数据的时候,需要先确认当前数据库是否支持!可以通过DatabaseMetaData的supportsBatchUpdates()方法来查看!
- 当批量更新数据量超过Interge.MAX_VALUE的时候,需要使用executeLargeUpdate()方法代替executeBatch()方法
2.6 JDBC获取数据库元数据
2.6.1 结果集的元数据:
通过ResultSet结果集的getMetaData()方法可以获取对应结果集的元数据,可以通过ResultSetMetaData对象获取到结果集的列相关信息!
public class JDBCTest {
public static void main(String[] args) throws Exception {
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql_test?serverTimezone=UTC&user=root&password=root");
Statement statement = conn.createStatement();
ResultSet resultSet = statement.executeQuery("select * from person");
ResultSetMetaData data = resultSet.getMetaData();
/*获取列个数*/
System.out.println(data.getColumnCount());
/*获取列名*/
System.out.println(data.getColumnName(1));
/*获取列类型*/
System.out.println(data.getColumnType(1));
resultSet.close();
statement.close();
conn.close();
return;
}
}
2.6.2 数据库的元数据:
通过Connection连接类的getMetaData()方法,可以获取到数据库的元数据DatabaseMetaData对象,通过DatabaseMetaData对象可以获取到数据库的详细信息!
public class JDBCTest {
public static void main(String[] args) throws Exception {
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql_test?serverTimezone=UTC&user=root&password=root");
DatabaseMetaData data = conn.getMetaData();
/*获取所有符合规则的数据表信息*/
ResultSet rs = data.getTables(null,null,"%",new String[]{"TABLE"});
while (rs.next()) {
for (int i = 1; i < rs.getMetaData().getColumnCount(); i++) {
System.out.println(rs.getMetaData().getColumnName(i) + " : " + rs.getString(i));
}
}
rs.close();
conn.close();
return;
}
}
Ps:DatabaseMetaData类其中的方法,基本上都是存在过滤条件的;可以使用null对象来忽略过滤条件,也可以使用"%"代替任意字符串、使用"_"代替任意一个字符!
2.6.3 使用系统表获取数据库信息:
常规数据都会有一个系统数据表,用来存放数据库信息,可以通过系统数据表获取对应的系统信息!
例如Mysql系统表如下:
2.7 数据库连接池
频繁的与数据库的进行连接、关闭操作,会造成IO和性能的浪费;所以引入数据库连接池的概念;常用的数据库连接池有DBCP和C3P0!
2.7.1 DBCP连接池(说明文档):
pom配置:
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
<version>2.7.0</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-pool2</artifactId>
<version>2.8.0</version>
</dependency>
代码示例:
public class JDBCTest {
public static void main(String[] args) throws Exception {
/*创建数据源*/
BasicDataSource ds = new BasicDataSource();
/*设置驱动类*/
ds.setDriverClassName("com.mysql.jdbc.Driver");
/*设置URL*/
ds.setUrl("jdbc:mysql://localhost:3306/mysql_test?serverTimezone=UTC&user=root&password=root");
/*设置数据库连接池的初始池子大小*/
ds.setInitialSize(5);
/*设置连接池最大连接个数*/
ds.setMaxTotal(20);
/*设置连接池最小空闲个数*/
ds.setMinIdle(5);
/*通过连接池获取数据库连接*/
Connection conn = ds.getConnection();
Statement stmt = conn.createStatement();
boolean b = stmt.execute("SELECT * FROM person");
ResultSet rs = null;
if (b) {
rs = stmt.getResultSet();
}
while ((null != rs) && (rs.next())) {
System.out.println(rs.getString("id"));
System.out.println(rs.getString("name"));
System.out.println(rs.getString("info"));
System.out.println(rs.getString("age"));
}
rs.close();
stmt.close();
conn.close();
return;
}
}
2.7.2 C3P0连接池(说明文档):
pom配置:
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.2</version>
</dependency>
代码示例:
public class JDBCTest {
public static void main(String[] args) throws Exception {
/*创建数据源*/
ComboPooledDataSource ds = new ComboPooledDataSource();
/*设置驱动类*/
ds.setDriverClass("com.mysql.jdbc.Driver");
/*设置URL*/
ds.setJdbcUrl("jdbc:mysql://localhost:3306/mysql_test?serverTimezone=UTC&user=root&password=root");
/*设置数据库连接池的初始池子大小*/
ds.setInitialPoolSize(5);
/*设置连接池最大连接个数*/
ds.setMaxPoolSize(20);
/*设置连接池最小空闲个数*/
ds.setMinPoolSize(5);
/*通过连接池获取数据库连接*/
Connection conn = ds.getConnection();
Statement stmt = conn.createStatement();
boolean b = stmt.execute("SELECT * FROM person");
ResultSet rs = null;
if (b) {
rs = stmt.getResultSet();
}
while ((null != rs) && (rs.next())) {
System.out.println(rs.getString("id"));
System.out.println(rs.getString("name"));
System.out.println(rs.getString("info"));
System.out.println(rs.getString("age"));
}
rs.close();
stmt.close();
conn.close();
return;
}
}