一、JDBC连接数据库的步骤
1、加载jdbc驱动程序；
2、提供JDBC连接的URL
2、创建数据库的连接；
3、创建preparedStatement；
4、执行SQL语句；
5、遍历结果集；
6、处理异常，关闭JDBC对象资源。
package com.example.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class JdbcProcess {
public static void main(String[] args){
try{
//首先加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//提供JDBC连接的URL
String url="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=Asia/Shanghai&autoReconnect=true";
String username="root";
String password="123456";
//创建数据库的连接
Connection con = DriverManager.getConnection(url,username,password);
//创建一个statement执行者
String sql="SELECT * FROM sys_role WHERE id = ?";
PreparedStatement statement = con.prepareStatement(sql);
statement.setLong(1,1);
//执行SQL语句
ResultSet result = statement.executeQuery();
//处理返回结果
while (result.next()){
System.out.println(result.getString("name") + "---" + result.getString("remark"));
}
//关闭JDBC对象
con.close();
result.close();
statement.close();
}catch(ClassNotFoundException e){
e.printStackTrace();
} catch(Exception e){
e.printStackTrace();
}
}
}
二、实操过程
1、创建数据库表sys_role
2、编写配置 dbConfig.properties
jdbcDriver=com.mysql.cj.jdbc.Driver
jdbcUrl=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=Asia/Shanghai&autoReconnect=true
jdbcUser=root
jdbcPassword=123456
3、编写工具类JdbcUtil.java
package com.example.jdbc;
import java.sql.*;
import java.util.ResourceBundle;
/**
* JDBC工具类
* @author
*
*/
public class JdbcUtil {
private static final String jdbcDriver;
private static final String jdbcUrl;
private static final String jdbcUser;
private static final String jdbcPassword;
static {
ResourceBundle bundle = ResourceBundle.getBundle("dbConfig");
jdbcDriver = bundle.getString("jdbcDriver");
jdbcUrl = bundle.getString("jdbcUrl");
jdbcUser = bundle.getString("jdbcUser");
jdbcPassword = bundle.getString("jdbcPassword");
}
//装载驱动
private static void loadDriver() throws ClassNotFoundException{
Class.forName(jdbcDriver);
}
//获取连接
public static Connection getConnection() throws Exception{
loadDriver();
return DriverManager.getConnection(jdbcUrl, jdbcUser, jdbcPassword);
}
//释放资源
public static void release(ResultSet rs, Statement stmt, Connection conn){
try {
if (rs != null) rs.close();
}catch (SQLException e){
e.printStackTrace();
}
release(stmt, conn);
}
public static void release(Statement stmt, Connection conn){
try {
if (stmt != null) stmt.close();
if (conn != null) conn.close();
}catch (SQLException e){
e.printStackTrace();
}
}
}
4、实现增删改查JdbcTest.java
package com.example.jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JdbcTest {
public static void main(String[] args) throws Exception{
jdbcSelect();
jdbcInsert();
jdbcUpdate();
jdbcDelete();
}
/**
* 查询
* @throws Exception
*/
public static void jdbcSelect() throws Exception{
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try{
conn = JdbcUtil.getConnection();
stmt = conn.createStatement();
String sql = "SELECT * FROM sys_role";
rs = stmt.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getString("name"));
}
}catch (SQLException e){
e.printStackTrace();
}finally {
JdbcUtil.release(rs, stmt, conn);
}
}
/**
* 插入
* @throws Exception
*/
public static void jdbcInsert() throws Exception{
Connection conn = null;
PreparedStatement stmt = null;
try {
conn = JdbcUtil.getConnection();
String sql = "INSERT INTO sys_role(module, name, remark, available)"
+"values(?,?,?,?)";
stmt = conn.prepareStatement(sql);
String module = "张三";
String name = "M";
String remark = "备注";
int available = 1;
stmt.setString(1, module);
stmt.setString(2, name);
stmt.setString(3, remark);
stmt.setInt(4, available);
stmt.executeUpdate();
}catch (SQLException e){
e.printStackTrace();
}finally {
JdbcUtil.release(stmt, conn);
}
}
/**
* 更新
* @throws Exception
*/
public static void jdbcUpdate() throws Exception{
Connection conn = null;
PreparedStatement stmt = null;
try {
conn = JdbcUtil.getConnection();
String sql = "update sys_role set name=? where id=?";
stmt = conn.prepareStatement(sql);
String name = "超级管理员";
int id = 34;
stmt.setString(1, name);
stmt.setInt(2, id);
stmt.executeUpdate();
}catch (SQLException e){
e.printStackTrace();
}finally {
JdbcUtil.release(stmt, conn);
}
}
/**
* 删除
* @throws Exception
*/
public static void jdbcDelete() throws Exception{
Connection conn = null;
PreparedStatement stmt = null;
try {
conn = JdbcUtil.getConnection();
String sql = "delete from sys_role where id=?";
stmt = conn.prepareStatement(sql);
int id = 34;
stmt.setLong(1, id);
stmt.executeUpdate();
}catch (SQLException e){
e.printStackTrace();
}finally {
JdbcUtil.release(stmt, conn);
}
}
}