查询结果的处理
Java.sql.ResultSet接口是jdbcAPI中唯一用来封装查询结果记录行的组件。
ResultSet接口唯一创建方式是通过执行SQL查询返回创建此对象
遍历结果集中数据
- true next()方法
- getXXX(int index);getXXX(String colum)
while(结果集对象.next()){
变量 = 结果集对象.getXX(1);
变量1 = 结果集对象.getXX(2);
变量2 = 结果集对象.getXX(“age”);
变量3 = 结果集对象.getXX(“birth”);
变量4 = 结果集对象.getXX(“sex”);
empList.add(emp;)//将Emp对象添加到List集合中存储
}
封装结果集中数据行。
每次循环获取结果集当前行封装Java对象后应将其存储在集合或数组中。
使用Map代替自定义实体类封装查询结果数据
优点:减少大量自定义Java类的定义
缺点:通常认为不是好的设计,访问没有java类方便
使用数据库连接池
01 C3P0连接池
02 DBCP连接池
03 Proxool连接池
04 BoneCP连接池
05 Druid(阿里巴巴德鲁伊)连接池
package com.xzit.dao;
import com.xzit.db.util.DataSourceManager;
import com.xzit.pojo.Department;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/*
* 数据访问处理组件,实现对dep_tab 部门表进行增,删,改,查操作
* */
public class DepartmentDao {
/*
* 添加新部门的方法
* @param dep 新添加的部门
* */
public int addDepartment(Department dep){
int res = 0;
String sql="insert into dep_table " +
"(id,name,createtime,descs) values (?,?,?,?)"; //sql插入语句
Connection conn = DataSourceManager.getConnection(); //获取数据库连接
PreparedStatement ps = null; //获取PreparedStatement接口对象
try {
ps = conn.prepareStatement(sql); //获取PreparedStatement对象
/* 设置替换sql语句中的参数占位符? */
ps.setString(1,"NO008");
ps.setString(2,dep.getName());
ps.setDate(3,new java.sql.Date(dep.getCreateDate().getTime()));
ps.setString(4,dep.getDescs());
res = ps.executeUpdate();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally { //关闭数据库连接,释放资源
DataSourceManager.close(ps);
DataSourceManager.close(conn);
}
return res;
}
/**
* 修改选定的部门对象
* @param dep 欲修改的部门
* @return
*/
public int modifyDepartment(Department dep){
int res = 0;
String sql = "update dep_table set name = ?,createtime=?,descs=? "
+ "where id=?";
Connection conn = DataSourceManager.getConnection(); //获取数据库连接
PreparedStatement ps = null; //获取PreparedStatement接口对象
try {
ps = conn.prepareStatement(sql);
ps.setString(1,dep.getName());
ps.setDate(2,new java.sql.Date(dep.getCreateDate().getTime()));
ps.setString(3,dep.getDescs());
ps.setString(4,dep.getId());
res = ps.executeUpdate();
} catch (Exception ex) {
ex.printStackTrace();
}finally {
DataSourceManager.close(ps);
DataSourceManager.close(conn);
}
return res;
}
/**
* 删除部门对象
* @param id 删除部门的id
* @return
*/
public int deleteDepartmentById(String id){
int res = 0;
String sql = "delete from dep_table where id = '"+id+"'";
Connection conn = DataSourceManager.getConnection(); //获取连接
PreparedStatement ps = null; //获取PreparedStatement接口对象
try {
ps = conn.prepareStatement(sql);
//执行删除操作
res = ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally { //关闭数据库连接
DataSourceManager.close(ps);
DataSourceManager.close(conn);
}
return res;
}
/**
* 查询获取所有部门信息列表
* @return List<Department> 所有部门列表
*/
public List<Department> queryDepartmentList(){
List<Department> depList = new ArrayList<Department>();
String sql = "select ID depid,NAME depname," +
"CREATETIME createdate,DESCS dec from dep_table";
Connection conn = DataSourceManager.getConnection(); //获取连接
PreparedStatement ps = null; //获取PreparedStatement接口对象
ResultSet set = null;
try {
ps = conn.prepareStatement(sql);
set = ps.executeQuery(); //查询返回ResultSet结果集对象
/* 处理结果集,封装结果集中行为java对象 */
while(set.next()){
Department dep = new Department();//每一行创建一个Department对象
String id = set.getString(1); //获取第1列id列值
dep.setId(id);
dep.setName(set.getString("depname"));
dep.setCreateDate(set.getDate("createdate"));
dep.setDescs(set.getString(4));
depList.add(dep);//将dep对象添加到List集合中
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DataSourceManager.close(set);
DataSourceManager.close(ps);
DataSourceManager.close(conn);
}
return depList; //返回封装Department List集合
}
/**
* 查询员工年龄大于给定参数的信息
* @param age 年龄
* @return
*/
public List<Map<String,Object>> queryEmpByArgs(int age){
List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
String sql = "select d.NAME dname,e.NAME ename,e.address,e.age\n"
+ "from dep_table d INNER JOIN emp_tab e\n"
+ "on d.id = e.dep_id where e.age > "+age+"";
Connection conn = DataSourceManager.getConnection(); //获取连接
PreparedStatement ps = null; //获取PreparedStatement接口对象
ResultSet set = null;
try {
ps = conn.prepareStatement(sql);
set = ps.executeQuery(); //查询返回ResultSet结果集对象
/* 处理结果集,封装结果集中行为java对象 */
while(set.next()){
Map<String,Object> map = new HashMap<String,Object>(); //每行记录封装为Map对象
map.put("dname",set.getString("dname"));
map.put("ename",set.getString("ename"));
map.put("address",set.getString("address"));
map.put("age",set.getInt("age"));
list.add(map);//将Map对象添加到List集合中
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DataSourceManager.close(set);
DataSourceManager.close(ps);
DataSourceManager.close(conn);
}
return list;//返回封装Map对象的List接口对象
}
}
package com.xzit.test;
import com.xzit.dao.DepartmentDao;
import com.xzit.pojo.Department;
import java.util.List;
public class BaseQueryTest {
public static void main(String[] args) {
List<Department> deps = new DepartmentDao().queryDepartmentList();
System.out.println("ID编号\t部门名称\t成立日期\t部门描述");
for (Department dep:deps){
System.out.println(dep.getId()+"\t"+dep.getName()
+ "\t"+dep.getCreateDate()+"\t"
+ (dep.getDescs()==null?"":dep.getDescs()));
}
}
}
package com.xzit.test;
import com.xzit.dao.DepartmentDao;
import java.util.List;
import java.util.Map;
public class TestMap {
public static void main(String[] args) {
List<Map<String,Object>> list = new DepartmentDao().queryEmpByArgs(27);
for (Map<String,Object> map:list){
System.out.println(map.get("dname")+"\t"+map.get("ename")
+"\t"+map.get("address")+"\t"+map.get("age"));
}
}
}
package com.xzit.db.util;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.beans.PropertyVetoException;
public class DataSourceForPool {
private static ComboPooledDataSource c3p0;
/**
* 创建ComboPooledDataSource数据源
*/
private static void createComboPooledDataSource(){
if(c3p0 == null){
c3p0 = new ComboPooledDataSource();
/* 数据源相关属性 */
try {
c3p0.setDriverClass(Env.JDBC_DRIVER);
c3p0.setUser(Env.JDBC_USER);
c3p0.setPassword(Env.JDBC_PASSWORD);
c3p0.setJdbcUrl(Env.JDBC_URL);
c3p0.setCheckoutTimeout(3000);
c3p0.setDataSourceName("c3p0DataSource");
c3p0.setMaxPoolSize(30);
} catch (PropertyVetoException e) {
e.printStackTrace();
}
}
}
}
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>org.example</groupId>
<artifactId>2021_10_13_jdbcapp</artifactId>
<version>1.0-SNAPSHOT</version>
<name>2021_10_13_jdbcapp</name>
<!-- FIXME change it to the project's website -->
<url>http://www.example.com</url>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>1.7</maven.compiler.source>
<maven.compiler.target>1.7</maven.compiler.target>
</properties>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<scope>test</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/com.oracle.database.jdbc/ojdbc8 -->
<dependency>
<groupId>com.oracle.database.jdbc</groupId>
<artifactId>ojdbc8</artifactId>
<version>12.2.0.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.mchange/c3p0 -->
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.5</version>
</dependency>
</dependencies>
<build>
<pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) -->
<plugins>
<!-- clean lifecycle, see https://maven.apache.org/ref/current/maven-core/lifecycles.html#clean_Lifecycle -->
<plugin>
<artifactId>maven-clean-plugin</artifactId>
<version>3.1.0</version>
</plugin>
<!-- default lifecycle, jar packaging: see https://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_jar_packaging -->
<plugin>
<artifactId>maven-resources-plugin</artifactId>
<version>3.0.2</version>
</plugin>
<plugin>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.8.0</version>
</plugin>
<plugin>
<artifactId>maven-surefire-plugin</artifactId>
<version>2.22.1</version>
</plugin>
<plugin>
<artifactId>maven-jar-plugin</artifactId>
<version>3.0.2</version>
</plugin>
<plugin>
<artifactId>maven-install-plugin</artifactId>
<version>2.5.2</version>
</plugin>
<plugin>
<artifactId>maven-deploy-plugin</artifactId>
<version>2.8.2</version>
</plugin>
<!-- site lifecycle, see https://maven.apache.org/ref/current/maven-core/lifecycles.html#site_Lifecycle -->
<plugin>
<artifactId>maven-site-plugin</artifactId>
<version>3.7.1</version>
</plugin>
<plugin>
<artifactId>maven-project-info-reports-plugin</artifactId>
<version>3.0.0</version>
</plugin>
</plugins>
</pluginManagement>
</build>
</project>