Mybatis

字数: 0 字 时长: 0 分钟

1. JDBC

• 引入依赖

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.33</version>
</dependency>

• 查询

public static void main(String[] args) {

    String url = "jdbc:mysql://8.140.207.120:3306/account_db?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
    String user = "root";
    String password = "ttdgg123";
    String sql = "select user_id, money from account_tbl where id = ?";
    String insertSql = "insert into account_tbl(user_id, money) values (?,?)";
    String updateSql = "update account_tbl set money = ? where id = ?";

    try (
            //1、自动获取并管理 Connection PreparedStatement ResultSet
            Connection connection = DriverManager.getConnection(url, user, password);
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
    ) {
        //2、设置查询参数
        preparedStatement.setInt(1, 1);

        //3、执行查询并处理 ResultSet
        try (
                ResultSet resultSet = preparedStatement.executeQuery()
        ) {
            while (resultSet.next()) {
                int userId = resultSet.getInt("user_id");
                int money = resultSet.getInt("money");
                System.out.println("userId:" + userId + ",money:" + money);
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

• 增删改 （开启事务）

try (
        Connection conn = DriverManager.getConnection(url,user,password);
){
    try (
            PreparedStatement insertPs = conn.prepareStatement(insertSql);
            PreparedStatement updatePs = conn.prepareStatement(updateSql)
     ){
            // 关闭自动更新，开启事务
            conn.setAutoCommit(false);

            // 执行插入
            insertPs.setInt(1,2);
            insertPs.setInt(2,2000);
            insertPs.executeUpdate();

            // 执行更新
            updatePs.setInt(1,1000);
            updatePs.setInt(2,1);
            updatePs.executeUpdate();

            conn.commit();
        }catch (SQLException e) {
            //如果失败则回滚
            conn.rollback();
            e.printStackTrace();
        }
}catch (SQLException e) {
    e.printStackTrace();
}

2. Mybatis

• 引入依赖

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.33</version>
</dependency>

<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.13</version>
</dependency>
<!--连接池-->
<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>5.1.0</version>
</dependency>

• 配置连接池

public class HikariCPDataSourceFactory implements DataSourceFactory {

    private Properties properties;

    @Override
    public void setProperties(Properties props) {
        this.properties = props;
    }

    @Override
    public DataSource getDataSource() {
        HikariConfig hikariConfig = new HikariConfig(properties);
        return new HikariDataSource(hikariConfig);
    }
}

• mybatis-config.xml 配置文件

<?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>
    <settings>
        <!-- 开启驼峰命名映射（user_name → userName） -->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

    <!-- 数据库配置 -->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/> <!-- 使用 JDBC 事务管理 -->
            <dataSource type="com.ttdxg.mybatis.HikariCPDataSourceFactory">
                <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
                <property name="jdbcUrl"
                          value="jdbc:mysql://8.140.207.120:3306/account_db?useSSL=false&amp;allowPublicKeyRetrieval=true&amp;serverTimezone=UTC"/>
                <property name="username" value="root"/>
                <property name="password" value="ttdgg123"/>
            </dataSource>
        </environment>
    </environments>

    <!-- 映射器扫描（XML 或注解） -->
    <mappers>
        <mapper class="com.ttdxg.mybatis.AccountMapper"/> <!-- 注解方式 -->
        <!-- 或 -->
        <!-- <mapper resource="mapper/AccountMapper.xml"/> --> <!-- XML方式 -->
    </mappers>
</configuration>

• 代码示例

public static void main(String[] args) {
    //1. 加载 Mybatis 配置文件创建 SqlSessionFactory
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder()
            .build(Resources.getResourceAsStream("mybatis-config.xml"));

    //2. 手动获取 SqlSession (自动提交默认关闭，需手动控制事务)
    SqlSession sqlSession = sqlSessionFactory.openSession(false);// 关闭自动提交

    try {
        AccountMapper mapper = sqlSession.getMapper(AccountMapper.class);

        //业务操作1: 插入用户
        Account account = new Account();
        account.setUserId(6L);
        account.setMoney(6000);
        mapper.insertAccount(account);

        //业务操作2: 查询用户
        Account result = mapper.selectByUserId(1L);
        //手动提交
        sqlSession.commit();
    }catch (Exception e) {
        //回滚
        sqlSession.rollback();
    }finally {
        sqlSession.close();
    }
}