Java 数据库编程

2021-09-22

Java Java进阶

1.数据库和 SQL

DB（文件集合，类似.doc,.docx 文件）
DBMS: Database Management System（类似 Office/WPS）
- 操纵和管理数据库的软件，可建立、使用和维护数据库
DB 种类
- 文本文件/二进制文件
- Xls 文件
- Access（包含在 office 里面，收费，只能运行在 Windows 上。32 和 64 位，office95/97/2000/2003/2007/2010/…）
- Mysql /Postgresql/Berkely DB (免费, 但也有收费版。多平台，32 和 64 位区分。)
- SQL Server（收费，只能运行 Windows，32 位和 64 位，中文文档。SQL Server 2000/2005/2008/2012/…,也有免费版，但有 CPU 和内存限制）
- Oracle/DB2（收费，全平台，32 和 64 位，英文文档，也有免费版，但有 CPU 和内存限制）
- SQLite (免费,手机上使用)

表

表：table, 实体
- 列：列、属性、字段
- 行：记录、元组 tuple，数据
数据值域：数据的取值范围
字段类型
- int :整数 -2147483648~2147483647，4 个字节
- double：小数，8 个字节
- datetime ：时间，7 个字节
- varchar：字符串，可变字节

SQL

结构化查询语言(Structured Query Language)，简称 SQL
- 是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。
SQL 标准
- SQL-86/SQL-89/SQL-92
- SQL:1999/ SQL:2003/ SQL:2008/ SQL:2011/ SQL:2016
- 基础的部分，所有标准都一样
- 标准仅仅是标准，每个厂商的数据库实现可能有一些不一致

常规语句

create table t1(a int, b varchar(20));
insert into t1(a,b) values(1,’abc’);
select a from t1;
select a,b from t1 where a > 1;
delete from t1 where a = 10 and b='ab';
update t1 set a=2, b = 'cd' where a=1 and b='ab';
drop table t1;

2.JDBC 基本操作

java.sql/ javax.sql （接口类）
- 根据数据库版本和 JDBC 版本合理选择
- 一般数据库发行包都会提供 jar 包，同时也要注意区分 32 位和 64 位
连接字符串（样例）
- jdbc:oracle:thin:@127.0.0.1:1521:dbname
- jdbc:mysql://localhost:3306/mydb
- jdbc:sqlserver://localhost:1433; DatabaseName=dbname

操作步骤

构建连接（搭桥）
- 注册驱动，寻找材质, class.forName(“…”)
- 确定对岸目标 , 建桥 Connection
执行操作（派个人过桥, 提着篮子，去拿数据）
- Statement (执行者)
- ResultSet(结果集)
释放连接（拆桥）
- connection.close();

Statement

Statement 执行者类
- 使用 executeQuery()执行 select 语句，返回结果放在 ResultSet
- 使用 executeUpdate()执行 insert/update/delete，返回修改的行数
- 一个 Statement 对象一次只能执行一个命令
ResultSet 结果对象
- next() 判断是否还有下一条记录
- getInt/getString/getDouble/……
  - 可以按索引位置，可以按照列名

//构建Java和数据库之间的桥梁介质
try {
  Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e1) {
  // 注册失败
  e1.printStackTrace();
  return;
}

String url = "jdbc:mysql://localhost:3306/test";
Connection conn = null;
try {
  // 构建Java和数据库之间的桥梁：URL，用户名，密码
  conn = DriverManager.getConnection(url, "root", "123456");

  // 构建数据库执行者
  Statement stmt = conn.createStatement();
  System.out.println("创建Statement成功！");

  // 执行SQL语句并返回结果到ResultSet
  ResultSet rs = stmt.executeQuery("select bookid, bookname, price from t_book order by bookid");

  // update
  int result = stmt.executeUpdate("update t_book set price = 300 where bookid = 1");
  result = stmt.executeUpdate("insert into t_book(bookid, bookname, price) values(4, '编译原理', 90)");
  result = stmt.executeUpdate("delete from t_book where id = 4");

  // 开始遍历ResultSet数据
  while (rs.next()) {
    System.out.println(rs.getInt(1) + "," + rs.getString(2) + "," + rs.getInt("price"));
  }
  rs.close();
  stmt.close();

} catch (SQLException e) {
  e.printStackTrace();
} finally {
  try {
    if (null != conn) {
      conn.close();
    }
  } catch (SQLException e) {
    e.printStackTrace();
  }
}

注意事项

ResultSet 不能多个做笛卡尔积连接
ResultSet 最好不要超过百条，否则极其影响性能
ResultSet 也不是一口气加载所有的 select 结果数据
Connection 很昂贵，需要及时 close
Connection 所用的 jar 包和数据库要匹配

3.JDBC 高级操作

事务

数据库事务，Database Transaction。 -作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行
事务，必须满足所谓的 ACID（原子性、一致性、隔离性和持久性）属性
事务是数据库运行中的逻辑工作单位，由 DBMS 中的事务管理子系统负责事务的处理

JDBC 事务

关闭自动提交，实现多语句同一事务：
- connection.setAutoCommit(false);
- connection.commit(); 提交事务
- connection.rollback(); 回滚事务
保存点机制
- connection.setSavepoint()
- connection.rollback(Savepoint)

conn.setAutoCommit(false);
insertBook(conn, "insert into t_book values(101, 'aaaa', 10)");
insertBook(conn, "insert into t_book values(102, 'bbbb', 10)");
insertBook(conn, "insert into t_book values(103, 'cccc', 10)");
Savepoint phase1 = conn.setSavepoint(); // 设置一个保存点
insertBook(conn, "insert into t_book values(104, 'cccc', 10)");
insertBook(conn, "insert into t_book values(105, 'cccc', 10)");
conn.rollback(phase1);  // 回滚到phase1保存点，即上面2行无效
conn.commit();

public static void insertBook(Connection conn, String sql) {
  try {
    // 构建数据库执行者
    Statement stmt = conn.createStatement();

    // 执行SQL语句
    int result = stmt.executeUpdate(sql);
    stmt.close();
  } catch (SQLException e) {
    e.printStackTrace();
  }
}

PreparedStatement

Java 提供 PreparedStatement，更为安全执行 SQL
和 Statement 区别是使用“?” 代替字符串拼接
使用 setXXX(int,Object)的函数来实现对于?的替换
- 注：不需要考虑字符串的两侧单引号
- 参数赋值，清晰明了，拒绝拼接错误

// 构建Java和数据库之间的桥梁：URL，用户名，密码
Connection conn = DriverManager.getConnection(url, "root", "123456");

// 使用“?” 代替字符串拼接
String sql = "insert into t_book(bookid,bookname,price) values(?,?,?)";

// 构建数据库执行者
PreparedStatement pstmt = conn.prepareStatement(sql);

// 执行SQL语句
int bookid = 10;
String bookName = "Effective Java',50);delete from t_book;insert into t_book values(101, 'faked book";
int price = 50;

// 设定value参数
pstmt.setInt(1, bookid);
pstmt.setString(2, bookName);
pstmt.setInt(3, price);

int result = pstmt.executeUpdate();

pstmt.close();

提供 addBatch 批量更新功能
Select 语句一样用 ResultSet 接收结果
使用 PreparedStatement 的好处：
- 防止注入攻击
- 防止繁琐的字符串拼接和错误
- 直接设置对象而不需要转换为字符串
- PreparedStatement 使用预编译速度相对 Statement 快很多

Connection conn = DriverManager.getConnection(url, "root", "123456");

String sql = "insert into t_book(bookid,bookname,price) values(?,?,?)";

// 构建数据库执行者
PreparedStatement pstmt = conn.prepareStatement(sql);

// 执行SQL语句
String bookName = "aaaaaaaaaaaaaaaa";
int price = 50;

// values(1, 'Effective Java', 50)
for(int i = 200 ;i < 210; i++) {
  pstmt.setInt(1, i);
  pstmt.setString(2, bookName);
  pstmt.setInt(3, price);
  pstmt.addBatch();
}
pstmt.executeBatch();

pstmt.close();

ResultSetMetaData

ResultSet 可以用来承载所有的 select 语句返回的结果集
ResultSetMetaData 来获取 ResultSet 返回的属性（如，每一行的名字类型等）
- getColumnCount()，返回结果的列数
- getColumnClassName(i)，返回第 i 列的数据的 Java 类名
- getColumnTypeName(i)，返回第 i 列的数据库类型名称
- getColumnType(i)，返回第 i 列的 SQL 类型
使用 ResultSetMetaData 解析 ResultSet

// 构建Java和数据库之间的桥梁：URL，用户名，密码
Connection conn = DriverManager.getConnection(url, "root", "123456");

// 构建数据库执行者
Statement stmt = conn.createStatement();

// 执行SQL语句并返回结果到ResultSet
ResultSet rs = stmt.executeQuery("select bookid, bookname, price from t_book order by bookid");

// 获取结果集的元数据
ResultSetMetaData meta = rs.getMetaData();
int cols = meta.getColumnCount();
for (int i = 1; i <= cols; i++) {
  System.out.println(meta.getColumnName(i) + "," + meta.getColumnTypeName(i));
}

rs.close();
stmt.close();

4.数据库连接池

享元模式

经典 23 个设计模式的一种，属于结构型模式。
一个系统中存在大量的相同的对象，由于这类对象的大量使用，会造成系统内存的耗费，可以使用享元模式来减少系统中对象的数量

数据库连接池

池 Pool 的概念
- 初始数、最大数、增量、超时时间等参数。
常用的数据库连接池
- DBCP (Apache, http://commons.apache.org/，性能较差)
- C3P0 (https://www.mchange.com/projects/c3p0/)
- Druid (Alibaba, https://github.com/alibaba/druid)

C3P0 连接池

默认配置

<default-config> <!-- 默认配置 -->
  <property name="driverClass">com.mysql.jdbc.Driver</property>
  <property name="jdbcUrl">jdbc:mysql://localhost:3306/test</property>
  <property name="user">root</property>
  <property name="password">123456</property>
  <property name="initialPoolSize">5</property>
  <property name="maxPoolSize">20</property>
</default-config>

driverClass 驱动 class，这里为 mysql 的驱动
jdbcUrl jdbc 链接
user password 数据库用户名密码
initialPoolSize 初始数量：一开始创建多少条链接
maxPoolSize 最大数：最多有多少条链接
acquireIncrement 增量：用完每次增加多少个
maxIdleTime 最大空闲时间：超出的链接会被抛弃

// 从c3p0获取
conn = C3p0Factory.getConnection();
// C3p0Factory
public class C3p0Factory {
  private static ComboPooledDataSource dataSource = null;

  public static void init() throws Exception {

    dataSource = new ComboPooledDataSource();
    // 配置文件可以放在xml文件中
    // dataSource 会自动加载resources文件夹中c3p0-config.xml文件（如果存在）
    dataSource.setDriverClass("com.mysql.jdbc.Driver");
    dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/test");
    dataSource.setUser("root");
    dataSource.setPassword("123456");

    // the settings below are optional -- c3p0 can work with defaults
    dataSource.setMinPoolSize(5);
    dataSource.setAcquireIncrement(5);
    dataSource.setMaxPoolSize(20);
  }

  public static Connection getConnection() throws Exception {
    if (null == dataSource) {
      init();
    }
    return dataSource.getConnection();
  }
}

// 从Druid获取
conn = DruidFactory.getConnection();
public class DruidFactory1 {
  private static DruidDataSource dataSource = null;

  public static void init() throws Exception {
    // 配置文件可以放在properties文件中
    // Properties properties = new Properties();
    // InputStream in = DruidFactory2.class.getClassLoader().getResourceAsStream("druid.properties");
    // properties.load(in);
    // dataSource = (DruidDataSource)DruidDataSourceFactory.createDataSource(properties);
    dataSource = new DruidDataSource();
    dataSource.setDriverClassName("com.mysql.jdbc.Driver");
    dataSource.setUsername("root");
    dataSource.setPassword("123456");
    dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/test");
    dataSource.setInitialSize(5);
    dataSource.setMinIdle(1);
    dataSource.setMaxActive(10);
    // 启用监控统计功能 dataSource.setFilters("stat");
  }

  public static Connection getConnection() throws Exception {
    if (null == dataSource) {
      init();
    }
    return dataSource.getConnection();
  }
}

// 构建数据库执行者
Statement stmt = conn.createStatement();

1.数据库和 SQL

表

SQL

常规语句

2.JDBC 基本操作

操作步骤

Statement

注意事项

3.JDBC 高级操作

事务

JDBC 事务

PreparedStatement

ResultSetMetaData

4.数据库连接池

享元模式

数据库连接池

C3P0 连接池

Leave a comment