全局配置
属性(properties)
既可以在典型的 Java 属性文件中配置这些属性,也可以在 properties 元素的子元素中设置。
resources–>创建config.properties
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/web_db?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
username=root
password=root
mybaties.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><!--配置文件-->
<!--mybatis核心配置文件-->
<!--导入属性文件-->
<properties resource="config.properties"></properties>
<!--配置与数据库的连接信息-->
<environments default="development">
<environment id="development">
<!--配置数据事物管理方式-->
<transactionManager type="JDBC"/>
<!--数据源配置 type="POOLED" 使用数据库连接池功能-->
<dataSource type="POOLED">
<!--数据库连接-->
<property name="driver" value="${driverClassName}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<!--sql映射-->
<mappers>
<mapper resource="mapper/GradeMapper.xml"></mapper>
<mapper resource="mapper/UserMapper.xml"></mapper>
</mappers>
</configuration>
类型别名(typeAliases)
在mybatis.xml中添加
<!--配置类型别名-->
<typeAliases>
<typeAlias type="mybatisPro.model.User" alias="User"></typeAlias>
</typeAliases>
在mapper的UserMapper.xml中改变
<select id="findUserById" parameterType="int" resultType="User">
select id,password,account from user where id =#{id}
</select>
**注:**配置顺序不能变
设置(settings)
IogImpl(日志组件)
<!--全局设置-->
<settings>
<!--日志组件配置-->
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
SQL映射
单张表
查询
数据库user表中的user_gender和User中的userGender名称不同时,如何映射成功?
在mybatis.xml中的setting配置
<!--全局设置-->
<settings>
<!--日志组件配置-->
<setting name="logImpl" value="STDOUT_LOGGING"/>
<!--从数据库命名 user_gender 自动映射到Java userGende 驼峰-->
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
改变UserMapper.xml中的sql语句
<select id="findUserById" parameterType="int" resultType="User">
select id,password,account,user_gender
from user
where id =#{id}
</select>
数据库user表中的user_gender和User中的gender名称不同时,如何映射成功?
改变UserMapper.xml中的sql语句 别名
<select id="findUserById" parameterType="int" resultType="User">
select id,password,account,user_gender gender
from user
where id =#{id}
</select>
选择一张表的全部数据
UserDao改变为
public interface UserDao {
/*
* 定义了一个功能
* */
User findUserById(int id);
List<User> findUsers();
}
UserMapper改变为
<select id="findUsers" resultType="User">
select id,password,account,user_gender gender
from user
</select>
test2改变为
package mybatisPro.test;
import mybatisPro.Dao.UserDao;
import mybatisPro.model.User;
import mybatisPro.util.mybatisUtil;
import org.apache.ibatis.session.SqlSession;
import java.io.IOException;
import java.util.List;
public class test2 {
public static void main(String[] args) throws IOException {
SqlSession sqlSession = mybatisUtil.getSqlSession();
/*生成接口的代理对象,由代理对象去实际调用接口中与映射文件中对应的sql*/
UserDao userDao = sqlSession.getMapper(UserDao.class);
List<User> users = userDao.findUsers();
System.out.println(users);
sqlSession.close();
}
}
新增
新增user
mapper–>UserMapper.xml
<!-- <select id="insertUser">
insert into user(account,password)value(#{account},#{password})
</select>-->
<!--#{} 可以获取到对象中的数据,是以预编译(?)的方式插入到sql中,安全的,防止sql注入
${} 是以字符串形式将数据拼接到sql中的,不能防止sql注入,用于向sql中传入列名时使用
-->
<select id="insertUser" parameterType="User">
insert into user(account,password)value(#{account},#{password})
/* insert into user(account,password)value('${account}','${password}')*/
</select>
Dao–>UserDao
//void insertUser(@Param("account") String account,@Param("password") String password);
void insertUser(User user);
test2
package mybatisPro.test;
import mybatisPro.Dao.UserDao;
import mybatisPro.model.User;
import mybatisPro.util.mybatisUtil;
import org.apache.ibatis.session.SqlSession;
import java.io.IOException;
import java.util.List;
public class test2 {
public static void main(String[] args) throws IOException {
SqlSession sqlSession = mybatisUtil.getSqlSession();
/*生成接口的代理对象,由代理对象去实际调用接口中与映射文件中对应的sql*/
UserDao userDao = sqlSession.getMapper(UserDao.class);
User user = new User();
user.setAccount("张三");
user.setPassword("111");
userDao.insertUser(user);
//userDao.insertUser("张三","111");
sqlSession.commit();//提交事务 原生的jdbc是自动提交事务,提交后表示本次操作结束,数据库执行sql
sqlSession.close();
}
}
插入时,可以将保存到数据库中数据的自增主键存储到对象中
UserMapper.xml中添加
<insert id="insertUser" parameterType="User" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
insert into user(account,password)value(#{account},#{password})
</insert>
test2中添加
package mybatisPro.test;
import mybatisPro.Dao.UserDao;
import mybatisPro.model.User;
import mybatisPro.util.mybatisUtil;
import org.apache.ibatis.session.SqlSession;
import java.io.IOException;
import java.util.List;
public class test2 {
public static void main(String[] args) throws IOException {
SqlSession sqlSession = mybatisUtil.getSqlSession();
/*生成接口的代理对象,由代理对象去实际调用接口中与映射文件中对应的sql*/
UserDao userDao = sqlSession.getMapper(UserDao.class);
User user = new User();
user.setAccount("张三");
user.setPassword("111");
userDao.insertUser(user);
System.out.println(user.getId());
sqlSession.commit();//提交事务 原生的jdbc是自动提交事务,提交后表示本次操作结束,数据库执行sql
sqlSession.close();
}
}
修改
修改user表中id=6的张三 111为李四 222
UserDao
package mybatisPro.Dao;
import mybatisPro.model.User;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface UserDao {
/*
* 定义了一个功能
* */
User findUserById(int id);
List<User> findUsers();
//void insertUser(@Param("account") String account,@Param("password") String password);
void insertUser(User user);
void updateUser(User user);
}
UserMapper.xml
<update id="updateUser" parameterType="User">
update user set account=#{account},password=#{password}
where id=#{id}
</update>
test2
package mybatisPro.test;
import mybatisPro.Dao.UserDao;
import mybatisPro.model.User;
import mybatisPro.util.mybatisUtil;
import org.apache.ibatis.session.SqlSession;
import java.io.IOException;
import java.util.List;
public class test2 {
public static void main(String[] args) throws IOException {
SqlSession sqlSession = mybatisUtil.getSqlSession();
/*生成接口的代理对象,由代理对象去实际调用接口中与映射文件中对应的sql*/
UserDao userDao = sqlSession.getMapper(UserDao.class);
User user = new User();
user.setId(6);
user.setAccount("李四");
user.setPassword("222");
userDao.updateUser(user);
sqlSession.commit();//提交事务 原生的jdbc是自动提交事务,提交后表示本次操作结束,数据库执行sql
sqlSession.close();
}
}
删除
UserDao
int deleteUser(int id);
UserMapper.xml
<delete id="deleteUser" parameterType="int">
delete from user where id=#{id}
</delete>
test2
package mybatisPro.test;
import mybatisPro.Dao.UserDao;
import mybatisPro.model.User;
import mybatisPro.util.mybatisUtil;
import org.apache.ibatis.session.SqlSession;
import java.io.IOException;
import java.util.List;
public class test2 {
public static void main(String[] args) throws IOException {
SqlSession sqlSession = mybatisUtil.getSqlSession();
/*生成接口的代理对象,由代理对象去实际调用接口中与映射文件中对应的sql*/
UserDao userDao = sqlSession.getMapper(UserDao.class);
int count = userDao.deleteUser(6);
System.out.println(count);
sqlSession.commit();//提交事务 原生的jdbc是自动提交事务,提交后表示本次操作结束,数据库执行sql
sqlSession.close();
}
}
结果集
检查user表中是否有张三
UserDao
int checkAccount(@Param("account")String account);
UserMapper.xml
<select id="checkAccount" resultType="integer">
select count(*) from user where account = #{account}
</select>
test2
package mybatisPro.test;
import mybatisPro.Dao.UserDao;
import mybatisPro.model.User;
import mybatisPro.util.mybatisUtil;
import org.apache.ibatis.session.SqlSession;
import java.io.IOException;
import java.util.List;
public class test2 {
public static void main(String[] args) throws IOException {
SqlSession sqlSession = mybatisUtil.getSqlSession();
/*生成接口的代理对象,由代理对象去实际调用接口中与映射文件中对应的sql*/
UserDao userDao = sqlSession.getMapper(UserDao.class);
int count = userDao.checkAccount("张三");
System.out.println(count);
sqlSession.commit();//提交事务 原生的jdbc是自动提交事务,提交后表示本次操作结束,数据库执行sql
sqlSession.close();
}
}
对象映射
如果表中的类名与类中的属性名完全相同,mybatis会自动将查询结果封装到POJO(简单的Java对象)对象中.
如果java中使用标准驼峰命名,数据库中使用下划线连接命名,可以开始全局设置实现自动转换
mybatis.xml
<settings>
<!--从数据库命名 user_gender 自动映射到Java userGende-->
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
特殊处理定义 resultMap
<resultMap id="studentmap" type="Student">
<id property="id" column="id"></id>
<result property="number" column="no"></result>
<result property="name" column="name"></result>
<result property="gender" column="gender"></result>
<result property="phone" column="phone"></result>
<result property="address" column="address"></result>
<result property="regTime" column="reg_time"></result>
<!--将关联表中的数据查询封装到关联的对象中-->
<association property="grade" javaType="Grade">
<result property="name" column="gname"></result>
</association>
<association property="user" javaType="User">
<result property="account" column="account"></result>
</association>
</resultMap>
- resutlMap 的 id 属性是 resutlMap 的唯一标识
- resutlMap 的 id 属性是映射的 POJO 类(映射到Student类的id)
- id 标签映射主键,result 标签映射非主键
- property 设置 POJO(Student类) 的属性名称,column 映射查询结果的列名称
多表关联
查询student表和grade表两张表
创建Student表
package mybatisPro.model;
public class Student {
private int id;
private int number;
private String name;
private String gender;
private Grade grade ;//类与类的关联关系 has-a关系
private String phone;
private String address;
private String regTime;
private User user;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getNumber() {
return number;
}
public void setNumber(int number) {
this.number = number;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public Grade getGrade() {
return grade;
}
public void setGrade(Grade grade) {
this.grade = grade;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getRegTime() {
return regTime;
}
public void setRegTime(String regTime) {
this.regTime = regTime;
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", number=" + number +
", name='" + name + '\'' +
", gender='" + gender + '\'' +
", grade=" + grade +
", phone='" + phone + '\'' +
", address='" + address + '\'' +
", regTime='" + regTime + '\'' +
", user=" + user +
'}';
}
}
创建StudentDao
package mybatisPro.Dao;
import mybatisPro.model.Student;
import mybatisPro.model.User;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface StudentDao {
List<Student> findStudents();
}
创建StudentMapper.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">
<!--sql映射文件 namespace唯一标识 namespace名字必须与接口的类路径一致-->
<mapper namespace="mybatisPro.Dao.StudentDao">
<resultMap id="studentmap" type="Student">
<id property="id" column="id"></id>
<result property="number" column="no"></result>
<result property="name" column="name"></result>
<result property="gender" column="gender"></result>
<result property="phone" column="phone"></result>
<result property="address" column="address"></result>
<result property="regTime" column="reg_time"></result>
<!--将关联表中的数据查询封装到关联的对象中-->
<association property="grade" javaType="Grade">
<result property="name" column="gname"></result>
</association>
<association property="user" javaType="User">
<result property="account" column="account"></result>
</association>
</resultMap>
<select id="findStudents" resultMap="studentmap">
SELECT s.id,
s.no,
s.name,
s.gender,
g.name gname,
s.phone,
s.address,
s.reg_time,
u.account
FROM student s LEFT JOIN grade g ON s.gradeid = g.id
LEFT JOIN USER u ON s.userid = u.id
</select>
</mapper>
配置mybatis.xml
<!--sql映射-->
<mappers>
<mapper resource="mapper/GradeMapper.xml"></mapper>
<mapper resource="mapper/UserMapper.xml"></mapper>
<mapper resource="mapper/StudentMapper.xml"></mapper>
</mappers>
在mybatis.xml中配置mybatisPro.model.Grade和mybatisPro.model.Student的别名
<!--配置类型别名-->
<typeAliases>
<typeAlias type="mybatisPro.model.User" alias="User"></typeAlias>
<typeAlias type="mybatisPro.model.Grade" alias="Grade"></typeAlias>
<typeAlias type="mybatisPro.model.Student" alias="Student"></typeAlias>
</typeAliases>
创建TestStudent
package mybatisPro.test;
import mybatisPro.Dao.StudentDao;
import mybatisPro.model.Student;
import mybatisPro.util.mybatisUtil;
import org.apache.ibatis.binding.MapperMethod;
import org.apache.ibatis.session.SqlSession;
import java.util.List;
public class TestStudent {
public static void main(String[] args) {
SqlSession sqlSession = mybatisUtil.getSqlSession();
//生产接口的代理对象
StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
List<Student> students = studentDao.findStudents();
for(Student student:students)//增强for
{
System.out.println(student.getId());
System.out.println(student.getNumber());
System.out.println(student.getName());
System.out.println(student.getGender());
System.out.println(student.getGrade().getName());//获得年级
System.out.println(student.getPhone());
System.out.println(student.getAddress());
System.out.println(student.getRegTime());
System.out.println(student.getUser().getAccount());//获得注册人
}
}
}