JDBC-study

JDBC的概述

数据的持久化

持久化(persistence):主要应用是将内存中的数据存储在关系型数据库中

JDBC的介绍

• JDBC(java database connectivity):是一个独立于特定数据库管理系统，通用的SQL数据库存取和操作的公共接口，（API（java.sql.javax.sql）一种标准的方法去访问数据库

获取数据库的连接

连接数据库方式一

package com.miao.connection;
import org.junit.jupiter.api.Test;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.SQLException;
import java.util.Properties;
public class ConnectionTest {
//连接数据库方式一
    @Test
    public void test() throws SQLException {
        
        Driver driver = new com.mysql.cj.jdbc.Driver();
        /*
        jdbc:mysql:协议
        localhost:IP地址
        3306：默认MySQL端口号
        school：school数据库
         */
        String url = "jdbc:mysql://localhost:3306/school";
        //将user和password封装在properties中
        Properties info = new Properties();
        info.setProperty("user","root");
        info.setProperty("password","123456");

        Connection connection = driver.connect(url,info);
        System.out.println(connection);
    }
}

方式二：连接数据库（迭代）

//方式二：连接数据库
@Test
public void test2() throws Exception {
    //获取Driver实现类对象：使用反射
    Class clazz = Class.forName("com.mysql.cj.jdbc.Driver");
    Driver driver = (Driver) clazz.newInstance();
    //提供连接的数据库
    String url = "jdbc:mysql://localhost:3306/school";
    //提供用户名和密码
    Properties info = new Properties();
    info.setProperty("user","root");
    info.setProperty("password","123456");
    //获取连接
    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.cj.jdbc.Driver");
    Driver driver = (Driver) clazz.newInstance();
    //提供连接的url user password
    String url = "jdbc:mysql://localhost:3306/school";
    String user = "root";
    String password = "123456";
    //注册驱动
    DriverManager.registerDriver(driver);
    //获取连接
    Connection connection = DriverManager.getConnection(url, user, password);
    System.out.println(connection);
}

方式四

    //方式四：加载Driver
    @Test
    public void test4() throws Exception {

        //提供连接的url user password
        String url = "jdbc:mysql://localhost:3306/school";
        String user = "root";
        String password = "123456";

        //加载Driver
         Class.forName("com.mysql.cj.jdbc.Driver");

//        Driver driver = (Driver) clazz.newInstance();
//        //注册驱动
//        DriverManager.registerDriver(driver);
        /*
        在MySQL的实现类中，声明了这个操作
          static {
              try {
                  DriverManager.registerDriver(new Driver());
              } catch (SQLException var1) {
                  throw new RuntimeException("Can't register driver!");
              }
          }
         */
        //获取连接
        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println(connection);
    }

方式五

    //方式五：通过读取配置文件获取连接数据库的 基本信息
    @Test
    public void test5() throws Exception {

        //读取配置文件的信息--方式一
//        InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("db.properties");
//        Properties pros = new Properties();
//        pros.load(is);
       //读取配置文件的信息--方式二
        File file = new File("src/main/resources/db.properties");
        FileInputStream is = new FileInputStream(file);
        Properties pros = new Properties();
        pros.load(is);

        String url = pros.getProperty("url");
        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String driver = pros.getProperty("driver");
        //加载驱动
        Class.forName(driver);
        //获取连接
        Connection connection = DriverManager.getConnection(url, user, password);
        System.out.println(connection);
    }

PreparedStatement操作数据库

普通操作数据库

@Test
public void testInsert(){
    Connection connection = null;
    PreparedStatement ps = null;
    try {
        //获取连接
        //ClassLoader.getSystemClassLoader() 获取系统类加载器
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("db.properties");
        Properties properties = new Properties();
        properties.load(is);
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driver = properties.getProperty("driver");
        Class.forName(driver);
        connection = DriverManager.getConnection(url, user, password);
        System.out.println(connection);
        //预编译sql语句，获取preparedstatement的实例
        String sql = "insert into books(bookName,bookDetail) values(?,?)";
        ps = connection.prepareStatement(sql);
        //填充占位符
        ps.setString(1,"动物世界");
        ps.setString(2,"中国作家");
        //执行操作
        ps.execute();
    } catch (Exception e) {
        e.printStackTrace();
    }finally {
        try {
            if (connection != null){
                //资源关闭
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (ps != null){
                ps.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

连接驱动

# 配置文件：连接数据库
# db.properties
user=root
password=123456
url=jdbc:mysql://localhost:3306/school
driver=com.mysql.cj.jdbc.Driver

封装更新操作

工具类-JDBCUtil

public class JDBCUtil {

    //获取连接驱动
    public static Connection getConnection() throws Exception {
        //获取连接
        //ClassLoader.getSystemClassLoader() 获取系统类加载器
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("db.properties");
        Properties properties = new Properties();
        properties.load(is);

        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driver = properties.getProperty("driver");

        Class.forName(driver);
        Connection conn = DriverManager.getConnection(url, user, password);

        return conn;
    }
    //关闭连接对象
    public static void closeResource(Connection con, Statement st){
        try {
            if (con != null){
                //资源关闭
                con.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (st != null){
                st.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

更新操作封装

//通用的增删改
public void update(String sql,Object ...args){
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        //获取连接
        conn = JDBCUtil.getConnection();
        //sql预编译
        ps = conn.prepareStatement(sql);
        //填充占位符
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i+1,args[i]);
        }
        //执行
        ps.execute();
    } catch (Exception e) {
        e.printStackTrace();
    }finally {
        //关闭流对象
        JDBCUtil.closeResource(conn,ps);
    }
}

测试

@Test
public void testCommon(){
    String sql = "delete from books where bookId = ?";
    update(sql,8);
}

简单封装查询操作

    //针对某张表的通用查询
    public Book queryForBook(String sql,Object ...args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //获取连接
            conn = JDBCUtil.getConnection();
            //sql预编译
            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();
            //通过ResultSetMetaData 获取结果集中的列数
            int columnCount = rsmd.getColumnCount();
            if (rs.next()){

                //需要一个对象
                Book book = new Book();
                //处理结果集一行中每一个列
                for (int i = 0; i < columnCount; i++) {
                    //获取列值
                    Object columnValue = rs.getObject(i + 1);
                    //获取每个列名
//                    String columnLabel = rsmd.getColumnLabel(i + 1);
                    //getColumnLabel() 针对于类的属性名 和 表的字段名 不一致的情况
                    //声明sql时，可以使用类的属性名 来命名字段的别名
                    String columnName = rsmd.getColumnName(i + 1);
                    //给book对象指定columnName属性，赋值columnValue，通过反射
                    Field field = Book.class.getDeclaredField(columnName);
                    field.setAccessible(true);
                    field.set(book,columnValue);
                }
                return book;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.closeResource(conn,ps,rs);
        }
        return null;
    }

测试

@Test
public void testQuery() {
    String sql = "select bookId,bookName,bookDetail from books where bookId = ?";
    Book book = queryForBook(sql, 1);
    System.out.println(book);

}