1.1 JDBC数据库链接

1、JDBC链接数据库Demo

相关Jar包：mysql-connector-java-8.0.23.jar

public static void main(String[] args) {
    Statement stmt = null;
    Connection conn = null;
    try {
        // 1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        // 2.定义SQL
        String sql = "insert into account values(null,'王五',300)";
        // 3.获取Connection对象
        conn = DriverManager.getConnection("jdbc:mysql://10.16.163.76:3306/dbtest","hhyu","Yhh920205");
        // 4.获取SQL执行对象 Statement
        stmt = conn.createStatement();
        // 5.执行
        int count = stmt.executeUpdate(sql);
        // 6.结果
        System.out.println(count);
        if(count > 0){
            System.out.println("添加成功！");
        }
        else{
            System.out.println("添加失败！");
        }
    } catch (ClassNotFoundException | SQLException e) {
        e.printStackTrace();
    }
    finally {
        if(conn !=null){
            try {
                // 7.释放
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(stmt !=null){
            try {
                stmt.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

2、JDBC 使用PreparedStatement获取数据

/**
     * 使用 PreparedStatement 防止SQL注入
     * @param username
     * @param password
     * @return
     */
public boolean loginByPrepare(String username, String password){
    if(username == null || password == null){
        return false;
    }
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    // 变量形式生成Query
    String sql = String.format("select * from user where username=? and password=?",username,password);
    try {
        conn = JDBCUtil.getConnection();
        pstmt = conn.prepareStatement(sql); //Prepared
        pstmt.setString(1,username); //补充参数
        pstmt.setString(2,password); 
        rs = pstmt.executeQuery();
        return rs.next();
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }
    finally {
        JDBCUtil.close(pstmt,conn,rs);
    }
    return false;
}

3、JDBC 提交事务

public static void main(String[] args) {
    Connection conn = null;
    PreparedStatement pstmt1 = null;
    PreparedStatement pstmt2 = null;
    String sql1 = "update user set username='NewJerry' where id = ?";
    String sql2 = "update user set username='NewTom' where id = ?";
    try {
        conn = JDBCUtil.getConnection();
        conn.setAutoCommit(false);//开启事务
        pstmt1 = conn.prepareStatement(sql1);
        pstmt2 = conn.prepareStatement(sql2);
        pstmt1.setInt(1,1);
        pstmt2.setInt(1,2);
        pstmt1.executeUpdate();
        pstmt2.executeUpdate();
        conn.commit();//提交事务
    } catch (SQLException throwables) {
        try {
            if(conn != null)
                conn.rollback();//回滚事务
        } catch (SQLException e) {
            e.printStackTrace();
        }
        throwables.printStackTrace();
    }
    finally {
        JDBCUtil.close(pstmt1,conn);
        JDBCUtil.close(pstmt2,null);
    }
}

4、数据库链接池（C3P0）

1. 引入jar 包 c3p0-0.9.5.5.bin.zip

2. 在根目录下创建c3p0.properties 或 c3p0-config.xml 文件

<c3p0-config>
    <default-config>
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://10.16.163.76:3306/dbtest</property>
        <property name="user">hhyu</property>
        <property name="password">Yhh920205</property>
        <property name="initialPoolSize">5</property>
        <property name="maxPoolSize">10</property>
        <property name="checkoutTimeout">3000</property>
    </default-config>
</c3p0-config>

3. 执行（获取Connection对象）

    DataSource ds = new ComboPooledDataSource();
     Connection conn = ds.getConnection();

5、数据库连接池（Druid）

1. 引入jar 包 druid-1.2.5.jar

2. 创建properties文件

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://10.16.163.76:3306/dbtest
username=hhyu
password=Yhh920205
initialSize=5
maxActive=10
maxWait=3000

3. 获取Connection对象

    var pro = new Properties();
    pro.load(DruidConnectionPool.class.getClassLoader().getResourceAsStream("druid.properties"););
    DataSource ds = DruidDataSourceFactory.createDataSource(pro);
    Connection conn = ds.getConnection();