JDBC使用List和Map键值封装多表查询结果 java JDBC insert查询返回List对象
JDBC使用List和Map键值封装多表查询结果
https://www.cnblogs.com/zengyu1234/p/15451812.html
查询结果的处理
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>
java JDBC insert查询返回List对象
一，封装对象
package JDBC;
public class student {
private int id;
private String name;
private String gender;
private int age;
private String address;
private String qq;
private String email;
private String username;
private String password;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getQq() {
return qq;
}
public void setQq(String qq) {
this.qq = qq;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "student{" +
"id=" + id +
", name='" + name + '\'' +
", gender='" + gender + '\'' +
", age=" + age +
", address='" + address + '\'' +
", qq='" + qq + '\'' +
", email='" + email + '\'' +
", username='" + username + '\'' +
", password='" + password + '\'' +
'}';
}
}
二，链接数据库并且查询返回List对象
package JDBC;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class my2 {
public List<student> findAll() throws ClassNotFoundException, SQLException {
List<student> list=null;
Connection connection=null;
Statement statement=null;
ResultSet re=null;
Class.forName("com.mysql.cj.jdbc.Driver");
// 获取数据库链接对象
connection= DriverManager.getConnection("jdbc:mysql://localhost:3306/day17","root","18388543714a");
// 定义SQL
String sql="select * from user";
//获取执行对象
statement=connection.createStatement();
// 执行SQL
re=statement.executeQuery(sql);
student student =null;
list=new ArrayList<student>();
while (re.next()){
// 获取数据
int id=re.getInt("id");
String name=re.getString("name");
String gender=re.getString("gender");
int age=re.getInt("age");
String address=re.getString("address");
String qq=re.getString("qq");
String email=re.getString("email");
String username=re.getString("username");
String password=re.getString("password");
// 创建student对象
student =new student();
student.setId(id);
student.setName(name);
student.setGender(gender);
student.setAge(age);
student.setAddress(address);
student.setQq(qq);
student.setEmail(email);
student.setUsername(username);
student.setPassword(password);
// 装载集合
list.add(student);
}
if (connection!=null){
connection.close();
}
if (statement!=null){
statement.close();
}
if (re!=null){
re.close();
}
return list;
}
}
三.测试
package JDBC;
import java.sql.SQLException;
import java.util.List;
public class ListTest {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
List<student> list=new my2().findAll();
for (student ss:list){
System.out.println(ss.getName()+"..."+ss.getAge());
}
}
}
