JAVA——JDBC进阶

目录

1.​​​​​​使用PreparedStatement

2.封装JDBC

3.JDBC分页查询

4.事务的控制

5.工厂设计模式(解耦合)

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;
 

public class SqlInsert {
	
	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;
	}
	
	
	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) {
		
		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;
 

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();
		}
	}
	
	
	public static Connection getConnection(){
		Connection conn = null;
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, user, password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	
	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;
 

public class Emp {
	
	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(){
	}
 
	
	@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;
 

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;";
 
	
	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;
	}
	
	
	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;
	}
	
	
	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;
	}
	
	
	public List queryAllEmp(){
		List 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;
 

public class EmpDao {
	private static final String QUERY_EMP_BY_PAGEANDNUMBER = "select id, name, hiredate, sal from emp limit ?, ?;";
 
	
	public List queryEmpByPageAndNumber(int page, int number){
		List 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;
 

public class EmpDao {
 
	private static final String UPDATe_SAL = "update emp set sal = sal + ? where id = ?;";
 
	
	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;
 

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;
 

public class EmpService {
	private EmpDaoIfac empDao = DaoFactory.getEmpDao();//从dao工厂里面获取EmpDao实例化对象，可以解耦合
	
	
	public boolean hire(Emp emp){
		return empDao.insertEmp(emp);
	}
	
	
	public boolean fire(int id){
		return empDao.deleteEmpById(id);
	}
	
	
	public boolean changeEmpInfo(Emp emp){
		return empDao.updateEmpById(emp);
	}
	
	
	public List showAllEmp(){
		return empDao.queryAllEmp();
	}
	
	
	public boolean transfer(int from, int to, double sal){
		return empDao.updateSal(from, to, sal);
	}
}

（4）创建EmpServiceIfac接口

（5）创建工厂类ServiceFactory

package com.lqh.service;
 

public class ServiceFactory {
	public static EmpService getEmpService(){
		return new EmpService();
	}
}