1 更新和插入Dept数据

本案例要求使用JDBC向Dept表中插入和更新数据，详细要求如下：

1.向Dept表中插入一条记录。其中为列deptno、dname、loc插入的数据分别为50、"developer"、 "Beijing"。

2.更新部门ID为50的部门所在地为“ShangHai”。

参考答案

实现此案例需要按照如下步骤进行。

步骤一：创建Dept类

创建Dept类，该类为数据库表Dept与实体类之间的映射，代码如下所示：

public class Dept {

	private int deptno;
	private String dname;
	private String loc;
	
	public Dept() {
		super();
	}
	public Dept(int deptno, String dname, String loc) {
		super();
		this.deptno = deptno;
		this.dname = dname;
		this.loc = loc;
	}
	public int getDeptno() {
		return deptno;
	}
	public void setDeptno(int deptno) {
		this.deptno = deptno;
	}
	public String getDname() {
		return dname;
	}
	public void setDname(String dname) {
		this.dname = dname;
	}
	public String getLoc() {
		return loc;
	}
	public void setLoc(String loc) {
		this.loc = loc;
	}
}

步骤二：在DeptDAO类中添加add方法

在DeptDAO类中添加add方法，用于实现向数据库的Dept表中添加数据，代码如下所示：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

publicclass DeptDAO {
	publicstaticvoid main(String[] args) {
		// 1.select
		DeptDAO dao = new DeptDAO();
		dao.findAll();
	}
	publicvoid findAll() {
		... ...
	}

#cold_bold	publicvoid add(Dept dept) {
#cold_bold		
#cold_bold	}
}

步骤三：拼写insert语句

在add方法中定义insert语句，代码如下所示：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

publicclass DeptDAO {
	publicstaticvoid main(String[] args) {
		// 1.select
		DeptDAO dao = new DeptDAO();
		dao.findAll();
	}
	publicvoid findAll() {
		... ...
	}

	publicvoid add(Dept dept) {
#cold_bold		String sql = "insert into dept(deptno,dname,loc) " +
#cold_bold				"values("+dept.getDeptno()+",'"+dept.getDname()+"','"+dept.getLoc()+"')";

	}
}

步骤四：执行插入语句

首先创建数据库连接；然后通过连接创建Statement对象；最后使用Statement对象的updateExecute方法，执行插入语句并处理异常，代码如下所示：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

publicclass DeptDAO {
	publicstaticvoid main(String[] args) {
		// 1.select
		DeptDAO dao = new DeptDAO();
		dao.findAll();
	}
	publicvoid findAll() {
		... ...
	}

	publicvoid add(Dept dept) {
#cold_bold		Connection con = null;
#cold_bold		Statement stmt = null;
#cold_bold		int flag = -1;
		String sql = "insert into dept(deptno,dname,loc) " +
				"values("+dept.getDeptno()+",'"+dept.getDname()+"','"+dept.getLoc()+"')";
		System.out.println(sql);
#cold_bold		try {
#cold_bold			con = ConnectionSource.getConnection();
#cold_bold			stmt = con.createStatement();
#cold_bold
#cold_bold			flag = stmt.executeUpdate(sql);
#cold_bold			if (flag > 0) {
#cold_bold				System.out.println("新增记录成功!");
#cold_bold			}
#cold_bold		} catch (SQLException e) {
#cold_bold			System.out.println("数据库访问异常!");
#cold_bold			throw new RuntimeException(e);
#cold_bold		} finally {
#cold_bold			try {
#cold_bold				if (stmt != null) {
#cold_bold					stmt.close();
#cold_bold				}
#cold_bold				if (con != null) {
#cold_bold					con.close();
#cold_bold				}
#cold_bold			} catch (SQLException e) {
#cold_bold				System.out.println("释放资源发生异常");
#cold_bold			}
#cold_bold		}
	}
}

步骤五：测试插入数据是否成功

在DeptDAO类的main方法中，调用add方法，代码如下所示：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

publicclass DeptDAO {
	publicstaticvoid main(String[] args) {
		// 1.select
		DeptDAO dao = new DeptDAO();
		//dao.findAll();
// 2.insert
#cold_bold		Dept dept = new Dept(50,"developer","Beijing");
#cold_bold		dao.add(dept);
	}
	publicvoid findAll() {
		... ...
	}

	publicvoid add(Dept dept) {
		Connection con = null;
		Statement stmt = null;
		int flag = -1;
		String sql = "insert into dept(deptno,dname,loc) " +
				"values("+dept.getDeptno()+",'"+dept.getDname()+"','"+dept.getLoc()+"')";
		System.out.println(sql);
		try {
			con = ConnectionSource.getConnection();
			stmt = con.createStatement();

			flag = stmt.executeUpdate(sql);
			if (flag > 0) {
				System.out.println("新增记录成功!");
			}
		} catch (SQLException e) {
			System.out.println("数据库访问异常!");
			thrownew RuntimeException(e);
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				System.out.println("释放资源发生异常");
			}
		}
	}
}

运行上述代码，数据库Dept表中会增加一条deptno为50的部门记录。

步骤六：对Emp表中的数据执行更新

在DeptDAO类中，添加update方法，该方法用于更新部门ID为50的部门所在地为“ShangHai”，代码如下所示：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

publicclass DeptDAO {
	publicstaticvoid main(String[] args) {
		// 1.select
		DeptDAO dao = new DeptDAO();
		dao.findAll();

		// 2.insert
		Dept dept = new Dept(50,"developer","Beijing");
		// dao.add(dept);

		// 3.update
		dept.setLoc("ShangHai");
		dao.update(dept);

	}
	publicvoid findAll() {
		... ...
	}

	publicvoid add(Dept dept) {
		......
	}

#cold_bold	publicvoid update(Dept dept) {
#cold_bold		Connection con = null;
#cold_bold		Statement stmt = null;
#cold_bold		int flag = -1;
#cold_bold		String sql = "update dept set dname = '" + dept.getDname() + "'," + "loc = '"
#cold_bold				+ dept.getLoc() + "' where deptno = " + dept.getDeptno();
#cold_bold		try {
#cold_bold			con = ConnectionSource.getConnection();
#cold_bold			stmt = con.createStatement();
#cold_bold
#cold_bold			flag = stmt.executeUpdate(sql);
#cold_bold			if (flag > 0) {
#cold_bold				System.out.println("更新记录成功!");
#cold_bold			}
#cold_bold		} catch (SQLException e) {
#cold_bold			System.out.println("数据库访问异常!");
#cold_bold			thrownew RuntimeException(e);
#cold_bold		} finally {
#cold_bold			try {
#cold_bold				if (stmt != null) {
#cold_bold					stmt.close();
#cold_bold				}
#cold_bold				if (con != null) {
#cold_bold					con.close();
#cold_bold				}
#cold_bold			} catch (SQLException e) {
#cold_bold				System.out.println("释放资源发生异常");
#cold_bold			}
#cold_bold		}
#cold_bold	}
#cold_bold}

步骤七：测试update方法更新数据是否成功

在DeptDAO的main方法中添加代码，调用update方法，代码如下所示：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

publicclass DeptDAO {
	publicstaticvoid main(String[] args) {
		// 1.select
		DeptDAO dao = new DeptDAO();
		//dao.findAll();

		// 2.insert
		//Dept dept = new Dept(50,"developer","Beijing");
		// dao.add(dept);

		// 3.update
#cold_bold		dept.setLoc("ShangHai");
#cold_bold		dao.update(dept);

	}
	publicvoid findAll() {
		... ...
	}

	publicvoid add(Dept dept) {
		... ...
	}

	publicvoid update(Dept dept) {
		Connection con = null;
		Statement stmt = null;
		int flag = -1;
		String sql = "update dept set dname = '" + dept.getDname() + "'," + "loc = '"
				+ dept.getLoc() + "' where deptno = " + dept.getDeptno();
		try {
			con = ConnectionSource.getConnection();
			stmt = con.createStatement();

			flag = stmt.executeUpdate(sql);
			if (flag > 0) {
				System.out.println("更新记录成功!");
			}
		} catch (SQLException e) {
			System.out.println("数据库访问异常!");
			thrownew RuntimeException(e);
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				System.out.println("释放资源发生异常");
			}
		}
	}
}

运行上述代码，数据库Dept表部门ID为50的部门所在地改为“ShangHai“。

本案例中，Dept类的完整代码如下所示：

public class Dept {

	private int deptno;
	private String dname;
	private String loc;
	
	public Dept() {
		super();
	}
	public Dept(int deptno, String dname, String loc) {
		super();
		this.deptno = deptno;
		this.dname = dname;
		this.loc = loc;
	}
	public int getDeptno() {
		return deptno;
	}
	public void setDeptno(int deptno) {
		this.deptno = deptno;
	}
	public String getDname() {
		return dname;
	}
	public void setDname(String dname) {
		this.dname = dname;
	}
	public String getLoc() {
		return loc;
	}
	public void setLoc(String loc) {
		this.loc = loc;
	}
}

DeptDAO类的完整代码如下所示：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

publicclass DeptDAO {
	publicstaticvoid main(String[] args) {
		// 1.select
		DeptDAO dao = new DeptDAO();
		dao.findAll();

		// 2.insert
		Dept dept = new Dept(50,"developer","Beijing");
		// dao.add(dept);

		// 3.update
		dept.setLoc("ShangHai");
		dao.update(dept);

	}
	publicvoid findAll() {
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;

		try {
			Class.forName("oracle.jdbc.OracleDriver");
			con = DriverManager.getConnection(
					"jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "tiger");
			stmt = con.createStatement();
			rs = stmt
					.executeQuery("select deptno,dname,loc from dept");
			while (rs.next()) {
				System.out.println(rs.getInt("deptno") + ","
						+ rs.getString("dname") + ","
						+ rs.getString("loc"));
			}
		} catch (ClassNotFoundException e) {
			System.out.println("驱动类无法找到!");
			thrownew RuntimeException(e);
		} catch (SQLException e) {
			System.out.println("数据库访问异常!");
			thrownew RuntimeException(e);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				System.out.println("关闭连接时发生异常");
			}
		}
	}

	publicvoid add(Dept dept) {
		Connection con = null;
		Statement stmt = null;
		int flag = -1;
		String sql = "insert into dept(deptno,dname,loc) " +
				"values("+dept.getDeptno()+",'"+dept.getDname()+"','"+dept.getLoc()+"')";
		System.out.println(sql);
		try {
			con = ConnectionSource.getConnection();
			stmt = con.createStatement();

			flag = stmt.executeUpdate(sql);
			if (flag > 0) {
				System.out.println("新增记录成功!");
			}
		} catch (SQLException e) {
			System.out.println("数据库访问异常!");
			thrownew RuntimeException(e);
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				System.out.println("释放资源发生异常");
			}
		}
	}

	publicvoid update(Dept dept) {
		Connection con = null;
		Statement stmt = null;
		int flag = -1;
		String sql = "update dept set dname = '" + dept.getDname() + "'," + "loc = '"
				+ dept.getLoc() + "' where deptno = " + dept.getDeptno();
		try {
			con = ConnectionSource.getConnection();
			stmt = con.createStatement();

			flag = stmt.executeUpdate(sql);
			if (flag > 0) {
				System.out.println("更新记录成功!");
			}
		} catch (SQLException e) {
			System.out.println("数据库访问异常!");
			thrownew RuntimeException(e);
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				System.out.println("释放资源发生异常");
			}
		}
	}
}

db.properties文件的内容与之前案例一样没有变化，该文件完整内容如下所示：

jdbc.driverClassName=oracle.jdbc.OracleDriver
jdbc.url=jdbc:oracle:thin:@127.0.0.1:1521:orcl
jdbc.username=scott
jdbc.password=tiger

#<!-- 初始化连接 -->
dataSource.initialSize=10
#<!-- 最大空闲连接 -->
dataSource.maxIdle=20
#<!-- 最小空闲连接 -->
dataSource.minIdle=5
#最大连接数量 
dataSource.maxActive=50
#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
dataSource.maxWait=1000

ConnectionSource类与之前案例一样没有变化，该类完整内容如下所示：

import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

import org.apache.commons.dbcp.BasicDataSource;

public class ConnectionSource {
	private static BasicDataSource dataSource = null;

	public ConnectionSource() {
	}
	public static void init() {

		Properties dbProps = new Properties();
		// 取配置文件可以根据实际的不同修改
		try {
			dbProps.load(ConnectionSource.class.getClassLoader().getResourceAsStream(
					"day01/v4/db.properties"));
		} catch (IOException e) {
			e.printStackTrace();
		}

		try {
			String driveClassName = dbProps.getProperty("jdbc.driverClassName");
			String url = dbProps.getProperty("jdbc.url");
			String username = dbProps.getProperty("jdbc.username");
			String password = dbProps.getProperty("jdbc.password");

			String initialSize = dbProps.getProperty("dataSource.initialSize");
			String minIdle = dbProps.getProperty("dataSource.minIdle");
			String maxIdle = dbProps.getProperty("dataSource.maxIdle");
			String maxWait = dbProps.getProperty("dataSource.maxWait");
			String maxActive = dbProps.getProperty("dataSource.maxActive");

			dataSource = new BasicDataSource();
			dataSource.setDriverClassName(driveClassName);
			dataSource.setUrl(url);
			dataSource.setUsername(username);
			dataSource.setPassword(password);

			// 初始化连接数
			if (initialSize != null)
				dataSource.setInitialSize(Integer.parseInt(initialSize));

			// 最小空闲连接
			if (minIdle != null)
				dataSource.setMinIdle(Integer.parseInt(minIdle));

			// 最大空闲连接
			if (maxIdle != null)
				dataSource.setMaxIdle(Integer.parseInt(maxIdle));

			// 超时回收时间(以毫秒为单位)
			if (maxWait != null)
				dataSource.setMaxWait(Long.parseLong(maxWait));

			// 最大连接数
			if (maxActive != null) {
				if (!maxActive.trim().equals("0"))
					dataSource.setMaxActive(Integer.parseInt(maxActive));
			}
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("创建连接池失败!请检查设置!!!");
		}
	}

	public static synchronized Connection getConnection() throws SQLException {
		if (dataSource == null) {
			init();
		}
		Connection conn = null;
		if (dataSource != null) {
			conn = dataSource.getConnection();
		}
		return conn;
	}
}

2 查询指定Emp的工资

查询指定姓名的员工的工资。详细要求如下：

1. 使用Statement实现查询指定姓名的员工的工资，并测试姓名为“CLARK”的员工工资，以及姓名为“a' OR 'b'='b”的员工工资。

2.使用PreparedStatement实现查询指定姓名的员工的工资，并测试姓名为“CLARK”的员工工资，以及姓名为“a' OR 'b'='b”的员工工资。

参考答案

实现此案例需要按照如下步骤进行。

步骤一：使用Statement实现根据员工姓名查询员工工资

新建EmpDAO类，在该类中添加findSalByEname1方法，在该方法中使用Statement实现根据员工姓名查询员工工资，代码如下所示：

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

publicclass EmpDAO {
	publicstaticvoid main(String[] args) {
		
	}
#cold_bold	publicvoid findSalByEname1(String ename){
#cold_bold		Connection con = null;
#cold_bold		Statement stmt = null;
#cold_bold		ResultSet rs = null;
#cold_bold		String sql = null;
#cold_bold
#cold_bold		try {
#cold_bold			sql = "select sal from emp where ename = '" + ename
#cold_bold					+ "'";
#cold_bold			System.out.println(sql);
#cold_bold			con = ConnectionSource.getConnection();
#cold_bold			stmt = con.createStatement();
#cold_bold			rs = stmt.executeQuery(sql);
#cold_bold
#cold_bold			while (rs.next()) {
#cold_bold				double sal=rs.getDouble("sal");
#cold_bold				System.out.println(sal);
#cold_bold			} 
#cold_bold		} catch (SQLException e) {
#cold_bold			System.out.println("数据库访问异常!");
#cold_bold			thrownew RuntimeException(e);
#cold_bold		} finally {
#cold_bold			try {
#cold_bold				if (rs != null) {
#cold_bold					rs.close();
#cold_bold				}
#cold_bold				if (stmt != null) {
#cold_bold					stmt.close();
#cold_bold				}
#cold_bold				if (con != null) {
#cold_bold					con.close();
#cold_bold				}
#cold_bold			} catch (SQLException e) {
#cold_bold				System.out.println("释放资源发生异常");
#cold_bold			}
#cold_bold		}
#cold_bold	}
}

步骤二：测试findSalByEname1方法

在EmpDAO的main方法中，分别使用姓名为“CLARK”、“a' OR 'b'='b”作为findSalByEname1方法的参数，测试是否能成功登录，代码如下所示：

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

publicclass EmpDAO {
	publicstaticvoid main(String[] args) {
		EmpDAO dao=new EmpDAO();
#cold_bold		dao.findSalByEname1("CLARK");
#cold_bold		dao.findSalByEname1("a' OR 'b'='b");
	}
	
	publicvoid findSalByEname1(String ename){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = null;

		try {
			sql = "select sal from emp where ename = '" + ename
					+ "'";
			System.out.println(sql);
			con = ConnectionSource.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);

			while (rs.next()) {
				double sal=rs.getDouble("sal");
				System.out.println(sal);
			} 
		} catch (SQLException e) {
			System.out.println("数据库访问异常!");
			thrownew RuntimeException(e);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				System.out.println("释放资源发生异常");
			}
		}
	}
}

运行EmpDAO类，控制台输出结果如下：

select sal from emp where ename = 'CLARK'
2450.0
select sal from emp where ename = 'a' OR 'b'='b'
800.0
1600.0
1250.0
2975.0
1250.0
2850.0
2450.0
3000.0
5000.0
1500.0
1100.0
950.0
3000.0
1300.0
5000.0
2000.0
2500.0
2500.0

使用、“a' OR 'b'='b”作为findSalByEname1方法的参数

从输出结果可以看出，使用姓名为“CLARK”可以成功查询出该员工的工资为2450.0

。但是姓名为“a' OR 'b'='b”在数据库中是不存在的数据，却查出很多条工资记录，不符合预期结果。问题出在哪里？

问题出在SQL语句上。查看上述控制台输出结果，可以看出SQL语句的where子句中使用or关键字连接两个表达式，即or关键字后边的表达式如果返回true，那么整个where条件的结果将是true。而or关键字后边的表达式为“'b'='b'”，该表达式的返回结果永远为true，因此，where条件的返回结果一定为true。这种现象在编程中称为SQL注入，是应该避免的编程方式。可以使用PreparedStatement来防止SQL 注入。

步骤三：使用PreparedStatement实现根据员工姓名查询员工工资

在EmpDAO类中添加findSalByEname方法，在该方法中使用PreparedStatement执行SQL语句，来实现据员工姓名查询员工工资，代码如下所示：

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

publicclass EmpDAO {
	publicstaticvoid main(String[] args) {
		EmpDAO dao=new EmpDAO();
		dao.findSalByEname1("CLARK");
		dao.findSalByEname1("a' OR 'b'='b");
		
	}
#cold_bold	publicvoid findSalByEname(String ename){
#cold_bold		Connection con = null;
#cold_bold		PreparedStatement stmt = null;
#cold_bold		ResultSet rs = null;
#cold_bold		String sql = null;
#cold_bold
#cold_bold		try {
#cold_bold			sql = "select sal from emp where ename = ?";
#cold_bold			con = ConnectionSource.getConnection();
#cold_bold			stmt = con.prepareStatement(sql);
#cold_bold			stmt.setString(1, ename);
#cold_bold			rs = stmt.executeQuery();
#cold_bold
#cold_bold			while (rs.next()) {
#cold_bold				double sal=rs.getDouble("sal");
#cold_bold				System.out.println(sal);
#cold_bold			} 
#cold_bold		} catch (SQLException e) {
#cold_bold			System.out.println("数据库访问异常!");
#cold_bold			thrownew RuntimeException(e);
#cold_bold		} finally {
#cold_bold			try {
#cold_bold				if (rs != null) {
#cold_bold					rs.close();
#cold_bold				}
#cold_bold				if (stmt != null) {
#cold_bold					stmt.close();
#cold_bold				}
#cold_bold				if (con != null) {
#cold_bold					con.close();
#cold_bold				}
#cold_bold			} catch (SQLException e) {
#cold_bold				System.out.println("释放资源发生异常");
#cold_bold			}
#cold_bold		}
#cold_bold	}
	
	publicvoid findSalByEname1(String ename){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = null;

		try {
			sql = "select sal from emp where ename = '" + ename
					+ "'";
			System.out.println(sql);
			con = ConnectionSource.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);

			while (rs.next()) {
				double sal=rs.getDouble("sal");
				System.out.println(sal);
			} 
		} catch (SQLException e) {
			System.out.println("数据库访问异常!");
			thrownew RuntimeException(e);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				System.out.println("释放资源发生异常");
			}
		}
	}
}

步骤四：测试findSalByEname方法

在EmpDAO的main方法中，分别使用姓名为“CLARK”、“a' OR 'b'='b”作为findSalByEname方法的参数，测试是否能成功登录，代码如下所示：

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

publicclass EmpDAO {
	publicstaticvoid main(String[] args) {
		EmpDAO dao=new EmpDAO();
#cold_bold		dao.findSalByEname("CLARK");
#cold_bold		dao.findSalByEname("a' OR 'b'='b");
		//dao.findSalByEname1("CLARK");
		//dao.findSalByEname1("a' OR 'b'='b");
		
	}
	publicvoid findSalByEname(String ename){
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String sql = null;

		try {
			sql = "select sal from emp where ename = ?";
			con = ConnectionSource.getConnection();
			stmt = con.prepareStatement(sql);
			stmt.setString(1, ename);
			rs = stmt.executeQuery();

			while (rs.next()) {
				double sal=rs.getDouble("sal");
				System.out.println(sal);
			} 
		} catch (SQLException e) {
			System.out.println("数据库访问异常!");
			thrownew RuntimeException(e);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				System.out.println("释放资源发生异常");
			}
		}
	}
	
	publicvoid findSalByEname1(String ename){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = null;

		try {
			sql = "select sal from emp where ename = '" + ename
					+ "'";
			System.out.println(sql);
			con = ConnectionSource.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);

			while (rs.next()) {
				double sal=rs.getDouble("sal");
				System.out.println(sal);
			} 
		} catch (SQLException e) {
			System.out.println("数据库访问异常!");
			thrownew RuntimeException(e);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				System.out.println("释放资源发生异常");
			}
		}
	}
}

运行EmpDAO类，控制台输出结果如下所示：

2450.0

从输出结果可以看出，使用姓名为“CLARK”可以成功查询出该员工的工资为2450.0。但是姓名为“a' OR 'b'='b”在数据库中是不存在的数据，没有查询到该员工的工资信息，符合预期结果，有效的防止了SQL注入的问题。

本案例中，EmpDAO类的完整代码如下所示：

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

publicclass EmpDAO {
	publicstaticvoid main(String[] args) {
		EmpDAO dao=new EmpDAO();
		dao.findSalByEname("CLARK");
		dao.findSalByEname("a' OR 'b'='b");
		//dao.findSalByEname1("CLARK");
		//dao.findSalByEname1("a' OR 'b'='b");
		
	}
	publicvoid findSalByEname(String ename){
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String sql = null;

		try {
			sql = "select sal from emp where ename = ?";
			con = ConnectionSource.getConnection();
			stmt = con.prepareStatement(sql);
			stmt.setString(1, ename);
			rs = stmt.executeQuery();

			while (rs.next()) {
				double sal=rs.getDouble("sal");
				System.out.println(sal);
			} 
		} catch (SQLException e) {
			System.out.println("数据库访问异常!");
			thrownew RuntimeException(e);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				System.out.println("释放资源发生异常");
			}
		}
	}
	
	publicvoid findSalByEname1(String ename){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = null;

		try {
			sql = "select sal from emp where ename = '" + ename
					+ "'";
			System.out.println(sql);
			con = ConnectionSource.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);

			while (rs.next()) {
				double sal=rs.getDouble("sal");
				System.out.println(sal);
			} 
		} catch (SQLException e) {
			System.out.println("数据库访问异常!");
			thrownew RuntimeException(e);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				System.out.println("释放资源发生异常");
			}
		}
	}
}

db.properties文件的内容与之前案例一样没有变化，该文件完整内容如下所示：

jdbc.driverClassName=oracle.jdbc.OracleDriver
jdbc.url=jdbc:oracle:thin:@127.0.0.1:1521:orcl
jdbc.username=scott
jdbc.password=tiger

#<!-- 初始化连接 -->
dataSource.initialSize=10
#<!-- 最大空闲连接 -->
dataSource.maxIdle=20
#<!-- 最小空闲连接 -->
dataSource.minIdle=5
#最大连接数量 
dataSource.maxActive=50
#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
dataSource.maxWait=1000

ConnectionSource类与之前案例一样没有变化，该类完整内容如下所示：

import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

import org.apache.commons.dbcp.BasicDataSource;

public class ConnectionSource {
	private static BasicDataSource dataSource = null;

	public ConnectionSource() {
	}
	public static void init() {

		Properties dbProps = new Properties();
		// 取配置文件可以根据实际的不同修改
		try {
			dbProps.load(ConnectionSource.class.getClassLoader().getResourceAsStream(
					"day01/v4/db.properties"));
		} catch (IOException e) {
			e.printStackTrace();
		}

		try {
			String driveClassName = dbProps.getProperty("jdbc.driverClassName");
			String url = dbProps.getProperty("jdbc.url");
			String username = dbProps.getProperty("jdbc.username");
			String password = dbProps.getProperty("jdbc.password");

			String initialSize = dbProps.getProperty("dataSource.initialSize");
			String minIdle = dbProps.getProperty("dataSource.minIdle");
			String maxIdle = dbProps.getProperty("dataSource.maxIdle");
			String maxWait = dbProps.getProperty("dataSource.maxWait");
			String maxActive = dbProps.getProperty("dataSource.maxActive");

			dataSource = new BasicDataSource();
			dataSource.setDriverClassName(driveClassName);
			dataSource.setUrl(url);
			dataSource.setUsername(username);
			dataSource.setPassword(password);

			// 初始化连接数
			if (initialSize != null)
				dataSource.setInitialSize(Integer.parseInt(initialSize));

			// 最小空闲连接
			if (minIdle != null)
				dataSource.setMinIdle(Integer.parseInt(minIdle));

			// 最大空闲连接
			if (maxIdle != null)
				dataSource.setMaxIdle(Integer.parseInt(maxIdle));

			// 超时回收时间(以毫秒为单位)
			if (maxWait != null)
				dataSource.setMaxWait(Long.parseLong(maxWait));

			// 最大连接数
			if (maxActive != null) {
				if (!maxActive.trim().equals("0"))
					dataSource.setMaxActive(Integer.parseInt(maxActive));
			}
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("创建连接池失败!请检查设置!!!");
		}
	}

	public static synchronized Connection getConnection() throws SQLException {
		if (dataSource == null) {
			init();
		}
		Connection conn = null;
		if (dataSource != null) {
			conn = dataSource.getConnection();
		}
		return conn;
	}
}