DEV Community

Scale
Scale

Posted on

Using MyBatis with GBase Database: A Complete CRUD Example for Java Developers

#gbase #database

Integrating ORM frameworks with a distributed database is a key skill for modern backend development. If you're working with GBase database, using MyBatis can greatly simplify data access while maintaining full control over SQL.

In this guide, we’ll walk through a complete example of using MyBatis with GBase 8s, including configuration, CRUD operations, and pagination.

🚀 Why Use MyBatis with GBase Database?

GBase is a powerful enterprise-grade database, but writing raw JDBC code can become complex.

MyBatis provides:

  • Flexible SQL control
  • Easy mapping between Java objects and database tables
  • Support for dynamic SQL
  • Lightweight compared to full ORM frameworks

👉 Combining MyBatis with GBase allows you to build efficient and maintainable data layers. :contentReference[oaicite:0]{index=0}

⚙️ Project Setup

🧰 Required Components

  • JDK 1.8
  • MyBatis 3.x
  • GBase 8s database
  • GBase JDBC Driver

📁 Database Connection Configuration

Create a db.properties file:

driver=com.gbasedbt.jdbc.IfxDriver
url=jdbc:gbasedbt-sqli://192.168.1.71:9088/mybatis:GBASEDBTSERVER=gbase01;
username=gbasedbt
password=GBase123
Enter fullscreen mode Exit fullscreen mode


`

This defines how your Java application connects to the GBase database.

🔧 MyBatis Configuration

`xml

<typeAliases>
    <typeAlias type="com.example.Student" alias="Student"/>
</typeAliases>

<typeHandlers>
    <typeHandler handler="org.apache.ibatis.type.StringTypeHandler"
        jdbcType="LONGVARCHAR" javaType="String" />
</typeHandlers>

<environments default="development">
    <environment id="development">
        <transactionManager type="JDBC"/>
        <dataSource type="POOLED">
            <property name="driver" value="${driver}"/>
            <property name="url" value="${url}"/>
            <property name="username" value="${username}"/>
            <property name="password" value="${password}"/>
        </dataSource>
    </environment>
</environments>

<mappers>
    <mapper resource="StudentMapper.xml"/>
</mappers>
Enter fullscreen mode Exit fullscreen mode


`

👉 MyBatis uses XML configuration to define database connectivity and mappings. (GBase 8s)

🧱 Step 1: Create Database Table

sql
CREATE TABLE student (
id SERIAL NOT NULL,
username VARCHAR(60),
usertext TEXT,
userphoto BYTE,
PRIMARY KEY(id)
);

🧩 Step 2: Define Java Entity

`java
public class Student {
private int id;
private String userName;
private String userText;
private byte[] userPhoto;

// getters and setters
Enter fullscreen mode Exit fullscreen mode

}
`

🔄 Step 3: Mapper Interface

`java
public interface StudentMapper {

List<Student> listStudents();

List<Student> listStudentsByPage(int pageNum, int pageSize);

void addStudent(Student student);

void updateStudent(int id, String userText);

void deleteStudent(int id);

void createStudent();
Enter fullscreen mode Exit fullscreen mode

}
`

🗂️ Step 4: Mapper XML

`xml

<insert id="addStudent" parameterType="Student">
    INSERT INTO student(username, usertext, userphoto)
    VALUES (#{userName}, #{userText}, #{userPhoto})
</insert>

<select id="listStudents" resultType="Student">
    SELECT * FROM student
</select>

<select id="listStudentsByPage" resultType="Student">
    SELECT SKIP #{pageNum} FIRST #{pageSize} * FROM student
</select>

<update id="updateStudent">
    UPDATE student SET usertext = #{userText} WHERE id = #{id}
</update>

<delete id="deleteStudent">
    DELETE FROM student WHERE id = #{id}
</delete>
Enter fullscreen mode Exit fullscreen mode


`

👉 GBase supports pagination using SKIP ... FIRST ..., which differs from MySQL’s LIMIT. (GBase 8s)

🧪 Step 5: Testing CRUD Operations

`java
SqlSession session = sqlSessionFactory.openSession();
StudentMapper mapper = session.getMapper(StudentMapper.class);

// Create table
mapper.createStudent();

// Insert
mapper.addStudent(new Student("Alice", "Test user", null));

// Query
List list = mapper.listStudents();

// Update
mapper.updateStudent(1, "Updated text");

// Delete
mapper.deleteStudent(1);

session.commit();
session.close();
`

📊 Batch Insert Example

xml
<insert id="batchInsert">
INSERT INTO student(username)
SELECT * FROM (
<foreach collection="list" item="item" separator=" UNION ALL ">
SELECT '${item.userName}' FROM dual
</foreach>
)
</insert>

⚠️ Note: Some large object types (e.g., TEXT, BYTE) may not be suitable for batch operations in this mode. (GBase 8s)

⚠️ Key Differences in GBase SQL

Feature GBase MySQL
Pagination SKIP ... FIRST LIMIT
Data types BYTE, TEXT BLOB, TEXT
Driver IfxDriver MySQL Driver

🧠 Best Practices

  • ✅ Use MyBatis XML for precise SQL control
  • ✅ Handle GBase-specific SQL syntax (e.g., pagination)
  • ✅ Avoid batch inserts with large object fields
  • ✅ Use connection pooling for performance
  • ✅ Validate JDBC driver compatibility

🧩 Real-World Takeaway

From practical implementations, combining MyBatis + GBase database provides:

  • High flexibility (SQL-first approach)
  • Strong performance
  • Better control compared to heavy ORM frameworks

📌 Final Thoughts

If you're building enterprise applications on GBase, MyBatis is a powerful companion.

With proper configuration and understanding of GBase-specific SQL features, you can:

  • Build scalable applications
  • Simplify database access
  • Maintain high performance

💬 Have you used MyBatis with GBase or other databases? Share your experience below!

`plaintext

If you want, I can also:

  • Generate a Hibernate version (for comparison article)
  • Create a Spring Boot + MyBatis + GBase tutorial
  • Or make a more advanced version (transactions, caching, performance tuning) 🚀 ::contentReference[oaicite:4]{index=4} `

Top comments (0)