day33、封装BaseDao
通过自己封装的类,对数据库进行增删改查
1.先复制JdbcUtil这个工具类到咱们的utils文件夹中
2.复制db.properties到src文件夹下面
3.在src下面新建lib文件夹 将mysqljar包导入当前工程中
BaseDao代码如下:
public class BaseDao {
public int updata(String sql, Object[] objs){
//创建数据库连接
Connection connection = JdbcUtil.getConnection();
//预处理搬运工对象
PreparedStatement preparedStatement = null;
try {
preparedStatement = connection.prepareStatement(sql);
//处理sql参数
//要获取参数元数据
//处理参数
int count = preparedStatement.getParameterMetaData().getParameterCount();
if(objs != null && count == objs.length){
for (int i = 1; i <= count; i++) {
preparedStatement.setObject(i, objs[i-1]);
}
}
int i = preparedStatement.executeUpdate();
return i;
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtil.close(preparedStatement, connection);
}
return 0;
}
public <T> List<T> query (String sql, Object[] objs, Class<T> cls){
//获取数据库连接
Connection connection = JdbcUtil.getConnection();
//获得预处理搬运工对象
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
//新建一个返回的集合
List<T> list = new ArrayList<>();
try {
preparedStatement = connection.prepareStatement(sql);
//获得参数元数据
ParameterMetaData parameterMetaData = preparedStatement.getParameterMetaData();
//获得参数个数
int parameterCount = parameterMetaData.getParameterCount();
//处理sql
if(objs != null && parameterCount == objs.length){
for (int i = 1; i <= parameterCount; i++) {
preparedStatement.setObject(i, objs[i-1]);
}
}
//执行,获得结果集
resultSet = preparedStatement.executeQuery();
//获得结果集元数据
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
while(resultSet.next()){
T t = cls.getDeclaredConstructor(null).newInstance(null);
for (int i = 1; i <= columnCount ; i++) {
String columnName = metaData.getColumnName(i);
System.out.println(columnName);
System.out.println(resultSet.getObject(columnName));
BeanUtils.setProperty(t, columnName, resultSet.getObject(columnName));
}
list.add(t);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JdbcUtil.close(resultSet, preparedStatement, connection);
}
return list.size() != 0 ? list: null;
}
}
day34、连接池
1、连接池
在代码中使用连接池的步骤
1、导包 druid的jar
2、在src下面的去新建一个druid.properties配置文件
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/java2304?useSSL=false
username=root
password=123456
initialSize=5
maxActive=10
maxWait=2000
3、写核心类
//读取druid.properties
Properties properties = new Properties();
//以IO流的方式,读取配置信息
properties.load(new FileInputStream("src/druid.properties"));
//创建Druid的核心类, druid数据源的工厂
//DataSource 数据源中 是获取配置文件的信息,并赋值给数据源
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
//获取连接数据库的对象
Connection connetion = dataSource.getConnetion();
4、将之前封装的JdbcUtil改成连接池的写法
public class JdbcUtil {
private static DataSource dataSource = null;
static {
try {
//Properties类表示一组持久的属性
Properties properties = new Properties();
//读取Druid.properties
properties.load(new FileInputStream("src/Druid.properties"));
//dataSource 数据源工厂,获取了配置文件中的数据,并赋值给数据源
dataSource = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection(){
Connection connection = null;
try {
connection = dataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
public static void close (Connection connection) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void close (Statement statement, Connection connection) {
try {
statement.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void close (ResultSet resultSet, Statement statement, Connection connection) {
try {
resultSet.close();
statement.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
2、JDBC中的事务
对应sql语句中的
set autocommit = 0;
commit;
rollback;
//1.关闭自动提交
connection.setAutoCommit(false);
//提交方法
connection.commit();
//回滚方法
connection.rollback();
3、DBUtils框架
是轻量级持久层框架,apache组织创建的一个框架,已经没人维护了。
使用步骤
1.导包 dbUtils.jar
2.直接使用核心类 QueryRunner 即可
主要代码如下:
//查询
public static void main(String[] args) throws SQLException {
//连接数据库
Connection connection = JdbcUtil.getConnection();
//DBUtils核心类 QueryRunner
QueryRunner queryRunner = new QueryRunner();
String sql = "select * from examStudent";
List<Examstudent> query = queryRunner.query(connection, sql, new BeanListHandler<Examstudent>(Examstudent.class));
System.out.println(query);
}
//更新
public static void main(String[] args) throws SQLException {
Connection connection = JdbcUtil.getConnection();
QueryRunner queryRunner = new QueryRunner();
String sql = "insert into work (name, age, info) values(?, ?, ?)";
//update(Connection conn, String sql, Object... params)
int update = queryRunner.update(connection, sql, "小猫", 56, "南山南");//
System.out.println(update);
}