尚硅谷之JDBC

package com.atguigu.statement;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.Statement;

import java.util.Scanner;

import org.junit.Test;

import com.atguigu.utils.JDBCUtils;

/*

 * Statement：

 * 1、SQL拼接

 * 2、SQL注入

 * 3、处理不了Blob类型的数据

 */

public class TestStatementProblem {

@Test

public void add() throws Exception{

Scanner input = new Scanner(System.in);

System.out.println(“请输入姓名：”);

String name = input.nextLine();

System.out.println(“请输入领导编号：”);

int mid = input.nextInt();

System.out.println(“请输入部门编号：”);

int did = input.nextInt();

//1、获取连接

Connection conn = JDBCUtils.getConnection();

//2、创建Statement对象

Statement st = conn.createStatement();

//3、编写sql

String sql = “INSERT INTO emp (ename,`mid`,did) VALUES(‘” + name+”‘,” + mid + “,” + did + “)”;

//4、执行sql

int update = st.executeUpdate(sql);

System.out.println(update>0?”添加成功”:”添加失败”);

//5、释放资源

JDBCUtils.closeQuietly(st, conn);

}

@Test

public void select()throws Exception{

Scanner input = new Scanner(System.in);

System.out.println(“请输入姓名：”);

String name = input.nextLine();

//1、获取连接

Connection conn = JDBCUtils.getConnection();

//2、写sql

//孙红雷  ‘ or ‘1’ = ‘1

String sql = “SELECT eid,ename,tel,gender,salary FROM t_employee WHERE ename = ‘” + name + “‘”;

System.out.println(sql);

// SELECT eid,ename,tel,gender,salary FROM t_employee WHERE ename = ‘孙红雷  ‘ or ‘1’ = ‘1’

//3、用Statement执行

Statement st = conn.createStatement();

//4、执行查询sql

ResultSet rs = st.executeQuery(sql);

while(rs.next()){

int id = rs.getInt(1);

String ename = rs.getString(2);

String tel = rs.getString(3);

String gender =rs.getString(4);

double salary = rs.getDouble(5);

System.out.println(id+”\t” + ename + “\t” + tel + “\t” + gender + “\t” +salary);

}

//5、释放资源

JDBCUtils.closeQuietly(rs, st, conn);

}

    @Test

public void testAddBlob(){

String sql = “INSERT INTO `user` (username,`password`,photo)VALUES(‘chai’,’123′,没法在String中处理Blob类型的数据);”;

}

}

package com.atguigu.preparedstatement;

import java.io.FileInputStream;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.util.Scanner;

import org.junit.Test;

import com.atguigu.utils.JDBCUtils;

/*

 * PreparedStatement：是Statement子接口

 * 1、SQL不需要拼接

 * 2、SQL不会出现注入

 * 3、可以处理Blob类型的数据

 * tinyblob：255字节以内

 * blob：65K以内

 * mediumblob:16M以内

 * longblob：4G以内

 *

 * 如果还是报错：xxx too large，那么在mysql的安装目录下，找my.ini文件加上如下的配置参数：

 * max_allowed_packet=16M

 * 注意：修改了my.ini文件，一定要重新启动服务

 *

 */

public class TestPreparedStatement {

@Test

public void add() throws Exception {

Scanner input = new Scanner(System.in);

System.out.println(“请输入姓名：”);

String name = input.nextLine();

System.out.println(“请输入性别：”);

String gender = input.nextLine();

System.out.println(“请输入领导编号：”);

int mid = input.nextInt();

System.out.println(“请输入部门编号：”);

int did = input.nextInt();

String sql = “INSERT INTO emp VALUES(NULL,?,?,?,?)”;// 参数，占位符，通配符，表示这个地方需要设置值

// 2、获取连接

Connection conn = JDBCUtils.getConnection();

// 3、准备一个PreparedStatement：预编译sql

PreparedStatement pst = conn.prepareStatement(sql);// 对带？的sql进行预编译

// 4、把?用具体的值进行代替

pst.setString(1, name);

pst.setString(2, gender);

pst.setInt(3, mid);

pst.setInt(4, did);

// 5、执行sql

int len = pst.executeUpdate();

// 6、释放资源

JDBCUtils.closeQuietly(pst, conn);

}

@Test

public void select() throws Exception {

// 3、写sql

Scanner input = new Scanner(System.in);

System.out.println(“请输入姓名：”);

String name = input.nextLine();

// 孙红雷 ‘ or ‘1’ = ‘1

String sql = “SELECT eid,ename,tel,gender,salary FROM t_employee WHERE ename = ?”;

// 1、注册驱动，注册过了

// 2、获取连接

Connection conn = JDBCUtils.getConnection();

// 3、把带？的sql语句进行预编译

PreparedStatement pst = conn.prepareStatement(sql);

// 4、把？用具体的变量的赋值

pst.setString(1, name);

// 5、执行sql

ResultSet rs = pst.executeQuery();

while (rs.next()) {

int id = rs.getInt(“eid”);

String ename = rs.getString(“ename”);

String tel = rs.getString(“tel”);

String gender = rs.getString(“gender”);

double salary = rs.getDouble(“salary”);

System.out.println(id + “\t” + ename + “\t” + tel + “\t” + gender + “\t” + salary);

}

// 6、释放资源

JDBCUtils.closeQuietly(rs, pst, conn);

}

@Test

public void addBlob() throws Exception {

Scanner input = new Scanner(System.in);

System.out.println(“请输入用户名：”);

String username = input.nextLine();

System.out.println(“请输入密码：”);

String password = input.nextLine();

System.out.println(“请指定照片的路径：”);

String photoPath = input.nextLine();

// INSERT INTO `user` VALUES(NULL,用户名,密码,照片)

String sql = “INSERT INTO `user` VALUES(NULL,?,?,?)”;

// 1、注册驱动，注册过了

// 2、获取连接

Connection conn = JDBCUtils.getConnection();

// 3、准备一个PreparedStatement：预编译sql

PreparedStatement pst = conn.prepareStatement(sql);// 对带？的sql进行预编译

// 4、对？进行设置

pst.setString(1, username);

pst.setString(2, password);

pst.setBlob(3, new FileInputStream(photoPath));

// 5、执行sql

int len = pst.executeUpdate();

System.out.println(len > 0 ? “添加成功” : “添加失败”);

// 6、释放资源

JDBCUtils.closeQuietly(pst, conn);

}

}