一、操作和访问数据库
数据库连接被用于向数据库服务器发送命令和 SQL 语句，并接受数据库服务器返回的结果。其实一个数据库连接就是一个Socket连接。
在 java.sql 包中有 3 个接口分别定义了对数据库的调用的不同方式：
Statement：用于执行静态 SQL 语句并返回它所生成结果的对象。PrepatedStatement：SQL 语句被预编译并存储在此对象中，可以使用此对象多次高效地执行该语句。CallableStatement：用于执行 SQL 存储过程。
二、使用Statement操作数据表的弊端
示例代码
package StatementTest;
public class User {
private String user;
private String password;
public User() {
}
public User(String user, String password) {
super();
this.user = user;
this.password = password;
}
@Override
public String toString() {
return "User [user=" + user + ", password=" + password + "]";
}
public String getUser() {
return user;
}
public void setUser(String user) {
this.user = user;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
package StatementTest;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.Scanner;
import org.junit.Test;
public class StatementTest {
// 使用Statement的弊端：需要拼写sql语句，并且存在SQL注入的问题
@Test
public void testLogin() {
Scanner scan = new Scanner(System.in);
System.out.print("用户名：");
String userName = scan.nextLine();
System.out.print("密 码：");
String password = scan.nextLine();
// SELECT user,password FROM user_table WHERE USER = '1' or ' AND PASSWORD = '
// ='1' or '1' = '1';
String sql = "SELECT user,password FROM user_table WHERE USER = '" + userName + "' AND PASSWORD = '" + password
+ "'";
User user = get(sql, User.class);
if (user != null) {
System.out.println("登陆成功!");
} else {
System.out.println("用户名或密码错误！");
}
}
// 使用Statement实现对数据表的查询操作
public <T> T get(String sql, Class<T> clazz) {
T t = null;
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
// 1.加载配置文件
InputStream is = StatementTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
// 2.读取配置信息
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String url = pros.getProperty("url");
String driverClass = pros.getProperty("driverClass");
// 3.加载驱动
Class.forName(driverClass);
// 4.获取连接
conn = DriverManager.getConnection(url, user, password);
st = conn.createStatement();
rs = st.executeQuery(sql);
// 获取结果集的元数据
ResultSetMetaData rsmd = rs.getMetaData();
// 获取结果集的列数
int columnCount = rsmd.getColumnCount();
if (rs.next()) {
t = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
// //1. 获取列的名称
// String columnName = rsmd.getColumnName(i+1);
// 1. 获取列的别名
String columnName = rsmd.getColumnLabel(i + 1);
// 2. 根据列名获取对应数据表中的数据
Object columnVal = rs.getObject(columnName);
// 3. 将数据表中得到的数据，封装进对象
Field field = clazz.getDeclaredField(columnName);
field.setAccessible(true);
field.set(t, columnVal);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 关闭资源
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return null;
}
}
使用Statement操作数据表，存在拼串操作，繁琐，而且存在SQL注入问题。SQL 注入是利用某些系统没有对用户输入的数据进行充分的检查，而在用户输入数据中注入非法的 SQL 语句段或命令。
此时数据库表有如下数据
正常操作可以登陆成功
但是如果按照下面非法输入数据，仍然登录成功
原因在于这条sql语句
SELECT user,password FROM user_table WHERE USER = '1' or ' AND PASSWORD = '='1' or '1' = '1';
1' OR 充当了user
='1' OR '1' = '1充当了password
整个sql语句的结构改变了，由原来的AND逻辑变成了OR逻辑，'1' = '1'是恒成立的，所以显示登录成功。
三、PreparedStatement实现增删改操作
对于 Java 而言，要防范 SQL 注入，只要用 PreparedStatement(从Statement扩展而来) 取代 Statement 就可以了。
代码示例
public class PreparedStatementTest {
//PreparedStatement实现表数据的添加操作
//向customers表中添加一条记录
@Test
public void testInsert() {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
//1、读取配置文件中的4个基本信息
InputStream resourceAsStream = PreparedStatementTest.class.getClassLoader().getResourceAsStream("jdbc.properties");//类的加载器的一个主要方法getResourceAsStream() ，可以获取类路径下的指定文件的输入流
Properties properties = new Properties();
properties.load(resourceAsStream);
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
String driverClass = properties.getProperty("driverClass");
//2、加载驱动
Class.forName(driverClass);
//3、获取连接
connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
//4、预编译sql语句，返回PreparedStatement实例
String sql = "insert into customers(name,email,birth)values(?,?,?)"; //？是占位符
preparedStatement = connection.prepareStatement(sql);
//5、填充占位符
preparedStatement.setString(1,"张三");
preparedStatement.setString(2,"zhangsan@qq.com");
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
Date date = simpleDateFormat.parse("2021-04-02");
preparedStatement.setDate(3,new java.sql.Date(date.getTime()));
//6、执行操作
preparedStatement.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
//7、资源的关闭
try {
if (preparedStatement != null)
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (connection != null)
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//PreparedStatement实现表数据的修改操作
//修改customers表中的一条记录
@Test
public void testUpdate() throws Exception {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
//1、获取数据库的连接
connection = JDBCUtils.getConnection();
//2、预编译sql语句，返回PreparedStatement的实例
String sql = "update customers set name = ? where id = ?";
preparedStatement = connection.prepareStatement(sql);
//3、填充占位符
preparedStatement.setObject(1,"李四");
preparedStatement.setObject(2,19);
//4、执行
preparedStatement.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
//5、资源的关闭
JDBCUtils.closeResource(connection,preparedStatement);
}
}
//PreparedStatement实现表数据的删除操作
//删除customers表中的一条记录
@Test
public void testDelete() throws Exception {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
//1、获取数据库的连接
connection = JDBCUtils.getConnection();
//2、预编译sql语句，返回PreparedStatement的实例
String sql = "delete from customers where id = ?";
preparedStatement = connection.prepareStatement(sql);
//3、填充占位符
preparedStatement.setObject(1,19);
//4、执行
preparedStatement.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
//5、资源的关闭
JDBCUtils.closeResource(connection,preparedStatement);
}
}
}
四、PreparedStatement实现通用的增删改操作
主要的不同在于第二步预编译SQL语句和第三步填充占位符
@Test
public void testCommonUpdate() throws Exception{
String sql = "insert into customers(name,email,birth)values(?,?,?)";
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
Date date = simpleDateFormat.parse("2021-04-02");
update(sql,"李四","lisi@163.com",new java.sql.Date(date.getTime()));
}
//通用的增删改操作
public void update(String sql, Object ...args) throws Exception{
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
//1、获取数据库的连接
connection = JDBCUtils.getConnection();
//2、预编译sql语句，返回PreparedStatement的实例
preparedStatement = connection.prepareStatement(sql);
//3、填充占位符
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
//4、执行
preparedStatement.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
//5、资源的关闭
JDBCUtils.closeResource(connection,preparedStatement);
}
}
五、JDBCUtils实现封装数据库连接和关闭操作
public class JDBCUtils {
//获取数据库的连接
public static Connection getConnection() throws Exception{
//1、读取配置文件中的4个基本信息
InputStream resourceAsStream = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");//类的加载器的一个主要方法getResourceAsStream() ，可以获取类路径下的指定文件的输入流
Properties properties = new Properties();
properties.load(resourceAsStream);
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
String driverClass = properties.getProperty("driverClass");
//2、加载驱动
Class.forName(driverClass);
//3、获取连接
Connection connection = DriverManager.getConnection(url, user, password);
return connection;
}
//关闭数据库的连接
public void closeResource(Connection connection, Statement statement){
//资源的关闭
try {
if (statement != null)
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (connection != null)
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
六、PreparedStatement实现查询操作
查询和增删改不同的地方在于，第四步执行并返回结果集，然后处理结果集。
//对Customers表进行查询操作
public class CustomersForQuery {
@Test
public void testQuery1() {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtils.getConnection();
String sql = "select id,name,email,birth from customers where id = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1,1);
//4、执行，返回结果集,并对结果集进行处理
resultSet = preparedStatement.executeQuery();
if (resultSet.next()){ //判断结果集的下一条是否有数据，如果有数据返回true，如果返回false，指针不会下移
//获取当前这条数据的各个字段值
int id = resultSet.getInt(1);
String name = resultSet.getString(2);
String email = resultSet.getString(3);
Date birth = resultSet.getDate(4);
//方式一：
//System.out.println("id = " + id + ",name = " + name + ",email = " + email + ",birth = " + birth);
//方式二：
//Object[] data = new Object[]{id,name,email,birth};
//方式三：将数据封装成一个对象
Customer customer = new Customer(id, name, email, birth);
System.out.println(customer);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
//关闭资源
JDBCUtils.closeResource(connection,preparedStatement,resultSet);
}
}
}
针对于Customers表的通用查询操作
@Test
public void testQueryForCustomers(){
String sql = "select id,name,birth,email from customers where id = ?";
Customer customer = queryForCustomers(sql, 16);
System.out.println(customer);
}
public Customer queryForCustomers(String sql, Object ...args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
resultSet = preparedStatement.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData(); //获取结果集的元数据
int columnCount = metaData.getColumnCount(); //通过元数据获取结果集中的列数
if (resultSet.next()){
Customer customer = new Customer();
//处理结果集一行数据中的每一个列
for (int i = 0; i < columnCount; i++) {
//获取每个列的的值
Object columnValue = resultSet.getObject(i + 1);
//获取每个列的列名
String columnName = metaData.getColumnName(i + 1);
//给Customer对象指定的columnName属性，赋值为columnValue：通过反射
Field declaredField = Customer.class.getDeclaredField(columnName);
declaredField.setAccessible(true);
declaredField.set(customer,columnValue);
}
return customer;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection,preparedStatement,resultSet);
}
return null;
}
针对于Order表的通用查询操作，当数据库表的列名与java类的属性名不一致时，可以通过给数据库表的字段名起别名实现过渡。使用类的属性名来命名字段的别名，同时使用getColumnLabel()方法。
@Test
public void testOrderForQuery(){
//String sql = "select order_id,order_name,order_date from `order` where order_id = ?";
String sql = "select order_id orderId,order_name orderName,order_date orderDate from `order` where order_id = ?";
Order order = orderForQuery(sql, 1);
System.out.println(order);
}
public Order orderForQuery(String sql, Object ...args){
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1,args[i]);
}
//执行，获取返回的结果集
resultSet = preparedStatement.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData(); //获取结果集的元数据
int columnCount = metaData.getColumnCount(); //通过元数据获取结果集中的列数
if (resultSet.next()){
Order order = new Order();
//处理结果集一行数据中的每一个列
for (int i = 0; i < columnCount; i++) {
//获取每个列的的值：通过resultSet结果集
Object columnValue = resultSet.getObject(i + 1);
//获取每个列的列名：通过metaData元数据
//String columnName = metaData.getColumnName(i + 1);
//获取每个列的列名的别名：通过metaData元数据
String columnLabel = metaData.getColumnLabel(i + 1);
//通过反射，给order对象指定的columnName属性，赋值为columnValue
Field declaredField = Order.class.getDeclaredField(columnLabel);
declaredField.setAccessible(true);
declaredField.set(order,columnValue);
}
return order;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection,preparedStatement,resultSet);
}
return null;
}
查询操作的流程图
七、PreparedStatement实现不同表的通用查询操作
返回的结果集只有一条记录的情况
public class PreparedStatementQueryTest {
@Test
public void testGetInstance(){
String sql = "select id,name,birth,email from customers where id = ?";
Customer customer = getInstance(Customer.class, sql, 16);
System.out.println(customer);
String sql1 = "select order_id orderId,order_name orderName,order_date orderDate from `order` where order_id = ?";
Order order = getInstance(Order.class, sql1, 2);
System.out.println(order);
}
//泛型方法，为了区别Class<T>的<T>是泛型参数，而不是有一个类叫T，所以方法前面要加上<T>代表泛型方法,返回的类型为T，
public <T> T getInstance(Class<T> clazz, String sql, Object ...args){
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1,args[i]);
}
//执行，获取返回的结果集
resultSet = preparedStatement.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData(); //获取结果集的元数据
int columnCount = metaData.getColumnCount(); //通过元数据获取结果集中的列数
if (resultSet.next()){
T t = clazz.newInstance();
//处理结果集一行数据中的每一个列
for (int i = 0; i < columnCount; i++) {
//获取每个列的的值：通过resultSet结果集
Object columnValue = resultSet.getObject(i + 1);
//获取每个列的列名：通过metaData元数据
//String columnName = metaData.getColumnName(i + 1);
//获取每个列的列名的别名：通过metaData元数据
String columnLabel = metaData.getColumnLabel(i + 1);
//通过反射，给t对象指定的columnName属性，赋值为columnValue
Field declaredField = clazz.getDeclaredField(columnLabel);
declaredField.setAccessible(true);
declaredField.set(t,columnValue);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection,preparedStatement,resultSet);
}
return null;
}
}
返回的结果集中有多条记录的情况
@Test
public void testForList(){
String sql = "select id,name,birth,email from customers where id < ?";
List<Customer> customerList = getForList(Customer.class, sql, 16);
customerList.forEach(System.out::println); //consumer消费者接口的方法引用
String sql1 = "select order_id orderId,order_name orderName,order_date orderDate from `order` where order_id < ?";
List<Order> orderList = getForList(Order.class, sql1, 3);
orderList.forEach(System.out::println);
}
public <T> List<T> getForList(Class<T> clazz, String sql, Object ...args){
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1,args[i]);
}
//执行，获取返回的结果集
resultSet = preparedStatement.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData(); //获取结果集的元数据
int columnCount = metaData.getColumnCount(); //通过元数据获取结果集中的列数
ArrayList<T> list = new ArrayList<>(); //创建集合对象
while (resultSet.next()){
T t = clazz.newInstance();
//处理结果集一行数据中的每一个列：给t对象指定属性赋值
for (int i = 0; i < columnCount; i++) {
//获取每个列的的值：通过resultSet结果集
Object columnValue = resultSet.getObject(i + 1);
//获取每个列的列名：通过metaData元数据
//String columnName = metaData.getColumnName(i + 1);
//获取每个列的列名的别名：通过metaData元数据
String columnLabel = metaData.getColumnLabel(i + 1);
//通过反射，给t对象指定的columnName属性，赋值为columnValue
Field declaredField = clazz.getDeclaredField(columnLabel);
declaredField.setAccessible(true);
declaredField.set(t,columnValue);
}
list.add(t);
}
return list;
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection,preparedStatement,resultSet);
}
return null;
}
八、PreparedStatement解决SQL注入问题
public class PreparedStatementSQLTest {
public static void main(String[] args) {
testLogin();
}
@Test
public static void testLogin() {
Scanner scan = new Scanner(System.in);
System.out.print("用户名：");
String userName = scan.nextLine();
System.out.print("密 码：");
String password = scan.nextLine();
// SELECT user,password FROM user_table WHERE USER = '1' or ' AND PASSWORD = '='1' or '1' = '1';
String sql = "SELECT user,password FROM user_table WHERE user = ? AND password = ?";
User user = getInstance(User.class,sql,userName,password);
if (user != null) {
System.out.println("登陆成功!");
} else {
System.out.println("用户名或密码错误！");
}
}
public static <T> T getInstance(Class<T> clazz, String sql, Object... args){
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1,args[i]);
}
//执行，获取返回的结果集
resultSet = preparedStatement.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData(); //获取结果集的元数据
int columnCount = metaData.getColumnCount(); //通过元数据获取结果集中的列数
if (resultSet.next()){
T t = clazz.newInstance();
//处理结果集一行数据中的每一个列
for (int i = 0; i < columnCount; i++) {
//获取每个列的的值：通过resultSet结果集
Object columnValue = resultSet.getObject(i + 1);
//获取每个列的列名：通过metaData元数据
//String columnName = metaData.getColumnName(i + 1);
//获取每个列的列名的别名：通过metaData元数据
String columnLabel = metaData.getColumnLabel(i + 1);
//通过反射，给t对象指定的columnName属性，赋值为columnValue
Field declaredField = clazz.getDeclaredField(columnLabel);
declaredField.setAccessible(true);
declaredField.set(t,columnValue);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection,preparedStatement,resultSet);
}
return null;
}
}
执行结果
通过使用占位符，预编译SQL语句时，就已经保存了SQL的逻辑结构。通过后续填充占位符的方式，只是填充了数据，并没有改变SQL的逻辑结构，所以就不存在SQL注入的问题了。除此之外
PreparedStatement 还可以操作Blob类型的数据，以及实现更高效批量的数据操作。
200
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
