使用JDBC连接数据库的简单操作和查询测试

jdbc.properties文件

driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql:///test
user=root
password=root

JdbcUtils.class工具类，包含获取连接和关闭连接的方法

package com.studyhub.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtils {


    /**
     * 获取连接
     * @return 返回Connection对象
     */
    public static Connection getConnection() {

        Connection connection = null;

        try {
        	//通过反射获取流
            InputStream inputStream = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
			//加载流
            Properties properties = new Properties();
            properties.load(inputStream);
			//获取jdbc.properties文件的属性
            String driverClass = properties.getProperty("driverClass");
            String url = properties.getProperty("url");
            String user = properties.getProperty("user");
            String password = properties.getProperty("password");
			//通过反射加载驱动
            Class.forName(driverClass);
            //获取连接
            connection = DriverManager.getConnection(url, user, password);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
		//返回连接 失败返回null
        return connection;
    }

    /**
     * 资源的关闭
     * @param conn
     * @param ps
     * @param rs
     */
    public static void close(Connection conn, PreparedStatement ps, ResultSet rs) {
        try {
            if(conn != null) {
                conn.close();
            }
            if(ps != null) {
                ps.close();
            }
            if(rs != null) {
                rs.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }
}

JdbcUtils的另一种写法（推荐）

package com.studyhub.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtils {
	
    private static String password = null;
    private static String user = null;
    private static String url = null;
    private static String driverClass = null;

    //使用静态代码块加载jdbc.properties中的资源，只用加载一次
    //这样每次调用获取连接方法只需用做一件事，提高了效率
     static {
        try {
            InputStream inputStream = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");

            Properties properties = new Properties();
            properties.load(inputStream);

            driverClass = properties.getProperty("driverClass");
            url = properties.getProperty("url");
            user = properties.getProperty("user");
            password = properties.getProperty("password");

            Class.forName(driverClass);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection() {
         Connection connection = null;
        try {
            System.out.println("获取连接");
            connection = DriverManager.getConnection(url, user, password);
        } catch (SQLException throwables) {
            System.out.println("失败");
            throwables.printStackTrace();
        }

        return connection;
    }

    /**
     * 资源的关闭
     * @param conn
     * @param ps
     * @param rs
     */
    public static void close(Connection conn, PreparedStatement ps, ResultSet rs) {
        try {
            if(conn != null) {
                conn.close();
            }
            if(ps != null) {
                ps.close();
            }
            if(rs != null) {
                rs.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

User.class 表示所要查询的实体类

package com.studyhub.pojo;

public class User {
    private Integer id;
    private String username;
    private String password;
    private String email;

    public User(Integer id, String username, String password, String email) {
        this.id = id;
        this.username = username;
        this.password = password;
        this.email = email;
    }

    public User() {
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", email='" + email + '\'' +
                '}';
    }
}

BaseDao.class此类中有一个查询方法

package com.studyhub.dao;

import com.studyhub.utils.JdbcUtils;

import java.lang.reflect.Field;
import java.sql.*;


public abstract class BaseDao {
    //从数据库中查询一条记录
    //为了简化测试，将此方法设置为静态方法，实际不需要设为静态方法

    /**
     * 从数据库中查询一条记录
     * 为了简化测试，将此方法设置为静态方法，实际不需要设为静态方法
     * @param type 要查询的类型
     * @param sql   sql语句
     * @param args  可变长参数
     * @param <T>
     * @return
     */
    public static <T> T queryOne(Class<T> type, String sql, Object... args) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            //获取连接对象
            connection = JdbcUtils.getConnection();
            //预编译sql语句
            preparedStatement = connection.prepareStatement(sql);
            //填充占位符
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i+1, args[i]);
            }
            //执行查询
            preparedStatement.executeQuery();
            //获取结果集对象
            resultSet = preparedStatement.getResultSet();
            //获取结果集元数据对象
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            //获取所查询记录的列数
            int columnCount = resultSetMetaData.getColumnCount();
            if (resultSet.next()) {
                T t = type.newInstance();
                for (int i = 0; i < columnCount; i++) {
                    Object columnValue = resultSet.getObject(i + 1);
                    String columnLabel = resultSetMetaData.getColumnLabel(i + 1);
                    //通过反射设置该属性可以访问，并赋值
                    Field field = t.getClass().getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t, columnValue);
                }
                //查询成功返回该对象
                return t;
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        } finally {
            //关闭资源
            JdbcUtils.close(connection, preparedStatement,resultSet);
        }
        //查询失败返回null
        return null;
    }
}

测试方法类：ClientAndServer.class

package com.studyhub.test;

import com.studyhub.dao.BaseDao;
import com.studyhub.pojo.User;



import java.util.Scanner;

public class ClientAndServerTest extends BaseDao {



    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);


        System.out.println("欢迎登录！");
        System.out.println("请输入用户名：");
        String username = scanner.next();
        System.out.println("请输入密码：");
        String password = scanner.next();

        String sql = "select * from `t_user` where username=? and password=?";

        User loginUser = queryOne(User.class, sql, username, password);
        if (loginUser == null) {
            System.out.println("用户名或密码错误，请重启客户端！");
        }
        else {
            System.out.println(loginUser);
            System.out.println("登录成功！");
        }
    }


}

效果截图