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);
}
}
}
浙公网安备 33010602011771号