条件查询-分页查询-条件分页查询

条件查询（参考）

设计图：

参考代码：

创建数据表：

#创建班级表
create table classInfo
(
    classId int primary key auto_increment,
    className varchar(20)
)

    insert into classInfo
(className)
select 'AAA01' UNION
select 'AAA02' UNION
select 'AAA03'

    insert into classInfo
(className)
select 'AAA04' UNION
select 'AAA05' UNION
select 'AAA06' UNION
select 'AAA07'

select * from classInfo

drop table studentInfo

    #创建学生信息表
create table studentInfo
(
    studentId int primary key auto_increment,
    name varchar(20),
    sex char(1),
    birthday date,
    tall decimal(3,2),
    classId int
)

    insert into studentInfo
(name,sex,birthday,tall,classId)
select '张三','男','2001-01-01',1.7,1 union
select '李四','女','2002-01-01',1.7,2

select * from studentInfo
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

BaseDAO:

package com.util;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.*;
import java.util.*;

public class BaseDAO {

	//四大金刚
	//驱动类
	private static final String DRIVER="com.mysql.cj.jdbc.Driver";
	//连接地址
	private static final String URL="jdbc:mysql://192.168.0.115:3306/0801_db?useSSL=false&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
	//用户名
	private static final String USER="root";
	//密码
	private static final String PASSWORD="123456";



	//获取连接
	public static Connection getConnection(){

		Connection con = null;

		try{
			//加载驱动类
			Class.forName(DRIVER);
			//获取连接
			con = DriverManager.getConnection(URL,USER,PASSWORD);
			
		}catch(Exception ex){
			ex.printStackTrace();
		}

		return con;
	}

	//关闭数据库对象
	public static void closeAll(Connection con,Statement st,ResultSet rs){
		
		if(rs!=null){
			try{
				rs.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
			
		}

		if(st!=null){

			try{
				st.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
			
		}

		if(con!=null){
			try{
				con.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
			
		}

	}


	//通用设置参数方法
	public static void setParams(PreparedStatement pst,Object[] params){

		if(params==null){
			return;
		}

		for(int i=0;i> executeQuery(String sql,Object[] params) {

		List> rows = new ArrayList<>();

		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;

		try{
			//获取连接	
			con = getConnection();			
			//获取命令对象
			pst = con.prepareStatement(sql);
			//设置参数
			setParams(pst,params);
			//执行查询
			rs = pst.executeQuery();

			//通过rs获取结果集的结构信息
			ResultSetMetaData rsmd =  rs.getMetaData();
			//获取结果集的列数
			int colCount = rsmd.getColumnCount();

			//遍历查询结果，并封装到List
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176

ClassInfo:

package com.entity;

/**
 * @author: hy
 * @create: 2022-08-01 10:52:43
 */
public class ClassInfo {
    private Integer classId;
    private String className;

    public ClassInfo() {
    }

    public ClassInfo(Integer classId, String className) {
        this.classId = classId;
        this.className = className;
    }

    public ClassInfo(String className) {
        this.className = className;
    }

    public Integer getClassId() {
        return classId;
    }

    public void setClassId(Integer classId) {
        this.classId = classId;
    }

    public String getClassName() {
        return className;
    }

    public void setClassName(String className) {
        this.className = className;
    }

    @Override
    public String toString() {
        return "ClassInfo{" +
                "classId=" + classId +
                ", className='" + className + '\'' +
                '}';
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

IClassInfoDAO:

package com.dao;

import com.entity.ClassInfo;

import java.util.List;
import java.util.Map;

public interface IClassInfoDAO {
    /**
     * 查询所有
     * @return
     */
    List> listAll();
    //带条件查询
    List> listByCon(String searchClassName);

    ClassInfo getByClassId(Integer classId);

    int insert(ClassInfo classInfo);
    int update(ClassInfo classInfo);
    int delete(Integer classId);
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

ClassInfoDAOImpl:

package com.dao.impl;

import com.dao.IClassInfoDAO;
import com.entity.ClassInfo;
import com.mysql.cj.util.StringUtils;
import com.util.BaseDAO;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author: hy
 * @create: 2022-08-01 10:54:26
 */
public class ClassInfoDAOImpl implements IClassInfoDAO {
    @Override
    public List> listAll() {
        String sql ="select classId,className from classInfo ";
        return BaseDAO.executeQuery(sql,null);
    }

    @Override
    public List> listByCon(String searchClassName) {
        //where sql语句
        StringBuilder whereSql = new StringBuilder();
        //sql中的参数
        List