2021-04-20 08:27  阅读(80)
文章分类:Java JDBC 教程 文章标签:JavaJDBC
©  原文作者:易百教程 原文地址:https://www.yiibai.com/jdbc

在本教程将演示如何在JDBC应用程序中,从数据库表中查询数据记录, 在查询选择记录时使用WHERE子句添加其他条件。 在执行以下示例之前,请确保您已经准备好以下操作:

  • 具有数据库管理员权限,以在给定模式的数据库表中查询数据记录。 要执行以下示例,需要用实际用户名和密码替换这里用户名(username)和密码(password)。
  • MySQL或数据库已启动并运行。

所需步骤

使用JDBC应用程序查询表中数据记录需要以下步骤:

  • 导入包:需要包含包含数据库编程所需的JDBC类的包。 大多数情况下,使用import java.sql.*就足够了。
  • 注册JDBC驱动程序:需要初始化驱动程序,以便可以程序中打开数据库的通信通道。
  • 打开连接:需要使用DriverManager.getConnection()方法来创建一个Connection对象,它表示与数据库服务器的物理连接。
  • 执行查询:需要使用类型为Statement的对象来构建和提交SQL语句,以从表中获取满足给定条件的记录。 查询语句使用WHERE子句来选择记录。
  • 清理环境:需要明确地关闭所有数据库资源,而不依赖于JVM的垃圾收集。

示例代码

复制以下示例代码保存到文件:SelectWhereClause.java中,然后编译并运行如下 -

    //STEP 1. Import required packages
    // See more detail at http://www.yiibai.com/jdbc/
    
    import java.sql.*;
    
    public class SelectWhereClause {
       // JDBC driver name and database URL
       static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
       static final String DB_URL = "jdbc:mysql://localhost/jdbc_db";
    
       //  Database credentials
       static final String USER = "root";
       static final String PASS = "123456";
    
       public static void main(String[] args) {
       Connection conn = null;
       Statement stmt = null;
       try{
          //STEP 2: Register JDBC driver
          Class.forName("com.mysql.jdbc.Driver");
    
          //STEP 3: Open a connection
          System.out.println("Connecting to a selected database...");
          conn = DriverManager.getConnection(DB_URL, USER, PASS);
          System.out.println("Connected database successfully...");
    
          //STEP 4: Execute a query
          System.out.println("Creating statement...");
          stmt = conn.createStatement();
    
          // Extract records without any condition.
          System.out.println("Fetching records without condition...");
          String sql = "SELECT id, first, last, age FROM student";
          ResultSet rs = stmt.executeQuery(sql);
    
          while(rs.next()){
             //Retrieve by column name
             int id  = rs.getInt("id");
             int age = rs.getInt("age");
             String first = rs.getString("first");
             String last = rs.getString("last");
    
             //Display values
             System.out.print("ID: " + id);
             System.out.print(", Age: " + age);
             System.out.print(", First: " + first);
             System.out.println(", Last: " + last);
          }
    
          // Select all records having ID equal or greater than 101
          System.out.println("Fetching records with condition...");
          sql = "SELECT id, first, last, age FROM student" +
                       " WHERE id >= 103 ";
          rs = stmt.executeQuery(sql);
    
          while(rs.next()){
             //Retrieve by column name
             int id  = rs.getInt("id");
             int age = rs.getInt("age");
             String first = rs.getString("first");
             String last = rs.getString("last");
    
             //Display values
             System.out.print("ID: " + id);
             System.out.print(", Age: " + age);
             System.out.print(", First: " + first);
             System.out.println(", Last: " + last);
          }
          rs.close();
       }catch(SQLException se){
          //Handle errors for JDBC
          se.printStackTrace();
       }catch(Exception e){
          //Handle errors for Class.forName
          e.printStackTrace();
       }finally{
          //finally block used to close resources
          try{
             if(stmt!=null)
                conn.close();
          }catch(SQLException se){
          }// do nothing
          try{
             if(conn!=null)
                conn.close();
          }catch(SQLException se){
             se.printStackTrace();
          }//end finally try
       }//end try
       System.out.println("Goodbye!");
    }//end main
    }//end JDBCExample
    

编译上面代码,如下 -

    F:\worksp\jdbc> javac -Djava.ext.dirs=F:\worksp\jdbc\libs SelectWhereClause.java
    

执行上面代码,如下 -

    ## 可先创建表或插入数据 -
    ## F:\worksp\jdbc> java -Djava.ext.dirs=F:\worksp\jdbc\libs InsertRecords
    
    F:\worksp\jdbc>java -Djava.ext.dirs=F:\worksp\jdbc\libs SelectWhereClause
    Connecting to a selected database...
    Fri Jun 02 00:22:10 CST 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
    Connected database successfully...
    Creating statement...
    Fetching records without condition...
    ID: 100, Age: 18, First: C++, Last: Li
    ID: 101, Age: 25, First: Python, Last: Py
    ID: 102, Age: 30, First: Ruby, Last: Ru
    ID: 103, Age: 28, First: Java, Last: Ja
    Fetching records with condition...
    ID: 103, Age: 28, First: Java, Last: Ja
    Goodbye!
    
    F:\worksp\jdbc>
    

在执行上面语句后,可以看到只有 ID 大于等于 103 的记录查询显示出来。

点赞(0)
版权归原创作者所有,任何形式转载请联系作者; Java 技术驿站 >> JDBC WHERE子句条件实例
上一篇
JDBC删除数据实例
下一篇
JDBC Like子句实例