数据库底层连接方式

数据库的连接

方式一:创建对象方式

1.使用的依赖

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

2.加载驱动

3.设置属性

4.建立连接

5.创建Statement或者PrepareStatement用来执行sql语句

6.释放资源(connection,和Statement或者PrepareStatement)

    /**
     * 本测试用于复习jdbc连接方式
     */
    @Test
    public void testJdbc(){
        Connection connect = null;
        PreparedStatement statement = null;
        try {
            //加载驱动
            Driver driver = new com.mysql.cj.jdbc.Driver();
            String url = "jdbc:mysql://localhost:3306/day05?useSSL=false&serverTimezone=UTC";
            //判断该url是否可用
            boolean b = driver.acceptsURL(url);
            System.out.println(b);
            Properties properties = new Properties();
            //设置基本信息
            properties.setProperty("user","root");
            properties.setProperty("password","123456");
            connect = driver.connect(url, properties);
            String sql = "select * from student";
            statement = connect.prepareStatement(sql);
            ResultSet rs = statement.executeQuery();
            //默认是从第一行开始的,我们的数据是从第二行开始的,所以要next一下
            while(rs.next()) {
                String sno = rs.getString("id");
                String sname = rs.getString("name");
                String ssex = rs.getString("sex");
                int sage = rs.getInt("age");
                System.out.println(sno + "\t" + sname + "\t" +ssex + "\t" + sage);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                connect.close();
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

方式二:反射方式

   /**
     * 本测试用于测试数据库的连接
     */
    @Test
    public void test() {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            //加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/day05?useSSL=false&serverTimezone=UTC";
            //创建连接
            connection = DriverManager.getConnection(url, "root", "123456");
            String sql = "select * from student";
            //创建执行sql声明
            statement = connection.prepareStatement(sql);
            ResultSet rs = statement.executeQuery();
            while (rs.next()) {
                String sno = rs.getString("id");
                String sname = rs.getString("name");
                String ssex = rs.getString("sex");
                int sage = rs.getInt("age");
                System.out.println(sno + "\t" + sname + "\t" + ssex + "\t" + sage);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

方式三:c3p0数据库连接池方式

依赖

<dependency>
    <groupId>com.mchange</groupId>
    <artifactId>c3p0</artifactId>
    <version>0.9.5.2</version>
</dependency>

然后需要一个配置文件

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
	<!-- 默认配置，如果没有指定则使用这个配置 -->
	<default-config>
		<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
		<property name="jdbcUrl">jdbc:mysql://localhost:3306/day05?characterEncoding=utf8&amp;serverTimezone=UTC</property>
		<property name="user">root</property>
		<property name="password">123456</property>
	
	</default-config> 
	<!-- 命名的配置 -->
	<named-config name="BarryLee">
		<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
		<property name="jdbcUrl">jdbc:mysql://localhost:3306/day05?characterEncoding=utf8&amp;serverTimezone=UTC</property>
		<property name="user">root</property>
		<property name="password">123456</property>
		<property name="acquireIncrement">5</property><!-- 如果池中数据连接不够时一次增长多少个 -->
		<property name="initialPoolSize">100</property>
		<property name="minPoolSize">50</property>
		<property name="maxPoolSize">1000</property>
		<property name="maxStatements">0</property>
		<property name="maxStatementsPerConnection">5</property> <!-- he's important, but there's only one of him -->
	</named-config>
</c3p0-config> 

测试

    /**
     * 本测试用于数据库连接池的创建
     */
    @Test
    public void testJdbc3(){
        DataSource ds = new ComboPooledDataSource();
        try {
            Connection connection = ds.getConnection();
            PreparedStatement statement = connection.prepareStatement("select * from student");
            ResultSet rs = statement.executeQuery();
            while (rs.next()){
                String sno = rs.getString("id");
                String sname = rs.getString("name");
                String ssex = rs.getString("sex");
                int sage = rs.getInt("age");
                System.out.println(sno + "\t" + sname + "\t" + ssex + "\t" + sage);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }