jar
mysql-connector-java-5.1.39-bin.jar
db.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/database?useUnicode=true&characterEncoding=utf8
username=root
password=root
JDBCUtils_V3
package cn.itheima.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;
import java.util.ResourceBundle;
/**
* 提供获取连接和释放资源的 方法
*
* @author Never Say Never
* @date 2016年7月29日
* @version V1.0
*/
public class JDBCUtils_V3 {
private static String driver;
private static String url;
private static String username;
private static String password;
/**
* 静态代码块加载配置文件信息
*/
static {
try {
ClassLoader classLoader = JDBCUtils_V3.class.getClassLoader();
InputStream is = classLoader.getResourceAsStream("db.properties");
Properties props = new Properties();
props.load(is);
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();
}
}
}
}
TestUtils
package cn.itheima.jdbc.test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.junit.Test;
import cn.itheima.jdbc.JDBCUtils_V1;
import cn.itheima.jdbc.JDBCUtils_V2;
import cn.itheima.jdbc.JDBCUtils_V3;
/**
* 测试工具类
*
* @author Never Say Never
* @date 2016年7月29日
* @version V1.0
*/
public class TestUtils {
/**
* 根据id更新用户信息方法
*/
@Test
public void testUpdateById() {
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = JDBCUtils_V3.getConnection();
String sql = "update tbl_user set upassword=? where uid=?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "999");
pstmt.setInt(2, 3);
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();
String sql = "delete from tbl_user where uid=?";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, 4);
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();
String sql = "insert into tbl_user values(null,?,?)";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "lisi");
pstmt.setString(2, "hehe");
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();
String sql = "select * from tbl_user where uid=?";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, 2);
rs = pstmt.executeQuery();
while (rs.next()) {
System.out.println(rs.getString(2) + "----" + rs.getString("upassword"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils_V1.release(conn, pstmt, rs);
}
}
}