JDBC程序，注入问题，事务

最新推荐文章于 2022-03-28 15:31:29 发布

FZY_

最新推荐文章于 2022-03-28 15:31:29 发布

阅读量528

点赞数

分类专栏：笔记文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/Hold__/article/details/121518226

版权

笔记专栏收录该内容

13 篇文章 0 订阅

订阅专栏

数据表

CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;

USE jdbcStudy;

CREATE TABLE users(
	id INT PRIMARY KEY,
	NAME VARCHAR(40),
	PASSWORD VARCHAR(40),
	email VARCHAR(60),
	birthday DATE
);

INSERT INTO users(id,NAME,PASSWORD,email,birthday)
VALUES(1,'zhansan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'angwu','123456','wangwu@sina.com','1979-12-04')

Java代码

package com.fzy.lesson01;

import java.sql.*;

//我的第一个JDBC程序
public class JdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver"); //固定写法

        //2.用户信息和url
        //useUnicode=true是支持中文编码
        //characterEncoding=utf8是设置中文字符集为utf8
        //useSSL=true是使用安全连接
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username = "root";
        String password = "123456";

        //3.连接成功，数据库对象 Connection 代表数据库
        Connection connection = DriverManager.getConnection(url, username, password);

        //4.执行SQL的对象 Statement 执行sql的对象
        Statement statement = connection.createStatement();
        //5.执行SQL的对象去执行SQL，可能存在结果，查看返回结果
        String sql = "select * from users";

        ResultSet resultSet = statement.executeQuery(sql); //返回的结果集
        while (resultSet.next()){
            System.out.println("id=" + resultSet.getObject("id"));
            System.out.println("name=" + resultSet.getObject("NAME"));
            System.out.println("pwd=" + resultSet.getObject("PASSWORD"));
            System.out.println("email=" + resultSet.getObject("email"));
            System.out.println("birth=" + resultSet.getObject("birthday"));
            System.out.println("-----------------------------");
        }

        //6.释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

对象解释

//connection 代表数据库
connection.setAutoCommit(true); //设置自动提交
connection.rollback(); //事务回滚
connection.commit(); //事务提交

//statement执行SQL的对象
String sql = "增删改查SQL语句";
statement.executeQuery(sql); //执行查询操作返回结果集
statement.execute(sql); //执行任何SQL
statement.executeUpdate(sql); //执行增删改操作，返回受影响的行数

每次增删改查都要写一样的代码会很冗余，所以我们要编写一个工具类

在src目录下增加一个db.properties文件，内容如下：

driver = com.mysql.jdbc.Driver
url  =jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username = root
password = 123456

编写工具类

package com.fzy.lesson02.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtils {
    private static  String driver = null;
    private static  String url = null;
    private static  String username = null;
    private static  String password = null;

    static {
        try{
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");
            //驱动只用加载一次，放在static中
            Class.forName(driver);
        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    //获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,username,password);
    }

    //释放连接资源
    public static void release(Connection conn, Statement st, ResultSet rs) {
        if (rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (st != null){
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

添加

package com.fzy.lesson02.utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestInsert {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
       
        try{
            conn = JdbcUtils.getConnection(); //获取数据库连接
            st = conn.createStatement(); //获得SQL的执行对象
            String sql = "insert into users(id,NAME,PASSWORD,email,birthday) " +
                    "values(4,'wangtiewan','123456','2272983012@qq.com','2017-01-01')";
            int num = st.executeUpdate(sql);
            if (num > 0){
                System.out.println("插入成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

更新

package com.fzy.lesson02.utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestUpdate {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        
        try {
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();
            String sql = "update users set NAME = 'liuyifei',PASSWORD = '654321' where id = 4";
            int num = st.executeUpdate(sql);
            if (num > 0){
                System.out.println("更新成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

删除

package com.fzy.lesson02.utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestDelete {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        
        try {
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();
            String sql = "delete from users where id = 4";
            int num = st.executeUpdate(sql);
            if (num > 0){
                System.out.println("删除成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

查询

package com.fzy.lesson02.utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestSelect {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();
            String sql = "select * from users where id = 1";
            rs = st.executeQuery(sql);
            if (rs.next()){
                System.out.println("id="+rs.getInt("id"));
                System.out.println("name="+rs.getString("NAME"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

sql 注入的问题

SQL存在漏洞，会被攻击导致数据泄露，or会被拼接

这里输入的用户名和密码并不对，但却登录成功

package com.fzy.lesson02.utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Login {
    public static void main(String[] args) {
       login("' or '1==1","' or '1==1");
    }
    public static void login(String name,String password){
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();
            String sql = "select * from users where NAME = '" +name+ "' and PASSWORD = '" +password+ "'";
            rs = st.executeQuery(sql);
            while (rs.next()){
                System.out.println("id="+rs.getInt("id"));
                System.out.println("name="+rs.getString("NAME"));
                System.out.println("password="+rs.getString("PASSWORD"));
                System.out.println("birth="+rs.getString("birthday"));
                System.out.println("========================");

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

用PreparedStatement可以防止SQL注入问题，效率更高

把传递进来的参数当做字符

假设其中存在转义字符，比如说 ’ 会被直接转义

查询

package com.fzy.lesson03;

import com.fzy.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestSelect {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try{
            conn = JdbcUtils.getConnection();
            //区别
            //使用？占位符代替参数
            String sql = "select * from users where id = ?";

            st = conn.prepareStatement(sql); //预编译SQL，先写sql，然后不执行

            //手动给参数赋值
            st.setInt(1,1);

            //执行SQL
            rs = st.executeQuery();
            if (rs.next()){
                System.out.println("name=" + rs.getString("NAME"));
                System.out.println("password=" + rs.getString("PASSWORD"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

添加

package com.fzy.lesson03;

import com.fzy.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;

public class TestInsert {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        try{
            conn = JdbcUtils.getConnection();
            //区别
            //使用？占位符代替参数
            String sql = "insert into users(id,NAME,PASSWORD,email,birthday) values(?,?,?,?,?)";

            st = conn.prepareStatement(sql); //预编译SQL，先写sql，然后不执行

            //手动给参数赋值
            st.setInt(1,5);
            st.setString(2,"ranqi");
            st.setString(3,"zxcvbn");
            st.setString(4,"jfgnjfn@jdf.com");
            //注意点：sql.Data  数据库的   java.sql.Date() 转换成数据库的时间类型
            //       util.Data  Java的   new Date().getTime() 获得时间戳
            st.setDate(5,new java.sql.Date(new Date().getTime()));
            //执行SQL
            int num = st.executeUpdate();
            if (num>0){
                System.out.println("添加成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

更新

package com.fzy.lesson03;

import com.fzy.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;

public class TestUpdate {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        try{
            conn = JdbcUtils.getConnection();
            //区别
            //使用？占位符代替参数
            String sql = "update users set NAME = ?,PASSWORD = ? where id = ?";

            st = conn.prepareStatement(sql); //预编译SQL，先写sql，然后不执行

            //手动给参数赋值

            st.setString(1,"tom");
            st.setString(2,"123dfg");
            st.setInt(3,5);


            //执行SQL
            int num = st.executeUpdate();
            if (num>0){
                System.out.println("更新成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

删除

package com.fzy.lesson03;

import com.fzy.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TestDelete {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        try{
            conn = JdbcUtils.getConnection();
            //区别
            //使用？占位符代替参数
            String sql = "delete from users where id = ?";

            st = conn.prepareStatement(sql); //预编译SQL，先写sql，然后不执行

            //手动给参数赋值
            st.setInt(1,5);

            //执行SQL
            int num = st.executeUpdate();
            if (num>0){
                System.out.println("删除成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

新的模拟登录

不会发生SQL注入问题，因为传入的参数会忽略掉 ’ 字符

package com.fzy.lesson03;

import com.fzy.lesson02.utils.JdbcUtils;

import java.sql.*;

public class Login {
    public static void main(String[] args) {
        login("'1' or 1==1","'1' or 1==1");
    }
    public static void login(String name,String password){
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();

            String sql = "select * from users where NAME = ? and PASSWORD = ?";
            st = conn.prepareStatement(sql);
            st.setString(1,name);
            st.setString(2,password);

            rs = st.executeQuery();

            if (rs.next()){
                System.out.println("id="+rs.getInt("id"));
                System.out.println("name="+rs.getString("NAME"));
                System.out.println("password="+rs.getString("PASSWORD"));
                System.out.println("birth="+rs.getString("birthday"));
                System.out.println("========================");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

java演示事务

数据表

CREATE TABLE account(
  id Int PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(40),
  money FLOAT
);

insert into account(name,money) values('A',1000);
insert into account(name,money) values('B',1000);
insert into account(name,money) values('C',1000);

java代码

package com.fzy.lesson04;

import com.fzy.lesson02.utils.JdbcUtils;

import java.sql.*;

public class TestTransaction1 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;

        try {
            conn = JdbcUtils.getConnection();
            //关闭数据库的自动提交，自动会开启事务
            conn.setAutoCommit(false);

            String sql1 = "update account set money = money - 100 where NAME = 'A'";
            st = conn.prepareStatement(sql1);
            st.executeUpdate();

            String sql2 = "update account set money = money + 100 where NAME = 'B'";
            st = conn.prepareStatement(sql2);
            st.executeUpdate();

            conn.commit();
            System.out.println("更新成功");
        } catch (SQLException e) {
            //如果失败，则默认回滚，所以不写这句代码也会回滚的
//            try {
//                conn.rollback();
//            } catch (SQLException ex) {
//                ex.printStackTrace();
//            }
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }

}

FZY_

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
JDBC程序，注入问题，事务

数据表CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;USE jdbcStudy;CREATE TABLE users( id INT PRIMARY KEY, NAME VARCHAR(40), PASSWORD VARCHAR(40), email VARCHAR(60), birthday DATE);INSERT INTO users(id,NAME,PASSWORD,email,bi
复制链接

扫一扫