带配置文件的工具类
public class DBUtil {
//声明成成员变量 方便使用
private static Connection connection;
private static String driverClass;
private static String url;
private static String username;
private static String password;
static {
readFile();
//注册驱动
try {
Class.forName(driverClass);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//读文件的方法
public static void readFile() {
//读文件
//类加载器 直接获取bin文件夹下的该文件
InputStream inputStream = DBUtil.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties=new Properties();
try {
properties.load(inputStream);
driverClass= properties.getProperty("driverClass");
url= properties.getProperty("url");
username= properties.getProperty("username");
password= properties.getProperty("password");
} catch (IOException e) {
e.printStackTrace();
}
}
//获取连接方法
public static Connection getConnection() {
try {
connection=DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException("获取连接失败");
}
return connection;
}
}
DBUtil 第三方类库
数据库的工具类
简化数据库操作的代码和jdbc连接的代码
操作数据库的三个核心类
1.DBUtil 关闭数据库
closeQuietly 不会抛出SQLException异常
2.QueryRunner 查询对象
update(Connection conn, String sql, Object... params)
传入的sql语句可以只用占位符?
通过参数3 把替换?的值传进去
3.ResultSetHandle 查询结果的返回集
接口需要使用它的实现类(8个)
插入一条数据:
QueryRunner qr=new QueryRunner();
String sql ="insert into goods values(null,?,?,?)";
Object[] params = {"糖",4,"好吃不贵"};
int row = qr.update(DBUtil.getConnection(), sql, params);
System.out.println(row);
DbUtils.closeQuietly(DBUtil.getConnection());
使用arrayhandle
QueryRunner qr=new QueryRunner();
String sql="select * from goods";
Object[] query = qr.query(DBUtil.getConnection(), sql, new ArrayHandler());
使用arrayhandle类 会返回数据库中的一条记录 并且会把该记录的字段放入object数组中
for (Object object : query) {
System.out.println(object);
}
测试ArrayListHandel
QueryRunner qr=new QueryRunner();
String sql="select * from goods";
集合中每一个object[] 就是数据库中的一条记录
List<Object[]> query = qr.query(DBUtil.getConnection(), sql, new ArrayListHandler());
for (Object[] objects : query) {
for (Object object : objects) {
System.out.print(object);
}
System.out.println();
}
测试beanhandle
可以将查询结果放入实体对象中
QueryRunner qr=new QueryRunner();
String sql="select * from goods";
参数是放入对象的实体类的.class文件类型
Goods query = qr.query(DBUtil.getConnection(), sql, new BeanHandler<Goods>(Goods.class));
System.out.println(query);
测试beanlisthandle
QueryRunner qr = new QueryRunner();
String sql = "select * from goods";
List<Goods> query = qr.query(DBUtil.getConnection(), sql, new BeanListHandler<>(Goods.class));
for (Goods goods : query) {
System.out.println(goods);
}
测试columnlisthandle
可以将查询结果放入实体对象中
QueryRunner qr = new QueryRunner();
String sql = "select * from goods";
数据库中一列数据放入集合中返回
List<Object> query = qr.query(DBUtil.getConnection(), sql, new ColumnListHandler<>("sname"));
for (Object object : query) {
System.out.println(object);
}
测试scalarhandler
QueryRunner qr = new QueryRunner();
String sql = "select count(*) from goods";
该方法返回类型是long
Long query = qr.query(DBUtil.getConnection(), sql, new ScalarHandler<Long>());
System.out.println(query);
测试maphandler
QueryRunner qr = new QueryRunner();
String sql = "select * from goods";
Map<String, Object> query = qr.query(DBUtil.getConnection(), sql, new MapHandler());
for (String key : query.keySet()) {
System.out.println(key+" "+query.get(key));
}
测试maplisthandler
QueryRunner qr = new QueryRunner();
String sql = "select * from goods";
List<Map<String,Object>> query = qr.query(DBUtil.getConnection(), sql, new MapListHandler());
for (Map<String, Object> map : query) {
for (String key : map.keySet()) {
System.out.print(key+" "+map.get(key));
}
System.out.println();
}
datasourse数据源
数据库连接池中保存了许多的连接(connection)
在操作数据库时 最耗费资源的是创建连接和销毁连接
当用户查询数据库时 从连接池取出一个使用 用完后重新放入连接池
java为连接池准备了一套规范(接口)
javax.sql扩展包
这套规范也是由数据库厂商实现 该类也提供获取连接的方法
常用的数据库连接池:
DBCP C3P0
利用数据库连接池获取连接
BasicDataSource dataSource=new BasicDataSource();
基础设置
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/myjdbc01");
dataSource.setUsername("root");
dataSource.setPassword("123456");
获取连接
Connection connection = dataSource.getConnection();
System.out.println(connection);
测试向queryrunner中传入数据源
传入数据源后 就不用考虑连接的事 直接调用查询
QueryRunner qr=new QueryRunner(DatasourseUitl.getDataSourse());
String sql="select * from goods";
List<Goods> query = qr.query(sql, new BeanListHandler<>(Goods.class));
for (Goods goods : query) {
System.out.println(goods);
}