如何找当前文件的绝对路径？
package com.cskaoyan.path;
import java.io.File;
public class PathDemo {
public static void main(String[] args) throws FileNotFoundException {
//方法一：file.getAbsolutePath()
File file = new File("druid.properties");
System.out.println(file.getAbsolutePath());
//方法二：System.getProperty("user.dir")
System.out.println(System.getProperty("user.dir"));
}
}
··············································································································································································································
总结
JDBC:
概述
入门案例
API详解
Driver
|-- Connection connect(String url, Properties info)
DriverManger
|-- void registerDriver(Driver driver)
|-- Connection getConnection(String url, String user, String password)
Statement
|-- boolean execute()
|-- int executeUpdate()
|-- ResultSet executeQuery()
|-- void addBatch()
|-- void clearBatch()
|-- void executeBatch()
|-- void close()
注意事项：SQL注入问题
PrepareStatement
ResultSet
|-- getXXX(int index) 注意事项：MySQL中索引是从1开始
|-- getXXX(String columnLabel)
|-- boolean next()
|-- boolean previous()
|-- void afterLast()
|-- void beforeFirst()
|-- void absolute(int row)
|-- void close()
Connection
|-- Statement createStatement()
|-- PrepareStatement prepareStatement(String sql)
|-- void close()
|-- void setAutoCommit(boolean autoCommit)
|-- void commit()
|-- Savepoint setSavepoint()
|-- Savepoint setSavepoint(String name)
|-- void rollback()
|-- void rollback(Savepoint sp)
|-- void setTransactionIsolation(int level)
数据库连接池：
|-- DBCP
|-- C3P0
|-- Druid
··············································································································································································································
DbUtils
简介
commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库，它是对 JDBC 的简单封装，学习成本极低，使用 dbutils 可以简化 jdbc 编码的工作量，同时也不会影响程序的性能。
使用 DBUtils 需要导入 commons-dbutils-1.7.jar。DBUtils 有三个核心的组件：
- QueryRunner：该类提供了 DML 和 DQL 的 API。
- ResultSetHandler：该接口定义如何封装结果集。
- DbUtils：一个简单的工具类，简化了关闭资源和事务处理，可以简化JDBC操作的模板代码。
注意，配置文件中：
url=jdbc:mysql://localhost:3306/jdbc_db?useServerPrepStmts=true&cachePrepStmts=true&rewriteBatchedStatements=true
路径与参数之间以？分隔，参数与参数之间以&分隔
··············································································································································································································
- QueryRunner
package com.cskaoyan.dbutils;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.junit.Assert;
import org.junit.Test;
import javax.sql.DataSource;
import java.io.FileReader;
import java.io.IOException;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
/**
* @author shihao
* @create 2020-06-07 9:50
*/
public class QueryRunnerDemo {
//传入一个连接
@Test
public void testInsert1() throws SQLException {
Connection connec = getConnection();
QueryRunner queryRunner = new QueryRunner();
String sql = "insert into t_user(name,password,balance) values(?,?,?)";
int rows = queryRunner.update(connec, sql, "wade", "123456", 1000);
Assert.assertEquals(1, rows);
}
//传入一个数据库连接池
@Test
public void testInsert2() throws SQLException {
DataSource ds = getDataSource();
QueryRunner queryRunner = new QueryRunner(ds);
String sql = "insert into t_user(name,password,balance) values(?,?,?)";
int rows = queryRunner.update(sql, "wade", "123456", 1000);
Assert.assertEquals(1, rows);
}
@Test
public void testUpdate() throws SQLException {
DataSource ds = getDataSource();
QueryRunner queryRunner = new QueryRunner(ds);
String sql = "update t_user set balance = balance + 100 where id = ?";
int rows = queryRunner.update(sql, 1);
Assert.assertEquals(rows, 1);
}
@Test
public void testDelete() throws SQLException {
QueryRunner queryRunner = new QueryRunner(getDataSource());
String sql = "delete from t_user where id = ?";
int rows = queryRunner.update(sql, 1);
Assert.assertEquals(1, rows);
}
@Test
public void testQuery() throws SQLException {
QueryRunner queryRunner = new QueryRunner(getDataSource());
String sql = "select * from t_user where id = ?";
//我们希望把查询的记录封装成一个User对象
//OOM
User user = queryRunner.query(sql, new BeanHandler<>(User.class), 2);
System.out.println(user);
}
@Test
public void testBatch() throws SQLException {
QueryRunner runner = new QueryRunner(getDataSource());
String sql = "insert into t_user(name, password, balance) values(?, ?, ?)";
Object[][] params = new Object[500][3];
for (int i = 0; i < 500; i++) {
params[i] = new Object[]{"user" + i, "pwd" + i, i};
}
runner.batch(sql, params);
}
private Connection getConnection() {
Properties info = new Properties();
try (Reader reader = new FileReader("druid.properties")) {
info.load(reader);
} catch (IOException e) {
e.printStackTrace();
}
String url = info.getProperty("url");
String username = info.getProperty("user");
String password = info.getProperty("password");
Connection conn = null;
try {
conn = DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
private DataSource getDataSource() {
Properties info = new Properties();
DataSource ds = null;
try (Reader reader = new FileReader("druid.properties")) {
info.load(reader);
ds = DruidDataSourceFactory.createDataSource(info);
} catch (Exception e) {
e.printStackTrace();
}
return ds;
}
}
··············································································································································································································
- ResultSetHandler
该接口用于处理 ResultSet，将查询返回的 ResultSet 按要求转换为另一种形式。该接口的定义如下：
public interface ResultSetHandler<T> {
T handle(ResultSet rs) throws SQLException;
}
3.1 该接口的实现类
ArrayHandler：把结果集中的第一行数据转成对象数组。
ArrayListHandler：把结果集中的每一行数据都转成一个数组，再存放到List中。
BeanHandler：将结果集中的第一行数据封装到一个对应的JavaBean实例中。
BeanListHandler：将结果集中的每一行数据都封装到一个对应的JavaBean实例中，存放到List
里。
ColumnListHandler：将结果集中某一列的数据存放到List中。
KeyedHandler(name)：将结果集中的每一行数据都封装到一个Map里，再把这些map再存到一个
map里，其key为指定的key。
MapHandler：将结果集中的第一行数据封装到一个Map里，key是列名，value就是对应的值。
MapListHandler：将结果集中的每一行数据都封装到一个Map里，然后再存放到List。
package com.cskaoyan.dbutils;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import jdk.nashorn.internal.objects.annotations.Setter;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;
import org.junit.Test;
import javax.sql.DataSource;
import java.io.FileReader;
import java.io.Reader;
import java.sql.SQLException;
import java.util.*;
/**
* @author shihao
* @create 2020-06-07 11:34
*/
public class ResultSetHandlerDemo {
@Test
public void testArrayHandler() throws SQLException {
QueryRunner queryRunner = new QueryRunner(getDataSource());
String sql = "select * from t_user";
Object[] arr = queryRunner.query(sql, new ArrayHandler());
System.out.println(Arrays.toString(arr));
}
@Test
public void testArrayListHandler() throws SQLException {
QueryRunner queryRunner = new QueryRunner(getDataSource());
String sql = "select * from t_user";
List<Object[]> list = queryRunner.query(sql, new ArrayListHandler());
for (Object[] arr : list) {
System.out.println(Arrays.toString(arr));
}
}
//BeanHandler
//底层实现是反射，如果把User类中的无参构造方法删除的话，就无法创建对象了
//因为它是利用class.newInstance实现的，而调用newInstance调用的就是无参构造方法
//并且无参构造方法必须是public
//如果把User类中的setter方法删除后各个属性值都为null
//因为BeanHandler底层是通过setter方法设置参数的，并且setter方法必须是public
//无参构造方法创建对象，setter方法设置参数
//如何找到对应的setter方法的呢？
//通过方法名setId、setName...，方法名一变，它就找不到了，就没法赋值了
//从结果集中返回的id、name每个字段加上set，去找这个方法
//所以，要让setter方法和返回的结果集中的列名一样
/*使用 BeanHandler 和 BeanListHandler 需要注意以下几点：
1. 对应的 JavaBean 必须提供无参构造方法。
2. JavaBean 的setter方法名应该和返回结果的字段名相同。
3. JavaBean 中必须提供公共的 Setter 方法。*/
@Test
public void testBeanHandler() throws SQLException {
QueryRunner queryRunner = new QueryRunner(getDataSource());
String sql = "select * from t_user";
User user = queryRunner.query(sql, new BeanHandler<>(User.class));
System.out.println(user);
}
@Test
public void testBeanListHandler() throws SQLException {
QueryRunner queryRunner = new QueryRunner(getDataSource());
String sql = "select * from t_user";
List<User> users = queryRunner.query(sql, new BeanListHandler<>(User.class));
for (User user : users) {
System.out.println(user);
}
}
@Test
public void testColumnListHandler() throws SQLException {
QueryRunner queryRunner = new QueryRunner(getDataSource());
String sql = "select * from t_user";
List<Integer> ids = queryRunner.query(sql, new ColumnListHandler<>());
for(int id : ids) {
System.out.println(id);
}
}
@Test
public void testMapHandler() throws SQLException {
QueryRunner queryRunner = new QueryRunner(getDataSource());
String sql = "select * from t_user";
Map<String, Object> map = queryRunner.query(sql, new MapHandler());
System.out.println(map);
}
@Test
public void testMapListHandler() throws SQLException {
QueryRunner queryRunner = new QueryRunner(getDataSource());
String sql = "select * from t_user";
List<Map<String, Object>> maps = queryRunner.query(sql, new MapListHandler());
for(Map<String, Object> m : maps) {
System.out.println(m);
}
}
@Test
public void testKeyedHandler() throws SQLException {
QueryRunner queryRunner = new QueryRunner(getDataSource());
String sql = "select * from t_user";
Map<Integer, Map<String, Object>> map = queryRunner.query(sql, new KeyedHandler<>("id"));
Set<Map.Entry<Integer, Map<String, Object>>> entries = map.entrySet();
for(Map.Entry<Integer, Map<String, Object>> e : entries) {
int key = e.getKey();
Map<String, Object> value = e.getValue();
System.out.println(key + "=" + value);
}
}
private DataSource getDataSource() {
Properties info = new Properties();
DataSource ds = null;
try (Reader reader = new FileReader("druid.properties")) {
info.load(reader);
ds = DruidDataSourceFactory.createDataSource(info);
} catch (Exception e) {
e.printStackTrace();
}
return ds;
}
}
··············································································································································································································
4. DbUtils
提供如关闭连接、装载JDBC驱动程序等常规工作的工具类，里面的所有方法都是静态的。
package com.cskaoyan.dbutils;
import org.apache.commons.dbutils.DbUtils;
import org.junit.Assert;
import org.junit.Test;
import java.io.FileReader;
import java.io.IOException;
import java.io.Reader;
import java.sql.*;
import java.util.Properties;
public class DbUtilsDemo {
@Test
public void testQueryData1() {
String sql = "select * from t_a";
//刚开始得赋值为null，因为后面close时需要在一个对象上close，如果try块中出现问题没有赋值成功
//就会报空指针异常
//如果这三个对象不在这定义而是定义在try块中，同样无法close，以为定义在try块中是try块中的局部变量
//在finally中访问不到的
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
} finally {
//非空才能close
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
public void testQueryData2() {
String sql = "select * from t_a";
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
} finally {
DbUtils.closeQuietly(conn, stmt, rs);
}
}
private Connection getConnection() {
Properties info = new Properties();
try (Reader reader = new FileReader("db.properties")) {
info.load(reader);
} catch (IOException e) {
e.printStackTrace();
}
String url = info.getProperty("url");
String user = info.getProperty("user");
String password = info.getProperty("password");
Connection conn = null;
try {
conn = DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
}
扫一扫
771
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
