1 jdbc介绍
2 jdbc使用
驱动包:链接:https://pan.baidu.com/s/1JHNtgBS8VwVvAX41RsMbTA
提取码:ehuk
import com.mysql.jdbc.PreparedStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestJdbc {
public static void main(String[] args) throws ClassNotFoundException, SQLException, SQLException {
/* 1: 注册驱动
在 mysql5.0 以后不需要注册驱动,已经自带驱动
但是我们写上可以向前兼容
*/
/*这样可以注册驱动的原因:
public class Driver extends NonRegisteringDriver implements java.sql.Driver {
public Driver() throws SQLException {} static {
try {
DriverManager.registerDriver(new Driver());
} catch (SQLException var1) {
throw new RuntimeException("Can't register driver!");}}}
*/
Class.forName("com.mysql.jdbc.Driver");
/* 2:获取数据库连接对象
DriverManger 类功能:
1)管理和注册驱动
2) 创建数据库的连接
<1>:Connection getConnection (String url, String user, String password) 通
过连接字符串,用户名,密码来得到数据库的连接对象
<2>:Connection getConnection (String url, Properties info->自己编写配置文
件) 通过连接字符串,属性对象来得到连接对象
编写配置文件格式:url = "jdbc:mysql://localhost:3306/连接数据库名称";
<3>:出现乱码解决方法(指定数据库比编码方式):
jdbc:mysql://localhost:3306/数据库?characterEncoding=utf8
<4>:简写 url 前提:必须是本地服务器,端口号是 3306
简写: jdbc:mysql:///数据库名
*/
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/nanzi", "root", "root");
/* 3 :获取执行 sql 语句的对象
Connection 类:
1)获取执行 sql 语句的对象
<1>:获取普通执行者对象:Statement createStatement();
Statement 对象由于存在 sql 注入不安全问题,已经不推荐使用
sql 注入问题:就是通过特殊的拼接 sql 语句,使数据库出现错误
示例:select * from 表名 where name='张三' and pass='123' or 'a'='a';
这样数据库就会出现数据混乱,给与认可
<2>:获取预编译执行者对象:PreparedStatement prepareStatement(String sql);
预编译对象解决了 sql 注入问题,通过问号作为占位符,先进行预编译,然后通过预编
译对象对占位符进行赋值
赋值方式:PreparedStatement 对象.setXxx(?序号,值->注意字符串和数字);
注意:在这块赋值从 1 开始,非从 0 开始
2)处理事务(主意顺序)
开启事务:setAutoCommit(boolean autoCommit); 参数为 false,则开启事务。
提交事务:commit();
回滚事务:rollback();
特别注意:事务的操作要在业务层(service)实现,dao 层一般只做简单的数据库逻辑
*/
PreparedStatement preparedStatement = (PreparedStatement)
connection.prepareStatement("select * from nanfengmessage where name=? and pass=? ?");
preparedStatement.setString(1, "name");
preparedStatement.setInt(2, 123);
/*4: 执行 sql 语句并接收返回对象:
1)执行 dml:修改等
int i = preparedStatement.executeUpdate();
返回值表示影响行数:一般用作 sql 语句的执行结果判断,大于 0 标志执
行成功,否则执行失败
2)执行 dql:查询
ResultSet resultSet = preparedStatement.executeQuery();
ResultSet 执行结果封装类:
常用方法:
next() boolean; 用于获取查询结果数据表中的下一组数据(相当于
在表中含有一个箭头,每次指向下一行), 若是有数据
则返回 true,没有数 false(类似迭代器),遍历常用
getXXX("列名") XXX 类型; 指定列名获取数据结果
getXXX(列号) XXX 类型; 制定法列序号获取数据结果
*/
ResultSet resultSet = preparedStatement.executeQuery();
//遍历一下结果
while (((ResultSet) resultSet).next()) {
String name = resultSet.getString("name");
System.out.println(name);
//具体处理....
}
/*5: 最后释放资源: */
connection.close();
preparedStatement.close();
resultSet.close();
}
}
数据获取时候对比表:
工具类的抽取:
public class JDBCUtils {
private static String driverClass;
private static String databaseName;
private static String databasePass;
private static String databaseURL;
//通过静态代码块初始化数据
static {
//获取当前类的字节码文件,通过字节码文件获取类加载器
ClassLoader classLoader = JDBCUtils.class.getClassLoader();
//通过类加载器将指定文件加载获取输入流对象(该文件路经必须位于src 目录下或者绝对路径)
InputStream resourceAsStream =
classLoader.getResourceAsStream("db.properties");
//创建 Properties 集合,通过 load 方法将流中数据加载
Properties properties = new Properties();
try {
properties.load(resourceAsStream);
//获取文件中的数据(根据文件中的键名,别写错了)
databaseURL = properties.getProperty("URL");
driverClass = properties.getProperty("driverClass");
databaseName = properties.getProperty("name");
databasePass = properties.getProperty("password");
//注册驱动
Class.forName(driverClass);
} catch (ClassNotFoundException | IOException e) {
e.printStackTrace();
}
}
private JDBCUtils() {
}
public static Connection getConnection() throws SQLException {
Connection connection = DriverManager.getConnection(databaseURL, databaseName, databasePass);
return connection;
}
public static void close(Connection connection, Statement statement) {
//这块参数采用 Statement,PreparedStatement 是 Statemment 子类(多态)
//做非空判断
if (statement != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(Connection connection, Statement statement, ResultSet resultSet) {
if (resultSet != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
close(connection, statement);
}
}
数据库连接几本文件配置:
driver=com.mysql.jdbc.Driver
url=jdbc:mysql:///mybatismussary
username=root
password=root
数据库连接池
3 c3p0数据库连接池
使用步骤:
(1) 导入 jar 包(三个)
<1> c3p0-0.9.5.2.jar
<2> mchange-commons-java-0.2.12.jar(c3p0 依赖包)
<3> 数据库驱动 jar 包:mysql-connector-java-5.1.37-bin.jar
链接:https://pan.baidu.com/s/1ZNItHGXaTT6to2RoJkr7_Q
提取码:fjy4
(2) 定义配置文件: 名称: c3p0.properties 或者 c3p0-config.xml(必须这样命名) 路径:直接将文件放在 src 目录下即可。
C3p0-config.xml(可以直接复制粘贴使用)
< c3p0-config >< default-config >< property name ="driverClass" >com.mysql.jdbc.Driverproperty>< property name ="jdbcUrl" >jdbc:mysql://localhost:3306/db4property>< property name ="user" >rootproperty>< property name ="password" >rootproperty>< property name ="initialPoolSize" >5property>< property name ="maxPoolSize" >10property>< property name ="checkoutTimeout" >3000property>default-config>< named-config name ="otherc3p0" >< property name ="driverClass" >com.mysql.jdbc.Driverproperty>< property name ="jdbcUrl" >jdbc:mysql://localhost:3306/db3property>< property name ="user" >rootproperty>< property name ="password" >rootproperty>< property name ="initialPoolSize" >5property>< property name ="maxPoolSize" >8property>< property name ="checkoutTimeout" >1000property>named-config>c3p0-config>
c3p0.properties 示例(复制修改可用):
c3p0.JDBC.url=jdbc:mysql://localhost:3306/库名?characterEncoding=utf8 c3p0.DriverClass=com.mysql.jdbc.Driver c3p0.user=root c3p0.pwd=root c3p0.acquireIncrement=3 c3p0.idleConnectionTestPeriod=60 c3p0.initialPoolSize=10 c3p0.maxIdleTime=60 c3p0.maxPoolSize=20 c3p0.maxStatements=100 c3p0.minPoolSize=5
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
public class C3p0PoolUtils {
private static DataSource ds;
static {
//获取数据库连接池对象
ds = new ComboPooledDataSource();
}
private C3p0PoolUtils() {
}
public static Connection getConnection() {
Connection connection = null;
try {
connection = ds.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
}
4 Druid:数据库连接池实现技术,由阿里巴巴提供的
提取码:sibk
driverClassName = com.mysql.jdbc.Driverurl = jdbc:mysql://localhost:3306/db3username = rootpassword = root# 初始化连接数量initialSize = 5# 最大连接数maxActive = 10# 最大等待时间maxWait = 3000
(4) 获取数据库连接池对象:通过工厂来来获取 DruidDataSourceFactory
(5) 获取连接:getConnection
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class DruidJDBCUtils {
private static DataSource dataSource;
//加载配置文件
static {
Properties properties = new Properties();
InputStream resourceAsStream =
ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
try {
properties.load(resourceAsStream);
//获取数据库连接池对象
dataSource = DruidDataSourceFactory.createDataSource(properties);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
private DruidJDBCUtils() {
}
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
public static DataSource getDataSource() throws SQLException {
return dataSource;
}
public static void close(Connection connection, Statement statement) {
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(Connection connection, Statement statement, ResultSet
resultSet) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
close(connection, statement);
}
}
测试代码:
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;
public class NanFengDruid {
public static void main(String[] args) throws Exception {
//加载配置文件
Properties properties = new Properties();
properties.load(NanFengDruid.class.getClassLoader().getResourceAsStream("druid.properties"));
//获取连接对象工厂
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
//获取数据库连接对象
Connection connection = dataSource.getConnection();
PreparedStatement preparedStatement =
connection.prepareStatement("select * from nanfengmessage");
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
System.out.println(resultSet.getInt("id") + "\t" +
resultSet.getString("name") + "\t" + resultSet.getString("pass"));
}
resultSet.close();
preparedStatement.close();
connection.close();
}
}
另外Spring也提供了数据库连接池,这里就不做单独的说明了,在后面的Spring家族的概述中做具体的论述!
5自定义数据库连接池(简述)
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
public class NanFengCreatDataSourcePool {
//保障线程安全
private static List<Connection> list =
Collections.synchronizedList(new ArrayList<Connection>());
static {
for (int i = 0; i < 10; i++) {
try {
Connection connection = JDBCUtils.getConnection();
list.add(connection);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public int getSize() {
return list.size();
}
public static Connection getConnection() {
if (list.size() > 0) {
Connection connection = list.get(0);
list.remove(0);
return connection;
} else {
throw new RuntimeException("连接对象没有了");
}
}
public static void clos(){
}
}