文章目录
JDBC中自定义工具类
Java数据库连接后的大部分操作都需要经过
定义需要的工具类对象(定义变量)
加载驱动
定义用户名密码及url来获得链接
得到(预)状态通道、(预状态通道则还需要绑定参数)
增删改查
关闭资源
…
等步骤,我们可以将这些重复步骤代码以方法的形式进行封装
方法封装
新建工具包util,包中新建类DBUtils,在类中将上述操作的重复代码以方法的形式进行封装
定义需要的工具类对象(定义变量)
(子类中方法的访问权限不能比父类中的访问权限低)
protected Connection connection = null;
protected PreparedStatement pps = null;//后续都是用预状态通道来实现
protected ResultSet rs = null;//结果集
protected int count = 0;//受影响的行数
加载驱动
静态代码块在类加载到内存中时便执行
static {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
定义用户名密码及url来获得链接
//登录的用户名和密码
private String username = "root";
private String password = "123456";
//url地址
private String url = "jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT";
protected Connection getConnection() {
try {
connection = DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
得到预状态通道
protected PreparedStatement getPps(String sql) {
try {
getConnection();
pps = connection.prepareStatement(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return pps;
}
绑定参数
给占位符赋值,list中保存的是给占位符所赋的值
private void setParams(List list) {
try {
if (list != null && list.size() > 0) {//集合中有内容
for (int i = 0; i < list.size(); i++) {
pps.setObject(i + 1, list.get(i));//赋值,位置从1开始所以为i+1
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
增删改
protected int update(String sql, List list) {
try {
getPps(sql);//得到预状态通道
setParams(list);//绑定参数
count = pps.executeUpdate();//pps.executeUpdate()执行sql语句,返回受影响的行数
} catch (SQLException e) {
e.printStackTrace();
}
return count;//返回受影响的行数
}
查询
protected ResultSet query(String sql, List list) {
try {
getPps(sql);//得到预状态通道
setParams(list);//绑定参数
rs = pps.executeQuery();//pps.executeUpdate()执行sql语句,返回结果集
return rs;//返回结果集
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
关闭资源
protected void closeAll() {
try {
if (rs != null) {
rs.close();
}
if (pps != null) {
pps.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
应用
例1
以student表的通过学生id查学生信息为例
DaoImplements类除了实现Dao接口,还要继承DBUtil类
public Student getByStuId(int stuid) {
Student student = new Student();
try {
String sql = "select* from student where stuid = ?";
List list = new ArrayList();
list.add(stuid);
ResultSet query = query(sql, list);//使用封装的查询方法
while(rs.next()){
student.setStuId(rs.getInt("stuid"));
student.setStuName(rs.getString("stuname"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
closeAll();//使用封装的关闭资源方法
}
return student;
}
编写测试类Test
public class Test{
public static void main(String[] args) {
DaoImplements dao = new DaoImplements();
Student student = dao.getByStuId(1);//查询id为1的学生
System.out.println(student.getStuId() + "," + student.getStuName());
}
}
输出结果:
1,张三
例2
以非反射处理结果集和反射处理结果集为例,原替换的代码用/**/注释
反射处理结果集内容可阅读文章:JDBC中批处理及通过反射处理结果集
DaoImplements类除了实现Dao接口,还要继承DBUtil类
非反射处理:
@Override
public List<Student> getAllStudent() {
/*
Connection connection = null;
PreparedStatement pps = null;
ResultSet resultSet = null;
*/
try {
/*
//1加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2获得链接
String userName = "root";
String passWord = "123456";
String url = "jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT";
connection = DriverManager.getConnection(url, userName, passWord);
//3定义sql,创建预状态通道(进行sql语句的发送)
String sql = "select * from student ";
pps = connection.prepareStatement(sql);
//执行sql
resultSet = pps.executeQuery();
*/
List<Student> students = new ArrayList<>();//创建学生集合
String sql = "select * from student ";
List list = new ArrayList();
query(sql,null);//查询
//取值
while(rs.next()){
Student student = new Student();
student.setStuId(rs.getInt("stuid"));
student.setStuName(rs.getString("stuname"));
student.setTeacherid(rs.getInt("teacherid"));
students.add(student);
}
return students;
}catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
closeAll();//关闭资源
}
return null;
}
编写测试类Test
public class Test{
public static void main(String[] args) {
DaoImplements dao = new DaoImplements();
List<Student> allStudent2 =dao.getAllStudent2();
for (Student student : allStudent) {
System.out.println(student.getStuId()+","+student.getStuName()+","+student.getTeacherid());
}
}//end main
}//end class
运行输出结果:
1,张三,3
2,李四,1
3,王五,3
4,赵六,1
5,花花,1
6,潇潇,2
反射处理:
@Override
public List<Student> getAllStudent(Class cla) {
/*
Connection connection = null;
PreparedStatement pps = null;
*/
try {
/*
//1加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2获得链接
String userName = "root";
String passWord = "123456";
String url = "jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT";
connection = DriverManager.getConnection(url, userName, passWord);
//3定义sql,创建预状态通道(进行sql语句的发送)
String sql = "select * from student ";
pps = connection.prepareStatement(sql);
//执行sql
resultSet = pps.executeQuery();
List students = new ArrayList();//创建学生集合
*/
List students = new ArrayList();//创建学生集合
String sql = "select * from student ";
List list = new ArrayList();
list.add(cla);
query(sql,list);
//通过反射,让结果集自动匹配set方法
//得到当前数据库查询结果的列信息
ResultSetMetaData metaData = rs.getMetaData();//存储结果集信息
int columnCount = metaData.getColumnCount();//得到列数
String[] columnNames = new String[columnCount];//数组长度为列的数量
//获取列名
for(int i = 0;i < columnCount;i++){
columnNames[i] = metaData.getColumnName(i+1);//列从1开始
System.out.println("columnName = "+columnNames[i]);
}
//获取类中所有的方法
Method[] declaredMethods = cla.getDeclaredMethods();
//取值
while(rs.next()){
try {
Object stu = cla.newInstance();//得到一个学生对象
//从对应列取出值,赋给对应的属性
for (String columnName : columnNames) {//遍历列名
String methodName = "set" + columnName;//装饰,set某一属性
for (Method declaredMethod : declaredMethods) {//遍历所有方法
//忽略大小写,通过反射获得的方法名,如果相等,则该方法为对此属性进行赋值的方法
if(declaredMethod.getName().equalsIgnoreCase(methodName)){
//不确定类为什么类型,因此为object
//给某一对象赋值,值为某一列中取出的值
declaredMethod.invoke(stu,rs.getObject(columnName));
break;
}
}
}
students.add(stu);
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
}//end while
return students;
}catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
closeAll();//关闭资源
}
return null;
}
编写测试类Test
public class Test{
public static void main(String[] args) {
DaoImplements dao = new DaoImplements();
List<Student> allStudent =dao.getAllStudent(Student.class);
for (Student student : allStudent) {
System.out.println(student.getStuId()+","+student.getStuName()+","+student.getTeacherid());
}
}//end main
}//end class
运行输出结果:
columnName = stuid
columnName = stuname
columnName = teacherid
1,张三,3
2,李四,1
3,王五,3
4,赵六,1
5,花花,1
6,潇潇,2
由上述例子可见,重复代码在DBUtil类中以方法形式进行封装有效简化了代码,使得程序更简洁更具可读性,在后续的JDBC编程中建议使用
封装优化(properties配置文件)
使用ResourceBundle访问本地资源
在设计时往往需要访问一些适合本地修改的配置信息,如果作为静态变量,那么每次修改都需要重新编译一个class
↓↓↓↓↓↓
这时我们需要通过ResourceBundle,访问位于/WEB-INF/classes目录下后缀名为properties的文本类型文件,从里面读取我们需要的值
properties文件(属性文件)存储数据库信息的格式:key = value
优化的部分:
- 加载驱动
静态代码块在类加载到内存中时便执行
static {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
- 定义用户名密码及url来获得链接
//登录的用户名和密码
private String username = "root";
private String password = "123456";
//url地址
private String url = "jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT";
protected Connection getConnection() {
try {
connection = DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
优化
方法一
新建属性文件db.properties,并将username、password 、url 、driver这些包含配置信息的字段写入文件中,并赋值
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT
username=root
password=123456
在静态代码块中编写读取属性文件的代码
//用户名和密码、url地址
private static String username;
private static String password;
private static String url;
private static String driverName;
//加载驱动
static {
try {
InputStream inputStream = DBUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(inputStream);
driverName = properties.getProperty("driver");//“”为文件中的字段名
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
Class.forName(driverName);
} catch (ClassNotFoundException| IOException e) {
e.printStackTrace();
}
}
方法二
在方法一的基础上进行修改
方法一中读取属性文件需要写文件的后缀名,此方法参数只写属性文件名,不需要写后缀
//用户名和密码、url地址
private static String username;
private static String password;
private static String url;
private static String driverName;
//加载驱动
static {
try {
/*
优化方法一:
InputStream inputStream = DBUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(inputStream);
driverName = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
*/
//优化方法二:
//参数只写属性文件名,不需要写后缀
ResourceBundle bundle = ResourceBundle.getBundle("db");
driverName = bundle.getString("driver");
url = bundle.getString("url");
username = bundle.getString("username");
password = bundle.getString("password");
Class.forName(driverName);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}