JDBC
连接数据库mysql-connector-java-5.1.37.jar的jar包下载:https://download.csdn.net/download/qq_42795277/12819561
JDBC是一种用于执行SQL语句的Java API,可以为多种关系型数据库提供统一访问,它是由一组用Java语言编写的类和接口组成的
JDBC本质:其实就是Java官方提供的一套规范(接口),用于帮助开发人员快速实现不同关系型数据库的连接
DriverManager
方法名名 | 说明 |
---|---|
static Connection getConnection(String url,String user,Sring password) | 获取数据库连接对象 |
参数
- url:指定连接的路径,语法:jdbc:mysql://ip地址(域名):端口/数据库名称
- user:用户名
- password:密码
Connection
方法名 | 说明 |
---|---|
Statement createStatement() | 获取普通执行者对象 |
PreparedStatement prepareStatement(String sql) | 获取预编译执行者对象 |
setAutoCommint(boolean autoCommint) | 开启事务,参数为false标识开启事务 |
commint() | 提交事务 |
rollback() | 回滚事务 |
void close() | 释放资源 |
Statement
方法名 | 说明 |
---|---|
int executeUpdate(String sql) | 执行DML语句 ,可以执行insert,update,delete语句 |
ResultSet executeQuery(String sql) | 执行DQL语句,可以执行select语句 |
void close | 释放资源 |
ResultSet
方法名 | 说明 |
---|---|
boolean next() | 判断结果集中是否还有数据 |
void close() | 释放资源 |
测试数据sql
/*
SQLyog Ultimate v12.09 (64 bit)
MySQL - 5.5.40
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
create table `user` (
`id` int (11),
`name` varchar (96),
`age` int (11)
);
insert into `user` (`id`, `name`, `age`) values('1','小明','12');
insert into `user` (`id`, `name`, `age`) values('2','小红','23');
insert into `user` (`id`, `name`, `age`) values('3','小李','22');
insert into `user` (`id`, `name`, `age`) values('4','小小','12');
insert into `user` (`id`, `name`, `age`) values('5','小溪','33');
package cn.cdw.demo;
import java.sql.*;
/**
* @author DW-CHEN
*
* jdbc
*
* 需要连接mysql数据库,需要mysql-connector-java-5.1.37-bin.jar包
*/
public class Demo1 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");//获取连接
Statement statement = connection.createStatement();//创建执行者对象
String sql = "select * from user";
ResultSet resultSet = statement.executeQuery(sql);//执行sql语句获取结果
while (resultSet.next()) {//遍历结果
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
int age = resultSet.getInt("age");
System.out.println("id:"+id+" 姓名:"+name+" 年龄:"+age);
}
resultSet.close();
statement.close();
connection.close();
}
}
JDBC工具类
项目的src目录下编写一个配置文件(jdbc.properties)
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test
user=root
password=root
读取配置文件配置相关的参数进行操作
package cn.cdw.utils;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
/**
* @author DW-CHEN
* JDBC工具类
*/
public class JDBCUtil {
private JDBCUtil() {}//定义JDBC工具类私有构造方法,就是不让其它人创建对象
//定义需要的一些成员变量
private static String driver;
private static String url;
private static String user;
private static String password;
private static Connection connection;
static {//读取配置文件进行赋值
try {
InputStream inputStream = JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties properties = new Properties();
properties.load(inputStream);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
user = properties.getProperty("user");
password = properties.getProperty("password");
Class.forName(driver); //注册驱动
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() {//获取数据库连接
try {
connection = DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
public static void close(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet) {//关闭资源,查询时需要
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(Connection connection, PreparedStatement preparedStatement) {//关闭资源,增删改时需要
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
SQl注入攻击
就是利用SQL语句的漏洞来对系统进行攻击
例如:输入用户名:tom,密码:xxx’ or ‘1’ = '1
Statement对象执行的sql语句就是:select * form user where name=tom and password=‘xxx’ or ‘1’=‘1’;
因为:Statement对象在处理执行sql语句时,将密码的一部分内容当作查询条件来执行了
解决SQL注入
PreparedStatement预编译执行者对象,在执行sql语句之前,将sql语句进行提前编译,明确sql语句的格式之后,就不会改变了,剩余的内容都会认为时参数
方法名 | 说明 |
---|---|
int executeUpdate() | 执行insert,update,delete语句 |
ResultSet executeQuery() | 执行select语句 |
JDBC管理事务
- 管理事务的功能类:Connection
- 开启事务:setAutoCommint(boolean autoCommint);参数设为false则开始事务
- 提交事务:commint()
- 回滚事务:rollback()
package cn.cdw.demo;
import cn.cdw.utils.JDBCUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/**
* @author DW-CHEN
* JDBC管理事物
*/
public class Demo4 {
public static void main(String[] args) throws SQLException {
Connection connection = JDBCUtil.getConnection();
PreparedStatement preparedStatement = null;
try {
connection.setAutoCommit(false);//开启事务
preparedStatement = connection.prepareStatement("insert into user values(null,?,?);");
preparedStatement.setString(1, "小溪");
preparedStatement.setInt(2, 33);
int i1 = preparedStatement.executeUpdate();
//int i = 1/0;//测试出现异常,看是否回滚,不添加数据到数据库中
System.out.println(i1);
connection.commit();//没有出现任何异常,就提交数据到数据库
} catch (Exception e) {
connection.rollback();//出现异常,回滚事务
e.printStackTrace();
}finally {
JDBCUtil.close(connection,preparedStatement);//关闭资源
}
}
}
数据库连接池
数据库连接池负责分配,管理和释放数据库连接,它允许应用程序重复使用一个现有的数据库连接,而不是重新建立一个,这个技术能明显提高数据库操作性能
自定义数据库连接池
DataSource
- java.sql.DataSource接口:数据源(数据库连接池),Java官方提供的数据库连接池规范(接口)
- 如果想完成数据库连接池技术,就必须实现DataSource接口
- 核心功能:获取数据库连接对象:Connection getConnection()
package cn.cdw.demo;
import cn.cdw.utils.JDBCUtil;
import javax.sql.DataSource;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.logging.Logger;
/**
* @author DW-CHEN
* 自定义数据库连接池
*/
public class Demo5 implements DataSource {
private static List<Connection> pool = Collections.synchronizedList(new ArrayList<>());//定义一个容器,用来存储多个连接对象
static {//使用静态代码块,通过JDBC获取10个连接对象保存到容器
for (int i = 0; i < 10; i++) {
Connection connection = JDBCUtil.getConnection();
pool.add(connection);
}
}
@Override
public Connection getConnection() throws SQLException {//获取数据库连接池的连接对象
if (pool.size() > 0) {
Connection connection = pool.remove(0);
return connection;
}else {
throw new RuntimeException("数据库连接池对象中的连接对象已用完.....");
}
}
public int getSize() {//获取数据库连接池容器的大小
int size = pool.size();
return size;
}
@Override
public Connection getConnection(String username, String password) throws SQLException {
return null;
}
@Override
public <T> T unwrap(Class<T> iface) throws SQLException {
return null;
}
@Override
public boolean isWrapperFor(Class<?> iface) throws SQLException {
return false;
}
@Override
public PrintWriter getLogWriter() throws SQLException {
return null;
}
@Override
public void setLogWriter(PrintWriter out) throws SQLException {
}
@Override
public void setLoginTimeout(int seconds) throws SQLException {
}
@Override
public int getLoginTimeout() throws SQLException {
return 0;
}
@Override
public Logger getParentLogger() throws SQLFeatureNotSupportedException {
return null;
}
}
测试自定义的数据库连接池
package cn.cdw.demo;
import cn.cdw.utils.JDBCUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* @author DW-CHEN
* 测试自定义的数据库连接池
*/
public class Demo6 {
public static void main(String[] args) throws SQLException {
Demo5 dataSource = new Demo5();
System.out.println("使用之前的数据库连接池容器数量:" + dataSource.getSize());
Connection connection = dataSource.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement("select * from user;");
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
System.out.println(resultSet.getInt("id") + " " + resultSet.getString("name") + " " + resultSet.getInt("age"));
}
JDBCUtil.close(connection, preparedStatement, resultSet);//注意:这里关闭数据库连接后并不会将连接对象归还到数据库连接池中
System.out.println("使用后的数据库连接池容器数量:" + dataSource.getSize());//关闭连接后,连接对象并不会归还,所有连接池中只有9个连接对象
}
}
归还数据库连接
动态代理方式
在不改变目标对象方法的情况下对方法进行增强
动态代理示例
package cn.cdw.demo;
/**
* @author DW-CHEN
* 动态代理
* 在接口中定义被代理对象的所需方法,然后被代理对象实现这些接口重写这个方法
*/
public interface Demo7 {
public void play(String name);
public void look();
}
package cn.cdw.demo;
/**
* @author DW-CHEN
* 动态代理
* 被代理对象,在不修这个被代理的对象的任何代码的前提,使用动态代理改变look()方法的输出内容
*/
public class Demo8 implements Demo7 {
public void play(String game) {
System.out.println("小明在玩"+game);
}
public void look() {
System.out.println("小明在看书");
}
}
package cn.cdw.demo;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
/**
* @author DW-CHEN
* 动态代理
* 代理对象
*/
public class Demo9 {
public static void proxy() {
Demo8 demo8 = new Demo8();//被代理对象
InvocationHandler invocationHandler = new InvocationHandler() {//代理规则
@Override
public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {//被代理对象的所有方法都会经过invoke方法
if(method.getName().equals("look")){//增强被代理对象的look方法
System.out.println("小明已经看完书了....");
return null;//没有返回值,直接返回null
}else {
return method.invoke(demo8, args);//执行方法
}
}
};
Demo7 obj = (Demo7) Proxy.newProxyInstance(demo8.getClass().getClassLoader(), new Class[]{Demo7.class}, invocationHandler);
obj.play("超级玛丽");
obj.look();
}
}
class TestProxyMain{
public static void main(String[] args) {//测试动态代理
Demo9.proxy();
}
}
使用动态代理方式归还数据库连接
package cn.cdw.demo;
import cn.cdw.utils.JDBCUtil;
import javax.sql.DataSource;
import java.io.PrintWriter;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.logging.Logger;
/**
* @author DW-CHEN
* 使用动态代理优化数据库连接池连接对象用完后归还池中的问题
*/
public class Demo10 implements DataSource {
private static List<Connection> pool = Collections.synchronizedList(new ArrayList<>());//定义数据库连接池容器
private static Connection connectionProxy;
static {
for (int i = 0; i < 10; i++) {
Connection connection = JDBCUtil.getConnection();
pool.add(connection);//通过JDBC工具类获取10个连接对象然后保存到数据库连接池容器中
}
}
@Override
public Connection getConnection() throws SQLException {
if (pool.size() > 0) {
Connection connection = pool.remove(0);
InvocationHandler invocationHandler = new InvocationHandler() {//代理规则
@Override
public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
if(method.getName().equals("close")){//如果是close()则归还连接
pool.add(connection);//将连接对象归还添加到数据库连接池中
return null;//close()方法无返回值,所有直接返回null
}else {
return method.invoke(connection, args);//否则执行对象原有的功能
}
}
};
connectionProxy = (Connection) Proxy.newProxyInstance(connection.getClass().getClassLoader(), new Class[]{Connection.class}, invocationHandler);
}
return connectionProxy;
}
public int getSize() {//获取数据库连接池容器的大小
int size = pool.size();
return size;
}
@Override
public Connection getConnection(String username, String password) throws SQLException {
return null;
}
@Override
public <T> T unwrap(Class<T> iface) throws SQLException {
return null;
}
@Override
public boolean isWrapperFor(Class<?> iface) throws SQLException {
return false;
}
@Override
public PrintWriter getLogWriter() throws SQLException {
return null;
}
@Override
public void setLogWriter(PrintWriter out) throws SQLException {
}
@Override
public void setLoginTimeout(int seconds) throws SQLException {
}
@Override
public int getLoginTimeout() throws SQLException {
return 0;
}
@Override
public Logger getParentLogger() throws SQLFeatureNotSupportedException {
return null;
}
}
测试
package cn.cdw.demo;
import cn.cdw.utils.JDBCUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* @author DW-CHEN
* 测试自定义的数据库连接池并自动归还连接对象
*/
public class Demo11 {
public static void main(String[] args) throws SQLException {
Demo10 dataSource = new Demo10();
System.out.println("使用之前的数据库连接池容器数量:" + dataSource.getSize());
Connection connection = dataSource.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement("select * from user;");
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
System.out.println(resultSet.getInt("id") + " " + resultSet.getString("name") + " " + resultSet.getInt("age"));
}
JDBCUtil.close(connection, preparedStatement, resultSet);
System.out.println("使用后的数据库连接池容器数量:" + dataSource.getSize());//关闭连接资源后会自动归还到连接池,所有连接池中还是10个连接对象资源
}
}
开源数据库连接池
C3P0数据库连接池
C3P0数据库所需jar包和配置文件下载地址:https://download.csdn.net/download/qq_42795277/12819563
- C3P0数据库连接池的配置文件会自动加载,但是必须叫c3p0-config.xml或者c3p0-config.properties
package cn.cdw.demo;
import cn.cdw.utils.JDBCUtil;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* @author DW-CHEN
* C3P0数据库连接池
*/
public class Demo12 {
public static void main(String[] args) throws SQLException {
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();//C3P0数据库连接池
Connection connection = comboPooledDataSource.getConnection();//获取连接池对象
PreparedStatement preparedStatement = connection.prepareStatement("select * from user; ");
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
System.out.println(resultSet.getInt("id") +" " + resultSet.getString("name") + " " + resultSet.getInt("age"));
}
JDBCUtil.close(connection, preparedStatement, resultSet);//关闭资源
}
}
Druid数据库连接池
Druid数据库所需jar包和配置文件下载地址:https://download.csdn.net/download/qq_42795277/12819563
- Druid不会自动加载配置文件,需要我们手动加载,但是文件的名称可以自定义
package cn.cdw.demo;
import cn.cdw.utils.JDBCUtil;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;
/**
* @author DW-CHEN
* Druid数据库连接池
* 注意,它并不会自动加载配置文件
*/
public class Demo13 {
public static void main(String[] args) throws Exception {
InputStream resourceAsStream = Demo13.class.getClassLoader().getResourceAsStream("druid.properties");//加载druid连接池配置文件
Properties properties = new Properties();
properties.load(resourceAsStream);
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);//druid连接池
Connection connection = dataSource.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement("select * from user; ");
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
System.out.println(resultSet.getInt("id") +" " + resultSet.getString("name") + " " + resultSet.getInt("age"));
}
JDBCUtil.close(connection, preparedStatement, resultSet);//关闭资源
}
}
数据库连接池工具类
package cn.cdw.utils;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
/**
* @author DW-CHEN
* 数据库连接池工具类
*/
public class DataSourceUtil {
private DataSourceUtil(){}//私有化构造方法,不让其它人创建对象
private static DataSource dataSource;//数据源
static {
try {
InputStream resourceAsStream = DataSourceUtil.class.getClassLoader().getResourceAsStream("druid.properties");//加载druid连接池配置文件
Properties properties = new Properties();
properties.load(resourceAsStream);
dataSource = DruidDataSourceFactory.createDataSource(properties);//druid连接池
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {//获取连接
Connection connection = dataSource.getConnection();
return connection;
}
public static DataSource getDataSource() {//获取数据源
return dataSource;
}
public static void close(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet) {//关闭资源,查询时需要
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(Connection connection, PreparedStatement preparedStatement) {//关闭资源,增删改时需要
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}