JDBC连接数据库实现增删改查之查询

一、JDBC连接数据库

JdbcUtils.java

public class JdbcUtils {
    public static Connection getConnection() throws IOException, ClassNotFoundException, SQLException {
        InputStream inputStream = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        properties.load(inputStream);
        String user= properties.getProperty("user");
        String password =  properties.getProperty("password");
        String url = properties.getProperty("url");
        String driverClass = properties.getProperty("driverClass");
        Class.forName(driverClass);
        Connection connection = DriverManager.getConnection(url,user,password);
        return connection;
    }
    //关闭资源
public static void closeResource(Connection connection, PreparedStatement ps, ResultSet rs){
        try {
            if (ps!=null) {
                ps.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            if (connection!=null) {
                connection.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            if (rs!=null){
                rs.close();
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }

jdbc.properties

user =root
password =123456
url =jdbc:mysql://localhost:3306/demo?useUnicode=true&&characterEncodeing=UTF-8&&useSSL=false&&serverTimezone=GMT
driverClass =com.mysql.cj.jdbc.Driver

二、javabean

Student.java

public class Student {
    private int Stuid;
    private String Stuname;
    private String Stusex;
    private int Stuage;

    public Student() { }
    public Student(int stuid, String stuname, String stusex, int stuage) {
        Stuid = stuid;
        Stuname = stuname;
        Stusex = stusex;
        Stuage = stuage;
    }
    public int getStuid() {
        return Stuid;
    }
    public void setStuid(int stuid) {
        Stuid = stuid;
    }
    public String getStuname() {
        return Stuname;
    }
    public void setStuname(String stuname) {
        Stuname = stuname;
    }
    public String getStusex() {
        return Stusex;
    }
    public void setStusex(String stusex) {
        Stusex = stusex;
    }
    public int getStuage() {
        return Stuage;
    }
    public void setStuage(int stuage) {
        Stuage = stuage;
    }
    @Override
    public String toString() {
        return "Student{" +
                "Stuid=" + Stuid +
                ", Stuname='" + Stuname + '\'' +
                ", Stusex='" + Stusex + '\'' +
                ", Stuage=" + Stuage +
                '}';
    }
}

三、

Query.java

    public <T> List<T> TestQuery(Class<T> clazz, String sql, Object ...args) {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ResultSetMetaData rsmd = null;
        try {
            connection = JdbcUtils.getConnection();
            ps = connection.prepareStatement(sql);
            for (int i =0;i< args.length;i++){
                ps.setObject(i+1,args[i]);
            }
            rs = ps.executeQuery();
            rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            ArrayList<T> list = new ArrayList<>();
            while (rs.next()){
                T t = clazz.newInstance();
                for (int i=0;i<columnCount;i++){
                    Object value = rs.getObject(i + 1);
                    String columnLabel = rsmd.getColumnLabel(i+1);
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t,value);
                }
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.closeResource(connection,ps,rs);
        }
        return null;
    }

四、数据库

五、测试
test.java

    @Test
    public void test()  {
        String sql = "select Stu_id Stuid,Stu_name Stuname,Stu_age Stuage,Stu_sex Stusex from Stu where Stu_id < ?";
        List<Student> list = TestQuery(Student.class, sql, 3);
        list.forEach(System.out::println);
    }

六、结果