JDBC操作详解
JDBC就是用Java语言操作数据库,在使用它之前,首先我要引入依赖。
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.33</version>
</dependency>
接下来就是具体步骤
public static final String URL = "jdbc:mysql://localhost:3306/jdbc_demo";
public static final String USERNAME = "root";
public static final String PASSWORD = "123456";
public static int insert(User user) throws SQLException {
// 1.注册数据库驱动
DriverManager.registerDriver(new Driver());
// 2.获取与的数据库连接
Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
// 3.书写sql语句
Statement statement = connection.createStatement();
String sql = String.format("INSERT INTO user (name,age) VALUES (%s,%d )", user.getName(), user.getAge());
// 4.执行sql语句
statement.executeUpdate(sql);
// 5.关闭数据库连接
connection.close();
}
我们把异常捕获一下
public static int insert(User user) {
// 1.注册数据库驱动
DriverManager.registerDriver(new Driver());
Connection connection = null;
try {
// 2.建立与数据库的连接
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
// 3.书写sql语句
// 使用connection.createStatement 有严重的sql注入
// 因此之后使用prepareStatement
Statement statement = connection.createStatement();
String sql = String.format("INSERT INTO user (name,age) VALUES (%s,%d )", user.getName(), user.getAge());
// 4.执行sql语句
int i = statement.executeUpdate(sql);
return i;
} catch (SQLException e) {
System.out.println(e.getMessage());
System.out.println("新增错误");
} finally {
try {
// 5.关闭与数据库的连接
connection.close();
} catch (SQLException ex) {
throw new RuntimeException(ex);
}
}
return -1;
}
我们还可以优化一下:
首先,数据库驱动注册可以省略
因为在 com.mysql.cj.jdbc.Driver内部代码有这样一段:
static {
try {
DriverManager.registerDriver(new Driver());
} catch (SQLException var1) {
throw new RuntimeException("Can't register driver!");
}
}
即在类加载时便注册了数据库驱动因此注册驱动可以省略
其次,使用createStatement有严重的sql注入问题,因此我们改用prepareStatement
接下来,Java1.7特性,提供了try-with-resource,try()可以传入一个实现了AutoCloseable接口的类,在离开try块时将自动调用close()方法。Collection正好实现了AutoCloseable。
优化后的代码:
public static int insert(User user) {
try (Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
String sql = "INSERT INTO user(name,age) VALUES (?,?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, user.getName());
preparedStatement.setInt(2, user.getAge());
int i = preparedStatement.executeUpdate();
return i;
} catch (SQLException e) {
System.out.println(e.getMessage());
System.out.println("新增错误");
}
return -1;
}
抽取Jdbc工具类
我们可以把url,username,password等数据库相关信息和getConnection方法,封装到一个工具类里;还可以利用配置文件导入数据库相关信息
在resourse下创建jdbc.properties
url:jdbc:mysql://localhost:3306/jdbc_demo"
username:root
password:123456
JdbcUtils
private static final String URL;
private static final String USERNAME;
private static final String PASSWORD;
static {
InputStream resource = ClassLoader.getSystemResourceAsStream("jdbc.properties");
Properties properties = new Properties();
try {
properties.load(resource);
} catch (IOException e) {
e.printStackTrace();
}
URL = properties.getProperty("url");
USERNAME = properties.getProperty("username");
PASSWORD = properties.getProperty("password");
}
public static Connection getConnection() {
Connection connection = null;
try {
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
} catch (SQLException e) {
System.err.println("数据库连接参数错误,Connection获取失败!");
System.err.println(e.getMessage());
}
return connection;
}
Druid数据库连接池
之前的代码,每进行一次sql语句,都要先建立连接之后断开,这样占用宝贵的连接资源,因此使用池化技术,使与数据库的连接不再断开。
首先引入druid依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.20</version>
</dependency>
druid.properties
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/project_01
username=root
password=123456
initialSize=5
maxActive=10
maxWait=3000
编写Jdbc工具类
private static DataSource druidDataSource;
static {
InputStream resource = ClassLoader.getSystemResourceAsStream("druid.properties");
Properties properties = new Properties();
try {
properties.load(resource);
druidDataSource = DruidDataSourceFactory.createDataSource(properties);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() {
Connection connection = null;
try {
connection = druidDataSource.getConnection();
} catch (SQLException e) {
System.out.println("数据库异常!");
}
return connection;
}
开启事务
public static int update(String id, String name, Timestamp updateTime) {
Connection connection = JdbcUtils.getConnection();
try {
// 1.开启事务
connection.setAutoCommit(false);
// sql1 查询
// 判断查询的结果
// sql2 修改
// 2.事务提交
connection.commit();
return i;
} catch (SQLException e) {
try {
// 3.回滚
connection.rollback();
} catch (SQLException ex) {
System.out.println(ex.getMessage());
}
System.out.println(e.getMessage());
}
return -1;
}