目录
在使用JDBC之前需要进行一定的jar包配置
源码如下:
package com.ys;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
/**
* JDBC快速入门
*/
public class jdbcDemo01{
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
// 2.获取连接
String url = "jdbc:mysql://127.0.0.1:3306/itcast";
String username = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url,username,password);
// 3.定义sql语句
String sql = "insert into hello(id,name) values (2,'狄仁杰');";
// 4.获取执行sql的对象
Statement stmt = conn.createStatement();
// 5.执行sql
int count = stmt.executeUpdate(sql); // 受影响的行数
// 6.处理数据
System.out.println(count);
// 7.释放资源
stmt.close();
conn.close();
}
}
注意事项:驱动需要抛出异常
url是固定格式,3306/数据库名字
username是用户名
password是密码
sql是自定义SQL语句
其余都是固定格式
从数据库中获取数据
package com.ys;
import java.sql.*;
/**
* ResultSet获取数据
*/
public class jdbcDemo02 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
// 2.获取连接
String url = "jdbc:mysql://127.0.0.1:3306/itcast?useSSL=false";
String username = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url,username,password);
// 3.定义sql
String sql = "select * from hello";
// 4.获取statement对象
Statement stmt = conn.createStatement();
// 5.执行sql
ResultSet rs = stmt.executeQuery(sql);
// 6.处理结果,遍历rs中所有数据
// 6.1 光标向下移动一行,并且判断当前行是否有数据
while (rs.next()){
//6.2 获取数据 getXxx()
int id = rs.getInt(1);
String name = rs.getString(2);
System.out.println(id);
System.out.println(name);
System.out.println("=================");
}
// 7.释放资源
rs.close();
stmt.close();
conn.close();
}
}
将数据存入集合操作
首先创建Account类
package com.ys.pojo;
public class Account {
private int id;
private String name;
public Account(String name) {
this.name = name;
}
public Account() {
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "Account{" +
"id=" + id +
", name='" + name + '\'' +
'}';
}
}
之后进行存入集合操作
package com.ys.jdbc;
import com.ys.pojo.Account;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class jdbcDemo03 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
// 2.获取连接
String url = "jdbc:mysql://127.0.0.1:3306/itcast?useSSL=false";
String username = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url,username,password);
// 3.定义sql
String sql = "select * from hello";
// 4.获取statement对象
Statement stmt = conn.createStatement();
// 5.执行sql
ResultSet rs = stmt.executeQuery(sql);
// 创建集合
List<Account> list = new ArrayList<>();
// 6.处理结果,遍历rs中所有数据
// 6.1 光标向下移动一行,并且判断当前行是否有数据
while (rs.next()){
// 创建Account对象
Account account = new Account();
//6.2 获取数据 getXxx()
int id = rs.getInt(1);
String name = rs.getString(2);
// 赋值
account.setId(id);
account.setName(name);
// 存入集合
list.add(account);
}
System.out.println(list);
// 7.释放资源
rs.close();
stmt.close();
conn.close();
}
}
解决SQL注入:
登录操作:
package com.ys.jdbc;
import java.sql.*;
public class jdbcDemo04 {
public static void main(String[] args) throws SQLException {
// 获取连接
String url = "jdbc:mysql://127.0.0.1:3306/itcast?useSSL=false";
String username = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url, username, password);
// 接收用户输入 用户名和密码
String name = "小明";
String pwd = "123456";
String sql = "select * from user where name='" + name + "' and pwd='" + pwd + "' ";
// 获取stmt对象
Statement stmt = conn.createStatement();
// 执行sql
ResultSet rs = stmt.executeQuery(sql);
// 判断是否登录成功
if (rs.next()){
System.out.println("登录成功");
}else {
System.out.println("登录失败");
}
}
}
演示sql注入:sql注入下,密码固定,用户名随意,均可登录成功
package com.ys.jdbc;
import java.sql.*;
public class jdbcDemo04 {
public static void main(String[] args) throws SQLException {
// 获取连接
String url = "jdbc:mysql://127.0.0.1:3306/itcast?useSSL=false";
String username = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url, username, password);
// 接收用户输入 用户名和密码
String name = "dahdjkabdka";
String pwd = "' or '1' = '1";
String sql = "select * from user where name='" + name + "' and pwd='" + pwd + "' ";
System.out.println(sql);
// 获取stmt对象
Statement stmt = conn.createStatement();
// 执行sql
ResultSet rs = stmt.executeQuery(sql);
// 判断是否登录成功
if (rs.next()){
System.out.println("登录成功");
}else {
System.out.println("登录失败");
}
}
}
此时通过运行能够发现,sql语句已经发生改变
解决SQL注入:![](https://i-blog.csdnimg.cn/blog_migrate/2223adde8e2d5252f10811272039ae92.png)
源码修改如下:
package com.ys.jdbc;
import java.sql.*;
/**
* 解决SQL注入
*/
public class jdbcDemo05 {
public static void main(String[] args) throws SQLException {
// 获取连接
String url = "jdbc:mysql://127.0.0.1:3306/itcast?useSSL=false";
String username = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url, username, password);
// 接收用户输入 用户名和密码
String name = "dahdjkabdka";
String pwd = "' or '1' = '1";
// 定义SQL 使用? 进行占位
String sql = "select * from user where name= ? and pwd= ? ";
// 获取pstmt对象
PreparedStatement pstmt = conn.prepareStatement(sql);
// 设置?的值
pstmt.setString(1,name);
pstmt.setString(2,pwd);
// 执行sql
ResultSet rs = pstmt.executeQuery();
// 判断是否登录成功
if (rs.next()){
System.out.println("登录成功");
}else {
System.out.println("登录失败");
}
// 释放资源
rs.close();
pstmt.close();
conn.close();
}
}
PreparedStatement开启预编译功能:useServerPrepStmts=true
具体url:
String url = "jdbc:mysql://127.0.0.1:3306/itcast?useSSL=false&useServerPrepStmts=true";