将json数据储存在数组_javaSE第十四部分 JDBC(3)java查询mysql数据并将结果集封装为JavaBean数组...

最新推荐文章于 2022-11-11 17:37:10 发布

weixin_39844963

最新推荐文章于 2022-11-11 17:37:10 发布

阅读量140

点赞数

文章标签：将json数据储存在数组将mysql结果转换成数组

将sql的查询结果封装为JavaBean

假如有一个表emp

DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp`  (
  `id` int(11) NOT NULL,
  `ename` varchar(50) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL,
  `job_id` int(11) NULL DEFAULT NULL,
  `mgr` int(11) NULL DEFAULT NULL,
  `joindate` date NULL DEFAULT NULL,
  `salary` decimal(7, 2) NULL DEFAULT NULL,
  `bonus` decimal(7, 2) NULL DEFAULT NULL,
  `dept_id` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `emp_jobid_ref_job_id_fk`(`job_id`) USING BTREE,
  INDEX `emp_deptid_ref_dept_id_fk`(`dept_id`) USING BTREE,
  CONSTRAINT `emp_deptid_ref_dept_id_fk` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `emp_jobid_ref_job_id_fk` FOREIGN KEY (`job_id`) REFERENCES `job` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = gbk COLLATE = gbk_chinese_ci ROW_FORMAT = Dynamic;

向表中插入14条数据

INSERT INTO `emp` VALUES (1001, '孙悟空', 4, 1004, '2000-12-17', 8000.00, NULL, 20);
INSERT INTO `emp` VALUES (1002, '卢俊义', 3, 1006, '2001-02-20', 16000.00, 3000.00, 30);
INSERT INTO `emp` VALUES (1003, '林冲', 3, 1006, '2001-02-22', 12500.00, 5000.00, 30);
INSERT INTO `emp` VALUES (1004, '唐僧', 2, 1009, '2001-04-02', 29750.00, NULL, 20);
INSERT INTO `emp` VALUES (1005, '李逵', 4, 1006, '2001-09-28', 12500.00, 14000.00, 30);
INSERT INTO `emp` VALUES (1006, '宋江', 2, 1009, '2001-05-01', 28500.00, NULL, 30);
INSERT INTO `emp` VALUES (1007, '刘备', 2, 1009, '2001-09-01', 24500.00, NULL, 10);
INSERT INTO `emp` VALUES (1008, '猪八戒', 4, 1004, '2007-04-19', 30000.00, NULL, 20);
INSERT INTO `emp` VALUES (1009, '罗贯中', 1, NULL, '2001-11-17', 50000.00, NULL, 10);
INSERT INTO `emp` VALUES (1010, '吴用', 3, 1006, '2001-09-08', 15000.00, 0.00, 30);
INSERT INTO `emp` VALUES (1011, '沙僧', 4, 1004, '2007-05-23', 11000.00, NULL, 20);
INSERT INTO `emp` VALUES (1012, '李逵', 4, 1006, '2001-12-03', 9500.00, NULL, 30);
INSERT INTO `emp` VALUES (1013, '小白龙', 4, 1004, '2001-12-03', 30000.00, NULL, 20);
INSERT INTO `emp` VALUES (1014, '关羽', 4, 1007, '2002-01-23', 13000.00, NULL, 10);

那么我们在java中创建一个Emp的JavaBean

package demo.jdbcTest;

import java.util.Date;

/**
 * 封装Emp表数据的JavaBean
 */
public class Emp {
	private int id;
	private String ename;
	private int job_id;
	private int mgr;
	private Date joindate;
	private double salary;
	private double bonus;
	private int dept_id;


	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getEname() {
		return ename;
	}

	public void setEname(String ename) {
		this.ename = ename;
	}

	public int getJob_id() {
		return job_id;
	}

	public void setJob_id(int job_id) {
		this.job_id = job_id;
	}

	public int getMgr() {
		return mgr;
	}

	public void setMgr(int mgr) {
		this.mgr = mgr;
	}

	public Date getJoindate() {
		return joindate;
	}

	public void setJoindate(Date joindate) {
		this.joindate = joindate;
	}

	public double getSalary() {
		return salary;
	}

	public void setSalary(double salary) {
		this.salary = salary;
	}


	public int getDept_id() {
		return dept_id;
	}

	public void setDept_id(int dept_id) {
		this.dept_id = dept_id;
	}


	public double getBonus() {
		return bonus;
	}

	public void setBonus(double bonus) {
		this.bonus = bonus;
	}

	@Override
	public String toString() {
		return "Emp{" +
				"id=" + id +
				", ename='" + ename + ''' +
				", job_id=" + job_id +
				", mgr=" + mgr +
				", joindate=" + joindate +
				", salary=" + salary +
				", bonus=" + bonus +
				", dept_id=" + dept_id +
				'}';
	}
}

我们使用java查询emp表，并且将查询结果封装为Emp对象的数组 ArrayList<Emp>，数组中每个元素为一个Emp对象

package demo.jdbcTest;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class JdbcQueryToBean {
/**
 * * 定义一个方法，查询emp表的数据将其封装为对象，然后装载集合，返回。
 */

	public static void main(String[] args) {
		List<Emp> list = new JdbcQueryToBean().findAll();
		System.out.println(list);
		System.out.println(list.size());
	}

	/**
	 * 查询所有emp对象
	 * @return List<Emp> list
	 */
	public List<Emp> findAll() {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		List<Emp> list = null;
		try {
			//1.注册驱动
			//2.获取连接
			conn = JDBCUtils.getConnection();
			//3.定义sql
			String sql = "select * from emp";
			//4.获取执行sql的对象
			stmt = conn.createStatement();
			//5.执行sql
			rs = stmt.executeQuery(sql);
			//6.遍历结果集，封装对象，装载集合
			Emp emp = null;
			list = new ArrayList<Emp>();
			while (rs.next()) {
				//获取数据
				int id = rs.getInt("id");
				String ename = rs.getString("ename");
				int job_id = rs.getInt("job_id");
				int mgr = rs.getInt("mgr");
				Date joindate = rs.getDate("joindate");
				double salary = rs.getDouble("salary");
				double bonus = rs.getDouble("bonus");
				int dept_id = rs.getInt("dept_id");
				// 创建emp对象,并赋值
				emp = new Emp();
				emp.setId(id);
				emp.setEname(ename);
				emp.setJob_id(job_id);
				emp.setMgr(mgr);
				emp.setJoindate(joindate);
				emp.setSalary(salary);
				emp.setBonus(bonus);
				emp.setDept_id(dept_id);
				//装载集合
				list.add(emp);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JDBCUtils.close(rs,stmt,conn);
		}
		return list;
	}
}

输出结果如下：

[Emp{id=1001, ename='孙悟空', job_id=4, mgr=1004, joindate=2000-12-17, salary=8000.0, bonus=0.0, dept_id=20}, 
Emp{id=1002, ename='卢俊义', job_id=3, mgr=1006, joindate=2001-02-20, salary=16000.0, bonus=3000.0, dept_id=30}, 
Emp{id=1003, ename='林冲', job_id=3, mgr=1006, joindate=2001-02-22, salary=12500.0, bonus=5000.0, dept_id=30}, 
Emp{id=1004, ename='唐僧', job_id=2, mgr=1009, joindate=2001-04-02, salary=29750.0, bonus=0.0, dept_id=20}, 
Emp{id=1005, ename='李逵', job_id=4, mgr=1006, joindate=2001-09-28, salary=12500.0, bonus=14000.0, dept_id=30}, 
Emp{id=1006, ename='宋江', job_id=2, mgr=1009, joindate=2001-05-01, salary=28500.0, bonus=0.0, dept_id=30}, 
Emp{id=1007, ename='刘备', job_id=2, mgr=1009, joindate=2001-09-01, salary=24500.0, bonus=0.0, dept_id=10}, 
Emp{id=1008, ename='猪八戒', job_id=4, mgr=1004, joindate=2007-04-19, salary=30000.0, bonus=0.0, dept_id=20}, 
Emp{id=1009, ename='罗贯中', job_id=1, mgr=0, joindate=2001-11-17, salary=50000.0, bonus=0.0, dept_id=10}, 
Emp{id=1010, ename='吴用', job_id=3, mgr=1006, joindate=2001-09-08, salary=15000.0, bonus=0.0, dept_id=30}, 
Emp{id=1011, ename='沙僧', job_id=4, mgr=1004, joindate=2007-05-23, salary=11000.0, bonus=0.0, dept_id=20}, 
Emp{id=1012, ename='李逵', job_id=4, mgr=1006, joindate=2001-12-03, salary=9500.0, bonus=0.0, dept_id=30}, 
Emp{id=1013, ename='小白龙', job_id=4, mgr=1004, joindate=2001-12-03, salary=30000.0, bonus=0.0, dept_id=20}, 
Emp{id=1014, ename='关羽', job_id=4, mgr=1007, joindate=2002-01-23, salary=13000.0, bonus=0.0, dept_id=10}]
14

weixin_39844963

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
将json数据储存在数组_javaSE第十四部分 JDBC(3)java查询mysql数据并将结果集封装为JavaBean数组...

将sql的查询结果封装为JavaBean 假如有一个表empDROP TABLE IF EXISTS `emp`;CREATE TABLE `emp` ( `id` int(11) NOT NULL, `ename` varchar(50) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL, `job_id` in...
复制链接

扫一扫