简介
JDBC用于在Java程序中操作数据库。JDBC提供一套访问各类数据库通用的API,不同的数据库厂商会根据各自的数据库特点去实现这些接口。
以MySQL为例,需要导入对应版本的jar包才能使用JDBC,如mysql-connector-java-8.0.28.jar
(下载)
基本步骤
- 加载驱动(Class.forName)
- 建立连接(DriverManager)并返回连接(Connection)
- 创建语句对象(Connection 创建一个 Statement 或 PreparedStatement , 用于执行SQL语句)
- 执行SQL语句(Statement 或 PreparedStatement执行SQL语句)
- 处理结果集(SELECT产生结果集ResultSet)
- 关闭连接(依次将ResultSet、Statement、PreparedStatement、Connection对象关闭,释放资源)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35
| import java.sql.*;
public class JdbcFirstDemo { public static void main(String[] args) throws ClassNotFoundException, SQLException { Class.forName("com.mysql.cj.jdbc.Driver"); String url = "jdbc:mysql://localhost:3306/school?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&useSSL=false"; String username = "root"; String password = "root"; Connection connection = DriverManager.getConnection(url, username, password); Statement statement = connection.createStatement(); String sql = "SELECT * FROM student"; ResultSet resultSet = statement.executeQuery(sql); while (resultSet.next()) { System.out.println("number=" + resultSet.getObject("number")); System.out.println("pwd=" + resultSet.getObject("login_pwd")); System.out.println("name=" + resultSet.getObject("name")); } resultSet.close(); statement.close(); connection.close(); } }
|
设计工具类JDBCUtils
工具类读取配置文件db.properties
的内容,连接上数据库。
1 2 3 4
| driver=com.mysql.cj.jdbc.Driver url=jdbc:mysql://localhost:3306/school?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&useSSL=false username=root password=root
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58
| package utils;
import java.io.IOException; import java.io.InputStream; import java.sql.*; import java.util.Properties;
public class JDBCUtils { private static String driver = null; private static String url = null; private static String username = null; private static String password = null;
static { try { InputStream in = JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties"); Properties properties = new Properties(); properties.load(in); driver = properties.getProperty("driver"); url = properties.getProperty("url"); username = properties.getProperty("username"); password = properties.getProperty("password"); Class.forName(driver); } catch (IOException | ClassNotFoundException e) { e.printStackTrace(); } }
public static Connection getConnection() throws SQLException { return DriverManager.getConnection(url, username, password); }
public static void release(Connection connection, Statement statement, ResultSet resultSet) throws SQLException { if (resultSet != null) { resultSet.close(); } if (statement != null) { statement.close(); } if (connection != null) { connection.close(); } } }
|
PreparedStatement
对象
使用PreparedStatement
可防止SQL注入,且执行效率比Statement
更高。PreparedStatement是如何防止SQL注入的? - 栖息之鹰 - 博客园
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
| import utils.JDBCUtils;
import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException;
public class TestSelect { public static void main(String[] args) throws SQLException { Connection connection = JDBCUtils.getConnection(); PreparedStatement statement = connection.prepareStatement("select * from `student` where `number`=?"); statement.setInt(1, 1000); ResultSet resultSet = statement.executeQuery(); if (resultSet.next()) { System.out.println(resultSet.getString("name")); } JDBCUtils.release(connection, statement, resultSet); } }
|