insert into student(username)<br>		select * from (<br>		<br>			SELECT<br>			'${item.userName}'<br>			FROM dual<br>		<br>		) 我看这里提供的这个'${item.userName}' 会不会出现sql注入的情况？

活动专区

活动公告

上车！GBase 8a开发者联盟发车了 技术福利、人脉资源全都有！

认证培训专区

认证培训资料

GBase 8s GDCA 认证培训课前准备

GBase 8a

其他

物化视图报错：references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

01-GBase 8s 事务型数据库 安装与卸载

GBase 8s

适配迁移

[GBase 8s 教程]GBase 8s 常用函数、表达式

这个例子将创建一个简单的 Java 项目结构，并演示如何使用 Mybatis 进行GBase 8s数据库的数据处理工作(插入，选择，更新和删除)，以及分页显示。
### 使用到的工具及组件包括：
> Eclipse
> JDK-1.8
> Mybatis-3.4.5
> GBase 8s 数据库及JDBC驱动2.0.1a2_2
### 最终的项目目录结构
![项目结构图1.png][1]



### 资源配置文件
*** mybatis-config.xml mybatis配置参数 ***
```
<?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>  
      
    <properties resource="db.properties"></properties>  

	  
    <typeAliases>  
        <typeAlias type="com.gbasedbt.mybatis.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="com/gbasedbt/mybatis/StudentMapper.xml"/>  
    </mappers>  
    
</configuration>
```
*** db.properties 配置数据库连接参数 ***
```text
driver=com.gbasedbt.jdbc.IfxDriver
url=jdbc:gbasedbt-sqli://192.168.1.71:9088/mybatis:GBASEDBTSERVER=gbase01;DB_LOCALE=zh_CN.utf8;CLIENT_LOCALE=zh_CN.utf8;IFX_LOCK_MODE_WAIT=10
username=gbasedbt
password=GBase123
```
### POJO类 Student.java
*** Student.java ***
```java
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 ***
```java
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 ***
```xml
<?xml version="1.0" encoding="UTF-8" ?>  
<!DOCTYPE mapper  
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"  
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.gbasedbt.mybatis.StudentMapper">

	
	<insert id="addStudent" parameterType="Student" useGeneratedKeys="true">
		insert into student(username,usertext,userphoto) values
		(#{userName},#{userText},#{userPhoto})
	</insert>

	
	<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>

	
	<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及分页显示 ***
```java
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][2]


  [1]: https://gbasedbt.com/usr/uploads/2020/03/4136547178.png
  [2]: https://gbasedbt.com/download/mybatis/mybatis-demo/

Mybatis操作GBase 8s数据库示例

Mybatis操作GBase 8s数据库示例

评论

热门帖子