1. Statement操作SQL语句
1.1 Statement查询SQL数据操作
package com.my.demo1;
import com.my.domain.User;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class Demo1 {
public static void main(String[] args) {
List<User> list = new Demo1().findAll();
System.out.println(list);
}
public List<User> findAll() {
Statement statement = null;
Connection connection = null;
ResultSet users = null;
User user = null;
List<User> friends = new ArrayList<>();
try {
Class.forName("com.mysql.jdbc.Driver");
String URL = "jdbc:mysql://localhost:3306/feige001? useSSL=true";
String USER = "root";
String PASSWORD = "123456";
connection = DriverManager.getConnection(URL, USER, PASSWORD);
statement = connection.createStatement();
String sql = "select * from user ";
users = statement.executeQuery(sql);
while (users.next()) {
int id = users.getInt(1);
String name = users.getString("name");
String ps = users.getString("password");
user = new User(id,name,ps);
friends.add(user);
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
} finally {
if (users != null) {
try {
users.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return friends;
}
}
2. JDBC工具类封装
需要完成的内容
1. 数据库连接对象java.sql.Connection获取过程
2. 关闭资源
JDBC工具类
1. 所有的方法都是static修饰的静态方法
2. 2. 需要考虑自动加载过程,完成一些必要数据的自动处理
url driver user password
3. 所需数据库连接条件保存到文件中
4. 关闭方法提供多种多样组合方法
【注意】 db.properties文件保存到src目录下
数据库配置文件 db.properties
URL = jdbc:mysql://localhost:3306/feige001? useSSL=true
USER = root
PASSWORD = 123456
DRIVER = com.mysql.jdbc.Driver
工具封装
package com.my.utils;
import java.io.FileReader;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
public class JDBCUtils {
private static String url;
private static String user;
private static String ps;
private static String driver;
/**
* 读取配置文件
*/
static {
try {
Properties properties = new Properties();
properties.load(new FileReader(JDBCUtils.class.getClassLoader().getResource("db.properties").getPath()));
url = properties.getProperty("URL");
user = properties.getProperty("USER");
ps = properties.getProperty("PASSWORD");
driver = properties.getProperty("DRIVER");
Class.forName(driver);
} catch (IOException | ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
* 获取连接
* @return
* @throws SQLException
*/
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,user,ps);
}
/**
* 释放资源
* @param rs
* @param statement
* @param connection
*/
public static void close(ResultSet rs, Statement statement,Connection connection) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void closeAll(Connection connection) {
close(null,null,connection);
}
public static void closeAll(Connection connection,Statement statement) {
close(null,statement,connection);
}
public static void closeAll(Connection connection,Statement statement,ResultSet resultSet) {
close(resultSet,statement,connection);
}
}
3. PreparedStatement使用
3.1 PreparedStatement插入数据SQL完成
@Test
public void testInsert() {
User user = new User(5 ,"昊昊","123456");
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JDBCUtils.getConnection();
String sql ="insert into user(id,name,password) value (?,?,?)";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1,user.getId());
preparedStatement.setObject(2,user.getName());
preparedStatement.setObject(3,user.getPassword());
int i = preparedStatement.executeUpdate();
System.out.println(i);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeAll(connection,preparedStatement);
}
}
3.2 PreparedStatment修改SQL完成
/**
* 修改用户
*/
@Test
public void testUpdate() {
User user = new User(1 ,"陈冠希","666666");
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JDBCUtils.getConnection();
String sql ="update user set name =?,password =? where id =?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1,user.getName());
preparedStatement.setObject(2,user.getPassword());
preparedStatement.setObject(3,user.getId());
int i = preparedStatement.executeUpdate();
System.out.println(i);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeAll(connection,preparedStatement);
}
}
3.3 PreparedStatment删除SQL完成
/**
* 删除用户
*/
@Test
public void testDelete() {
int id = 5;
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JDBCUtils.getConnection();
String sql ="delete from user where id=?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1,id);
int i = preparedStatement.executeUpdate();
System.out.println(i);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeAll(connection,preparedStatement);
}
}
3.4 PreparedStatment查询SQL完成
/**
* 查询指定用户
*/
@Test
public void testSelectOne() {
int id = 2;
User user = null;
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtils.getConnection();
String sql ="select * from user where id =?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1,id);
resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
user = new User(resultSet.getInt(1),resultSet.getString(2),resultSet.getString(3));
}
System.out.println(user);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeAll(connection,preparedStatement,resultSet);
}
}
/**
* 查询所有用户
*/
@Test
public void testSelectAll() {
List<User> list = new ArrayList<>();
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtils.getConnection();
String sql ="select * from user";
preparedStatement = connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
list.add(new User(resultSet.getInt(1),resultSet.getString(2),resultSet.getString(3)));
}
for (User user : list) {
System.out.println(user);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeAll(connection,preparedStatement,resultSet);
}
}
4. SQL注入问题
Statement是一个SQL语句搬运工对象,不存在SQL语句语预处 理能力,Java代码SQL语句原封不动搬运到数据库!!! PreparedStatement 存在SQL语句预处理过程,这个过程可 以有效的防止一定条件的SQL注入 Statement存在SQL注入问题,而PreparedStatemen可以有 效的避免SQL注入
强烈推荐使用PreparedStatement
1. PreparedStatement操作性更强 2. PreparedStatement安全性更高
5. BaseDao封装
5.1 需求和问题
需求
1. 完成通用的更新方法,满足insert,update,delete操作
2. 完成通用的查询方法,满足select
问题
1. 数据库连接对象获取[解决]
2.资源关闭[解决]
3.PreparedStatement参数赋值过程【未解决】
a. 参数个数 PreparedStatement预处理SQL语句?有多少个
b. 赋值顺序‘ 参数传入方式和顺序问题
2. 查询结果集解析过程【未解决】
a. 返回值是一个List<目标数据类型>集合
b. 返回值是一个Object数组
5.2 【补充知识点-元数据】
三种元数据
数据库元数据
通过java.sql.Connection获取对应的元数据
/**
* 获取数据库元数据
*/
@Test
public void test1() {
try {
Connection connection = JDBCUtils.getConnection();
DatabaseMetaData databaseMetaData = connection.getMetaData();
String userName = databaseMetaData.getUserName();
String driverName = databaseMetaData.getDriverName();
String version = databaseMetaData.getDriverVersion();
String url = databaseMetaData.getURL();
System.out.println(userName);
System.out.println(driverName);
System.out.println(version);
System.out.println(url);
} catch (SQLException e) {
e.printStackTrace();
}
}
SQL语句元数据
通过java.sql.PreparedStatement获取对应的元数据
@Test
public void test2() {
try {
Connection connection = JDBCUtils.getConnection();
String sql = "insert into user(id, name, password) VALUE (?,?,?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
ParameterMetaData parameterMetaData = preparedStatement.getParameterMetaData();
int count = parameterMetaData.getParameterCount();
System.out.println(count);
} catch (SQLException e) {
e.printStackTrace();
}
}
数据库结果集元数据
通过java.sql.ResultSet获取对应的元数据
@Test
public void test3() {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtils.getConnection();
String sql = "select * from user";
preparedStatement = connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
while (resultSet.next()) {
for (int i = 1; i <= columnCount; i++) {
System.out.println(metaData.getColumnName(i) + ":" + resultSet.getObject(i));
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeAll(connection,preparedStatement,resultSet);
}
}
MetaData 【重点】
1. SQL语句元数据,参数元数据其中的参数个数 对应 ? 占位符个数
2. 结果集元数据中的字段个数,和对应当前字段下标的字段名字
5.3 【补充知识点 BeanUtils使用】
BeanUtils提供了对于符合JavaBean规范的实体类进行赋值,取值,拷贝操作的一系列方法,可以自动完成数据类型转换,方 便开发者在数据交互中使用。 所有的方法都是静态方法
三个方法
1. 赋值指定成员变量对应数据
a. 符合JavaBean规范的类对象
b. 指定成员变量的名字
c. Object类型数据用于赋值成员变量
@Test
public void test1() throws InvocationTargetException, IllegalAccessException {
User user = new User();
BeanUtils.setProperty(user, "id","10");
BeanUtils.setProperty(user, "name","彭于晏");
BeanUtils.setProperty(user, "password",123456);
System.out.println(user);
}
2. 取值指定成员变量的数据
a. 符合JavaBean规范的类对象
b. 指定成员变量的名字
返回值是对应当前成员变量的数据类型
@Test
public void test2() throws IllegalAccessException, NoSuchMethodException, InvocationTargetException {
User user = new User(11 , "锤子" ,"123456");
System.out.println(BeanUtils.getProperty(user,"id"));
System.out.println(BeanUtils.getProperty(user,"name"));
System.out.println(BeanUtils.getProperty(user,"password"));
}
3. 拷贝符合JavaBean规范的两个对象数据
a. 符合JavaBean规范的目标类对象
b. 符合JavaBean规范的目标数据源对象
@Test
public void test3() throws InvocationTargetException, IllegalAccessException {
User user1 = new User(111 , "棒子", "111222");
User user2 = new User();
System.out.println("user1:" + user1);
System.out.println("user2:" + user2);
BeanUtils.copyProperties(user2,user1);
System.out.println("user1:" + user1);
System.out.println("user2:" + user2);
}
4. 真香方法,从Map双边对联中匹配赋值数据到符合 JavaBean规范的
类对象
a. 符合JavaBean规范的类对象 b. Map双边队列
@Test
public void test4() throws InvocationTargetException, IllegalAccessException, NoSuchMethodException {
HashMap<String , String> map = new HashMap<>();
map.put("id","222");
map.put("name","飞哥哥");
map.put("password","666666");
User user = new User();
BeanUtils.populate(user,map);
System.out.println(user);
System.out.println(BeanUtils.getProperty(user,"id"));
System.out.println(BeanUtils.getProperty(user,"name"));
System.out.println(BeanUtils.getProperty(user,"password"));
}
5.4 通用更新方法实现
分析:
完成通用的更新方法,update,insert,delete操作
权限修饰符:
public
返回值类型:
int 当前SQL语句参数,数据库收到影响的行数
方法名: update
形式参数列表:
1. String sql语句
、指定执行的SQL语句 update insert delete。。。
2. SQL语句可能需要参数
SQL有可能没有参数,有可能多个参数,而且参数类 型都不一
样!!!
a. Object… Object类型的不定长参数
b. Object[] 所有对应当前SQL语句的参数都存储在
Object 类型数组中 (String sql, Object[] parameters)
方法声明: public int update(String sql, Object[] parameters)
/**
* 通用的更新方法
* @param sql 需要执行的sql语句
* @param parameters 对应当前sql语句的参数列表
* @return 返回数据库收到影响的数据行数
*/
public int update(String sql , Object... parameters) {
int affectedRows = 0;
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JDBCUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
int count = preparedStatement.getParameterMetaData().getParameterCount();
if (count != 0 && parameters != null && count ==parameters.length) {
for (int i = 0; i < parameters.length ; i++) {
preparedStatement.setObject(i+1 ,parameters[i]);
}
}
affectedRows = preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeAll(connection,preparedStatement);
}
return affectedRows;
}
5.5 通用查询方法实现
分析:
完成通用的查询方法,select操作
权限修饰符:
public
需要声明泛型 T ==> Type
返回值类型: List<指定数据类型>
方法名: query
形式参数列表:
1. String sql语句
指定执行的SQL语句 Select语句
2. SQL语句可能需要参数
SQL有可能没有参数,有可能多个参数,而且参数类型都不一
样!!!
a. Object… Object类型的不定长参数
b. Object[] 所有对应当前SQL语句的参数都存储在Object 类型数组中
3. 告知当前SQL语句出现的查询结果对应数据类型是哪一 个 Class cls
a. 泛型T 用于数据类型约束,传入哪一个类的.class当 前T对应的就是哪一个类
b. Class 反射对应的Class类对象
为所欲为!!!
有了对应类的.class字节码文件对应Class对 象。可以通过反射为所欲
为 (String sql, Object[] parameters, Class cls)
方法声明: public List query(String sql, Object[] parameters, Class cls)
/**
* 通用查找方法
* @param sql 需要执行的sql语句
* @param parameters 与sql语句对应的参数列表
* @param cls 指定数据类型的class对象
* @param <T> 泛型占位符
* @return 返回查找的结果集合
*/
public <T> List<T> query(String sql,Object[] parameters,Class<T> cls) {
ResultSet resultSet = null;
Connection connection = null ;
PreparedStatement preparedStatement = null;
List<T> list = new ArrayList<>();
try {
connection = JDBCUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
int count = preparedStatement.getParameterMetaData().getParameterCount();
if (count!=0 && parameters != null && parameters.length == count) {
for (int i = 0; i<parameters.length; i++) {
preparedStatement.setObject(i+1,parameters[i]);
}
}
resultSet = preparedStatement.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
while (resultSet.next()) {
T t = cls.getConstructor().newInstance();
for (int i =1 ;i<=columnCount ; i++) {
// String columnName = metaData.getColumnName(i);
// Object value = resultSet.getObject(columnName);
// BeanUtils.setProperty(t,columnName,value);
BeanUtils.setProperty(t,metaData.getColumnName(i),resultSet.getObject(i));
}
list.add(t);
}
} catch (SQLException | NoSuchMethodException | InstantiationException | IllegalAccessException | InvocationTargetException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeAll(connection,preparedStatement,resultSet);
}
return list.size() != 0 ? list : null;
}
}