使用JDBC增删改查-优快云博客

本文介绍了一个简单的Java程序如何实现对MySQL数据库的基本操作，包括插入、更新、查询和删除（CRUD）。通过具体的代码示例展示了如何使用PreparedStatement来执行SQL语句，并提供了连接数据库的方法。

insert 增加

update 更新

select 查询

delete 删除

首先先需要写个连接类

protected Connection conn;
protected PreparedStatement ps;
protected ResultSet rs;

/**
* 连接
*/
public void OpenConn() {
  try {
   Class.forName("com.mysql.jdbc.Driver");
   conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/petshop","root","0000");
  } catch (Exception e) {
   e.printStackTrace();
  }
}

/**
* 关闭连接
*/
public void Close(ResultSet rs,PreparedStatement ps,Connection con) {
  if (rs!=null) {
   try {
    rs.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
  if (ps!=null) {
   try {
    ps.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
  if (conn!=null) {
   try {
    conn.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
}

private static int insert(Student student) {
    Connection conn = getConn();
    int i = 0;
    String sql = "insert into students (Name,Sex,Age) values(?,?,?)";
    PreparedStatement pstmt;
    try {
        pstmt = (PreparedStatement) conn.prepareStatement(sql);
        pstmt.setString(1, student.getName());
        pstmt.setString(2, student.getSex());
        pstmt.setString(3, student.getAge());
        i = pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return i;
}

private static int update(Student student) {
    Connection conn = getConn();
    int i = 0;
    String sql = "update students set Age='" + student.getAge() + "' where Name='" + student.getName() + "'";
    PreparedStatement pstmt;
    try {
        pstmt = (PreparedStatement) conn.prepareStatement(sql);
        i = pstmt.executeUpdate();
        System.out.println("resutl: " + i);
        pstmt.close();
        conn.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return i;
}

private static Integer getAll() {
    Connection conn = getConn();
    String sql = "select * from students";
    PreparedStatement pstmt;
    try {
        pstmt = (PreparedStatement)conn.prepareStatement(sql);
        ResultSet 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();
    }
    return null;
}