基础JDBC API使用

基本流程

import com.mysql.jdbc.Driver;
import org.junit.Test;

import java.sql.*;

public class Demo1 {
    @Test
    public void demo1() {
        //为了可以在代码块外进行资源释放,定义在try外部
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        //1.加载驱动
        try {
            //DriverManager.registerDriver(new Driver());
            //该方法注册两次驱动
            //应使用Class.forName()载入
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获得链接
            conn = DriverManager.getConnection("jdbc:mysql:///jdbctest", "root", "root");
            //驱动管理类 DriverManager
            //Connection 用于创建执行sql语句的对象
            //Statement createStatement/                     ===> 执行sql语句(可能造成sql注入漏洞)
            //PreparedStatement prepareStatement(String sql) ===> 预编译sql语句
            //CallableStatement prepareCall(String sql)      ===> 执行sql中存储过程

            //事务管理
            //setAutoCommit(boolean autoCommit) ===> 事务是否自动提交
            //commit()                          ===> 事务提交
            //rollback()                        ===> 事务回滚
            //3.执行语句
            String sql = "select * from user";
            stmt = conn.createStatement();
            //Statement
            //boolean execute(String sql)            ===>执行sql(执行select语句返回true/否则false)
            //ResultSet executeQuery(String sql)     ===>sql中select语句
            //int executeUpdate(String sql)          ===>sql中insert/update/delete语句

            //批处理
            //addBatch(String sql)        ===>添加到批处理
            //executeBatch()              ===>执行批处理
            //clearBatch()                ===>清空批处理
            rs = stmt.executeQuery(sql);
            //结果集(查询语句结果封装)
            //next()将光标移动到下一行
            //针对不同类型数据可以使用不 getString...
            //通用数据getObject 获取
            while (rs.next()) {
                int uid = rs.getInt("uid");
                String username = rs.getString("username");
                String password = rs.getString("password");
                String name = rs.getString("name");
                System.out.println(uid + "" + username + " " + password + " " + name);
            }
            //4.释放资源
            //rs.close();
            //stmt.close();
            //conn.close();
            //connection对象资源有限,应晚创建早释放
            //防止上面代码发生异常应写入final中

        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            //正确释放方式
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                rs = null;
                //可提前资源回收
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                stmt = null;
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                conn = null;
            }
        }

    }

保存操作

public class Demo2 {
    @Test
    public void demo1() {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");

            conn = DriverManager.getConnection("jdbc:mysql:///jdbctest","root","root");

            stmt = conn.createStatement();

            String sql = "insert into user values(null,'eee','123','张三')";

            int i = stmt.executeUpdate(sql);

            if(i>0){
                System.out.println("success");
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                stmt = null;
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                conn = null;
            }
        }
    }
}

改操作 删操作

基本类似只需要修改sql语句即可

String sql = "update user set username = 'qqq',password = '423', name ='张三' where uid = 4";

String sql = "delete from user where uid = 4";