利用JDBC实现数据库的CRUD
使用Statement
使用statement不是常用的数据库操作方式,其对数据库的操作是由拼接sql语句来实现的,因为拼接,所以可以使用很多方式造成数据库的漏洞,例如select语句,完全可以在where中拼接一个永真的语句,这就造成了漏洞的产生,所以一般使用的都是PrepareStatement(预编译的Statement)
使用PrepareStatement
PrepareState使用预编译的形式,将值单独拉出来,语句预先编译好,这样减少了漏洞的产生
例:
@Test
public void insert(){
Connection connect = null;
PreparedStatement preparedStatement = null;
try {
connect = getConnect();
//sql预编译 "?"代表占位符
String sql = "insert into user(account,password)values(?,?)";
preparedStatement = connect.prepareStatement(sql);
//填充占位符 索引从1开始 与sql相同
preparedStatement.setString(1,"d");
preparedStatement.setString(2,"888888");
//执行操作
preparedStatement.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
//资源关闭
try {
if (preparedStatement != null)
preparedStatement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if (connect != null)
connect.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
预编译中使用?当作占位符,之后使用setXxx进行填充,上例演示了插入操作,接下来举例其他常规操作(其中数据库的连接和资源的关闭使用JDBCUtils自己封装的工具类进行代替)
首先对操作进行分类 :
增删改为一类,查询一类
增删改没有返回值,查询有返回值
因此可以对增删改操作进行封装
增删改操作
通用的增删改操作:
//通用的更新操作(增删改)
public void update(String sql,Object ...values){
Connection connect = null;
PreparedStatement preparedStatement = null;
try {
connect = JDBCUtils.getConnect();
preparedStatement = connect.prepareStatement(sql);
//设置参数
for (int i=1;i<=values.length;i++){
System.out.println("i:" + i+"value" + values[i-1]);
preparedStatement.setObject(i,values[i-1]);
}
preparedStatement.execute();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtils.closeResource(connect,preparedStatement);
}
}
@Test
public void testUpdate(){
String sql = "update user set password = ? where account = ?";
update(sql,"666666","d");
}
查询操作
@Test
public void testQuery(){
Connection connect = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connect = JDBCUtils.getConnect();
String sql = "select password from user where account = ?";
preparedStatement = connect.prepareStatement(sql);
preparedStatement.setString(1,"a");
resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
System.out.println(resultSet.getString(1));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JDBCUtils.closeResource(connect,preparedStatement,resultSet);
}
}
这样写法的查询操作局限性比较大,只适用于固定的sql语句,所以可以将此方法稍加改进,改变为通用的查询操作
通用的查询操作:
public UserInfo query(String sql,Object ...objects) {
Connection connect = null;
PreparedStatement ps = null;
ResultSet resultSet = null;
try {
connect = JDBCUtils.getConnect();
ps = connect.prepareStatement(sql);
//填充占位符
for (int i = 0; i < objects.length; i++) {
ps.setObject(i+1, objects[i]);
}
//执行查询
resultSet = ps.executeQuery();
//接收查询结果
if (resultSet.next()) {
//获取结果集元数据(元数据例如列名,列数)
ResultSetMetaData metaData = ps.getMetaData();
//获取列数(查询时不一定查找表中所有列的数据,所以需要获取结果集的列数,有几列就设置几列的数据到UserInfo对象中)
int columnCount = metaData.getColumnCount();
UserInfo userInfo = new UserInfo();
for (int i = 0; i < columnCount; i++) {
//获取查询出来的值
Object value = resultSet.getObject(i + 1);
//SQL中计数从1开始,获取列名(列名与bean类属性对应)
String name = metaData.getColumnName(i + 1);
//利用反射,设置属性
Field field = UserInfo.class.getDeclaredField(name);
//设置权限
field.setAccessible(true);
field.set(userInfo,value);
}
return userInfo;
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} catch (NoSuchFieldException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}finally {
//关闭资源
JDBCUtils.closeResource(connect,ps,resultSet);
}
//查询失败,返回空
return null;
}
@Test
public void testQuery2(){
String sql = "select account,password,birthday,phone,sexual from user where id = ?";
UserInfo userInfo = query(sql, 1);
System.out.println(userInfo.toString());
}
//结果
UserInfo{account='张三', password='123456', birthday=2021-10-22, phone='13901115076', sexual='male'}
但是对于该方法,仍有通用扩展空间,接下来实现更为通用的修改,可以查询不同表的数据
//一张表对应一个类 一个列对应一个类的属性
public <T> T difTableQuery(Class<T> clazz,String sql,Object ...objects) {
Connection connect = null;
PreparedStatement ps = null;
ResultSet resultSet = null;
try {
connect = JDBCUtils.getConnect();
ps = connect.prepareStatement(sql);
//填充占位符
for (int i = 0; i < objects.length; i++) {
ps.setObject(i+1,objects[i]);
}
//查询操作获取结果集
resultSet = ps.executeQuery();
//获取结果集元数据
ResultSetMetaData metaData = resultSet.getMetaData();
T t = clazz.getConstructor().newInstance();
//查询到数据
if (resultSet.next()) {
//获取列数
int columnCount = metaData.getColumnCount();
for (int i = 0; i < columnCount; i++) {
//获取列名
String name = metaData.getColumnName(i + 1);
//获取结果
Object value = resultSet.getObject(i + 1);
//通过反射 设置属性值
Field field = clazz.getDeclaredField(name);
//设置权限
field.setAccessible(true);
field.set(t,value);
}
return t;
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (NoSuchFieldException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connect,ps,resultSet);
}
return null;
}
@Test
public void testQuery3(){
String sql1 = "select account,password,birthday,phone,sexual from user where id = ?";
UserInfo userInfo = difTableQuery(UserInfo.class,sql1, 1);
System.out.println(userInfo.toString());
String sql2 = "select math,chinese from grade where account = ?";
UserGrade userGrade = difTableQuery(UserGrade.class,sql2, "张三");
System.out.println(userGrade.toString());
}
//结果
UserInfo{account='张三', password='123456', birthday=2021-10-22, phone='13901115076', sexual='male'}
UserGrade{account='null', math=91, chinese=60, english=0}
当然这里还不是最终的最通用的写法,当最后结果为多个的时候,可以返回列表,当然该方法也适用于单个结果的查询方式
//对于查询结果为多个的 通用性查询操作
//一张表对应一个类 一个列对应一个类的属性
public <T> List<T> difTableQueryForList(Class<T> clazz, String sql, Object ...objects) {
Connection connect = null;
PreparedStatement ps = null;
ResultSet resultSet = null;
ArrayList<T> list = new ArrayList<T>();
try {
connect = JDBCUtils.getConnect();
ps = connect.prepareStatement(sql);
//填充占位符
for (int i = 0; i < objects.length; i++) {
ps.setObject(i+1,objects[i]);
}
//查询操作获取结果集
resultSet = ps.executeQuery();
//获取结果集元数据
ResultSetMetaData metaData = resultSet.getMetaData();
//查询到数据
while (resultSet.next()) {
T t = clazz.getConstructor().newInstance();
//获取列数
int columnCount = metaData.getColumnCount();
for (int i = 0; i < columnCount; i++) {
//获取列名
String name = metaData.getColumnName(i + 1);
//获取结果
Object value = resultSet.getObject(i + 1);
//通过反射 设置属性值
Field field = clazz.getDeclaredField(name);
//设置权限
field.setAccessible(true);
field.set(t,value);
}
list.add(t);
}
return list;
} catch (SQLException throwables) {
throwables.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (NoSuchFieldException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connect,ps,resultSet);
}
return null;
}
@Test
public void testQuery4(){
String sql1 = "select account,password,birthday,phone,sexual from user where id <= ?";
List<UserInfo> list = difTableQueryForList(UserInfo.class, sql1, 3);
list.forEach(System.out::println);
}
//结果
UserInfo{account='张三', password='123456', birthday=2021-10-22, phone='13901115076', sexual='male'}
UserInfo{account='李四', password='654321', birthday=2021-10-23, phone='15388888765', sexual='male'}
UserInfo{account='王五', password='666666', birthday=2021-10-13, phone='18337188788', sexual='female'}
这基本就是最通用的查询操作了,后期可以将其封装于工具类中,提升工作效率!!!