首页 > 编程语言 >JDBC使用List和Map键值封装多表查询结果 java JDBC insert查询返回List对象

JDBC使用List和Map键值封装多表查询结果 java JDBC insert查询返回List对象

时间：2023-04-27 17:12:47浏览次数：55  
标签：ps JDBC java String dep List 查询 public

JDBC使用List和Map键值封装多表查询结果

https://www.cnblogs.com/zengyu1234/p/15451812.html

查询结果的处理

Java.sql.ResultSet接口是jdbcAPI中唯一用来封装查询结果记录行的组件。

ResultSet接口唯一创建方式是通过执行SQL查询返回创建此对象

 

遍历结果集中数据

  1. true next()方法
  2. 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());

        }

    }

}

 

 

 

标签：ps,JDBC,java,String,dep,List,查询,public
From： https://www.cnblogs.com/chuangsi/p/17359458.html

相关文章

  • asp.net 绑定数组数据到下拉列表，绑定数组到DropDownList，数据绑定
    前台代码：<asp:DropDownListID="ddlHPZL"runat="server"Width="130px"></asp:DropDownList>后台代码：#region绑定号牌号码string[]iniHPHM=newstring[];iniHPHM=["辽P,京A,京B,吉C,黑D,云E,蒙F,"]......
  • 查询出数据库中char型字段的最大值，查出数据库中字段最大值，max，缺少列，xh...
    SELECTmax(cast(xhasint))asxhFROMkk.kkcltj用cast(xhasint)把varchar(2)的类型转成int后再查出最大值注意：xh的字段里只能存储char型或number型数据，否则会报无效符号错误有时候不主意会写成SELECTmax(cast(xhasint))FROMkk.kkcltj这样select出来的值就没有......
  • List与HashMap区别，key，value，感谢火龙果，dgqbcht,awusoft帮助
    首先要感谢不想要妮称，dgqbcht,awusoft友情帮助Map是一个映射,是key-value值对.Map在java1.0以后进行了修改,使其能够与普通的集合相融.在Map的内部定义了内部接口Entry,主要就是要讲key和value以集合(Set)的形式来体现.List是集合的一个分支,是一个接口.List中的元素有顺序(输......
  • @KafkaListener属性简介
    @KafkaListener从2.2.4版开始，您可以直接在注释上指定Kafka使用者属性，这些属性将覆盖在使用者工厂中配置的具有相同名称的所有属性。可以使用#{…​}或属性占位符（${…​}）在SpEL上配置注释上的大多数属性。例如:@KafkaListener(id="consumer-id",......
  • MFC-CListCtrl-SetExtendedStyle设置扩展风格
       mylist.SetExtendedStyle(LVS_SHOWSELALWAYS|LVS_EX_CHECKBOXES|LVS_EX_FULLROWSELECT|LVS_EX_GRIDLINES);//设置扩展风格风格看：https://www.cnblogs.com/liming19680104/p/17358671.html   ......
  • os.listdir()读出来的文件顺序和本地的顺序不一致，应该怎么做呢？
    今日鸡汤冉冉几盈虚，澄澄变今古。大家好，我是皮皮。一、前言前几天在帮助粉丝解决问题的时候，遇到一个简单的小需求，这里拿出来跟大家一起分享，后面再次遇到的时候，可以从这里得到灵感。二、需求澄清问题如下所示：问题截图如下所示：三、实现过程这里【崔峰】给了一个思路，使用编码的方式进......
  • uniapp脚手架项目抖音小程序中使用了uView框架中的IndexList 索引列表组件报错uni.req
    解决办法如果您是vue-cli模式的项目，还需要在项目根目录的vue.config.js文件中进行如下配置：//vue.config.js，如没有此文件则手动创建module.exports={transpileDependencies:['uview-ui']}......
  • mysql 查询某字段中以逗号分隔的字符串的方法
    mysql查询某字段中以逗号分隔的字符串的方法文章目录mysql查询某字段中以逗号分隔的字符串的方法前言https://cdnvue.gymcity.com.cn/betterwe_1.0/userlogo/userlogopro62d8bf98afde709d37c08f7b129e96277388757530c72.png一、首先讲一个需求总结前言现在有如下场景。我们有一个......
  • MybatisPlus 模糊查询数组中的所有字符串
     List<IcvDevice>deviceList=icvDeviceMapper.selectList(Wrappers.<IcvDevice>query().or(qw->pidList.forEach(sbbh->qw.like("sbbh",sbbh))).eq("sssblx&q......
  • 查询域名注册商
    #!/bin/bash#从文件中读取域名列表foriin`catdomain.txt`doregistrar_url=$(whois$i|grep"RegistrarWHOISServer"|awk'{print$NF}')if["$registrar_url"="whois.jumi.com"];then#使用grep命令搜索匹配的域名#matching_dom......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99