JDBC 配置工具类，以及增删改查(一)
序一：先导入mysql驱动jar包，创建数据库jdbc,建表user（字段：id，name）
1.创建一个jdbc.properties文件
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbc
user=root
password=12345
2.创建jdbcUtils工具类
package com.xdl.jdbc.hello;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ResourceBundle;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;
public class jdbcUtils {
static final String DRIVERCLASS;
static final String URL;
static final String USER;
static final String PASSWORD;
static {
//获取ResourceBunle
ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
//获取指定的内容
DRIVERCLASS=bundle.getString("driverClass");
URL=bundle.getString("url");
USER=bundle.getString("user");
PASSWORD=bundle.getString("password");
}
static {
//注册驱动
try {
Class.forName(DRIVERCLASS);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws Exception {
//获取连接
return (Connection) DriverManager.getConnection(URL,USER,PASSWORD);
}
/**
* 释放资源
* @param conn 连接
* @param st 语句执行者
* @param rs 结果集
*/
public static void closeResource(Connection conn,Statement st,ResultSet rs) {
closeResultSet(rs);
closeStatement(st);
closeConn(conn);
}
//释放连接
public static void closeConn(Connection conn) {
if(conn!=null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn=null;
}
}
//释放语句执行者
public static void closeStatement(Statement st) {
if(st!=null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
st=null;
}
}
//释放結果集
public static void closeResultSet(ResultSet rs) {
if(rs!=null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs=null;
}
}
}
3.创建测试类
package com.xdl.jdbc.hello;
import java.sql.DriverManager;
import java.sql.ResultSet;
import org.junit.Test;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
public class hello {
@Test
public void insert() throws Exception {//增加操作
Connection conn=null;
ResultSet rs=null;
PreparedStatement st=null;
try {
//获取连接
conn=jdbcUtils.getConnection();
//编写sql
String sql="insert into user(id,name) values(?,?)";
//获取语句执行者
st= (PreparedStatement)conn.prepareStatement(sql);
//设置参数
st.setString(1, "008");
st.setString(2, "测试");
//执行sql
int i=st.executeUpdate();
System.out.println(i);
//处理结果
if(i==1) {
System.out.println("执行成功");
}else {
System.out.println("执行失败");
}
} catch (Exception e) {
e.printStackTrace();
}finally {
jdbcUtils.closeResource(conn, st, rs);
}
}
@Test
public void update() {
Connection conn=null;
PreparedStatement st=null;
ResultSet rs=null;
try {
conn=jdbcUtils.getConnection();
String sql="update user set name= ? where id= ?";
st=(PreparedStatement) conn.prepareStatement(sql);
st.setString(1, "手机");//赋给第一个问号name的值
st.setString(2, "003");//赋给第二个问号id的值
int i=st.executeUpdate();
if(i==1) {
System.out.println("执行成功");
}else {
System.out.println("执行失败");
}
} catch (Exception e) {
e.printStackTrace();
}finally{
//释放资源
jdbcUtils.closeResource(conn, st, rs);
}
}
@Test
public void delete() {//删除
Connection conn=null;
PreparedStatement st=null;
ResultSet rs=null;
try {
conn=jdbcUtils.getConnection();
String sql="delete from user where id= ? ";
st=(PreparedStatement) conn.prepareStatement(sql);
st.setString(1, "003");//赋给id的值
int i=st.executeUpdate();
if(i==1) {
System.out.println("执行成功");
}else {
System.out.println("执行失败");
}
} catch (Exception e) {
e.printStackTrace();
}finally{
//释放资源
jdbcUtils.closeResource(conn, st, rs);
}
}
