JDBC基础操作

目录

1.数据库连接及简单增删改查

2.junit包Test注释下的增删改查

1.数据库连接及简单增删改查

a.导入jar包

        创建一个lib目录，然后将MySQL的jar包粘贴到lib目录下，之后单击右键选择Add as Library选项，点击确定后就能导入jar包。

b.数据库连接及增删改查的具体实现

步骤：

        1.加载驱动类(用反射的方式加载)

        2. 获取连接

        3. 获取数据库操作对象(命令执行对象)

        4. 执行sql（增删改查）

        5. 获取结果集对象(针对查询操作)

        6. 释放连接

public class Test1 {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
//        System.out.println("123");
        test1();
    }

    public static void test1() throws ClassNotFoundException, SQLException {
        Driver driver = null;
//        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        // 连接数据库
        String url = "jdbc:mysql://localhost:3306/0720_db?useSSL=false&characterEncoding=utf-8&serverTimezone=Asia/Shanghai";
        String user = "liu";
        String password = "123456";
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection con = DriverManager.getConnection(url,user,password);
               //添加
//        String sql = "insert into bank" +
//                "(account,money)" +
//                "values" +
//                "('小明',10000)";
        //修改
//        String sql = "update bank set account = '小绿'" +
//                "where bid = 3";
        //删除
//        String sql = "delete from bank where bid = 3";

//        statement = con.createStatement();
//        int count = statement.executeUpdate(sql);
//        System.out.println(count);
        //查询
        String sql = "select * from bank";
        statement = con.createStatement();
        resultSet = statement.executeQuery(sql);
        while (resultSet.next()){
            for (int i = 1; i < 3; i++) {
                System.out.print(resultSet.getObject(i)+" ");
            }
            System.out.println();
        }
        
        //关闭数据库
        con.close();
}

2.junit包Test注释下的增删改查

a.导入junit 的jar包

        步骤与MySQL的jar包导入一样

b.具体实现

package com.yzh.test2;


import org.junit.Test;

import java.sql.*;

public class MyTest {
    private String drive = "com.mysql.cj.jdbc.Driver";
    private String url = "jdbc:mysql://localhost:3306/0720_db?useSSL=false&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
    private String user = "liu";
    private String password = "123456";

    @Test
    public void insert(){
//        System.out.println("插入数据");
        Connection con = null;
        Statement st = null;
        String sql = "insert into bank" +
                "(account,money)" +
                "values" +
                "('李四',200)";
        try {
            Class.forName(drive);
            con = DriverManager.getConnection(url,user,password);
            st = con.createStatement();
            int count = st.executeUpdate(sql);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            if (st!=null){
                try {
                    st.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (con!=null){
                try {
                    con.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }

    }
    @Test
    public void update(){
//        System.out.println("修改数据");
        Connection con = null;
        Statement st = null;
        String sql = "update bank set account='小紫'" +
                "where bid = 4";
        try {
            Class.forName(drive);
            con = DriverManager.getConnection(url,user,password);
            st = con.createStatement();
            int count = st.executeUpdate(sql);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            if (st!=null){
                try {
                    st.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (con!=null){
                try {
                    con.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }

    }
    @Test
    public void delete(){
//        System.out.println("删除数据");
        Connection con = null;
        Statement st = null;
        String sql = "delete from bank where bid=4";
        try {
            Class.forName(drive);
            con = DriverManager.getConnection(url,user,password);
            st = con.createStatement();
            int count = st.executeUpdate(sql);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            if (st!=null){
                try {
                    st.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (con!=null){
                try {
                    con.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }

    }
    @Test
    public void select(){
//        System.out.println("插入数据");
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        String sql = "select * from bank";
        try {
            Class.forName(drive);
            con = DriverManager.getConnection(url,user,password);
            st = con.createStatement();
            rs = st.executeQuery(sql);
            while (rs.next()){
                for (int i = 1; i < 3; i++) {
                    System.out.print(rs.getObject(i)+" ");
                }
                System.out.println();
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            if (rs!=null){
                try {
                    rs.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (st!=null){
                try {
                    st.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (con!=null){
                try {
                    con.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }

    }
}