---

一、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 {@Testvoid 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、测试代码

    @Testvoid 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、测试代码

    @Testvoid 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、结果