数据库开发 - Java学习笔记

关系型数据库基础

关系型数据库是基于关系模型的数据库，使用表格存储数据，表格之间可以建立关联关系。常见的关系型数据库包括MySQL、PostgreSQL、Oracle、SQL Server等。

数据库设计原则

良好的数据库设计应遵循以下原则：

原子性：每个字段应该包含最小的数据单元
唯一性：每条记录应该有唯一标识（主键）
数据完整性：确保数据的准确性和一致性
关系合理性：表之间的关系应该合理设计
范式化：遵循数据库范式，减少数据冗余

数据库范式

数据库范式是数据库设计的规范，常见的有三种：

第一范式（1NF）：要求数据库表的每一列都是不可分割的原子数据项
第二范式（2NF）：在1NF的基础上，非主键列必须完全依赖于主键，而不能只依赖于主键的一部分
第三范式（3NF）：在2NF的基础上，非主键列必须直接依赖于主键，不能存在传递依赖

数据库索引

索引是数据库中用于提高查询效率的数据结构，常见的索引类型有：

主键索引：用于唯一标识表中的每一行数据
唯一索引：确保索引列的值唯一，但可以有多个NULL值
普通索引：最基本的索引类型，没有唯一性限制
复合索引：由多个列组成的索引
全文索引：用于全文搜索

注意：索引可以提高查询效率，但会降低插入、更新和删除的效率，因为这些操作需要维护索引。因此，索引的使用需要权衡利弊。

SQL语法

SQL（Structured Query Language）是用于管理关系型数据库的标准语言，主要分为以下几类：

数据定义语言（DDL）

用于定义数据库结构，如创建、修改和删除表。

-- 创建表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    password VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 修改表
ALTER TABLE users ADD COLUMN status TINYINT DEFAULT 1;

-- 删除表
DROP TABLE users;

数据操作语言（DML）

用于操作数据库中的数据，如插入、更新和删除数据。

-- 插入数据
INSERT INTO users (username, email, password) 
VALUES ('john_doe', 'john@example.com', 'hashed_password');

-- 更新数据
UPDATE users 
SET email = 'new_email@example.com' 
WHERE username = 'john_doe';

-- 删除数据
DELETE FROM users 
WHERE username = 'john_doe';

数据查询语言（DQL）

用于从数据库中查询数据。

-- 基本查询
SELECT * FROM users;

-- 条件查询
SELECT id, username, email 
FROM users 
WHERE status = 1;

-- 排序
SELECT * FROM users 
ORDER BY created_at DESC;

-- 分页
SELECT * FROM users 
LIMIT 10 OFFSET 20;

-- 连接查询
SELECT u.username, p.title 
FROM users u 
JOIN posts p ON u.id = p.user_id;

事务控制语言（TCL）

用于管理事务，确保数据的一致性。

-- 开始事务
BEGIN TRANSACTION;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

JDBC编程

JDBC（Java Database Connectivity）是Java语言访问关系型数据库的标准API。

JDBC基本步骤

加载JDBC驱动
建立数据库连接
创建Statement对象
执行SQL语句
处理结果集
关闭连接

import java.sql.*;

public class JdbcDemo {
    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        
        try {
            // 1. 加载JDBC驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            
            // 2. 建立数据库连接
            String url = "jdbc:mysql://localhost:3306/mydb?useSSL=false";
            String user = "root";
            String password = "password";
            conn = DriverManager.getConnection(url, user, password);
            
            // 3. 创建Statement对象
            stmt = conn.createStatement();
            
            // 4. 执行SQL语句
            String sql = "SELECT id, username, email FROM users";
            rs = stmt.executeQuery(sql);
            
            // 5. 处理结果集
            while (rs.next()) {
                int id = rs.getInt("id");
                String username = rs.getString("username");
                String email = rs.getString("email");
                System.out.println("ID: " + id + ", Username: " + username + ", Email: " + email);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 6. 关闭连接
            try {
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

PreparedStatement

PreparedStatement是Statement的子接口，用于执行参数化查询，可以防止SQL注入攻击。

// 使用PreparedStatement
String sql = "INSERT INTO users (username, email, password) VALUES (?, ?, ?)";
PreparedStatement pstmt = conn.prepareStatement(sql);

pstmt.setString(1, "john_doe");
pstmt.setString(2, "john@example.com");
pstmt.setString(3, "hashed_password");

int rowsAffected = pstmt.executeUpdate();

事务管理

JDBC提供了事务管理功能，可以确保一组操作要么全部成功，要么全部失败。

// 事务管理
conn.setAutoCommit(false); // 关闭自动提交

try {
    // 执行多个SQL操作
    // ...
    
    conn.commit(); // 提交事务
} catch (SQLException e) {
    conn.rollback(); // 回滚事务
    e.printStackTrace();
} finally {
    conn.setAutoCommit(true); // 恢复自动提交
}

MyBatis框架

MyBatis是一个优秀的持久层框架，它支持自定义SQL、存储过程以及高级映射。

MyBatis配置

MyBatis的配置文件包含数据库连接信息和映射文件位置。

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
  PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
  <environments default="development">
    <environment id="development">
      <transactionManager type="JDBC"/>
      <dataSource type="POOLED">
        <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/mydb"/>
        <property name="username" value="root"/>
        <property name="password" value="password"/>
      </dataSource>
    </environment>
  </environments>
  <mappers>
    <mapper resource="com/example/mapper/UserMapper.xml"/>
  </mappers>
</configuration>

映射文件

映射文件定义了SQL语句和结果映射。

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.mapper.UserMapper">
  <select id="selectUser" resultType="com.example.model.User">
    SELECT id, username, email FROM users WHERE id = #{id}
  </select>
  
  <insert id="insertUser" parameterType="com.example.model.User">
    INSERT INTO users (username, email, password)
    VALUES (#{username}, #{email}, #{password})
  </insert>
  
  <update id="updateUser" parameterType="com.example.model.User">
    UPDATE users
    SET username = #{username}, email = #{email}
    WHERE id = #{id}
  </update>
  
  <delete id="deleteUser" parameterType="int">
    DELETE FROM users WHERE id = #{id}
  </delete>
</mapper>

Mapper接口

Mapper接口定义了与映射文件中SQL语句对应的方法。

public interface UserMapper {
    User selectUser(int id);
    void insertUser(User user);
    void updateUser(User user);
    void deleteUser(int id);
}

使用示例

// 获取SqlSessionFactory
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

// 获取SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
    // 获取Mapper
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    
    // 查询用户
    User user = userMapper.selectUser(1);
    System.out.println(user.getUsername());
    
    // 插入用户
    User newUser = new User();
    newUser.setUsername("jane_doe");
    newUser.setEmail("jane@example.com");
    newUser.setPassword("hashed_password");
    userMapper.insertUser(newUser);
    
    // 提交事务
    sqlSession.commit();
} finally {
    // 关闭SqlSession
    sqlSession.close();
}

JPA与Hibernate

JPA（Java Persistence API）是Java EE标准的ORM规范，Hibernate是JPA的一种实现。

实体类

JPA使用注解将Java类映射到数据库表。

import javax.persistence.*;

@Entity
@Table(name = "users")
public class User {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @Column(name = "username", nullable = false, unique = true)
    private String username;
    
    @Column(name = "email", nullable = false)
    private String email;
    
    @Column(name = "password", nullable = false)
    private String password;
    
    // 构造函数、getter和setter
}

EntityManager

EntityManager是JPA的核心接口，用于管理实体的生命周期。

// 创建EntityManagerFactory
EntityManagerFactory emf = Persistence.createEntityManagerFactory("myPersistenceUnit");

// 创建EntityManager
EntityManager em = emf.createEntityManager();

// 开始事务
em.getTransaction().begin();

try {
    // 查询用户
    User user = em.find(User.class, 1L);
    System.out.println(user.getUsername());
    
    // 插入用户
    User newUser = new User();
    newUser.setUsername("jane_doe");
    newUser.setEmail("jane@example.com");
    newUser.setPassword("hashed_password");
    em.persist(newUser);
    
    // 提交事务
    em.getTransaction().commit();
} catch (Exception e) {
    // 回滚事务
    em.getTransaction().rollback();
    e.printStackTrace();
} finally {
    // 关闭EntityManager
    em.close();
}

// 关闭EntityManagerFactory
emf.close();

JPQL查询

JPQL（Java Persistence Query Language）是JPA定义的面向对象的查询语言。

// 基本查询
Query query = em.createQuery("SELECT u FROM User u");
List users = query.getResultList();

// 条件查询
Query query = em.createQuery("SELECT u FROM User u WHERE u.username = :username");
query.setParameter("username", "john_doe");
User user = (User) query.getSingleResult();

// 命名查询
@Entity
@NamedQueries({
    @NamedQuery(name = "User.findAll", query = "SELECT u FROM User u"),
    @NamedQuery(name = "User.findByUsername", query = "SELECT u FROM User u WHERE u.username = :username")
})
public class User {
    // ...
}

// 使用命名查询
List users = em.createNamedQuery("User.findAll", User.class).getResultList();
User user = em.createNamedQuery("User.findByUsername", User.class)
              .setParameter("username", "john_doe")
              .getSingleResult();

MySQL性能优化

MySQL性能优化是提高数据库性能的重要手段，包括SQL优化、索引优化、配置优化等。

SQL优化

避免SELECT *：只查询需要的列，减少数据传输量
使用LIMIT：限制结果集大小，避免返回过多数据
避免使用OR：使用UNION ALL代替OR，提高查询效率
使用EXPLAIN：分析SQL执行计划，找出性能瓶颈

-- 使用EXPLAIN分析SQL
EXPLAIN SELECT * FROM users WHERE username = 'john_doe';

索引优化

为WHERE条件创建索引：加速查询
为JOIN条件创建索引：提高连接查询效率
避免过多索引：索引会占用空间并降低写入性能
定期分析和优化表：保持索引的效率

-- 创建索引
CREATE INDEX idx_username ON users(username);

-- 分析表
ANALYZE TABLE users;

-- 优化表
OPTIMIZE TABLE users;

配置优化

调整缓冲区大小：如innodb_buffer_pool_size
优化连接池：设置合适的最大连接数
启用查询缓存：缓存频繁执行的查询结果
使用慢查询日志：找出执行慢的查询

# my.cnf配置示例
[mysqld]
innodb_buffer_pool_size = 1G
max_connections = 100
query_cache_type = 1
query_cache_size = 64M
slow_query_log = 1
long_query_time = 2

警告：数据库优化是一个复杂的过程，需要根据具体情况进行调整。在生产环境中修改配置前，应该先在测试环境中验证效果。

目录

关系型数据库基础

数据库设计原则

数据库范式

数据库索引

SQL语法

数据定义语言（DDL）

数据操作语言（DML）

数据查询语言（DQL）

事务控制语言（TCL）

JDBC编程

JDBC基本步骤

PreparedStatement

事务管理

MyBatis框架

MyBatis配置

映射文件

Mapper接口

使用示例

JPA与Hibernate

实体类

EntityManager

JPQL查询

MySQL性能优化

SQL优化

索引优化

配置优化