JDBC
1、Java Database Connectivity 是一个独立于特定数据库的管理系统,通过通用SQL数据库存取公共接口,使得Java程序连接不同的数据库
2、JDBC体系结构,接口主要有
(1)面向应用的API,写代码的时需要调用的API
(2)面向底层数据库的API,负责JAVA与数据库连接
JDBD使用原理
(1)加载JDBC驱动,使用Java程序与数据库连接
(2)获取Connection,进行连接
(3)创建Statement,用来执行SQL语句
(4)Resultset,保存SQL执行之后的结果
1、添加数据
//添加数据
public static void add() throws Exception{
//连接数据库
String url = "jdbc:mysql://127.0.0.1:3306/test";
String user = "root";
String pwd = "123456";
//jdbc驱动管理对象
Connection connection = DriverManager.getConnection(url,user,pwd);
Statement statement = connection.createStatement();
String sql ="insert into student(name) values('张三')";
int result = statement.executeUpdate(sql);
System.out.println(result);
//释放资源
connection.close();
statement.close();
}
2、删除数据
//删除数据库
public static void delete() throws Exception{
//连接数据库
String url = "jdbc:mysql://127.0.0.1:3306/test";
String user = "root";
String pwd = "123456";
//获取连接
Connection connection = DriverManager.getConnection(url,user,pwd);
//执行sql
String sql = "delete from student where id='5'";
Statement statement = connection.createStatement();
//保存结果
int result = statement.executeUpdate(sql);
System.out.println(result);
//释放资源
connection.close();
statement.close();
}
3、更新数据
//更新数据库
public static void update() throws Exception{
String url = "jdbc:mysql://127.0.0.1:3306/test";
String user = "root";
String pwd = "123456";
Connection connection = DriverManager.getConnection(url,user,pwd);
String sql = "update student set name = 'mike' where id = '5'";
Statement statement = connection.createStatement();
int result = statement.executeUpdate(sql);
System.out.println(result);
//释放资源
connection.close();
statement.close();
}
4、查询数据
//查询数据库
public static void query() throws Exception{
//连接数据库
String url = "jdbc:mysql://127.0.0.1:3306/test";
String user = "root";
String pwd = "123456";
//jdbc驱动管理对象
Connection connection = DriverManager.getConnection(url,user,pwd);
// System.out.println(connection);
//执行sql语句
String sql = "select * from student";
Statement statement = connection.createStatement();
//获取查询结果
ResultSet resultSet =statement.executeQuery(sql);
//集合 遍历
while (resultSet.next()) {
int id = resultSet.getInt(1);
String name = resultSet.getNString(2);
System.out.println(id+":"+name);
}
//释放资源
connection.close();
statement.close();
resultSet.close();
}
JDBC处理SQL注入
1、SQL注入:是指web应用程序对用户输入数据的合法性没有判断或过滤不严,攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句,在管理员不知情的情况下实现非法操作,以此来实现欺骗数据库服务器执行非授权的任意查询,从而进一步得到相应的数据信息。(–百度百科)
2、JDBC为了防止SQL注入,在执行时使用Statement的子类PreparedStatement执行SQL语句,其提供了SQL占位符的功能,来防止SQL注入风险,同时还可以避免拼接字符串的情况。
示例:
//SQL注入
public static void sqlIn(String user,String pwd) throws Exception{
String url = "jdbc:mysql://127.0.0.1:3306/test3";
String username = "root";
String password = "123456";
Connection connection = DriverManager.getConnection(url,username,password);
/* String sql = "select * from user where user = '"+user+"' and pwd = '"+pwd+"' ";
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(sql);*/
//通过Statement的子类PreparedStatement解决SQl注入
String sql = "select * from user where user = ? and pwd = ?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//赋值给占位符
preparedStatement.setString(1,user);
preparedStatement.setString(2,pwd);
//获取结果
ResultSet resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
System.out.println("登录成功");
}else {
System.out.println("登录失败");
}
//关闭资源
connection.close();
preparedStatement.close();
}
JDBC事务
1、使用步骤:
(1)关闭connection自动提交
(2)在代码出现异常时回滚
(3)最后无异常,commit事务
//jdbc操作事务
public static void transaction(){
String url = "jdbc:mysql://127.0.0.1:3306/test3";
String username = "root";
String password = "123456";
Connection connection = null;
PreparedStatement preparedStatement1 = null;
PreparedStatement preparedStatement2 = null;
try {
connection = DriverManager.getConnection(url,username,password);
//关闭自动提交
connection.setAutoCommit(false);
String sql = "update user set money= ? where user = ?";
preparedStatement1 = connection.prepareStatement(sql);
//tom有1000块给了mike 800
preparedStatement1.setString(1,"200");
preparedStatement1.setString(2,"tom");
System.out.println(preparedStatement1.executeUpdate());
//mike 借了tom 800块
int a= 10/0;
System.out.println(a);
preparedStatement2 = connection.prepareStatement(sql);
preparedStatement2.setString(1,"1800");
preparedStatement2.setString(2,"mike");
System.out.println(preparedStatement2.executeUpdate());
//事务提交
connection.commit();
} catch (SQLException throwables) {
throwables.printStackTrace();
//事务回滚
try {
connection.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
}finally {
try {
if (connection!=null)
connection.close();
if (preparedStatement1!=null)
preparedStatement1.close();
if (preparedStatement2!=null)
preparedStatement2.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
数据库连接池
1、为了提高数据库执行的效率而存在,主要提高了connection对象的利用率,代码具有复用性
2、给JDBC建立一个缓冲池,其会保存一定数量的连接对象,供不同的业务需求去利用连接数据库去进行操作。
主要步骤:
(1)导入c3p0的jar包
(2)配置xml文件
(3)创建连接池
xml配置:
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<named-config name="c3p0test">
<!-- 指定连接数据源的基本属性 -->
<property name="user">root</property>
<property name="password">123456</property>
<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/test11?useUnicode=true&characterEncoding=UTF-8</property>
<!-- 若数据库中连接数不足时, 一次向数据库服务器申请多少个连接 -->
<property name="acquireIncrement">5</property>
<!-- 初始化数据库连接池时连接的数量 -->
<property name="initialPoolSize">5</property>
<!-- 数据库连接池中的最小的数据库连接数 -->
<property name="minPoolSize">1</property>
<!-- 数据库连接池中的最大的数据库连接数 -->
<property name="maxPoolSize">10</property>
</named-config>
</c3p0-config>
public class c3p0Test {
public static void main(String[] args) throws Exception{
//创建连接池
ComboPooledDataSource dataSource = new ComboPooledDataSource("c3p0test");
Connection connection = dataSource.getConnection();
//执行sql语句
String sql = "select * from student";
Statement statement = connection.createStatement();
//获取查询结果
ResultSet resultSet =statement.executeQuery(sql);
//集合 遍历
while (resultSet.next()) {
int id = resultSet.getInt(1);
String name = resultSet.getString(2);
System.out.println(id+":"+name);
}
//将资源还回连接池
connection.close();
statement.close();
resultSet.close();
}
}
注意:NewproxyConnection是c3p0基于原生连接即connectionImpl,生成的代理对象。