📒 笔记文档

1. JDBC基本使用

1.1 引入必要依赖

<dependencies>
   <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-core</artifactId>
        <version>3.3.2</version> <!-- 更高的mp版本依赖于spring框架，这里只能用3.3.2 -->
    </dependency>
        <!-- sqlite-jdbc依赖 -->
    <dependency>
        <groupId>org.xerial</groupId>
        <artifactId>sqlite-jdbc</artifactId>
        <version>3.49.1.0</version>
    </dependency>
    
        <!--mysql连接 -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.33</version>
    </dependency>
    
    <!-- lombok -->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.42</version>
    </dependency>
    
    <!-- druid数据库连接池-->
	<dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.2.28</version>
    </dependency>
</dependencies>

注意点：

• mybatis-plus-core核心依赖的版本不能太高，高版本还依赖部分spring相关功能。
• sqlite-jdbc依赖

1.2 使用

• 按照jdbc的标准，首先要配置数据源。

//数据源连接地址。sqlite其实就是路径，例：D://te.db
// 或者内存数据库"jdbc:sqlite::memory:";//直接往内存中读写数据。
private static String dbUrl = "jdbc:sqlite:test.db"; 

//sqlite数据源 实现了DataSource接口，一般jdbc数据源实现这个接口，mysql也是
SQLiteDataSource dataSource = new SQLiteDataSource(); 
dataSource.setUrl(dbUrl);

• 接下来就是jdbc部分的内容。不管数据源是什么玩意，只要你实现了我jdbc的接口规范。就能使用jdbc操作数据库，接下来编写测试。

  • 注意：

    Statement，每次执行都编译，❌ 不支持占位符，❌ 易受攻击，适用场景DDL、静态 SQL。

    PreparedStatement，预编译，可复用，✅ 支持 ? 占位符，✅ 防止注入，适用场景DML、DQL、动态参数。

public class SqliteDemo001 {
    private static String dbUrl = "jdbc:sqlite:test.db";
    private static SQLiteDataSource dataSource = new SQLiteDataSource();

    public static void main(String[] args) {
        dataSource.setUrl(dbUrl);

    }
    private static void createTable() throws Exception{
        String sql = "CREATE TABLE IF NOT EXISTS user (" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "name TEXT NOT NULL, " +
                "age INTEGER, " +
                "email TEXT, " +
                "deleted INTEGER DEFAULT 0" +
                ")";
        //建立jdbc连接，这一步是与数据库建立连接
        Connection connection = dataSource.getConnection();
        //设置自动提交为false，表示不自动提交，自己处理事务，只对本次连接
        connection.setAutoCommit(false);
        //创建statement对象，用于执行sql语句
        Statement statement = connection.createStatement();
        //执行sql语句
        boolean execute = statement.execute(sql);
        //提交事务，不提交不会执行这一次的事务
        connection.commit();
        //关闭连接 自动提交
        statement.close();
        connection.close();
    }
    private static void insertData() throws Exception{
        String sql = "INSERT INTO user (name, age, email) VALUES ('张三', 25, 'zhangsan@example.com')";

        //获取连接
        Connection connection = dataSource.getConnection();
        connection.setAutoCommit(false);
        PreparedStatement statement = connection.prepareStatement(sql);
        int i = statement.executeUpdate();
        System.out.println("插入了" + i + "行数据");
        //提交事务，不提交不会执行这一次的事务
        connection.commit();
        //关闭连接
        statement.close();
        connection.close();
    }
}

2. 结合mybatis-plus

新建实体类

@Data
@TableName("user")
public class User {
    @TableId(type = IdType.AUTO)
    private Long id;
    private String name;
    private Integer age;
    private String email;
    @TableLogic
    private Integer deleted;
}

public class SqliteDemo002 {
    private static String dbUrl = "jdbc:sqlite:test.db";
    private static SQLiteDataSource dataSource = new SQLiteDataSource();
    public static void main(String[] args) throws Exception{
        dataSource.setUrl(dbUrl);

        //mybatis-plus相关配置
        // 事务工厂
        TransactionFactory transactionFactory = new JdbcTransactionFactory();
        // 环境配置  "development"参数随便即可
        //一般用于区分不同环境（开发、测试、生产）
        Environment environment = new Environment("development", transactionFactory, dataSource);

        MybatisConfiguration configuration = new MybatisConfiguration();
        configuration.setEnvironment(environment);

        //开启驼峰映射user_name->userName
        configuration.setMapUnderscoreToCamelCase(true);

        // 添加Mapper，比较重要，orm映射关系，配置
        // spring框架启动时，会自动扫描mapper接口，会做这个配置，这里我们手写
        configuration.addMapper(UserMapper.class);

        //全局策略配置类,逻辑删除、主键生成策略等。 这3行代码可要可不要。
        GlobalConfig globalConfig = new GlobalConfig();
        globalConfig.setBanner(false); //启动mp时，不打印mp启动信息
        configuration.setGlobalConfig(globalConfig);//关联全局策略

        // 创建 SqlSessionFactory，这个是操作数据库的工厂类
        MybatisSqlSessionFactoryBuilder builder = new MybatisSqlSessionFactoryBuilder();
        SqlSessionFactory sqlSessionFactory = builder.build(configuration);
        //从工厂中创建连接器
        SqlSession sqlSession = sqlSessionFactory.openSession();

        // 获取Mapper
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> users = userMapper.selectAll();
        System.out.println("用户列表: " + users);

//        User user = new User("张三", 20,"lisi@email.com");
//        int insertResult = userMapper.insert(user);
//        System.out.println("插入结果: " + insertResult);

        sqlSession.commit(); // 提交事务
    }
}

3. 连接池

3.1 基本使用

public class SqliteDemo003 {
    private static String dbUrl = "jdbc:sqlite:test.db";
    public static void main(String[] args) throws SQLException {
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setUrl(dbUrl);
        druidDataSource.setDriverClassName("org.sqlite.JDBC");
        druidDataSource.setInitialSize(1);// 初始连接数
        druidDataSource.setMaxActive(10);// 最大连接数
        druidDataSource.setMinIdle(1);// 最小连接数
        druidDataSource.setMaxWait(3000);// 最大等待时间3000ms

        druidDataSource.setValidationQuery("select 1");//用来确定是否真正连接成功。以及保持连接
        
        DruidPooledConnection connection = druidDataSource.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("select * from `user`");
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            System.out.println(resultSet.getInt(1) + " " + resultSet.getString(2) + " " + resultSet.getInt(3) + " " + resultSet.getString(4));
        }
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }
}

3.2 数据库连接池+mybatis-plus

public class SqliteDemo004 {
    private static String dbUrl = "jdbc:sqlite:test.db";
    public static void main(String[] args) {
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setUrl(dbUrl);
        druidDataSource.setDriverClassName("org.sqlite.JDBC");
        druidDataSource.setInitialSize(1); // 初始连接数
        druidDataSource.setMaxActive(10); // 最大连接数
        druidDataSource.setMinIdle(1); // 最小连接数
        druidDataSource.setMaxWait(3000); // 最大等待时间3000ms
        druidDataSource.setValidationQuery("select 1"); // 验证

        TransactionFactory transactionFactory = new JdbcTransactionFactory();
        // 环境配置  "development"参数随便即可
        //一般用于区分不同环境（开发、测试、生产）
        Environment environment = new Environment("development", transactionFactory, druidDataSource);

        MybatisConfiguration configuration = new MybatisConfiguration();
        configuration.setEnvironment(environment);

        //开启驼峰映射user_name->userName
        configuration.setMapUnderscoreToCamelCase(true);

        // 添加Mapper，比较重要，orm映射关系，配置
        // spring框架启动时，会自动扫描mapper接口，会做这个配置
        configuration.addMapper(UserMapper.class);

        //全局策略配置类,逻辑删除、主键生成策略等。
        GlobalConfig globalConfig = new GlobalConfig();
        globalConfig.setBanner(false);
        configuration.setGlobalConfig(globalConfig);//关联全局策略

        // 创建 SqlSessionFactory，这个是操作数据库的工厂类
        MybatisSqlSessionFactoryBuilder builder = new MybatisSqlSessionFactoryBuilder();
        SqlSessionFactory sqlSessionFactory = builder.build(configuration);
        //从工厂中创建连接器
        SqlSession sqlSession = sqlSessionFactory.openSession();

        // 获取Mapper
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        while(true){
            List<User> users = userMapper.selectAll();
            System.out.println("用户列表: " + users);
            sqlSession.commit();
            Thread.sleep(1000); // 休眠1秒
        }
    }
}

多数据源

import com.alibaba.druid.pool.DruidDataSource;
import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.core.MybatisSqlSessionFactoryBuilder;
import com.baomidou.mybatisplus.core.config.GlobalConfig;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.transaction.TransactionFactory;
import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;
import org.apache.ibatis.mapping.Environment;

import java.util.List;

public class SqliteDemo004 {
    // 两个数据库的URL
    private static final String DB_URL1 = "jdbc:sqlite:test.db";
    private static final String DB_URL2 = "jdbc:sqlite:test2.db";

    public static void main(String[] args) throws Exception {
        // 创建第一个数据源（test.db）
        DruidDataSource dataSource1 = createDataSource(DB_URL1);
        // 创建第二个数据源（test2.db）
        DruidDataSource dataSource2 = createDataSource(DB_URL2);

        // 为第一个数据库创建 SqlSessionFactory
        SqlSessionFactory sqlSessionFactory1 = createSqlSessionFactory(dataSource1, UserMapper.class);
        // 为第二个数据库创建 SqlSessionFactory
        SqlSessionFactory sqlSessionFactory2 = createSqlSessionFactory(dataSource2, UserMapper.class);

        // 循环查询两个数据库
        while (true) {
            // 查询第一个数据库
            try (SqlSession session = sqlSessionFactory1.openSession()) {
                UserMapper mapper = session.getMapper(UserMapper.class);
                List<User> users1 = mapper.selectAll();
                System.out.println("test.db 用户列表: " + users1);
            }

            // 查询第二个数据库
            try (SqlSession session = sqlSessionFactory2.openSession()) {
                UserMapper mapper = session.getMapper(UserMapper.class);
                List<User> users2 = mapper.selectAll();
                System.out.println("test2.db 用户列表: " + users2);
            }

            Thread.sleep(1000); // 每秒查询一次
        }
    }

    /**
     * 创建并配置 Druid 数据源
     */
    private static DruidDataSource createDataSource(String url) {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUrl(url);
        dataSource.setDriverClassName("org.sqlite.JDBC");
        dataSource.setInitialSize(1);
        dataSource.setMaxActive(10);
        dataSource.setMinIdle(1);
        dataSource.setMaxWait(3000);
        dataSource.setValidationQuery("select 1");
        return dataSource;
    }

    /**
     * 构建 SqlSessionFactory
     */
    private static SqlSessionFactory createSqlSessionFactory(DruidDataSource dataSource, Class<?> mapperClass) {
        TransactionFactory transactionFactory = new JdbcTransactionFactory();
        Environment environment = new Environment("development", transactionFactory, dataSource);

        MybatisConfiguration configuration = new MybatisConfiguration();
        configuration.setEnvironment(environment);
        configuration.setMapUnderscoreToCamelCase(true);
        configuration.addMapper(mapperClass);

        GlobalConfig globalConfig = new GlobalConfig();
        globalConfig.setBanner(false);
        configuration.setGlobalConfig(globalConfig);

        MybatisSqlSessionFactoryBuilder builder = new MybatisSqlSessionFactoryBuilder();
        return builder.build(configuration);
    }
}