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