jdbc 连接(java database connectivity)
jdbc使用的步骤
1.通过反射机制加载驱动类–>>相当于是一个jdbc管理的工具类 2.找到我们的 数据库连接池 的url 3.使用 Driver Manager (驱动管理器)获得到数据库连接 4.通过 connect 创建一个状态通道 5.就可以使用 sql语句 向状态通道中传入我们想要实现的sql语句 6.最后,注意一定要关闭所有的连接
通过使用数据库驱动–>>使用 java 来对数据库进行增删改的操作
注意 jar 包的添加
具体实现
示例:
package com;
import java.sql.*;
/**
* @Author: fyw
* @Description:1.通过反射机制加载驱动类-->>相当于是一个jdbc管理的工具类
2.找到我们的数据库连接池的url
3.使用Driver Manager(驱动管理器)获得到数据库连接
4.通过connect创建一个状态通道
5.就可以使用sql语句向状态通道中传入我们想要实现的sql语句
6.最后,注意一定要关闭所有的连接
* @Date Created in 2021-08-28 19:12
* @Modified By:
*/public class Demo01 {
public static void main(String[] args) {
Statement statement = null;
ResultSet resultSet = null;
Connection connection = null;
//1. 加载驱动类
try {
Class.forName("com.mysql.cj.jdbc.Driver");
//2. 获得连接
String url = "jdbc:mysql://localhost:3306/yhp2?serverTimezone=UTC";
String userName = " root ";
String passWord = "123456";
connection = DriverManager.getConnection(url, userName, passWord);
//3. 定义sql语句,创建状态通道,进行sql语句的发送
statement = connection.createStatement();
resultSet = statement.executeQuery("select *from yhp2.emp1");
while (resultSet.next()){//判断是否有下一条数据
System.out.println("姓名:"+resultSet.getString("ename")+"工资:"+resultSet.getDouble("sal"));
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQL Exception throwables) {
throwables.printStackTrace();
}finally {
try {
if (resultSet != null) {
resultSet.close();
}
//5. 关闭资源
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
通过状态通道传入sql语句的时候有几种不同的函数用法,注意返回值,
如果是查询executeQuery()–>>resultSet–>>结果集映射
如果是更新操作(包括update,delete,insert)–>>返回的都是int性的影响行数
package com;
import java.sql.*;
/**
* @Author: fyw
* @Description:
* @Date Created in 2021-08-28 19:12
* @Modified By:
*/public class Demo01 {
public static void main(String[] args) {
Statement statement = null;
ResultSet resultSet = null;
Connection connection = null;
//1. 加载驱动类
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/yhp2?serverTimezone=UTC";
String userName = "root";
String passWord = "123456";
//2. 获得连接
connection = DriverManager.getConnection(url, userName, passWord);
//3. 定义 sql 语句,创建状态通道,进行sql语句的发送
statement = connection.createStatement();
resultSet = statement.executeQuery("select *from yhp2.emp1");
while (resultSet.next()){//判断是否有下一条数据
System.out.println("姓名:"+resultSet.getString("ename")+"工资:"+resultSet.getDouble("sal"));
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
try {
if (resultSet != null) {
resultSet.close();
}
//5. 关闭资源
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
sql注入
package com;
import java.sql.*;
/**
* @Author: fyw
* @Description:
* @Date Created in 2021-08-28 19:12
* @Modified By:
*/public class Demo04 {
public static void main(String[] args) {
Statement statement = null;
ResultSet resultSet = null;
Connection connection = null;
//1. 加载驱动类
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/yhp2?serverTimezone=UTC";
String userName = "root";
String passWord = "123456";
//2. 获得连接
connection = DriverManager.getConnection(url, userName, passWord);
//3. 定义sql语句,创建状态通道,进行sql语句的发送
statement = connection.createStatement();//通过连接通道返回一个状态通道,可以通过状态通道进行传入sql语句
// int result = statement.executeUpdate("insert into yhp2.emp1(empno,ename,hiredate,sal) values(100005,'fyw','2001-04-29',20000)");
String username1 = "sdfsdf";
String pwd1 = " '' or 1=1 ";
resultSet = statement.executeQuery("select *from `5-3kkb作业`.login where username='"+username1+"' and pwd="+pwd1);
if(resultSet.next()){
System.out.println("登录成功");
}else{
System.out.println("登录失败");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
try {
if (resultSet != null) {
resultSet.close();
}
//5. 关闭资源
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
sql注入解决方案
预状态通道
preparedstatement
1.通过connect连接–>>preparstatement生成我们的预状态通道
2.直接将要执行的sql语句传入–>>关键部分用?代替
3.通过preparstatement.set类型,来传入我们的关键参数 注意:下标是从1开始
4.直接通过预状态通道执行sql
package com;
import java.sql.*;
/**
* @Author: fyw
* @Description:
* @Date Created in 2021-08-28 19:12
* @Modified By:
*/public class Demo05 {
public static void main(String[] args) {
Statement statement = null;
ResultSet resultSet = null;
Connection connection = null;
//1. 加载驱动类
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/yhp2?serverTimezone=UTC";
String userName = "root";
String passWord = "123456";
//2. 获得连接
connection = DriverManager.getConnection(url, userName, passWord);
//3. 防止 sql注入 ,先连接一个预状态通道
PreparedStatement preparedStatement = connection.prepareStatement("select * from `5-3kkb作业`.login where pwd=? and username=?");
//4. 通过对 占位符 的赋值,进行sql;
String username1 = "sdfsdf";
String pwd1 = " '' or 1=1 ";
preparedStatement.setString(1,username1);
preparedStatement.setString(2,pwd1);
//5. 执行sql
ResultSet resultSet1 = preparedStatement.executeQuery();
if(resultSet1.next()){
System.out.println("登录成功");
}else{
System.out.println("登录失败");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
try {
if (resultSet != null) {
resultSet.close();
}
//5. 关闭资源
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
