Java基础笔记36-JDBC修改数据库
-
数据库是用于管理数据的,后台是用于根据业务做逻辑处理的,前台显示数据并进行交互
驱动包:DriverManager(驱动管理-注册驱动)、Statement(编译及执行SQL语句)、ResultSet(处理结果集)
步骤:
1、关联数据库驱动包(不同的数据库使用的驱动包是不一样的)
2、注册驱动(通过反射进行驱动的注册)
3、连接数据库获取数据库连接对象(登录-选择数据库的过程)
4、执行SQL语句
5、处理结果集
6、关闭数据库连接对象
-
关键词:DriverManager(管理JDBC驱动)、Statement(用于SQL的编译及执行)/PreparedStatement(预编译)/CallableStatement(执行存储过程)、ResultSet(结果集)
如果在执行修改动作时出现乱码,是因为开发工具的编码与数据库中的编码不一致导致的,数据库的编码大多数为UTF-8,所以在连接的地址上去设置编码即可,针对5.1及8.0的版本不同,设置编码的方式为:
8.0:jdbc:mysql://localhost:3306/数据库名?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai 5.1:jdbc:mysql://localhost:3306/数据库名?characterEncoding=UTF-8
示例:
public class JdbcTest { //驱动,数据库地址,用户名,密码是通用的,可以定义为final类型全局变量, private static final String DRIVER = "com.mysql.cj.jdbc.Driver"; private static final String URL = "jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai"; private static final String USER = "root"; private static final String PASSWORD = "123456"; public static void main(String[] args) { delete(); } public static void delete() { //注册驱动,连接数据库,定义sql语句,定义并使用Statement对象执行sql, Connection conn =null; Statement statement = null; try { Class.forName(DRIVER); conn = DriverManager.getConnection(URL,USER,PASSWORD); String sql = "delete from department where did=60"; statement = conn.createStatement(); int result = statement.executeUpdate(sql); System.out.println(result); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); }finally { try { if(statement!=null) { statement.close(); } if(conn!=null) { conn.close(); } } catch (SQLException e) { e.printStackTrace(); } } } /** * 修改数据 */ public static void update() { //注册驱动,连接数据库,定义sql语句,定义并使用Statement对象执行sql, Connection conn =null; Statement statement = null; try { Class.forName(DRIVER); conn = DriverManager.getConnection(URL,USER,PASSWORD); String sql = "update department set dphone='01066666' where did=60"; statement = conn.createStatement(); int result = statement.executeUpdate(sql); System.out.println(result); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); }finally { try { if(statement!=null) { statement.close(); } if(conn!=null) { conn.close(); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } /** * 增加数据 */ public static void insert() { Connection conn = null; Statement statement = null; try { //注册驱动 Class.forName(DRIVER); //登录数据库链接对象 conn = DriverManager.getConnection(URL, USER, PASSWORD); //定义sql String sql = "insert into department values(60,'采购部','2楼202',037122222)"; //执行sql 需要Statement对象 statement = conn.createStatement(); //执行sql获取结果集 int result = statement.executeUpdate(sql); System.out.println(result); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); }finally{ try { if(statement!=null) { statement.close(); } if(conn!=null) { conn.close(); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } /** * 查询数据库功能 */ public static void query(int num) { Connection conn = null; Statement statement = null; ResultSet rs = null; try { //注册驱动 Class.forName(DRIVER); //登录数据库链接对象 conn = DriverManager.getConnection(URL, USER, PASSWORD); //定义sql String sql = "select did,dname,loc,dphone from department"; //执行sql 需要Statement对象 statement = conn.createStatement(); //执行sql获取结果集 rs = statement.executeQuery(sql); //遍历结果集(查询过程中可能会出现查询结果为空) while(rs.next()) { int did = rs.getInt(1); String dname = rs.getString(2); String loc = rs.getString(3); int dphone = rs.getInt(4); System.out.println(did+"--"+dname+"--"+loc+"--"+dphone); } } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); }finally{ try { if(rs!=null) { rs.close(); } if(statement!=null) { statement.close(); } if(conn!=null) { conn.close(); } } catch (SQLException e) { e.printStackTrace(); } } } }
从以上代码可以看出增删改的代码只有sql语句的区别,可以将他们的代码封装到一个工具类中
代码示例:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBUtil {
//驱动,数据库地址,用户名,密码是通用的,可以定义为final类型全局变量,
private static final String DRIVER = "com.mysql.cj.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai";
private static final String USER = "root";
private static final String PASSWORD = "123456";
private static Connection conn = null;
//注册驱动
static {
try {
Class.forName(DRIVER);
} catch (ClassNotFoundException e) {
System.out.println("驱动注册失败");
e.printStackTrace();
}
}
/**
* 连接数据库
* @return
* @throws SQLException
*/
public static Connection newInstance() throws SQLException {
if(conn == null) {
conn = DriverManager.getConnection(URL,USER,PASSWORD);
}
return conn;
}
/**
* 增删改通用方法
* @param sql
* @return
*/
public static int curdMethod(String sql) {
//注册驱动,连接数据库,定义sql语句,定义并使用Statement对象执行sql,
Statement statement = null;
try {
statement = conn.createStatement();
int result = statement.executeUpdate(sql);
return result;
} catch (SQLException e) {
e.printStackTrace();
}finally {
close(conn,statement,null);
}
return 0;
}
/** 关闭连接对象
* @param conn
* @param statement
* @param rs
*/
public static void close(Connection conn,Statement statement,ResultSet rs) {
try {
if(rs != null) {
rs.close();
}
if(statement != null) {
statement.close();
}
if(conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
上面的示例中只需要在curdMethod(String sql)方法中传入要执行的增删改sql语句就可以实现增删改功能,查询的封装较为复杂,之后补充。