配置pom.xml依赖
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>test-sql</groupId>
<artifactId>test-sql</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.9</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
</dependencies>
</project>
在resource文件夹下面创建mybatis-config.xml
<?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>
<environments default="development">
<environment id="development">
<!-- 使用JDBC事务管理器,目前由MyBatis管理 -->
<transactionManager type="JDBC"/>
<!--dataSource 指连接源配置,POOLED是JDBC连接对象的数据源连接池的实现-->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=false&useUnicode=true&characterEncoding=UTF-8"/>
<property name="username" value="root"/>
<property name="password" value="001122"/>
</dataSource>
</environment>
</environments>
<mappers>
<!--这是告诉Mybatis区哪找持久化类的映射文件(xml映射文件)-->
<mapper resource="mapper/User.xml"/>
</mappers>
</configuration>
为了能在控制台看到输出(如上所示),需要配置log4j.properties文件,和mybatis.xml配置文件同级
log4j.rootLogger=DEBUG, stdout
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p %d %C: %m%n
log4j.logger.java.sql.ResultSet=INFO
log4j.logger.org.apache=INFO
log4j.logger.java.sql.Connection=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG
数据库查询语句写在resource/mapper下的.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">
<!--namespace:用来隔离sql-->
<mapper namespace="mapper.User">
<!--resultType指定返回数据的实体类型,parameterType指定输入数据类型-->
<select id="searchById" parameterType="int" resultType="com.neuedu.pojo.User">
select * from User where id = #{id}
</select>
<!-- concat用来拼接,%为通配符,这种方式可以防止sql注入-->
<select id="findUserByName" resultType="com.neuedu.pojo.User" parameterType="string">
select * from User where username like concat('%',#{info},'%');
</select>
<!-- insert 插入数据 只有parameterType输入参数
useGeneratedKeys属性指定开启自动填充功能
keyProperty属性用于指定生成的主键值填充到类的哪个属性上 -->
<insert id="insertUser" parameterType="com.neuedu.pojo.User" useGeneratedKeys="true" keyProperty="id">
insert into User values(null, #{username}, #{birthday}, #{sex}, #{address})
</insert>
<!-- update 同样只有输入参数parameterType-->
<update id="updateUser" parameterType="com.neuedu.pojo.User">
update User set username = #{username}, sex = #{sex}, birthday = #{birthday}, address = #{address}
where id = #{id}
</update>
<!-- delete 也是输入参数 -->
<delete id="deleteUser" parameterType="string">
delete from User where username = #{username}
</delete>
</mapper>
我们创建一个工具类,可以直接调用获取SqlSession
package com.neuedu.util;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
public class MyBatisUtil {
private static SqlSessionFactory sessionFactory;
static {
try {
//加载配置文件
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
//创建sessionFactory对象
sessionFactory = new SqlSessionFactoryBuilder().build(is);
} catch (IOException e) {
e.printStackTrace();
}
}
//获取session对象
public static SqlSession openSession() {
return sessionFactory.openSession();
}
}
我们在test/java目录下创建sessionTest,进行单元测试
package com.neuedu.test;
import com.neuedu.pojo.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class sessionTest {
private SqlSession session;
@Before
public void init() throws IOException {
//通过Resources类加载和读取配置文件
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
//根据配置文件的信息创建SqlSessionFactory对象
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);
//通过SqlSessionFactory获取SqlSession对象
session = sessionFactory.openSession();
}
//带一个参数
@Test
public void findUserById(){
User u = session.selectOne("mapper.User.searchById", 1);
System.out.println("findUserById" + u.toString());
}
//带一个参数
@Test
public void findUserByNameTest(){
List<User> userList = session.selectList("mapper.User.findUserByName", "哈");
System.out.println("findUserByNameTest" + userList.get(0).toString());
}
//插入数据
@Test
public void testInsertUser(){
try {
User user = new User(null, "嘿嘿嘿", "男", "2022-12-12", "东北大学");
int rows = session.insert("mapper.User.insertUser", user);
System.out.println(rows);
session.commit();
} catch (Exception e) {
session.rollback();
}
}
//修改数据
@Test
public void testUpdateUser(){
try {
User user = session.selectOne("mapper.User.searchById", 1);
user.setAddress("西北大学");
int rows = session.update("mapper.User.updateUser", user);
System.out.println("testUpdateUser"+rows);
session.commit();
} catch (Exception e) {
session.rollback();
System.out.println("fails");
}
}
//删除数据
@Test
public void testDeleteUser() {
try {
int rows = session.delete("mapper.User.deleteUser", "嘿嘿嘿");
System.out.println(rows);
session.commit();
} catch (Exception e) {
session.rollback();
e.printStackTrace();
}
}
// @Test
// public void testSession(){
// //输出session
// System.out.println(session);
// //输出session对应的Connection的连接
// System.out.println(session.getConnection());
// }
@After
public void destory(){
//对连接进行关闭
session.close();
}
}
也可以新建一个test测试类,在测试类中写主函数,代码如下
//获取SqlSessionFactory对象
String resource = "mybatis-config.xml";
try{
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = builder.build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
Object one = sqlSession.selectOne("searchById",1);
System.out.println(one);
} catch (IOException e) {
e.printStackTrace();
}