JDBCUtils 类设计:
1. 创建私有的属性*(连接数据库必要的四个变量):dreiver url user password
2. 将构造函数私有化
3.将注册驱动写入静态代码块
4.外界只能通过调用本工具的静态方法 getConnectio() 获取数据库连接,让调用者处理异常(抛出异常)
5. 资源关闭采用也采用静态方法调用
升级版:使用 properties 配置文件进行数据库连接配置见下文工具二
JDBCUtils 代码实现:
package com.test.utils; import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement; public final class JDBCUtils {
private static String driver="com.mysql.jdbc.Driver";
private static String url="jdbc:mysql://localhost:3306/store28";
private static String user="root";
private static String password="root1234"; private JDBCUtils(){} static {
/**
* 驱动注册
*/
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
throw new ExceptionInInitializerError(e);
} } /**
* 获取 Connetion
* @return
* @throws SQLException
*/
public static Connection getConnection() throws SQLException{
return DriverManager.getConnection(url, user, password);
} /**
* 释放资源
* @param conn
* @param st
* @param rs
*/
public static void colseResource(Connection conn,Statement st,ResultSet rs) {
closeResultSet(rs);
closeStatement(st);
closeConnection(conn);
} /**
* 释放连接 Connection
* @param conn
*/
public static void closeConnection(Connection conn) {
if(conn !=null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
//等待垃圾回收
conn = null;
} /**
* 释放语句执行者 Statement
* @param st
*/
public static void closeStatement(Statement st) {
if(st !=null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
//等待垃圾回收
st = null;
} /**
* 释放结果集 ResultSet
* @param rs
*/
public static void closeResultSet(ResultSet rs) {
if(rs !=null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
//等待垃圾回收
rs = null;
}
}
工具一测试:
package com.test.jdbcTest; import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException; import org.junit.Test; import com.test.utils.JDBCUtils; public class JdbcTest {
@Test
public void add() {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null; try {
// 获取连接
conn = JDBCUtils.getConnection(); // 编写sql
String sql = "insert into category values (?,?)"; // 创建语句执行者
st= conn.prepareStatement(sql); //设置参数
st.setString(1, "10");
st.setString(2, "测试目录"); // 执行sql
int i = st.executeUpdate(); if(i==1) {
System.out.println("数据添加成功!");
}else {
System.out.println("数据添加失败!");
} } catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils.colseResource(conn, st, rs);
} }
}
工具类二(升级版):
使用 eclipse 在 src 目录下创建 jdbc.properties 文件,写入健值对:
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/store28
user=root
password=root1234
package com.test.utils; import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle; public final class JDBCUtilsPlus {
private static final String DRIVER;
private static final String URL;
private static final String USER;
private static final String PASSWORD; private JDBCUtilsPlus(){} static {
ResourceBundle bundle = ResourceBundle.getBundle("jdbc"); DRIVER = bundle.getString("driver");
URL = bundle.getString("url");
USER = bundle.getString("user");
PASSWORD = bundle.getString("password"); /**
* 驱动注册
*/
try {
Class.forName(DRIVER);
} catch (ClassNotFoundException e) {
throw new ExceptionInInitializerError(e);
}
} /**
* 获取 Connetion
* @return
* @throws SQLException
*/
public static Connection getConnection() throws SQLException{
return DriverManager.getConnection(URL, USER, PASSWORD);
} /**
* 释放资源
* @param conn
* @param st
* @param rs
*/
public static void colseResource(Connection conn,Statement st,ResultSet rs) {
closeResultSet(rs);
closeStatement(st);
closeConnection(conn);
} /**
* 释放连接 Connection
* @param conn
*/
public static void closeConnection(Connection conn) {
if(conn !=null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
//等待垃圾回收
conn = null;
} /**
* 释放语句执行者 Statement
* @param st
*/
public static void closeStatement(Statement st) {
if(st !=null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
//等待垃圾回收
st = null;
} /**
* 释放结果集 ResultSet
* @param rs
*/
public static void closeResultSet(ResultSet rs) {
if(rs !=null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
//等待垃圾回收
rs = null;
}
}
工具测试:
package com.test.jdbcTest; import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException; import org.junit.Test; import com.test.utils.JDBCUtils;
import com.test.utils.JDBCUtilsPlus; public class JdbcTest {
@Test
public void select() {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null; try {
// 获取连接
conn = JDBCUtilsPlus.getConnection(); // 编写sql
String sql = "select * from category"; // 创建语句执行者
st= conn.prepareStatement(sql); rs = st.executeQuery(); while(rs.next()) {
System.out.println(rs.getString(1)+"..."+rs.getString(2));
} } catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtilsPlus.colseResource(conn, st, rs);
} }
}