package com.sise.util;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStream;
import java.net.URL;
import java.sql.*;
import java.util.Properties;
/**
* 工具类的作用:简化繁琐重复的代码
* 抽取:连接数据库和释放资源(重复性高)
*
*/
public class JdbcUtil {
/**
* 注意因为静态代码块、静态方法只能访问静态成员变量
*/
private static String url = null;
private static String user = null;
private static String password = null;
private static String driver=null;
/**
* 如果把读取文件代码写到getConnection()方法中,那么每次调用
* getConnection()时都会读取文件,则影响效率,所以把读取文件
* 代码写到外面,只需要读取一次即可,提高效率
*
* 怎样实现呢?可以使用静态代码块,因为它会随着类的加载而加载,所以只会
* 执行一次
* @return
* @throws Exception
*/
static{
try {
//url表示统一资源定位符,可以定位一个文件的绝对路径
URL resource = JdbcUtil.class.getClassLoader().getResource("properties.properties");
//获取路径
String path = resource.getPath();
Properties p=new Properties();
p.load(new FileReader(path));
url = p.getProperty("url");
user = p.getProperty("user");
password = p.getProperty("password");
//注册驱动
Class.forName(p.getProperty("Driver"));
} catch (IOException e) {
e.printStackTrace();
}catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws Exception {
return DriverManager.getConnection(url, user, password);
}
/**
* DML需要释放两个资源:Connection c, Statement s
*
* @param c
* @param s
*/
public static void close(Connection c, Statement s) {
if (s != null) {
try {
s.close();
/*c.close();不要这样写,因为假如s.close()报错则直接catch,就不会执行c.close()则导致
资源的浪费
*/
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (c != null) {
try {
c.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
/**
* DQL需要释放三个资源:Connection c, Statement s, ResultSet r
*
* @param c
* @param s
* @param r
*/
public static void close(Connection c, Statement s, ResultSet r) {
if (r != null) {
try {
r.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (s != null) {
try {
s.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (c != null) {
try {
c.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
package com.sise.jdbc;
import com.sise.emp.Student;
import com.sise.util.JdbcUtil;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class JdbcUtilTest {
public static void main(String[] args) {
List<Student> students = new JdbcTest4().findAll();
System.out.println(students);
}
public List<Student> findAll() {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
List<Student> list = null;
try {
connection = JdbcUtil.getConnection();
String sql = "select * from student";
statement = connection.createStatement();
resultSet = statement.executeQuery(sql);
Student stu = null;
list = new ArrayList<>();
while (resultSet.next()) {
stu = new Student();
stu.setId(resultSet.getInt("id"));
stu.setName(resultSet.getString("name"));
stu.setCls_id(resultSet.getInt("cls_id"));
stu.setScore(resultSet.getInt("score"));
list.add(stu);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
//为了避免执行失败,导致调用方法时出现空指针异常
//先判断再释放
//谁最后使用谁就最先释放
JdbcUtil.close(connection, statement);
}
return list;
}
}