注:此博客不再更新,所有最新文章将发表在个人独立博客limengting.site。分享技术,记录生活,欢迎大家关注
目录结构:
**Version 1:**只抽象出方法封装成一个类,但是仍然是硬编码,数据库的四个参数直接写在类中
JDBCUtils_V1.java中的内容:
package sunnie;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* 提供获取连接和释放资源的 方法
* @version V1.0
*/
public class JDBCUtils_V1 {
/**
* 获取连接方法
*
* @return
*/
public static Connection getConnection() {
Connection conn = null;
try {
//Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/sunnie?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC&useSSL=true", "root", "password");
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
public static void release(Connection conn, PreparedStatement pstmt, ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
**Version 2:**把数据库的四个参数写在.properties文件中国,使用ResourceBundle对象的getBundle()方法加载properties文件
db.properties中的内容:
#driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/sunnie?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC&useSSL=true
username=root
password=password
JDBCUtils_V2.java中的内容:
package sunnie;
import java.sql.*;
import java.util.ResourceBundle;
/**
* 提供获取连接和释放资源的方法
*
* @version V2.0
*/
public class JDBCUtils_V2 {
//private static String driver;
private static String url;
private static String username;
private static String password;
/**
* 静态代码块加载配置文件信息
*/
static {
//getBundle中写的是不加.properties后缀的文件名
ResourceBundle bundle = ResourceBundle.getBundle("db");
//driver = bundle.getString("driver");
url = bundle.getString("url");
username = bundle.getString("username");
password = bundle.getString("password");
}
/**
* 获取连接方法
*
* @return
*/
public static Connection getConnection() {
Connection conn = null;
try {
//Class.forName(driver);
conn = DriverManager.getConnection(url, username, password);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
public static void release(Connection conn, PreparedStatement pstmt, ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) {
testAdd();
}
public static void testAdd() {
Connection conn = null;
PreparedStatement pstmt = null;
try {
// 1.获取连接
conn = JDBCUtils_V2.getConnection();
// 2.编写sql语句
String sql = "insert into user values(null,?,?)";
// 3.获取执行sql语句对象
pstmt = conn.prepareStatement(sql);
// 4.设置参数
pstmt.setString(1, "sunnie");
pstmt.setString(2, "limengting");
// 5.执行插入操作
int row = pstmt.executeUpdate();
if (row > 0) {
System.out.println("添加成功!");
} else {
System.out.println("添加失败!");
}
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
// 6.释放资源
JDBCUtils_V2.release(conn, pstmt, null);
}
}
}
**Version 3:**使用类加载器(ClassLoader)加载资源把配置文件以流的方式获取,再使用Properties对象的load方法加载指定的流
InputStream is = xxx.class.getClassLoader().getResourceAsStream("xx.properties");
Properties props = new Properties();
props.load(is);
//获取参数
url = props.getProperty("jdbc.url");
...
JDBCUtils_V3.java中的内容:
package sunnie;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import java.util.ResourceBundle;
/**
* 提供获取连接和释放资源的 方法
* @version V3.0
*/
public class JDBCUtils_V3 {
private static String driver;
private static String url;
private static String username;
private static String password;
/**
* 静态代码块加载配置文件信息
*/
static {
try {
// 1.通过当前类获取类加载器
ClassLoader classLoader = JDBCUtils_V3.class.getClassLoader();
// 2.通过类加载器的方法获得一个输入流
InputStream is = classLoader.getResourceAsStream("db.properties");
// 3.创建一个properties对象
Properties props = new Properties();
// 4.加载输入流
props.load(is);
// 5.获取相关参数的值
driver = props.getProperty("driver");
url = props.getProperty("url");
username = props.getProperty("username");
password = props.getProperty("password");
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 获取连接方法
*
* @return
*/
public static Connection getConnection() {
Connection conn = null;
try {
//Class.forName(driver);
conn = DriverManager.getConnection(url, username, password);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
public static void release(Connection conn, PreparedStatement pstmt, ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
从配置文件中读取参数需要注意的问题:
.properties配置文件放在\src\main\resources\db.properties位置,直接写"xx.properties"
junit测试:
package sunnietest;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.junit.Test;
import sunnie.JDBCUtils_V1;
import sunnie.JDBCUtils_V2;
import sunnie.JDBCUtils_V3;
/**
* 测试工具类
*/
public class TestUtils {
/**
* 根据id更新用户信息方法
* 测试JDBCUtils_V3
*/
@Test
public void testUpdateById() {
Connection conn = null;
PreparedStatement pstmt = null;
try {
// 1.获取连接
conn = JDBCUtils_V3.getConnection();
// 2.编写sql语句
String sql = "update user set password=? where id=?";
// 3.获取执行sql语句对象
pstmt = conn.prepareStatement(sql);
// 4.设置参数
pstmt.setString(1, "666");
pstmt.setInt(2, 3);
// 5.执行更新操作
int row = pstmt.executeUpdate();
if (row > 0) {
System.out.println("更新成功!");
} else {
System.out.println("更新失败!");
}
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
// 6.释放资源
JDBCUtils_V3.release(conn, pstmt, null);
}
}
/**
* 根据id删除信息方法
* 测试JDBCUtils_V3
*/
@Test
public void testDeleteById() {
Connection conn = null;
PreparedStatement pstmt = null;
try {
// 1.获取连接
conn = JDBCUtils_V3.getConnection();
// 2.编写sql语句
String sql = "delete from user where id=?";
// 3.获取执行sql语句对象
pstmt = conn.prepareStatement(sql);
// 4.设置参数
pstmt.setInt(1, 4);
// 5.执行删除操作
int row = pstmt.executeUpdate();
if (row > 0) {
System.out.println("删除成功!");
} else {
System.out.println("删除失败!");
}
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
// 6.释放资源
JDBCUtils_V3.release(conn, pstmt, null);
}
}
/**
* 添加用户信息方法
* 测试JDBCUtils_V2
*/
@Test
public void testAdd() {
Connection conn = null;
PreparedStatement pstmt = null;
try {
// 1.获取连接
conn = JDBCUtils_V2.getConnection();
// 2.编写sql语句
String sql = "insert into user values(null,?,?)";
// 3.获取执行sql语句对象
pstmt = conn.prepareStatement(sql);
// 4.设置参数
pstmt.setString(1, "lisi");
pstmt.setString(2, "hehe");
// 5.执行插入操作
int row = pstmt.executeUpdate();
if (row > 0) {
System.out.println("添加成功!");
} else {
System.out.println("添加失败!");
}
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
// 6.释放资源
JDBCUtils_V2.release(conn, pstmt, null);
}
}
/**
* 根据id查询用户信息
* 测试JDBCUtils_V1
*/
@Test
public void testFindUserById() {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
// 1.获取连接
conn = JDBCUtils_V1.getConnection();
// 2.编写sql语句
String sql = "select * from user where id=?";
// 3.获取执行sql语句对象
pstmt = conn.prepareStatement(sql);
// 4.设置参数
pstmt.setInt(1, 2);
// 5.执行查询操作
rs = pstmt.executeQuery();
// 6.处理结果集
while (rs.next()) {
System.out.println(rs.getString(2) + "----" + rs.getString("password"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 7.释放资源
JDBCUtils_V1.release(conn, pstmt, rs);
}
}
}
pom.xml添加依赖:
<dependencies>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>6.0.6</version>
</dependency>
<!-- https://mvnrepository.com/artifact/junit/junit -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
</dependencies>