package com.mozq.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* JDBCUtils获取连接和释放资源的工具类
*
* @author jie
*
*/
public class JDBCUtils_V1 {
/**
* 获取连接
*
* @throws ClassNotFoundException
*/
public static Connection getConnection() {
// 1.准备配置信息
String dirverClass = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/jdbc_day1";
String user = "root";
String password = "root";
// 2.加载驱动,获得链接
Connection connection = null;
try {
Class.forName(dirverClass);
connection = DriverManager.getConnection(url, user, password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
/**
* 释放资源,注意资源的释放顺序
*/
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 com.mozq.jdbc;
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;
/**
* 使用Properties类,从配置文件获取配置信息。实现JDBCUtils工具类
*
* @author jie
*
*/
public class JDBCUtils_V2 {
// 1.准备配置信息
public static String dirverClass;
public static String url;
public static String user;
public static String password;
static {
ClassLoader classLoader = JDBCUtils_V2.class.getClassLoader();
InputStream inStream = classLoader.getResourceAsStream("db.properties");
Properties properties = new Properties();
try {
properties.load(inStream);
dirverClass = properties.getProperty("dirverClass");
url = properties.getProperty("url");
user = properties.getProperty("user");
password = properties.getProperty("password");
Class.forName(dirverClass);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
* 获取连接
*
* @throws ClassNotFoundException
*/
public static Connection getConnection() {
// 2.加载驱动,获得链接
Connection connection = null;
try {
connection = DriverManager.getConnection(url, user, password);
}catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
/**
* 释放资源,注意资源的释放顺序
*/
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 com.mozq.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ResourceBundle;
/**
* 使用ResourceBundle类,从配置文件获取配置信息。实现JDBCUtils工具类
*
* @author jie
*
*/
public class JDBCUtils_V3 {
// 1.准备配置信息
public static String dirverClass;
public static String url;
public static String user;
public static String password;
static {
ResourceBundle bundle = ResourceBundle.getBundle("db");
try {
dirverClass = bundle.getString("dirverClass");
url = bundle.getString("url");
user = bundle.getString("user");
password = bundle.getString("password");
Class.forName(dirverClass);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
* 获取连接
*
* @throws ClassNotFoundException
*/
public static Connection getConnection() {
// 2.加载驱动,获得链接
Connection connection = null;
try {
connection = DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
/**
* 释放资源,注意资源的释放顺序
*/
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();
}
}
}
}
#配置文件,位于classpath:db.properties
dirverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbc_day1
user=root
password=root
package com.mozq.jdbc.test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.junit.Test;
import com.mozq.jdbc.JDBCUtils_V2;
public class JDBCUtils_V2Test {
/**
* 测试try catch finally 代码执行顺序
*/
@Test
public void tryCatchFinally() {
System.out.println(1);
try {
System.out.println(2);
//int i = 1 / 0;
System.out.println(3);
} catch (Exception e) {
System.out.println(4);
} finally {
System.out.println(5);
}
System.out.println(6);
}
/**
* 插入
*/
@Test
public void insert() {
//准备参数
String user = "刘备";
String password = "123";
Connection conn = null;
PreparedStatement pstmt = null;
try {
//获取链接
conn = JDBCUtils_V2.getConnection();
//获取语句执行对象
String sql = "insert into t_user (name, password) values(?, ?)";
pstmt = conn.prepareStatement(sql );
pstmt.setString(1, user);
pstmt.setString(2, password);
//结果处理
int row = pstmt.executeUpdate();
if(row > 0) {
System.out.println("插入用户成功");
}else {
System.out.println("插入用户失败");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils_V2.release(conn, pstmt, null);
}
}
/**
* 查找
*/
@Test
public void find() {
//准备参数
String user = "刘备";
String password = "123";
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
//获取链接
conn = JDBCUtils_V2.getConnection();
//获取语句执行对象
String sql = "select * from t_user where name=? and password=?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, user);
pstmt.setString(2, password);
//结果处理
rs = pstmt.executeQuery();
if(rs.next()) {
System.out.println("返回用户对象");
}else {
System.out.println("用户或密码错误");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils_V2.release(conn, pstmt, null);
}
}
/**
* 修改
*/
@Test
public void modify() {
//准备参数
String user = "京东";
Connection conn = null;
PreparedStatement pstmt = null;
try {
//获取链接
conn = JDBCUtils_V2.getConnection();
//获取语句执行对象
String sql = "update t_user set name=? where id=?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, user);
//pstmt.setInt(2, 1);
pstmt.setString(2, "hehe");
//结果处理
int row = pstmt.executeUpdate();
if(row > 0) {
System.out.println("修改成功");
}else {
System.out.println("修改失败");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils_V2.release(conn, pstmt, null);
}
}
/**
* 修改
*/
@Test
public void delete() {
//准备参数
int id = 1;
Connection conn = null;
PreparedStatement pstmt = null;
try {
//获取链接
conn = JDBCUtils_V2.getConnection();
//获取语句执行对象
String sql = "delete from t_user where id=?";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, id);
//结果处理
int row = pstmt.executeUpdate();
if(row > 0) {
System.out.println("删除成功");
}else {
System.out.println("删除失败");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils_V2.release(conn, pstmt, null);
}
}
}