01 Druid 連接池
1.手動設(shè)置參數(shù)的方法
@Test
// 手動設(shè)置參數(shù)的方法:
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);
// 執(zhí)行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);
// 執(zhí)行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.手動設(shè)置鏈接代碼
@Test
// 手動設(shè)置參數(shù)的方法:
public void demo1(){
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
// 核心類:
ComboPooledDataSource dataSource = new ComboPooledDataSource();
// 手動設(shè)置參數(shù):
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);
// 執(zhí)行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);
// 執(zhí)行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 ：核心執(zhí)行類
ResultSetHandler ：提供對查詢結(jié)果封裝
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:將查詢到的一條記錄封裝到數(shù)組當(dāng)中
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：
將多條記錄進(jìn)行封裝，一條記錄封裝成一個(gè)數(shù)組拗踢，多條記錄封裝成一個(gè)裝有數(shù)組的集合
@Test
// ArrayListHandler:? 將多條記錄進(jìn)行封裝母市，一條記錄封裝成一個(gè)數(shù)組桐腌，多條記錄封裝成一個(gè)裝有數(shù)組的集合
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
將一條記錄封裝到一個(gè)JavaBean中
JavaBean就是滿足了一定格式的Java類 屬性私有化
提供無參數(shù)的構(gòu)造
對私有屬性提供public的get和set方法
@Test
// BeanHandler:將一條記錄封裝到一個(gè)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
將多條記錄封裝到一個(gè)裝有JavaBean的List集合中
@Test
// BeanListHandler:將多條記錄封裝到一個(gè)裝有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
將一條記錄封裝成一個(gè)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
將多條記錄進(jìn)行封裝庵寞，一條記錄封裝成一個(gè)Map，多條記錄封裝成一個(gè)裝有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的事務(wù)管理
在Connection對象中有對事務(wù)管理操作的一組API：
setAutoCommit(boolean flag);
commit();
rollback();
事務(wù)管理：
@Test
// 基本轉(zhuǎn)賬環(huán)境
public void demo1(){
Connection conn = null;
PreparedStatement pstmt = null;
try{
// 獲得連接:
conn = JDBCUtils.getConnection();
###ＱΨ恕捐川！// 開啟事務(wù):（***重點(diǎn)***）
conn.setAutoCommit(false);
// 編寫SQL:
String sql = "update account set money = money + ? where name = ?";
// 預(yù)編譯SQL:
pstmt = conn.prepareStatement(sql);
// 設(shè)置參數(shù)：
// 扣除劉立 1000
pstmt.setDouble(1, -1000);
pstmt.setString(2, "劉立");
pstmt.executeUpdate(); // 扣錢
int d = 1 / 0;
// 給鳳姐加 1000
pstmt.setDouble(1, 1000);
pstmt.setString(2, "鳳姐");
pstmt.executeUpdate(); // 加錢
###！逸尖！// 提交事務(wù):（***重點(diǎn)***）
conn.commit();
}catch(Exception e){
###９帕ぁ瘸右！// 回滾事務(wù):（***重點(diǎn)***）
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
}finally{
// 釋放資源:
JDBCUtils.release(pstmt, conn);
}
}
