目录
1.??????使用PreparedStatement
（1）好处：避免SQL注入（SQL注入是一种注入攻击，可以执行恶意SQL语句。）
（2）例子
①数据库表
drop table if exists user;
create table user (
name varchar(10),
password varchar(15)
);
insert into user(name, password) values
('tom1', '123456'),
('tom2', '123456'),
('tom3', '123456'),
('tom4', '123456'),
('tom5', '123456');②Java代码?
package com.sqlinsert.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* SQL注入是一种注入攻击，可以执行恶意SQL语句。
*/
public class SqlInsert {
/**
* @param name
* @param password
* TODO 判断用户是否存在
* @return 布尔值
*/
public static boolean login(String name, String password) {//使用处理对象Statement
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
boolean flag = false;//默认用户不存在
String sql = "select * from user where name = " + name + " and password = '" + password + "';";
try {
//注册驱动
Class.forName("com.mysql.jdbc.Driver");
//获取数据库连接
conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai",
"root", "123456");
//获取执行对象
stmt = conn.createStatement();
//执行sql语句
rs = stmt.executeQuery(sql);
if (rs.next()) {
flag = true;//该用户存在
}
} catch (Exception e) {
e.printStackTrace();
} finally {
//释放资源
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return flag;
}
/**
* @param name
* @param password
* TODO 判断该用户是否存在
* @return 布尔值
*/
public static boolean loginStronger(String name, String password) {//使用预处理对象PreparedStatement
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
boolean flag = false;//默认用户不存在
//使用占位符? 避免SQL注入
String sql = "select * from user where name = ? and password = ?;";
try {
//注册驱动
Class.forName("com.mysql.jdbc.Driver");
//获取数据库连接
conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai",
"root", "123456");
//获取执行对象
stmt = conn.prepareStatement(sql);
//为两个占位符赋值
stmt.setString(1, name);
stmt.setString(2, password);
//执行sql语句
rs = stmt.executeQuery();
if (rs.next()) {
flag = true;//该用户存在
}
} catch (Exception e) {
e.printStackTrace();
} finally {
//释放资源
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return flag;
}
public static void main(String[] args) {
/*此时sql语句为：select * from user where name = null and password = 'null' or 1 = '1';
* 无论账号和密码是否正确，都会提示的”该用户存在！“
*/
boolean flag = login("null", "null' or 1 = '1");
if (flag) {
System.out.println("该用户存在！");
} else {
System.out.println("该用户不存在！");
}
/* 此时，可以正常判断用户是否存在了 */
boolean flag2 = loginStronger("null", "null' or 1 = '1");
if (flag2) {
System.out.println("该用户存在！");
} else {
System.out.println("该用户不存在！");
}
}
}③执行结果
该用户存在！
该用户不存在！
2.封装JDBC
①封装JDBC工具类
Ⅰ.在src下创建文件“jdbc_dev.properties”，内容如下
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai
user=root
password=123456?Ⅱ.创建包“com.XXX.util”，并包里面创建DBUtils类，代码如下
package com.lqh.util;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;
/**
* @author lqh JDBC工具类
*/
public class DBUtils {
private static String driver = null;
private static String url = null;
private static String user = null;
private static String password = null;
//静态代码块
static{
//使用类加载器加载属性文件
//把文件一定放在src路径下
InputStream is = DBUtils.class.getClassLoader().getResourceAsStream("jdbc_dev.properties");
Properties props = new Properties();
try {
props.load(is);
driver = props.getProperty("driver");
url = props.getProperty("url");
user = props.getProperty("user");
password = props.getProperty("password");
} catch (IOException e) {
e.printStackTrace();
}
}
/** 获取数据库连接对象
* @return 连接对象
*/
public static Connection getConnection(){
Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, user, password);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
/** 释放资源
* @param conn
* @param stmt
* @param rs
*/
public static void close(Connection conn, Statement stmt, ResultSet rs){
try {
if(rs != null){
rs.close();
}
if(stmt != null){
stmt.close();
}
if(conn != null){
conn.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}?②根据数据库中的表创建实体类
Ⅰ.数据库中有表：
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
`id` int NOT NULL,
`name` varchar(15) NOT NULL,
`hiredate` date NOT NULL,
`sal` decimal(10, 2) NOT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO `emp` VALUES (1, 'jesson', '1988-12-02', 4521.50);
INSERT INTO `emp` VALUES (2, 'amy', '2012-12-12', 3589.50);
INSERT INTO `emp` VALUES (3, 'mary', '2022-02-10', 2545.20);
INSERT INTO `emp` VALUES (4, 'ken', '2020-02-10', 3526.20);
INSERT INTO `emp` VALUES (5, 'henry', '2012-02-10', 4553.20);
INSERT INTO `emp` VALUES (6, 'chen', '2015-05-13', 6541.50);
INSERT INTO `emp` VALUES (7, 'li', '1998-01-09', 6545.50);
INSERT INTO `emp` VALUES (8, 'ha', '2022-01-01', 3500.50);Ⅱ.创建包“com.XXX.entity”，创建Emp类
package com.lqh.entity;
import java.util.Date;
/** 雇员，实体类
* @author lqh
*/
public class Emp {
/** 雇员ID */
private Integer id;
/** 雇员姓名 */
private String name;
/** 雇员入职日期 */
private Date hiredate;
/** 雇员薪水 */
private Double sal;
//提供属性访问器getter和setter
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Date getHiredate() {
return hiredate;
}
public void setHiredate(Date hiredate) {
this.hiredate = hiredate;
}
public Double getSal() {
return sal;
}
public void setSal(Double sal) {
this.sal = sal;
}
/** 提供有参构造函数 */
public Emp(Integer id, String name, Date hiredate, Double sal) {
super();
this.id = id;
this.name = name;
this.hiredate = hiredate;
this.sal = sal;
}
/** 提供无参构造函数 */
public Emp(){
}
/** 重写toString方法，方便查看对象里面的所有信息 */
@Override
public String toString() {
return "Emp [id=" + id + ", name=" + name + ", hiredate=" + hiredate + ", sal=" + sal + "]";
}
}③创建“com.XXX.dao”包，创建Emp对应的dao层，即EmpDao类
package com.lqh.dao;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.lqh.entity.Emp;
import com.lqh.util.DBUtils;
/** 数据访问层
* @author lqh
*/
public class EmpDao {
//把sql语句从方法中剥离出来定义成常量
private static final String INSERT_EMP = "insert into emp(id, name, hiredate, sal) values(?, ?, ?, ?);";
private static final String DELETE_EMP = "delete from emp where id = ?;";
private static final String QUERY_ALLEMP = "select id, name, hiredate, sal from emp;";
/** 添加雇员
* @param emp
* @return 添加结果
*/
public boolean insertEmp(Emp emp){
boolean result = false;//用于存放添加的结果
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DBUtils.getConnection();//从工具类获取连接对象
pstmt = conn.prepareStatement(INSERT_EMP);
pstmt.setInt(1, emp.getId());
pstmt.setString(2, emp.getName());
pstmt.setDate(3, new Date(emp.getHiredate().getTime()));//sql包和util包的date之间的转换
pstmt.setDouble(4, emp.getSal());
int num = pstmt.executeUpdate();
result = (num > 0)?true: false;
} catch (Exception e) {
e.printStackTrace();
} finally{
DBUtils.close(conn, pstmt, null);//释放资源
}
return result;
}
/** 通过雇员id删除雇员信息
* @param id
* @return 删除结果
*/
public boolean deleteEmpById(int id){
boolean result = false;//用于存放删除的结果
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DBUtils.getConnection();//从工具类获取连接对象
pstmt = conn.prepareStatement(DELETE_EMP);
pstmt.setInt(1, id);
int num = pstmt.executeUpdate();
result = (num > 0)?true: false;
} catch (Exception e) {
e.printStackTrace();
} finally{
DBUtils.close(conn, pstmt, null);//释放资源
}
return result;
}
/** 通过雇员id修改雇员信息
* @param emp
* @return 修改结果
*/
public boolean updateEmpById(Emp emp){
boolean result = false;//用于存放修改的结果
Connection conn = null;
PreparedStatement pstmt = null;
StringBuffer sql = new StringBuffer("update emp set");//存放sql语句
try {
conn = DBUtils.getConnection();//从工具类获取连接对象
if(emp.getName() != null && !"".equals(emp.getName())){//雇员姓名不为空
sql.append(" name = '" + emp.getName() + "',");//拼接sql语句
}
if(emp.getHiredate() != null && !"".equals(emp.getHiredate())){//入职日期不为空
sql.append(" hiredate = '" + emp.getHiredate() + "',");//拼接sql语句
}
if(emp.getSal() != null && !"".equals(emp.getSal())){//薪水不为空
sql.append(" sal = " + emp.getSal() + ",");
}
sql.delete(sql.length() - 1, sql.length());//移除sql语句中的“,”
sql.append(" where id = " + emp.getId());//给sql添加限制条件
pstmt = conn.prepareStatement(sql.toString());
int num = pstmt.executeUpdate();
result = (num > 0)?true: false;
} catch (Exception e) {
e.printStackTrace();
} finally{
DBUtils.close(conn, pstmt, null);//释放资源
}
return result;
}
/** 查询所有雇员信息
* @return 所有雇员信息
*/
public List<Emp> queryAllEmp(){
List<Emp> emps = new ArrayList<>();
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
conn = DBUtils.getConnection();
try {
pstmt = conn.prepareStatement(QUERY_ALLEMP);
rs = pstmt.executeQuery();
while(rs.next()){
Emp emp = new Emp();
emp.setId(rs.getInt("id"));//这里，rs.getInt("id")等价于：rs.getInt(1)，但不建议写后者
emp.setName(rs.getString("name"));
emp.setHiredate(rs.getDate("hiredate"));
emp.setSal(rs.getDouble("sal"));
emps.add(emp);//往集合添加查询到的对象
}
} catch (Exception e) {
e.printStackTrace();
} finally{
DBUtils.close(conn, pstmt, rs);
}
return emps;
}
}3.JDBC分页查询
package com.lqh.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.lqh.entity.Emp;
import com.lqh.util.DBUtils;
/** 数据访问层
* @author lqh
*/
public class EmpDao {
private static final String QUERY_EMP_BY_PAGEANDNUMBER = "select id, name, hiredate, sal from emp limit ?, ?;";
/** 雇员信息每number个为一页，显示第page页
* @param page
* @param number
* @return 第page页雇员信息
*/
public List<Emp> queryEmpByPageAndNumber(int page, int number){
List<Emp> emps = new ArrayList<>();
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = DBUtils.getConnection();
pstmt = conn.prepareStatement(QUERY_EMP_BY_PAGEANDNUMBER);
pstmt.setInt(1, (page - 1) * number);//从下标为：(page - 1) * number开始查询总数据数为number
pstmt.setInt(2, number);
rs = pstmt.executeQuery();
while(rs.next()){
Emp emp = new Emp();
emp.setId(rs.getInt("id"));
emp.setName(rs.getString("name"));
emp.setHiredate(rs.getDate("hiredate"));
emp.setSal(rs.getDouble("sal"));
emps.add(emp);
}
} catch (Exception e) {
e.printStackTrace();
} finally{
DBUtils.close(conn, pstmt, rs);
}
return emps;
}
}4.事务的控制
package com.lqh.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import com.lqh.util.DBUtils;
/** 数据访问层
* @author lqh
*/
public class EmpDao {
private static final String UPDATE_SAL = "update emp set sal = sal + ? where id = ?;";
/** 两个雇员之间的转账（from：转账人id，to：收钱方id，sal：转账金额）
* @param from
* @param to
* @param sal
* @return 转账结果
*/
public boolean updateSal(int from, int to, double sal){
boolean isFlag = false;
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DBUtils.getConnection();
/** 设置数据库为手动提交 */
conn.setAutoCommit(false);
pstmt = conn.prepareStatement(UPDATE_SAL);
//转账人：
pstmt.setDouble(1, -sal);
pstmt.setInt(2, from);
int num1 = pstmt.executeUpdate();
//这时遇到算数异常，第一条sql语句能成功执行，第二条不行
//所以需要在处理异常catch里面，回滚事务
//System.out.println(1 / 0);
//收钱方：
pstmt.setDouble(1, sal);
pstmt.setInt(2, to);
int num2 = pstmt.executeUpdate();
if(num1 > 0 && num2 > 0){//只有送钱和收钱两个行为完成了才提交
conn.commit();//提交事务
}
} catch (Exception e) {
try {
//回滚事务，防止因为出现异常，只执行了一条sql语句，另一个未执行
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
} finally{
DBUtils.close(conn, pstmt, null);
}
return isFlag;
}
}5.工厂设计模式(解耦合)
（1）创建EmpDaoIfac接口
点击“Refactor”，选中"Extract Interface"，输入接口名“EmpDaoIfac”,勾选所有EmpDao里创建的方法
?（2） 创建工厂类DaoFactory
package com.lqh.dao;
/** dao工厂
* @author lqh
*/
public class DaoFactory {
public static EmpDao getEmpDao(){
return new EmpDao();
}
}（3）创建包“com.XXX.service”，创建业务层EmpService类
package com.lqh.service;
import java.util.List;
import com.lqh.dao.DaoFactory;
import com.lqh.dao.EmpDaoIfac;
import com.lqh.entity.Emp;
/** 业务层，调用dao层方法
* @author lqh
*/
public class EmpService {
private EmpDaoIfac empDao = DaoFactory.getEmpDao();//从dao工厂里面获取EmpDao实例化对象，可以解耦合
/** 雇佣新员工
* @param emp
* @return 雇佣结果
*/
public boolean hire(Emp emp){
return empDao.insertEmp(emp);
}
/** 裁员
* @param id
* @return 裁员结果
*/
public boolean fire(int id){
return empDao.deleteEmpById(id);
}
/** 修改雇员信息
* @param emp
* @return 修改结果
*/
public boolean changeEmpInfo(Emp emp){
return empDao.updateEmpById(emp);
}
/** 显示所有员工信息
* @return 所有员工信息
*/
public List<Emp> showAllEmp(){
return empDao.queryAllEmp();
}
/** 转账
* @param from
* @param to
* @param sal
* @return 转账结果
*/
public boolean transfer(int from, int to, double sal){
return empDao.updateSal(from, to, sal);
}
}（4）创建EmpServiceIfac接口
（5）创建工厂类ServiceFactory
package com.lqh.service;
/** service工厂
* @author lqh
*/
public class ServiceFactory {
public static EmpService getEmpService(){
return new EmpService();
}
}?