现在主要是学习源代码,其实后面都是可以直接实现的。
1、针对特定表的一条数据查询
package demo02;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import org.junit.jupiter.api.Test;
import utils.Bean;
import utils.JDBCUtils;
public class PreparedStatementQuary {
/***
* 针对beauty表的某一个查询操作
*/
@Test
public void testQuary(){
Connection conn = null;
PreparedStatement ps = null;
ResultSet resultSet = null;
try {
conn = JDBCUtils.getConnection();
//sql语句只能指定,因此只能针对单一操作
String sql = "select name,borndate,phone from beauty where id = ?";
ps = conn.prepareStatement(sql);
ps.setObject(1, 1);
//执行,并返回结果集
resultSet = ps.executeQuery();
//处理结果集
if(resultSet.next()) { //判断结果集的下一条是否有数据
//获取当前数据的各个字段值
Object name = resultSet.getObject(1);
Object borndate = resultSet.getObject(2);
Object phone = resultSet.getObject(3);
//将数据封装为一个对象
Bean bean = new Bean(name,borndate,phone);
System.out.println(bean);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
//关闭资源
JDBCUtils.closeResourse(conn, ps, resultSet);
}
}
}
对应的Bean类(其实应起名为Beauty类更为合理,一个表对应一个类。当时初学没有意识到这个问题):
package utils;
public class Bean {
private Object name;
private Object borndate;
private Object phone;
public Bean() {
super();
}
public Bean(Object name, Object borndate, Object phone) {
super();
this.name = name;
this.borndate = borndate;
this.phone = phone;
}
public Object getName() {
return name;
}
public void setName(Object name) {
this.name = name;
}
public Object getBorndate() {
return borndate;
}
public void setBorndate(Object borndate) {
this.borndate = borndate;
}
public Object getPhone() {
return phone;
}
public void setPhone(Object phone) {
this.phone = phone;
}
@Override
public String toString() {
return "Bean [name=" + name + ", borndate=" + borndate + ", phone=" + phone + "]";
}
}
2、针对特定表的通用查询操作,,只能返回一条数据
package demo02;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import org.junit.jupiter.api.Test;
import utils.Bean;
import utils.JDBCUtils;
public class PreparedStatementQuary_2 {
@Test
public void test() {
String sql = "select name,borndate from beauty where id=?";
Bean bean = quary(sql,13);
System.out.println(bean);
}
/***
* 针对beauty表的通用查询操作
* @param sql
* @param args
* @return
*/
public Bean quary(String sql,Object ...args){
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
ps = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i+1, args[i]);
}
rs = ps.executeQuery();
//获取结果集的元数据
ResultSetMetaData rsmd = rs.getMetaData();
//通过ResultSetMetaData获取元数据中的列数,以列数控制下面的for循环
int columnCount = rsmd.getColumnCount();
if(rs.next()) {
Bean bean = new Bean();
//处理结果集一行数据中的每一个列
for (int i = 0; i < columnCount; i++) {
//获取填充占位符的元素
Object columnValue = rs.getObject(i+1);
//获取每个列的列名
String columnName = rsmd.getColumnName(i+1);
//给Bean对象指定的columnName,赋值为columnValue,通过反射
Field field = Bean.class.getDeclaredField(columnName);
//防止私有
field.setAccessible(true);
field.set(bean, columnValue);
}
return bean;
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.closeResourse(conn, ps, rs);
}
//没成功就返回null
return null;
}
}
3、针对所有表的通用查询操作,只能返回一条数据
package demo02;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import org.junit.jupiter.api.Test;
import utils.Bean;
import utils.Boys;
import utils.JDBCUtils;
public class PreparedStatementQuary_3 {
@Test
public void test() {
String sql = "select name,phone from beauty where id=?";
Bean bean = quary(Bean.class, sql, 13);
System.out.println(bean);
String sql_2 = "select boyName,userCP from boys where id=?";
Boys boys = quary(Boys.class, sql_2, 1);
System.out.println(boys);
}
/***
* 针对所有表的preparedStatement通用查询,返回表中一条记录
* @param clazz
* @param sql
* @param args
* @return
*/
public <T> T quary(Class<T> clazz, String sql, Object... args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
ps = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
rs = ps.executeQuery();
// 获取结果集的元数据
ResultSetMetaData rsmd = rs.getMetaData();
// 通过ResultSetMetaData获取元数据中的列数,以列数控制下面的for循环
int columnCount = rsmd.getColumnCount();
if (rs.next()) {
T t = clazz.newInstance();
// 处理结果集一行数据中的每一个列
for (int i = 0; i < columnCount; i++) {
// 获取填充占位符的元素
Object columnValue = rs.getObject(i + 1);
// 获取每个列的列名
String columnName = rsmd.getColumnName(i + 1);
// 给t对象指定的columnName,赋值为columnValue,通过反射
Field field = clazz.getDeclaredField(columnName);
// 防止私有
field.setAccessible(true);
field.set(t, columnValue);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResourse(conn, ps, rs);
}
// 没成功就返回null
return null;
}
}
4,针对所有表的通用查询操作,返回任意条数据
package demo02;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import org.junit.jupiter.api.Test;
import utils.Boys;
import utils.JDBCUtils;
public class PreparedStatementQuary_4 {
@Test
public void test() {
String sql = "select boyName,userCP from boys where id<?";
ArrayList<Boys> boys = quary(Boys.class, sql, 4);
boys.forEach(System.out::println);
}
/***
* 针对所有表的通用查询操作,查询多条数据
* @param clazz
* @param sql
* @param args
* @return
*/
public <T> ArrayList<T> quary(Class<T> clazz, String sql, Object ...args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
ps = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
rs = ps.executeQuery();
// 获取结果集的元数据
ResultSetMetaData rsmd = rs.getMetaData();
// 通过ResultSetMetaData获取元数据中的列数,以列数控制下面的for循环
int columnCount = rsmd.getColumnCount();
ArrayList<T> arrayList = new ArrayList<T>();
while (rs.next()) {
T t = clazz.newInstance();
// 处理结果集一行数据中的每一个列
for (int i = 0; i < columnCount; i++) {
// 获取填充占位符的元素
Object columnValue = rs.getObject(i + 1);
// 获取每个列的列名
String columnName = rsmd.getColumnName(i + 1);
// 给t对象指定的columnName,赋值为columnValue,通过反射
Field field = clazz.getDeclaredField(columnName);
// 防止私有
field.setAccessible(true);
field.set(t, columnValue);
}
arrayList.add(t);
}
return arrayList;
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResourse(conn, ps, rs);
}
// 没成功就返回null
return null;
}
}