使用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("登录成功！"); } } }

效果截图