这篇博客记录了作者使用Mybatis进行数据库增删改查操作的学习过程，包括bean层、dao层接口、工厂类、实现类的编写，以及mapper.xml文件中的SQL查询。内容涵盖基本查询、条件查询、分页、排序、聚合函数等操作，并在测试类中展示了功能的实现和结果输出。
学习过程中,写博客的第三天,今天没有项目,只有一些对数据库的增删改查等操作,下一步准备去学Python,之后可能会更新Python相关内容,但是也会去回顾Java项目,学习如逆水行舟,不进则退,希望每天都能有毅力和耐心,下面是我用Mybatis做的对数据库的操作,我会把最详细的源码放出来,有什么不足,望各位多担待.谢谢.
-
编写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; } } -
编写dao层接口:
public interface StuDao { //查询所有 public List<StuBean> getStuAll(); //IN查询 public List<StuBean> getStuByIn(); //Not In查询 public List<StuBean> getStuByNotIn(); //distinct去重:查询出所有列的数据有完全一致的才会去重 public List<StuBean> getStuByDistinct(); //行数限定,分页 public List<StuBean> getStuByLimit(); //排序,order by,默认是升序(asg),降序是(desc) public List<StuBean> 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(); } -
编写工厂类,实现数据库连接:
//工厂类 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(); } }
4.编写dao层实现类:
public class StuDaoImpl implements StuDao {
//默认不连接
SqlSession session=null;
//查询所有
@Override
public List<StuBean> getStuAll() {
try {
session= SF.getSession();
List<StuBean> list=session.selectList("getStuAll");
return list;
}catch (Exception e){
e.printStackTrace();
session.rollback();
return null;
}
}
//IN查询
@Override
public List<StuBean> getStuByIn() {
try {
session= SF.getSession();
List<StuBean> list=session.selectList("getStuByIn");
return list;
}catch (Exception e){
e.printStackTrace();
session.rollback();
return null;
}
}
//Not In 查询
@Override
public List<StuBean> getStuByNotIn() {
try {
session= SF.getSession();
List<StuBean> list=session.selectList("getStuByNotIn");
return list;
}catch (Exception e){
e.printStackTrace();
session.rollback();
return null;
}
}
//distinct:去重
@Override
public List<StuBean> getStuByDistinct() {
try {
session= SF.getSession();
List<StuBean> list=session.selectList("getStuByDistinct");
return list;
}catch (Exception e){
e.printStackTrace();
session.rollback();
return null;
}
}
//行数限定,分页
@Override
public List<StuBean> getStuByLimit() {
try {
session= SF.getSession();
List<StuBean> list=session.selectList("getStuByLimit");
return list;
}catch (Exception e){
e.printStackTrace();
session.rollback();
return null;
}
}
//升序,降序
@Override
public List<StuBean> getStuByOrder() {
try {
session= SF.getSession();
List<StuBean> 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;
}
}
}
5.编写mapper.xml文件,对数据库进行操作:
<!--查询所有-->
<select id="getStuAll" resultType="com.xh.bean.StuBean">
select id,name,score,tid from student
</select>
<!-- IN查询-->
<select id="getStuByIn" resultType="com.xh.bean.StuBean">
select id,name,score,tid from student where name IN("张三","李四")
</select>
<!-- Not In 查询-->
<select id="getStuByNotIn" resultType="com.xh.bean.StuBean">
select id,name,score,tid from student where name NOT IN ("张三","李四")
</select>
<!-- Distinct 去重-->
<select id="getStuByDistinct" resultType="com.xh.bean.StuBean">
select DISTINCT name,score,tid from student
</select>
<!-- 行数限定,分页 -->
<select id="getStuByLimit" resultType="com.xh.bean.StuBean">
select id,name,score,tid from student limit 0,3
</select>
<!-- 排序 asc:升序,desc:降序-->
<select id="getStuByOrder" resultType="com.xh.bean.StuBean">
select id,name,score,tid from student ORDER BY score ASC
</select>
<!-- IFNULL函数-->
<select id="getStuByIfnull" resultType="int">
select (IFNULL(score,0)+100) from student where id=2
</select>
<!-- 对所有学生的成绩求和-->
<select id="getStuBySum" resultType="int">
select SUM(score) from student
</select>
<!-- 求学生成绩的平均数-->
<select id="getStuByAvg" resultType="int">
select AVG(score) from student
</select>
<!-- 求最高成绩-->
<select id="getStuByMax" resultType="int">
select MAX(score) from student
</select>
<!-- 求最低成绩-->
<select id="getStuByMin" resultType="int">
select MIN(score) from student
</select>
<!-- 求总共有多少学生-->
<select id="getStuByCount" resultType="int">
select COUNT(id) from student
</select>
</mapper>
6.编写测试类:
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()+"个学生");
}
}
7.下面是测试类实现的内容截图:
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
