JDBC基础知识(3)——预编译SQL命令格式

原始登录应用程序的实现

package com.bjpowernode.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner;

public class 原始登录应用程序 {
    public static void main(String[] args) throws Exception{
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入用户名");
        String userName = sc.nextLine();
        System.out.println("请输入密码");
        String password = sc.nextLine();
        //字符串拼接方式(禁止)
        String sql = "select count(*) from emp where ename ='"+userName+"'"+"and empno ="+password;

        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/bjpowernode","root","123456");
        PreparedStatement car = con.prepareStatement(sql);
        ResultSet table = car.executeQuery();
        table.next();
        int flag = table.getInt("count(*)");
        if (flag == 0) {
            System.out.println("登录信息不存在，请重新登录");
        }else {
            System.out.println("欢迎光临");
        }
        if(table != null) {
            table.close();
        }
        if(car != null) {
            car.close();
        }
        if(con != null) {
            con.close();
        }
    }
}

采用字符串拼接得到SQL命令的方式是被禁止的！

通过预编译SQL命令实现登录应用程序

预编译SQL命令格式：

1.书写SQL命令时，使用？代替赋值数据 “？”——占位符
例子：

insert into dept values(?,?,?)
delete from dept where deptno=?
select count(*) from emp where ename=? and empno=?

2.在推送SQL命令之前，由PrepareStatement对象负责对占位符进行赋值
好处：
①降低SQL命令拼写难度
②有效防止SQL注入攻击

package com.bjpowernode.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner;

public class SQL预编译命令格式 {
    public static void main(String[] args) throws Exception{
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入用户名");
        String userName = sc.nextLine();
        System.out.println("请输入密码");
        String password = sc.nextLine();
        //预编译SQL命令
        String sql = "select count(*) from emp where ename = ? and empno = ?";
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/bjpowernode","root","123456");
        PreparedStatement car = con.prepareStatement(sql);
        //对占位符进行赋值
        car.setString(1,userName);
        car.setString(2,password);
        ResultSet table = car.executeQuery();
        table.next();
        int flag = table.getInt("count(*)");
        if (flag == 0) {
            System.out.println("登录信息不存在，请重新登录");
        }else {
            System.out.println("欢迎光临");
        }
        if(table != null) {
            table.close();
        }
        if(car != null) {
            car.close();
        }
        if(con != null) {
            con.close();
        }
    }
}