JDBC判断ResultSet是否为空以及匹配字段编码和遍历字段

---

一、JDBC判断ResultSet是否为空

1、连接类

public class TZDBConn {
	private static final String URL = "jdbc:mysql://127.0.0.1:3306/mytest?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&useSSL=false&allowMultiQueries=true&allowPublicKeyRetrieval=true";
    private static final String DRIVER = "com.mysql.cj.jdbc.Driver";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "123";

    private static Connection connection = null;
    private static PreparedStatement sta = null;
	private static ResultSet rs = null;
	
	/**
     * 读取属性文件中的信息
     *
     * @param key
     * @return
     */
    private static String getValue(String key) {
        // 资源包绑定
        ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
        return bundle.getString(key);
    }
    
    /**
	 * 加载驱动程序
	 */
	static {
		try {
			Class.forName(DRIVER);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * @return 连接对象
	 */
	public Connection getConnection() {
		try {
			connection = DriverManager.getConnection(URL,USERNAME,PASSWORD);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return connection;
	}
	
	/**
	 * @param sql sql语句
	 * @param obj 参数
	 * @return 数据集合
	 */
	public ResultSet Query(String sql,Object...obj){
		connection=getConnection();
		try {
			sta=connection.prepareStatement(sql);
			if(obj!=null){
				for(int i=0;i<obj.length;i++){
					sta.setObject(i+1, obj[i]);
				}
			}
			rs=sta.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}
	
	/**
	 * 关闭资源
	 */
	public void close() {
		try {
			if (rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (sta != null) {
					sta.close();
				}
			} catch (SQLException e2) {
				e2.printStackTrace();
			} finally {
				if (connection != null) {
					try {
						connection.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
		}
	}

	public List<User> getUser(String sql) {
		ArrayList<User> users = new ArrayList<>();
		TZDBConn2 dbconn=new TZDBConn2();
		try {
			rs =dbconn.Query(sql, null);
			if(rs.isBeforeFirst()==false){
				return null;
			}
			while(rs.next()){
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setUserName(rs.getString("username"));
				user.setPassWord(rs.getString("password"));
				users.add(user);
			}
		} catch (Exception e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
		}finally {
			close();
		}

		return users;
	}
}

2、测试数据

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;


INSERT INTO `mytest`.`user`(`id`, `username`, `password`) VALUES (2, '2', '');
INSERT INTO `mytest`.`user`(`id`, `username`, `password`) VALUES (3, '3', NULL);

3、测试代码

@SpringBootTest
class MyJavaTestApplicationTests {

    @Test
    void contextLoads() {
        TZDBConn2 tzdbConn2 = new TZDBConn2();
        List<User> user = tzdbConn2.getUser("select * from user where id = 1");

        if (user == null) {
            System.out.println("空");
        } else {
            for (User u : user) {
                System.out.println(u.toString());
            }
        }
        System.out.println("===============================================================");
        List<User> userList = tzdbConn2.getUser("select * from user");
        if (userList == null) {
            System.out.println("空");
        } else {
            for (User u : userList) {
                System.out.println(u.toString());
            }
        }
    }
 }

4、输出结果

空
===============================================================
User{id=2, userName='2', passWord=''}
User{id=3, userName='3', passWord='null'}

二、匹配字段问题

1、连接类的新增方法

	public List<User> getUser2(String sql) {
		ArrayList<User> users = new ArrayList<>();
		TZDBConn2 dbconn=new TZDBConn2();
		try {
			rs =dbconn.Query(sql, null);
			if(rs.isBeforeFirst()==false){
				return null;
			}
			while(rs.next()){
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setUserName(rs.getString("username"));
				user.setPassWord(rs.getString("password").equals("1")?"有":"无");
				users.add(user);
			}
		} catch (Exception e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
		}finally {
			close();
		}

		return users;
	}

2、测试代码

    @Test
    void contextLoad5() {
        TZDBConn2 tzdbConn2 = new TZDBConn2();
        List<User> userList = tzdbConn2.getUser2("select * from user where id =2 ");
        if (userList == null) {
            System.out.println("空");
        } else {
            for (User u : userList) {
                System.out.println(u.toString());
            }
        }
        System.out.println("===============================================================");
        List<User> userList2 = tzdbConn2.getUser2("select * from user where id =3 ");
        if (userList == null) {
            System.out.println("空");
        } else {
            for (User u : userList2) {
                System.out.println(u.toString());
            }
        }
    }

3、结果

如果数据为空则可以识别，如果数据为null则会报错（测试数据和第一个相同）

三、遍历字段

1、连接类新增方法

	public List<Pud> getUser3(String sql) {
		ArrayList<Pud> users = new ArrayList<>();
		TZDBConn2 dbconn=new TZDBConn2();
		try {
			rs =dbconn.Query(sql, null);
			if(rs.isBeforeFirst()==false){
				return null;
			}
			while(rs.next()){
				for (int i = 1; i <= 3; i++) {
					Pud pud = new Pud();
					pud.setId(i);
					pud.setName(rs.getString("name"+i));
					users.add(pud);
				}
			}
		} catch (Exception e) {
			System.out.println(e.getMessage());
			e.printStackTrace();
		}finally {
			close();
		}

		return users;
	}

2、表数据

CREATE TABLE `pud` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name1` varchar(255) DEFAULT NULL,
  `name2` varchar(255) DEFAULT NULL,
  `name3` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

INSERT INTO `mytest`.`pud`(`id`, `name1`, `name2`, `name3`) VALUES (1, '卢', '本', '伟');
INSERT INTO `mytest`.`pud`(`id`, `name1`, `name2`, `name3`) VALUES (2, '五', '五 ', '开');

3、测试代码

    @Test
    void contextLoads4() {
        TZDBConn2 tzdbConn2 = new TZDBConn2();
        List<Pud> puds = tzdbConn2.getUser3("select * from pud where id =1");
        if (puds == null) {
            System.out.println("空");
        } else {
            for (Pud u : puds) {
                System.out.println(u.toString());
            }
        }
    }

4、结果