开发者学堂课程【【名师课堂】Java 高级开发：【第17个代码模型】使用 PreparedStatement 操作数据库（PrepareStatement 查询案例)】学习笔记，与课程紧密联系，让用户快速学习知识。
课程地址：https://developer.aliyun.com/learning/course/372/detail/4653
【第17个代码模型】使用 PreparedStatement 操作数据库（PrepareStatement 查询案例)
PreparedStatement接口查询（核心）
由于在实际的开发中心 PreparedStatement 接口的使用频率非常高，所以对于此接口的查询操作特别重要，下列为最基础的查询处理模型：
1、查询全部
Class.forName(DBDRIVER);//进行数据库驱动的加载
Connection conn = DriverManager.getConnection(DBURL, DBUSER, DBPASSIString sql = " SELECT mid,name ,age,birthday, note FROM member" ;
Preparedstatement pstmt = conn.preparestatement(sql);
Resultset rs = pstmt.executeQuery() ;
while (rs.next()）i
int mid = rs.getInt(1);
string name = rs.getstring(2);int age = rs.getInt(3) ;
Date birthday = rs.getDate(4) ;string note = rs.getstring(5);
system.out.println(mid +"、" + name + "、 "+age +"、"+ birthd
}
2、id 查询
public static final string DBUSER = "scott";
public static fina1 string DBPASSwORD = "tiger";
public static void main(String[] args)throws Exception {
Class.forName(DBDRIVER);//进行数据库驱动的加载
Connection conn = DriverManager.getConnection(DBURL,DBUSER, DBPASSNORD);string sql = " SELECT mid,name,age,birthday,note FRoM member WHERE mid=?" ;Preparedstatement pstmt = conn.preparestatement(sql);
pstmt.setInt(1,50);l
ResultSet rs = pstmt.executeQuery( ;while (rs.next()) i
int mid = rs.getInt(1) ;
string name = rs.getstring(2);int age = rs.getInt(3) ;
Date birthday = rs.getDate(4);string note = rs.getstring(5);
System.out.printIn(mid +" " +name + "、" +age +"." + birthday + ". " +note)
conn.close();
3、模糊查询处理
public static final string DBPASSWORD = "tiger";
public static void main(String[] args) throws Exception {
string column = "name" ; // 在那个列上执行模糊查询
String keyWord = "李" ; // 关键字
Class.forName ( DBDRIVER); // 进行数据库驱动的加载
Connection conn = DriverManager .getConnection( DBURL, DBUSER, DBPASS
//使用“?填充的占位符只有数据才可以使用，而对于列是无法使用的
String sq1 = " SELECT mid, name，age , birthday，note F ROM member WHERE
Preparedstatement pstmt = conn . prepareStatement(sq1) ;
pstmt. setInt(1, 50);
ResultSet rs = pstmt. executeQuery() ;
while (rs .next()) {
int mid = rs.getInt(1) ;
4、分页查询
Class.forName(DBDRIVER);//进行数据库驱动的加致
Connectionconn= DriverManager.getConnection(DBURL,DBUSER，DBPASSNORD);1/使用“?”填充的占位符只有数据才可以使用,而对于列是无法使用的
String sql = "SELECT * FROM（"
+"SELECT mid,name, age,birthday, note,ROWNUM rn "
+"FROM member WHERE " + column + " LIKE ? AND ROWNUM<=? ) temp"
+" WHERE temp.rn>?";
Preparedstatementpstmt= conn.preparestatement(sql);pstmt.setstring(1,"%"+keyword+"%");
pstmt.setInt(2,currentPage* linesize);
pstmt.setInt(3，(currentPage - 1)* linesize); IResultset rs =pstmt.executeQuery(;
while (rs.next()) {
int mid =rs.getInt(1) ;
string name = rs.getstring(2);int age = rs.getInt(3);
Date birthday = rs.getDate(4) ;String note = rs.getstring(5);
System.out.println(mid + " " + name + "、" +age + "." +birthday + " " + note);
5、统计查询
public static final String DBUSER = "scott";
public static final string DBPASSWORD = "tiger";
public static void main(String[] args) throws Exception {
string column = "name” ; 1/在那个列上执行模糊查询
string keyword =“张”;1/关键字
Class.forName(DBDRIVER);//进行数据库驱动的加载
connection conn = DriverManager.getconnection(DBURL，DBuSER，DBPASSIORD);// 使用“?”填充的占位符只有数据才可以使用,而对于列是无法使用的
string sql = " SELECT COUNT(*） FROM member WHERE" + column +" LIKE ? " ;Preparedstatement pstmt = conn.prepareStatement(sql) ;
pstmt.setstring(1,"%"+keyword+"%");
Resultset rs = pstmt.executeQuery(O;if (rs.next())i
longl count = rs.getLong(1) ;system.out. println(count);
}
conn.close();
以上所给的几个开发代码是后续开发项目的核心基础部分，请牢固掌握
