The saddest thing is that a|
收藏闪存小组博问

柒木木木

园龄：2年8个月粉丝：9关注：0

JDBC配置（普通方法和连接池方法）

创建数据库和用到的SQL语句

点击获取 
CREATE DATABASE study;
 
CREATE TABLE admin(
id INT PRIMARY KEY AUTO_INCREMENT,
	username VARCHAR(20),
	PASSWORD VARCHAR(20)
)CHARSET=utf8;
#查全部数据
SELECT *FROM admin;
#有多少条数据
SELECT COUNT(*) FROM admin;
#删除表
DROP TABLE admin;

1. druid连接池方法

创建一个lib文件夹，和src同级，并把druid依赖包放在lib文件夹文件下，
鼠标左键选中druid依赖包，右键之后选择Add，把jar包添加到项目中

1.1 配置文件

创建一个名为druid.properties的文件，放在src目录下，
填入下面配置（根据自己需求）。

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/study?characterEncoding=UTF-8&rewriteBatchedStatements=true
username=root
password=自己的数据库密码
#初始化连接数
initialPoolSize=10
#最小连接数
minIdle=5
#最大连接
maxActive=50
# 超时时间，以毫秒为单位，1000毫秒==1秒
maxWait=5000
1.2 封装JDBCDruid工具类
package com.dz.jdbc.utilis;
 
import com.alibaba.druid.pool.DruidDataSourceFactory;
 
 
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
 
public class JDBCDruid {
    private static DataSource ds;
 
    static {
        try {
            Properties properties = new Properties();
            properties.load(new FileInputStream("练习/src/druid.properties"));
            ds = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
 
    public static Connection getConnection() throws Exception {
        return ds.getConnection();
    }
 
    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 e) {
            throw new RuntimeException(e);
        }
    }
}
1.3 创建一个JDBCUtils类

封装JDBC连接数据库要使用的方法

package com.dz.jdbc.utilis;
 
 
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
 
public class SelectJDBCTest {
    private static String user;
    private static String password;
    private static String url;
    private static String driver;
 
 
    static {
        try {
            Properties properties = new Properties();
            properties.load(new FileInputStream("练习/src/mysql.properites"));
             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 connection() {
        try {
            return DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            //将编译异常转为运行异常
            //调用者，可以选择捕获该异常，也可以默认处理该异常，比较方便
            throw new RuntimeException(e);
        }
    }
 
    //关闭资源
    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 e) {
            throw new RuntimeException(e);
        }
    }
}
1.4 创建Admin类（ 和admin表的记录对应 ）

注意！！！：属性对应admin表的字段，构造器必须要有无参和有参

package com.dz.jdbc.druid;
 
public class Admin {
    private Integer id;
    private String username;
    private String password;
 
    public Admin() {
    }
 
    public Admin(Integer id, String username, String password) {
        this.id = id;
        this.username = username;
        this.password = password;
    }
 
    public Integer getId() {
        return id;
    }
 
    public void setId(Integer id) {
        this.id = id;
    }
 
    public String getUsername() {
        return username;
    }
 
    public void setUsername(String username) {
        this.username = username;
    }
 
    public String getPassword() {
        return password;
    }
 
    public void setPassword(String password) {
        this.password = password;
    }
 
    @Override
    public String toString() {
        return "Admin{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}
1.5 使用JDBCDruid工具类
package com.dz.jdbc.druid;
 
 
import com.dz.jdbc.utilis.JDBCDruid;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
 
public class DruidUtilisTest {
    public static void main(String[] args) throws Exception {
        Connection connection = null;
        String sql = "select * from admin";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        ArrayList<Admin> admins = new ArrayList<>();
        try {
            connection = JDBCDruid.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                String password = resultSet.getString("password");
                admins.add(new Admin(id,username, password));
                for (Admin admin : admins) {
                    System.out.println(admin);
                }
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCDruid.close(resultSet, preparedStatement, connection);
        }
    }
}
1.5 使用dbutils工具类

导入dbutils依赖包到lib,并添加到项目

1.5.1 查询操作
package com.dz.jdbc.dbutils;
 
 
import com.dz.jdbc.druid.Admin;
import com.dz.jdbc.utilis.JDBCDruid;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;
 
import java.sql.Connection;
import java.util.List;
 
public class Dbutils {
    @Test
    //返回结果是多行的情况
    public void testQueryMany() throws Exception {
        Connection connection = JDBCDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select * from admin";
        //返回一个集合
        List<Admin> list = queryRunner.query(connection, sql, new BeanListHandler<>(Admin.class));
        for (Admin admin : list) {
            System.out.println(admin);
        }
        JDBCDruid.close(null, null, connection);
    }
 
    @Test
    //返回结果是单行记录（单个对象）
    public void testQuerySingle() throws Exception {
        Connection connection = JDBCDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select * from admin where id =?";
        Admin admin = queryRunner.query(connection, sql, new BeanHandler<>(Admin.class), 1);
        if (admin != null) {
            System.out.println("查询成功\n" + admin);
        } else {
            System.out.println("不存在");
        }
        JDBCDruid.close(null, null, connection);
    }
 
    @Test
    //查询单行单列
    public void testScalar() throws Exception {
        Connection connection = JDBCDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select username from admin where id =?";
        Object obj = queryRunner.query(connection, sql, new ScalarHandler<>(), 1);
        if (obj != null) {
            System.out.println("查询成功\n" + obj);
        } else {
            System.out.println("不存在");
        }
        JDBCDruid.close(null, null, connection);
    }
}
1.5.2 增删改DML操作
package com.dz.jdbc.dbutils;
 
import com.dz.jdbc.utilis.JDBCDruid;
import org.apache.commons.dbutils.QueryRunner;
import org.junit.Test;
 
import java.sql.Connection;
 
public class DbutilsDML {
    @Test
    public void testDML() throws Exception {
        Connection connection = JDBCDruid.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        // (1) 增加
//        String sql = "insert into admin value (null,?,?)";
        // (2) 修改
//        String sql = "update admin set username=? where username=?";
        // (3) 删除
        String sql = "delete from admin where username=?";
 
        // (1) 增加
//        int affectedRow = queryRunner.update(connection, sql, "李四", "456");
        // (2) 修改
//        int affectedRow = queryRunner.update(connection, sql, "麻子", "李四");
        // (3) 删除
        int affectedRow = queryRunner.update(connection, sql, "麻子");
        System.out.println(affectedRow > 0 ? "执行成功" : "执行没有影响数据库");
        JDBCDruid.close(null, null, connection);
    }
}

2. c3p0连接池方法

2.1 配置文件

导入c3p0依赖包到lib目录，创建一个名为c3p0-config.xml的文件，
放在src目录下，配置如下：

<c3p0-config>
    <named-config name="qimu">
        <!--    连接参数-->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/study</property>
        <property name="user">root</property>
        <property name="password">数据库密码</property>
 
        <!--    连接池-->
        <!--    每次增长的连接数-->
        <property name="acquireIncrement">5</property>
        <!--    初始化连接的数量-->
        <property name="initialPoolSize">10</property>
        <!--    最小连接数量-->
        <property name="minPoolSize">5</property>
        <!--    最大连接数量-->
        <property name="maxPoolSize">50</property>
        <!--    每个连接对象可连接的最多的命令对象数-->
        <property name="maxStatementsPerConnection">2</property>
        <!--    超时时间，以毫秒为单位，1000毫秒==1秒-->
        <property name="checkoutTimeout">5000</property>
    </named-config>
</c3p0-config>
2.2 使用c3p0方法
package com.dz.jdbc.c3p0;
 
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.junit.Test;
 
import java.sql.Connection;
import java.sql.SQLException;
 
public class C3p0Test {
    @Test
    public void c3p0() throws SQLException {
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource("qimu");
//        String sql = "insert into admin2 value (null ,?,?)";
        long steat = System.currentTimeMillis();
        for (int i = 0; i < 50000000; i++) {
            Connection connection = comboPooledDataSource.getConnection();
            connection.close();
        }
        long end = System.currentTimeMillis();
        System.out.println(end - steat);
    }
}

3. 普通连接方法

3.1 配置文件方法封装

创建一个mysql.properites文件,放在src文件目录下，输入以下内容：

user =root//数据库用户名
password =1234//数据库密码
url=jdbc:mysql://localhost:3306/study?characterEncoding=UTF-8&rewriteBatchedStatements=true
//?characterEncoding=utf8   字符集
//?rewriteBatchedStatements=true 批处理
driver=com.mysql.jdbc.Driver

封装成工具类

package com.dz.utilis;
 
 
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
 
public class JDBCUtils {
    private static String user;
    private static String password;
    private static String url;
    private static String driver;
 
 
    static {
        try {
            Properties properties = new Properties();
            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 connection() {
        try {
            return DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            //将编译异常转为运行异常
            //调用者，可以选择捕获该异常，也可以默认处理该异常，比较方便
            throw new RuntimeException(e);
        }
    }
 
    //关闭资源
    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 e) {
            throw new RuntimeException(e);
        }
    }
}
3.2 普通方法的使用方法
3.2.1 查询数据
package com.dz.jdbc;
 
import com.dz.jdbc.utilis.JDBCUtils;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
 
public class TestJDBC {
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        String sql = "select * from admin";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            JDBCUtils.connection();
            preparedStatement = JDBCUtils.connection().prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                String password = resultSet.getString("password");
                System.out.println(id + "\t" + username + "\t" + password);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(resultSet, preparedStatement, connection);
        }
    }
}
3.2.3普通方法的 增，删，改
package com.dz.jdbc;
 
import com.dz.jdbc.utilis.JDBCUtils;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
 
public class SqlJdbcTest {
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        // (1)添加
        String sql = "insert into admin value (null ,?,?)";
        // (2)修改
//        String sql = "update admin set username=? where username=?";
        // (3)删除
//        String sql = "delete from admin where username=?";
        try {
            connection = JDBCUtils.connection();
            preparedStatement = connection.prepareStatement(sql);
            // (1)添加
            preparedStatement.setString(1, "张三");
            preparedStatement.setString(2, "1234");
            // (2)修改
//            preparedStatement.setString(1, "李四");
//            preparedStatement.setString(2, "张三");
            // (3)删除 username为李四的数据
//            preparedStatement.setString(1, "李四");
            int i = preparedStatement.executeUpdate();
            System.out.println(i > 0 ? "执行成功" : "执行失败");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.close(null, preparedStatement, connection);
        }
    }
}

本文作者：柒木木木

本文链接：https://www.cnblogs.com/qimu666/articles/16596477.html

版权声明：本作品采用知识共享署名-非商业性使用-禁止演绎 2.5 中国大陆许可协议进行许可。

posted @   柒木木木  阅读(811)  评论(1)  编辑  收藏  举报
点击右上角即可分享
微信分享提示
SQL AI 助手
评论
收藏
关注
推荐
深色
回顶
收起