Mybatis中对MySQL数据库进行增删改查等操作[简单易上手]

学习过程中,写博客的第三天,今天没有项目,只有一些对数据库的增删改查等操作,下一步准备去学Python,之后可能会更新Python相关内容,但是也会去回顾Java项目,学习如逆水行舟,不进则退,希望每天都能有毅力和耐心,下面是我用Mybatis做的对数据库的操作,我会把最详细的源码放出来,有什么不足,望各位多担待.谢谢.

1. 编写bean层,创建数据库对象:

   public class StuBean {
       private int id;//学生学号
       private String name;//学生姓名
       private int tid;//老师的外键id
       private int score;//成绩
   
       @Override
       public String toString() {
           return "StuBean{" +
                   "id=" + id +
                   ", name='" + name + ''' +
                   ", tid=" + tid +
                   ", score=" + score +
                   '}';
       }
   
       public int getScore() {
           return score;
       }
   
       public void setScore(int score) {
           this.score = score;
       }
   
       public int getId() {
           return id;
       }
   
       public void setId(int id) {
           this.id = id;
       }
   
       public String getName() {
           return name;
       }
   
       public void setName(String name) {
           this.name = name;
       }
   
       public int getTid() {
           return tid;
       }
   
       public void setTid(int tid) {
           this.tid = tid;
       }
   }
   

2. 编写dao层接口:

   
   public interface StuDao {
       //查询所有
       public List getStuAll();
       //IN查询
       public List getStuByIn();
       //Not In查询
       public List getStuByNotIn();
       //distinct去重:查询出所有列的数据有完全一致的才会去重
       public List getStuByDistinct();
       //行数限定,分页
       public List getStuByLimit();
       //排序,order by,默认是升序(asg),降序是(desc)
       public List getStuByOrder();
       //使用IFNULL函数
       public int getStuByIfnull();
       //聚合函数:sum,avg,max,min,count
       //sum函数:求和
       public int getStuBySum();
       //avg函数:平均数
       public int getStuByAvg();
       //max:最大值
       public int getStuByMax();
       //min:最小值
       public int getStuByMin();
       //count:查询表中有多少数据
       public int getStuByCount();
   }
   

    

3. 编写工厂类,实现数据库连接:

   
   //工厂类
   public class SF {
       private static SqlSessionFactory sf;
       static {
           try {
               //解析配置文件
               Reader reader= Resources.getResourceAsReader("mybatis.xml");
               //创建SqlsessionFactory
               sf=new SqlSessionFactoryBuilder().build(reader);
           } catch (IOException e) {
               e.printStackTrace();
           }
       }
       //提供一个对外连接数据库的方法
       public static SqlSession getSession(){
           return sf.openSession();
       }
   }
   

    

public class StuDaoImpl implements StuDao {
    //默认不连接
    SqlSession session=null;
    //查询所有
    @Override
    public List getStuAll() {
        try {
            session= SF.getSession();
            List list=session.selectList("getStuAll");
            return list;
        }catch (Exception e){
            e.printStackTrace();
            session.rollback();
            return null;
        }

    }

    //IN查询
    @Override
    public List getStuByIn() {
        try {
            session= SF.getSession();
            List list=session.selectList("getStuByIn");
            return list;
        }catch (Exception e){
            e.printStackTrace();
            session.rollback();
            return null;
        }
    }

    //Not In 查询
    @Override
    public List getStuByNotIn() {
        try {
            session= SF.getSession();
            List list=session.selectList("getStuByNotIn");
            return list;
        }catch (Exception e){
            e.printStackTrace();
            session.rollback();
            return null;
        }
    }

    //distinct:去重
    @Override
    public List getStuByDistinct() {
        try {
            session= SF.getSession();
            List list=session.selectList("getStuByDistinct");
            return list;
        }catch (Exception e){
            e.printStackTrace();
            session.rollback();
            return null;
        }
    }

    //行数限定,分页
    @Override
    public List getStuByLimit() {
        try {
            session= SF.getSession();
            List list=session.selectList("getStuByLimit");
            return list;
        }catch (Exception e){
            e.printStackTrace();
            session.rollback();
            return null;
        }
    }

    //升序,降序
    @Override
    public List getStuByOrder() {
        try {
            session= SF.getSession();
            List list=session.selectList("getStuByOrder");
            return list;
        }catch (Exception e){
            e.printStackTrace();
            session.rollback();
            return null;
        }
    }

    //IFNULL函数,查询学生成绩
    @Override
    public int getStuByIfnull() {
        try {
            session= SF.getSession();
            int st=session.selectOne("getStuByIfnull");
            return st;
        }catch (Exception e){
            e.printStackTrace();
            session.rollback();
            return -1;
        }
    }

    //sum求和
    @Override
    public int getStuBySum() {
        try {
            session= SF.getSession();
            int st=session.selectOne("getStuBySum");
            return st;
        }catch (Exception e){
            e.printStackTrace();
            session.rollback();
            return -1;
        }
    }

    //Avg:平均数
    @Override
    public int getStuByAvg() {
        try {
            session= SF.getSession();
            int st=session.selectOne("getStuByAvg");
            return st;
        }catch (Exception e){
            e.printStackTrace();
            session.rollback();
            return -1;
        }
    }

    //最大值
    @Override
    public int getStuByMax() {
        try {
            session= SF.getSession();
            int st=session.selectOne("getStuByMax");
            return st;
        }catch (Exception e){
            e.printStackTrace();
            session.rollback();
            return -1;
        }
    }

    //最小值
    @Override
    public int getStuByMin() {
        try {
            session= SF.getSession();
            int st=session.selectOne("getStuByMin");
            return st;
        }catch (Exception e){
            e.printStackTrace();
            session.rollback();
            return -1;
        }
    }

    //总数
    @Override
    public int getStuByCount() {
        try {
            session= SF.getSession();
            int st=session.selectOne("getStuByCount");
            session.commit();
            return st;
        }catch (Exception e){
            e.printStackTrace();
            session.rollback();
            return -1;
        }
    }
}

    
        select id,name,score,tid from student
    


    
        select id,name,score,tid from student where name IN("张三","李四")
    


    
        select id,name,score,tid from student where name NOT IN ("张三","李四")
    


    
        select DISTINCT name,score,tid from student
    


    
        select id,name,score,tid from student limit 0,3
    


    
        select id,name,score,tid from student ORDER BY score ASC
    


    
        select (IFNULL(score,0)+100) from student where id=2
    


    
        select SUM(score) from student
    


    
        select AVG(score) from student
    


    
        select MAX(score) from student
    


    
        select MIN(score) from student
    


    
        select COUNT(id) from student
    

public class TeaTest {
    public static void main(String[] args) {
        StuDao sd=new StuDaoImpl();
        System.out.println("学生列表:"+sd.getStuAll());
        System.out.println("=================================");
        System.out.println("查学生是张三和李四的信息:"+sd.getStuByIn());
        System.out.println("=================================");
        System.out.println("查学生不是张三和李四的信息:"+sd.getStuByNotIn());
        System.out.println("=================================");
        System.out.println("去重查询:"+sd.getStuByDistinct());
        System.out.println("=================================");
        System.out.println("从第一个查到第三个:"+sd.getStuByLimit());
        System.out.println("=================================");
        System.out.println("对成绩用升序查询:"+sd.getStuByOrder());
        System.out.println("=================================");
        System.out.println("求某一个学生的成绩并加一百分:"+sd.getStuByIfnull());
        System.out.println("=================================");
        System.out.println("学生成绩总和:"+sd.getStuBySum());
        System.out.println("=================================");
        System.out.println("学生成绩平均值:"+sd.getStuByAvg());
        System.out.println("=================================");
        System.out.println("学生成绩最大值:"+sd.getStuByMax());
        System.out.println("=================================");
        System.out.println("学生成绩最小值:"+sd.getStuByMin());
        System.out.println("=================================");
        System.out.println("总共有"+sd.getStuByCount()+"个学生");

    }
}