暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / Mybatis操作南大通用GBase 8s数据库示例

Mybatis操作南大通用GBase 8s数据库示例

原创 GBASE数据库 2024-02-26
802

GBASE南大通用技术分享

这个例子将创建一个简单的 Java 项目结构，并演示如何使用 Mybatis 进行南大通用GBase 8s数据库的数据处理工作(插入，选择，更新和删除)，以及分页显示。

使用到的工具及组件包括：

Eclipse
JDK-1.8
Mybatis-3.4.5
GBase 8s 数据库及JDBC驱动2.0.1a2_2

最终的项目目录结构

项目结构图1.png

资源配置文件

mybatis-config.xml mybatis配置参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
<?xml version="1.0" encoding="UTF-8" ?> 
<!DOCTYPE configuration 
  PUBLIC "-//mybatis.org//DTD Config 3.0//EN" 
 
<!--
configuration（配置）
    properties（属性）
    settings（设置）
    typeAliases（类型别名）
    typeHandlers（类型处理器）
    objectFactory（对象工厂）
    plugins（插件）
    environments（环境配置）
        environment（环境变量）
        transactionManager（事务管理器）
        dataSource（数据源）
    databaseIdProvider（数据库厂商标识）
    mappers（映射器）
--> 
<configuration> 
    <!-- 数据库相关属性文件，这里不写的话，会自动加载 config.properties --> 
    <properties resource="db.properties"></properties> 
 
    <!-- 配置别名 --> 
    <typeAliases> 
        <typeAlias type="com.gbasedbt.mybatis.Student" alias="Student"/> 
    </typeAliases> 
     
    <!-- 配置以支持lvarchar -->
    <typeHandlers>
        <typeHandler handler="org.apache.ibatis.type.StringTypeHandler"
            jdbcType="LONGVARCHAR" javaType="String" />
    </typeHandlers>
       
    <!-- 环境配置 --> 
    <environments default="development"> 
        <environment id="development"> 
            <transactionManager type="JDBC"/> 
            <!-- 数据库连接相关配置 ,这里动态获取config.properties文件中的内容--> 
            <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> 
     
    <!-- 0.映射文件 ,我们还没有，这里什么都不写--> 
    <!-- 1.映射文件 --> 
    <mappers> 
        <mapper resource="com/gbasedbt/mybatis/StudentMapper.xml"/> 
    </mappers> 
     
</configuration>

db.properties 配置数据库连接参数

1
2
3
4
driver=com.gbasedbt.jdbc.IfxDriver
username=gbasedbt
password=GBase123

POJO类 Student.java

Student.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
package com.gbasedbt.mybatis;
 
/*
drop table if exists student;
create table student
(
  id serial not null,
  username varchar(60),
  usertext text,
  userphoto byte,
  primary key(id)
);
 */
 
public class Student {
     
    // 充号，自增长
    private int Id;
    // 用户名
    private String userName;
    // 简单信息
    private String userText;
    // 照片
    private byte[] userPhoto;
     
    public int getId() {
        return Id;
    }
    public void setId(int id) {
        Id = id;
    }
     
    public Student() {}
     
    public String getUserName() {
        return userName;
    }
    public void setUserName(String userName) {
        this.userName = userName;
    }
     
    public String getUserText() {
        return userText;
    }
    public void setUserText(String userText) {
        this.userText = userText;
    }
     
    public byte[] getUserPhoto() {
        return userPhoto;
    }
    public void setUserPhoto(byte[] userPhoto) {
        this.userPhoto = userPhoto;
    }
     
    public void setStudent(String userName, String userText, byte[] userPhoto) {
        this.userName = userName;
        this.userText = userText;
        this.userPhoto = userPhoto;
    }
     
    public void setStudent(String userName, String userText) {
        this.userName = userName;
        this.userText = userText;
    }
     
    @Override
    public String toString() {
        return "学生  [序号 = " + Id + "\t姓名 = " + userName + "\t简介= " + userText + "]";
    }
 
}

StudentMapper接口及StudentMapper.xml配置文件

StudentMapper.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
package com.gbasedbt.mybatis;
 
import java.util.ArrayList;
import org.apache.ibatis.annotations.Param;
 
public interface StudentMapper {
 
    public ArrayList <Student> listStudents();
     
    public ArrayList <Student> listStudentsBypage(@Param("pageNum") int pageNum, @Param("pageSize") int pageSize);
     
    public void addStudent(Student student);
    public void batchaddStudent(ArrayList <Student> students);
    public void foreachaddStudent(ArrayList <Student> students);
     
    public void updateStudent(@Param("id") int id, @Param("userText") String userText);
    public void deleteStudent(@Param("id") int id);
     
    public void createStudent();
 
}

StudentMapper.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
<?xml version="1.0" encoding="UTF-8" ?> 
<!DOCTYPE mapper 
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
 
<mapper namespace="com.gbasedbt.mybatis.StudentMapper">
 
    <!-- 插入单用户 -->
    <insert id="addStudent" parameterType="Student" useGeneratedKeys="true">
        insert into student(username,usertext,userphoto) values
        (#{userName},#{userText},#{userPhoto})
    </insert>
 
    <!-- 批量插入用户,不能使用text/byte/clob/blob字段 -->
    <insert id="batchaddStudent" useGeneratedKeys="true">
        insert into student(username)
        select * from (
        <foreach collection="list" item="item" separator=" union all ">
            SELECT
            '${item.userName}'
            FROM dual
        </foreach>
        )
    </insert>
 
    <!-- 批量插入用户, foreach 循环操作, 同样不能使用text/byte/clob/blob字段 -->
    <insert id="foreachaddStudent" useGeneratedKeys="true">
        <foreach collection="list" item="item">
            insert into student(username,usertext,userphoto) values
            (#{item.userName},#{item.userText},#{item.userPhoto});
        </foreach>
    </insert>
 
    <!-- 查询所有用户  -->
    <select id="listStudents" resultType="Student">
        select * from student
    </select>
     
    <!-- 分页查询所有用户  -->
    <select id="listStudentsBypage" resultType="Student">
        select skip #{pageNum} first #{pageSize} * from student
    </select>
     
    <!-- 删除用户  -->
    <delete id="deleteStudent">
        delete from student where id = #{id}
    </delete>
     
    <!-- 更新用户 -->
    <update id="updateStudent">
        update student set usertext = #{userText} where id = #{id}
    </update>
     
    <!-- 创建表 -->
    <update id="createStudent">
        drop table if exists student;
        create table student (id serial not null, username varchar(60), usertext text, userphoto byte, primary key(id));
    </update>
 
</mapper>

操作与测试类

StudentTest.java 实现建表、CRUD及分页显示

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
package com.gbasedbt.mybatis;
 
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
 
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
 
public class StudentTest {
     
    private static SqlSessionFactory sf;
    private static SqlSession sqlsession;
    private static StudentMapper studentMapper;
    static String resource = "mybatis-config.xml";
 
    public static void main(String[] args) throws Exception {
         
        InputStream inputStream = Resources.getResourceAsStream(resource); 
        sf = new SqlSessionFactoryBuilder().build(inputStream);    
        sqlsession = sf.openSession();
        studentMapper = sqlsession.getMapper(StudentMapper.class);
         
        // 创建表
        doCreateStudent();
         
        // 增加用户
        for (int i=1; i<=10; i++) {
            doAddStudent("test00" + i, "user text 00" + i, ("user photo 00" + i).getBytes());
        }
         
        // 分页显示
        doListStudentsBypage(2,5);
         
        // 更新用户
        doUpdateStudent(8, "sdfasdfasdlfalsdflasdf");
         
        // 删除用户
        doDeleteStudent(9);
        doListStudentsBypage(2,5);
         
        // 批量增加用户
        ArrayList <Student> students = new ArrayList<Student>();
        Student stu1 = new Student();
        stu1.setStudent("batch 001", "text001");
        Student stu2 = new Student();
        stu2.setStudent("batch 002", "text001");
        Student stu3 = new Student();
        stu3.setStudent("batch 003", "text001");
         
        students.add(stu1);
        students.add(stu2);
        students.add(stu3);
         
        doBatchaddStudent(students);
         
        // 显示所有用户
        doListStudents();
         
        sqlsession.close();
    }
     
    /**
     * 执行增加单用户
     * @param username
     * @param usertext
     * @param userphoto
     */
    public static void doAddStudent(String username, String usertext, byte[] userphoto) {
        Student student = new Student();
        student.setUserName(username);
        student.setUserText(usertext);
        student.setUserPhoto(userphoto);
         
        studentMapper.addStudent(student);
        sqlsession.commit();
    }
     
    /**
     * 执行批量增加用户
     * @param students
     */
    public static void doBatchaddStudent(ArrayList <Student> students) {
        studentMapper.batchaddStudent(students);
        sqlsession.commit();
    }
     
    /**
     * 执行分页显示用户
     * @param pagenum
     * @param pagesize
     */
    public static void doListStudentsBypage(int pagenum, int pagesize) {
        int skiprows = 0;
        if (pagesize > 0) {
            skiprows = (pagenum - 1) * pagesize;
        }
        System.out.println("从第 " + (skiprows + 1) + " 行开始，显示 " + pagesize + " 行");
        List <Student> students = studentMapper.listStudentsBypage(skiprows, pagesize);
        for (Student stu : students) {
            System.out.println(stu.toString());
        }
    }
     
    /**
     * 执行显示所有用户
     */
    public static void doListStudents() {
        System.out.println("显示所有用户");
        List <Student> students = studentMapper.listStudents();
        for (Student stu : students) {
            System.out.println(stu.toString());
        }
    }
     
    /**
     * 删除指定用户
     * @param id
     */
    public static void doDeleteStudent(int id) {
        studentMapper.deleteStudent(id);
        sqlsession.commit();
    }
     
    /**
     * 修改指定用户
     * @param id
     * @param usertext
     */
    public static void doUpdateStudent(int id, String usertext) {
        studentMapper.updateStudent(id, usertext);
        sqlsession.commit();
    }
     
    /**
     * 创建student表
     */
    public static void doCreateStudent() {
        studentMapper.createStudent();
        sqlsession.commit();
    }
 
}

源文件代码：Mybatis3-Demo.zip

标签: crud, mybatis

gbase
最后修改时间：2024-02-26 17:28:29
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论