使用最原始的sql连接,并将其封装成工具
使用JDBC连接
package com.lzy.jdbc;
import java.sql.*;
public class JDBCDemo {
public static void main(String[] args) throws Exception {
//1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获取连接
String url = "jdbc:mysql://127.0.0.1:3306/tlias?serverTimezone=UTC";
String username = "root";
String password = "root";
Connection connection = DriverManager.getConnection(url, username, password);
//3.定义Sql语句
String sql1 = "update dept set name = '好吃' where id = 1";
String sql2 = "select * from dept";
//4.获取执行sql的对象 Statement
Statement statement = connection.createStatement();
//5.执行sql
//修改
int count = statement.executeUpdate(sql1);
//查询
ResultSet resultSet = statement.executeQuery(sql2);
//6.处理结果
//修改结果
System.out.println(count);
//查询结果
while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
String createTime = resultSet.getString("create_time");
String updateTime = resultSet.getString("update_time");
System.out.println(id + " " + name + " " + createTime + " " + updateTime);
}
//7.释放资源
statement.close();
connection.close();
}
}
最原始的连接数据库
缺点:
1.sql语句的编写太过繁琐
2.每次连接都是一个连接池,都得创建新的连接
3.字段和实体属性名不匹配,每次都得写对应的名字,字段一但多起来就麻烦
使用Druid(德鲁伊连接池)
1.普通java工程需要去下载Druid的jar包并导入,我们为了省事,直接使用maven工程导入依赖
<!-- druid连接池 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.23</version>
</dependency>
2.使用Druid连接池后配置文件druid.preperties
druid.properties=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/tlias?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8
username=root
password=root
# ??????
initialSize=5
# ?????
maxActive=10
# ??????
maxWait=3000
3.代码示例
package com.lzy.druid;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.util.Properties;
//使用数据库连接池
public class DruidDemo {
public static void main(String[] args) throws Exception {
//1.导入依赖 druid
//2.定义配置文件,在resource目录下创建druid.properties
//3.加载配置文件
Properties properties = new Properties();
/*有时候会报错,路径的问题,慢慢加路径或者减少路径,
*最长:jdbc/src/main/resources/druid.properties
* 最短:druid.properties
*/
properties.load(new FileInputStream("src/main/resources/druid.properties"));
//4.获取连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
//5.获取连接
Connection connection = dataSource.getConnection();
System.out.println(connection);
}
}
这里是使用了Druid的连接池
优点:
1.有着多个连接池,不必每次都去创建新的连接池
2.使用配置文件,可以灵活变更数据库
利用Druid编写一个功能的增删改查
package com.lzy.test;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.lzy.pojo.Brand;
import org.junit.Test;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Properties;
public class BrandTest {
/*
* 查询所有
* 1.sql:select * from dept;
* 2.参数:不需要
* 3.结果:返回一个List<Brand>集合
*/
@Test
public void testSelectAll() throws Exception {
//1.获取Connection对象
//3.加载配置文件
Properties properties = new Properties();
/*有时候会报错,路径的问题,慢慢加路径或者减少路径,
*最长:jdbc/src/main/resources/druid.properties
* 最短:druid.properties
*/
properties.load(new FileInputStream("src/main/resources/druid.properties"));
//4.获取连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
//5.获取连接对象
Connection connection = dataSource.getConnection();
//2.定义SQL语句
String sql = "select * from dept";
//3.获取PreparedStatement对象
PreparedStatement pr = connection.prepareStatement(sql);
//4.执行SQL语句
ResultSet resultSet = pr.executeQuery();
//5.处理结果集将结果集封装成Brand对象,然后放到集合
ArrayList<Brand> list = new ArrayList<>();
while (resultSet.next()) {
//获取数据
int id = resultSet.getInt("id");
String brandName = resultSet.getString("name");
String createTime = resultSet.getString("create_time");
String updateTime = resultSet.getString("update_time");
//封装成Brand对象
Brand brand = new Brand(id, brandName, createTime, updateTime);
//brand.setId(id);
//brand.setBrandName(brandName);
//brand.setCreatTime(createTime);
//brand.setUpdateTime(updateTime);
//装载集合
list.add(brand);
}
System.out.println(list);
//6.释放资源
resultSet.close();
pr.close();
connection.close();
}
/*
* 新增
* 1.sql:insert into dept(id,name,create_time,update_time) values(?,?,?,?) ;
* 2.参数:不需要
* 3.结果:返回一个List<Brand>集合
*/
@Test
public void testAdd() throws Exception {
//1.获取Connection对象
//3.加载配置文件
Properties properties = new Properties();
/*有时候会报错,路径的问题,慢慢加路径或者减少路径,
*最长:jdbc/src/main/resources/druid.properties
* 最短:druid.properties
*/
properties.load(new FileInputStream("src/main/resources/druid.properties"));
//4.获取连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
//5.获取连接对象
Connection connection = dataSource.getConnection();
//2.定义SQL语句
String sql = "insert into dept(id,name,create_time,update_time) values(?,?,?,?)";
//3.获取PreparedStatement对象
PreparedStatement pr = connection.prepareStatement(sql);
//4.设置参数
//定义变量
int id = 7;
String brandName = "创业部";
String createTime = "2020-01-01";
String updateTime = "2020-01-03";
pr.setInt(1, id);
pr.setString(2, brandName);
pr.setString(3, createTime);
pr.setString(4, updateTime);
//5.执行SQL语句
int count = pr.executeUpdate();
//6.处理结果
System.out.println(count > 0 ? "添加成功" : "添加失败");
//7.释放资源
pr.close();
connection.close();
}
/*
* 修改
* 1.sql:update dept set name = ? where id = ?;
* 2.参数:name
* 3.结果:boolean
*/
@Test
public void testUpdate() throws Exception {
//1.获取Connection对象
//3.加载配置文件
Properties properties = new Properties();
/*有时候会报错,路径的问题,慢慢加路径或者减少路径,
*最长:jdbc/src/main/resources/druid.properties
* 最短:druid.properties
*/
properties.load(new FileInputStream("src/main/resources/druid.properties"));
//4.获取连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
//5.获取连接对象
Connection connection = dataSource.getConnection();
//2.定义SQL语句
String sql = "update dept set name = ? where id = ?";
//3.获取PreparedStatement对象
PreparedStatement pr = connection.prepareStatement(sql);
//4.设置参数
//定义变量
int id = 7;
String brandName = "失业部";
pr.setString(1, brandName);
pr.setInt(2, id);
//5.执行SQL语句
int count = pr.executeUpdate();
//6.处理结果
System.out.println(count > 0 ? "修改成功" : "修改失败");
//7.释放资源
pr.close();
connection.close();
}
/*
* 删除
* 1.sql:delete from dept where id = ?;
* 2.参数:id
* 3.结果:boolean
*/
@Test
public void testDelete() throws Exception {
//1.获取Connection对象
//3.加载配置文件
Properties properties = new Properties();
/*有时候会报错,路径的问题,慢慢加路径或者减少路径,
*最长:jdbc/src/main/resources/druid.properties
* 最短:druid.properties
*/
properties.load(new FileInputStream("src/main/resources/druid.properties"));
//4.获取连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
//5.获取连接对象
Connection connection = dataSource.getConnection();
//2.定义SQL语句
String sql = "delete from dept where id = ?";
//3.获取PreparedStatement对象
PreparedStatement pr = connection.prepareStatement(sql);
//4.设置参数
//定义变量
int id = 7;
pr.setInt(1, id);
//5.执行SQL语句
int count = pr.executeUpdate();
//6.处理结果
System.out.println(count > 0 ? "删除成功" : "删除失败");
//7.释放资源
pr.close();
connection.close();
}
}
注意,这里的有着防止sql注入的方法解决
将其封装成工具类JDBCUtils
package com.lzy.util;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;
public class JDBCUtils {
private static DataSource dataSource = null;
static {
//1.获取Connection对象
//3.加载配置文件
Properties properties = new Properties();
/*有时候会报错,路径的问题,慢慢加路径或者减少路径,
*最长:jdbc/src/main/resources/druid.properties
* 最短:druid.properties
*/
try {
properties.load(new FileInputStream("src/main/resources/druid.properties"));
} catch (IOException e) {
throw new RuntimeException(e);
}
//4.获取连接池对象
try {
dataSource = DruidDataSourceFactory.createDataSource(properties);
/*不把这个也当成工具放进去是因为这里需要传值*/
//Connection connection = dataSource.getConnection();
} catch (Exception e) {
throw new RuntimeException(e);
}
}
/*对外提供获取连接的方法*/
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
/*对外提供关闭资源的方法*/
public static void close(Connection connection) throws SQLException {
connection.close();
}
}
使用工具类来进行增删改查
package com.lzy.test;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.lzy.pojo.Brand;
import com.lzy.util.JDBCUtils;
import org.junit.Test;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Properties;
public class BrandTest2 {
JDBCUtils jdbcUtils = new JDBCUtils();
/*
* 查询所有
* 1.sql:select * from dept;
* 2.参数:不需要
* 3.结果:返回一个List<Brand>集合
*/
@Test
public void testSelectAll() throws Exception {
//2.定义SQL语句
String sql = "select * from dept";
//3.获取PreparedStatement对象
Connection connection = jdbcUtils.getConnection();
PreparedStatement pr = connection.prepareStatement(sql);
//4.执行SQL语句
ResultSet resultSet = pr.executeQuery();
//5.处理结果集将结果集封装成Brand对象,然后放到集合
ArrayList<Brand> list = new ArrayList<>();
while (resultSet.next()) {
//获取数据
int id = resultSet.getInt("id");
String brandName = resultSet.getString("name");
String createTime = resultSet.getString("create_time");
String updateTime = resultSet.getString("update_time");
//封装成Brand对象
Brand brand = new Brand(id, brandName, createTime, updateTime);
//brand.setId(id);
//brand.setBrandName(brandName);
//brand.setCreatTime(createTime);
//brand.setUpdateTime(updateTime);
//装载集合
list.add(brand);
}
System.out.println(list);
//6.释放资源
resultSet.close();
pr.close();
jdbcUtils.close(connection);
}
}
优点:
1.将繁琐的重复性工作去掉
2。实现代码的复用