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

一、JDBC判断ResultSet是否为空

1、连接类2、测试数据3、测试代码4、输出结果 二、匹配字段问题

1、连接类的新增方法2、测试代码3、结果 三、遍历字段

1、连接类新增方法2、表数据3、测试代码4、结果

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;
	
	
    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();
		}
	}
	
	
	public Connection getConnection() {
		try {
			connection = DriverManager.getConnection(URL,USERNAME,PASSWORD);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return connection;
	}
	
	
	public ResultSet Query(String sql,Object...obj){
		connection=getConnection();
		try {
			sta=connection.prepareStatement(sql);
			if(obj!=null){
				for(int i=0;i getUser(String sql) {
		ArrayList 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;
	}
}

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);

@SpringBootTest
class MyJavaTestApplicationTests {

    @Test
    void contextLoads() {
        TZDBConn2 tzdbConn2 = new TZDBConn2();
        List 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 userList = tzdbConn2.getUser("select * from user");
        if (userList == null) {
            System.out.println("空");
        } else {
            for (User u : userList) {
                System.out.println(u.toString());
            }
        }
    }
 }

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

	public List getUser2(String sql) {
		ArrayList 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;
	}

    @Test
    void contextLoad5() {
        TZDBConn2 tzdbConn2 = new TZDBConn2();
        List 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 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());
            }
        }
    }

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

	public List getUser3(String sql) {
		ArrayList 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;
	}

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, '五', '五 ', '开');

    @Test
    void contextLoads4() {
        TZDBConn2 tzdbConn2 = new TZDBConn2();
        List 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());
            }
        }
    }