Hibernate 连接 OceanBase 数据库

<dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <version>8.0.25</version>
</dependency>
<dependency>
   <groupId>org.hibernate</groupId>
   <artifactId>hibernate-core</artifactId>
   <version>5.0.7.Final</version>
</dependency>

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
        <!--oceanbase连接信息-->
        <property name="connection.driver_class">com.mysql.cj.jdbc.Driver</property>
        <property name="connection.url">jdbc:mysql://10.100.xxx.xxx:18815/test</property>
        <property name="connection.username">admin</property>
        <property name="connection.password">******</property>

        <!-- 可选配置 -->
        <!--是否支持方言，以下MySQLMode和OracleMode视数据库模式情况配置-->
        <!--MySQLMode-->
        <property name="dialect">org.hibernate.dialect.MySQLDialect</property>
       <!--OracleMode-->
       <property name="dialect">org.hibernate.dialect.Oracle12cDialect</property>
        <!--执行CURD时是否打印sql 语句  -->
        <property name="show_sql">true</property>
        <!--自动建表(修改表)-->
        <!--<property name="hbm2ddl.auto">update</property>-->
        <!--<property name="hbm2ddl.auto">create</property>-->

        <!-- 资源注册 (实体类映射文件)-->
        <mapping resource="./UserModel.hbm.xml"/>
    </session-factory>
</hibernate-configuration>

public class User implements Serializable {
    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;
    //先注释一个字段，以便验证使用框架建表后，自动根据实体类新增属性修改表
    //private String tel;
    //。。。。。。构造器、get、set方法此处省略。。。。。。。。
}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
        '-//Hibernate/Hibernate Mapping DTD 3.0//EN'
        'http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd'>
<hibernate-mapping>
    <!-- 类与表的映射制作在class元素上 -->
    <!-- name:全路径类名 -->
    <!-- table:表名 -->
    <class name="com.alibaba.ob.pojo.User" table="user">
        <!-- 主键的映射制作在id元素上 -->
        <!-- name:对象中用于作为主键的属性名 -->
        <!-- colomn:表中主键字段名 -->
        <!-- 如果name与column值相同，可以省略column -->
        <id name="id" column="id">
            <!-- 将generator元素class属性设置为"assigned" 手动生成,必须给id -->
            <generator class="identity"  />

            <!--**要注意Hibernate主键生成策略**-->

        </id>
        <!-- 属性与字段的映射制作在property元素上 -->
        <!-- name:类中的属性名 -->
        <!-- column:表中的字段名 -->
        <!-- 如果name与column值相同，可以省略column -->
        <property name="username" />
        <property name="birthday" />
        <property name="sex" />
        <property name="address"/>
       <!--此处同实体类-->
    <!--<property name="tel"/>-->
    </class>
</hibernate-mapping>

@Test
    public void testConnection(){
     //加载配置信息
     Configuration conf = new Configuration().configure();
     //基于配置信息,创建SessionFactory对象
     SessionFactory sessionFactory = conf.buildSessionFactory();
     //打开一个与数据库相关的session对象
     Session session = sessionFactory.openSession();
        System.out.println(session);
    }

<—-自动建表（修改表）-->
<!--<property name="hbm2ddl.auto">update</property>-->
<!--<property name="hbm2ddl.auto">create‹/property>-->

@Test
    public void testCreateTableAndInsertData(){
        //创建要测试的对象
        User user = new User();
        user.setUsername("hibernateTest");
        user.setSex("女");
        user.setBirthday(new Date());
        user.setAddress("北京");

        //开启事务,基于session得到
        Transaction transaction = session.beginTransaction();
        //通过session保存数据
        session.save(user);
        //提交事务
        transaction.commit();
        //操作完毕,关闭session连接对象
        session.close();
    }

Hibernate: drop table if exists user
Hibernate: create table user (id integer not null auto_increment, username varchar (255), birthday datetime, sex varchar (255), address varchar (255), primary key (id))
Hibernate: insert into user (username, birthday, sex, address) values (?, ?, ?, ?)

Process finished with exit code

obclient> drop table user;
Query OK, 0 rows affected

obclient› select * from user;
+----+---------------+-----------------------+------+---------+
| id | username      | birthday              | sex  | address |
+----+---------------+-----------------------+------+---------+
| 1  | hibernateTest | 2020-09-17 17:22:32   | 女   | 北京     |
+----+---------------+-----------------------+------+---------+

<—-自动建表（修改表）-->
<!--<property name="hbm2ddl.auto">update</property>-->  //打开本行注释
<!--<property name="hbm2ddl.auto">create‹/property>-->  //注释掉本行

@Test
    public void testAlterTable(){
        //创建要测试的对象
        User user = new User();
        user.setUsername("hibernateTest");
        user.setSex("女");
        user.setBirthday(new Date());
        user.setAddress("北京");
        user.setTel("12345678911");

        //开启事务,基于session得到
        Transaction transaction = session.beginTransaction();
        //通过session保存数据

        session.save(user);
        //提交事务
        transaction.commit();
        //操作完毕,关闭session连接对象
        session.close();
    }

Hibernate: insert into user (username, birthday, sex, address, tel) values (?, ?, ?, ?, ?)

Process finished with exit code

obclient› select * from user;
+----+---------------+-----------------------+------+---------+
| id | username      | birthday              | sex  | address |
+----+---------------+-----------------------+------+---------+
| 1  | hibernateTest | 2020-09-17 17:30:39   | 女   | 北京     |
+----+---------------+-----------------------+------+---------+
1 row in set

obclient› select * from user;
+----+---------------+-----------------------+------+---------+---------------+
| id | username      | birthday              | sex  | address | tel           |
+----+---------------+-----------------------+------+---------+---------------+
| 1  | hibernateTest | 2020-09-17 17:30:39   | 女   | 北京     | NULL          |
| 2  | hibernateTest | 2020-09-17 17:31:27   | 女   | 北京     | 123456789111  |
+----+---------------+-----------------------+------+---------+---------------+
2 row in set

@Test
    public void testInsert() {
        User u1 = new User("asd", new Date(), "男", "漳州");
        User u2 = new User("qwe", new Date(), "女", "杭州");
        User u3 = new User("zxc", new Date(), "男", "上海");
        User u4 = new User("xcv", new Date(), "女", "杭州");
        User u5 = new User("sdf", new Date(), "男", "杭州");
        User u6 = new User("wer", new Date(), "女", "杭州");
        User u7 = new User("ert", new Date(), "男", "漳州");
        User u8 = new User("rty", new Date(), "女", "上海");
        User u9 = new User("tyu", new Date(), "男", "杭州");
        ArrayList<User> users = new ArrayList<>();
        users.add(u1);
        users.add(u2);
        users.add(u3);
        users.add(u4);
        users.add(u5);
        users.add(u6);
        users.add(u7);
        users.add(u8);
        users.add(u9);
        //开启事务,基于session得到
        Transaction transaction = session.beginTransaction();
        //通过session保存数据
        for (User user : users) {
            session.save(user);
        }
        //提交事务
        transaction.commit();
        //操作完毕,关闭session连接对象
        session.close();
    }

obclient› select * from user;
+----+---------------+-----------------------+------+---------+---------------+
| id | username      | birthday              | sex  | address | tel           |
+----+---------------+-----------------------+------+---------+---------------+
| 1  | hibernateTest | 2020-09-17 17:30:39   | 女   | 北京     | NULL          |
| 2  | hibernateTest | 2020-09-17 17:31:27   | 女   | 北京     | 123456789111  |
| 3  | asd           | 2020-09-17 18:05:17   | 男   | 漳州     | NULL          |
| 4  | qwe           | 2020-09-17 18:05:17   | 女   | 杭州     | NULL          |
| 5  | zxc           | 2020-09-17 18:05:17   | 男   | 上海     | NULL          |
| 6  | xcv           | 2020-09-17 18:05:17   | 女   | 杭州     | NULL          |
| 7  | sdf           | 2020-09-17 18:05:17   | 男   | 杭州     | NULL          |
| 8  | wer           | 2020-09-17 18:05:17   | 女   | 杭州     | NULL          |
| 9  | ert           | 2020-09-17 18:05:17   | 男   | 漳州     | NULL          |
| 10 | rty           | 2020-09-17 18:05:17   | 女   | 上海     | NULL          |
| 11 | tyu           | 2020-09-17 18:05:17   | 男   | 杭州     | NULL          |
+----+---------------+-----------------------+------+---------+---------------+

@Test
    public void testQuery() {
       //SQL模式
  //SQLQuery query = sessionFactory.openSession().createSQLQuery("select * from user where sex = '男'").addEntity(User.class);
        //HQL模式
        //Query query = sessionFactory.openSession().createQuery("select User from User User where User.sex = '男'");
        List<User> list = query.list();
        list.forEach(System.out::println);
    }

@Test
    public void testChange(){
        User user = (User) session.createSQLQuery("select * from user where id = 1").addEntity(User.class).list().get(0);
        System.out.println("修改前："+user);
        user.setAddress("杭州");
        session.update(user);
        user = (User) session.createSQLQuery("select * from user where id = 1").addEntity(User.class).list().get(0);
        System.out.println("修改后："+user);
    }

@Test
    public void testDelete(){
        Transaction transaction = session.beginTransaction();
        List<User> list = session.createSQLQuery("select * from user").addEntity(User.class).list();
        System.out.println(list);
        int i = session.createSQLQuery("delete from user where id = " + list.get(0).getId()).executeUpdate();
        list = session.createSQLQuery("select * from user").addEntity(User.class).list();
        System.out.println(list);
        //提交事务
        transaction.commit();
        //操作完毕,关闭session连接对象
        session.close();
    }