java Statement查询clob java statement查询

清楚了PreparedStatement实现更新处理之后，那么下面可以使用其实现数据的查询处理操作，由于在开发之中PreparedStatement的使用是最广泛的，下面将列举几个有代表性的查询。
1、查询全部数据：

package cn.mldn.demo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Date;
public class JDBCDemo {
	private static final String DATABASE_DRIVER = "oracle.jdbc.driver.OracleDriver";
	private static final String DATABASE_URL = "jdbc:oracle:thin:@localhost:1521:mldn";
	private static final String DATABASE_USER = "scott";
	private static final String DATABASE_PASSWORD = "tiger";
	public static void main(String[] args) throws Exception{
		String sql = "SELECT nid,title,read,price,content,pudate FROM news";
		Connection conn = null;  //每一个Connection接口对象描述的就是一个用户连接
		Class.forName(DATABASE_DRIVER); //向容器之中加载数据库驱动程序
		conn = DriverManager.getConnection(DATABASE_URL, DATABASE_USER, DATABASE_PASSWORD);
		PreparedStatement pstmt = conn.prepareStatement(sql); // 创建数据库的操作对象
		// 在执行具体的数据库操作之前需要为占位符设置内容，按照顺序设置
		ResultSet rs = pstmt.executeQuery(sql);
		while (rs.next()) { // 现在如果发现还有数据行未输出
			int nid = rs.getInt(1);
			String title = rs.getString(2);
			int read = rs.getInt(3);
			double price = rs.getDouble(4);
			String content = rs.getString(5);
			Date pubdate = rs.getDate(6);
			System.out.println(nid + "、" + title + "、" + read
			+ "、" + price + "、" + content + "、" + pubdate);
		}
		System.out.println("更新操作影响的数据行数：" + count);
		conn.close();  //数据库的连接资源有限一定要关闭
	}
}

2、根据id进行数据查询：

package cn.mldn.demo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Date;
public class JDBCDemo {
	private static final String DATABASE_DRIVER = "oracle.jdbc.driver.OracleDriver";
	private static final String DATABASE_URL = "jdbc:oracle:thin:@localhost:1521:mldn";
	private static final String DATABASE_USER = "scott";
	private static final String DATABASE_PASSWORD = "tiger";
	public static void main(String[] args) throws Exception{
		String sql = "SELECT nid,title,read,price,content,pudate FROM news WHERE nid=?";
		Connection conn = null;  //每一个Connection接口对象描述的就是一个用户连接
		Class.forName(DATABASE_DRIVER); //向容器之中加载数据库驱动程序
		conn = DriverManager.getConnection(DATABASE_URL, DATABASE_USER, DATABASE_PASSWORD);
		PreparedStatement pstmt = conn.prepareStatement(sql); // 创建数据库的操作对象
		pstmt.setInt(1,5); // 设置nid的数据
		// 在执行具体的数据库操作之前需要为占位符设置内容，按照顺序设置
		ResultSet rs = pstmt.executeQuery(sql);
		while (rs.next()) { // 现在如果发现还有数据行未输出
			int nid = rs.getInt(1);
			String title = rs.getString(2);
			int read = rs.getInt(3);
			double price = rs.getDouble(4);
			String content = rs.getString(5);
			Date pubdate = rs.getDate(6);
			System.out.println(nid + "、" + title + "、" + read
			+ "、" + price + "、" + content + "、" + pubdate);
		}
		System.out.println("更新操作影响的数据行数：" + count);
		conn.close();  //数据库的连接资源有限一定要关闭
	}
}

3、在进行全部数据查询的时候如果返回的内容过多则一会造成内存的大量占用，那么此时可以使用分页的形式实现数据的查询处理（模糊查询）。

package cn.mldn.demo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Date;
public class JDBCDemo {
	private static final String DATABASE_DRIVER = "oracle.jdbc.driver.OracleDriver";
	private static final String DATABASE_URL = "jdbc:oracle:thin:@localhost:1521:mldn";
	private static final String DATABASE_USER = "scott";
	private static final String DATABASE_PASSWORD = "tiger";
	public static void main(String[] args) throws Exception{
		int currentPage = 2; // 当前页
		int lineSize = 5 ; // 每页显示的数据行
		String column = "title" ; // 模糊查询列
		String keyWord = "MLDN"; // 查询关键字
		String sql = "SELECT * FROM ( "
		+ " SELECT nid,title,read,price,content,pudate,ROWNUM rn "
		+ " FROM news WHERE " + column + " LIKE ? AND ROWNUM<=? ORDER BY nid) temp "
		+ " WHERE temp.rn>?";
		Connection conn = null;  //每一个Connection接口对象描述的就是一个用户连接
		Class.forName(DATABASE_DRIVER); //向容器之中加载数据库驱动程序
		conn = DriverManager.getConnection(DATABASE_URL, DATABASE_USER, DATABASE_PASSWORD);
		PreparedStatement pstmt = conn.prepareStatement(sql); // 创建数据库的操作对象
		pstmt.setString(1,"%" + keyWord + "%");
		pstmt.setInt(2,currentPage * lineSize);
		pstmt.setInt(3,(currentPage - 1) * lineSize);
		// 在执行具体的数据库操作之前需要为占位符设置内容，按照顺序设置
		ResultSet rs = pstmt.executeQuery(sql);
		while (rs.next()) { // 现在如果发现还有数据行未输出
			int nid = rs.getInt(1);
			String title = rs.getString(2);
			int read = rs.getInt(3);
			double price = rs.getDouble(4);
			String content = rs.getString(5);
			Date pubdate = rs.getDate(6);
			System.out.println(nid + "、" + title + "、" + read
			+ "、" + price + "、" + content + "、" + pubdate);
		}
		System.out.println("更新操作影响的数据行数：" + count);
		conn.close();  //数据库的连接资源有限一定要关闭
	}
}

4、统计指定关键字标题的新闻数量

package cn.mldn.demo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class JDBCDemo {
	private static final String DATABASE_DRIVER = "oracle.jdbc.driver.OracleDriver";
	private static final String DATABASE_URL = "jdbc:oracle:thin:@localhost:1521:mldn";
	private static final String DATABASE_USER = "scott";
	private static final String DATABASE_PASSWORD = "tiger";
	public static void main(String[] args) throws Exception{
		String column = "title" ; // 模糊查询列
		String keyWord = "MLDN"; // 查询关键字
		String sql = "SELECT COUNT(*) FROM news WHERE " + column + " LIKE ?";
		Connection conn = null;  //每一个Connection接口对象描述的就是一个用户连接
		Class.forName(DATABASE_DRIVER); //向容器之中加载数据库驱动程序
		conn = DriverManager.getConnection(DATABASE_URL, DATABASE_USER, DATABASE_PASSWORD);
		PreparedStatement pstmt = conn.prepareStatement(sql); // 创建数据库的操作对象
		pstmt.setString(1,"%" + keyWord + "%");
		// 在执行具体的数据库操作之前需要为占位符设置内容，按照顺序设置
		ResultSet rs = pstmt.executeQuery(sql);
		if (rs.next()) {
			long count = rs.getLong(1);
			System.out.println("符合条件的数据量：" + count);
		}
		conn.close();  //数据库的连接资源有限一定要关闭
	}
}

在使用COUNT()函数做统计查询的时候一定会返回查询结果，如果表中没有数据则返回0。