真理并无尽头

注解增删改查

HeJin大约 6 分钟数据库技术Mybatis

1、自动提交事务

我们可以在工具类创建的时候实现自动提交事务！

/**
 * 获取SqlSession
 * @return
 */
public static SqlSession getSqlSession(){
    return sqlSessionFactory.openSession();
}

openSession默认未实现事务。

@Override
public SqlSession openSession() {
    return openSessionFromDataSource(configuration.getDefaultExecutorType(), null, false);
}

修改工具类代码

/**
 * 获取SqlSession
 * 实现事务
 * @return
 */
public static SqlSession getSqlSession(){
    return sqlSessionFactory.openSession(true);
}

2、增删改查

条件查询

方法带有多个参数，除了使用Map封装以外，还可以使用注解@Param("value")。查询的时候mybatis以注解指定的为准。

/**
 * 根据条件查询
 * @param id
 * @param name
 * @return
 */
@Select("select * from user where id = #{id} and name = #{name}")
User getUserById(@Param("id") int id, @Param("name") String name);

测试

@Test
public void test01(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    User user = userMapper.getUserById(1, "狂神");
    System.out.println(user);

    sqlSession.close();
}

结果正常。

插入数据

UserMapper.java文件修改

public interface UserMapper {

    /**
     * 注解查询
     * @return
     */
    @Select("select * from user")
    List<User> getUserList();

    /**
     * 根据条件查询
     * @param id
     * @param name
     * @return
     */
    @Select("select * from user where id = #{id} and name = #{name}")
    User getUserById(@Param("id") int id, @Param("name") String name);

    /**
     * 插入数据
     * @param user
     * @return
     */
    @Insert("insert into user(id,name,pwd) values (#{id},#{name},#{password})")
    int addUser(User user);

}

测试

@Test
public void test02(){

    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    userMapper.addUser(new User(1010, "哈哈哈", "101010"));

    sqlSession.close();

}

结果

Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
Class not found: org.jboss.vfs.VFS
JBoss 6 VFS API is not available in this environment.
Class not found: org.jboss.vfs.VirtualFile
VFS implementation org.apache.ibatis.io.JBoss6VFS is not valid in this environment.
Using VFS adapter org.apache.ibatis.io.DefaultVFS
Find JAR URL: file:/E:/code/Java/mybatis-study/mybatis-04/target/classes/com/kuang/pojo
Not a JAR: file:/E:/code/Java/mybatis-study/mybatis-04/target/classes/com/kuang/pojo
Reader entry: User.class
Listing file:/E:/code/Java/mybatis-study/mybatis-04/target/classes/com/kuang/pojo
Find JAR URL: file:/E:/code/Java/mybatis-study/mybatis-04/target/classes/com/kuang/pojo/User.class
Not a JAR: file:/E:/code/Java/mybatis-study/mybatis-04/target/classes/com/kuang/pojo/User.class
Reader entry: ����   4 <
Checking to see if class com.kuang.pojo.User matches criteria [is assignable to Object]
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 52908367.
==>  Preparing: insert into user(id,name,pwd) values (?,?,?) 
==> Parameters: 1010(Integer), 哈哈哈(String), 101010(String)
<==    Updates: 1
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@327514f]
Returned connection 52908367 to pool.

Process finished with exit code 0

修改记录

UserMapper.java文件修改

public interface UserMapper {

    /**
     * 注解查询
     * @return
     */
    @Select("select * from user")
    List<User> getUserList();

    /**
     * 根据条件查询
     * @param id
     * @param name
     * @return
     */
    @Select("select * from user where id = #{id} and name = #{name}")
    User getUserById(@Param("id") int id, @Param("name") String name);

    /**
     * 插入数据
     * @param user
     * @return
     */
    @Insert("insert into user(id,name,pwd) values (#{id},#{name},#{password})")
    int addUser(User user);

    /**
     * 修改用户
     * @param user
     * @return
     */
    @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
    int updateUser(User user);

}

测试

@Test
public void test03(){

    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    userMapper.updateUser(new User(1010,"000","000000"));

    sqlSession.close();

}

结果

Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
Class not found: org.jboss.vfs.VFS
JBoss 6 VFS API is not available in this environment.
Class not found: org.jboss.vfs.VirtualFile
VFS implementation org.apache.ibatis.io.JBoss6VFS is not valid in this environment.
Using VFS adapter org.apache.ibatis.io.DefaultVFS
Find JAR URL: file:/E:/code/Java/mybatis-study/mybatis-04/target/classes/com/kuang/pojo
Not a JAR: file:/E:/code/Java/mybatis-study/mybatis-04/target/classes/com/kuang/pojo
Reader entry: User.class
Listing file:/E:/code/Java/mybatis-study/mybatis-04/target/classes/com/kuang/pojo
Find JAR URL: file:/E:/code/Java/mybatis-study/mybatis-04/target/classes/com/kuang/pojo/User.class
Not a JAR: file:/E:/code/Java/mybatis-study/mybatis-04/target/classes/com/kuang/pojo/User.class
Reader entry: ����   4 <
Checking to see if class com.kuang.pojo.User matches criteria [is assignable to Object]
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 1527953000.
==>  Preparing: update user set name=?,pwd=? where id = ? 
==> Parameters: 000(String), 000000(String), 1010(Integer)
<==    Updates: 1
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@5b12b668]
Returned connection 1527953000 to pool.

Process finished with exit code 0

删除记录

修改UserMapper.java文件

public interface UserMapper {

    /**
     * 注解查询
     * @return
     */
    @Select("select * from user")
    List<User> getUserList();

    /**
     * 根据条件查询
     * @param id
     * @param name
     * @return
     */
    @Select("select * from user where id = #{id} and name = #{name}")
    User getUserById(@Param("id") int id, @Param("name") String name);

    /**
     * 插入数据
     * @param user
     * @return
     */
    @Insert("insert into user(id,name,pwd) values (#{id},#{name},#{password})")
    int addUser(User user);

    /**
     * 修改用户
     * @param user
     * @return
     */
    @Update("update user set name=#{name},pwd=#{password} where id = #{id}")
    int updateUser(User user);

    /**
     * 删除记录
     * @param id
     * @return
     */
    @Delete("delete from user where id = #{id}")
    int deleteUser(@Param("id") int id);

}

测试

@Test
public void test04(){

    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    int i = userMapper.deleteUser(1010);
    System.out.println(i);


    sqlSession.close();

}

结果

Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
Class not found: org.jboss.vfs.VFS
JBoss 6 VFS API is not available in this environment.
Class not found: org.jboss.vfs.VirtualFile
VFS implementation org.apache.ibatis.io.JBoss6VFS is not valid in this environment.
Using VFS adapter org.apache.ibatis.io.DefaultVFS
Find JAR URL: file:/E:/code/Java/mybatis-study/mybatis-04/target/classes/com/kuang/pojo
Not a JAR: file:/E:/code/Java/mybatis-study/mybatis-04/target/classes/com/kuang/pojo
Reader entry: User.class
Listing file:/E:/code/Java/mybatis-study/mybatis-04/target/classes/com/kuang/pojo
Find JAR URL: file:/E:/code/Java/mybatis-study/mybatis-04/target/classes/com/kuang/pojo/User.class
Not a JAR: file:/E:/code/Java/mybatis-study/mybatis-04/target/classes/com/kuang/pojo/User.class
Reader entry: ����   4 <
Checking to see if class com.kuang.pojo.User matches criteria [is assignable to Object]
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 18242360.
==>  Preparing: delete from user where id = ? 
==> Parameters: 1010(Integer)
<==    Updates: 1
1
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@1165b38]
Returned connection 18242360 to pool.

Process finished with exit code 0

注意点

接口必须注册绑定到mybatis核心配置文件。

关于@Param注解

基本类型的参数或者String类型，需要加上。

引用类型不需要加。

如果只有一个基本类型的话，可以忽略，但是建议都写上！

在SQL中引用的就是这里@Param("value")中设定的属性名。

mybatis中#{} 和 ${} 区别

#{}是预编译处理，${}是字符串替换。Mybatis在处理#{}时，会将sql中的#{}替换为?号，调用PreparedStatement的set方法来赋值。Mybatis在处理，就是把$ {} 替换成变量的值。使用#{}可以有效的防止SQL注入，提高系统安全性。

使用#{}处理成？

Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
Class not found: org.jboss.vfs.VFS
JBoss 6 VFS API is not available in this environment.
Class not found: org.jboss.vfs.VirtualFile
VFS implementation org.apache.ibatis.io.JBoss6VFS is not valid in this environment.
Using VFS adapter org.apache.ibatis.io.DefaultVFS
Find JAR URL: file:/E:/code/Java/mybatis-study/mybatis-04/target/classes/com/kuang/pojo
Not a JAR: file:/E:/code/Java/mybatis-study/mybatis-04/target/classes/com/kuang/pojo
Reader entry: User.class
Listing file:/E:/code/Java/mybatis-study/mybatis-04/target/classes/com/kuang/pojo
Find JAR URL: file:/E:/code/Java/mybatis-study/mybatis-04/target/classes/com/kuang/pojo/User.class
Not a JAR: file:/E:/code/Java/mybatis-study/mybatis-04/target/classes/com/kuang/pojo/User.class
Reader entry: ����   4 <
Checking to see if class com.kuang.pojo.User matches criteria [is assignable to Object]
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 1153447573.
==>  Preparing: select * from user where id = ? and name = ? 
==> Parameters: 1(Integer), 狂神(String)
<==    Columns: id, name, pwd
<==        Row: 1, 狂神, 123456
<==      Total: 1
User{id=1, name='狂神', password='null'}
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@44c03695]
Returned connection 1153447573 to pool.

Process finished with exit code 0

使用${}处理成变量值报错

Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
Class not found: org.jboss.vfs.VFS
JBoss 6 VFS API is not available in this environment.
Class not found: org.jboss.vfs.VirtualFile
VFS implementation org.apache.ibatis.io.JBoss6VFS is not valid in this environment.
Using VFS adapter org.apache.ibatis.io.DefaultVFS
Find JAR URL: file:/E:/code/Java/mybatis-study/mybatis-04/target/classes/com/kuang/pojo
Not a JAR: file:/E:/code/Java/mybatis-study/mybatis-04/target/classes/com/kuang/pojo
Reader entry: User.class
Listing file:/E:/code/Java/mybatis-study/mybatis-04/target/classes/com/kuang/pojo
Find JAR URL: file:/E:/code/Java/mybatis-study/mybatis-04/target/classes/com/kuang/pojo/User.class
Not a JAR: file:/E:/code/Java/mybatis-study/mybatis-04/target/classes/com/kuang/pojo/User.class
Reader entry: ����   4 <
Checking to see if class com.kuang.pojo.User matches criteria [is assignable to Object]
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 940584193.
==>  Preparing: select * from user where id = 1 and name = 狂神 
==> Parameters: 

org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: Unknown column '狂神' in 'where clause'
Copyright © 2023 HeJin