mybaits增删改查
github源码(day52-mybatis-crud):https://github.com/1196557363/ideaMavenProject.git
导依赖到pom.xml
<!-- junit依赖 -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<scope>test</scope>
</dependency>
<!-- mybatis依赖 -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.5</version>
</dependency>
<!-- MySQL数据库依赖 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.6</version>
</dependency>
2. 创建配置文件mybatis-config.xml 和 创建表 user_crud
<?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="dev_mysql">
<environment id="dev_mysql">
<!--事务管理器-->
<transactionManager type="JDBC"></transactionManager>
<!--mybatis提供的连接池-->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/mybatis1" />
<property name="username" value="root" />
<property name="password" value="123456" />
</dataSource>
</environment>
</environments>
<!-- 映射接口 -->
<mappers>
<mapper resource="wpj/mapper/IUserDao.xml" />
</mappers>
</configuration>
3. 定义bean
package wpj.bean;
/**
* ClassName: User
* Description:
*
* @author JieKaMi
* @version 1.0
* @date: 2019\12\31 0031 11:20
* @since JDK 1.8
*/
public class User {
private String name;
private int age;
public User() { }
public User(String name, int age) {
this.name = name;
this.age = age;
}
public String getName() { return name; }
public void setName(String name) { this.name = name; }
public int getAge() { return age; }
public void setAge(int age) { this.age = age; }
@Override
public String toString() {
return "User{" +
"name='" + name + '\'' +
", age=" + age +
'}';
}
}
4. 定义接口
package wpj.dao;
import wpj.bean.*;
import java.util.*;
/**
* ClassName: IUserDao
* Description:
*
* @author JieKaMi
* @version 1.0
* @date: 2019\12\31 0031 11:22
* @since JDK 1.8
*/
public interface IUserDao {
// 查询所有
List<User> selectAllUser();
// 根据id查找User id为主键 只存在一个
User selectUserById(int id);
// 根据name查找User(age同理) name可重复 可能查出多个
List<User> selectUsersByName(String name);
// 根据Id修改User
int updateUserById(User user);
// 根据ID删除User
int deleteUserById(int id);
// 添加User
int insertUser(User user);
}
5. 写对应接口的XML IUserDao.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="wpj.dao.IUserDao">
<!-- 查询所有 id和dao接口的方法一致 下同 -->
<select id="selectAllUser" resultType="wpj.bean.User">
select * from user_crud
</select>
<!-- 根据id查询 -->
<select id="selectUserById" resultType="wpj.bean.User">
select * from user_crud where id = #{id}
</select>
<!-- 根据name查询 -->
<select id="selectUsersByName" resultType="wpj.bean.User">
select * from user_crud where name = #{name}
</select>
<!-- 根据id修改 -->
<update id="updateUserById">
update user_crud set name = #{name} where id = #{id}
</update>
<!-- 根据id删除 -->
<delete id="deleteUserById">
delete from user_crud where id = #{id}
</delete>
<!-- 添加 -->
<insert id="insertUser">
insert into user_crud(id, name, age) values(#{id}, #{name}, #{age})
</insert>
</mapper>
6. Test
package wpj;
import org.apache.ibatis.io.*;
import org.apache.ibatis.session.*;
import java.io.*;
/**
* ClassName: TestCrud
* Description:
*
* @author JieKaMi
* @version 1.0
* @date: 2019\12\31 0031 11:34
* @since JDK 1.8
*/
public class TestCrud {
// 抽取sqlSeesionFactory
public static SqlSessionFactory getSqlSeesionFactory() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
return sqlSessionFactory;
}
}
6.1 查询所有
/**
* 查询所有
* @throws IOException
*/
@Test
public void TestSelectAllUser() throws IOException {
SqlSession sqlSession = getSqlSeesionFactory().openSession();
List<User> userList = sqlSession.selectList("wpj.dao.IUserDao.selectAllUser");
for(User user : userList) {
System.out.println(user);
}
}
/**
* 查询所有 第二种方式
* @throws IOException
*/
@Test
public void TestSelectAllUser() throws IOException {
SqlSession sqlSession = getSqlSeesionFactory().openSession();
IUserDao mapper = sqlSession.getMapper(IUserDao.class);
List<User> userList = mapper.selectAllUser();
for(User user : userList) {
System.out.println(user);
}
}
6.2 根据ID(主键)查找
/**
* 根据ID查询
* @throws IOException
*/
@Test
public void TestSelectUserById() throws IOException {
SqlSession sqlSession = getSqlSeesionFactory().openSession();
User user = sqlSession.selectOne("wpj.dao.IUserDao.selectUserById",1);
System.out.println(user);
}
/**
* 根据ID查询 第二种方式
* @throws IOException
*/
@Test
public void TestSelectUserById() throws IOException {
SqlSession sqlSession = getSqlSeesionFactory().openSession();
IUserDao mapper = sqlSession.getMapper(IUserDao.class);
User user = mapper.selectUserById(1);
System.out.println(user);
}
6.1 根据name(可重复)查找
/**
* 根据Name查询
* @throws IOException
*/
@Test
public void TestSelectUsersByName() throws IOException {
SqlSession sqlSession = getSqlSeesionFactory().openSession();
List<User> userList = sqlSession.selectList("wpj.dao.IUserDao.selectUsersByName","jieKaMi");
for(User user : userList) {
System.out.println(user);
}
}
/**
* 根据Name查询 第二种方式
* @throws IOException
*/
@Test
public void TestSelectUsersByName() throws IOException {
SqlSession sqlSession = getSqlSeesionFactory().openSession();
IUserDao mapper = sqlSession.getMapper(IUserDao.class);
List<User> userList = mapper.selectUsersByName("jieKaMi");
for(User user : userList) {
System.out.println(user);
}
}
6.1 根据ID修改
/**
* 根据id修改
* @throws IOException
*/
@Test
public void TestUpdateUserById() throws IOException {
SqlSession sqlSession = getSqlSeesionFactory().openSession();
User user = new User(1, "jieKaMi2333", 15);
int result = sqlSession.update("wpj.dao.IUserDao.updateUserById",user);
sqlSession.commit();
System.out.println("修改: " + (result == 1));
}
/**
* 根据id修改 第二种方式
* @throws IOException
*/
@Test
public void TestUpdateUserById() throws IOException {
SqlSession sqlSession = getSqlSeesionFactory().openSession();
User user = new User(1, "jieKaMi2333", 15);
IUserDao mapper = sqlSession.getMapper(IUserDao.class);
int result = mapper.updateUserById(user);
sqlSession.commit();
System.out.println("修改: " + (result == 1));
}
6.1 根据ID删除
/**
* 根据id删除
* @throws IOException
*/
@Test
public void TestDeleteUserById() throws IOException {
SqlSession sqlSession = getSqlSeesionFactory().openSession();
int result = sqlSession.update("wpj.dao.IUserDao.deleteUserById",1);
sqlSession.commit();
System.out.println("删除: " + (result == 1));
}
/**
* 根据id删除 第二种方式
*/
@Test
public void TestDeleteUserById() throws IOException {
SqlSession sqlSession = getSqlSeesionFactory().openSession();
IUserDao mapper = sqlSession.getMapper(IUserDao.class);
int result = mapper.deleteUserById(1);
sqlSession.commit();
System.out.println("修改: " + (result == 1));
}
6.1 插入Bean
/**
* 添加Bean
* @throws IOException
*/
@Test
public void TestInsertUser() throws IOException {
SqlSession sqlSession = getSqlSeesionFactory().openSession();
User user = new User(1,"jieKaMi",22);
int result = sqlSession.update("wpj.dao.IUserDao.insertUser",user);
sqlSession.commit();
System.out.println("修改: " + (result == 1));
}
/**
* 添加Bean 第二种方式
* @throws IOException
*/
@Test
public void TestInsertUser() throws IOException {
SqlSession sqlSession = getSqlSeesionFactory().openSession();
IUserDao mapper = sqlSession.getMapper(IUserDao.class);
User user = new User(1,"jieKaMi",22);
int result = mapper.insertUser(user);
sqlSession.commit();
System.out.println("修改: " + (result == 1));
}