Java进阶十—JDBC

一.说明

用Java语言操作Mysql，首先需要学习Mysql

MySQL入门教程-CSDN博客

二.JDBC的由来以及定义

JDBC是什么？

Java数据库连接(Java Database Connectivity)简称JDBC
JDBC是Java操作各数据库的一种规范，是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口，提供了诸如查询和更新数据库中数据的方法。JDBC是面向关系型数据库的

打个比方？

假设Java公司是布料厂，那么各SQL数据库公司就是服装设计厂
Java公司规定JDBC接口，允许去操作各数据库，相当于提供原材料
各SQL公司去实现接口，相当于拿原材料设计出自己的服装

三.JDBC体验，statement.executeQuery() 查询

1.首先下载架包：Maven Repository: mysql » mysql-connector-java (mvnrepository.com)

下载完成后，将架包拖入idea中的lib文件夹下(没有的话，自己创建)，然后点击

点击OK

2.连接

package com.jdbc;import java.sql.*;public class JDBCDemo {public static final String URL = "jdbc:mysql://localhost:3306/student";public static final String USER = "root";public static final String PASSWORD = "123456"; // 需要抛出异常：ClassNotFoundException, SQLExceptionpublic static void main(String[] args) throws ClassNotFoundException, SQLException {// 1.加载驱动程序 ——> 打电话给布料公司，你给我送来，我是mysql公司Class.forName("com.mysql.jdbc.Driver");// 2.获取数据库连接 ——> 告诉送货员，送货路线Connection connection = DriverManager.getConnection(URL,USER,PASSWORD);// 3.获取数据库操作对象 ——> 货到了，卸货到仓库Statement statement = connection.createStatement();// 4.从仓库选择要用的货// 我要查询student表ResultSet resultSet= statement.executeQuery("SELECT * FROM student");// 判断是否有数据while (resultSet.next()) {int id = resultSet.getInt(1);String name = resultSet.getString(2);int age = resultSet.getInt(3);double score = resultSet.getDouble(4);int classId = resultSet.getInt(5);System.out.println("["+id+","+name+","+age+","+score+","+classId+"]");}// 5.关闭仓库与数据库statement.close();connection.close();}}

四.整理和释放

整理上面的代码

package com.jdbc;import java.sql.*;public class JDBCDemo {// 配置public static final String URL = "jdbc:mysql://localhost:3306/student";public static final String USER = "root";public static final String PASSWORD = "123456";public static final String DRIVER = "com.mysql.jdbc.Driver";public static Connection connection;public static Statement statement;public static ResultSet resultSet; // 需要抛出异常：ClassNotFoundException, SQLExceptionpublic static void main(String[] args){try {// 1.加载驱动程序 ——> 打电话给布料公司，你给我送来，我是mysql公司Class.forName(DRIVER);// 2.获取数据库连接 ——> 告诉送货员，送货路线connection = DriverManager.getConnection(URL,USER,PASSWORD);// 3.获取数据库操作对象 ——> 货到了，卸货到仓库statement = connection.createStatement();// 4.从仓库选择要用的货// 我要查询student表 resultSet= statement.executeQuery("SELECT * FROM student"); // 判断是否有数据while (resultSet.next()) {int id = resultSet.getInt(1);String name = resultSet.getString(2);int age = resultSet.getInt(3);double score = resultSet.getDouble(4);int classId = resultSet.getInt(5);System.out.println("["+id+","+name+","+age+","+score+","+classId+"]");}} catch (ClassNotFoundException e) {throw new RuntimeException(e);} catch (SQLException e) {e.printStackTrace();} finally {try {// 5.关闭仓库与数据库statement.close();connection.close();} catch (SQLException e) {e.printStackTrace();}}}}

五.封装JDBCUtils

每个人的配置都不一样，那我们就读取位置文件。

1.创建db.properties配置文件，写入配置

2.创建JDBCUtils类，读取配置

package com.google.util;import java.io.InputStream;import java.sql.*;import java.util.Properties;public class JDBCUtils {public static String url;public static String user;public static String password;public static String driver;// 使用静态代码块，做预处理static {// 使用try/catch包裹try {// 读取配置文件InputStream inputStream = ClassLoader.getSystemResourceAsStream("db.properties");// 加载对象Properties properties = new Properties();;properties.load(inputStream);// 读取配置url = properties.getProperty("url");user = properties.getProperty("user");password = properties.getProperty("password");driver = properties.getProperty("driver");System.out.println(url+"\n"+user+"\n"+password+"\n"+driver);} catch (Exception e) {e.printStackTrace();}}// 测试是否能加载成功public static void init() {System.out.println("加载成功");}// 创建单例，获取配置项public static Connection getConnection() throws SQLException{return DriverManager.getConnection(url,user,password);}// 释放，关闭结果，关闭仓库，关闭数据库连接public static void close(Connection connection, Statement statement, ResultSet resultSet) throws SQLException{if (resultSet != null){resultSet.close();}if (statement != null){statement.close();}if (connection != null){connection.close();}}// 如果没有操作那么，关闭仓库，关闭数据库连接，方法的重载public static void close(Connection connection,Statement statement) throws SQLException{if(statement != null){statement.close();}if (connection != null){connection.close();}}}

3.测试是否能读取

package com.google.util;public class Test {public static void main(String[] args) {JDBCUtils.init();}}

注意：ClassLoader的getResourceAsStream方法使用及在java和web项目中的路径问题_classloader.getresourceasstream-CSDN博客

六.增删改executeUpdate()

使用创建JDBCUtils类，更新数据

package com.google.util;import org.junit.Test;import java.sql.Connection;import java.sql.SQLException;import java.sql.Statement;public class UpdateTest {public Connection connection;public Statement statement;@Testpublic void updateTest() {try {// 1.获取数据库连接和操作对象connection = JDBCUtils.getConnection();statement = connection.createStatement();// 2.更新一条数据String sql = "update student set score=33 where id=4";int res = statement.executeUpdate(sql);if (res > 0) {System.out.println("更新成功");}} catch (SQLException e) {e.printStackTrace();} finally {try {JDBCUtils.close(connection,statement);} catch (SQLException e) {throw new RuntimeException(e);}}}}

删除数据

 public void deleteTest() {try {// 1.获取数据库连接和操作对象connection = JDBCUtils.getConnection();statement = connection.createStatement();// 2.删除一条数据String sql = "delete from student where id=6";int res = statement.executeUpdate(sql);if (res > 0) {System.out.println("删除成功");}} catch (SQLException e) {e.printStackTrace();} finally {try {JDBCUtils.close(connection,statement);} catch (SQLException e) {throw new RuntimeException(e);}}}

插入数据

 public void insertTest() {try {// 1.获取数据库连接和操作对象connection = JDBCUtils.getConnection();statement = connection.createStatement();// 2.插入一条数据String sql = "insert into student values(6,'LiLi',15,44.3,2)";int res = statement.executeUpdate(sql);if (res > 0) {System.out.println("插入成功");}} catch (SQLException e) {e.printStackTrace();} finally {try {JDBCUtils.close(connection,statement);} catch (SQLException e) {throw new RuntimeException(e);}}}

七.字符编码问题

如果插入中文时乱码怎么办？

字符编码问题，需要更改数据库、IDE、终端的编码格式为UTF-8，在配置项中添加字符编码

八.PreparedStatement和问号占位符

如果想从键盘中输入数据，而不是像下面这样

String sql = "insert into student values(7,'mimi',33,52.3,1)";

那就把Statement换成PreparedStatement

1.第一步：修改封装JDBCUtils：把Statement换成PreparedStatement

package com.google.util;import java.io.InputStream;import java.sql.*;import java.util.Properties;public class JDBCUtils {public static String url;public static String user;public static String password;public static String driver;// 使用静态代码块，做预处理static {// 使用try/catch包裹try {// 读取配置文件InputStream inputStream = ClassLoader.getSystemResourceAsStream("db.properties");// 加载对象Properties properties = new Properties();;properties.load(inputStream);// 读取配置url = properties.getProperty("url");user = properties.getProperty("user");password = properties.getProperty("password");driver = properties.getProperty("driver");} catch (Exception e) {e.printStackTrace();}}// 测试是否能加载成功public static void init() {System.out.println("加载成功");}// 创建单例，获取配置项public static Connection getConnection() throws SQLException{return DriverManager.getConnection(url,user,password);}// 释放，关闭结果，关闭仓库，关闭数据库连接public static void close(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet) throws SQLException{if (resultSet != null){resultSet.close();}if (preparedStatement != null){preparedStatement.close();}if (connection != null){connection.close();}}// 如果没有操作那么，关闭仓库，关闭数据库连接，方法的重载public static void close(Connection connection,PreparedStatement preparedStatement) throws SQLException{if(preparedStatement != null){preparedStatement.close();}if (connection != null){connection.close();}}}

第二步：使用下面代码方式

package com.google.util;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;import java.util.Scanner;public class Test {// 注意此处是静态的public static Connection connection;public static PreparedStatement preparedStatement;// 接收器public static Scanner scanner = new Scanner(System.in);public static void main(String[] args) {try {// 1.获取数据库connection = JDBCUtils.getConnection();// 2.插入一条数据String sql = "insert into student values(" /> 0) {System.out.println("插入成功");}} catch (SQLException e) {e.printStackTrace();} finally {try {JDBCUtils.close(connection,preparedStatement);} catch (SQLException e) {scanner.close();throw new RuntimeException(e);}}}}

同理删除与更改一样

读取只需要更改一小部分

// 2.插入一条数据String sql = "select * from student wherealigncenter" src="https://img.maxssl.com/uploads/?url=https://img-blog.csdnimg.cn/direct/5fd8deeb3bac4f308323125f201f3f6c.png" />

Java进阶十—JDBC