MyBatis 是支持普通 SQL查询，存储过程和高级映射的优秀持久层框架。本节用mybatis来替代JDBC的功能。

材料是jc0122.sql，里面的admin_info是要操作的表。不像上一节需要把数据库文件放到java目录里。数据库只要在本地即可。本节要实现对数据库的查询+添加。

需要导入包，下载可去官网 mybatis – MyBatis 3 | 入门

 本次作业存放位置：注意最后两个在src目录下，不在S5包内

 一、配置文件

创建mybatis-config.xml，里面粘贴上官网上的配置代码，自己改改

注意绑定的数据库，我一开始只有jc0122会报错，加上后面这些字符就好了。用户名和密码也设置成自己的，mapper一开始没写的话就注释pass掉

再新建config.properties，里面放上类似上面的四项数据，方便后面调用

url=jdbc:mysql://localhost:3306/jc0122
driver=com.mysql.cj.jdbc.Driver
name=root
pwd=123456

二、 Util包-链接数据库

创建DBUtil，写链接数据库的方法。会调用到上面提到的config.properties

package S5.util;import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;public class DBUtil {private static Properties pro = new Properties();private static String url;private static String driver;private static String name;private static String pwd;static {InputStream ins = DBUtil.class.getClassLoader().getResourceAsStream("config.properties");try {pro.load(ins);url = pro.getProperty("url");driver = pro.getProperty("driver");name = pro.getProperty("name");pwd = pro.getProperty("pwd");} catch (IOException e) {e.printStackTrace();}}public static Connection getCone() {try {Class.forName(driver);Connection cone  = DriverManager.getConnection(url,name,pwd);return cone;} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();}return null;}// 关闭的方法public static void close(Connection cone) {try {if (cone != null) {cone.close();}} catch (SQLException e) {e.printStackTrace();}}
}

三、pojo包-声明变量getset

确定类型直接生成，最终要字符串输出

package S5.pojo;import java.util.Date;public class AdminInfo {private Integer adminId ;private String adminName;private String adminPwd;private Integer roleId;private Integer adminState;private Date adminCtime;private Date adminMtime;public AdminInfo() {}public Integer getAdminId() {return adminId;}public void setAdminId(Integer adminId) {this.adminId = adminId;}public String getAdminName() {return adminName;}public void setAdminName(String adminName) {this.adminName = adminName;}public String getAdminPwd() {return adminPwd;}public void setAdminPwd(String adminPwd) {this.adminPwd = adminPwd;}public Integer getRoleId() {return roleId;}public void setRoleId(Integer roleId) {this.roleId = roleId;}public Integer getAdminState() {return adminState;}public void setAdminState(Integer adminState) {this.adminState = adminState;}public Date getAdminCtime() {return adminCtime;}public void setAdminCtime(Date adminCtime) {this.adminCtime = adminCtime;}public Date getAdminMtime() {return adminMtime;}public void setAdminMtime(Date adminMtime) {this.adminMtime = adminMtime;}@Overridepublic String toString() {return "AdminInfo{" +"adminId=" + adminId +", adminName='" + adminName + '\'' +", adminPwd='" + adminPwd + '\'' +", roleId=" + roleId +", adminState=" + adminState +", adminCtime=" + adminCtime +", adminMtime=" + adminMtime +'}';}
}

四、DAO包

先创建一个接口类AdminDao，要导入上面的AdminInfo，声明两个方法，分别是账号密码查询和分页查询

package S5.dao;import S5.pojo.AdminInfo;import java.util.List;public interface AdminDao {/*** 通过账号密码查询admin对象* @param name  账号* @param pwd   密码* @return  admin  对象*/AdminInfo selectByNameAndPwd(String name, String pwd);/*** 分页查询用户信息* @param start 起始* @param limit 条数* @return  admin对象集合*/List selectByPage(Integer start, Integer limit);}

再创建AdminDaolmpl，实现查询，会调用到DBUtil。变量名大写。

package S5.dao;import S5.pojo.AdminInfo;
import S5.util.DBUtil;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;public class AdminDaoImpl implements AdminDao {@Overridepublic AdminInfo selectByNameAndPwd(String name, String pwd) {//获取sql连接Connection conn = DBUtil.getCone();String sql = "SELECT * FROM ADMIN_INFO WHERE ADMIN_NAME = ?  AND ADMIN_PWD = ?";try {PreparedStatement ps = conn.prepareStatement(sql);ps.setObject(1,name);ps.setObject(2,pwd);ResultSet set = ps.executeQuery();if (set.next()){AdminInfo adminInfo = new AdminInfo();adminInfo.setAdminId(set.getInt("ADMIN_ID"));adminInfo.setAdminName(set.getString("ADMIN_NAME"));adminInfo.setAdminPwd(set.getString("ADMIN_PWD"));adminInfo.setAdminCtime(set.getDate("ADMIN_CTIME"));adminInfo.setAdminMtime(set.getDate("ADMIN_MTIME"));adminInfo.setAdminState(set.getInt("ADMIN_STATE"));adminInfo.setRoleId(set.getInt("ROLE_ID"));return  adminInfo;}} catch (SQLException e) {e.printStackTrace();}finally {DBUtil.close(conn);}return null;}/*** 分页查询用户信息** @param start 起始* @param limit 条数* @return admin对象集合*/@Overridepublic List selectByPage(Integer start, Integer limit) {//获取sql连接Connection conn = DBUtil.getCone();String sql = "SELECT * FROM ADMIN_INFO LIMIT ? , ?";try {PreparedStatement ps = conn.prepareStatement(sql);ps.setObject(1,start);ps.setObject(2,limit);ResultSet set = ps.executeQuery();List list = new ArrayList<>();while (set.next()){AdminInfo adminInfo = new AdminInfo();adminInfo.setAdminId(set.getInt("ADMIN_ID"));adminInfo.setAdminName(set.getString("ADMIN_NAME"));adminInfo.setAdminPwd(set.getString("ADMIN_PWD"));adminInfo.setAdminCtime(set.getDate("ADMIN_CTIME"));adminInfo.setAdminMtime(set.getDate("ADMIN_MTIME"));adminInfo.setAdminState(set.getInt("ADMIN_STATE"));adminInfo.setRoleId(set.getInt("ROLE_ID"));list.add(adminInfo);}return  list;} catch (SQLException e) {e.printStackTrace();}finally {DBUtil.close(conn);}return null;}
}

五、封装成DAO工厂

方便调用

package S5.factory;import S5.dao.AdminDao;
import S5.dao.AdminDaoImpl;public class DaoFactory {public static AdminDao getAdminDAO() {return new AdminDaoImpl();}}

六、mapper

创建一个包mapper，新建一个接口  命名规则 pojo+Mapper

package S5.mapper;import org.apache.ibatis.annotations.Param;
import S5.pojo.AdminInfo;import java.util.List;public interface AdminInfoMapper {List selectAll();Integer insertOne(@Param("a") AdminInfo adminInfo);/*** 根据idx修改名字* @param adminName* @param id* @return*/Integer updateById(@Param("name") String adminName, @Param("id") Integer id);Integer deleteById(@Param("id") Integer id);/*** 分页并按条件查询* @param adminName     姓名* @param bdata     注册开始时间* @param edate     注册的结束时间* @param rid       角色查询* @param start     分页开始* @param limit     分页条数* @return*/List selectByPage(@Param("name") String adminName, @Param("bdata") String bdata,@Param("edate") String edate, @Param("rid") Integer rid,@Param("start") Integer start, @Param("limit") Integer limit);}

继续在这个mapper 包下创建一个AdminInfoMapper.xml

   
select * from admin_info
insert into admin_info(admin_id, admin_name, admin_pwd, admin_state, admin_ctime, role_id)values(null,#{a.adminName},#{a.adminPwd},1,now(),#{a.roleId})

注意配置要和自己的一致，并且mybatis-config.xml可以不注释mapper这行了

七、测试

demo中测试

import S5.factory.DaoFactory;
import S5.mapper.AdminInfoMapper;
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 S5.pojo.AdminInfo;
import S5.util.DBUtil;import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.List;public class Demo {public static void main(String[] args) throws IOException {//        System.out.println("DBUtil:" + DBUtil.getCone());
//
//        AdminInfo adminInfo =  DaoFactory.getAdminDAO().selectByNameAndPwd("zhangsan","123456");
//        System.out.println(adminInfo);
//
//        List list = DaoFactory.getAdminDAO().selectByPage(0,3);
//        System.out.println(list);String resource = "mybatis-config.xml";  //读取配置文件的目录InputStream inputStream = Resources.getResourceAsStream(resource);  //加载配置文件SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); //从配置文件中获取sqlsession 连接的工厂类SqlSession sqlSession = sqlSessionFactory.openSession(); //获取sql语句的操作对象 SQLsessionSystem.out.println(sqlSession);AdminInfoMapper adminInfoMapper = sqlSession.getMapper(AdminInfoMapper.class);  // 由SQL工厂来管理mapper（dao）List list = adminInfoMapper.selectAll();System.out.println(list);AdminInfo adminInfo = new AdminInfo();adminInfo.setAdminName("班班");adminInfo.setAdminPwd("123456");adminInfo.setRoleId(1);adminInfoMapper.insertOne(adminInfo);//        数据库中增删改是需要提交的 commit  ,平时的数据库的软件默认自动提交的，但是代码中需要手动提交sqlSession.commit();//提交到数据库List list2 = adminInfoMapper.selectAll();System.out.println(list2);}}

 第一行是原先数据列出，第二行是添加后的所有

navicat上也可以查到。（左下角刷新）