任何技术都是为了解决实际问题而存在的
目标
- 数据库连接，简单查询
- 批量插入问题
- 事务问题，开始事务，提交事务，回滚，事务隔离级别
- 高级查询，子查询
技术栈
- maven 包管理打包工具
- java. jdbc
1. 数据库连接，简单查询
- 加载驱动, 不同的数据库都有不同的驱动，但都是事先的统一的接口，java.sql的接口
MySQL : com.mysql.jdbc.Driver // 需要依赖 mysql: mysql-connector-java，本篇样例使用的是MySQL
Oracle： com.oracle.ojdbc14 // 这个需要你去oracle官网下载，根据你的数据库版本，选择相应的驱动，然后集成到项目中，关于依赖本地文件，可以上网查一下
- 获取连接，输入相应的URL, USERNAME,PASSWORD,创建数据库连接
- 获取操作Statement，要想执行SQL，必须先获取Statement
- 执行语句
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class JdbcConnect {
private static final String URL = "jdbc:mysql://localhost:3306/task_enjoy";
private static final String NAME = "root";
private static final String PASSWORD = "xxxxxx"; // TODO 自己改密码
public static void main(String[] args) throws Exception {
//1.加载驱动程序
Class.forName("com.mysql.jdbc.Driver");
//2.获得数据库的连接
Connection conn = DriverManager.getConnection(URL, NAME, PASSWORD);
//3. 通过连接，获取数据库操作Statement
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select name,suffix from app_file");//选择import java.sql.ResultSet;
while (rs.next()) {//如果对象中有数据，就会循环打印出来
System.out.println(rs.getString("name") + "," + rs.getString("suffix"));
}
}
}
1.1 查询详解
- 创建数据库操作Statement时，有三种Statement,分别对应下面三种情况
- 执行静态语句，可使用Statement实例实现。
- 执行动态SQL语句，可使用PreparedStatement实例实现
- 执行数据库存储过程，可使用CallableStatement实例实现。
CallableStatement 继承 PreparedStatement， PreparedStatement 继承 Statement
下面举例说明一下，分为测试类和业务类，
业务类
package com.fjp.base.jdbc;
import java.sql.*;
public class JdbcConnect {
private static final String URL = "jdbc:mysql://localhost:3306/task_enjoy";
private static final String NAME = "root";
private static final String PASSWORD = "xxxx"; // TODO 自己改密码
private static Connection conn = null;
static {
try {
//1.加载驱动程序
Class.forName("com.mysql.jdbc.Driver");
//2.获得数据库的连接
conn = DriverManager.getConnection(URL, NAME, PASSWORD);
} catch (Exception e) {
e.printStackTrace();
}
}
public void excuteCommonSql() {
try {
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select name,suffix from app_file");
while (rs.next()) {
System.out.println(rs.getString("name") + "," + rs.getString("suffix"));
}
} catch (Exception e) {
e.printStackTrace();
}
}
public void excutePreparedCommonSql() {
try {
PreparedStatement preparedStatement = conn.prepareStatement("select name,suffix from app_file where `name` = ?");
preparedStatement.setString(1, "aassasd");
ResultSet rs = preparedStatement.executeQuery();
while (rs.next()) {
System.out.println(rs.getString("name") + "," + rs.getString("suffix"));
}
} catch (Exception e) {
e.printStackTrace();
}
}
public void excuteCallbleSql() {
try {
CallableStatement preparedStatement = conn.prepareCall("{CALL demoSp(? , ?)}");
preparedStatement.setString(1, "aassasd");
preparedStatement.setString(2, "33");
ResultSet rs = preparedStatement.executeQuery();
while (rs.next()) {
System.out.println(rs.getString("name") + "," + rs.getString("suffix"));
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
测试类
public class JdbcTest {
private JdbcConnect jdbcConnect = new JdbcConnect();
@Test
public void test() {
jdbcConnect.excuteCommonSql();
}
@Test
public void test2() {
jdbcConnect.excutePreparedCommonSql();
}
@Test
public void test3() {
jdbcConnect.excuteCallbleSql();
}
}
注：接下来都写伪代码了
-
执行SQL语句
Statement接口提供了三种执行SQL语句的方法：executeQuery 、executeUpdate 和execute- ResultSet executeQuery(String sqlString)：执行查询数据库的SQL语句，返回一个结果集（ResultSet）对象。
- int executeUpdate(String sqlString)：用于执行INSERT、UPDATE或 DELETE语句以及SQL DDL语句，如：CREATE TABLE和DROP TABLE等
- execute(sqlString):用于执行返回多个结果集、多个更新计数或二者组合的语句。
具体实现的代码：
ResultSet rs = stmt.executeQuery("SELECT * FROM ...") ;
Integer rows = stmt.executeUpdate("INSERT INTO ...") ;
Boolean flag = stmt.execute(String sql) ;
- 处理结果集
两种情况：
1、执行更新返回的是本次操作影响到的记录数。
2、执行查询返回的结果是一个ResultSet对象。
• ResultSet包含符合SQL语句中条件的所有行，并且它通过一套get方法提供了对这些行中数据的访问。
• 使用结果集（ResultSet）对象的访问方法获取数据：
while(rs.next()){
String name = rs.getString("name") ;
String pass = rs.getString(1) ; // 此方法比较高效 （列是从左到右编号的，并且从列1开始）
}
- 关闭JDBC对象
操作完成以后要把所有使用的JDBC对象全都关闭，以释放JDBC资源，关闭顺序和声
明顺序相反：
1、关闭记录集
2、关闭声明
3、关闭连接对象
if(rs != null){ // 关闭记录集
try{
rs.close() ;
}catch(SQLException e){
e.printStackTrace() ;
}
}
if(stmt != null){ // 关闭声明
try{
stmt.close() ;
}catch(SQLException e){
e.printStackTrace() ;
}
}
if(conn != null){ // 关闭连接对象
try{
conn.close() ;
}catch(SQLException e){
e.printStackTrace() ;
}
}
2. 批量插入问题
public void excuteBatch() {
try {
Statement statement = conn.createStatement();
for (int i = 0; i < 500; i++) {
statement.addBatch("insert into app_file(id, `name`, suffix) values ('" + "id02" + i + "', '111', '222')");
}
int[] re = statement.executeBatch();
statement.clearBatch();
System.out.println(re);
} catch (Exception e) {
e.printStackTrace();
}
}
批量插入，需要考虑事务的问题，和每次提交的数量问题，下面会有说明
3. 事务问题
事务涉及到的知识较多，需要考虑事务隔离级别，和事务的传播问题
3.1 事务的使用
public void transactionMananger() {
try {
Statement statement = conn.createStatement();
conn.setAutoCommit(false);
String sql = "insert into app_file(id, `name`, suffix) values ('id022222', '111', '222')";
statement.execute(sql);
conn.commit();
} catch (Exception e) {
e.printStackTrace();
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
}
3.2 数据库事务正确执行的四个基本要素 ACID
原子性(A, Atomicity)：指整个数据库事务是不可分割的工作单位。只有使据库中所有的操作执行成功，才算整个事务成功；事务中任何一个SQL语句执行失败，那么已经执行成功的SQL语句也必须撤销，数据库状态应该退回到执行事务前的状态。
一致性(C, Consistency)：指数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性。例如对银行转帐事务，不管事务成功还是失败，应该保证事务结束后ACCOUNTS表中Tom和Jack的存款总额为2000元。
隔离性(I, Isolation)：指的是在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间。由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。事务查看数据更新时，数据所处的状态要么是另一事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看到中间状态的数据。
持久性(D, Durability)：指的是只要事务成功结束，它对数据库所做的更新就必须永久保存下来。即使发生系统崩溃，重新启动数据库系统后，数据库还能恢复到事务成功结束时的状态。
事务的（ACID）特性是由关系数据库管理系统（RDBMS，数据库系统）来实现的。数据库管理系统采用日志来保证事务的原子性、一致性和持久性。日志记录了事务对数据库所做的更新，如果某个事务在执行过程中发生错误，就可以根据日志，撤销事务对数据库已做的更新，使数据库退回到执行事务前的初始状态。
3.3 事务并发处理可能的问题
1、脏读（dirty read）：一个事务读取了另一个事务尚未提交的数据
2、不可重复读（non-repeatable read）：一个事务的操作导致另一个事务前后两次读到不同的数据
3、幻读（phantom read）：一个事务的操作导致另一个事务前后两次查询的结果数据量不同
3.4 事务隔离级别
JDBC定义了五种事务隔离级别：
TRANSACTION_NONE JDBC驱动不支持事务
TRANSACTION_READ_UNCOMMITTED 允许脏读、不可重复读和幻读
TRANSACTION_READ_COMMITTED 禁止脏读，但允许不可重复读和幻读
TRANSACTION_REPEATABLE_READ 禁止脏读和不可重复读，单运行幻读
TRANSACTION_SERIALIZABLE 禁止脏读、不可重复读和幻读
4. 高级查询
其实高级查询与jdbc关系不大，但是这个还是需要注意
4.1 子查询
这里面有几个我遇到的比较麻烦的问题
1. 如何分组查询，根据某个字段查询，去数据最大的一条
EXPLAIN SELECT
*
FROM
record e1
WHERE
not EXISTS ( SELECT 1 FROM record e2 WHERE e2.type_id = e1.type_id AND e1.score < e2.score );
上面这个查询语句，分组字段是type_id, 排序字段是 score，取得的是根据 type_id分组，socre 最大的一条，
查询时，发现扫描了这个表两次，不是最优的解决方案，另外如果最大的有多条的话，也会一起查询出来
EXPLAIN SELECT
*
FROM
enjoy e1
WHERE
e1.score = ( SELECT max(e2.score) FROM enjoy e2 WHERE e2.tag = e1.tag );
第二个方法与第一个方法没有多大差别，结果也是完全一样的
2. 如何避免查询索引字段时，不是查询的索引而是全表检索
这个就与索引的机制相关了，不做过多扩展
- 应尽量避免在where 子句中对字段进行null 值判断
- 尽量避免在where 子句中使用!=或<>操作符
- 可以在LIKE操作中使用索引的情形是指另一个操作数不是以通配符（%或者_）开头的情形
- 尽量避免在where 子句中使用or 来连接条件
