import java.sql.*;
/**
* 提供获取链接和释放资源的方法一
*/
public class JDBCUtils_V1 {
/**
* 获取连接方法
* @return
* @throws ClassNotFoundException
*/
public static Connection getConnection() {
Connection conn=null;
try {
//注册驱动
Class.forName("com.mysql.jdbc.Driver");
//获取连接
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","*****"); //数据库密码
} catch (ClassNotFoundException | SQLException 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();
}
}
}
}
import java.sql.*;
import java.util.ResourceBundle;
/**
* 提供获取链接和释放资源的方法二,通过ResourceBundle对象获取配置文件properties,
*/
public class JDBCUtils_V2 {
private static String driver;
private static String url;
private static String username;
private static String password;
/**
* 静态代码块加载配置文件信息
*/
static{
ResourceBundle bundle = ResourceBundle.getBundle("Jdbc");
driver = bundle.getString("driver");
url = bundle.getString("url");
username = bundle.getString("username");
password = bundle.getString("password");
}
/**
* 获取连接方法
* @return
* @throws ClassNotFoundException
*/
public static Connection getConnection() {
Connection conn=null;
try {
//注册驱动
Class.forName(driver);
//获取连接
conn = DriverManager.getConnection(url,username,password);
} catch (ClassNotFoundException | SQLException 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();
}
}
}
}
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
import java.util.ResourceBundle;
/**
* 提供获取链接和释放资源的方法三,用类加载器获得一个输入流(properties文件)
*/
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.getSystemResourceAsStream("Jdbc.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
* @throws ClassNotFoundException
*/
public static Connection getConnection() {
Connection conn=null;
try {
//注册驱动
Class.forName(driver);
//获取连接
conn = DriverManager.getConnection(url,username,password);
} catch (ClassNotFoundException | SQLException 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();
}
}
}
}
package WEB05_JDBC;
import com.mysql.jdbc.ResultSet;
import org.junit.Test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/**
* 测试工具类,通过三种方法完成数据库的连接,执行增删改查操作
*/
public class TestUtils {
/**
* 根据id更新用户信息
*/
@Test
public void testUpdateById(){
Connection conn=null;
PreparedStatement pstmt=null;
try {
//获取连接
conn= JDBCUtils_V3.getConnection();
//编写sql语句
String sql="update student set name=? where stuID=?";
//获取执行sql语句对象
pstmt=conn.prepareStatement(sql);
//4、设置参数(paramerterIndex=第几个问号)
pstmt.setString(1,"FF");
pstmt.setInt(2,5);
//执行插入操作
int row = pstmt.executeUpdate();
if (row>0){
System.out.println("更新成功");
}else{
System.out.println("更新失败");
}
} catch (Exception e) {
throw new RuntimeException(e);
}finally {
//释放资源
JDBCUtils_V3.release(conn,pstmt,null);
}
}
/**
* 根据id删除信息方法
*/
@Test
public void testDeleteById(){
Connection conn=null;
PreparedStatement pstmt=null;
try {
//获取连接
conn= JDBCUtils_V3.getConnection();
//编写sql语句
String sql="delete from student where stuID=?";
//获取执行sql语句对象
pstmt=conn.prepareStatement(sql);
//4、设置参数(paramerterIndex=第几个问号)
pstmt.setInt(1,6);
//执行插入操作
int row = pstmt.executeUpdate();
if (row>0){
System.out.println("删除成功");
}else{
System.out.println("删除失败");
}
} catch (Exception e) {
throw new RuntimeException(e);
}finally {
//释放资源
JDBCUtils_V3.release(conn,pstmt,null);
}
}
/**
* 添加用户信息
*/
@Test
public void testAdd(){
Connection conn=null;
PreparedStatement pstmt=null;
try {
//获取连接
conn= JDBCUtils_V2.getConnection();
//编写sql语句
String sql="insert into student values(?,?,?,?,?,?) ";
//获取执行sql语句对象
pstmt=conn.prepareStatement(sql);
//4、设置参数(paramerterIndex=第几个问号)
pstmt.setInt(1,6);
pstmt.setString(2,"E");
pstmt.setInt(3,17);
pstmt.setString(4,"女");
pstmt.setInt(5,136);
pstmt.setString(6, "大学");
//执行插入操作
int row = pstmt.executeUpdate();
if (row>0){
System.out.println("添加成功");
}else{
System.out.println("插入失败");
}
} catch (Exception e) {
throw new RuntimeException(e);
}finally {
//释放资源
JDBCUtils_V2.release(conn,pstmt,null);
}
}
/**
* 根据用户id查询信息
*/
@Test
public void testFindUserById() {
Connection conn=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
try {
conn= JDBCUtils_V1.getConnection();
//编写sql语句
String sql="select * from student where StuID=?";
//获取执行sql语句对象
pstmt=conn.prepareStatement(sql);
//4、设置参数
pstmt.setInt(1,2);
//5、执行查询操作
rs= (ResultSet) pstmt.executeQuery();
//6、处理结果集
while(rs.next()){
System.out.println(rs.getString(2)+"----"+rs.getString("sex"));
}
//释放资源放在此处是不可以滴
} catch (SQLException e) {
e.printStackTrace();
}finally {
//7、释放资源
JDBCUtils_V1.release(conn,pstmt,rs);
}
}
}
配置文件 :Jdbc.properties
driver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8 username=root password=********(你自己数据库的密码)