JDBC Day1

JDBC

day1_2023.9.23

概念

JDBC(Java Data Base Connectivity) Java数据库连接，是一种用于执行SQL语句的Java的API
它是由一组使用Java语言编写的类和接口组成。
本质上是Sun公司定义的一套操作所有关系型数据库的规则(接口)，将来每个数据库厂商，去写实现类实现接口，提供数据库jar包，程序员可以使用提供的jar包进行编程。

JDBC快速实现

/*
jdbc快速实现
 */
public class JDBCDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1,导入jar包
        //2，加载(注册)驱动
        Class.forName("com.mysql.jdbc.Driver");
        //3,通过注册的驱动，获取数据库连接
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test",
                "root", "123456");
        //4,定义sql语句
        String sql = "update user set pwd = '666666' where name = 'jack'";
        //5,获取执行sql的对象
        Statement stmt = conn.createStatement();
        //6,通过stmt对象，调用方法，执行sql,
        // 如果执行成功，那么会返回一个值，表示执行的行数
        int i = stmt.executeUpdate(sql);
        //7，处理结果
        System.out.println(i > 0 ? "执行成功": "执行失败");
        //8，关闭资源
        stmt.close();
        conn.close();
    }
}

jdbc:mysql://localhost:3306/数据库名?serverTimezone=GMT%2B8&useSSL=false&useUnicode=true&characterEncoding=utf-8
oracle连接：
驱动地址 ：oracle.jdbc.OracleDriver
connection连接地址：jdbc:oracle:thin:@//192.168.100.160:1521/ORCL

JDBC实现增、删、改

/*
jdbc快速实现
 */
public class JDBCDemo01 {
    public static void main(String[] args){
        Connection conn = null;
        Statement stmt = null;
        try {
            //1,导入jar包
            //2，加载(注册)驱动
            Class.forName("com.mysql.jdbc.Driver");
            //3,通过注册的驱动，获取数据库连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?serverTimezone=GMT%2B8&useSSL=false&useUnicode=true&characterEncoding=utf-8",
                    "root", "123456");
            //4,定义sql语句
            //String sql = "update user set pwd = '666666' where name = 'jack'";
            //String sql = "insert into user values(11,'lucy','123')";
            String sql = "delete from user where id = '5'";
            //5,获取执行sql的对象
            stmt = conn.createStatement();
            //6,通过stmt对象，调用方法，执行sql,
            // 如果执行成功，那么会返回一个值，表示执行的行数
            int i = stmt.executeUpdate(sql);
            //7，处理结果
            System.out.println(i > 0 ? "执行成功": "执行失败");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //8，关闭资源
            try {
                if (stmt != null){
                    stmt.close();
                }
                if (conn != null){
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
    }
}

JDBC实现查询

将查询的数据，绑定到指定的实体类对象

JDBC中使用的对象总结：

DriverManager ：驱动管理对象

功能：

1，注册驱动，告诉程序使用哪个数据库驱动jar包，mysql5版本之后，可以省略这一步

2，获取数据库连接 Connection：

static Connection getConnection(String url, String user, String password)

尝试建立与给定数据库URL的连接。

参数 ： url 表示连接数据库的地址

mysql地址 ：jdbc:mysql://主机ip:3306/数据库名

oracle地址 ：jdbc:oracle:thin:@//主机ip:1521/数据库名

user 表示连接数据库的用户名

password 表示连接数据库的密码

Connection：数据库连接对象

功能 ：

1，获取执行sql语句的对象

Statement createStatement()

创建一个 Statement对象，用于将SQL语句发送到数据库。

PreparedStatement prepareStatement(String sql)

创建一个 PreparedStatement对象，用于将参数化的SQL语句发送到数据库。

2，管理事务

void setAutoCommit(boolean autoCommit) 将此连接的自动提交模式设置为给定状态。

void commit() 使自上次提交/回滚以来所做的所有更改

void rollback() 撤消在当前事务中所做的所有更改

Statement ：执行sql对象

功能：

1，执行sql语句

ResultSet executeQuery(String sql) 执行给定的SQL语句，该语句返回单个 ResultSet对象。

int executeUpdate(String sql) 执行给定的SQL语句，这可能是 INSERT ， UPDATE ，或 DELETE语句，或者不返回任何内容，如SQL DDL语句的SQL语句。

ResultSet ：查询返回的结果集对象，封装查询结果的

ResultSet对象保持一个光标指向其当前的数据行。

最初，光标位于第一行之前。 next方法将光标移动到下一行，并且由于在ResultSet对象中没有更多行时返回false ，因此可以在while循环中使用循环来遍历结果集。

常用方法：

1，boolean next() 将光标从当前位置向前移动一行。

2，getXxx(参数) 方法 ：获取结果集中具体值的方法

方法名Xxx ： 获取到的数据类型是什么类型，这里的Xxx就用什么类型

比如，字段是int类型的， 那么就用getInt()，字段是字符串类型，就用getString()

字段是小数类型，就用 getDouble()

参数 ：参数可以传入两个类型，一个int ，一个String

int代表传入的是获取第几列的数据

String代表传入获取指定的字段名的数据，如果字段名是sname，类型String

方法的写法： getString(“sname”)

PreparedStatement : 处理预编译的SQL

SQL注入问题 ：

编写SQL语句的时候，使用字符串拼接的方式，拼接数据，会产生的一些问题

为了避免这个问题，JDBC中提供了PreparedStatement对象，可以通过处理预编译SQL的方式，完成SQL的查询

用法 ：

1，定义sql的时候，将需要传入的参数，使用 ? 代替,

比如 SELECT * FROM USER WHERE NAME = ? AND pwd = ?

2，conn对象调用方法，传入sql语句，进行预编译处理，并返回pstmt对象

3，通过pstmt对象，完成 ？(占位符) 的赋值，调用setXxx(参数)方法

set方法的名称类型和你要赋值的实际字段类型一致

参数： 第一个参数表示 给第几个 ？ 赋值， 第二个参数表示具体的值

4，通过pstmt对象，调用方法，执行sql

executeQuery() 执行查询

executeUpdate() 执行增删改

让用户输入用户名和密码完成登录的判断？

public class JDBCDemo04 {
    public static void main(String[] args) throws SQLException {
        //接收用户输入
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入用户名:");
        String username = scanner.next();
        System.out.println("请输入密码");
        String password = scanner.next();

        //获取数据库连接
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?serverTimezone=GMT%2B8&useSSL=false&useUnicode=true&characterEncoding=utf-8",
                "root", "123456");
        //conn获取stmt对象
        Statement stmt = conn.createStatement();
        //定义sql
        String sql = "SELECT * FROM USER WHERE NAME = '" +username + "' AND pwd = '" + password + "'";
        System.out.println(sql);
        //执行sql
        ResultSet rs = stmt.executeQuery(sql);
        if (rs.next()){
            System.out.println("用户存在，登录成功！");
        }else {
            System.out.println("用户信息错误，请重新输入");
        }
    }
}

输入一下内容，认识SQL注入问题：
请输入用户名:
sasdgasg
请输入密码
safsdf’or’a’='a
SELECT * FROM USER WHERE NAME = ‘sasdgasg’ AND pwd = ‘safsdf’or’a’=‘a’
用户存在，登录成功！
使用pstmt解决SQL注入问题

/*
让用户输入用户名和密码完成登录的判断？
 */
public class JDBCDemo05 {
    public static void main(String[] args) throws SQLException {
        //接收用户输入
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入用户名:");
        String username = scanner.next();
        System.out.println("请输入密码");
        String password = scanner.next();

        //获取数据库连接
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?serverTimezone=GMT%2B8&useSSL=false&useUnicode=true&characterEncoding=utf-8",
                "root", "123456");

        //定义sql
        String sql = "SELECT * FROM USER WHERE NAME = ? AND pwd = ?";

        //conn获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        //通过pstmt，完成占位符的赋值
        //赋值的数据类型和set方法后面跟的类型是一样的
        pstmt.setString(1,username);
        pstmt.setString(2,password);
        //执行sql
        ResultSet rs = pstmt.executeQuery();

        if (rs.next()){
            System.out.println("用户存在，登录成功！");
        }else {
            System.out.println("用户信息错误，请重新输入");
        }
    }
}

事务的处理

通过转账案例，模拟事务的处理流程
一次转账中，包含两次修改操作，这两次操作应该在同一个事务中，如果第一次操作之后，发生了异常，应该将数据回滚，否则数据的一致性不能得到保证

出现异常的情况

/*
事务：转账案例
 */
public class JDBCDemo06 {
    public static void main(String[] args) throws SQLException {
      Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?serverTimezone=GMT%2B8&useSSL=false&useUnicode=true&characterEncoding=utf-8",
                "root", "123456");

        //事务操作1
        String sql1 = "update tb_account set money = money - 500 where name = 'jack'";
        PreparedStatement pstmt1 = conn.prepareStatement(sql1);
        int i = pstmt1.executeUpdate();
        int a = 3 / 0;  //模拟一个异常
        //事务操作2
        String sql2 = "update tb_account set money = money + 500 where name = 'tom'";
        PreparedStatement pstmt2 = conn.prepareStatement(sql2);
        int j = pstmt2.executeUpdate();
        
    }
}

使用Connection解决事务问题

/*
事务：转账案例
 */
public class JDBCDemo06 {
    public static void main(String[] args)  {
        Connection conn = null;
        PreparedStatement pstmt1 = null;
        PreparedStatement pstmt2 = null;
        try {
             conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?serverTimezone=GMT%2B8&useSSL=false&useUnicode=true&characterEncoding=utf-8",
                      "root", "123456");

            conn.setAutoCommit(false); //手动开启事务
            //事务操作1
            String sql1 = "update tb_account set money = money - 500 where name = 'jack'";
            pstmt1 = conn.prepareStatement(sql1);
            int i = pstmt1.executeUpdate();
            int a = 3 / 0;  //模拟一个异常
            //事务操作2
            String sql2 = "update tb_account set money = money + 500 where name = 'tom'";
            pstmt2 = conn.prepareStatement(sql2);
            int j = pstmt2.executeUpdate();

            //提交事务
            conn.commit();
            //恢复自动提交
            conn.setAutoCommit(true);
        } catch (SQLException e) {
            try {
                //将来发生异常后，进入这个代码块，需要将事务回滚
                conn.rollback();
                //恢复自动提交
                conn.setAutoCommit(true);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }finally {
                try {
                    pstmt2.close();
                    pstmt1.close();
                    conn.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                } finally {
                }
            }
        }
    }
}

编写JDBC工具类

工具类主要是为了简化书写，将共同的内容抽取到方法中使用
需要抽取哪些？
1，注册驱动
2，获取数据库连接方法
解决问题：不想每次调用都去传递参数，还得保证工具类的通用性
3，关闭资源方法

package com.iweb.airui369.jdbc1;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JDBCUtil {

    //想办法，让变量值可以动态的获取，可以根据用户的需求自定义传入的url、user、password
    //而且传入之后，得让数据能获取，并加载
    private static String url;
    private static String username;
    private static String password;
    private static String driver;
    static {
        try {
            //加载配置properties配置文件
            InputStream is = JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
            Properties properties = new Properties();
            properties.load(is);
            //获取到配置文件中的数据
             url = properties.getProperty("url");
             username = properties.getProperty("username");
             password = properties.getProperty("password");
             driver = properties.getProperty("driver");
             Class.forName(driver);
        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    //获取连接方法
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

    //获取pstmt对象的方法封装
    public static PreparedStatement getPstmt(String sql,Connection conn) throws SQLException {
       return conn.prepareStatement(sql);
    }


    //关闭方法
    public static void close(ResultSet rs, Statement stmt, Connection conn){
        try {
            if (rs != null){
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
        }
    }
}

数据库连接池

为什么需要数据库连接池?
因为频繁的获取Connection对象，需要消耗很多的资源，所以可以利用池子技术，在连接池中放入创建好的Connection对象，下次使用，直接从池子中获取，用完之后，调用close()方法，将对象再还到池子里。

package com.iweb.airui369.jdbc2;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.LinkedList;
import java.util.Properties;
import java.util.logging.Logger;

/*
数据库连接池，需要实现一个DataSource接口
 */
public class JDBCPool implements DataSource {
    //创建连接池
    private static LinkedList<Connection> list = new LinkedList<>();
    //希望类在加载的时候，就把连接池中放满Connection对象
    static {
        try {
            //获取properties文件的输入流
            InputStream is =
                    JDBCPool.class.getClassLoader().getResourceAsStream("jdbc.properties");
            //创建Properties,并加载输入流
            Properties properties = new Properties();
            properties.load(is);
            String url = properties.getProperty("url");
            String username = properties.getProperty("username");
            String password = properties.getProperty("password");
            String driver = properties.getProperty("driver");
            //注册驱动
            Class.forName(driver);
            //获取conn连接对象，并将连接对象，放入池中
            for (int i = 0; i < 10; i++) {
                Connection conn = DriverManager.getConnection(url, username, password);
                list.add(conn);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
  
    public Connection getConnection() throws SQLException {
        System.out.println(list);
        System.out.println("当前的池子中,还有:" + list.size() + "个conn对象");
        Connection conn = list.removeFirst();
        System.out.println("conn对象被取走一个，当前的池子中,还有:" + list.size() + "个conn对象");
        //让Proxy动态代理Connection对象，判断connection将来执行的方法
        return (Connection) Proxy.newProxyInstance(JDBCPool.class.getClassLoader(),
                conn.getClass().getInterfaces(),
                new InvocationHandler() {
                    //将来当Connection执行方法的时候，都会经过这个invoke方法
                    //只需要判断使用的是否是close方法，如果close，就做单独处理
                    //如果是其他方法，正常执行
                    @Override
                    public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
                        if(!method.getName().equals("close")){
                            //其他情况继续执行方法
                            return method.invoke(conn, args);
                        }
                        //将conn对象还到池子里
                        list.add(conn);
                        System.out.println("池子中归还了一个conn对象，现在大小是:" + list.size());
                        return null;
                    }
                });
    }

    @Override
    public Connection getConnection(String username, String password) throws SQLException {
        return null;
    }

    @Override
    public <T> T unwrap(Class<T> iface) throws SQLException {
        return null;
    }

    @Override
    public boolean isWrapperFor(Class<?> iface) throws SQLException {
        return false;
    }

    @Override
    public PrintWriter getLogWriter() throws SQLException {
        return null;
    }

    @Override
    public void setLogWriter(PrintWriter out) throws SQLException {

    }

    @Override
    public void setLoginTimeout(int seconds) throws SQLException {

    }

    @Override
    public int getLoginTimeout() throws SQLException {
        return 0;
    }

    @Override
    public Logger getParentLogger() throws SQLFeatureNotSupportedException {
        return null;
    }
}

public class TestConn {
    public static void main(String[] args) throws SQLException {
        JDBCPool pool = new JDBCPool();

        pool.getConnection(); //剩9
        pool.getConnection(); //剩8
        pool.getConnection(); //剩7
        //这个conn获取后，就归还
        Connection conn = pool.getConnection();  // 剩6
        conn.close();  //剩7

        pool.getConnection();// 剩6
    }
}

工具类结合连接池的使用

public class JDBCUtil {
    //获取连接方法
    public static Connection getConnection() throws SQLException {
        return new JDBCPool().getConnection();
    }

    //获取pstmt对象的方法封装
    public static PreparedStatement getPstmt(String sql,Connection conn){
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return pstmt;
    }
    //动态绑定pstmt参数的方法
    //需要传入一个pstmt对象，传入要绑定的参数，完成参数的动态绑定
    public static void bindPstmt(PreparedStatement pstmt,Object...params){
            try {
                for (int i = 1; i <= params.length ; i++) {
                    pstmt.setObject(i, params[i - 1]);
                }
            } catch (SQLException e) {
                e.printStackTrace();
        }
    }


    //关闭方法
    public static void close(ResultSet rs, Statement stmt, Connection conn){
        try {
            if (rs != null){
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
        }
    }
}

BaseDao 中的方法

/*
封装一些公共的方法
执行增删改的方法，查询的方法
 */
public class BaseDao {
    //增删改语句的通用方法
    public boolean update(String sql,Object...params){
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            //获取connection连接
            conn = JDBCUtil.getConnection();
            //获取pstmt
            pstmt = JDBCUtil.getPstmt(sql, conn);
            //动态绑定参数
            JDBCUtil.bindPstmt(pstmt,params);
            //执行sql
            int i = pstmt.executeUpdate();
            return i > 0 ? true :false;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(null,pstmt,conn);
        }
        return false;
    }
    //查询方法的公共方法
    //查询单个
    public Emp QueryOne(String sql,Object...params){

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Emp emp = null;
        try {
            //通过工具类获取需要操作的对象
            conn = JDBCUtil.getConnection();
            pstmt = JDBCUtil.getPstmt(sql, conn);
            JDBCUtil.bindPstmt(pstmt,params);
            //pstmt执行获取结果集对象
            rs = pstmt.executeQuery();
            while (rs.next()){
               emp = new Emp();
               emp.setEmpno(rs.getInt("empno"));
               emp.setEname(rs.getString("ename"));
               emp.setJob(rs.getString("job"));
               emp.setMgr(rs.getInt("mgr"));
               emp.setHiredate(rs.getString("hiredate"));
               emp.setSal(rs.getDouble("sal"));
               emp.setComm(rs.getDouble("comm"));
               emp.setDeptno(rs.getInt("deptno"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(rs,pstmt,conn);
        }
        return emp;
    }

    //查询所有
    public List<Emp> QueryAll(String sql,Object...params){

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Emp> list = null;
        try {
            //通过工具类获取需要操作的对象
            conn = JDBCUtil.getConnection();
            pstmt = JDBCUtil.getPstmt(sql, conn);
            JDBCUtil.bindPstmt(pstmt,params);
            //pstmt执行获取结果集对象
            rs = pstmt.executeQuery();

            //通过rs获取ResultSetMetaData对象
            ResultSetMetaData metaData = rs.getMetaData();
            Emp emp = null;
            list = new ArrayList<>();
            while (rs.next()){
                emp = new Emp();
                //循环的次数根据列的数量来判断
                for (int i = 0; i < metaData.getColumnCount(); i++) {
                    //BeanUtils工具类调用方法，传入需要绑定的数据，完成
                    //对象数据的动态绑定
                    BeanUtils.setProperty(emp,
                            metaData.getColumnLabel(i+1),
                            rs.getObject(i+1));
                }
                list.add(emp);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(rs,pstmt,conn);
        }
        return list;
    }


    //通用的方法，将来任何的实体类都可以通过这个查询方法来查询数据
    public <T> T QueryOne(Class<T> tClass,String sql,Object...params){
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        T bean = null;
        try {
            //通过工具类获取需要操作的对象
            conn = JDBCUtil.getConnection();
            pstmt = JDBCUtil.getPstmt(sql, conn);
            JDBCUtil.bindPstmt(pstmt,params);
            //pstmt执行获取结果集对象
            rs = pstmt.executeQuery();

            ResultSetMetaData metaData = rs.getMetaData();

            while (rs.next()){
                //通过类对象调用newInstance()完成对象创建
                bean = tClass.newInstance();
                for (int i = 0; i < metaData.getColumnCount(); i++) {
                    BeanUtils.setProperty(bean,
                            metaData.getColumnLabel(i+1),
                            rs.getObject(i+1));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(rs,pstmt,conn);
        }
        return bean;
    }
}