java使用jdbc

什么是jdbc

Java数据库连接，（Java Database Connectivity，简称JDBC）是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口，提供了诸如查询和更新数据库中数据的方法。

jdbc使用

连接mysql依赖：

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.32</version>
</dependency>

步骤：

1. 加载驱动类
2. 获取连接
3. 创建执行器
4. 执行sql

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://ip:prot/user?useUnicode=true&characterEncoding=utf8&useTimezone=true&serverTimezone=GMT%2B8", "username", "password");
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery("select * from user");
            while(resultSet.next()) {
                System.out.println(resultSet.getString("id") + "\t" + resultSet.getString("user_name"));
            }
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

使用数据源

数据源就是数据的来源，我们一般说的数据源就是数据库。java提供了对应的接口DataSource。
使用：

	// 继承DataSource接口，实现获取连接方法
	public class MyDataSource implements DataSource {
	    @Override
	    public Connection getConnection() throws SQLException {
	        return DriverManager.getConnection("jdbc:mysql://ip:prot/user?useUnicode=true&characterEncoding=utf8&useTimezone=true&serverTimezone=GMT%2B8", "username", "password");
	    }
		// 其他方法不显示
	}

    public static void main(String[] args) throws SQLException, IOException {
    	// 使用数据源获取连接
        DataSource dataSource = new MyDataSource();
        Connection connection = dataSource.getConnection();
        ResultSet resultSet = connection.createStatement().executeQuery("select * from user");
        while(resultSet.next()) {
            System.out.println(resultSet.getString("id") + "\t" + resultSet.getString("user_name"));
        }
        connection.close();
    }

使用连接池

数据库连接创建需要开销，频繁创建影响程序性能，使用连接池可以让连接重用，提供性能。
思路：

1. 数据源内维护一个池子，连接关闭的时候是把本身放入池子中。
2. 连接关闭的操作需要自己控制，实现Connection接口，自定义关闭操作，程序拿到的连接对象是自定义的。

自定义连接

	public class MyConnectionImpl implements Connection {
	
	    private Connection connection;
	
	    private Consumer<Connection> connectionConsumer;
		
	    public MyConnectionImpl(Connection connection, Consumer<Connection> connectionConsumer) {
	    	// 实际连接对象
	        this.connection = connection;
	        // 关闭操作
	        this.connectionConsumer = connectionConsumer;
	    }
	
	    @Override
	    public Statement createStatement() throws SQLException {
	        return connection.createStatement();
	    }
	
	    public void close() {
	        connectionConsumer.accept(this);
	    }
	    // 其他方法不显示
	}

自定义连接池

	public class MyDataSourcePool implements DataSource {
		// 使用队列存放连接
	    private Queue<Connection> pool = new LinkedBlockingQueue();
	
	    public MyDataSourcePool() throws SQLException {
	    	// 连接关闭操作 把自身放入池中
	        Consumer<Connection> connectionConsumer = connection -> {
	            pool.add(connection);
	        };
	        // 初始化三个连接
	        Connection connection = DriverManager.getConnection("jdbc:mysql://ip:prot/user?useUnicode=true&characterEncoding=utf8&useTimezone=true&serverTimezone=GMT%2B8", "username", "password");
	        Connection connection2 = DriverManager.getConnection("jdbc:mysql://ip:prot/user?useUnicode=true&characterEncoding=utf8&useTimezone=true&serverTimezone=GMT%2B8", "username", "password");
	        Connection connection3 = DriverManager.getConnection("jdbc:mysql://ip:prot/user?useUnicode=true&characterEncoding=utf8&useTimezone=true&serverTimezone=GMT%2B8", "username", "password");
	        // 连接池放入自定义连接对象
	        pool.add(new MyConnectionImpl(connection, connectionConsumer));
	        pool.add(new MyConnectionImpl(connection2, connectionConsumer));
	        pool.add(new MyConnectionImpl(connection3, connectionConsumer));
	
	    }
	    @Override
	    public Connection getConnection() throws SQLException {
	    	// 连接从队列中获取
	        return pool.poll();
	    }
		// 其他方法不显示
	}

    public static void main(String[] args) throws SQLException, IOException {
		//从连接池中获取连接
        MyDataSourcePool dataSource = new MyDataSourcePool();
        Connection connection = dataSource.getConnection();
        ResultSet resultSet = connection.createStatement().executeQuery("select * from user");
        while(resultSet.next()) {
            System.out.println(resultSet.getString("id") + "\t" + resultSet.getString("user_name"));
        }
        System.out.println(connection);
        System.out.println(dataSource.getConnection());
        System.out.println(dataSource.getConnection());
        System.out.println(dataSource.getConnection());
        connection.close();
        System.out.println(dataSource.getConnection());
    }

使用阿里开源druid

引入依赖

<dependency>
     <groupId>com.alibaba</groupId>
     <artifactId>druid</artifactId>
     <version>1.2.16</version>
 </dependency>

使用

    public static void main(String[] args) throws Exception {
		// 连接配置
        Map config = new HashMap();
        config.put("driverClassName", "com.mysql.cj.jdbc.Driver");
        config.put("url", "jdbc:mysql://ip:prot/user?useUnicode=true&characterEncoding=utf8&useTimezone=true&serverTimezone=GMT%2B8");
        config.put("username", "username");
        config.put("password", "password");
        config.put("maxWait", "3000");
        config.put("initialSize", "3");
        config.put("maxActive", "4");
        DataSource dataSource = DruidDataSourceFactory.createDataSource(config);
        Connection connection = dataSource.getConnection();
        ResultSet resultSet = connection.createStatement().executeQuery("select * from user");
        while(resultSet.next()) {
            System.out.println(resultSet.getString("id") + "\t" + resultSet.getString("user_name"));
        }
        System.out.println(connection);
        System.out.println(dataSource.getConnection());
        System.out.println(dataSource.getConnection());
        System.out.println(dataSource.getConnection());
        connection.close();
        System.out.println(dataSource.getConnection());
    }