JDBC核心技术1

最新推荐文章于 2024-04-30 17:28:01 发布

盖大大

最新推荐文章于 2024-04-30 17:28:01 发布

阅读量89

点赞数

分类专栏： Java基础文章标签： java

本文链接：https://blog.csdn.net/u014215131/article/details/113810679

版权

Java基础专栏收录该内容

13 篇文章 0 订阅

订阅专栏

1. JDBC概述

1.1 Java中的数据存储技术

JDBC直接访问数据库
JDO(Java Data Object)技术
第三方O/R工具，Hibernate,Mybatis等

1.2 JDBC介绍

JDBC(Java Database Connectivity)独立于特定数据库管理系统，通用的SQL数据库存取和操作的公共接口，定义了用来访问数据库标准java类库，java.sql,javax.sql
为访问不同的数据库提供了一种统一的途径，使用JDBC可以连接任何提供了JDBC驱动程序的数据库系统

JDBC

1.3 JDBC体系结构

JDBC接口(API)包括两个层次：

1.面向应用的API：Java API，抽象接口，供应用程序开发人员使用（连接数据库，执行SQL语句，获得结果）
2.面向数据库API：Java Driver API，供开发商开发数据库驱动程序用

1.4 JDBC 程序编写步骤

2. 获取数据库连接

2.1 要素一：Driver接口实现类

java.sql.Driver是所有JDBC驱动程序需要实现的接口，这个接口是提供给数据库厂商使用的，不同数据库厂商提供不同的实现
在程序中不需要直接去访问实现了Driver接口的类，而是由驱动程序管理类(java.sql.DriverManager)去调用这些Driver实现

2.2 要素二：URL

JDBC URL用于标识一个被注册的驱动程序，通过这个URL选择正确的驱动程序，从而建立到数据库的连接
JDBC URL由三部分组成，各部分用冒号分隔 jdbc:mysql://localhost:3306/test

2.3 连接方式举例

public class ConnectionTest {
    //方式一
    @Test
    public void test1() throws SQLException {
        //获取Driver实现类对象
        Driver driver = new com.mysql.jdbc.Driver();
        String url = "jdbc:mysql://localhost:3306/test";

        //将用户名密码封装到Properties中
        Properties info = new Properties();
        info.setProperty("user", "root");
        info.setProperty("password", "1234");
        Connection connect = driver.connect(url, info);
        System.out.println(connect);
    }

    //方式二：对方式一进行升级，不出现第三方API，使程序有更好的移植性
    @Test
    public void test2() throws Exception {
        //用反射获取Driver实现类对象
        Class<?> clazz = Class.forName("com.mysql.jdbc.Driver");
        Driver driver = (Driver) clazz.newInstance();

        //提供要连接的数据库
        String url = "jdbc:mysql://localhost:3306/test";
        //连接需要的用户名和密码
        Properties info = new Properties();
        info.setProperty("user", "root");
        info.setProperty("password", "1234");
        Connection connect = driver.connect(url, info);

        System.out.println(connect);
    }

    //方式三：升级方式二，使用DriverManager替换Driver
    @Test
    public void test3() throws Exception {
        //获取Driver实现类对象
        Class<?> clazz = Class.forName("com.mysql.jdbc.Driver");
        Driver driver = (Driver) clazz.newInstance();
        // 注册驱动
        DriverManager.registerDriver(driver);
        //获取连接
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String password = "1234";
        Connection connect = DriverManager.getConnection(url, user, password);

        System.out.println(connect);
    }

    //方式四：优化方式三
    @Test
    public void test4() throws Exception {
        //加载Driver，相较于方式三，可以省略注册
        //Class.forName("com.mysql.jdbc.Driver");
        //获取连接
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String password = "1234";
        Connection connect = DriverManager.getConnection(url, user, password);

        System.out.println(connect);
    }

    //方式五：升级方式四，在配置文件中读取url,user,password
    @Test
    public void test5() throws Exception {
        //1.读取配置文件中的基本信息
        InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        properties.load(is);
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driverClass = properties.getProperty("driverClass");

        //2.加载驱动
        Class.forName(driverClass);

        //3.获取连接
        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println(connection);
    }
}

3. 使用PreparedStatement实现CRUD操作

3.1 操作和访问数据库

数据库连接被用于向数据库服务器发送命令和SQL语句，并接受数据库服务器返回的结果。一个数据库连接其实就是一个Socket连接
在java.sql包中有3个接口分别定义了对数据库的调用的不同方式：
Statement用于执行静态SQL语句并返回他所生成结果的对象
PrepatedStatement SQL语句被预编译并存储在此对象中，可以使用此对象多次高效地执行该语句
CallableStatement 用于执行SQL存储过程

Statement

3.2 使用Statement操作数据表的弊端

存在拼窜操作，繁琐
存在SQL注入问题

3.3 PreparedStatement的使用

PreparedStatement

3.3.1 使用PreparedStatement实现增删改操作

/*
 * 使用PreparedStatement替代Statement，实现对数据表的增删改查操作
 *
 * 增删改；查
 * */
public class PreparedStatementUpdteTest {
    //向customers表中添加一条记录
    @Test
    public void test1() {
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
            Properties properties = new Properties();
            properties.load(is);
            String user = properties.getProperty("user");
            String password = properties.getProperty("password");
            String url = properties.getProperty("url");
            String driverClass = properties.getProperty("driverClass");

            Class.forName(driverClass);

            connection = DriverManager.getConnection(url, user, password);
            System.out.println(connection);

            //预编译sql语句，返回PreparedStatement实例
            String sql = "insert into customers(name,email,birth) values(?,?,?)";
            ps = connection.prepareStatement(sql);
            //填充占位符
            ps.setString(1, "Nike");
            ps.setString(2, "nike@126.com");
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            Date date = sdf.parse("2000-01-01");
            ps.setDate(3, new java.sql.Date(date.getTime()));

            //执行操作
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //资源关闭
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

    }

    //修改customers表中的一条记录
    @Test
    public void test2() throws Exception {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            //获取数据库的连接
            conn = JDBCUtils.getConnection();
            //预编译sql语句，返回PreparedStatement的实例
            String sql = "update customers set email=? where id = ?";
            ps = conn.prepareStatement(sql);
            //填充占位符
            ps.setObject(1, "123@163.com");
            ps.setObject(2, 18);
            //执行
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //资源的关闭
            JDBCUtils.closetResource(conn, ps);
        }
    }

    @Test
    public void testUpdate(){
//        String sql="delete from customers where id=?";
//        update(sql,3);
        String sql = "update `order` set order_name=? where order_id =?";
        update(sql,"DD",2);
    }

    //通用的增删改操作
    public void update(String sql, Object... args)  {//sql中占位符的个数与可变形参的长度相同
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            //1.获取数据库的连接
            conn = JDBCUtils.getConnection();
            //2.预编译sql语句，返回PreparedStatement的实例
            ps = conn.prepareStatement(sql);
            //3.填充占位符
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);//!!!!
            }
            //4.执行
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //5.关闭资源
            JDBCUtils.closetResource(conn, ps);
        }
    }
}

3.3.2 JDBCUtils封装数据库连接和关闭

/*
 * 操作数据库的工具类
 * */
public class JDBCUtils {
    //获取数据库的连接
    public static Connection getConnection() throws Exception {
        //1.读取配置文件中的基本信息
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        properties.load(is);
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driverClass = properties.getProperty("driverClass");

        //2.加载驱动
        Class.forName(driverClass);

        //3.获取连接
        Connection connection = DriverManager.getConnection(url, user, password);
        return connection;
    }

    //关闭连接和Statement的操作
    public void closetResource(Connection conn, Statement ps) {
        try {
            if (ps != null) {
                ps.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    public static void closetResource(Connection conn, Statement ps, ResultSet rs) {
        try {
            if (ps != null) {
                ps.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            if (rs != null) {
                conn.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

3.3.2 使用PrepredStatement实现查操作

/*
 * ORM(Object Relational Mapping)一个数据包对应一个java类
 * 表中的一条记录对应java类的一个对象
 * 表中的一个字段对应java类的一个属性
 * */
public class Customer {
    private int id;
    private String name;
    private String email;
    private Date birth;

    public Customer() {
    }

    public Customer(int id, String name, String email, Date birth) {
        this.id = id;
        this.name = name;
        this.email = email;
        this.birth = birth;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Date getBirth() {
        return birth;
    }

    public void setBirth(Date birth) {
        this.birth = birth;
    }

    @Override
    public String toString() {
        return "Customer{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", email='" + email + '\'' +
                ", birth=" + birth +
                '}';
    }
}

//针对于Customers表的查询操作
public class CustomerForQuery {
    @Test
    public void test1() {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtils.getConnection();
            String sql = "select id,name,email,birth from customers where id=?";
            ps = connection.prepareStatement(sql);
            ps.setObject(1, 1);
            //执行，并返回结果集
            resultSet = ps.executeQuery();
            //处理结果集
            if (resultSet.next()) {//判断结果集的下一条是否有数据，如果返回true，指针下移
                //获取当前这条数据的各个字段
                int id = resultSet.getInt(1);
                String name = resultSet.getString(2);
                String email = resultSet.getString(3);
                Date birth = resultSet.getDate(4);

                //System.out.println(id + name + email + birth);
                Customer customer = new Customer(id, name, email, birth);
                System.out.println(customer);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //关闭资源
            JDBCUtils.closetResource(connection, ps, resultSet);
        }
    }

    //针对customers表的通用的查询操作
    public Customer queryForCustomers(String sql, Object... args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            //获取结果集的元数据
            ResultSetMetaData rsmd = rs.getMetaData();

            int columnCount = rsmd.getColumnCount();
            if (rs.next()) {
                Customer cust = new Customer();
                //处理一行结果集中的每个列
                for (int i = 0; i < columnCount; i++) {
                    //获取列值
                    Object columnValue = rs.getObject(i + 1);
                    //获取每个列的列名
                    String columnName = rsmd.getColumnName(i + 1);
                    //给cust对象指定的columnName属性，赋值为columnValue，通过反射
                    Field field = Customer.class.getDeclaredField(columnName);
                    field.setAccessible(true);
                    field.set(cust, columnValue);
                }
                return cust;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closetResource(conn, ps, rs);
        }
        return null;
    }

    @Test
    public void testQueryForCustomers() {
        String sql1 = "select id,name from customers where id =?";
        Customer customer = queryForCustomers(sql1, 13);
        System.out.println(customer);
    }
}

public class Order {
    private int orderId;
    private String orderName;
    private Date orderDate;

    public Order() {
    }

    public Order(int orderId, String orderName, Date orderDate) {
        this.orderId = orderId;
        this.orderName = orderName;
        this.orderDate = orderDate;
    }

    public int getOrderId() {
        return orderId;
    }

    public void setOrderId(int orderId) {
        this.orderId = orderId;
    }

    public String getOrderName() {
        return orderName;
    }

    public void setOrderName(String orderName) {
        this.orderName = orderName;
    }

    public Date getOrderDate() {
        return orderDate;
    }

    public void setOrderDate(Date orderDate) {
        this.orderDate = orderDate;
    }

    @Override
    public String toString() {
        return "Order{" +
                "orderId=" + orderId +
                ", orderName='" + orderName + '\'' +
                ", orderDate=" + orderDate +
                '}';
    }
}

/*针对于Order表的通用查询操作，针对于表的字段名与类的属性名不相同的情况
*
* */
public class OrderForQuery {
    @Test
    public void test1() {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            connection = JDBCUtils.getConnection();
            String sql = "select order_id,order_name,order_date from `order` where order_id =?";
            ps = connection.prepareStatement(sql);
            ps.setObject(1, 1);
            rs = ps.executeQuery();
            if (rs.next()) {
                int id = (int) rs.getObject(1);
                String name = (String) rs.getObject(2);
                Date date = (Date) rs.getObject(3);
                Order order = new Order(id, name, date);
                System.out.println(order);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closetResource(connection, ps, rs);
        }
    }

    @Test
    public void test2() {
        String sql = "select order_id orderId,order_name orderName,order_date orderDate from `order` where order_id = ?";
        Order order = orderForQuery(sql, 1);
        System.out.println(order);
    }

    public Order orderForQuery(String sql, Object... args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            if (rs.next()) {
                Order order = new Order();
                for (int i = 0; i < columnCount; i++) {

                    //获取每个列的列值
                    Object columnValue = rs.getObject(i + 1);
                    //获取每个列的列的别名
                    String columnLabel = metaData.getColumnLabel(i + 1);
                    //通过反射将对象指定名的属性赋值为指定的值
                    Field field = Order.class.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(order, columnValue);
                }
                return order;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closetResource(conn, ps, rs);
        }
        return null;
    }
}

//使用PreparedStatement实现针对不同表的通用的查询操作
public class PreparedStatementQueryTest {
    @Test
    public void test1() {
        String sql = "select id,name,email,birth from customers where id<?";
        List<Customer> customerList = getInstance(Customer.class, sql, 12);
        customerList.forEach(System.out::println);
    }

    public <T> List<T> getInstance(Class<T> clazz, String sql, Object... args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            //获取结果集的元数据
            ResultSetMetaData rsmd = rs.getMetaData();

            int columnCount = rsmd.getColumnCount();
            //创建集合对象
            ArrayList<T> list = new ArrayList<>();
            while (rs.next()) {
                T t = clazz.newInstance();
                //处理一行结果集中的每个列
                for (int i = 0; i < columnCount; i++) {
                    //获取列值
                    Object columnValue = rs.getObject(i + 1);
                    //获取每个列的列名
                    String columnName = rsmd.getColumnName(i + 1);
                    //给cust对象指定的columnName属性，赋值为columnValue，通过反射
                    Field field = clazz.getDeclaredField(columnName);
                    field.setAccessible(true);
                    field.set(t, columnValue);
                }
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closetResource(conn, ps, rs);
        }
        return null;
    }
}

3.4 JDBC API小结

面向接口编程的思想
ORM思想(Object Relational Mapping)
JDBC结果集的元数据
通过反射，创建指定类的对象，获取指定的属性并赋值

4. 操作BLOB类型字段

4.1 MySQL BLOB类型

BLOB是一个二进制大型对象，是一个可以存储大量数据的容器，能容纳不同大小的数据
插入BLOB类型的数据必须使用PrepredSttement，因为BLOB类型的数据无法使用字符串拼接写的
如果存储的文件过大，数据库的性能会下降

4.2 向数据表中插入大数据类型

//使用PrepredStatement测试Blob类型的数据
public class BlobTest {
    //向数据表customers插入Blob类型的字段
    @Test
    public void test1() throws Exception {
        Connection connection = JDBCUtils.getConnection();
        String sql = "insert into customers(name,email,birth,photo) values(?,?,?,?)";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setObject(1, "zhang");
        ps.setObject(2, "erew@qq.com");
        ps.setObject(3, "2000-01-01");
        FileInputStream is = new FileInputStream("tp.png");
        ps.setBlob(4, is);
        ps.execute();
        JDBCUtils.closetResource(connection, ps);
    }
}

4.3 从数据表中读取的数据类型

    @Test
    public void test2() throws Exception {
        Connection connection = JDBCUtils.getConnection();
        String sql = "select id,name,email,birth,photo from customers where id =?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1, 22);
        ResultSet rs = ps.executeQuery();
        InputStream is = null;
        FileOutputStream fos = null;
        if (rs.next()) {
            int id = rs.getInt("id");
            String name = rs.getString("name");
            String email = rs.getString("email");
            Date birth = rs.getDate("birth");
            Customer customer = new Customer(id, name, email, birth);
            System.out.println(customer);
            //将blob类型的字段下载下来，保存在本地
            Blob photo = rs.getBlob("photo");
            is = photo.getBinaryStream();
            fos = new FileOutputStream("zhang.png");
            byte[] buffer = new byte[1024];
            int len;
            while ((len = is.read(buffer)) != -1) {
                fos.write(buffer, 0, len);
            }
        }
        is.close();
        fos.close();
        JDBCUtils.closetResource(connection, ps, rs);
    }

盖大大

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
JDBC核心技术1

1. JDBC概述2. 获取数据库连接3. 使用PreparedStatement实现CRUD操作4. 操作BLOB类型字段5. 批量插入6. 数据库事务7. DAO及其实现类8. 数据库连接池9. Apache-DBUtils实现CRUD操作
复制链接

扫一扫