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("登录成功!");
}
}
}
效果截图