package PreparedStatement_sql注入;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
public class PreparedStatement_sql {
// 用?作为占位符号
/**
* 保存图片mysql中用longblob
* @throws Exception
*/
@Test
public void saveImg() throws Exception{
String sql = "insert into stud values(66,?,?)";
PreparedStatement pst = con.prepareStatement(sql);
//声明图片的信息
File file = new File("./img/a.jpg");
InputStream in = new FileInputStream(file);
//设置参数到pst中
pst.setString(1, "ss");
pst.setBinaryStream(2,in);
//执行
pst.executeUpdate();
}
/**
* 防止sql注入
*
* @throws Exception
*/
@Test
public void regWithPre() throws Exception {
Scanner sc = new Scanner(System.in);
System.err.println("输入id ,name");
String id = sc.nextLine();
String name = sc.nextLine();
String sql = "insert into stud values(?,?)";
// preparedstatement pst 接收sql
// 执行sql语句再设置参数
PreparedStatement pst = con.prepareStatement(sql);
// 编译好后设置参数
// 设置值要从1开始
pst.setString(1, id);
pst.setString(2, name);
pst.executeUpdate();
}
/**
* 判断数据库里是否有值
*
* @throws Exception
*/
@Test
public void loginPst() throws Exception {
Scanner sc = new Scanner(System.in);
String nm = sc.nextLine();
String id = sc.nextLine();
String sql = "select * from stud where id=? and name=?";
PreparedStatement pst = con.prepareStatement(sql);
pst.setString(1, id);
pst.setString(2, nm);
System.err.println(sql);
ResultSet rs = pst.executeQuery();// 判断是否有值
if (rs.next()) {
System.err.println("你登录成功,你好欢迎你..");
} else {
System.err.println("你登录不成功。。。");
}
}
@Before
// 执行Test前执行
public void getCon() throws Exception {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/abc?useUnicode=true&characterEncoding=utf8";
con = DriverManager.getConnection(url, "root", "1234");
// con.close();
// System.err.println(con);
}
@After
// 执行Test后执行
public void closeConn() throws Exception {
if (con != null || !con.isClosed()) {
con.close();
}
}
private Connection con;
}