JDBC 的查询操作、添加操作、修改操作、删除操作

package com.atguigu.jdbc;

import org.junit.Test;

import java.sql.*;

public class JDBCDemo1 {

    //4删除操作
    @Test
    public void testDelete(){
        Connection connection = null;
        Statement statement =null;
        try{
            //加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //创建数据库连接
            connection = DriverManager.getConnection("jdbc:mysql:///db2","root","666666");
            //编写sql语句，创建statement对象执行sql语句
            String sql= "delete from dept where did=4";
            statement = connection.createStatement();
            //执行executeUpdate方法进行添加操作，返回影响行数
            int row = statement.executeUpdate(sql);
            System.out.println(row);

        }catch(Exception e){

        }finally{
            //关闭资源
            try {
                statement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    //3修改操作
    @Test
    public void testUpdate(){
        Connection connection = null;
        Statement statement =null;
        try{
            //加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //创建数据库连接
            connection = DriverManager.getConnection("jdbc:mysql:///db2","root","666666");
            //编写sql语句，创建statement对象执行sql语句
            String sql= "update dept set dname='对外交流部' where did=4";
            statement = connection.createStatement();
            //执行executeUpdate方法进行添加操作，返回影响行数
            int row = statement.executeUpdate(sql);
            System.out.println(row);

        }catch(Exception e){

        }finally{
            //关闭资源
            try {
                statement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    //2添加操作
    @Test
    public void testInsert(){
        Connection connection = null;
        Statement statement =null;
        try{
            //加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //创建数据库连接
            connection = DriverManager.getConnection("jdbc:mysql:///db2","root","666666");
            //编写sql语句，创建statement对象执行sql语句
            String sql= "insert into dept values(4,'外交部')";
            statement = connection.createStatement();
            //执行executeUpdate方法进行添加操作，返回影响行数
            int row = statement.executeUpdate(sql);
            System.out.println(row);

        }catch(Exception e){

        }finally{
            //关闭资源
            try {
                statement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

   //1查询操作
    @Test
    public void testSelect(){
        Connection conn = null;
        Statement statement =null;
        ResultSet rs = null;
        try {
            //1加载数据驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2创建数据库连接
            //第一个参数url：数据库地址 jdbc:mysql://数据库ip:端口号/数据库名称
            //如果连接数据库是本地数据库，并且端口号是3306 简写jdbc:mysql:///db2
            //第二个参数user:用户名
            //第三个参数 password: 密码
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db2","root","666666");
            //3编写sql语句，创建Statement，执行sql语句
            String sql =  "select did,dname from dept";
            //创建Statement
            statement = conn.createStatement();
            //statement执行sql语句
            rs = statement.executeQuery(sql);
            //4查询返回结果，遍历显示内容
            while(rs.next()){
                int did = rs.getInt("did");
                String dname = rs.getString("dname");
                System.out.println("did:"+did);
                System.out.println("dname:"+dname);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //关闭资源
            try {
                rs.close();
                statement.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}