七、封装BaseDao类
这个类是对数据库的数据进行增删改查的 封装到utils包下
本类中封装了两个方法
1.增删改
2.查询
1.先复制JdbcUtil这个工具类到咱们的utils文件夹中
2.复制db.properties到src文件夹下面
3.在src下面新建lib文件夹 将mysqljar包导入当前工程中
1.封装增删改方法
public class BaseDao {
/**
* 增删改
* @param sql 需要传入的sql语句
* @param parameters 对sql的?进行赋值
* @return 受影响的行数
*/
public int update (String sql, Object[] parameters) {
Connection connection= JdbcUtil.getConnection();
PreparedStatement preparedstatement =
connection.preparedStatement(sql);
int parameterCount = preparedStatement.getParameterMetaData().getParameterCount();//获取参数个数
//判断,如果参数个数不为空 并且 参数数组长度等于参数个数
if(parameters!=null&¶meters.length==parameterCount){//满足以上条件,同意赋值
for (int i = 1; i <= parameterCount; i++) {
preparedStatement.setObject(i, parameters[i - 1]);
}
}
int i = preparedStatement.executeUpdate();
JdbcUtil.close(preparedStatement,connection);
return i ;
}
2.封装查询方法
public class BaseDao {
/**
*查询方法
* @param sql 需要传入的sql语句
* @param parameters sql语句的参数
* @param cls 查询出来的数据赋值给一个类对象
* @param <T> 类对象
* @return 返回一个List<T>数组 泛型是一个类对象
*/
public <T> List<T> query (String sql, Object[] parameters, Class<T> cls) {
Connection connection =JdbcUtil.getConnection();
PreparedStatement preparedStatement =
connection.preparedStatement(sql);
ResultSet resultSet=preparedStatement.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
List<T> list = new ArrayList<>();
while(resultSet.next()){
T t = cls.getConstrutor(null).newInstance(null);
for (int i = 1; i <= columnCount; i++) {
String columnName = metaData.getCountName(i);
Object valus = resultSet.getObject(columnName);
//通过BeanUtils给对象取值赋值
// BeanUtils.setProperty(对象,属性,值);
BeanUtils.setProperty(t,columnName,value);
}
list.add(t);
}
JdbcUtil.close(resultSet, preparedStatement, connection);
return list.size() !=0 ? list : null;
}
八、连接池
在创建Connection对象以后,用完以后不要关闭资源。把它放到连接池中,再做其他的增删改查时,可以直接从连接池取出连接对象,不再需要重复的关闭和重新连接。
1.导包 druid的jar包
2.在src文件下面去新建一个druid.properties配置文件
配置内容:连接驱动、url、user、password
连接池初始化容量、连接池最大容量、等待时间。
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/java2304?useSSL=false
username=root
password=123456
initialSize=5
maxActive=10
maxWait=2000
1.使用连接池的JdbcUtil封装类
public class JdbcUtil {
private static DataSource dataSource = null;
static{
//1.读取druid.properties文件
Properties properties = new Properties();
//2.以流的形式进行操作
properties.load(new FileInputStream("src/druid.properties"));
//3.创建Druid的核心类 druid数据源的工厂
//dataSource 数据源中获取了配置文件的数据并赋值给数据源
DataSource dataSource =
DruidDataSourceFactory.createDataSource(properties);
}
//4.获取连接数据库的对象
public static Connection getConnection(){
Connection connection = dataSource.getConnection();
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();
}
}
}
九、JDBC中的事务
事务牵涉到增删改
SQL语句中的事务
set autocommit = 0;
sql语句1
sql语句2
...
rollback; 回滚命令 or commit; 执行命令
案例场景:转钱事件
sql: update user set balance = balance - 100 where id = 2
sql: update user set balance = balance + 100 where id = 1
public class Demo1 {
public static void main(String[] args) {
Connection connection = JdbcUtil.getConnection(); //1.关闭自动提交
connection.setAutoCommit(false);
//sql语句1
try{
String sql1 = "update user set balance = balance - 100 where id =?";
PreparedStatement preparedStatement1 = connection.PrepareStatement(sql);
preparedStatement1.setObject(1,2);
int i1 = preparedStatement1.executeUpdate();
preparedStatement1.close();
//int i2 = 10/0; 在此时如果运行报错,下面语句不执行,但是上边的语句已经执行。
//sql语句2
String sql2 = "update user set balance = balance + 100 where id =?";
PreparedStatement preparedStatement2 = connection.PrepareStatement(sql);
preparedStatement2.setObject(1,1);
int i2 = preparedStatement2.executeUpdate();
preparedStatement2.close();
connection.commit();//将全部sql语句提交
} catch (Exception e) {
try {
connection.rollback();//当异常时回滚
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
}
十、DBUtils框架
轻量级持久层框架,可以对数据库的数据进行增删改查(CURD)
创建(Create)、更新(Update)、读取(Read)和删除(Delete)操作
1.导包 dbUtils.jar
2.直接核心类即可
这个框架仅仅是用来处理增删改查的
1.增删改
public class Demo1 {
public static void main(String[] args) throws SQLException {
//1.连接数据库
Connection connection = JdbcUtil.getConnection();
//2.DBUtils核心类 QueryRunner
QueryRunner queryRunner = new QueryRunner();
String sql = "insert into work (name, age, info) values(?, ?, ?)";
//update(Connection conn, String sql, Object... params)
int i = queryRunner.update(connection,sql,"小猫", 56, "南山南");
}
}
2.查询一条数据
public class Demo3 {
public static void main(String[] args) throws SQLException {
//1.连接数据库
Connection connection = JdbcUtil.getConnection();
//2.QueryRunner 核心类
QueryRunner queryRunner = new QueryRunner();
String sql = "select * from work where id = ?";
Work work = queryRunner.query(connection,sql,7,
new ResultSetHandler<Work>(){
Work work1 = null;
@Override
public Work handle(ResultSet resultSet) throws SQLException {
while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
int age = resultSet.getInt("age");
String info = resultSet.getString("info");
work1 = new Work(id, name, age, info);
}
return work1;
}
});
System.out.println(work);
}
}