JDBC增删改查 头歌实践教学Java

 

 

package step2;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class AddData {
    public  void run() throws SQLException, ClassNotFoundException {
        // ---------------------Begin------------------------
        //获取数据库连接
        Connection connection = this.getConnection();
        //获取statement对象
        Statement statement = connection.createStatement();
        //向staff员工表中插入id为5，名字为:李敏，性别为：女，年龄为：21的数据
        String sql = "INSERT INTO `company`.`staff` (`id`, `name`, `sex`, `age`) VALUES ('5', '李敏', '女', '21')";
        statement.executeUpdate(sql);
        //关闭数据库连接，释放资源
        statement.close();
        connection.close();
// ---------------------End------------------------
    }
    public   Connection getConnection() throws ClassNotFoundException, SQLException {
        // ---------------------Begin------------------------
        //获取数据库连接，账户名：root,密码：123123, 数据库名：company,MySQL版本为8.0.20
        Class.forName("com.mysql.cj.jdbc.Driver");
        String username = "root";
        String password = "123123";
        String url = "jdbc:mysql://localhost:3306/company?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true";
        return DriverManager.getConnection(url, username, password);
// ---------------------End------------------------
    }
}

package step3;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class DelData {
    public  void run() throws SQLException, ClassNotFoundException {
        // ---------------------Begin------------------------
        //获取数据库连接
        Connection connection = this.getConnection();
        //获取statement对象
        Statement statement = connection.createStatement();
        //删除staff员工表中id为4的记录
         String sql = "delete from staff where id = 4";
         statement.executeUpdate(sql);
        //关闭数据库连接，释放资源
        statement.close();
        connection.close();
 
    // ---------------------End------------------------
    }
    public   Connection getConnection() throws ClassNotFoundException, SQLException {
        // ---------------------Begin------------------------
        //获取数据库连接，账户名：root,密码：123123, 数据库名：company,MySQL版本为8.0.20
        Class.forName("com.mysql.cj.jdbc.Driver");
        String username = "root";
        String password = "123123";
        String url = "jdbc:mysql://localhost:3306/company?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true";
        return DriverManager.getConnection(url, username, password);
// ---------------------End------------------------
    }
}

package step4;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class EditData {
    public  void run() throws SQLException, ClassNotFoundException {
        // ---------------------Begin------------------------
        //获取数据库连接
        Connection connection = this.getConnection();
        //获取statement对象
        Statement statement = connection.createStatement();
        //修改staff表中id=3的性别为男
        String sql =  "update staff set sex = '男' where id = 3";
        statement.executeUpdate(sql);
        //关闭数据库连接，释放资源
        statement.close();
        connection.close();
// ---------------------End------------------------
    }
    public   Connection getConnection() throws ClassNotFoundException, SQLException {
        // ---------------------Begin------------------------
        //获取数据库连接，账户名：root,密码：123123, 数据库名：company,MySQL版本为8.0.20
        Class.forName("com.mysql.cj.jdbc.Driver");
        String username = "root";
        String password = "123123";
        String url = "jdbc:mysql://localhost:3306/company?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true";
        return DriverManager.getConnection(url, username, password);
// ---------------------End------------------------
    }
}

package step5;
import java.sql.*;
public class QuerryData {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        QuerryData querryData = new QuerryData();
        // ---------------------Begin------------------------
      //获取数据库连接
        try{
      Connection conn = querryData.getConnection();
        //获取statement对象    
      Statement sta = conn.createStatement();
        //遍历输出staff表中的数据
String sql = "SELECT id, name, sex, age from staff";
//"SELECT id, first, last, age FROM student";
 ResultSet rs = sta.executeQuery(sql);
 while(rs.next()){
                // 通过字段检索
                int id  = rs.getInt("id");
                String name = rs.getString("name");
                String sex = rs.getString("sex");
              String age = rs.getString("age");
                // 输出数据
                System.out.print(id+",");
                System.out.print(name+",");
                System.out.print(sex+",");
                System.out.println(age);       
            }
        //关闭数据库连接，释放资源
  rs.close();
    sta.close();
        conn.close();
        }catch(Exception e){
          e.printStackTrace();
        }

// ---------------------End------------------------
    }
  public   Connection getConnection() throws ClassNotFoundException, SQLException {
      // ---------------------Begin------------------------
      //获取数据库连接，账户名：root,密码：123123, 数据库名：company,MySQL版本为8.0.20
       Class.forName("com.mysql.cj.jdbc.Driver");
      String username = "root";
      String password = "123123";
      String url = "jdbc:mysql://localhost:3306/company?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true";
    return DriverManager.getConnection(url, username, password);

// ---------------------End------------------------
  }
}