JDBC和数据库连接池

JDBC

基本介绍

JDBC原理

模拟JDBC

public class MysqlJdbcImp implements JdbcInterface{

    @Override
    public Object getConnection() {
        System.out.println("得到mysql的连接" );
        return null;
    }

    @Override
    public void crud() {
        System.out.println("得到mysql的增删改查" );
    }

    @Override
    public void close() {
        System.out.println("关闭连接" );
    }
}

public interface JdbcInterface {
    //连接
    public Object getConnection();
    //crud操作
    public void crud();
    //关闭连接
    public void close();
}

public class Test {
    public static void main(String[] args) {
        JdbcInterface jdbcInterface = new MysqlJdbcImp();
        jdbcInterface.getConnection();//通过接口来调用实现类
        jdbcInterface.crud();
        jdbcInterface.close();
    }
}

JDBC的API

JDBC程序编写步骤

package com.wcz_.jdbc;

import com.mysql.jdbc.Driver;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * @author 王成志
 * @version 1.0
 */
public class Jdbc01 {
    public static void main(String[] args) throws SQLException {
        //1.注册驱动
        Driver driver = new Driver();
        //2.得到连接
        //jdbc:mysql://规定好表示协议， 不能改变
        //localhost 表示主机，可以是ip地址
        //3306 表示mysql接听端口
        String url = "jdbc:mysql://localhost:3306/wcz_db02";
        //将用户名和密码封装到properties对象中
        Properties properties = new Properties();
        properties.setProperty("user","root");
        properties.setProperty("password","wcz");
        Connection connect = driver.connect(url, properties);//连接

        //3.执行sql
        String sql = "insert into actor values(null,'刘德华','男','1970-11-11','110' )";
        //用于执行静态sql语句并返回其生成的对象和结果
        Statement statement = connect.createStatement();
        int rows = statement.executeUpdate(sql);//如果是dml语句返回的就是影响行数
        System.out.println(rows > 0 ? "成功":"失败");
        //4.关闭连接
        statement.close();
        connect.close();
    }
}

JDBC连接数据库的方式

public void connect02() throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {
        Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
        Driver driver = (Driver) aClass.newInstance();
        String url = "jdbc:mysql://localhost:3306/wcz_db02";
        //将用户名和密码封装到properties对象中
        Properties properties = new Properties();
        properties.setProperty("user","root");
        properties.setProperty("password","wcz");
        Connection connect = driver.connect(url, properties);//连接
    }

//使用DriverManager替代Driver统一管理
    public void connect03() throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {
        Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
        Driver driver = (Driver) aClass.newInstance();
        //创建url
        String url = "jdbc:mysql://localhost:3306/wcz_db02";
        String user = "root";
        String password = "wcz";
        DriverManager.registerDriver(driver);//注册driver驱动
        DriverManager.getConnection(url,user,password);
    }

//方式4
    public void connect04() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        //创建url
        String url = "jdbc:mysql://localhost:3306/wcz_db02";
        String user = "root";
        String password = "wcz";
        Connection connection = DriverManager.getConnection(url, user, password);
    }

 public void connect05() throws IOException, ClassNotFoundException, SQLException {
        //通过properties对象获取信息
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\mysql.properties"));
        //获取相关值
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");

        String url = properties.getProperty("url");
        String driver = properties.getProperty("driver");
        Class.forName(driver);
        Connection connection = DriverManager.getConnection(url, user, password);

    }

ResultSet

statement

preparedstatement

JDBCUtils

public class JDBCUtils {
    //定义相关属性，只需要一份
    private static String user;//用户名
    private static String password;//密码
    private static String url;//url
    private static String driver;//驱动

    //在static代码块去初始化
    static {
        Properties properties = new Properties();
        try {
            properties.load(new FileInputStream("src\\mysql.properties"));
            user = properties.getProperty("user");
            password = properties.getProperty("password");
            url = properties.getProperty("url");
            driver = properties.getProperty("driver");
        } catch (IOException e) {
            //实际开发中可以这样处理
            throw new RuntimeException(e);
        }
    }

    //连接数据库
    public static Connection getConnection() {
        try {
            return DriverManager.getConnection(url, user, password);
        } catch (SQLException throwables) {
            throw new RuntimeException(throwables);
        }
    }

    //关闭相关资源
    /*
    1.ResultSet结果集
    2.Statement 或者 PreparedStatement
    3.Connection
     */
    public static void close(ResultSet resultSet, Statement statement, Connection connection) {
        //判断是否为空
        try {
            if(resultSet != null){
                resultSet.close();
            }
            if(statement != null){
                statement.close();
            }
            if(connection != null){
                connection.close();
            }
        } catch (SQLException throwables) {
            throw new RuntimeException(throwables;
        }
    }
}

事务

public class Transaction {
    @Test
    public void noTransaction(){
        //不用事务进行操作
        PreparedStatement preparedStatement =null;
        Connection connection = null;
        String sql = "update account1 set balance = balance - 100 where id = 1";
        String sql2 = "update account1 set balance = balance + 100 where id = 2";
        try {
            connection = JDBCUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate();
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.close(null,preparedStatement,connection);
        }

    }
    @Test
    public void useTransaction(){
        //使用事务进行
        PreparedStatement preparedStatement =null;
        Connection connection = null;
        String sql = "update account1 set balance = balance - 100 where id = 1";
        String sql2 = "update account1 set balance = balance + 100 where id = 2";
        try {
            connection = JDBCUtils.getConnection();
            //将connection设置为不自动提交
            connection.setAutoCommit(false);//相当于开启了事务
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.executeUpdate();
            int i = 1 / 0;
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();

        } catch (Exception e) {
            //在这边进行一个回滚
            try {
                connection.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        } finally {
        }
    }
}

批处理

public class Batch {
    //演示批处理
    public void noBatch() throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        String sql = "insert into admin2 values(null,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        for (int i = 0; i < 5000; i++) {
            preparedStatement.setString(1, "jack" + i);
            preparedStatement.setString(2, "666");
            preparedStatement.executeUpdate();
        }
        JDBCUtils.close(null, preparedStatement, connection);
    }

    public void useBatch() throws Exception {
        Connection connection = JDBCUtils.getConnection();
        String sql = "insert into admin2 values(null,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        for (int i = 0; i < 5000; i++) {
            preparedStatement.setString(1, "jack" + i);
            preparedStatement.setString(2, "666");
            //将sql语句加入批处理包
            preparedStatement.addBatch();
            //当有1000条时再执行
            if ((i + 1) % 1000 == 0){
                preparedStatement.executeBatch();
                // 清空
                preparedStatement.clearBatch();
            }
        }
        JDBCUtils.close(null, preparedStatement, connection);
    }
}

数据库连接池

基本介绍

连接种类

C3P0

//方式1相关参数在程序中指定
    @Test
    public void testC3P0_01() throws Exception{
        //创建一个数据源对象
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
        //通过配置文件获取相关信息
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\mysql.properties"));
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driver = properties.getProperty("driver");
        //给数据源comboPooledDataSource配置相关参数
        comboPooledDataSource.setDriverClass(driver);
        comboPooledDataSource.setJdbcUrl(url);
        comboPooledDataSource.setUser(user);
        comboPooledDataSource.setPassword(password);
        //设置初始化连接数
        comboPooledDataSource.setInitialPoolSize(10);
        //最大连接数
        comboPooledDataSource.setMaxPoolSize(50);
        Connection connection = comboPooledDataSource.getConnection();//核心方法
        System.out.println("连接成功");
        connection.close();
    }

   //第二种方法
    public void testC3P0_02() throws Exception{
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource("wcz_db02");
        Connection connection = comboPooledDataSource.getConnection();
        connection.close();
    }

druid

public class Druid_ {
    public void testDruid() throws Exception{
        //加入配置文件拷贝到src目录
        //读取配置文件
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\druid.properties"));
        //创建一个数据源
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        Connection connection = dataSource.getConnection();
        connection.close();

    }

}

德鲁伊工具类

public class JDBCUtilsByDruid {
    private static DataSource ds;
    //在静态代码块完成初始化
    static {
        Properties properties = new Properties();
        try {
            properties.load(new FileInputStream("src\\druid.properties"));
            ds = DruidDataSourceFactory.createDataSource(properties);


        } catch (Exception e) {
            e.printStackTrace();
        }

    }
    //编写getConnection方法
    public  static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }
    //关闭连接,只是把connection放到连接池
    public static void close(ResultSet resultSet, Statement statement,Connection connection){
        try {
            if(resultSet != null){
                resultSet.close();
            }
            if(statement != null){
                statement.close();
            }
            if(connection != null){
                connection.close();
            }
        } catch (SQLException throwables) {
            throw new RuntimeException(throwables);
        }
    }
}

Apache——DBUtils

查询

public class DBUtils_Use {
    //使用apache的dbutils工具类完成crud
    @Test
    public void testQueryMany() throws SQLException {
        //得到连接
        Connection connection = JDBCUtilsByDruid.getConnection();
        //使用dbutils的类和接口,先引入jar文件
        //创建一个QueryRunner
        QueryRunner queryRunner = new QueryRunner();
        // 就可以执行相关方法
        String sql = "select * from actor where id >= ?";
        //query方法就是执行一个sql语句得到一个arraylist
        //beanlisthandler用来在将resultset取出来封装到arraylist
        //1是用来传给sql语句中的问号
        //底层会关闭resultset和statement
        List<Actor> list =
                queryRunner.query(connection, sql, new BeanListHandler<>(Actor.class), 1);
        for (Actor actor : list) {
            System.out.println(actor);//输出集合信息
        }
        JDBCUtils.close(null,null,connection);


    }
}

   @Test
    //演示返回结果是单行
    public void testQuerySimple() throws SQLException {
        //得到连接
        Connection connection = JDBCUtilsByDruid.getConnection();
        //使用dbutils的类和接口,先引入jar文件
        //创建一个QueryRunner
        QueryRunner queryRunner = new QueryRunner();
        // 就可以执行相关方法返回单个对象
        String sql = "select * from actor where id = ?";
        Actor actor =
                queryRunner.query(connection, sql, new BeanHandler<>(Actor.class),4);
        System.out.println(actor);
        JDBCUtils.close(null,null,connection);
    }

DML

@Test
    //演示完成dml操作
    public void testDML() throws SQLException {
        //得到连接
        Connection connection = JDBCUtilsByDruid.getConnection();
        //使用dbutils的类和接口,先引入jar文件
        //创建一个QueryRunner
        QueryRunner queryRunner = new QueryRunner();
        //组织sql语句完成dml
        String sql = "update actor set name = ? where id = ?";
        //返回的是生效行数
        int affectedRow = queryRunner.update(connection, sql, "rose", 4);
        System.out.println(affectedRow > 0?"执行成功":"无影响");
        JDBCUtils.close(null,null,connection);
    }

BasicDao

public class BasicDAO<T> {//泛型指定具体类型
    private QueryRunner qr = new QueryRunner();
    //开发通用的dml表
    public int update(String sql,Object... parameters){
        Connection connection = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            int update = qr.update(connection, sql, parameters);
            return update;
        } catch (SQLException throwables) {
            throw new RuntimeException(throwables);
        }finally {
            JDBCUtilsByDruid.close(null,null,connection);
        }

    }
    //返回多行查询
    public List<T> queryMulti(String sql,Class<T> clazz,Object...parameters){
        Connection connection = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            return qr.query(connection,sql,new BeanListHandler<T>(clazz),parameters);
        } catch (SQLException throwables) {
            throw new RuntimeException(throwables);
        }finally {
            JDBCUtilsByDruid.close(null,null,connection);
        }

    }
    //查询单行
    public T querySimple(String sql, Class<T> clazz , Object... parameters){
        Connection connection = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            return qr.query(connection,sql,new BeanHandler<T>(clazz),parameters);
        } catch (SQLException throwables) {
            throw new RuntimeException(throwables);
        }finally {
            JDBCUtilsByDruid.close(null,null,connection);
        }
    }
    //查询单行单列
    public Object queryScalar(String sql,Object... parameters){
        Connection connection = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            return qr.query(connection,sql,new ScalarHandler(),parameters);
        } catch (SQLException throwables) {
            throw new RuntimeException(throwables);
        }finally {
            JDBCUtilsByDruid.close(null,null,connection);
        }
    }
}

public class TestDAO {
    //测试
    @Test
    public void testActorDAO(){
        ActorDAO actorDAO = new ActorDAO();
        //查询语句
        List<Actor> actors = actorDAO.queryMulti("select * from actor where id >= ?", Actor.class, 1);
        for (Actor actor : actors) {
            System.out.println(actor);
        }

    }
    
}