01 Druid 连接池
1.手动设置参数的方法
@Test
// 手动设置参数的方法:
public void demo1(){
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql:///day04");
dataSource.setUsername("root");
dataSource.setPassword("123");
try{
// 获得连接:
conn = dataSource.getConnection();
// 编写SQL：
String sql = "select * from account";
pstmt = conn.prepareStatement(sql);
// 执行sql:
rs = pstmt.executeQuery();
while(rs.next()){
System.out.println(rs.getInt("id")+" "+rs.getString("name")+" "+rs.getDouble("money"));
}
}catch(Exception e){
e.printStackTrace();
}finally{
JDBCUtils.release(rs, pstmt, conn);
}
}
2.使用配置文件 .properties 文件使用Properties 类来处理
@Test
public void demo2(){
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
Properties properties = new Properties();
properties.load(new FileInputStream("src/druid.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
// 获得连接:
conn = dataSource.getConnection();
// 编写SQL：
String sql = "select * from account";
pstmt = conn.prepareStatement(sql);
// 执行sql:
rs = pstmt.executeQuery();
while(rs.next()){
System.out.println(rs.getInt("id")+" "+rs.getString("name")+" "+rs.getDouble("money"));
}
}catch(Exception e){
e.printStackTrace();
}finally{
JDBCUtils.release(rs, pstmt, conn);
}
}
02 C3P0连接池
1.手动设置链接代码
@Test
// 手动设置参数的方法:
public void demo1(){
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
// 核心类:
ComboPooledDataSource dataSource = new ComboPooledDataSource();
// 手动设置参数:
dataSource.setDriverClass("com.mysql.jdbc.Driver");
dataSource.setJdbcUrl("jdbc:mysql:///day04");
dataSource.setUser("root");
dataSource.setPassword("123");
// 获得连接:
conn = dataSource.getConnection();
// 编写SQL：
String sql = "select * from account";
pstmt = conn.prepareStatement(sql);
// 执行sql:
rs = pstmt.executeQuery();
while(rs.next()){
System.out.println(rs.getInt("id")+" "+rs.getString("name")+" "+rs.getDouble("money"));
}
}catch(Exception e){
e.printStackTrace();
}finally{
JDBCUtils.release(rs, pstmt, conn);
}
}
2.使用配置文件添加
@Test
// 配置文件的方式
public void demo2(){
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
// 核心类:
ComboPooledDataSource dataSource = new ComboPooledDataSource();
// 获得连接:
conn = dataSource.getConnection();
// 编写SQL：
String sql = "select * from account";
pstmt = conn.prepareStatement(sql);
// 执行sql:
rs = pstmt.executeQuery();
while(rs.next()){
System.out.println(rs.getInt("id")+" "+rs.getString("name")+" "+rs.getDouble("money"));
}
}catch(Exception e){
e.printStackTrace();
}finally{
JDBCUtils.release(rs, pstmt, conn);
}
}
03 DBUtils 使用
查看核心API：
QueryRunner ：核心执行类
ResultSetHandler ：提供对查询结果封装
DbUtils ：工具类
@Test
简单使用DBUils
public void demo1() throws SQLException{
QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());
String sql = "insert into account values (null,?,?)";
queryRunner.update(sql, "刘如花",10000);
}
1.添加
@Test
// 保存操作
public void demo1() throws SQLException{
QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());
String sql ="insert into account values (null,?,?)";
queryRunner.update(sql, "aaa",10000);
}
2.修改
@Test
// 修改操作
public void demo2() throws SQLException{
QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());
String sql ="update account set name=?,money=? where id =?";
queryRunner.update(sql, "bbb",20000,4);
}
3.删除
@Test
// 删除操作
public void demo3() throws SQLException{
QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());
String sql ="delete from account where id =?";
queryRunner.update(sql, 4);
}
4.查询
ArrayHandler：
@Test
// ArrayHandler:将查询到的一条记录封装到数组当中
public void demo1() throws SQLException{
QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());
String sql = "select * from account where id = ?";
Object[] objs = queryRunner.query(sql, new ArrayHandler(), 1);
System.out.println(Arrays.toString(objs));
}
ArrayListHandler：
将多条记录进行封装，一条记录封装成一个数组，多条记录封装成一个装有数组的集合
@Test
// ArrayListHandler: 将多条记录进行封装，一条记录封装成一个数组，多条记录封装成一个装有数组的集合
public void demo2() throws SQLException{
QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());
String sql = "select * from account";
List<Object[]> list = queryRunner.query(sql, new ArrayListHandler());
for (Object[] objects : list) {
System.out.println(Arrays.toString(objects));
}
}
BeanHandler
将一条记录封装到一个JavaBean中
JavaBean就是满足了一定格式的Java类 属性私有化
提供无参数的构造
对私有属性提供public的get和set方法
@Test
// BeanHandler:将一条记录封装到一个javaBean中的
public void demo3()throws SQLException{
QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());
String sql = "select * from account where id = ?";
Account account = queryRunner.query(sql, new BeanHandler<>(Account.class), 1);
System.out.println(account);
}
BeanListHandler
将多条记录封装到一个装有JavaBean的List集合中
@Test
// BeanListHandler:将多条记录封装到一个装有JavaBean的List集合中
public void demo4()throws SQLException{
QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());
String sql = "select * from account";
List<Account> list = queryRunner.query(sql, new BeanListHandler<>(Account.class));
for (Account account : list) {
System.out.println(account);
}
}
MapHandler
将一条记录封装成一个Map集合，Map的key是字段名称，Map的value是字段的值。
@Test
// MapHandler:封装一条记录到Map中
public void demo5()throws SQLException{
QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());
String sql = "select * from account where id = ?";
Map<String,Object> map = queryRunner.query(sql, new MapHandler() ,2);
System.out.println(map);
}
MapListHandler
将多条记录进行封装，一条记录封装成一个Map，多条记录封装成一个装有Map的List集合中
@Test
// MapListHandler:
public void demo6()throws SQLException{
QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());
String sql = "select * from account";
List<Map<String,Object>> list = queryRunner.query(sql, new MapListHandler());
for (Map<String, Object> map : list) {
System.out.println(map);
}
}
ColumnListHandler
@Test
// ColumnListHandler
public void demo7()throws SQLException{
QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());
String sql = "select name from account";
List<Object> list = queryRunner.query(sql, new ColumnListHandler());
for (Object object : list) {
System.out.println(object);
}
}
ScalarHandler
@Test
//ScalarHandler：单值查询
public void demo8()throws SQLException{
QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());
String sql = "select count(*) from account";
Long count = (Long) queryRunner.query(sql, new ScalarHandler());
System.out.println(count);
}
KeyedHandler
@Test
// KeyedHandler:
public void demo9()throws SQLException{
QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());
String sql = "select * from account";
Map<Object,Map<String,Object>> map= queryRunner.query(sql, new KeyedHandler("name"));
for (Object key : map.keySet()) {
System.out.println(key + " "+map.get(key));
}
}
04 JDBC的事务管理
在Connection对象中有对事务管理操作的一组API：
setAutoCommit(boolean flag);
commit();
rollback();
事务管理：
@Test
// 基本转账环境
public void demo1(){
Connection conn = null;
PreparedStatement pstmt = null;
try{
// 获得连接:
conn = JDBCUtils.getConnection();
###！！// 开启事务:（***重点***）
conn.setAutoCommit(false);
// 编写SQL:
String sql = "update account set money = money + ? where name = ?";
// 预编译SQL:
pstmt = conn.prepareStatement(sql);
// 设置参数：
// 扣除刘立 1000
pstmt.setDouble(1, -1000);
pstmt.setString(2, "刘立");
pstmt.executeUpdate(); // 扣钱
int d = 1 / 0;
// 给凤姐加 1000
pstmt.setDouble(1, 1000);
pstmt.setString(2, "凤姐");
pstmt.executeUpdate(); // 加钱
###！！// 提交事务:（***重点***）
conn.commit();
}catch(Exception e){
###！！// 回滚事务:（***重点***）
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
}finally{
// 释放资源:
JDBCUtils.release(pstmt, conn);
}
}
