Contents
25.1. Java操作Mysql¶
25.1.1. JDBC技术¶
Java中数据库编程是通过JDBC（Java Database Connectivity）实现的。 使用JDBC技术涉及到三种不同的角色：Java官方、开发人员和数据库厂商。
JDBC技术涉及到三种不同的角色 * Java官方提供JDBC接口，如Connection、Statement和ResultSet等。
- 数据库厂商为了支持Java语言使用自己的数据库，他们根据这些接口提供了具体的实现类，这 些具体实现类称为JDBC Driver（JDBC驱动程序），例如Connection是数据库连接接口，如何能 够高效地连接数据库或许只有数据库厂商自己清楚，因此他们提供的JDBC驱动程序当然是最高 效的，当然针对某种数据库也可能有其他第三方JDBC驱动程序。
- 对于开发人员而言，JDBC提供了一致的API，开发人员不用关心实现接口的细节。
25.1.2. 建立数据连接¶
HelloWorld.java
package com.a51work;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class HelloWorld {
public static void main(String[] args) {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
System.out.println("驱动程序加载成功...");
} catch (
ClassNotFoundException e) {
System.out.println("驱动程序加载失败...");
// 退出
return;
}
String url = "jdbc:mysql://localhost:3306/test?serverTimezone=UTC";
String user = "root";
String password = "admin#123";
try (
Connection conn = DriverManager.getConnection(url, user, password)) {
System.out.println("数据库连接成功：" + conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
读取配置文件示例¶
package com.a51work;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
public class JDBCUtilsV3 {
private Connection conn2; //连接对象
private PreparedStatement pst;
private ResultSet rst;
private static String DRIVER;
private static String URL;
private static String USER;
private static String PASSWORD;
static {
Properties pt = new Properties();
try {
FileInputStream in = new FileInputStream("resources/config.properties");
pt.load(in);
DRIVER = pt.getProperty("driver");
URL = pt.getProperty("url");
USER = pt.getProperty("user");
PASSWORD = pt.getProperty("password");
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 获取连接方法
*
* @return
*/
public static Connection getConnection() {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
Class.forName(DRIVER);
System.out.println("连接数据库驱动成功.....");
} catch (ClassNotFoundException e) {
System.out.println("连接数据库驱动失败.....");
return conn;
}
try {
conn = DriverManager.getConnection(URL, USER, PASSWORD);
System.out.println("连接数据库成功......");
} catch (Exception e) {
e.printStackTrace();
} finally {
release(conn, pstmt, rs);
}
return conn;
}
/**
* 释放资源方法
*
* @param conn
* @param pstmt
* @param rs
*/
public static void release(Connection conn, PreparedStatement pstmt, ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) {
JDBCUtilsV3.getConnection();
}
}
Java连接Mysql¶
JdbcUtil.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JdbcUtil {
//这个url用来跟数据库建立连接
//localhost 这个是用来建立连接的数据库的ip地址
//3306是要连接的端口，就是数据库绑定的端口
// db这个是要建立连接的数据库的名字
//useUnicode=true&characterEncoding=UTF-8这里使用unicode编码，编码格式是UTF-8，就是跟数据库建立连接的格式
//这个字符集的设置是用来解决中文乱码问题的
static String url ="jdbc:mysql://localhost:3306/db?useUnicode=true&characterEncoding=UTF-8";
//这个连接数据库的用户名
static String username = "root";
//这个是连接数据库的密码
static String password = "root";
//注册驱动
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//得到一个数据库的连接
public static Connection getConnection() throws Exception{
return DriverManager.getConnection(url,username,password);
}
//这个方法用来释放连接
//ResultSet是结果集，从数据库里查询出来的数据会存放在这里
//Statement 这个是用来执行事务，SQL语句的
//Connection是跟数据库之间的连接
public static void free(ResultSet rs,Statement st,Connection con){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(st!=null){
try {
st.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(con!=null){
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
25.1.3. 数据CRUD操作（MySQL增删改查）¶
有条件的查询¶
CRUDSample.java
package com.a51work;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class CRUDSample {
//连接数据库url
private static String DRIVER;
private static String URL;
private static String USER;
private static String PASSWORD;
//创建Properties对象
static Properties info = new Properties();
//1.驱动程序加载
static {
try {
FileInputStream in = new FileInputStream("resources/config.properties");
info.load(in);
DRIVER = info.getProperty("driver");
URL = info.getProperty("url");
USER = info.getProperty("user");
PASSWORD = info.getProperty("password");
Class.forName(DRIVER);
System.out.println("");
System.out.println("驱动程序加载成功...");
} catch (ClassNotFoundException e) {
System.out.println("驱动程序加载失败...");
} catch (IOException e) {
System.out.println("加载属性文件失败...");
}
}
//数据有条件查询操作
public static void read() {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
// 2创建数据库连接
conn = DriverManager.getConnection(URL, info);
// 创建语句对象
pstmt = conn.prepareStatement("select * from student where id =?");
pstmt.setInt(1,1001);
rs = pstmt.executeQuery();
while (rs.next()) {
System.out.printf("id: %d name:%s\n", rs.getInt(1), rs.getString("name"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 释放资源
release(conn, pstmt, rs);
}
}
/**
* 释放资源方法
*
* @param conn
* @param pstmt
* @param rs
*/
public static void release(Connection conn, PreparedStatement pstmt, ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) {
read();
}
}
/*
驱动程序加载成功...
id: 1001 name:hujianli
*/
无条件的查询¶
//数据无条件查询操作
public static void readMaxUserId() {
int maxId = 0;
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
// 2创建数据库连接
conn = DriverManager.getConnection(URL, info);
// 创建语句对象
pstmt = conn.prepareStatement("select max(id) from student");
rs = pstmt.executeQuery();
while (rs.next()) {
System.out.printf("id: %d \n", rs.getInt(1));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 释放资源
release(conn, pstmt, rs);
}
}
插入数据¶
// 插入数据
public static void create() {
try (Connection conn = DriverManager.getConnection(URL, info);
PreparedStatement pstmt = conn.prepareStatement("INSERT INTO student(id,name,phone) VALUES(?,?,?)")) {
int maxId = readMaxUserId();
System.out.println(maxId);
pstmt.setInt(1, ++maxId);
pstmt.setString(2, "xiaojian" + maxId);
pstmt.setString(3,"1213122344325");
int affectedRows = 0;
try {
affectedRows = pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
System.out.printf("成功插入%d条数据。\n", affectedRows);
} catch (SQLException e) {
e.printStackTrace();
}
}
更新数据¶
package com.a51work;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
public class CRUDSample4 {
//连接数据库url
private static String DRIVER;
private static String URL;
private static String USER;
private static String PASSWORD;
//创建Properties对象
static Properties info = new Properties();
//1.驱动程序加载
static {
try {
FileInputStream in = new FileInputStream("resources/config.properties");
info.load(in);
DRIVER = info.getProperty("driver");
URL = info.getProperty("url");
USER = info.getProperty("user");
PASSWORD = info.getProperty("password");
Class.forName(DRIVER);
System.out.println("");
System.out.println("驱动程序加载成功...");
} catch (ClassNotFoundException e) {
System.out.println("驱动程序加载失败...");
} catch (IOException e) {
System.out.println("加载属性文件失败...");
}
}
// 更新数据
public static void update() {
// 自动释放资源
try ( // 2创建数据库连接
Connection conn = DriverManager.getConnection(URL, info);
// 3.创建语句对象
PreparedStatement pstmt = conn.prepareStatement("update student set name = ? where id > ?")) {
// 4.绑定参数
pstmt.setString(1, "胡建力");
pstmt.setInt(2, 1001);
int affectedRows = pstmt.executeUpdate();
System.out.printf("成功更新%d条数据。\n", affectedRows);
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
update();
}
}
//驱动程序加载成功...
// 成功更新3条数据。
删除数据¶
// 删除数据
public static void delete() {
// 自动释放资源
try ( // 2创建数据库连接
Connection conn = DriverManager.getConnection(URL, info);
// 3.创建语句对象
PreparedStatement pstmt = conn.prepareStatement("delete from student where name = ?")) {
// 4.绑定参数
pstmt.setString(1, "hu");
// 执行修改（C、U、D）
int affectedRows = pstmt.executeUpdate();
System.out.printf("成功删除%d条数据。\n", affectedRows);
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
delete();
}
数据增删改查（CRUD汇总）¶
package com.a51work;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
public class CRUDSample6 {
//连接数据库url
private static String DRIVER;
private static String URL;
private static String USER;
private static String PASSWORD;
//创建Properties对象
static Properties info = new Properties();
//1.驱动程序加载
static {
try {
FileInputStream in = new FileInputStream("resources/config.properties");
info.load(in);
DRIVER = info.getProperty("driver");
URL = info.getProperty("url");
USER = info.getProperty("user");
PASSWORD = info.getProperty("password");
Class.forName(DRIVER);
System.out.println("");
System.out.println("驱动程序加载成功...");
} catch (ClassNotFoundException e) {
System.out.println("驱动程序加载失败...");
} catch (IOException e) {
System.out.println("加载属性文件失败...");
}
}
//数据有条件查询操作
public static void read() {
try (
// 2创建数据库连接
Connection conn = DriverManager.getConnection(URL, info);
// 3.创建语句对象
PreparedStatement pstmt = conn.prepareStatement("select * from student where id =?")){
pstmt.setInt(1,1001);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
System.out.printf("id: %d name:%s\n", rs.getInt(1), rs.getString("name"));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
//数据无条件查询操作
public static int readMaxUserId() {
int maxId = 0;
// 自动释放资源
try ( // 2创建数据库连接
Connection conn = DriverManager.getConnection(URL, info);
// 3.创建语句对象
PreparedStatement pstmt = conn.prepareStatement("select max(id) from student")) {
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
System.out.printf("id: %d \n", rs.getInt(1));
maxId = rs.getInt(1);
}
} catch (SQLException e) {
e.printStackTrace();
}
return maxId;
}
// 插入数据
public static void create() {
try (Connection conn = DriverManager.getConnection(URL, info);
PreparedStatement pstmt = conn.prepareStatement("INSERT INTO student(id,name,phone) VALUES(?,?,?)")) {
int maxId = readMaxUserId();
System.out.println(maxId);
pstmt.setInt(1, ++maxId);
pstmt.setString(2, "xiaojian" + maxId);
pstmt.setString(3,"1213122344325");
int affectedRows = 0;
try {
affectedRows = pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
System.out.printf("成功插入%d条数据。\n", affectedRows);
} catch (SQLException e) {
e.printStackTrace();
}
}
// 更新数据
public static void update() {
// 自动释放资源
try ( // 2创建数据库连接
Connection conn = DriverManager.getConnection(URL, info);
// 3.创建语句对象
PreparedStatement pstmt = conn.prepareStatement("update student set name = ? where id > ?")) {
// 4.绑定参数
pstmt.setString(1, "胡建力");
pstmt.setInt(2, 1001);
int affectedRows = pstmt.executeUpdate();
System.out.printf("成功更新%d条数据。\n", affectedRows);
} catch (SQLException e) {
e.printStackTrace();
}
}
// 删除数据
public static void delete() {
// 自动释放资源
try ( // 2创建数据库连接
Connection conn = DriverManager.getConnection(URL, info);
// 3.创建语句对象
PreparedStatement pstmt = conn.prepareStatement("delete from student where name = ?")) {
// 4.绑定参数
pstmt.setString(1, "hu");
// 执行修改（C、U、D）
int affectedRows = pstmt.executeUpdate();
System.out.printf("成功删除%d条数据。\n", affectedRows);
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
create();
delete();
update();
read();
readMaxUserId();
}
}