JAVA访问数据库之增删改查(CRUD)

上一篇博客介绍了如何使用JAVA连接数据库，那么这一篇将继续为大家介绍如何使用JDBC对数据库的增删改查(CRUD)操作。
这一篇博客中的示例将使用上一篇中生成的H2数据库文件。

查询

查询在数据库的操作中是很重要的，我们把数据保存在数据库中，就是为了我们在需要的时候能够快速、高效的查询出来。

/**
    * 查询
    * 
    * @throws Exception
    */
   public void query() throws Exception
   {
      Connection conn = getConnection();
      Statement statement = conn.createStatement();
      ResultSet resultSet = statement
            .executeQuery("SELECT USER_ID, USER_NAME FROM USER_INFO");
      System.out.printf("%20s %50s\n", "USER_ID", "USER_NAME");
      // 遍历结果集
      while (resultSet.next())
      {
         System.out.printf("%20s %50s\n", resultSet.getString("USER_ID"),
               resultSet.getString("USER_NAME"));
      }
      resultSet.close();
      statement.close();
      conn.close();
   }

   /**
    * 通过用户编号查询
    * 
    * @param userId
    * @throws Exception
    */
   public void queryById(String userId) throws Exception
   {
      Connection conn = getConnection();
      // 动态参数使用PreparedStatement，使用Statement存在SQL注入风险
      PreparedStatement statement = conn
            .prepareStatement("SELECT USER_ID, USER_NAME FROM USER_INFO WHERE USER_ID = ?");
      statement.setString(1, userId);
      ResultSet resultSet = statement.executeQuery();
      System.out.printf("%20s %50s\n", "USER_ID", "USER_NAME");
      // 遍历结果集
      while (resultSet.next())
      {
         System.out.printf("%20s %50s\n", resultSet.getString("USER_ID"),
               resultSet.getString("USER_NAME"));
      }
      resultSet.close();
      statement.close();
      conn.close();
   }

修改

在这里，我讲增删改作为一类，同属于对数据库的修改操作，在例子中，也会发现，我们使用的方法完全相同，仅仅是SQL的区别。

/**
    * 修改
    * 
    * @param userId
    * @param userName
    * @throws Exception
    */
   public void update(String userId, String userName) throws Exception
   {
      Connection conn = getConnection();
      // 动态参数使用PreparedStatement，使用Statement存在SQL注入风险
      PreparedStatement statement = conn
            .prepareStatement("UPDATE USER_INFO SET USER_NAME = ? WHERE USER_ID = ?");
      statement.setString(1, userName);
      statement.setString(2, userId);
      System.out.println("受影响的记录条数为：" + statement.executeUpdate());
      statement.close();
      conn.close();
   }

   /**
    * 清空
    */
   public void clear() throws Exception
   {
      Connection conn = getConnection();
      Statement statement = conn.createStatement();
      System.out.println("受影响的记录条数为："
            + statement.executeUpdate("DELETE USER_INFO"));
      statement.close();
      conn.close();
   }

完整示例

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class JDBCCRUDemo
{

   /**
    * 获得数据库连接
    * 
    * @return
    * @throws Exception
    */
   public Connection getConnection() throws Exception
   {
      Class.forName("org.h2.Driver");
      return DriverManager.getConnection("jdbc:h2:h2.db", "test", "123");
   }

   /**
    * 查询
    * 
    * @throws Exception
    */
   public void query() throws Exception
   {
      Connection conn = getConnection();
      Statement statement = conn.createStatement();
      ResultSet resultSet = statement
            .executeQuery("SELECT USER_ID, USER_NAME FROM USER_INFO");
      System.out.printf("%20s %50s\n", "USER_ID", "USER_NAME");
      // 遍历结果集
      while (resultSet.next())
      {
         System.out.printf("%20s %50s\n", resultSet.getString("USER_ID"),
               resultSet.getString("USER_NAME"));
      }
      resultSet.close();
      statement.close();
      conn.close();
   }

   /**
    * 通过用户编号查询
    * 
    * @param userId
    * @throws Exception
    */
   public void queryById(String userId) throws Exception
   {
      Connection conn = getConnection();
      // 动态参数使用PreparedStatement，使用Statement存在SQL注入风险
      PreparedStatement statement = conn
            .prepareStatement("SELECT USER_ID, USER_NAME FROM USER_INFO WHERE USER_ID = ?");
      statement.setString(1, userId);
      ResultSet resultSet = statement.executeQuery();
      System.out.printf("%20s %50s\n", "USER_ID", "USER_NAME");
      // 遍历结果集
      while (resultSet.next())
      {
         System.out.printf("%20s %50s\n", resultSet.getString("USER_ID"),
               resultSet.getString("USER_NAME"));
      }
      resultSet.close();
      statement.close();
      conn.close();
   }

   /**
    * 添加
    * 
    * @param userId
    * @param userName
    * @throws Exception
    */
   public void insert(String userId, String userName) throws Exception
   {
      Connection conn = getConnection();
      // 动态参数使用PreparedStatement，使用Statement存在SQL注入风险
      PreparedStatement statement = conn
            .prepareStatement("INSERT INTO USER_INFO(USER_ID, USER_NAME) VALUES(?, ?)");
      statement.setString(1, userId);
      statement.setString(2, userName);
      System.out.println("受影响的记录条数为：" + statement.executeUpdate());
      statement.close();
      conn.close();
   }

   /**
    * 修改
    * 
    * @param userId
    * @param userName
    * @throws Exception
    */
   public void update(String userId, String userName) throws Exception
   {
      Connection conn = getConnection();
      // 动态参数使用PreparedStatement，使用Statement存在SQL注入风险
      PreparedStatement statement = conn
            .prepareStatement("UPDATE USER_INFO SET USER_NAME = ? WHERE USER_ID = ?");
      statement.setString(1, userName);
      statement.setString(2, userId);
      System.out.println("受影响的记录条数为：" + statement.executeUpdate());
      statement.close();
      conn.close();
   }

   /**
    * 清空
    */
   public void clear() throws Exception
   {
      Connection conn = getConnection();
      Statement statement = conn.createStatement();
      System.out.println("受影响的记录条数为："
            + statement.executeUpdate("DELETE USER_INFO"));
      statement.close();
      conn.close();
   }

   public static void main(String[] args) throws Exception
   {
      JDBCCRUDemo demo = new JDBCCRUDemo();
      demo.insert("id111", "name111");
      demo.insert("id222", "name222");
      demo.insert("id333", "name333");
      demo.insert("id444", "name444");
      demo.insert("id555", "name555");
      demo.query();
      demo.queryById("id444");
      demo.update("id222", "name1234");
      demo.query();
      demo.clear();
   }
}

运行结果：
受影响的记录条数为：1
受影响的记录条数为：1
受影响的记录条数为：1
受影响的记录条数为：1
受影响的记录条数为：1
             USER_ID                                          USER_NAME
               id111                                            name111
               id222                                            name222
               id333                                            name333
               id444                                            name444
               id555                                            name555
             USER_ID                                          USER_NAME
               id444                                            name444
受影响的记录条数为：1
             USER_ID                                          USER_NAME
               id111                                            name111
               id333                                            name333
               id444                                            name444
               id555                                            name555
               id222                                           name1234
受影响的记录条数为：5