代码使用Mysql8.0,演示连接数据库的6步。
第一步导入jar包时,应使用和Mysql版本相匹配的包
public class JDBCDemo {
//1.在项目上新建一个文件夹,lib,导入mysql驱动,并将jar包加入到项目中
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
//2.注册驱动,使用到了反射
Class.forName("com.mysql.cj.jdbc.Driver");
//3、获取连接,这里连接了云端服务器。若连接本地则把IP地址换为localhost
String url = "jdbc:mysql://60.205.235.184:3306/userdb";
String usename = "userdb";
String password = "root";
connection = DriverManager.getConnection(url,usename,password);
//4.获取预编译SQL,查询emp表中的数据
String sql = "select * from emp";
preparedStatement = connection.prepareStatement(sql);
//5、查询,获取结梁集(增删改,添加数据即可)并遍历
//增删改使用execute()或者executeUpdate()
resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
String ename = resultSet.getString("ename");
String job = resultSet.getString("job");
System.out.println(ename + " ===== " + job);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
//6、释放资源
while (resultSet != null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
while (preparedStatement != null){
try {
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
while (connection != null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
由于在数据库连接,每次对数据库使用增删改查操作都要执行一次来连接代码,及其占用内存,所以将代码封装:
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
/**
* @version 1.0
* @Author:2238
* @Date:2024/1/31-17:27
* @Description: 封装与关闭工具
*/
public class DbUtils {
//静态带代码块,只执行一次
private static String url = "";
private static String username = "";
private static String password = "";
static{
InputStream inputStream = DbUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties properties = new Properties();
try {
properties.load(inputStream);
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
try {
Class.forName("com.mysql.cj.jdbc.Driver");
}catch(ClassNotFoundException e){
e.printStackTrace();
}
} catch (IOException e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,username,password);
}
public static void close(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet){
//6、释放资源 Connection,PreparedStatement,ResultSet,释放时需从后往前
//如果connection,preparedStatement,resultSet对象为null JVM GC
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
测试类
mport org.junit.Test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import static org.junit.Assert.*;
/**
* @version 1.0
* @Author:2238
* @Date:2024/1/31-17:42
* @Description:
*/
public class DbUtilTest {
private static final String INSERT_SQL = "insert into emp(empno,ename,job) values(?,?,?)";
private static final String UPDATE_SQL = "update emp set ename=?,job=? where empno=?";
private static final String DELETE_SQL = "delete from emp where empno=?";
@Test
public void test01() throws SQLException {
Connection connection = DbUtils.getConnection();
System.out.println(connection);
}
@Test
public void insertTest(){
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = DbUtils.getConnection();
preparedStatement = connection.prepareStatement(INSERT_SQL);
preparedStatement.setString(1,"2024");
preparedStatement.setString(2,"z");
preparedStatement.setString(3,"CEO");
int i = preparedStatement.executeUpdate();
if(i > 0){
System.out.println("插入成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
DbUtils.close(connection,preparedStatement,null);
}
}
@Test
public void updateTest(){
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = DbUtils.getConnection();
preparedStatement = connection.prepareStatement(UPDATE_SQL);
preparedStatement.setString(1,"张辉");
preparedStatement.setString(2,"董事长");
preparedStatement.setString(3,"2024");
int i = preparedStatement.executeUpdate();
if (i > 0){
System.out.println("修改成功!");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
DbUtils.close(connection,preparedStatement,null);
}
}
@Test
public void deleteTest(){
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = DbUtils.getConnection();
preparedStatement = connection.prepareStatement(DELETE_SQL);
preparedStatement.setString(1,"2024");
boolean b = preparedStatement.execute();
if (b != false){
System.out.println("删除成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
DbUtils.close(connection,preparedStatement,null);
}
}
}