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