Java知识点总结（JDBC-连接步骤及CRUD）

Java知识点总结（JDBC-连接步骤及CRUD）

@(Java知识点总结)[Java, JDBC]

连接数据库步骤

依序关闭使用的对象连接：

• ResultSet -> Statement -> Connection

CRUD操作

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
 
 
public class StudentDao {
 
  private static Connection getConn() {
      String driver = "com.mysql.jdbc.Driver";
      String url = "jdbc:mysql://localhost:3306/test";
      String username = "root";
      String password = "123";
      Connection conn = null;
      try {
          Class.forName(driver); //classLoader,加载对应驱动
          //建立连接（连接对象内部包含了Socket对象，是一个远程连接。比较耗时！这是Connection对象管理的一个要点！）
          //真正开发中，为了提高效率，都会使用连接池来管理连接对象！
          conn = (Connection) DriverManager.getConnection(url, username, password);
      } catch (ClassNotFoundException e) {
          e.printStackTrace();
      } catch (SQLException e) {
          e.printStackTrace();
      }
      return conn;
  }
  
  private static int insert(User user) {
      Connection conn = getConn();
      int i = 0;
      String sql = "insert into users (Name,Sex,Age) values(?,?,?)";
      PreparedStatement pstmt = null;
      try {
          pstmt = (PreparedStatement) conn.prepareStatement(sql);
          pstmt.setString(1, user.getName());
          pstmt.setString(2, user.getSex());
          pstmt.setInt(3, user.getAge());
          i = pstmt.executeUpdate();
          pstmt.close();
          conn.close();
      } catch (SQLException e) {
          e.printStackTrace();
      }finally {
     try {
      pstmt.close();
     } catch (SQLException e) {
      e.printStackTrace();
     }
     try {
      conn.close();
     } catch (SQLException e) {
      e.printStackTrace();
     }
   }
      return i;
  }
  
  private static int update(User user) {
      Connection conn = getConn();
      int i = 0;
      String sql = "update users set Age='" + user.getAge() + "' where Name='" + user.getName() + "'";
      PreparedStatement pstmt = null;
      try {
          pstmt = (PreparedStatement) conn.prepareStatement(sql);
          i = pstmt.executeUpdate();
          System.out.println("resutl: " + i);
          pstmt.close();
          conn.close();
      } catch (SQLException e) {
          e.printStackTrace();
      }finally {
     try {
      pstmt.close();
     } catch (SQLException e) {
      e.printStackTrace();
     }
     try {
      conn.close();
     } catch (SQLException e) {
      e.printStackTrace();
     }
   }
      return i;
  }
  
  private static Integer getAll() {
      Connection conn = getConn();
      String sql = "select * from users";
      PreparedStatement pstmt =null;
      ResultSet  rs = null;
      try {
          pstmt = (PreparedStatement)conn.prepareStatement(sql);
          rs = pstmt.executeQuery();
          int col = rs.getMetaData().getColumnCount();
          System.out.println("============================");
          while (rs.next()) {
              for (int i = 1; i <= col; i++) {
                  System.out.print(rs.getString(i) + "\t");
                  if ((i == 2) && (rs.getString(i).length() < 8)) {
                      System.out.print("\t");
                  }
               }
              System.out.println("");
          }
              System.out.println("============================");
      } catch (SQLException e) {
          e.printStackTrace();
      }finally {
       try {
      rs.close();
     } catch (SQLException e1) {
      e1.printStackTrace();
     }
     try {
      pstmt.close();
     } catch (SQLException e) {
      e.printStackTrace();
     }
     try {
      conn.close();
     } catch (SQLException e) {
      e.printStackTrace();
     }
   }
      return null;
  }
  
  private static int delete(String name) {
      Connection conn = getConn();
      int i = 0;
      String sql = "delete from users where Name='" + name + "'";
      PreparedStatement pstmt = null;
      try {
          pstmt = (PreparedStatement) conn.prepareStatement(sql);
          i = pstmt.executeUpdate();
          System.out.println("resutl: " + i);
          pstmt.close();
          conn.close();
      } catch (SQLException e) {
          e.printStackTrace();
      }finally {
     try {
      pstmt.close();
     } catch (SQLException e) {
      e.printStackTrace();
     }
     try {
      conn.close();
     } catch (SQLException e) {
      e.printStackTrace();
     }
   }
      return i;
  }
  
  public static void main(String[] args) {
   getAll();
       insert(new User("张柏芝", "女", 43));
       getAll();
       update(new User("张三", "", 38));
       delete("Achilles");
       getAll();
  }
}

public class User {
 
  private int id;
  private int age;
  private String name ;
  private String sex;
  
  
  public User( String name, String sex,int age) {
   this();
   this.age = age;
   this.name = name;
   this.sex = sex;
  }
  public User() {
 
  }
  public int getAge() {
   return age;
  }
  public int getId() {
   return id;
  }
  public String getName() {
   return name;
  }
  public String getSex() {
   return sex;
  }
  public void setAge(int age) {
   this.age = age;
  }
  public void setId(int id) {
   this.id = id;
  }
  public void setName(String name) {
   this.name = name;
  }
  public void setSex(String sex) {
   this.sex = sex;
  }
  
}

执行结果：