抽取JDBCUtils工具類簡化開發(fā)
package utils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* JDBCUtils工具類
* @author me
*
*/
public class JDBCUtils {
//構(gòu)造方法私有
private JDBCUtils() {}
/**
* 注冊驅(qū)動
*/
public static void loadDriver() {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
* 獲取數(shù)據(jù)庫鏈接對象
* @return 鏈接對象
*/
public static Connection getConnection() {
try {
return DriverManager.getConnection("jdbc:mysql:///testjdbc", "root", "123");
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/**
* 釋放資源
* @param conn數(shù)據(jù)庫鏈接對象
* @param stat執(zhí)行SQL語句的對象
* @param rs結(jié)果集對象
*/
public static void release(Connection conn, Statement stat, ResultSet rs) {
try {
if(rs != null) {
rs.close();
rs = null;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if(stat != null) {
stat.close();
stat = null;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if(conn != null) {
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
/**
* 釋放資源
* @param conn數(shù)據(jù)庫鏈接對象
* @param stat執(zhí)行SQL語句的對象
*/
public static void release(Connection conn, Statement stat) {
try {
if(stat != null) {
stat.close();
stat = null;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if(conn != null) {
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
通過JDBCUtils進(jìn)行增刪改查：
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.junit.Test;
import utils.JDBCUtils;
public class JDBC_Utils_CURD {
/**
* 增加
*/
@Test
public void method1() {
JDBCUtils.loadDriver();
Connection conn = null;
Statement stat = null;
try {
conn = JDBCUtils.getConnection();
stat = conn.createStatement();
String sql = "insert into student values(null, 'zhangsan1')";
int r = stat.executeUpdate(sql);
if(r > 0) {
System.out.println("添加成功");
System.out.println(r + "行受到影響");
} else {
System.out.println("添加失敗");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.release(conn, stat);
}
}
/**
* 刪除
*/
@Test
public void method2() {
JDBCUtils.loadDriver();
Connection conn = null;
Statement stat = null;
try {
conn = JDBCUtils.getConnection();
stat = conn.createStatement();
String sql = "delete from student where name = 'zhangsan'";
int r = stat.executeUpdate(sql);
if(r > 0) {
System.out.println("刪除成功");
System.out.println(r + "行受到影響");
} else {
System.out.println("刪除失敗");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 修改
*/
@Test
public void method3() {
JDBCUtils.loadDriver();
Connection conn = null;
Statement stat = null;
try {
conn = JDBCUtils.getConnection();
stat = conn.createStatement();
String sql = "update student set name = 'zhangsan' where name = 'zhangsan1'";
int r = stat.executeUpdate(sql);
if(r > 0) {
System.out.println("修改成功");
System.out.println(r + "行受到影響");
} else {
System.out.println("修改失敗");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.release(conn, stat);
}
}
/**
* 查詢
*/
@Test
public void method4() {
JDBCUtils.loadDriver();
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
stat = conn.createStatement();
String sql = "select * from student";
rs = stat.executeQuery(sql);
while(rs.next()) {
System.out.println(rs.getInt("id") + "---" + rs.getString("name"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.release(conn, stat, rs);
}
}
}
讀取配置文件版JDBCUtils2
config.properties:
driverName = com.mysql.jdbc.Driver
#url的格式：jdbc:mysql://ip:port/數(shù)據(jù)庫名
url = jdbc:mysql:///testjdbc
username = root
password = 123
package utils;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/**
* JDBCUtils讀取配置文件版
* @author me
*/
public class JDBCUtils2 {
private static String driverName;
private static String url;
private static String username;
private static String password;
/**
* 構(gòu)造函數(shù)私有化
*/
private JDBCUtils2() {}
/**
* 讀取配置文件，然后將值賦給成員變量
*/
public static void readConfig() {
Properties pp = new Properties();
try {
pp.load(new FileInputStream("src/config.properties"));
driverName = pp.getProperty("driverName");
url = pp.getProperty("url");
username = pp.getProperty("username");
password = pp.getProperty("password");
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 注冊驅(qū)動
*/
static {
readConfig();
try {
Class.forName(driverName);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
* 獲取鏈接對象
* @return Connection
*/
public static Connection getConnection() {
try {
return DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/**
* 釋放資源
* @param conn
* @param stat
* @param rs
*/
public static void release(Connection conn, Statement stat, ResultSet rs) {
try {
if(rs != null) {
rs.close();
rs = null;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if(stat != null) {
stat.close();
stat = null;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if(conn != null) {
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
/**
* 釋放資源
* @param conn
* @param stat
*/
public static void release(Connection conn, Statement stat) {
try {
if(stat != null) {
stat.close();
stat = null;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if(conn != null) {
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
使用JDBCUtils2進(jìn)行增刪改查
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.junit.Test;
import utils.JDBCUtils2;
/**
* 使用讀取配置文件的JDBCUtils省去了JDBCUtils.loadDrever();
* 讀取配置文件的信息放入了靜態(tài)代碼塊中
* @author me
*/
public class JDBC_Utils2_CURD {
/**
* 增加
*/
@Test
public void method1() {
Connection conn = null;
Statement stat = null;
try {
conn = JDBCUtils2.getConnection();
stat = conn.createStatement();
String sql = "insert into student values(null, 'zhangsansan')";
int r = stat.executeUpdate(sql);
if(r > 0) {
System.out.println("插入成功");
System.out.println(r + "條數(shù)據(jù)受到影響");
} else {
System.out.println("插入失敗");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils2.release(conn, stat);
}
}
/**
* 刪除
*/
@Test
public void method2() {
Connection conn = null;
Statement stat = null;
try {
conn = JDBCUtils2.getConnection();
stat = conn.createStatement();
String sql = "delete from student where name = 'zhangsansan'";
int r = stat.executeUpdate(sql);
if(r > 0) {
System.out.println("刪除成功");
System.out.println(r + "條數(shù)據(jù)受到影響");
} else {
System.out.println("刪除失敗");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils2.release(conn, stat);
}
}
/**
* 修改
*/
@Test
public void method3() {
Connection conn = null;
Statement stat = null;
try {
conn = JDBCUtils2.getConnection();
stat = conn.createStatement();
String sql = "update student set name = 'zhangsansan' where name = 'zhangsan'";
int r = stat.executeUpdate(sql);
if(r > 0) {
System.out.println("修改成功");
System.out.println(r + "條數(shù)據(jù)受到影響");
} else {
System.out.println("修改失敗");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils2.release(conn, stat);
}
}
/**
* 查詢
*/
@Test
public void method4() {
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
try {
conn = JDBCUtils2.getConnection();
stat = conn.createStatement();
String sql = "select * from student";
rs = stat.executeQuery(sql);
while(rs.next()) {
System.out.println(rs.getInt("id") + "---" + rs.getString("name"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils2.release(conn, stat, rs);
}
}
}
