数据库查询的几种方法

1、Statement接口查询
这通常是比较常用的。

Statement statement  =  connection.createStatement();
ResultSet rs  =  getResultSet( " SELECT * FROM TB_STUDENTS WHERE AGE=20 " );
...
while  (rs.next()) 
       ... { 
       ...
      }  

 

2、PreparedStatement接口查询
如果需要多次执行SQL语句，那么PreparedStatement是首选。因为他包含了一个已经被编译的SQL语句，提高了程序的效率和性能。
下面分别对TB_CUSTOMERS表，进行查询、插入、修改、删除的例子。
查询：

 

public   void  preQueryCustomersTB( int  ID)
         ... {
            try
                ...{
                    PreparedStatement pstmt = connection.prepareStatement("SELECT * FROM TB_CUSTOMERS WHERE ID = ?;");
                    pstmt.setInt(1, ID);
                    pstmt.executeQuery();
                    connection.commit();
                    pstmt.clearParameters();
                    
                } catch (SQLException e)
                ...{
                    e.printStackTrace();
                }
        }

插入：

public   boolean  preInsertCustomersTB(String company,String name,String address,String email,String phone,String other)
         ... {
            try
                ...{
                    PreparedStatement pstmt = connection.prepareStatement("INSERT INTO TB_CUSTOMERS (COMPANY,NAME,ADDRESS,EMAIL,PHONE,OTHER) VALUES (?,?,?,?,?,?) ;");
                    pstmt.setString(1, company);
                    pstmt.setString(2, name);
                    pstmt.setString(3, address);
                    pstmt.setString(4, email);
                    pstmt.setString(5, phone);
                    pstmt.setString(6, other);
                    pstmt.execute();
                    connection.commit();
                    
                } catch (SQLException e)
                ...{
                    // TODO 自动生成 catch 块
                    e.printStackTrace();
                    return false;
                }
            return true;
        }

修改：

public   boolean  preUpdataCustomersTB( int  id,String company,String name,String address,String email,String phone,String other)
         ... {
            try
                ...{
                    PreparedStatement pstmt = connection.prepareStatement(
                            "update TB_CUSTOMERS SET company = ?, name = ?, address = ?, email = ?, phone = ?, other = ? WHERE ID = ?;");
                    pstmt.setString(1, company);
                    pstmt.setString(2, name);
                    pstmt.setString(3, address);
                    pstmt.setString(4, email);
                    pstmt.setString(5, phone);
                    pstmt.setString(6, other);
                    pstmt.setInt(7, id);
                    pstmt.execute();
                    connection.commit();
                    
                } catch (SQLException e)
                ...{
                    // TODO 自动生成 catch 块
                    e.printStackTrace();
                    return false;
                }
            return true;
        }

删除：

public   void  preDeletCustomersTB( int  RowID)
         ... {
            try
                ...{
                    PreparedStatement pstmt = connection.prepareStatement("delete FROM TB_CUSTOMERS where ID = ?;");
                    pstmt.setInt(1, RowID);
                    pstmt.execute();
                    connection.commit();
                    
                } catch (SQLException e)
                ...{
                    // TODO 自动生成 catch 块
                    e.printStackTrace();
                }
        }

 

3、CallableStatement接口查询 
CallableStatement接口提供了通过JDBC API调用SQL存储过程的标准途径。
使用IN参数的例子：

public   void  callSetPlayer(String player)
     ... {
        try
            ...{
                CallableStatement cStatement = connection.prepareCall("{CALL setPlayName(?}");
                cStatement.setString(1, "John Doe");
                cStatement.execute();
            } catch (SQLException e)
            ...{
                e.printStackTrace();
            }
    }

使用OUT参数的例子：

public  String callGetPlayer()
         ... {
            try
                ...{
                    CallableStatement cStatement = connection.prepareCall("{CALL getPlayName(?}");
                    cStatement.registerOutParameter(1, java.sql.Types.VARCHAR);
                    cStatement.execute();
                    String name = cStatement.getString(1);
                    //...
                    return name;
                } catch (SQLException e)
                ...{
                    e.printStackTrace();
                    return null;
                }
        }