JDBC_使用ResultSet 执行查询操作(基于oracle数据库)

时间:2021-07-09 11:50:28

上篇文章已经使用Statement实现了对数据库的增删改操作,接下来使用ResultSet结果集执行查询操作
步骤如下:
ResultSet:结果集.封装了使用JDNC进行查询的结果
1.调用Statement对象的executeQuery(sql) 可以得到结果集
2.ResultSet返回的实际上就是一张数据表,有一个指针指向数据表的第一行的前面
可以调用next() 方法检测下一行是否有效,若有效该方法返回true,且指针下移。
3.当指针对位到一行时。可以调用getXxx(index) 或getXxx(columnName)
获取每一列的值 例如:getInt(1),getString(“name”)
4/Reslut 当然也需要进行关闭

定义三个变量
Connection conn = null;
Statement statement = null;
ResultSet rs = null;

1.获取Connection连接
conn = JDBCTools.getConnection();

2.获取Statement
statement = conn.createStatement();

3.创建sql语句
String sql = “select * from student where SNO = ‘1001’”;

4.执行查询得到ResultSet
rs = statement.executeQuery(sql);

5.处理ResultSet
while(rs.next()){
String sno = rs.getString(1);
String sname = rs.getString(2);
String ssex = rs.getString(3);
String sdept = rs.getString(4);
System.out.print(sno);
System.out.print(” ” + sname);
System.out.print(” ” + ssex);
System.out.println(” ” + sdept);
}

6.关闭结果集
JDBCTools.release(rs,statement, conn);

这里将关闭和连接的代码封装成了JDBCTools类中,后面有该类完整代码

执行查询代码如下
JDBCTest1.java

package com.atchance.jdbc;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import oracle.jdbc.*;
import oracle.jdbc.driver.*;

public class JDBCTest1 {

public void testReslutSet() throws Exception{

//获取 sno = 100 的数据表student的记录并打印

Connection conn = null;
Statement statement = null;
ResultSet rs = null;

try {
//1.获取Connection
conn = JDBCTools.getConnection();

//2.获取Statement
statement = conn.createStatement();

//3.准备SQL
String sql = "select * from student where SNO = '1001'";

//4.执行查询得到ResultSet
rs = statement.executeQuery(sql);
System.out.println(rs);

//5.处理ResultSet
while(rs.next()){
String sno = rs.getString(1);
String sname = rs.getString(2);
String ssex = rs.getString(3);
String sdept = rs.getString(4);
System.out.print(sno);
System.out.print(" " + sname);
System.out.print(" " + ssex);
System.out.println(" " + sdept);
}
} catch (Exception e) {
e.printStackTrace();
}
finally{
//6.关闭结果集
JDBCTools.release(rs,statement, conn);
}
}
public static void main(String[] arg) throws Exception{
JDBCTest1 t1 = new JDBCTest1();
t1.testReslutSet();
}
}

JDBCTools.java

package com.atchance.jdbc;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JDBCTools {

public static void release(ResultSet rs,Statement statement, Connection con) throws SQLException{

try {
if(rs != null)
rs.close();
} catch (Exception e1) {
e1.printStackTrace();
}
try {
if(statement != null)
statement.close();
} catch (Exception e) {
e.printStackTrace();
}finally{
if(con != null)
con.close();
}
}

public static void release(Statement statement, Connection con) throws SQLException{
try {
if(statement != null)
statement.close();
} catch (Exception e) {
e.printStackTrace();
}finally{
if(con != null)
con.close();
}
}
public static Connection getConnection() throws Exception{

String driverClass = "oracle.jdbc.driver.OracleDriver";
String jdbcUrl = "jdbc:oracle:thin:@localhost:1521:ORCL";
String user = "scott";
String password = "tiger";

Driver driver = (Driver)Class.forName(driverClass).newInstance();

Properties info = new Properties();
info.put("user", user);
info.put("password", password);
Connection connection = driver.connect(jdbcUrl,info);
return connection;
}
}

“`

“至此,JDBC连接数据库并进行增删改查四种操作已全部完成
数据库连接以及增删改祥见另两篇文章
使用JDBC中的DriverManager实现数据库的连接
JDBC_使用 Statement 执行更新操作(基于oracle数据库)