概述
1.为什么要使用JDBC
2.JDBC是什么
3.下载驱动包与使用
4.JDBC的操作步骤
5.JDBC入门案例
6.练习
7.SQL注入问题与解决防范
8.总结
为什么要使用JDBC
使用JDBC技术将Java程序与数据库连接, 让数据库存储与管理项目的数据
JDBC是什么
JDBC(Java与数据库连接):Java DataBase Connectivity
该技术是由Java提供的一种操作数据库的技术, 并提供了统一的标准
但是该技术不在JDK中, 所以需要下载并使用提供JDBC技术的JAR包
Jar包的本质: 就是别人写好的代码打成类似于压缩包的文件, 可以理解为大型工具包
JAR包的下载与使用
下载地址:https://mvnrepository.com/
搜索mysql
使用jar包
步骤:
1.在项目的根目录下创建文件夹,这个文件夹必须命名为lib
2.将需要使用的jar包拷贝到lib文件夹下
3.选择jar包,点击右键选择Add as Library...
注意:一个项目一个JAR包只需要添加一次
JDBC的操作步骤
1.加载驱动, 反射的技术
5.+的驱动位置:com.mysql.jdbc.Driver
8.+的驱动位置:com.mysql.cj.jdbc.Driver
2.创建连接
3.获取sql语句执行对象
4.执行sql语句
5.处理结果
6.关闭资源
JDBC注册案例
package demo;
import java.sql.*;
import java.util.Scanner;
public class Demo01 {
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
private static final String URL = "jdbc:mysql://localhost:3306/test?useSSL=false&characterEncoding=utf-8";
private static final String SQL_USERNAME = "root";
private static final String SQL_PASSWORD = "123456";
public static void main(String[] args) {
try {
regist();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void regist() throws SQLException {
//注册步骤
//1.让用户输入信息
Scanner scanner = new Scanner(System.in);
System.out.println("请输入昵称: ");
String name = scanner.next();
System.out.println("请输入账号: ");
String username = scanner.next();
System.out.println("请输入密码: ");
String password = scanner.next();
System.out.println("请输入性别: ");
String sex = scanner.next();
System.out.println("请输入年龄: ");
int age = scanner.nextInt();
//2.判断账号是否存在, 操作数据库查询账号是否存在
//2.1加载驱动
//2.2获取连接
Connection connection = DriverManager.getConnection(URL, SQL_USERNAME, SQL_PASSWORD);
//2.3创建sql语句执行对象
Statement statement = connection.createStatement();
//2.4执行sql语句
String sql = "select s_id from myusers where s_username = '" + username + "'";
//返回值为查询到的数据集合
ResultSet resultSet = statement.executeQuery(sql);
if (resultSet.next()) {
//查询到了数据, 账号已经存在
System.out.println("账号已存在");
return;
}
//3.不存在存储用户信息
String insertSQL = "insert into myusers(s_name, s_username, s_password, s_sex, s_age) values('" +
name + "','" +
username + "','" +
password + "','" +
sex + "'," +
age + ")";
int i = statement.executeUpdate(insertSQL);
//4.判断是否存储成功, 如果成功显示注册成功, 否则显示注册失败
if (i > 0) {
System.out.println("注册成功");
} else {
System.out.println("注册失败");
}
//最后关闭资源
if (resultSet != null && !resultSet.isClosed()) {
resultSet.close();
}
if (statement != null && !statement.isClosed()) {
statement.close();
}
if (connection != null && !connection.isClosed()) {
connection.close();
}
}
}
JDBC登录案例
public static void login() throws SQLException {
//输入账号密码
Scanner scanner = new Scanner(System.in);
System.out.println("请输入账号: ");
String username = scanner.next();
System.out.println("请输入密码: ");
String password = scanner.next();
//判断账号密码是否存在
Connection connection = DriverManager.getConnection(URL, SQL_USERNAME, SQL_PASSWORD);
Statement statement = connection.createStatement();
String selectSQL = "select s_id, s_name, s_username, s_password, s_sex, s_age from myusers where s_username = '" +
username + "' and s_password = '" + password + "'";
//返回值为查询到的数据集合
ResultSet resultSet = statement.executeQuery(selectSQL);
if (resultSet.next()) {
int s_id = resultSet.getInt("s_id");
String s_name = resultSet.getString("s_name");
String s_username = resultSet.getString("s_username");
String s_password = resultSet.getString("s_password");
String s_sex = resultSet.getString("s_sex");
int s_age = resultSet.getInt("s_age");
System.out.println(s_id + "\t" + s_name + "\t" + s_username + "\t" + s_password + "\t" + s_sex + "\t" + s_age);
System.out.println("登陆成功");
}
//最后关闭资源
if (resultSet != null && !resultSet.isClosed()) {
resultSet.close();
}
if (statement != null && !statement.isClosed()) {
statement.close();
}
if (connection != null && !connection.isClosed()) {
connection.close();
}
}
SQL注入问题
解决: java提供了对sql注入问题的解决方案
使用：PreparedStatement
示例
public static void login0() throws SQLException {
//输入账号密码
Scanner scanner = new Scanner(System.in);
System.out.println("请输入账号: ");
String username = scanner.next();
System.out.println("请输入密码: ");
String password = scanner.next();
Connection connection = DriverManager.getConnection(URL, SQL_USERNAME, SQL_PASSWORD);
//PreparedStatement是Statement的子类
//prepareStatement需要预加载要执行的sql语句
//?表示值的占位符
//PreparedStatement的一个sql语句中可以提供多个?
PreparedStatement preparedStatement = connection.prepareStatement("select * from myusers where s_username = ? and s_password = ?");
//替换占位符对应的字符串
//1参: 第几个?号
//2参: 替换?号的字符串
preparedStatement.setString(1, username);
preparedStatement.setString(2, password);
//执行
ResultSet resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
int s_id = resultSet.getInt("s_id");
String s_name = resultSet.getString("s_name");
String s_username = resultSet.getString("s_username");
String s_password = resultSet.getString("s_password");
String s_sex = resultSet.getString("s_sex");
int s_age = resultSet.getInt("s_age");
System.out.println(s_id + "\t" + s_name + "\t" + s_username + "\t" + s_password + "\t" + s_sex + "\t" + s_age);
System.out.println("登陆成功");
}
//最后关闭资源
if (resultSet != null && !resultSet.isClosed()) {
resultSet.close();
}
if (preparedStatement != null && !preparedStatement.isClosed()) {
preparedStatement.close();
}
if (connection != null && !connection.isClosed()) {
connection.close();
}
}
package demo;
import java.sql.*;
import java.util.Scanner;
public class Demo01 {
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
private static final String URL = "jdbc:mysql://localhost:3306/test?useSSL=false&characterEncoding=utf-8";
private static final String SQL_USERNAME = "root";
private static final String SQL_PASSWORD = "123456";
public static void main(String[] args) {
try {
regist();
// login();
// login0();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void regist() throws SQLException {
//注册步骤
//1.让用户输入信息
Scanner scanner = new Scanner(System.in);
System.out.println("请输入昵称: ");
String name = scanner.next();
System.out.println("请输入账号: ");
String username = scanner.next();
System.out.println("请输入密码: ");
String password = scanner.next();
System.out.println("请输入性别: ");
String sex = scanner.next();
System.out.println("请输入年龄: ");
int age = scanner.nextInt();
//2.判断账号是否存在, 操作数据库查询账号是否存在
//2.1加载驱动
//2.2获取连接
Connection connection = DriverManager.getConnection(URL, SQL_USERNAME, SQL_PASSWORD);
//2.3创建sql语句执行对象
Statement statement = connection.createStatement();
//2.4执行sql语句
String sql = "select s_id from myusers where s_username = '" + username + "'";
//返回值为查询到的数据集合
ResultSet resultSet = statement.executeQuery(sql);
if (resultSet.next()) {
//查询到了数据, 账号已经存在
System.out.println("账号已存在");
return;
}
//3.不存在存储用户信息
String insertSQL = "insert into myusers(s_name, s_username, s_password, s_sex, s_age) values('" +
name + "','" +
username + "','" +
password + "','" +
sex + "'," +
age + ")";
int i = statement.executeUpdate(insertSQL);
//4.判断是否存储成功, 如果成功显示注册成功, 否则显示注册失败
if (i > 0) {
System.out.println("注册成功");
} else {
System.out.println("注册失败");
}
//最后关闭资源
if (resultSet != null && !resultSet.isClosed()) {
resultSet.close();
}
if (statement != null && !statement.isClosed()) {
statement.close();
}
if (connection != null && !connection.isClosed()) {
connection.close();
}
}
public static void login() throws SQLException {
//输入账号密码
Scanner scanner = new Scanner(System.in);
System.out.println("请输入账号: ");
String username = scanner.next();
System.out.println("请输入密码: ");
String password = scanner.next();
//判断账号密码是否存在
Connection connection = DriverManager.getConnection(URL, SQL_USERNAME, SQL_PASSWORD);
Statement statement = connection.createStatement();
String selectSQL = "select s_id, s_name, s_username, s_password, s_sex, s_age from myusers where s_username = '" +
username + "' and s_password = '" + password + "'";
//返回值为查询到的数据集合
ResultSet resultSet = statement.executeQuery(selectSQL);
if (resultSet.next()) {
int s_id = resultSet.getInt("s_id");
String s_name = resultSet.getString("s_name");
String s_username = resultSet.getString("s_username");
String s_password = resultSet.getString("s_password");
String s_sex = resultSet.getString("s_sex");
int s_age = resultSet.getInt("s_age");
System.out.println(s_id + "\t" + s_name + "\t" + s_username + "\t" + s_password + "\t" + s_sex + "\t" + s_age);
System.out.println("登陆成功");
}
//最后关闭资源
if (resultSet != null && !resultSet.isClosed()) {
resultSet.close();
}
if (statement != null && !statement.isClosed()) {
statement.close();
}
if (connection != null && !connection.isClosed()) {
connection.close();
}
}
public static void login0() throws SQLException {
//输入账号密码
Scanner scanner = new Scanner(System.in);
System.out.println("请输入账号: ");
String username = scanner.next();
System.out.println("请输入密码: ");
String password = scanner.next();
Connection connection = DriverManager.getConnection(URL, SQL_USERNAME, SQL_PASSWORD);
//PreparedStatement是Statement的子类
//prepareStatement需要预加载要执行的sql语句
//?表示值的占位符
//PreparedStatement的一个sql语句中可以提供多个?
PreparedStatement preparedStatement = connection.prepareStatement("select * from myusers where s_username = ? and s_password = ?");
//替换占位符对应的字符串
//1参: 第几个?号
//2参: 替换?号的字符串
preparedStatement.setString(1, username);
preparedStatement.setString(2, password);
//执行
ResultSet resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
int s_id = resultSet.getInt("s_id");
String s_name = resultSet.getString("s_name");
String s_username = resultSet.getString("s_username");
String s_password = resultSet.getString("s_password");
String s_sex = resultSet.getString("s_sex");
int s_age = resultSet.getInt("s_age");
System.out.println(s_id + "\t" + s_name + "\t" + s_username + "\t" + s_password + "\t" + s_sex + "\t" + s_age);
System.out.println("登陆成功");
}
//最后关闭资源
if (resultSet != null && !resultSet.isClosed()) {
resultSet.close();
}
if (preparedStatement != null && !preparedStatement.isClosed()) {
preparedStatement.close();
}
if (connection != null && !connection.isClosed()) {
connection.close();
}
}
}
运行结果
总结
JDBC:
操作步骤:
1.加载驱动
Class.forName("驱动类所在的包名.类名");
2.获取连接
Connection c = DriverManger.getConnection(数据库url,数据库账号,数据库密码);
3.获取sql语句执行对象
//存在sql注入问题
Statement s = c.createStatement();
//解决了sql注入问题
PreparedStatement s = c.prepareStatement(sql);
4.执行sql语句
//用于执行DML操作,返回值是受影响行数
int i = s.executeUpdate();
//用于执行DQL操作,返回值是查询到的结果
ResultSet set = s.executeQuery();
5.处理结果
根据业务决定
6.关闭资源
如果执行的是查询操作需要关闭查询结果集
关闭sql语句执行对象
关闭连接对象
JDBC操作核心类:
DriverManger:驱动管理器
作用:获取连接
提供的方法:
Connection getConnection(url,username,password);
Connection:数据库连接对象
作用:获取sql语句执行对象,执行事务
注意:默认自动提交
提供的方法:
//有sql注入风险
Statement createStatement();
//解决了sql注入风险
PreparedStatement prepareStatement(sql);
//用于调用存储过程
CallableStatement prepareCall(sql);
//回滚
void rollback();
//提交
void commit();
//设置是否自动添加,默认为true
//c.setAutoCommit(false);关闭自动提交并开启事务
void setAutoCommit(boolean autoCommit);
Statement
作用:执行sql语句
提供的方法:
int executeUpdate():用于执行DML操作
ResultSet executeQuery():用于执行DQL操作
boolean execute():用于执行DDL操作
子类:
PreparedStatement
作用:解决sql注入问题
提供的方法:
setInt(?号的位置,替换?号的值);
setString(?号的位置,替换?号的值);
...
注意:?表示占位符,位置从1开始
子类:
CallableStatement
作用:调用存储过程
ResultSet
作用:存储查询的结果
提供的方法:
boolean next():移动游标
getInt(列名);
getString(列名);
...
练习
将银行项目数据用数据库管理,并使用jdbc操作
205
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
