链接数据库mysql50:进行mysql50的增删查改
1.首先创建Mybatis，配置相关的环境
2.进入界面，打开pom,xml(mysql50mybatis),导入相关的代码块
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.29</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.2.2</version>
</dependency>
3.新建resources(设置成resources root),将原有的databases.properties和mybatis-config.xml包带入resources包中
4.将databases中的url修改为
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://192.168.153.149:3306/mysql50
user=root
password=root
5.在resours中创建ni.zb.mysql文件夹，在src-main-nj.zb.kb22中创造dao（创建mysql数据库中四张表的接口）和pojo（创建mysql数据库中的4张表）的包 ;在test文件中创建nj.zb.kb22(四张表测试)
6.在resources中创建四个相关的xml表
7.在mybatis中创建四个相关的mapper resource
<mappers>
<mapper resource=”nj/zb/mysql/StudentDao.xml”/>
<mapper resource=”nj/zb/mysql/TeacherDao.xml”/>
<mapper resource=”nj/zb/mysql/ScoreDao.xml”/>
<mapper resource=”nj/zb/mysql/CourseDao.xml”/>
</mappers>
8.进行StudentTest的增删查改
9.在IStudentDao接口中创建四个增删改查的命令：
public List<Student> getAllStudent();
public Integer save(Student student);
public Integer update(Student student);
public Integer delete(Integer id );
10.在StudentDao.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=”nj.zb.kb22.dao.IStudentDao”>
<select id=”getAllStudent” resultType=”Student”>
select s_id, s_name, s_birth, s_sex from Student
</select>
<insert id=”save”>
insert into student(s_id,s_name,s_birth,s_sex)
values (#{s_id},#{s_name},now(),#{s_sex})
</insert>
<update id=”update”>
update student set s_name=#{s_name} where s_id=#{s_id}
</update>
<delete id=”delete”>
delete from student where s_id=#{s_id}
</delete>
</mapper>
11.在test中输出：public class StudentTest {
SqlSessionFactory sqlSessionFactory = null;
SqlSession sqlSession = null;
IStudentDao studentDao = null;
@Before
public void setUP() {
System.out.println(“hello before”);
InputStream inputStream = StudentTest.class.getClassLoader()
.getResourceAsStream(“mybatis-config.xml”);
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
//获取构建工厂
sqlSessionFactory = builder.build(inputStream);
//打开会话
sqlSession = sqlSessionFactory.openSession();
studentDao = sqlSession.getMapper(IStudentDao.class);
}
@Test
public void testA(){
List<Student> studentList=studentDao.getAllStudent();
for (Student student:
studentList) {
System.out.println(student);
}
}
输出结果：输出Student中所有的列表;
@Test
public void testStudentSave(){
Student student = new Student();
student.setS_id(9);
student.setS_name(“大狗”);
student.setS_sex(“男”);
Integer save = studentDao.save(student);
System.out.println(save);
}
输出结果：增加一个id为9的，name为大狗，sex为男的student用户;
@Test
public void testStudentUpdate(){
Student student = new Student();
student.setS_id(9);
student.setS_name(“zgx”);
Integer num = studentDao.update(student);
System.out.println(num);
}
输出结果:将id号为9的名字改为zgx的，并刷新数据，结果为id为9的用户name为zgx;
@Test
public void testStudentDelete(){
System.out.println(studentDao.delete(9));
}
输出结果：在datagrip中id为9的数据删除，没有id为9的用户
@After
public void tearDown () {
System.out.println(“hello after”);
sqlSession.commit();
sqlSession.close();
System.out.println(“game over”);
}
}
一对一使用Mybatis查询数据
1.首先在原有的的IMasterDao中创建public Master getMasterAndDog(String masterName);
2.在MasterDao.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=”nj.zb.kb22.dao.IMasterDao”>
<resultMap id=”masterMap” type=”Master”>
<id column=”id” property=”id”></id>
<result column=”mastername” property=”masterName”></result>
<result column=”birthday” property=”birthday”></result>
<result column=”dogid” property=”dogId”></result>
<result column=”datetime” property=”datetime”></result>
<association property=”dog” javaType=”Dog”>
<id column=”did” property=”id”></id>
<result column=”name” property=”name”></result>
<result column=”health” property=”health”></result>
<result column=”love” property=”love”></result>
<result column=”strain” property=”strain”></result>
<result column=”birthday” property=”datetime”></result>
</association>
</resultMap>
<select id=”getMasterAndDog” resultMap=”masterMap”>
select m.id,m.mastername,m.birthday,m.dogid,m.datetime,
d.id did,d.name,d.health,d.love,d.strain,d.datetime
from master m left join dog d on m.dogid=d.id
where m.mastername=#{mastername}
3.在datagrip中书写语句select m.id,m.mastername,m.birthday,m.dogid,m.datetime,
d.id did,d.name,d.health,d.love,d.strain,d.datetime
from master m left join dog d on m.dogid=d.id
where m.mastername=#{mastername};（找出master id =1时在dog库中相对应的狗的各项数据）
4.在MasterTest中测试输入的select语句
package bj.zb.kb22;
import nj.zb.kb22.dao.IMasterDao;
import nj.zb.kb22.pojo.Master;
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.InputStream;
import java.util.List;
public class MasterTest {
SqlSessionFactory sqlSessionFactory = null;
SqlSession sqlSession = null;
IMasterDao masterDao = null;
@Before
public void setUP() {
System.out.println(“hello before”);
InputStream inputStream = DogTest.class.getClassLoader()
.getResourceAsStream(“mybatis-config.xml”);
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
//获取构建工厂
sqlSessionFactory = builder.build(inputStream);
//打开会话
sqlSession = sqlSessionFactory.openSession();
masterDao = sqlSession.getMapper(IMasterDao.class);
}
@Test
public void testGetMaterAndDog(){
Master masterAndDog = masterDao.getMasterAndDog(“安安”);
System.out.println(masterAndDog);
System.out.println(masterAndDog.getDog());
}
@After
public void tearDown() {
System.out.println(“hello after”);
sqlSession.commit();
sqlSession.close();
System.out.println(“game over”);
}
}
@Test
publc void testGetMasterAndDog(){
Ms=aster masterAndDog=masterDao.getMasterAndDog(“安安”)
System.out.println(masterAndDog);
System.out.println(masterAndDog.getDog());
}
输出结果：与安安id相同在dog类里狗的数据
5.在MasterDaoImpl中实现方法
return null;
一对一：对Master中狗的品种相对应的在dog类中种类相同的狗的数据；
1.在Dogdao中 创建Dog getDogAndDogTypeInfo(String dogName);
2.在DogDao.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=”nj.zb.kb22.dao.IDogDao”>
<resultMap id=”dogStrainMap” type=”Dog”>
<id column=”id” property=”id”></id>
<result column=”name” property=”name”></result>
<result column=”health” property=”health”></result>
<result column=”love” property=”love”></result>
<result column=”strain” property=”strain”></result>
<result column=”datetime” property=”datetime”></result>
<association property=”dogInfo” javaType=”DogStrain”>
<id column=”s_id” property=”id”></id>
<result column=”s_name” property=”name”></result>
<result column=”heigh” property=”heigh”></result>
<result column=”weight” property=”weight”></result>
<result column=”describe” property=”describe”></result>
</association>
</resultMap>
<select id=”getDogAndDogTypeInfo” resultMap=”dogStrainMap”>
select d.id,d.name,d.health,d.love,d.strain,d.datetime,
s.id s_id,s.name s_name,s.heigh,s.weight,s.`describe`
from dog d left join dogstrain s on d.strain = s.name
where s.name=#{name};
</select>
</mapper>
3.在Test中输出： @Test
public void testGetDogAndDogTypeInfo(){
Dog dog = dogDao.getDogAndDogTypeInfo(“天狗”);
System.out.println(dog);
System.out.println(dog.getDogInfo());
输出结果是：天狗类型所对的dog类中的数据和master中所相对的数据
一对多：通过狗的类型查找对应的狗的数据
1.在mybatis-config.xml中创建 <mapper resource=”nj/zb/kb22/dao/DogStrain.xml”/>，进行数据读取
2.在DogStrain接口中创建 DogStrain getDogStrainAndDog(String dogtype);
3.在Dogstrain类中创建相关的类型
4.在DogStraindao中创建
<?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=”nj.zb.kb22.dao.IDogStrain”>
<resultMap id=”dogStrainMap” type=”DogStrain”>
<id column=”id” property=”id”></id>
<result column=”name” property=”name”></result>
<result column=”heigh” property=”heigh”></result>
<result column=”weight” property=”weight”></result>
<result column=”describe” property=”describe”></result>
<collection property=”dogs” ofType=”Dog”>
<id column=”did” property=”id”></id>
<result column=”dname” property=”name”></result>
<result column=”health” property=”health”></result>
<result column=”love” property=”love”></result>
<result column=”strain” property=”strain”></result>
<result column=”datetime” property=”datetime”></result>
</collection>
</resultMap>
<select id=”getDogStrainAndDog” resultMap=”dogStrainMap”>
select ds.id,ds.name,ds.heigh,ds.weight,ds.`describe`,
d.id did,d.name dname,d.health,d.love,d.strain,d.datetime
from dogStrain ds left join dog d on ds.name = d.strain
where ds.name=#{dogtype}
</select>
</mapper>
5.在DogStrainTest中创建：
package bj.zb.kb22;
import nj.zb.kb22.dao.IDogStrain;
import nj.zb.kb22.dao.IMasterDao;
import nj.zb.kb22.pojo.Dog;
import nj.zb.kb22.pojo.DogStrain;
import nj.zb.kb22.pojo.Master;
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.InputStream;
import java.util.List;
public class DogStrainTest {
SqlSessionFactory sqlSessionFactory = null;
SqlSession sqlSession = null;
IDogStrain dogStrainDao= null;
@Before
public void setUP() {
System.out.println(“hello before”);
InputStream inputStream = DogTest.class.getClassLoader()
.getResourceAsStream(“mybatis-config.xml”);
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
//获取构建工厂
sqlSessionFactory = builder.build(inputStream);
//打开会话
sqlSession = sqlSessionFactory.openSession();
dogStrainDao = sqlSession.getMapper(IDogStrain.class);
}
@Test
public void testA() {
System.out.println(dogStrainDao);
DogStrain dogStrain = dogStrainDao.getDogStrainAndDog(“泰迪”);
System.out.println(dogStrain);
System.out.println(dogStrain.getDogs());
}
@After
public void tearDown() {
System.out.println(“hello after”);
sqlSession.commit();
sqlSession.close();
System.out.println(“game over”);
}
}
输出结果：泰迪品种的狗在Master和dog中