Part01:JDBC
1、JDBC概述:
- JDBC(Java DataBase Connectivity)是一种用于执行SQL语句的Java API。JDBC是Java访问数据库的标准规范,可以为不同的关系型数据库提供统一访问,它由一组用Java语言编写的接口(大部分)和类组成;
- JDBC需要连接驱动,没有驱动将无法完成数据库连接,从而不能操作数据库,每个数据库厂商都需要提供自己的驱动,用来连接自己公司的数据库。
- JDBC和数据库驱动的关系:接口与实现类的关系;JDBC是接口,驱动是接口的实现类;
2、JDBC中核心的三个接口和一个类:
- DriverManager(驱动管理类):帮助我们加载各种驱动
- Connection(数据库连接接口):数据库连接类的根接口,实现类在驱动中
- Statement(执行SQL语句的接口):数据库语句执行类的根接口,实现类在驱动中
- ResultSet(结果集接口):结果集类的根接口,实现类在驱动中
3、使用JDBC操作数据库步骤:
-
先把对应的数据库驱动添加到工程的构建路径
-
代码步骤:
-
1.注册驱动
//把com.mysql.cj.jdbc.Driver这个类加载到内存中,运行这个类中到静态代码块,注册驱动 Class.forName("com.mysql.cj.jdbc.Driver");
-
2.获取数据库的连接对象
//url链接串,为固定语法 String url = "jdbc:mysql://localhost:3306/数据库名”; //username为数据库用户名 String username = “用户名”; //password为数据库密码 String password = "密码"; Connection connection = DriverManager.getConnection(url,username,password);
-
3.获取SQL语句执行者对象
//第一种方法 Statement statement = connection.createStatement(); //第二种方法 PreparedStatement ps = connection.prepareStatement(sql); ps.setObject(占位符序号,具体的参数值)
-
4.获取结果集对象,只有执行查询,才有结果集对象
String sql = "select * from student"; //1 ResultSet rs = statement.executeQuery(sql);//执行查询,返回值是结果集 //2 ResultSet rs = ps.executeQuery();
-
5.处理结果集
//判断有没有下一条记录 while (rs.next()){ //取出对应字段的值,getInt getString getDouble getObject(通用) //getObject(序号)、getObject(“列名”) Object sid = rs.getObject("sid"); Object sname = rs.getObject("sname"); System.out.println(sid+":"+sname); }
-
6.释放资源
rs.close();//只有执行查询,才有结果集对象;如果执行增删改,只有int类型行数 statement.close(); connection.close();
-
Part02:JDBC工具类及使用
1、JDBC工具类:
import java.sql.*;
//这个工具类,主要为我们获取一个数据库连接
public class JDBCUtils {
private static String driverName = "com.mysql.cj.jdbc.Driver";
private static String url="jdbc:mysql://localhost:3306/数据库名";
private static String userName = “数据库用户名";
private static String password = “数据库密码";
//静态代码块,目的:让第一次使用到JDBCUtils时加载驱动,第二次以后不再加载
static {
//1.加载驱动
try {
Class.forName(driverName);
} catch (ClassNotFoundException e) {
throw new RuntimeException("驱动加载失败");
//当驱动加载失败时,让程序直接停止运行
}
}
//获取数据库连接
public static Connection getConnection() throws Exception {
//2.获取数据库的连接对象
Connection connection = DriverManager.getConnection(url,userName,password);
return connection;
}
//关闭所有资源
public static void closeAll(Connection con, Statement st, ResultSet rs) {
if(con != null){
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(st != null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
2、JDBC工具类的使用:使用Statement
-
插入数据:
//插入 public static void insert(){ Connection connection = null; Statement statement = null; ResultSet rs = null; try { //获取连接 connection = JDBCUtils.getConnection(); //获取执行者对象 statement = connection.createStatement(); //String sql = "insert into student values(10019,'大帅比',20,'男','福建','计算机','1班','汉族')”; String sql1 = "insert into student (sid,sname) values(1,'小帅比'),(2,'大美人'),(3,'么古')”; //执行SQL插入数据语句,返回行数 int rows = statement.executeUpdate(sql1); System.out.println("成功插入"+rows+"行"); }catch (Exception e){ e.printStackTrace(); }finally { //关闭资源 JDBCUtils.closeAll(connection,statement,rs); } }
-
删除数据:
public static void delete(){ Connection connection = null; Statement statement = null; ResultSet rs = null; try { //获取连接 connection =JDBCUtils.getConnection(); //获取执行者对象 statement = connection.createStatement(); String sql = "delete from student where sid in(1,2,3)”; //执行SQL删除数据语句,返回行数 int rows = statement.executeUpdate(sql); System.out.println("成功删除"+rows+"条数据"); }catch (Exception e){ e.printStackTrace(); }finally { //关闭资源 JDBCUtils.closeAll(connection,statement,rs); } }
-
修改数据:
//修改 public static void update(){ Connection connection = null; Statement statement = null; ResultSet rs = null; try { //获取连接 connection = JDBCUtils.getConnection(); //获取执行者对象 statement = connection.createStatement(); String sql = "update student set ssex = '女' where sid = 10019”; //执行SQL修改数据语句,返回行数 int rows = statement.executeUpdate(sql); System.out.println("成功修改"+rows+"条数据"); }catch (Exception e){ e.printStackTrace(); }finally { //关闭资源 JDBCUtils.closeAll(connection,statement,rs); } }
-
查询数据:
//查询 public static void query(){ Connection connection = null; Statement statement = null; ResultSet rs = null; try { //通过JDBC工具类,获取到数据库连接 connection = JDBCUtils.getConnection(); //通过连接对象,获取SQL执行对象 statement = connection.createStatement(); //执行sql语句 String sql = "select * from student where sid = 10019"; rs = statement.executeQuery(sql); //处理结果集 while (rs.next()){ Object sid = rs.getObject("sid"); Object sname = rs.getObject("sname"); Object ssex = rs.getObject("ssex"); System.out.println("学号:"+sid); System.out.println("姓名:"+sname); System.out.println("性别:"+ssex); } } catch (Exception e) { e.printStackTrace(); }finally { //释放资源 JDBCUtils.closeAll(connection,statement,rs); } }
3、JDBC工具类的使用:使用preparedStatement
//插入
public static void insert(){
Connection connection = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
//获取连接
connection = JDBCUtils.getConnection();
String sql = "insert into student (sid,sname) values(?,?),(?,?)”;
//创建SQL预处理对象PreparedStatement
ps = connection.prepareStatement(sql);
//给sql语句中的?号占位符赋值
ps.setObject(1,1);
ps.setObject(2,"小红");
ps.setObject(3,2);
ps.setObject(4,"小明”);
//执行语句
int rows = ps.executeUpdate();
System.out.println("成功插入"+rows+"行");
}catch (Exception e){
e.printStackTrace();
}finally {
//释放资源
JDBCUtils.closeAll(connection,ps,rs);
}
}
//删除
public static void delete(){
Connection connection = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
//获取连接
connection = JDBCUtils.getConnection();
String sql = "delete from student where sid in(?,?)”;
//创建SQL预处理对象PreparedStatement
ps = connection.prepareStatement(sql);
//给sql语句中的?号占位符赋值
ps.setInt(1,1);
ps.setInt(2,2);
//执行语句
int rows = ps.executeUpdate();
System.out.println("成功删除"+rows+"条数据");
}catch (Exception e){
e.printStackTrace();
}finally {
//释放资源
JDBCUtils.closeAll(connection,ps,rs);
}
}
//修改
public static void update(){
Connection connection = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
//获取连接
connection = JDBCUtils.getConnection();
String sql = "update student set ssex = ? where sid = ?”;
//获取SQL预处理对象PreparedStatement
ps = connection.prepareStatement(sql);
//设置值
ps.setObject(1,"未知");
ps.setObject(2,10019);
//执行语句
int rows = ps.executeUpdate();
System.out.println("成功修改"+rows+"条数据");
}catch (Exception e){
e.printStackTrace();
}finally {
//释放资源
JDBCUtils.closeAll(connection,ps,rs);
}
}
//查询
public static void query(){
Connection connection = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
//通过JDBC工具类,获取到数据库连接
connection = JDBCUtils.getConnection();
String sql = "select * from student where sid = ?”;
//获取SQL预处理对象PreparedStatement
ps = connection.prepareStatement(sql);
//设置值
ps.setObject(1,10019);
//执行语句
rs = ps.executeQuery();
//处理结果集
while (rs.next()){
Object sid = rs.getObject("sid");
Object sname = rs.getObject("sname");
Object ssex = rs.getObject("ssex");
System.out.println("学号:"+sid);
System.out.println("姓名:"+sname);
System.out.println("性别:"+ssex);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
//释放资源
JDBCUtils.closeAll(connection,ps,rs);
}
}
Part03:SQL注入问题
1、SQL注入:
SQL注入指的是用户输入的内容作为SQL语句语法的一部分,改变了原有SQL真正的意义。
如:
String sql = "select * from user where username = '"+userName+"' and password = '"+passeord+"’";5
2、preparedStatement:
-
预编译对象,是Statement的子类
-
作用:防止SQL注入,内部自动对SQL语句进行转译,让这个SQL语句中和SQL语法有关的字符都失效;
-
通过连接对象的prepareStatement(sql)方法获取对象//注意⚠️:sql语句中不要写具体参数,用?代替,通过preparedStatement对象的set方法为prepareStatement中的sql语句设置具体的值;
public class LoginDemo { public static void main (String[] args){ Scanner scanner = new Scanner(System.in); System.out.println("请输入用户名:"); String userName = scanner.nextLine(); System.out.println("请输入密码:"); String passeord = scanner.nextLine(); Connection connection = null; //Statement statement = null; PreparedStatement ps =null; ResultSet rs = null; try { connection = JDBCUtils.getConnection(); //statement = connection.createStatement(); String sql = "select * from user where username = ? and password = ? "; //获取preparedStatement对象 ps = connection.prepareStatement(sql); //设置值 ps.setObject(1,userName); ps.setObject(2,passeord); rs = ps.executeQuery(); System.out.println(sql); if (rs.next()){ //查询到数据说明用户名密码是数据库中对应的,所以登录成功 System.out.println("登录成功?"); }else { System.out.println("登录失败?"); } }catch (Exception e){ e.printStackTrace(); }finally { JDBCUtils.closeAll(connection,ps,rs); } } }