mysql_1">一、Java连接mysql数据库
1.1 流程
- 导入驱动包：这里我使用的是mysql-connector-java-8.0.17.jar(
点击下载)，这个包连接mysql5.6，5.7，8.0版本都没问题。
Class.forName("com.mysql.cj.jdbc.Driver"); - url和账户名密码
JDBC连接串：jdbc:mysql://<地址>:<端口>/<数据库> - 获取连接：
DriverManager.getConnection(url, user, password); - 执行sql的Statement对象：
connection.createStatement(); - 执行SQL
- 释放连接
java_13">1.2 一个测试连接的java程序
JdbcFirstDemo.java
java">package com.peng.less01;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JdbcFirstDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
// 1.导入驱动类
Class.forName("com.mysql.cj.jdbc.Driver");
// 2.用户信息和url
String url = "jdbc:mysql://localhost:3306/shop?useUnicode=true&characterEncoding=utf8&useSSL=false";
String user = "root";
String password = "12345678";
// 3.获取连接
Connection conn = DriverManager.getConnection(url, user, password);
// 4.执行SQL的对象
Statement sta = conn.createStatement();
// 5.执行SQL
String sql = "select * from account";
ResultSet rs = sta.executeQuery(sql);
while (rs.next()){
System.out.println("id= " + rs.getObject("id"));
System.out.println("name= " + rs.getObject("name"));
System.out.println("money= " + rs.getObject("money"));
System.out.println("=========================================");
}
// 6.释放连接
rs.close();
sta.close();
conn.close();
}
}
这里使用的是VScode，connector包放到了lib目录下，代码放在了src/com/peng/less01下。
执行结果如下，显示了shop.account表下面的三条记录。
二、优化：创建一个工具类
2.1 存在的问题
1、JDBC连接串，用户名，密码等都存在于代码中，需要进行解耦
2、创建连接，释放连接这些操作都是固定的，没必要每次都重复写这些代码（创建函数解决）
2.2 创建配置文件和工具类
src/db.properties 文件中记录driver，url，user，password
java">driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/shop?useUnicode=true&characterEncoding=utf8&useSSL=false
user=root
password=12345678
src/com/peng/less02/utils/JdbcUtils.java 工具类：读取properties配置文件
自动读取配置信息，加载mysql驱动。
java">package com.peng.less02.utils;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
// 这是一个工具类，用来减少重复操作
public class JdbcUtils {
private static String driver = null;
private static String url = null;
private static String user = null;
private static String password = null;
static{
try{
// 读取db.properties
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(in);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
user = properties.getProperty("user");
password = properties.getProperty("password");
// 加载驱动
Class.forName(driver);
}catch (Exception e){
e.printStackTrace();
}
}
// 创建连接
public static Connection getConnection() throws SQLException{
return DriverManager.getConnection(url,user,password);
}
// 释放连接
public static void releaseConnection(Connection conn, Statement sta, ResultSet rs){
if (rs != null){
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (sta != null){
try {
sta.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (conn != null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
2.3 测试使用工具类进行CRUD操作
测试插入操作
src/com/peng/less02/TestInsert.java
java">package com.peng.less02;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.peng.less02.utils.JdbcUtils;
public class TestInsert {
public static void main(String[] args) {
Connection conn = null;
Statement sta = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
sta = conn.createStatement();
String sql = "insert into account(`id`,`name`,`money`) values(4,'药水哥',4698888)";
int i = sta.executeUpdate(sql);
if (i > 0){
System.out.println("插入成功！");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JdbcUtils.releaseConnection(conn, sta, rs);
}
}
}
测试更新操作
src/com/peng/less02/TestUpdate.java
java">package com.peng.less02;
import java.sql.*;
import com.peng.less02.utils.JdbcUtils;
public class TestUpdate {
public static void main(String[] args) {
Connection conn = null;
Statement sta = null;
try {
conn = JdbcUtils.getConnection();
sta = conn.createStatement();
String sql = "update account set `money`=238888 where id=3";
int i = sta.executeUpdate(sql);
if (i > 0){
System.out.println("更新成功");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JdbcUtils.releaseConnection(conn, sta, null);
}
}
}
测试读取操作
java">package com.peng.less02;
import java.sql.*;
import com.peng.less02.utils.JdbcUtils;
public class TestSelect {
public static void main(String[] args) {
Connection conn = null;
Statement sta = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
sta = conn.createStatement();
String sql = "select * from account";
rs = sta.executeQuery(sql);
while (rs.next()){
System.out.println("id= " + rs.getInt("id"));
System.out.println("name= " + rs.getString("name"));
System.out.println("money= " + rs.getObject("money"));
System.out.println("======================================================");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JdbcUtils.releaseConnection(conn, sta, rs);
}
}
}
